Advertisement

Wednesday, April 19, 2017

Oracle Database - Current Wait Events / Metrics Statistics

 

In this blog I am going to discuss on how to find out current statistics in the database.
There are quite a few events and metrics available.
I will be discussing on how to find out those from the below available views. 

In the next blog I have discussed how to extract data from AWR tables.

 Oracle Database - Historical Events / Wait Times / Statistics - AWR Data Mining & Extraction
  
 V$EVENTMETRIC
    This view is used to find out latencies / values of various database events as of current time in the system, these are wait event deltas for past 60 seconds.  These events are wait events in the database example - log file sync or db file parallel write.
This is very useful as it helps you find out what are the latencies related to each wait event currently. 

V$SYSTEM_EVENT
  This view is values cumulative since startup of the system events and their latencies. 
Basically V$Eventmetric provides values for past 60 seconds and those values from startup are in V$system_event view


V$EVENT_HISTOGRAM
  This view is really helpful, as it provides values in a histogram fashion, i.e. how are the waits distributed in time windows. how many waits under 1ms, how many under 2ms and so on.
 

V$SYSMETRIC
   This view is used to find out current deltas of various other statistical metrics which oracle provides
For example - redo generated per second or Average Active Sessions

V$SYSMETRIC_HISTORY
  This view is super set of the above  view, it provides values for each  minute in past hour. 
you can think of it like retaining values from V$Sysmetric every minute for one hour.
This can help you find out history of a metric for past one hour, which can be really powerful in performance scenarios. 


V$SYSMETRIC_SUMMARY
  This view is similar to History view, however it provided last hours min, max and average values for the same metrics in sysmetric view.
 

V$WAITCLASSMETRIC
  This view is helpful in finding wait events on per class basis rather than individual events
for example - User I/O class has all db file scattered read, sequential read, read by other session events. These are deltas for past 60 seconds

 
V$WAITCLASSMETRIC_HISTORY
 This view provides deltas for 60 seconds for past one hour, again basically a superset of Waitclassmetric
  
V$SYSTEM_WAIT_CLASS
 This view is cumulative values since startup of waitclass wait events. 



V$FILEMETRIC
    This view is helpful in taking out file level statistics. It gives current latency in read / write for past 60 seconds on per file basis.  

 V$FILEMETRIC_HISTORY
 This view  provides file level statistics and extension to filemetric view for 10 minute interval for past one hour 

 
 V$FILESTAT
 This view provides read/write latency cumulative from instance startup at file level. 


Below are examples for usage of above views-

Find out current event statistics

set lines 500 pages 500
col event_name for a30

select evm.BEGIN_TIME, evm.END_TIME,
       evnt.name EVENT_NAME ,
       round(evm.time_waited,3) time_waited,
       evm.wait_count,
       round(10*evm.time_waited/nullif(evm.wait_count,0),3) AVG_MS  -- Multiply by 10 to convert centiseconds to milliseconds
from v$eventmetric evm,
     v$event_name evnt
where evm.event_id=evnt.event_id
  and evnt.name in ('log file sync',
                  'log file parallel write','direct path read',
                  'direct path read temp',
                  'direct path write',
                  'direct path write temp',
                  'log file single write',
                  'db file sequential read',
                  'db file scattered read',
                  'control file sequential read')
                  order by EVENT_NAME;

 


Find out event statistics cumulative from startup

SQL > set lines 500 pages 500
SQL >  col EVENT_NAME for a30
SQL >  r
    select EVENT AS  EVENT_NAME, AVERAGE_WAIT*10 AVERAGE_WAIT,AVERAGE_WAIT_FG*10 AVERAGE_WAIT_FG
    FROM
     V$SYSTEM_EVENT
    where event in ('log file sync',
                      'log file parallel write','direct path read',
                      'direct path read temp',
                      'direct path write',
                      'direct path write temp',
                      'log file single write',
                     'db file sequential read',
                     'db file scattered read',
                     'control file sequential read')
                    order by EVENT




Find out event statistics histogram for log file sync



 set numwidth 15
col EVENT for a30

SELECT
 EVENT, WAIT_TIME_MILLI, WAIT_COUNT
FROM V$EVENT_HISTOGRAM
WHERE EVENT='&event'
ORDER BY  WAIT_TIME_MILLI;

Enter value for event: log file sync
old   4: WHERE EVENT='&event'
new   4: WHERE EVENT='log file sync'

EVENT           WAIT_TIME_MILLI      WAIT_COUNT
--------------- --------------- ---------------
log file sync                 1       334695742
log file sync                 2      1068423223
log file sync                 4       853888410
log file sync                 8       303521739
log file sync                16       114332733
log file sync                32        96808378
log file sync                64        40842144
log file sync               128         6424359
log file sync               256          798555
log file sync               512          198027
log file sync              1024           49883
log file sync              2048           20747
log file sync              4096            2602
log file sync              8192             417
log file sync             16384              12
log file sync             32768              12
log file sync             65536             251




Find out various database metrics stats
                
set lines 500 pages 500
         
select BEGIN_TIME, END_TIME, METRIC_NAME, round(value,2) VALUE , METRIC_UNIT
from v$sysmetric
where metric_name in
('Average Synchronous Single-Block Read Latency',
'I/O Megabytes per Second','Redo Generated Per Sec',
'Physical Read Total Bytes Per Sec',
'Physical Read Bytes Per Sec',
'Physical Write Total Bytes Per Sec',
'Physical Write Bytes Per Sec',
'Average Active Sessions')
order by METRIC_NAME;



Find out Average Single Block Read Latency for Past One hour -

select BEGIN_TIME, END_TIME, METRIC_NAME, round(value,2) VALUE , METRIC_UNIT
from v$sysmetric_history
where metric_name in
('Average Synchronous Single-Block Read Latency')
order by begin_time;



Find out MIN/MAX/AVG Metric Values for Past one Hour

set lines 500 pages 500
col BEGIN_INTERVAL_TIME for a30
col METRIC_NAME for a50
col METRIC_UNIT for a30

select BEGIN_TIME, END_TIME, METRIC_NAME, round(MINVAL,2) MIN_VAL, round(MAXVAL,2) MAX_VAL , round(AVERAGE,2) AVG_VAL, METRIC_UNIT
from v$sysmetric_summary
where metric_name in
('Average Synchronous Single-Block Read Latency',
'I/O Megabytes per Second','Redo Generated Per Sec',
'Physical Read Total Bytes Per Sec',
'Physical Read Bytes Per Sec',
'Physical Write Total Bytes Per Sec',
'Physical Write Bytes Per Sec',
'Average Active Sessions')
order by METRIC_NAME;


 


Find out Latency in wait class User I/o


SQL > set lines 500 pages 500SQL > col WAIT_CLASS for a15SQL > r
   select
           BEGIN_TIME, END_TIME, SWC.WAIT_CLASS, round(10*wcm.time_waited/nullif(wcm.wait_count,0),3) AVG_MS -- convert centisecs to millisecs
    from   v$waitclassmetric  wcm,
           v$system_wait_class swc
    where wcm.wait_class_id=swc.wait_class_id
     and swc.wait_class='User I/O'

BEGIN_TIME         END_TIME           WAIT_CLASS          AVG_MS
------------------ ------------------ --------------- ----------
19-APR-17 05:54:16 19-APR-17 05:55:16 User I/O              .496




Find out Latency in wait class User I/o for past one hour -

SQL > set lines 500 pages 500SQL > col WAIT_CLASS for a15SQL > r
    SELECT
            BEGIN_TIME, END_TIME, SWC.WAIT_CLASS, round(10*wcm.time_waited/nullif(wcm.wait_count,0),3) AVG_MS -- convert centisecs to millisecs
    FROM   V$WAITCLASSMETRIC_HISTORY  wcm,
           v$system_wait_class swc
    WHERE wcm.wait_class_id=swc.wait_class_id
     AND swc.wait_class='User I/O'

BEGIN_TIME         END_TIME           WAIT_CLASS          AVG_MS
------------------ ------------------ --------------- ----------
19-APR-17 05:55:16 19-APR-17 05:56:15 User I/O              .652
19-APR-17 05:54:16 19-APR-17 05:55:16 User I/O              .496
19-APR-17 05:53:16 19-APR-17 05:54:16 User I/O              .554
19-APR-17 05:52:15 19-APR-17 05:53:16 User I/O              .529
19-APR-17 05:51:16 19-APR-17 05:52:15 User I/O              .583
19-APR-17 05:50:16 19-APR-17 05:51:16 User I/O              .868
19-APR-17 05:49:15 19-APR-17 05:50:16 User I/O              .965
19-APR-17 05:48:15 19-APR-17 05:49:15 User I/O             1.022
19-APR-17 05:47:16 19-APR-17 05:48:15 User I/O              .728
19-APR-17 05:46:16 19-APR-17 05:47:16 User I/O              .651
19-APR-17 05:45:15 19-APR-17 05:46:16 User I/O              .581
19-APR-17 05:44:15 19-APR-17 05:45:15 User I/O              .649
19-APR-17 05:43:16 19-APR-17 05:44:15 User I/O              .658
19-APR-17 05:42:16 19-APR-17 05:43:16 User I/O              .637



Find out Latency in wait class User I/o cumulative from startup - 

  1  SELECT
  2          WAIT_CLASS, time_waited*10/TOTAL_WAITS AVG_MS    3          FROM
  4         V$SYSTEM_WAIT_CLASS
  5* WHERE WAIT_CLASS='User I/O'

WAIT_CLASS          AVG_MS
--------------- ----------
User I/O        1.00084214




Find out  current read / write latency at file level

set lines 500 pages 500
SELECT
FILE_ID, BEGIN_TIME, END_TIME, AVERAGE_READ_TIME * 10 AVG_RD_MS , AVERAGE_WRITE_TIME * 10 AVG_WRT_MS, INTSIZE_CSEC
FROM V$FILEMETRIC
WHERE FILE_ID IN (1,2,3)
ORDER BY FILE_ID;  


   FILE_ID BEGIN_TIME           END_TIME              AVG_RD_MS AVG_WRT_MS INTSIZE_CSEC
---------- -------------------- -------------------- ---------- ---------- ------------
         1 23-APR-2017 10:07:41 23-APR-2017 10:17:41 .481927711 3.88286334        60026
         2 23-APR-2017 10:07:41 23-APR-2017 10:17:41 .354154214 1.73882584        60026
         3 23-APR-2017 10:07:41 23-APR-2017 10:17:41 .561422736 6.98739217        60026
 


Find out  current read / write latency at file level for past one hour

SELECT
FILE_ID, BEGIN_TIME, END_TIME, INTSIZE_CSEC, AVERAGE_READ_TIME * 10 AVG_RD_MS , AVERAGE_WRITE_TIME * 10 AVG_WRT_MS
FROM V$FILEMETRIC_HISTORY
WHERE FILE_ID IN (1,2,3)
ORDER BY FILE_ID, BEGIN_TIME;
  

   FILE_ID BEGIN_TIME           END_TIME             INTSIZE_CSEC  AVG_RD_MS AVG_WRT_MS
---------- -------------------- -------------------- ------------ ---------- ----------
         1 23-APR-2017 09:07:41 23-APR-2017 09:17:41        60009 .411153791 .476190476
         1 23-APR-2017 09:17:41 23-APR-2017 09:27:41        59974 .403685827 2.35294118
         1 23-APR-2017 09:27:41 23-APR-2017 09:37:40        59988 .388768898         .4
         1 23-APR-2017 09:37:40 23-APR-2017 09:47:40        60008 .409824361  2.4137931
         1 23-APR-2017 09:47:40 23-APR-2017 09:57:41        60025 .422555948          0
         1 23-APR-2017 09:57:41 23-APR-2017 10:07:41        59974 .439093484 2.33333333
         1 23-APR-2017 10:07:41 23-APR-2017 10:17:41        60026 .481927711 3.88286334
         2 23-APR-2017 09:07:41 23-APR-2017 09:17:41        60009 .348623853 1.59437129
         2 23-APR-2017 09:17:41 23-APR-2017 09:27:41        59974 .347034561 1.72499271
         2 23-APR-2017 09:27:41 23-APR-2017 09:37:40        59988 .352133255 1.65487745
         2 23-APR-2017 09:37:40 23-APR-2017 09:47:40        60008 .354619565 1.65991317
         2 23-APR-2017 09:47:40 23-APR-2017 09:57:41        60025 .350512946 1.60788234
         2 23-APR-2017 09:57:41 23-APR-2017 10:07:41        59974  .34965035 1.67443032
         2 23-APR-2017 10:07:41 23-APR-2017 10:17:41        60026 .354154214 1.73882584
         3 23-APR-2017 09:07:41 23-APR-2017 09:17:41        60009 .373831776 4.23076923
         3 23-APR-2017 09:17:41 23-APR-2017 09:27:41        59974 .362227143        4.2
         3 23-APR-2017 09:27:41 23-APR-2017 09:37:40        59988 .361088655 4.27272727
         3 23-APR-2017 09:37:40 23-APR-2017 09:47:40        60008 .373976202 5.70621469
         3 23-APR-2017 09:47:40 23-APR-2017 09:57:41        60025 .394984326  4.8255814
         3 23-APR-2017 09:57:41 23-APR-2017 10:07:41        59974 .394618834 4.31818182
         3 23-APR-2017 10:07:41 23-APR-2017 10:17:41        60026 .561422736 6.98739217




Find out  current read / write latency cumulative


SELECT FILE#,
 WRITETIM * 10 /PHYWRTS as AVG_WRT_MS ,READTIM * 10 /PHYRDS AS AVG_RD_MS
 FROM V$FILESTAT
 WHERE FILE# IN (1,2,3)
ORDER BY FILE#;  


     FILE# AVG_WRT_MS  AVG_RD_MS
---------- ---------- ----------
         1 1.54151474 .351090769
         2 1.23481134 .361346068
         3  3.2816945 .332541687

No comments:
Write comments