| Rosco's Performance Tuning Guide [message #195322] |
Thu, 28 September 2006 00:12  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
There is a small motley assortment of people here that use my tuning guide at http://www.orafaq.com/tuningguide/. The tuning guide and all of its content is free, and it includes a link to a WinZip file that makes the entire guide downloadable for offline use.
I periodically read through the old material and update it (I dream of the day when it is in wide enough circulation that I get unsolicted feedback!!), but this leaves the downloaders in the lurch.
As a concession, anyone interested in updates should subscribe to this thread. When I post updates (don't hold your breath), I will add a new reply to the thread and you will be emailed automatically. Really conscientious users of the guide may also use this thread to provide feedback.
Apologies in advance to the organisers of this site if this is a misuse of the technology, but my site is completely free and contains no advertising whatsoever. In no way can this be considered spam.
|
|
|
|
|
|
|
|
| Re: Rosco's Performance Tuning Guide [message #195468 is a reply to message #195434] |
Thu, 28 September 2006 18:49  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Got a PM from @faiz_hyd pointing out that st.sql was 10g specific.
I'm a bit of a cowboy when it comes to backwards compatability, when I go 10g, everyone does!
I reconstructed the 7->9i version from memory, but I'm on holidays at the moment and don't have database to test on, so hopefully it works. The site now contains 2 versions: st.sql and st10g.sql
Ross Leishman
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Rosco's Performance Tuning Guide [message #249555 is a reply to message #249411] |
Thu, 05 July 2007 00:46 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Sorry about that. It's a bit old and superseded.
rplan.sql looks like this:
accept stmtid prompt "Statement ID: "
SELECT LPAD(' ',2*(LEVEL-1))||operation||' '||options
||' '||object_name
||' '||DECODE(id, 0, 'Cost = '||position) "Query Plan"
FROM plan_table
START WITH
id = 0
AND statement_id = '&stmtid'
CONNECT BY
PRIOR id = parent_id
AND statement_id = 'stmtid';
A better way to do it in v9i and 10g is:
SELECT plan_table_output
FROM table(dbms_xplan.display());
I have some other handy Explain Plan scripts that display the plan for a SQL running in another session, the plan for the SQL currently in the SQL*Pluys Buffer, and the plan for the last SQL you executed. One day I will add them to the page.
Ross Leishman
|
|
|
|
| Re: Rosco's Performance Tuning Guide [message #314890 is a reply to message #249555] |
Fri, 18 April 2008 03:33 |
virmahi
Messages: 266
Registered: April 2008
Location: India
|
Senior Member |
|
|
Hi Ross,
U indeed have done a great work. This link is very useful and explainatory.
I tried to download the file stats.sql from the link -->> Analysing sql problem -->> Quick fixes -->> No Statistices
I was not able to download the file as it says | Quote: | The page you are looking for has restricted access. You are not allowed to access this page. If this is a mistake, please report the problem to the Webmaster.
|
Please have a look at it.
Thanks,
Mahi
[Updated on: Fri, 18 April 2008 08:09] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Rosco's Performance Tuning Guide [message #315047 is a reply to message #195322] |
Fri, 18 April 2008 13:50 |
varu123
Messages: 754
Registered: October 2007
|
Senior Member |
|
|
From the tuning guide,
| Quote: | Scope
There are 4 main areas of performance tuning:
* SQL Tuning – Responsibility of the Developer
|
I always use to think its the DBA that tunes SQL because tuning is a specilaised job.
Its great to know developer running explain plan,TKPROF,,etc.
|
|
|
|
| Re: Rosco's Performance Tuning Guide [message #315077 is a reply to message #315047] |
Fri, 18 April 2008 20:26 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
| varu123 wrote on Sat, 19 April 2008 04:50 | I always use to think its the DBA that tunes SQL because tuning is a specilaised job.
|
That's a self-fulfiling prophecy.
If you take the approach that tuning is someone else's problem, you won't do it. And when the excrement hits the fan, the boss goes to the most highly paid and skilled person they can find. That's not always the DBA, but if your entire shop is filled with people who don't bother tuning, it's a fair chance.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
