Home › Discussions › Questions & Answers › Diagnosing Bottlenecks in Current SQL Workload (SGA)
Customer · · Questions & Answers · 5 replies
We need to analyze the SQL performance summary immediately to catch transient performance issues before they hit AWR. What metrics in the DBA SGA SQL Performance Summary are key for identifying IO bottlenecks?
Beyond execution time, look closely at the ‘User Io Wait Time’, ‘Disk IO’, and ‘Buffer IO’ columns. The report also calculates ‘IO Sec Avg’, which is the average IO in MB per second per overall server time, indicating the average IO server load of the individual SQL.
There is a column called ‘IO Factor’. How should I interpret that metric for performance tuning?
The ‘IO Factor’ indicates how much faster the query would execute if it ran without I/O wait times or unlimited memory. A high factor suggests performance is heavily constrained by IO waits.
What about tuning concurrency waits, like buffer busy waits?
The report includes a ‘Concurrency Wait Time’ column, which captures total elapsed time from the ‘Concurrency’ wait event class, such as ‘buffer busy waits’ or ‘enq: TX – index contention’, enabling you to identify SQL suffering from these waits.
Sign in with LinkedIn or Google to take part.
