-
Notifications
You must be signed in to change notification settings - Fork 9
/
mystats.sql
513 lines (463 loc) · 19.2 KB
/
mystats.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
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
-- ----------------------------------------------------------------------------------------------
--
-- Utility: MyStats
--
-- Script: mystats.sql
--
-- Version: 3.0
--
-- Author: Adrian Billington
-- www.oracle-developer.net
-- (c) oracle-developer.net
--
-- Description: A free-standing SQL*Plus script to output the resource consumption of a unit or
-- units of work as recorded in v$mystat, v$latch and v$sess_time_model.
-- Based on Jonathan Lewis's SNAP_MY_STATS package but as a standalone script (i.e.
-- no database objects need to be created).
--
-- Key Differences
-- ---------------
--
-- a) This is a SQL*Plus script that requires no objects to be created;
--
-- b) This includes latch statistics and makes use of Tom
-- Kyte's RUNSTATS method for distinguising between stats
-- and latches;
--
-- c) This includes advanced reporting options (see Usage and Examples
-- sections below for details);
--
-- d) This includes a session time model report;
--
-- e) This requires at least version 10.1 to run because it
-- makes use of collection methods such as MEMBER OF and
-- reports on V$SESS_TIME_MODEL statistics.
--
-- Configuration: Edit the c_ms_rmcmd variable in the Constants section at the start of this
-- script to use the correct file deletion command for your SQL*Plus client
-- platform. It is defaulted to a Windows "del" command, so you will need to
-- change it if you are using a Linux/Unix SQL*Plus client.
--
-- Reason: To make this run in standalone mode, a couple of temporary files
-- are written to your current directory. These files are automatically
-- removed on completion of this script.
--
-- Usage: @mystats start [optional statistics type(s)]
-- --<do some work>--
-- @mystats stop [optional reporting parameter]
--
-- Optional statistics types format:
--
-- Short Format Long Format Equivalent
-- ----------------------- ----------------------
-- s=[csv] stattypes=[csv]
--
-- Statistics types values for the csv list are as follows:
--
-- Statistics Type Short Format Long Format Equivalent
-- --------------- ------------ ----------------------
-- Statistic s statistic
-- Latch l latch
-- Time Model t time
-- All (default) a all
--
-- Optional reporting parameter formats:
--
-- Short Format Long Format Equivalent
-- ----------------------- ----------------------
-- t=<number> threshold=<number>
-- l=<string> like=<string>
-- n=<string list> names=<string list>
-- r=<regular expression> regexp=<regular expression>
--
-- Use double-quotes when the strings contain spaces,
-- e.g. "option=value with space"
--
-- Examples: 1. Output all statistics
-- -------------------------------------------------------------
-- @mystats start
-- --<do some work>--
-- @mystats stop
--
-- 2. Output statistics with delta values >= 1,000
-- -------------------------------------------------------------
-- @mystats start
-- --<do some work>--
-- @mystats stop t=1000
--
-- 3. Output statistics for "redo size" and "user commits" only
-- -------------------------------------------------------------
-- @mystats start
-- --<do some work>--
-- @mystats stop "n=redo size, user commits"
--
-- 4. Output statistics for those containing the word 'memory'
-- -------------------------------------------------------------
-- @mystats start
-- --<do some work>--
-- @mystats stop l=memory
--
-- 5. Output statistics for those with I/O, IO, i/o or io in the name
-- ------------------------------------------------------------------
-- @mystats start
-- --<do some work>--
-- @mystats stop r=I/?O\s
--
-- 6. Capture and output statistics and time model only
-- -------------------------------------------------------------
-- @mystats start s=s,t
-- --<do some work>--
-- @mystats stop
--
-- 7. Output statistics only for those containing 'parallel'
-- -------------------------------------------------------------
-- @mystats start s=s
-- --<do some work>--
-- @mystats stop l=parallel
--
-- Notes: 1. See http://www.jlcomp.demon.co.uk/snapshot.html for original
-- version.
--
-- 2. As described in Configuration above, this script writes and removes
-- a couple of temporary files during execution.
--
-- 3. A PL/SQL package version of MyStats (v3.0) is also available.
--
-- 4. Thanks to Martin Bach for the idea to provide a regexp reporting filter
-- option and some example code.
--
-- Disclaimer: http://www.oracle-developer.net/disclaimer.php
--
-- ----------------------------------------------------------------------------------------------
set define on autoprint off
set serveroutput on format wrapped
-- Constants...
-- -----------------------------------------------------------------------
define c_ms_version = 3.0
define c_ms_rmcmd = "del" --Windows
--define c_ms_rmcmd = "rm" --Unix/Linux
define c_ms_init = "_ms_init.sql"
define c_ms_clear = "_ms_teardown.sql"
-- Initialise default substitution variables 1 and 2...
-- -----------------------------------------------------------------------
set termout off
col 1 new_value 1
col 2 new_value 2
select null as "1"
, null as "2"
from dual
where 1=2;
-- Input parameters...
-- -----------------------------------------------------------------------
define p_ms_snap = &1
define p_ms_option = "&2"
-- Initialisation section...
-- -----------------------------------------------------------------------
column snap noprint new_value v_ms_snap
column if_start noprint new_value v_ms_if_start
column if_stop noprint new_value v_ms_if_stop
select snap
, decode(snap, 'start', '', '--') as if_start
, decode(snap, 'stop', '', '--') as if_stop
from (
select rtrim(lower('&p_ms_snap'),';') as snap
from dual
);
spool "&c_ms_init" replace
prompt var bv_ms_&v_ms_snap clob;
prompt var bv_ms_ela_&v_ms_snap number;
prompt var bv_ms_cpu_&v_ms_snap number;
spool off
@"&c_ms_init"
host &c_ms_rmcmd "&c_ms_init"
-- Parse the options...
-- -----------------------------------------------------------------------
column threshold noprint new_value v_ms_threshold
column namelist noprint new_value v_ms_name_list
column namelike noprint new_value v_ms_name_like
column regexplike noprint new_value v_ms_name_regexp
column stattypes noprint new_value v_ms_stattypes
column ms_option noprint new_value v_ms_option
column start_option noprint new_value v_ms_start_option
column stop_option noprint new_value v_ms_stop_option
select case
when o in ('threshold','t')
then 1
when o in ('names','n')
then 2
when o in ('like','l')
then 3
when o in ('regex','r')
then 4
when o in ('stattype','s')
then 5
else 0
end as ms_option
, case
when o in ('threshold','t')
then to_number(v)
else 0
end as threshold
, case
when o in ('names','n')
then '''' || regexp_replace(v, ' *, *', ''',''') || ''''
else 'null'
end as namelist
, case
when o in ('like','l')
then '''%' || v || '%'''
else 'null'
end as namelike
, case
when o in ('regexp','r')
then '''' || v || ''''
else 'null'
end as regexplike
&v_ms_if_start , case
&v_ms_if_start when o in ('stattype','s')
&v_ms_if_start then regexp_replace(v, ' *, *', ',')
&v_ms_if_start when o is null
&v_ms_if_start then 'all'
&v_ms_if_start end as stattypes
&v_ms_if_start , p as start_option
, p as stop_option
from (
select trim(regexp_substr(lower('&p_ms_option'), '[^=]+')) as o
, trim(regexp_substr('&p_ms_option', '[^=]+', 1, 2)) as v
, '&p_ms_option' as p
from dual
);
-- Stattypes include/exclude section...
-- -----------------------------------------------------------------------
column snap noprint
column include_stats noprint new_value v_ms_include_statistics
column include_latches noprint new_value v_ms_include_latches
column include_time noprint new_value v_ms_include_time_model
select snap
&v_ms_if_start , case when regexp_like('&v_ms_stattypes', '(^|,)(s|statistic|a|all)(,|$)') then 'Y' else 'N' end as include_stats
&v_ms_if_start , case when regexp_like('&v_ms_stattypes', '(^|,)(l|latch|a|all)(,|$)') then 'Y' else 'N' end as include_latches
&v_ms_if_start , case when regexp_like('&v_ms_stattypes', '(^|,)(t|time|a|all)(,|$)') then 'Y' else 'N' end as include_time
from (
select rtrim(lower('&p_ms_snap'),';') as snap
from dual
);
-- The utility...
-- -----------------------------------------------------------------------
set termout on
declare
-- Run constants...
-- -------------------------------------------------------------------------
c_snap1 constant pls_integer := 1;
c_snap2 constant pls_integer := 2;
-- Snapshots...
-- -------------------------------------------------------------------------
type rt_snaps is record
( snap1 clob
, snap2 clob );
g_snaps rt_snaps;
-- Elapsed time calculation...
-- -------------------------------------------------------------------------
type rt_time is record
( ela integer
, cpu integer );
type aat_time is table of rt_time
index by pls_integer;
g_times aat_time;
-- Utility info...
-- -------------------------------------------------------------------------
procedure ms_options is
begin
dbms_output.put_line('- Statistics types : ' || nvl('&v_ms_start_option', 'all'));
dbms_output.put_line('- Reporting filter : ' || nvl('&v_ms_stop_option', 'none'));
end ms_options;
procedure ms_info is
begin
dbms_output.put_line('- MyStats v&c_ms_version by Adrian Billington (http://www.oracle-developer.net)');
dbms_output.put_line('- Original version based on the SNAP_MY_STATS utility by Jonathan Lewis');
dbms_output.new_line();
end ms_info;
-- Snapshot procedure...
-- -------------------------------------------------------------------------
procedure ms_snap( p_stats in out clob ) is
begin
select dbms_xmlgen.getxml(
q'[select 'STAT' as type
, a.name
, b.value
from v$statname a
, v$mystat b
where a.statistic# = b.statistic#
and '&v_ms_include_statistics' = 'Y'
union all
select 'LATCH'
, name
, gets
from v$latch
where '&v_ms_include_latches' = 'Y'
union all
select 'TIME'
, stat_name
, value
from v$sess_time_model
where sid = sys_context('userenv','sid')
and '&v_ms_include_time_model' = 'Y']'
) into p_stats
from dual;
end ms_snap;
-- Time snapshot...
-- -------------------------------------------------------------------------
procedure ms_time( p_times in out nocopy aat_time,
p_snap in pls_integer ) is
begin
p_times(p_snap).ela := dbms_utility.get_time;
p_times(p_snap).cpu := dbms_utility.get_cpu_time;
end ms_time;
-- Reporting procedure...
-- -------------------------------------------------------------------------
procedure ms_report( p_times in aat_time,
p_snaps in rt_snaps ) is
procedure div( p_divider in varchar2 default '-',
p_width in pls_integer default 90 ) is
begin
dbms_output.put_line( rpad(p_divider, p_width, p_divider) );
end div;
procedure nl( p_newlines in pls_integer default 1 ) is
begin
for i in 1 .. p_newlines loop
dbms_output.put_line(null);
end loop;
end nl;
procedure sh ( p_title in varchar2,
p_header in boolean default true ) is
begin
nl(2);
div;
dbms_output.put_line(p_title);
div;
if p_header then
nl;
dbms_output.put_line('Type ' || rpad('Statistic Name',64) || lpad('Value',18));
dbms_output.put_line(rpad('-',6,'-') || ' ' || rpad('-',64,'-') || ' ' || lpad('-',16,'-'));
end if;
end sh;
begin
-- Report header...
-- ----------------
nl;
div('=');
dbms_output.put_line('MyStats report : ' || to_char(sysdate,'dd-MON-YYYY hh24:mi:ss'));
div('=');
-- Summary timings...
-- ------------------
sh('1. Summary Timings');
dbms_output.put_line(rpad('TIMER', 8) || rpad('snapshot interval (seconds)', 64) ||
lpad(to_char(round((p_times(c_snap2).ela-p_times(c_snap1).ela)/100,2), 'fm999,990.00'),18));
dbms_output.put_line(rpad('TIMER', 8) || rpad('CPU time used (seconds)', 64) ||
lpad(to_char(round((p_times(c_snap2).cpu-p_times(c_snap1).cpu)/100,2), 'fm999,990.00'),18));
-- Output the sorted stats...
-- --------------------------
sh('2. Statistics Report');
for r in ( with ms_start as (
select extractValue(xs.object_value, '/ROW/TYPE') as type
, extractValue(xs.object_value, '/ROW/NAME') as name
, to_number(extractValue(xs.object_value, '/ROW/VALUE')) as value
from table(xmlsequence(extract(xmltype(p_snaps.snap1), '/ROWSET/ROW'))) xs
)
, ms_stop as (
select extractValue(xs.object_value, '/ROW/TYPE') as type
, extractValue(xs.object_value, '/ROW/NAME') as name
, to_number(extractValue(xs.object_value, '/ROW/VALUE')) as value
from table(xmlsequence(extract(xmltype(p_snaps.snap2), '/ROWSET/ROW'))) xs
)
, ms_diffs as (
select type
, name
, ms_stop.value - ms_start.value as diff
from ms_start
inner join
ms_stop
using (type, name)
)
select type, name, diff
from ms_diffs
where (&v_ms_option = 1 and abs(diff) >= &v_ms_threshold)
or (&v_ms_option = 2 and name in (&v_ms_name_list))
or (&v_ms_option = 3 and name like &v_ms_name_like)
or (&v_ms_option = 4 and regexp_like(name, &v_ms_name_regexp, 'i'))
or &v_ms_option = 0
order by
abs(diff) )
loop
dbms_output.put_line(rpad(r.type,8) || rpad(r.name,64) ||
lpad(to_char(r.diff,'999,999,999,999'),18));
end loop;
-- Options...
-- ----------
sh('3. Options Used', false);
ms_options;
-- About...
-- -------
sh('4. About', false);
ms_info;
nl;
div('=');
dbms_output.put_line('End of report');
div('=');
end ms_report;
begin
-- Runtime program...
-- -------------------------------------------------------------------------
if '&v_ms_snap' = 'start' then
ms_snap(g_snaps.snap1);
:bv_ms_start := g_snaps.snap1;
ms_time(g_times, c_snap1);
:bv_ms_ela_start := g_times(c_snap1).ela;
:bv_ms_cpu_start := g_times(c_snap1).cpu;
elsif '&v_ms_snap' = 'stop' then
ms_time(g_times, c_snap2);
g_snaps.snap1 := :bv_ms_start;
g_times(c_snap1).ela := :bv_ms_ela_start;
g_times(c_snap1).cpu := :bv_ms_cpu_start;
ms_snap(g_snaps.snap2);
ms_report(g_times, g_snaps);
else
raise_application_error( -20000,
'Incorrect parameter at position 1 '||
'[used="&v_ms_snap"; valid="start" or "stop"]',
false );
end if;
end;
/
-- Teardown section...
-- -----------------------------------------------------------------------
set termout off
spool "&c_ms_clear" replace
prompt &v_ms_if_stop undefine bv_ms_start
prompt &v_ms_if_stop undefine bv_ms_ela_start
prompt &v_ms_if_stop undefine bv_ms_cpu_start
prompt &v_ms_if_stop undefine v_ms_stattypes
prompt &v_ms_if_stop undefine v_ms_include_statistics
prompt &v_ms_if_stop undefine v_ms_include_latches
prompt &v_ms_if_stop undefine v_ms_include_time_model
prompt &v_ms_if_stop undefine v_ms_start_option
prompt &v_ms_if_stop undefine v_ms_stop_option
spool off
@"&c_ms_clear"
host &c_ms_rmcmd "&c_ms_clear"
undefine 1
undefine 2
undefine p_ms_snap
undefine p_ms_option
undefine v_ms_snap
undefine c_ms_rmcmd
undefine c_ms_init
undefine c_ms_clear
undefine v_ms_if_stop
undefine v_ms_if_start
undefine v_ms_threshold
undefine v_ms_name_list
undefine v_ms_name_like
undefine v_ms_name_regexp
undefine v_ms_option
undefine c_ms_version
set termout on