Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Thursday, 10 January 2013

How to Tune Oracle 10g Database

Database Tuning is essential not only for better execution of SQL statement but also for smooth running of applications. Oracle gives us flexible option in which we can generate AWR report in for tuning purpose. Same work you can perform easily through License version of Enterprise Manager. It is not possible to highlights every thing as the performance tuning is a big concept. Through this article I am trying to cover some of the required concept for database performing tuning.
Collecting Statspack/AWR Report:
Login with SYSDBA and make the Oracle Environment for the particular Database is set. If you are oracle 10g or onward gather the AWR report. In oracle 9i you can generate Statspack for the same purpose. If you want the AWR report in Oracle RAC environment than use 'awrgrpt.sql' script to gather the report of multiple instances running on various nodes.
DBID        NAME
691421794   ORCL3
SQL> select status from v$instance;
Now select format for the report either ‘HTML’ or ‘TEXT’
Here Select numbers of days you want to go back or just hit enter for listing all completed snapshots then

Enter value for begin_snap: 181
Enter value for end_snap: 181
Enter value for report_name:awrrpt_1_181_181.html
Enter value for report_type: html
Here you can specify the name of the report or select the default name assigned (html). Enter 'html' for an HTML report, or 'text' for plain text Defaults is 'html'. Use ls –ltr to show the new file created under the path: d01/oratst/orcl3/product/10.2/rdbms/admin. Check my other post: How to Create AWR Report Manually, How to use Statspack Report
Analyzing the AWR report and Suggesting possible Recommendations:
Once we obtain the AWR report our main motive is to analyze the AWR report and come up with possible recommendations. Depending on the size of our Production Database we can come up with possible recommendations.
Note: This recommended result should be first implemented in test environment and after successful results should be adopted in production environments.
We need to make sure our redo logs are large enough. Check the number of log switches, one every twenty minutes is ideal, more than this is too high and you should make them larger to reduce the number of switches. We can find the log switches in the Instance Activity Stats part of the AWR report. Check more about Redolog Performace: Redolog Performace
Instance Activity Stats - Thread Activity
* Statistics identified by '(derived)' come from sources other than SYSSTAT
Statistic Total per Hour
log switches (derived)
2 2.00
We can see in this system there are 2 log switches per hourly basis, which is
good. So this tells us the redo logs are large enough.
Check the hard parsing amount. It should be zero. If it is not, this indicates that our SGA is probably too small, increase the size of SGA and test again. Hard parsing is caused by use of literals in SQL (as opposed to bind variables).If the queries in question are our own, we should change them to use bind variables. We can find this information on the first page.
Load Profile per Second per Transaction per Exec per Call.
Parses: 33.9 7.2
hard parses:
0.5 0.1
We can see in this system the hard parses are almost zero, which is good. Now coming to the SGA we can focus on the below considerations:
Buffer hit and Library hit percentages:
Check the buffer hit and library hit percentages. We want them to be 100%, if not we should increase the size of SGA. This is also on the first page: How to take Health report check my post:
DB Health Report
Instance Efficiency Percentages (Target 100%)
Buffer Nowait %: 99.82 Redo NoWait %: 100.00
Buffer Hit %: 99.52 In-memory Sort %:
Library Hit %: 98.63 Soft Parse %:
Execute to Parse %: 50.96 Latch Hit %: 98.16
Parse CPU to Parse Elapsd %: 66.67 % Non-Parse CPU: 97.75
In this case they are also
Top 5 Timed Foreground Events:

Check the average wait times. Anything over 5ms indicates a problem. If we see database CPU events in the Top 5, this indicates that SGA is too small. We may also be missing indexes. Check the optimizer statistics.
Avg wait % DB
Event Waits Time(s) (ms) time Wait Class
---------- ------------ ----------- ------ ------ ----------
DB CPU 13 59.9
log file sync 1,592 8 5 32.3 Commit
sort segment request 1 1 1001 4.0 Configurat
db file sequential read 216 1 4 3.6 User I/O
db file scattered read 64 0 6 1.5 User I/O
We can see here that the major issue is DB CPU, which generally indicates SGA is too small. Check my other post (under Performance Tuning how Category) to find DB CPU report.
Database file sequential/scattered read
These indicate time spent doing table scans and index scans (respectively).If these are high (over 5ms),We should consider moving your data files to reduce disk I/O contention, or move them to faster disks.
5. Enqueue high watermark:
This indicates hardware contention that occurs when there are multiple users inserting into LOB segments at once while the database is trying to reclaim unused space. We should consider enabling secure files to improve LOB performance (SECURE_FILES = ALWAYS). We cannot see these in my example report, because this was not a problem in my environment, so it did not make it into the Top 5. If it did, you would see an event called: example: HW – contention
other things to be aware of… we will also check our database configuration.
Do not use this setting. We should have our DBA tune the memory manually instead. This will result in a better tuned database. We should start with 60% of physical memory allocated to SGA and 20% to PGA.
Usually we do not use this setting much for tuning. But auditing on Database level
can be overhead to the Database.
Related Post:

DB Monitoring & Performance Script


  1. Shahid, great post, thanks so much!

    I think this page is also helpful for performance tuning:

    Tuning tips for database

    1. I will appreciated if related link is pasted.

  2. Nice blog. Really helpful information about Oracle …. Please keep update some more…………

  3. Thanks for splitting your comprehension with us. It’s really useful to me & I hope it helps the people who in need of this vital informationOracle performance Tunning Training in Chennai