Professional Oracle tuning with Statspack

This piece of information is for my friends and others who are keen and interested in knowing about me and my current work in Oracle. Also as a guide/starting point for some of them who mailed me recently as to what is this tuning Oracle all about :
Here are my comprehensive points/tips on that :(Don't forget to see my favourite links down below on this page...) ...

1. If you need installation information/usage info,you can read the Oracle supplied spdoc.txt which you can find in your ORACLE_HOME/rdbms/admin folder of your ORACLE home installation in your oracle DB server.All the information on how Statspack works,installation,usage,customizing it to collect stats automatically,maintenance of historic statspack data are neatly documented here in spdoc.txt.
Pls give it a reading ..it's worth spending some time here.Also if you have basic understanding of how Oracle processes sql and aware of V$ views in Oracle,then you can get some good info from other Oracle supplied sql scripts like sprepsql.sql which you use in getting sql reports . You can actually see how the ratios appearing in statspack are getting computed here.

2. In terms of interpreting it for analyzing performance problems this is really a huge area , i should say..But fortunately there are many good websites which give us good starting guide/info on this.

3. http://www.oraperf.com is a very good website which would give you good overall picture of performance issues in your DB.You can actually upload your statspack report for your oracle version and get a free advice report on performance related issues.I suggest you start with this free website which bases its interpretation on Oracle Wait Interface rather than the conventional hit ratios analysis.There are two schools in Oracle performance tuning..One which focuses on hit ratios and proactive tuning tech..and the other very popular one focuses on Oracle Wait interface analysis(often called reactive tuning and well suited for Systems which are already in live).
(Don't miss the paper by Anjo on Wait Interface tuning and also his blog...from the site)


4. These days i like reading Burleson's books a lot and found new interests in his articles too.
In particular i use the book Oracle 9i High Performance Tuning With Statspack by well known Oracle guru Don.Burleson. This book is really worth going buying it and gives you a complete picture of interpreting Statspack reports.This is a very written book which gives you scripts to start off with,Apart from Oracle supplied tables under PERFSTAT schema for Statspack,Burleson has developed some other small tools which will aid you in Disk,CPU and memory tuning of your Oracle DB server . Tuning your Oracle DB server in terms of CPU,Memory and Storage system is one that is not covered in statspack reports and this book would be ideal for tuning Unix based Oracle servers. Object growth analysis and Statspack Trend analysis are also covered in this book.
Pls read this http://www.osborne.com/products/0072133783/0072133783_ch15.pdf
(Trend Analysis using Statspack data) This is actually from the above book by Burleson and is very simple to understand.

5.There are a whole lot of tools available for interpreting statspack reports which aid in tuning effectively. If you can afford to buy one you can go for the one by http://dba-oracle.com/oracle_news/2005_1_11_statspack_viewer_software.htm. If you are very serious about performance tuning and your business management takes it seriously, much depends on your analysis etc..then you can go for it. Otherwise you can do without it. Afterall tuning Oracle applications depends much on KYD factor(Knowing Your data) and application sqls. 90% of tuning issues would be related to Application sqls and design issues . Only the remaining 10% would depend on Oracle instance tuning and DB server tuning(CPU,memory and Disk).

6. This tip is particularly relevant to one of my friend who mailed me the other day asking about how to go about reading Statspack reports for benchmarking and analyzing performance problems.
Thanks to my friend ..i am writing these tips becos of him and made me share a few things with others on Oracle tuning issues.

Since you say that your system is already in live and that you are planning to implement a new program you should focus on reactive tuning rather than the traditional top-down approach to tuning. Just get a good idea of how your existing system behaves and have the SLAs before you (say, this xxx report needs to finish in yyy seconds etc). After you implement your new program if any of the SLAs get disturbed you can start tuning by focusing on them. This way your tuning would be more meaningful and cost-effective pleasing your management at the same time. In case you have some tough DBA issues in the process you can raise them in this good DBA forum..(http://dba.ipbhost.com/index.php) In case you need any other info,pls feel free to contact me or in the above forum for DBAs

Hope this helps and Good luck to your future efforts ..

For all those who read this article,pls rate it and post your relevant comments and share things you know.