scripts for query proportion of the on-idle wait time is taken up by these wait classes

 As of Oracle 10g, wait events are assigned to specific wait classes such as "User I/O" or "Concurrency". You can use the following command to determine what proportion of the entire non-idle wait time is taken up by these wait classes:

example:

SQL> SELECT   2    SUBSTR(WAIT_CLASS, 1, 30) WAIT_CLASS,   3    ROUND(TIME_WAITED/100) "TIME_WAITED (S)",   4    ROUND(RATIO_TO_REPORT(TIME_WAITED) OVER () * 100) PERCENT   5  FROM   6    (SELECT WAIT_CLASS, SUM(TIME_WAITED) TIME_WAITED   7    FROM V$SYSTEM_EVENT   8    WHERE WAIT_CLASS NOT IN ('Idle', 'System I/O')   9     GROUP BY WAIT_CLASS)  10  ORDER BY 2 DESC; WAIT_CLASS                                                                                                               TIME_WAITED (S)    PERCENT ------------------------------------------------------------------------------------------------------- --------------------- ------------------ User I/O                                                                                                                            253463                   59 Commit                                                                                                                             104239                   24 Concurrency                                                                                                                     43807                     10 Other                                                                                                                                 14665                     3 Application                                                                                                                       8776                       2 Configuration                                                                                                                   787                          0 Network                                                                                                                            594                         0 Scheduler                                                                                                                          84                           0 Administrative                                                                                                                  19                           0 9 rows selected.