AWK to the rescue – wait event histogram from trace file


08.03.2016
by Kamil Stawiarski

I wrote a very simple script in AWK to create wait event histogram (for db file scattered read, db file sequential read, direct path read and direct path read temp) based on 10046 trace file with wait events.

Maybe someone will find it useful 🙂

 
/(db file scattered read)|(db file sequential read)|(direct path read)/ {
        event_part=substr($0,index($0,"'")+1);
        event_name=substr(event_part,1,index(event_part,"'")-1);
        event_time1=substr($0,index($0,"ela= ")+5);
        event_time=substr(event_time1,1,index(event_time1,"file")-1);
        events[event_name]+=event_time;
        event_cnt[event_name]++;

        event_time_m=event_time/1000;
        if(event_time_m<=1)
        {
                event_hist[event_name,"<= 1"]++;
        }
        else if(event_time_m>1 && event_time_m<=2)
        {
                event_hist[event_name,"<= 2"]++;
        }
        else if(event_time_m>2 && event_time_m<=4)
        {
                event_hist[event_name,"<= 4"]++;
        }
        else if(event_time_m>4 && event_time_m<=8)
        {
                event_hist[event_name,"<= 8"]++;
        }
        else if(event_time_m>8 && event_time_m<=16)
        {
                event_hist[event_name,"<=16"]++;
        }
        else if(event_time_m>16 && event_time_m<=32)
        {
                event_hist[event_name,"<=32"]++;
        }
        else if(event_time_m>32)
        {
                event_hist[event_name,">32"]++;
        }
}
END {
        br=0;
        n=asorti(event_hist,event_hist_s);
        for(i=1 ; i<=n ; i++ ) {
                ev=substr(event_hist_s[i],1,index(event_hist_s[i],"<")-2);
                if(event_cnt[ev]<1)
                {
                        ev=substr(event_hist_s[i],1,index(event_hist_s[i],">")-2);
                        br=1;
                }
                print event_hist_s[i] "(ms)\t\t" event_hist[event_hist_s[i]] "\t" event_hist[event_hist_s[i]]/event_cnt[ev];
                if(br==1)
                {
                        print " ";
                        br=0;
                }

        }

        for(event in events) {
                print event " elapsed time (us): " events[event];
        }

}

The sample output looks like this:

ora-600:oracle inter$ awk -f event_hist.awk mymicr1_ora_7084_dtp.trc
db file scattered read<= 1(ms)		907	0.0182411
db file scattered read<= 2(ms)		3076	0.0618627
db file scattered read<= 4(ms)		13434	0.270177
db file scattered read<= 8(ms)		7482	0.150474
db file scattered read<=16(ms)		21428	0.430947
db file scattered read<=32(ms)		2978	0.0598918
db file scattered read>32(ms)		418	0.00840657

db file sequential read<= 1(ms)		2963	0.355916
db file sequential read<= 2(ms)		632	0.0759159
db file sequential read<= 4(ms)		903	0.108468
db file sequential read<= 8(ms)		2128	0.255616
db file sequential read<=16(ms)		1366	0.164084
db file sequential read<=32(ms)		280	0.0336336
db file sequential read>32(ms)		53	0.00636637

direct path read temp<= 1(ms)		4	0.00202634
direct path read temp<= 2(ms)		2	0.00101317
direct path read temp<= 4(ms)		3	0.00151976
direct path read temp<= 8(ms)		397	0.201114
direct path read temp<=16(ms)		1234	0.625127
direct path read temp<=32(ms)		290	0.14691
direct path read temp>32(ms)		44	0.0222898

db file scattered read elapsed time (us): 417265927
db file sequential read elapsed time (us): 41364490
direct path read temp elapsed time (us): 24084309

Enjoy 😉

Search:


Kontakt

Database Whisperers sp. z o. o. sp. k.
Al. Jerozolimskie 200
Wejście B, III piętro/ pokój 342
02-486 Warszawa
NIP: 5272744987
REGON:362524978
+48 508 943 051
+48 661 966 009
info@ora-600.pl

Newsletter zapisz się żeby być na bieżąco