AWR / Statspack Analysis

Problem : Performance and CPU





Solution : The 3rd TOP 5 Wait Event 'log file switch' indicates there is a problem with Checkpoint and Switching. It directly relates to a problem with the size of the online redo log files. According to the statpack we found that there are 70 + switches perfoemd by the database per hour and also a Delete Statement was found as the TOP CPU Consuming Statement, It clearly states that there is a bottleneck during the delete operation as much redo will be generated and because of small online redo log files, switched are happening frequently which in turn making the delete statement wait for log switch to perform. Also the delete statement is using the Index which is the cuase for the first 'db file sequential read ' wait event.

Happy Tuning
Team ORAKHOJ -- Get Free AWR / STATSPACK Analysis from ORAKHOJ tuning@orakhoj.com

Statspack/ AWR Analysis

Oracle Release 10.2.0.3
Problem CPU and Performance

Top 5 Wait Events

You can find 'db file sequential read' and 'read by other session' apart from CPU as TOP 5Events. Based on the 'db file sequential read' it is evident that the system is using most of the indexes.

Once we go to the other wait event 'read by other session' which is nothing but buffer busy waits where information is requested from the database, Oracle will first read the data from disk into the database buffer cache. If two or more sessions request the same information, the first session will read the data into the buffer cache while other sessions wait; e.g. many sessions scanning the same index or performing full-table scans on the same table. Tuning this issue is a matter of finding and eliminating this contention.

So to figure which object is causing the contention, you can look at which tablespace/datafile has got buffer waits and which object is falling under the Segments by Physical Reads and Logical Reads. Based out which we have identified an index which is the cause for this contention. The same has been rebuild on to a different tablespace to reduce the contention.

You can get free tuning advices from our DB experts by simply sending mail to tuning@orakhoj.com
-------------------------------------------------------------------------------

Statspack/ AWR Analysis

Database Version = 10.2.0.3



Problem : High CPU consumption.

Solution : Based on the above top 5 events one can cleary see that db file scattered read is high event after the CPU, which means that the application is performing full tablescans. Based on this one has to look at SQL ordered by CPU session of the report to find out which queries on performing full table scan. Based on the index recommendations the above problem has been sorted out.

Team ORAKHOJ

You can also get free tuning analysis from our database experts. Please send a mail to tuning@orakhoj.com

Free Statspack/AWR Analysis

Dear Oracle Professional,
Free Tuning Advisory Service has been started by ORAKHOJ- Exclusive Search Engine for Oracle Professionals. You can get free analysis & tuning recommendations from our tuning experts. Mail your AWR (html format) or Statspack reports to tuning@orakhoj.com. AWR / Statspack can be of RAC / Non RAC databases.
For best results, please collect AWR or Statspack report for 30 to 60 minutes duration.The information shared will be kept confidential including the mailer information.

Thank you for your patronage and keep using ORAKHOJ. - The ORAKHOJ Team"
www.orakhoj.com