New View V$LGWRIO_OUTLIER In 12C

Recently I discovered the v$lgwrio_outlier, this view contains entries corresponding to the Log Writer process that have taken more than 500 ms. These entries are the same of trace LGWR log trace file. The advantage here is that you hava into a table the same information and not need to process this information.

This view is a starting point to detect slow disks, when it is on separete device, you can try optimize performance in the other hand if all files are in the same disk you have a problem.
In the next query I show a simple query to get the average, max an min latency (min in this case is 500 because of timeout)

                                 
DB12*ANTO> select max (io_latency), min (io_latency), avg (io_latency) from V$LGWRIO_OUTLIER;

MAX(IO_LATENCY) MIN(IO_LATENCY) AVG(IO_LATENCY)
_______________ _______________ _______________
           2094             500      685,002004

In the next example, the query return the files with more latency, there are three with high latency.

 

DB12*ANTO> select file_name, sum(io_latency)/1000 as "Latency Secs" from  V$LGWRIO_OUTLIER
  2   group by file_name order by 2 desc;

FILE_NAME                                               Latency Secs
_______________________________________________________ ____________
/ORACLE/DB12/RDO2/FINANCIAL_PROD_LOG12.RDO                    78,909
/ORACLE/DB12/RDO1/FINANCIAL_PROD_LOG21.RDO                    74,851
/ORACLE/DB12/RDO1/FINANCIAL_PROD_LOG11.RDO                    72,194
/ORACLE/DB12/RDO2/FINANCIAL_PROD_LOG22.RDO                    63,079
/ORACLE/DB12/RDO1/FINANCIAL_PROD_LOG31.RDO                    44,353
/ORACLE/DB12/RDO2/FINANCIAL_PROD_LOG32.RDO                      8,43

The only problem I have seen is that it need a timestamp or scn to get a time referential.

HTH – Antonio NAVARRO

 

Advertisements