Home » RDBMS Server » Performance Tuning » Rosco's Performance Tuning Guide
Rosco's Performance Tuning Guide [message #195322] Thu, 28 September 2006 00:12 Go to next message
rleishman
Messages: 3724
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 #195371 is a reply to message #195322] Thu, 28 September 2006 05:08 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Thanks for sharing! this will be very useful to me..
Re: Rosco's Performance Tuning Guide [message #195434 is a reply to message #195322] Thu, 28 September 2006 10:35 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Great Work Ross ,

I visited your site couple of times earlier and it is a very good resource and the way you explained and targetted more on Tuning aspect is great.

Best Regards
Re: Rosco's Performance Tuning Guide [message #195468 is a reply to message #195434] Thu, 28 September 2006 18:49 Go to previous messageGo to next message
rleishman
Messages: 3724
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 #210781 is a reply to message #195468] Fri, 22 December 2006 05:36 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Some minor mods now available:
- Mitigating sub-query blowout with an index
- Perils of foreign keys on high volume inserts and updates
- and not much more...
Updates for the true believers in the original post above.

Ross Leishman
Re: Rosco's Performance Tuning Guide [message #211424 is a reply to message #210781] Thu, 28 December 2006 10:01 Go to previous messageGo to next message
gbarbisan
Messages: 67
Registered: August 2006
Location: Treviso - Italy
Member
Great work!

Thanks for sharing it with us.

Giovanni.
Re: Rosco's Performance Tuning Guide [message #248733 is a reply to message #211424] Sun, 01 July 2007 02:12 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Made a long-overdue update to the Fragmented Indexes page.
Re: Rosco's Performance Tuning Guide [message #249411 is a reply to message #248733] Wed, 04 July 2007 06:31 Go to previous messageGo to next message
kpremsagar
Messages: 26
Registered: June 2007
Junior Member
Hi Rosco,
I found your site to be very useful and easy to read.

However, the attachment mentioned here doesnt seem to exist. Can you pls upload that to your site again?

This is the one:
http://people.aapt.net.au/roxsco/tuning/rplan.sql

Regards
Prem
Re: Rosco's Performance Tuning Guide [message #249555 is a reply to message #249411] Thu, 05 July 2007 00:46 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
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 #314981 is a reply to message #314890] Fri, 18 April 2008 08:02 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I changed ISP, and since I'm too lazy and cheap to buy a domain name, OraFAQ kindy offered to host it for me. I cannot get in and check what the problem is. Could be that the file is not there; could be that OraFAQ's web server is blocking the file type.

Frank, if you read this, could you check to see if stats.sql is in the tuningguide directory.

Here it is anyway

Ross Leishman
  • Attachment: stats.sql
    (Size: 2.90KB, Downloaded 254 times)
Re: Rosco's Performance Tuning Guide [message #315047 is a reply to message #195322] Fri, 18 April 2008 13:50 Go to previous messageGo to next message
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 Go to previous message
rleishman
Messages: 3724
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
Previous Topic: how can i execute this query faster
Next Topic: more reloads
Goto Forum:
  


Current Time: Sat Dec 10 18:55:01 CST 2016

Total time taken to generate the page: 0.04842 seconds