latch_stats_11g.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
| /* This file is part of demos for "Latch Mutex and beyond blog" Andrey S. Nikolaev (Andrey.Nikolaev@rdtex.ru) Compute the latch statistics For Oracle versions 11g: @latch_stats_11g ADDRESS*/set verify offselect name from v$latch where addr='&1'union allselect name from v$latch_children where addr='&1';SET SERVEROUTPUT ONset timing onDECLARE i number; Samples number:= 300; SampleFreq number:= 1 / 10; -- Hz; Nw NUMBER; laddr raw(8); cursor lstat(laddr_ raw) is /* latch statistics */ select
kslltnum LATCH#,kslltwgt GETS, kslltwff MISSES,kslltwsl
SLEEPS,ksllthst0 SPIN_GETS,kslltwtt latch_wait_time, kslltcnm child# from x$kslltr_children where kslltaddr=hextoraw(laddr_) union all select kslltnum LATCH#,kslltwgt GETS,kslltwff MISSES,kslltwsl SLEEPS,ksllthst0 SPIN_GETS,kslltwtt latch_wait_time, 0 child# from x$kslltr_parent where kslltaddr=hextoraw(laddr_); Lstat1 lstat%ROWTYPE; Lstat2 lstat%ROWTYPE; dgets number; dmisses number; rho number; eta number; lambda number; kappa number; W number; sigma number; error_ varchar2(100):=''; lname varchar2(100); level_ number; dtime number; U number :=0; ssleeps number; S number; params varchar2(2000):='';BEGIN laddr := HEXTORAW ('&1');/* CPU count */ select value into Nw from v$parameter where name = 'cpu_count'; if Nw != 1 then eta:= Nw/(Nw-1); else eta:=1; Error_ := Error_||' Single CPU configuration '; end if; Nw := 0;/* Beginning latch statistics */ dtime := DBMS_UTILITY.GET_TIME(); OPEN Lstat(laddr); FETCH Lstat into Lstat1; if Lstat%NOTFOUND then raise_application_error(-20001,'No latch at 0x'||laddr); end if; CLOSE Lstat;/* Sampling */ FOR i IN 1 .. Samples LOOP /* number of pocesses waiting for the latch */ for Sample in (SELECT count(decode(ksllawat,'00',null,1)) wat FROM x$ksupr WHERE ksllawat=laddr) LOOP Nw := Nw + Sample.wat; END LOOP; /* Is latch busy */ for Hold in (select 1 hold from x$ksuprlat where ksuprlat=laddr) loop U:=U+1; exit; end loop; DBMS_LOCK.sleep (SampleFreq); END LOOP;/* End latch statistics */ OPEN Lstat(laddr); FETCH Lstat into Lstat2; CLOSE Lstat; dtime:=(DBMS_UTILITY.GET_TIME()-dtime)*0.01; /* delta time in seconds *//* Compute derived statistics */ dgets := (lstat2.gets-lstat1.gets); dmisses:= (lstat2.misses-lstat1.misses); if(dgets>0)then rho := dmisses/dgets; else raise_application_error(-20002,'No gets activity for this latch'); end if; Nw:=Nw/Samples; U:=U/Samples; lambda:=dgets/dtime; W:= (lstat2.latch_wait_time-lstat1.latch_wait_time)/dtime*1.E-6; /* wait time in seconds */ select kslldnam,kslldlvl into lname,level_ from x$kslld where indx=lstat2.latch#; /* S:=eta*rho/lambda; */ S:=U/lambda; if(dmisses>0) then kappa:=(lstat2.sleeps-lstat1.sleeps)/dmisses; sigma:=(lstat2.spin_gets-lstat1.spin_gets)/dmisses; else error_ := Error_||' Delta MISSES='||dmisses; kappa:=null; sigma:=null; end if; if(kappa>0) then ssleeps:=(kappa+sigma-1)/kappa; else error_ := Error_||' Sigma='||sigma; ssleeps:=null; end if; if(length(Error_)>0 ) then DBMS_OUTPUT.put_LINE (' Error: '||error_); end if; DBMS_OUTPUT.put_LINE
(chr(10)||'Latch statistics for 0x'||laddr||' "'||lname||'"
level#='||level_||' child#='||lstat2.child#); DBMS_OUTPUT.put_LINE ('Requests rate: lambda=' || to_char(lambda,'999999.9')||' Hz'); DBMS_OUTPUT.put_LINE ('Miss /get: rho=' || to_char(rho,'9.999999')); DBMS_OUTPUT.put_LINE ('Est. Utilization: eta*rho=' || to_char(eta*rho,'9.999999')); DBMS_OUTPUT.put_LINE ('Sampled Utilization: U='||to_char(U,'9.999999')); DBMS_OUTPUT.put_LINE ('Slps /Miss: kappa=' || to_char(kappa,'9.999999')); DBMS_OUTPUT.put_LINE ('Wait_time/sec: W=' || to_char(W,'999.999999')); DBMS_OUTPUT.put_LINE ('Sampled queue length L=' || to_char(Nw,'999.999999')); DBMS_OUTPUT.put_LINE ('Spin_gets/miss: sigma=' || to_char(sigma,'9.999999')); DBMS_OUTPUT.put_LINE (chr(10)||'Derived statistics:'); DBMS_OUTPUT.put_LINE ('Secondary sleeps ratio =' || to_char(ssleeps,'9.99EEEE')); DBMS_OUTPUT.put_LINE ('Avg latch holding time =' || to_char(S*1000000,'999999.9')||' us'); DBMS_OUTPUT.put_LINE ('. sleeping time =' || to_char(W/lambda*1000000,'999999.9')||' us'); DBMS_OUTPUT.put_LINE ('. avg latch free wait =' || to_char(W/(kappa*rho*lambda)*1000000,'999999.9')||' us'); DBMS_OUTPUT.put_LINE ('. miss rate=' || to_char(rho*lambda,'999999.9')||' Hz'); DBMS_OUTPUT.put_LINE ('. waits rate =' || to_char(kappa*rho*lambda,'999999.9')||' Hz'); DBMS_OUTPUT.put_LINE ('. spin inefficiency k=' || to_char(kappa/(1+kappa*rho),'9.999999')); /* latch parameters */ for Param in (select ksppinm,ksppstvl from x$ksppi x join x$ksppcv using (indx ) where ksppinm like '\_latch\_class%' ESCAPE '\' or ksppinm in ('_spin_count','_enable_reliable_latch_waits','_latch_miss_stat_sid','_ultrafast_latch_statistics') order by ksppinm) loop params:=params||Param.ksppinm||'='||Param.ksppstvl||' '; end loop; DBMS_OUTPUT.put_LINE (chr(10) ||'Latch related parameters:'||chr(10) || params);EXCEPTION when others then DBMS_OUTPUT.put_LINE (chr(10)||'Error raised:'||SQLERRM); DBMS_OUTPUT.put_LINE (DBMS_UTILITY.FORMAT_CALL_STACK); DBMS_OUTPUT.put_LINE ('----- '||chr(10)||'LADDR= 0x'||rawtohex(laddr)||' dtime='||dtime||' Nw= '||Nw||' U='||U); DBMS_OUTPUT.put_LINE
('gets='||lstat2.gets||'-'||lstat1.gets||'='||dgets||'
misses='||lstat2.misses||'-'||lstat1.misses||'='||dmisses); DBMS_OUTPUT.put_LINE ('sleeps='||lstat2.sleeps||'-'||lstat1.sleeps||'='||(lstat2.sleeps-lstat1.sleeps)|| ' spin_gets='||lstat2.spin_gets||'-'||lstat1.spin_gets||'='||(lstat2.spin_gets-lstat1.spin_gets)); DBMS_OUTPUT.put_LINE ('rho='||rho||' lambda='||lambda||' kappa= '||kappa||' sigma='||sigma);END;/ |
No comments:
Post a Comment