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
