Wait Classes – How Do You Use Them to Find the Root Cause?

“As per documents In general, the addition of wait classes helps direct the DBA more quickly toward the root cause of performance problems.

How could i trace the root cause of performence problems if it is related to wait class?”

—–

This is the answer that I provided:

I am not completely sure that I understand your question. The wait class gives you an approximate idea of where the performance problem will be found. You must then further investigate the wait events in that wait class. There are of course potential problems with starting at the wait class (some wait classes have 2 wait events, while others have many – that could throw off the search for the problem that is impacting performance the most), but at least it provides a starting point. To give you an idea of the wait events in each wait class, here is a SQL statement that was executed on Oracle Database 11.1.0.7:

So, if the User I/O wait class floats to the top of the wait classes between a known start time and end time, and the Commit wait class is at the bottom of the wait classes when comparing accumulated time, it probably would not make much sense to spend time investigating the wait events in the Commit class… until you realize that there is a single event in the Commit wait class that typically contributes wait time, while there are many in the User I/O wait class.

—-

It appears that my answer either missed the target or was ignored by the original poster. It appeared that the OP was attempting to immediately jump from step 1 to the last step, without considering any of the intermediate steps.

How would you answer the above question? Would you read the question differently and start writing about Statspack, AWR, ADDM, 10046 traces, or process state dumps? Would you lead the OP step by step through the process? Or would you take an entirely different approach?

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:
<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: