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 🙂
1 | /(db file scattered read)|(db file sequential read)|(direct path read)/ { |
2 | event_part=substr($0,index($0,"'")+1); |
3 | event_name=substr(event_part,1,index(event_part,"'")-1); |
4 | event_time1=substr($0,index($0,"ela= ")+5); |
5 | event_time=substr(event_time1,1,index(event_time1,"file")-1); |
6 | events[event_name]+=event_time; |
7 | event_cnt[event_name]++; |
8 |
9 | event_time_m=event_time/1000; |
10 | if(event_time_m<=1) |
11 | { |
12 | event_hist[event_name,"<= 1"]++; |
13 | } |
14 | else if(event_time_m>1 && event_time_m<=2) |
15 | { |
16 | event_hist[event_name,"<= 2"]++; |
17 | } |
18 | else if(event_time_m>2 && event_time_m<=4) |
19 | { |
20 | event_hist[event_name,"<= 4"]++; |
21 | } |
22 | else if(event_time_m>4 && event_time_m<=8) |
23 | { |
24 | event_hist[event_name,"<= 8"]++; |
25 | } |
26 | else if(event_time_m>8 && event_time_m<=16) |
27 | { |
28 | event_hist[event_name,"<=16"]++; |
29 | } |
30 | else if(event_time_m>16 && event_time_m<=32) |
31 | { |
32 | event_hist[event_name,"<=32"]++; |
33 | } |
34 | else if(event_time_m>32) |
35 | { |
36 | event_hist[event_name,">32"]++; |
37 | } |
38 | } |
39 | END { |
40 | br=0; |
41 | n=asorti(event_hist,event_hist_s); |
42 | for(i=1 ; i<=n ; i++ ) { |
43 | ev=substr(event_hist_s[i],1,index(event_hist_s[i],"<")-2); |
44 | if(event_cnt[ev]<1) |
45 | { |
46 | ev=substr(event_hist_s[i],1,index(event_hist_s[i],">")-2); |
47 | br=1; |
48 | } |
49 | print event_hist_s[i] "(ms)\t\t" event_hist[event_hist_s[i]] "\t" event_hist[event_hist_s[i]]/event_cnt[ev]; |
50 | if(br==1) |
51 | { |
52 | print " "; |
53 | br=0; |
54 | } |
55 |
56 | } |
57 |
58 | for(event in events) { |
59 | print event " elapsed time (us): " events[event]; |
60 | } |
61 |
62 | } |
The sample output looks like this:
1 | ora-600:oracle inter$ awk -f event_hist.awk mymicr1_ora_7084_dtp.trc |
2 | db file scattered read<= 1(ms) 907 0.0182411 |
3 | db file scattered read<= 2(ms) 3076 0.0618627 |
4 | db file scattered read<= 4(ms) 13434 0.270177 |
5 | db file scattered read<= 8(ms) 7482 0.150474 |
6 | db file scattered read<=16(ms) 21428 0.430947 |
7 | db file scattered read<=32(ms) 2978 0.0598918 |
8 | db file scattered read>32(ms) 418 0.00840657 |
9 |
10 | db file sequential read<= 1(ms) 2963 0.355916 |
11 | db file sequential read<= 2(ms) 632 0.0759159 |
12 | db file sequential read<= 4(ms) 903 0.108468 |
13 | db file sequential read<= 8(ms) 2128 0.255616 |
14 | db file sequential read<=16(ms) 1366 0.164084 |
15 | db file sequential read<=32(ms) 280 0.0336336 |
16 | db file sequential read>32(ms) 53 0.00636637 |
17 |
18 | direct path read temp<= 1(ms) 4 0.00202634 |
19 | direct path read temp<= 2(ms) 2 0.00101317 |
20 | direct path read temp<= 4(ms) 3 0.00151976 |
21 | direct path read temp<= 8(ms) 397 0.201114 |
22 | direct path read temp<=16(ms) 1234 0.625127 |
23 | direct path read temp<=32(ms) 290 0.14691 |
24 | direct path read temp>32(ms) 44 0.0222898 |
25 |
26 | db file scattered read elapsed time (us): 417265927 |
27 | db file sequential read elapsed time (us): 41364490 |
28 | direct path read temp elapsed time (us): 24084309 |
Enjoy 😉