HomePage     |    Contact Us    |   Contribute     |  Forums     |  TechWiki

Oracle Performance Tuning

Speed, fascinates almost everyone and this attraction to speed has driven you to read this blog  on application tuning. After reading this chapter and implementing some of the things mentioned you might be able to improve performance by more than 100% in some of the applications. Now this may appear to be a very big number but based on our experience this is a achievable target and this was done not at one place but few places and was done with least amount of effort, but always in a methodical and iterative way. We do not advocate that you can increase performance by just working on database or by just working on the hardware or just working on network or just working on application. It has to be worked on all the layers of technology. But for the purposes of this chapter let us just focus on the database level. This is where you can gain maximum ROI, maximum gain for least amount of effort. (Usually, since data is growing at a much faster rate than anything else in the world). Performance improvement process in a database, is a very simple and logical process, sometimes I feel it is a combination of Art and Science. Okay, let us start. First we need to decide who will do it? Database performance improvement is not 100% responsibility of a DBA and it is not 100% responsibility of a application developer. This is one of the areas where DBA’s and Developers will have to have do a handshake and work on a common goal, the goal to improve performance of their application. To achieve that first we need to identify: - 1.      What is the Problem? Of course we are talking about performance, but what I mean is, what to look for in a database to see where the bottleneck is? Is it just full table scans or something else? 2.      Once you have identified the above then have some Goals. You need not wait for the management to decide your goals (they may take more time to set your goals), decide it for yourself. If 100 queries are doing full table scans then have some realistic goal, say reduce the full table scans by 50%. (A very important note: Full Table Sans are always not bad, Oracle may find full table scans to be cheaper sometimes, compared to index scans.). A Goal is very important when any initiative of performance improvements is considered else we will be doing modifications not realizing it. Also if we don’t have a Goal then we may sometimes end up over-tuning the database, and that is also not a very desirable situation since the impact can be heavy for small changes to the database. 3.      Decide on the mode of Action- How the changes will be implemented? Try to break all your proposed changes into set of 3 iterations. (3 is a magic number and has always worked for me). 4.      Process to Measure improvements. Decide before hand, how you are going to measure those changes both in development, quality assurance and production. Make sure all the impacted modules are performance tested for the before and after images. 5.      Apply the changes. Follow your development process to apply the change, DO NOT do the changes in production directly. 6.      Have the Rollback scripts ready, just in case. (Not that you will need them). How to identify where and what the problem is? There are different tools available in market, but I am not a tool guy. I believe in running small small queries in the database system tables and finding out what’s going on. The two very important queries that I always use to determine the bad sql’s are SELECT disk_reads, executions, disk_reads/executions, sql_text FROM v$sqlarea WHERE disk_reads > 5000 ORDER BY disk_reads SELECT buffer_gets, executions, buffer_gets/executions, sql_text FROM v$sqlarea WHERE buffer_gets > 100000 ORDER BY buffer_gets You can decide for yourself what the number for disk-reads and buffer_gets should be. These queries give you the sql statements that are biggest consumers of your disk reads and buffer gets. I can guarantee that if you can improve the performance of these queries alone you should see considerable improvement in the performance of your application and overall database health. Another very good way to get all the bad SQL’s and see many other details is oracle stats pack. Take some instance snap shots during the peak period and make sure that the interval is around 15 mins and not more. The longer the interval, the chances of skewed data are more. The initialization parameter TIMED_STATISTICS should be set to true. Log in a sys and execute command ALTER SYSTEM SET TIMED_STATISTICS = TRUE, you need not bounce the database after that. You can also set this parameter as true permanently in the init.ora.  For more information please refer to oracle documentation for this. Once you have this parameter set, we need to run the some scripts provided by Oracle to gather a given snapshot for the Oracle instance. Execute $ORACLE_HOME/rdbms/admin/utlbstat.sql to begin the snapshot. Execute $ORACLE_HOME/rdmbs/admin/utlestat.sql To be continued…