I’ve been working on shell code to mine the log writer trace. I wrote about that two weeks ago. Now I have a more advanced piece of shell code that mines all log writer traces of all instances. It makes a CSV file with the following data in columns: date, response time, instance name, hostname, size (I don’t do much with the size at this moment, but you never know):
for lgwrtrc in `ls -1 /u01/app/oracle/diag/rdbms/*/*/trace/*lgwr*.trc` do #echo $lgwrtrc ORASID=`echo $lgwrtrc| cut -d '/' -f 8` #echo $ORASID grep "Warning: log write elapsed time" $lgwrtrc -B1| sed 's/\r$//' |grep -v "\-\-" | sed 's/\*\*\* //'| tail -n +3| sed "s/Warning: log write elapsed time/, /" | sed "s/ms/ ,$ORASID ,$HOSTNAME /" | sed 's/size//' | sed 's/KB//' | xargs -L 2|cut -c -19,24- done > /orabackup2/performance/lgwr_$HOSTNAME.csv
In no time (unless you really, really have some serious log writer issues) you will get a result that looks something like this:
2013-10-22 23:00:24 , 807 ,ORCL14 ,dbserver1.domain.com , 5 2013-10-23 23:00:23 , 1005 ,ORCL14 ,dbserver1.domain.com , 1 2013-10-24 23:00:16 , 1477 ,ORCL14 ,dbserver1.domain.com , 624 2013-10-24 23:00:17 , 630 ,ORCL22 ,dbserver1.domain.com , 47 2013-10-25 23:00:26 , 603 ,ORCL22 ,dbserver1.domain.com , 0 2013-10-28 23:00:26 , 1330 ,ORCL31 ,dbserver1.domain.com , 1 2013-10-29 23:00:27 , 1867 ,ORCL31 ,dbserver1.domain.com , 2 2013-10-30 12:06:15 , 5584 ,ORCL31 ,dbserver1.domain.com , 0
You can pump these CSV files in a new Excel sheet by going to the Data ribbon, and choose From Text. Then pick your CSV file and start dividing the data into columns (unfortunately I have a Dutch version of Excel, so I have no idea how these commands actually are named).
With the data from all nodes of several clusters I could see that some log writer issues happened at the same time on different clusters. And some log writer waits seemed to happen every day at the same time. Very useful to know.