Oracle Database Discussions

I am a newbie DBA and faced with a task for performance improvement of the database as users are complaining of response time of the report

Our database is running on Release 11.2.0.3.0 running on AIX based systems ( 64 bits) .

I tried running the below query dealing with performance metrics on the database and below is the result.

select METRIC_NAME,

  VALUE

from SYS.V_$SYSMETRIC

where METRIC_NAME IN ('Database CPU Time Ratio',

  'Database Wait Time Ratio') AND

  INTSIZE_CSEC =

  (select max(INTSIZE_CSEC) from SYS.V_$SYSMETRIC);

I read in one Oracle blog that in a healthy database the Database CPU Time Ratio should be around 90-95% but in my case it is between 3 to 15%.So it seems that the database us busy waiting than processing.

When I tried to drill down I found the I/O class to the "biggest" wait event, always at the top. I used the following query

select WAIT_CLASS,

  TOTAL_WAITS,

  round(100 * (TOTAL_WAITS / SUM_WAITS),2) PCT_WAITS,

  ROUND((TIME_WAITED / 100),2) TIME_WAITED_SECS,

  round(100 * (TIME_WAITED / SUM_TIME),2) PCT_TIME

from

(select WAIT_CLASS,

  TOTAL_WAITS,

  TIME_WAITED

from V$SYSTEM_WAIT_CLASS

where WAIT_CLASS != 'Idle'),

(select sum(TOTAL_WAITS) SUM_WAITS,

  sum(TIME_WAITED) SUM_TIME

from V$SYSTEM_WAIT_CLASS

where WAIT_CLASS != 'Idle')

order by 5 desc;

After more or less identifying that I/O is the main bottel neck, I executed the worst performing ( heavy reports) at the same time and took the AWR report.Below is the snapshot of the report. I could see from the Load Profile that "Physical Reads" is around 5000/sec and Hard parsing in 1.2 per secs. Top 5 Waiting Events are all of class "I/O" with direct path read having highest wait time ( 56 ms). Is there anything else I can infer from the "Top 5 Waiting Events". I have also attached the Instance Stats related to physical read I/O request , bytes etc as well as break up of IO stats. Is my understanding correct that I/O is the main constraint ?....

2nd Question is how to improve/eliminate the bottle neck ? Is it hardware or SQL query ? If it is the SQL query, which section of SQL statistics should U be looking ? SQL ordered by "elapsed time" or "User I/O wait time" or "Read" or "Physical read"

Looking forward for co operation from all.Thanks in advance

Load Profile

Instance Efficiency Percentages (Target 100%)

Shared Pool Statistics

Top 5 Timed Foreground Events

The foreground Wait calss shows that User I/O accounting to 53% of DB time

Foreground Wait Class

• s - second, ms - millisecond - 1000th of a second
• ordered by wait time desc, waits desc
• %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
• Captured Time accounts for 64.6% of Total DB time 100,564.61 (s)
• Total FG Wait Time: 57,498.11 (s) DB CPU time: 7,502.80 (s)

Instance Activity Stats

IOStat by Function summary

• 'Data' columns suffixed with M,G,T,P are in multiples of 1024 other columns suffixed with K,M,G,T,P are in multiples of 1000
• ordered by (Data Read + Write) desc

IOStat by Filetype summary

• 'Data' columns suffixed with M,G,T,P are in multiples of 1024 other columns suffixed with K,M,G,T,P are in multiples of 1000
• Small Read and Large Read are average service times, in milliseconds
• Ordered by (Data Read + Write) desc

IOStat by Function/Filetype summary

• 'Data' columns suffixed with M,G,T,P are in multiples of 1024 other columns suffixed with K,M,G,T,P are in multiples of 1000
• Ordered by (Data Read + Write) desc for each function