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 😉