Advanced mining the log writer trace

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.

Advertisements

About Marcel-Jan Krijgsman

Ever since I started working with Oracle, I had an interest in Oracle database performance tuning. This led, eventually, to a four day training I made and gave for customers of Transfer Solutions. Since 2012 I work for Rabobank Nederland. A few years ago I also became interested in Oracle database security. All technology aside, it is my experience that security usually plays out on a political level. I'm a Oracle certified professional for the 8i, 9i, 10g and 11g databases and Oracle Database 11g Performance Tuning Certified Expert.
This entry was posted in Oracle performance tuning, Oracle scripts. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s