check_wait_class.sql

Set verify off
set feed off
set echo off
undefine CLASS

/*
V$SYSMETRIC – last 15 and 60 seconds
V$SYSMETRIC_SUMMARY – values  last hour (last snapshot)  like avg, max, min etc
V$SYSMETRIC_HISTORY – last hour for 1 minute, last 3 mintes for 15 second deltas
DBA_HIST_SYSMETRIC_SUMMARY – hour summaries for last week.
V$SYSTEM_WAIT_CLASS – cumulative since start up
V$WAITCLASSMETRIC – last 60 seconds deltas
V$WAITCLASSMETRIC_HISTORY – 60 seconds deltas for last hour

The relationships between the statistics:

1) background elapsed time
    2) background cpu time
          3) RMAN cpu time (backup/restore)
1) DB time
    2) DB CPU
    2) connection management call elapsed time
    2) sequence load elapsed time
    2) sql execute elapsed time
    2) parse time elapsed
          3) hard parse elapsed time
                4) hard parse (sharing criteria) elapsed time
                    5) hard parse (bind mismatch) elapsed time
          3) failed parse elapsed time
                4) failed parse (out of shared memory) elapsed time
    2) PL/SQL execution elapsed time
    2) inbound PL/SQL rpc elapsed time
    2) PL/SQL compilation elapsed time
    2) Java execution elapsed time
    2) repeated bind elapsed time
Children do not necessarily add up to the parent.

Children are not necessarily exclusive (that is, it is possible that they overlap).

The union of children does not necessarily cover the whole of the parent.
WAIT_CLASS                WAIT_CLASS_ID WAIT_CLASS#
------------------------- ------------- -----------
Other                        1893977003           0
Application                  4217450380           1
Configuration                3290255840           2
Concurrency                  3875070507           4
Commit                       3386400367           5
Idle                         2723168908           6
Network                      2000153315           7
User I/O                     1740759767           8
System I/O                   4108307767           9

*/
REM DB TIME vs CPU Time last hour
col C1 heading " WAIT_CLASS| EVENT" for a40

select   s.Begin_time ,s.End_time,s.METRIC_NAME,round(MINVAL,2) MINIMUM,round(MAXVAL,2) MAXIMUM,round(AVERAGE,2) AVERAGE,round(VALUE,2) "CURRENT",s.METRIC_UNIT
from    SYS.V_$SYSMETRIC_SUMMARY s JOIN SYS.V_$SYSMETRIC m on (s.METRIC_NAME=m.METRIC_NAME)
where   s.METRIC_NAME IN ('Database CPU Time Ratio','Database Wait Time Ratio') AND
        m.INTSIZE_CSEC = (select max(INTSIZE_CSEC) from SYS.V_$SYSMETRIC)
/

prompt===================================================================================================

prompt
prompt-Administrative/: Waits caused by administrative commands, such as rebuilding an index, for example.
prompt-Application/: Waits due to the application code.
prompt-Commit/: Waits caused by commits in the database(log file sync event).
prompt-Concurrency/: Waits for resources used for locking; for example, latches.
prompt-Configuration/: Waits caused by database or instance configuration problems(example: low shared-pool memory size).
prompt-Cluster/: Waits related to RAC Cluster management.
prompt-Idle/: Waits when a session isn’t active;(i.e ‘SQL*Net message from client’ wait event.

col WAIT_CLASS for a25
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,
        BEGIN_TIME,END_TIME
from
(select
        b.WAIT_CLASS,
        SUM(a.WAIT_COUNT) TOTAL_WAITS ,
        SUM(a.TIME_WAITED)TIME_WAITED,
        to_char(trunc(min(a.begin_time),'MI'),'DD-MON-YY HH24:MI') BEGIN_TIME,to_char(trunc(max(a.end_time),'MI'),'DD-MON-YY HH24:MI') END_TIME
from    v$waitclassmetric_history a,V$SYSTEM_WAIT_CLASS b
where   WAIT_CLASS != 'Idle'
and a.wait_class# = b.wait_class#
GROUP BY b.wait_class),
(select  sum(a.WAIT_COUNT) SUM_WAITS,
        sum(a.TIME_WAITED) SUM_TIME
from    v$waitclassmetric_history a,V$SYSTEM_WAIT_CLASS b
where   b.WAIT_CLASS != 'Idle'
and a.wait_class# = b.wait_class#  )
order by 5 desc;

column wait_type format a35 heading "Wait Type"
column lock_name format a12
column waits_1000 format  99,999,999 heading "Waits|\1000"
column time_waited_hours format 99,999.99 heading "Time|Hours"
column pct format 99.99 Heading "Pct"
column avg_wait_ms format 9,999.99 heading "Avg Wait|Ms"
set pagesize 10000
set lines 150

WITH system_event AS
    (SELECT CASE
              WHEN wait_class IN ('User I/O', 'System I/O')
              THEN event ELSE wait_class
             END  wait_type, e.*
        FROM v$system_event e)
SELECT wait_type, SUM(total_waits) / 1000 waits_1000,
       ROUND(SUM(time_waited_micro) / 1000000 / 3600, 2)
             time_waited_hours,
       ROUND(SUM(time_waited_micro) / SUM(total_waits) / 1000, 2)
             avg_wait_ms,
       ROUND(  SUM(time_waited_micro)
             * 100
             / SUM(SUM(time_waited_micro)) OVER (), 2)
          pct
FROM (SELECT wait_type, event, total_waits, time_waited_micro
      FROM system_event e
      UNION
      SELECT 'CPU', stat_name, NULL, VALUE
      FROM v$sys_time_model
      WHERE stat_name IN ('background cpu time', 'DB CPU')) l
WHERE wait_type <> 'Idle'
GROUP BY wait_type
HAVING ROUND(SUM(time_waited_micro) / 1000000, 2) >0
ORDER BY SUM(time_waited_micro) DESC,wait_type
/

Prompt select WAIT_CLASS: Other ,User I/O ,Concurrency ,System I/O,Commit,Configuration,Application,Administrative

Select  '==> PARENT WAIT_CLASS STAT: '||WAIT_CLASS c1,
        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 = '&&CLASS'),
(select  sum(TOTAL_WAITS) SUM_WAITS,
        sum(TIME_WAITED) SUM_TIME
from    V$SYSTEM_WAIT_CLASS where   WAIT_CLASS!= 'Idle')
union
Select EVENT,
       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 EVENT,
        TOTAL_WAITS,
        TIME_WAITED
from    V$SYSTEM_EVENT where   WAIT_CLASS = '&&CLASS'),
(select  sum(TOTAL_WAITS) SUM_WAITS,
        sum(TIME_WAITED) SUM_TIME
from    V$SYSTEM_EVENT where   WAIT_CLASS= '&&CLASS')
WHERE   ROUND((TIME_WAITED / 100),2)>0
order by 1 desc,5 desc
/