-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathsnapper.sql
executable file
·2198 lines (1967 loc) · 149 KB
/
snapper.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
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
------------------------------------------------------------------------------
--
-- File name: snapper.sql (Oracle Session Snapper v4)
-- Purpose: An easy to use Oracle session-level performance measurement tool
-- which does NOT require any database changes nor creation of any
-- database objects!
--
-- This is very useful for ad-hoc performance diagnosis in environments
-- with restrictive change management processes, where creating
-- even temporary tables and PL/SQL packages is not allowed or would
-- take too much time to get approved.
--
-- All processing is done by a few sqlplus commands and an anonymous
-- PL/SQL block, all that's needed is SQLPLUS access (and if you want
-- to output data to server-side tracefile then execute rights on
-- DBMS_SYSTEM). Snapper only queries some V$ views (and in advanced
-- mode some X$ fixed tables, but it does not enable any traces nor
-- use oradebug.
--
-- The output is formatted the way it could be easily post-processed
-- by either Unix string manipulation tools or loaded to spreadsheet.
--
-- Snapper v4 supports RAC and requires Oracle 10.1 or a newer DB version.
-- Snapper v3.5 works on Oracle versions starting from Oracle 9.2 (no RAC support)
--
-- Note1: The "ASH" functionality in Snapper just samples GV$SESSION view,
-- so you do NOT need Diagnostics Pack licenses to use Snapper's
-- "ASH" output
--
-- Note2: Snapper just reports you performance metric deltas in a snapsphot
-- and does not attempt to solve any performance problems for you.
-- You still need to interpret and understand these standard Oracle
-- metrics yourself
--
-- Author: Tanel Poder ([email protected])
-- Copyright: (c) Tanel Poder - http://blog.tanelpoder.com - All rights reserved.
--
-- Disclaimer: This script is provided "as is", so no warranties or guarantees are
-- made about its correctness, reliability and safety. Use it at your
-- own risk!
--
-- License: 1) You may use this script for your (or your businesses) purposes for free
-- 2) You may modify this script as you like for your own (or your businesses) purpose,
-- but you must always leave this script header (the entire comment section), including the
-- author, copyright and license sections as the first thing in the beginning of this file
-- 3) You may NOT publish or distribute this script or any variation of it PUBLICLY
-- (including, but not limited to uploading it to your public website or ftp server),
-- instead just link to its location in blog.tanelpoder.com
-- 4) You may distribute this script INTERNALLY in your company, for internal use only,
-- for example when building a standard DBA toolset to be deployed to all
-- servers or DBA workstations
--
--
-- Thanks to: Adrian Billington, Jamey Johnston, Marcus Mönnig, Hans-Peter Sloot
-- and Ronald Rood for bugfixes, additions and improvements
--
--------------------------------------------------------------------------------
--
-- The Session Snapper v4.15 ( USE AT YOUR OWN RISK !!! )
-- (c) Tanel Poder ( http://blog.tanelpoder.com )
--
--
--
-- Usage:
--
-- snapper.sql <ash[1-3]|stats|all>[,out][,trace][,pagesize=X][,gather=[s][t][w][l][e][b][a]]> <seconds_in_snap> <snapshot_count> <sid(s)_to_snap>
--
-- ash - sample session activity ASH style, waits and SQL_IDs from gv$session and
-- print a TOP SQL/wait report from these samples (this is the default from
-- Snapper 3.0). The columns chosen for TOP calculation are defined in CONFIG
-- section below.
--
-- ash=sql_id+event+wait_class
-- - the above example illustrates that you can also specify the gv$session
-- columns for TOP report yourself. The above example will show a TOP
-- activity report grouped by SQL_ID + EVENT + WAIT_CLASS
-- Note that the columns are separated by a "+" sign (as comma is a snapper
-- parameter separator, not ASH column separator)
--
-- ash1
-- ash2
-- ash3 - in addition to "ash" report you can have 3 more reported during the same
-- snapper sampling snapshot. Just include ash1=col1+col2,ash2=col3+col4,...
-- parameters if you want multiple TOP reports per Snapper snapshot
--
-- stats - sample gv$sesstat,gv$sess_time_model,gv$session_event performance counters
-- and report how much these stats increased (deltas) during Snapper run
-- all - report both ASH and stats sections
--
-- out - use dbms_output.put_line() for output. output will be seen only when
-- Snapper run completes due to dbms_output limitations. This is the default.
-- trace - write output to server process tracefile
-- (you must have execute permission on sys.dbms_system.ksdwrt() for that,
-- you can use both out and trace parameters together if you like )
--
-- pagesize - display header lines after X snapshots. if pagesize=0 don't display
-- any headers. pagesize=-1 will display a terse header only once
--
-- gather - if omitted, gathers s,t,w statistics (see below)
-- - if specified, then gather following:
--
-- Session-level stats:
-- s - Session Statistics from gv$sesstat
-- t - Session Time model info from gv$sess_time_model
-- w - Session Wait statistics from gv$session_event and gv$session_wait
--
-- Instance-level stats:
-- l - instance Latch get statistics ( gets + immediate_gets )
-- e - instance Enqueue lock get statistics
-- b - buffer get Where statistics -- useful in versions up to 10.2.x
-- a - All above
--
-- sinclude - if specified, then show only GV$SESSTAT stats which match the
-- LIKE pattern of sinclude (REGEXP_LIKE in 10g+)
-- linclude - if specified, then show only GV$LATCH latch stats which match the
-- LIKE pattern of linclude (REGEXP_LIKE in 10g+)
-- tinclude - if specified, then show only GV$SESS_TIME_MODEL stats which match the
-- LIKE pattern of tinclude (REGEXP_LIKE in 10g+)
-- winclude - if specified, then show only GV$SESSION_EVENT wait stats which match the
-- LIKE pattern of winclude (REGEXP_LIKE in 10g+)
--
-- you can combine above parameters in any order, separate them by commas
-- !!!don't use spaces as otherwise they are treated as next parameters by sqlplus !!!
-- !!!if you want to use spaces, enclose the whole sqlplus parameter in doublequotes !!!
--
-- <seconds_in_snap> - the number of seconds between taking snapshots
-- <snapshot_count> - the number of snapshots to take ( maximum value is power(2,31)-1 )
--
-- <sids_to_snap> can be either one sessionid, multiple sessionids separated by
-- commas or a SQL statement which returns a list of SIDs (if you need spaces
-- in that parameter text, enclose it in double quotes).
--
-- if you want to snap ALL sids, use "all" as value for
-- <sids_to_snap> parameter
--
-- alternatively you can use "select sid from gv$session" as value for <sids_to_snap>
-- parameter to capture all SIDs. you can write any query (with multiple and/or)
-- conditions to specify complex rules for capturing only the SIDs you want
--
-- starting from version 3.0 there are further session_id selection options available in
-- instead of sid you can write such expressions for snapper's <sids_to_snap> parameter:
--
-- sid=123 -- take sid 123 only (the same as just writing 123)
-- user=tanel -- take all sessions where username is 'tanel' (case insensitive)
-- -- this is the same as writing following subquery for the
-- -- <sids_to_snap> parameter:
-- select sid from gv$session where lower(username) like lower('tanel')
--
-- user=tanel% -- take all sessions where username begins with 'tanel%' (case insensitive)
-- -- the = means actually LIKE in SQL terms in this script
--
-- spid=1234 -- all these 3 parameters do the same thing:
-- ospid=1234 -- they look up the sessions(s) where the processes OS PID=1234
-- pid=1234 -- this is useful for quickly looking up what some OS process is doing
-- -- if it consumes too much of some resource
-- qc=123
-- qcsid=123 -- show query coordinator and all PX slave sessions
--
-- program=sqlplus% -- the following examples filter by corresponding gv$session coulmns
-- machine=linux01 -- machine
-- osuser=oracle -- os username
-- module=HR -- module
-- "action=Find Order" -- note the quotes because there is a space inside the parameter
-- -- value
-- client_id=tanelpoder -- show only sessions where client_identifier is set to tanelpoder
-- -- this is very useful in cases with (properly instrumented)
-- -- connection pools
--
--
-- Note that if you want to change some "advanced" snapper configuration parameters
-- or default values then search for CONFIG in this file to see configurable
-- variable section
--
--
-- Examples:
-- NB! Read the online examples, these are more detailed and list script output too!
--
-- http://tech.e2sn.com/oracle-scripts-and-tools/session-snapper
--
-- @snapper ash,stats 1 1 515
-- (Output one 1-second snapshot of session 515 using dbms_output and exit
-- Wait, gv$sesstat and gv$sess_time_model statistics are reported by default
-- Starting from V3 the ASH style session activity report is shown as well)
--
-- @snapper stats,gather=w 1 1 515
-- (Output one 1-second snapshot of session 515 using dbms_output and exit
-- only Wait event statistics are reported, no ASH)
--
-- @snapper ash,gather=st 1 1 515
-- (Output one 1-second snapshot of session 515 using dbms_output and exit
-- only gv$sesstat and gv$sess_Time_model statistics are gathered + ASH)
--
-- @snapper trace,ash,gather=stw,pagesize=0 10 90 117,210,313
-- (Write 90 10-second snapshots into tracefile for session IDs 117,210,313
-- all statistics are reported, do not print any headers)
--
-- @snapper trace,ash 900 999999999 "select sid from v$session"
-- (Take a snapshot of ALL sessions every 15 minutes and write the output to trace,
-- loop (almost) forever )
--
-- @snapper out,trace 300 12 "select sid from v$session where username='APPS'"
-- (Take 12 5-minute snapshots of all sessions belonging to APPS user, write
-- output to both dbms_output and tracefile)
--
-- Notes:
--
-- Snapper does not currently detect if a session with given SID has
-- ended and been recreated between snapshots, thus it may report bogus
-- statistics for such sessions. The check and warning for that will be
-- implemented in a future version.
--
--------------------------------------------------------------------------------
set termout off tab off verify off linesize 999 trimspool on trimout on null ""
--debug:
-- set termout on serveroutput on
-- Get parameters (future snapper v4.x extended syntax: @snapper <options> <"begin"|"end"|sleep#> <"snap_name"|snap_count> <sid>)
define snapper_options="&1"
define snapper_sleep="&2"
define snapper_count="&3"
define snapper_sid="&4"
-- The following code is required for making this script "dynamic" as due to
-- different Oracle versions, script parameters or granted privileges some
-- statements might not compile if not adjusted properly.
define _IF_ORA11_OR_HIGHER="--"
define _IF_LOWER_THAN_ORA11="--"
define _IF_DBMS_SYSTEM_ACCESSIBLE="/* dbms_system is not accessible"
-- /*dummy*/ -- this "dummy" is here just for avoiding VIM syntax highlighter going crazy due to previous line
define _IF_X_ACCESSIBLE="--"
-- plsql_object_id columns available in v$session (from 10.2.0.3)
define _YES_PLSQL_OBJ_ID="--"
define _NO_PLSQL_OBJ_ID=""
-- blocking_instance available in v$session (from 10.2)
define _YES_BLK_INST="--"
define _NO_BLK_INST=""
-- snapper v4 manual before/after snapshotting
define _MANUAL_SNAPSHOT="--"
define _USE_DBMS_LOCK=""
-- set the noprint's value to "noprint" if you don't want these temporary variables to show up in a sqlplus spool file
DEF noprint="noprint"
col snapper_ora11higher &noprint new_value _IF_ORA11_OR_HIGHER
col snapper_ora11lower &noprint new_value _IF_LOWER_THAN_ORA11
col dbms_system_accessible &noprint new_value _IF_DBMS_SYSTEM_ACCESSIBLE
col x_accessible &noprint new_value _IF_X_ACCESSIBLE
col no_plsql_obj_id &noprint new_value _NO_PLSQL_OBJ_ID
col yes_plsql_obj_id &noprint new_value _YES_PLSQL_OBJ_ID
col no_blk_inst &noprint new_value _NO_BLK_INST
col yes_blk_inst &noprint new_value _YES_BLK_INST
col manual_snapshot &noprint new_value _MANUAL_SNAPSHOT
col use_dbms_lock &noprint new_value _USE_DBMS_LOCK
col snapper_sid &noprint new_value snapper_sid
-- sid_filter and inst_filter are the new RAC gv$ friendly way to filter sessions in Snapper v4
def sid_filter="/**/"
def inst_filter="/**/"
col sid_filter &noprint new_value sid_filter
col inst_filter &noprint new_value inst_filter
-- initialize, precompute and determine stuff
var v varchar2(100)
var x varchar2(10)
var sid_filter varchar2(4000)
var inst_filter varchar2(4000)
-- this is here for a reason
-- im extracting the first word of the snapper_sid (if its a complex expression, not just a single SID)
-- by relying on how DEF and & assignment treat spaces in strings
def ssid_begin=&snapper_sid
declare
o sys.dbms_describe.number_table;
p sys.dbms_describe.number_table;
l sys.dbms_describe.number_table;
a sys.dbms_describe.varchar2_table;
dty sys.dbms_describe.number_table;
def sys.dbms_describe.number_table;
inout sys.dbms_describe.number_table;
len sys.dbms_describe.number_table;
prec sys.dbms_describe.number_table;
scal sys.dbms_describe.number_table;
rad sys.dbms_describe.number_table;
spa sys.dbms_describe.number_table;
tmp number;
lv_sid_filter varchar2(4000);
lv_inst_filter varchar2(4000);
function get_filter(str in varchar2) return varchar2
is
ret varchar2(1000);
begin
if str like '%@%' then
--dbms_output.put_line('get_filter:1 str= '||str);
ret := lower(trim(regexp_replace(substr(str,instr(str,'=')+1), '^(.+)@([[:digit:]\*]+)(.*)', '\1')));
else
--dbms_output.put_line('get_filter:2 str= '||str);
ret := lower(trim(substr(str,instr(str,'=')+1)));
end if;
--dbms_output.put_line('get_filter = ' || ret);
return ret;
end get_filter;
begin
-- compute inst_filter
case
when regexp_instr('&ssid_begin','@') = 0 then
lv_inst_filter := '/* inst_filter */ s.inst_id=USERENV(''Instance'')';
when regexp_instr('&ssid_begin','@\*') > 0 or '&ssid_begin' like '(%' then
lv_inst_filter := '/* inst_filter */ 1=1';
when regexp_instr('&ssid_begin','@\d+') > 0 then
lv_inst_filter := 's.inst_id = ' || regexp_replace('&ssid_begin', '^(.+)@(\d+)(.*)', '\2');
else
lv_inst_filter := 's.inst_id=USERENV(''Instance'')';
--when regexp_instr('&ssid_begin','@\d+') > 0 then regexp_replace(snapper_sid, '^(.+)@\d+', '\1') || ' AND inst_id = ' || regexp_replace(snapper_sid, '^(.+)@(\d+)(.*)', '\2')
end case;
-- compute sid_filter
case
when trim(lower('&ssid_begin')) like 'sid=%' then lv_sid_filter := 's.sid in (' ||get_filter('&ssid_begin')||')'; --||trim(replace('&ssid_begin','sid=',''))||')';
when trim(lower('&ssid_begin')) like 'audsid=%' then lv_sid_filter := 's.audsid in ('||get_filter('&ssid_begin')||')'; --||trim(replace('&ssid_begin','sid=',''))||')';
when trim(lower('&ssid_begin')) like 'user=%' then lv_sid_filter := 'lower(username) like ''' ||get_filter('&ssid_begin')||'''';
when trim(lower('&ssid_begin')) like 'username=%' then lv_sid_filter := 'lower(username) like ''' ||get_filter('&ssid_begin')||'''';
when trim(lower('&ssid_begin')) like 'machine=%' then lv_sid_filter := 'lower(machine) like ''' ||get_filter('&ssid_begin')||'''';
when trim(lower('&ssid_begin')) like 'program=%' then lv_sid_filter := 'lower(program) like ''' ||get_filter('&ssid_begin')||'''';
when trim(lower('&ssid_begin')) like 'service=%' then lv_sid_filter := 'lower(service_name) like ''' ||get_filter('&ssid_begin')||'''';
when trim(lower('&ssid_begin')) like 'module=%' then lv_sid_filter := 'lower(module) like ''' ||get_filter('&ssid_begin')||'''';
when trim(lower('&ssid_begin')) like 'action=%' then lv_sid_filter := 'lower(action) like ''' ||get_filter('&ssid_begin')||'''';
when trim(lower('&ssid_begin')) like 'osuser=%' then lv_sid_filter := 'lower(osuser) like ''' ||get_filter('&ssid_begin')||'''';
when trim(lower('&ssid_begin')) like 'client_id=%' then lv_sid_filter := 'lower(client_identifier) like '''||get_filter('&ssid_begin')||'''';
when trim(lower('&ssid_begin')) like 'spid=%' then lv_sid_filter := '(s.inst_id,s.paddr) in (select /*+ UNNEST */ inst_id,addr from gv$process where spid in ('||get_filter('&ssid_begin')||'))';
when trim(lower('&ssid_begin')) like 'ospid=%' then lv_sid_filter := '(s.inst_id,s.paddr) in (select /*+ UNNEST */ inst_id,addr from gv$process where spid in ('||get_filter('&ssid_begin')||'))';
when trim(lower('&ssid_begin')) like 'pid=%' then lv_sid_filter := '(s.inst_id,s.paddr) in (select /*+ UNNEST */ inst_id,addr from gv$process where spid in ('||get_filter('&ssid_begin')||'))';
when trim(lower('&ssid_begin')) like 'qcsid=%' then lv_sid_filter := '(s.inst_id,s.sid) in (select /*+ NO_UNNEST */ inst_id,sid from gv$px_session where qcsid in ('||get_filter('&ssid_begin')||'))';
when trim(lower('&ssid_begin')) like 'qc=%' then lv_sid_filter := '(s.inst_id,s.sid) in (select /*+ NO_UNNEST */ inst_id,sid from gv$px_session where qcsid in ('||get_filter('&ssid_begin')||'))';
when trim(lower('&ssid_begin')) like 'all%' then lv_sid_filter := '1=1';
when trim(lower('&ssid_begin')) like 'bg%' then lv_sid_filter := 'type=''BACKGROUND''';
when trim(lower('&ssid_begin')) like 'fg%' then lv_sid_filter := 'type=''USER''';
when trim(lower('&ssid_begin')) like 'smon%' then lv_sid_filter := 'program like ''%(SMON)%''';
when trim(lower('&ssid_begin')) like 'pmon%' then lv_sid_filter := 'program like ''%(PMON)%''';
when trim(lower('&ssid_begin')) like 'ckpt%' then lv_sid_filter := 'program like ''%(CKPT)%''';
when trim(lower('&ssid_begin')) like 'lgwr%' then lv_sid_filter := 'program like ''%(LG__)%'''; -- 12c multiple adaptive LGWR workers
when trim(lower('&ssid_begin')) like 'dbwr%' then lv_sid_filter := 'regexp_like(program, ''.*\((DBW.|BW..)\).*'', ''i'')';
when trim(lower('&ssid_begin')) like 'select%' then /*lv_inst_filter := '/* inst_filter2 1=1'; */ lv_sid_filter := q'{(s.inst_id,s.sid) in (&snapper_sid)}';
--when trim(lower('&ssid_begin')) like 'select%' then lv_sid_filter := '(s.inst_id,s.sid) in ('||regexp_replace(replace(q'{&snapper_sid}','',''''''), '^select ', 'select /*+ unnest */ ', 1, 1, 'i')||')'; -- '1=1'; lv_inst_filter := '1=1';
--when trim(lower('&ssid_begin')) like 'with%' then lv_sid_filter := '(s.inst_id,s.sid) in (&snapper_sid)'; -- '1=1'; lv_inst_filter := '1=1';
when trim(lower('&ssid_begin')) like '(%' then lv_inst_filter := '/* inst_filter2 */ 1=1'; lv_sid_filter := q'{(s.inst_id,s.sid) in (&snapper_sid)}'; -- '1=1'; lv_inst_filter := '1=1';
else lv_sid_filter := '/* sid_filter_else_cond */ s.sid in ('||get_filter('&ssid_begin')||')'; --lv_sid_filter := '/* sid_filter_else_cond */ s.sid in (&ssid_begin)';
end case;
:inst_filter := lv_inst_filter;
:sid_filter := lv_inst_filter||' and '||lv_sid_filter;
-- this block determines whether dbms_system.ksdwrt is accessible to us
-- dbms_describe is required as all_procedures/all_objects may show this object
-- even if its not executable by us (thanks to o7_dictionary_accessibility=false)
begin
execute immediate 'select count(*) from x$kcbwh where rownum = 1' into tmp;
:x:= ' '; -- x$ tables are accessible, so dont comment any lines out
exception
when others then null;
end;
sys.dbms_describe.describe_procedure(
'DBMS_SYSTEM.KSDWRT', null, null,
o, p, l, a, dty, def, inout, len, prec, scal, rad, spa
);
-- we never get to following statement if dbms_system is not accessible
-- as sys.dbms_describe will raise an exception
:v:= '-- dbms_system is accessible';
exception
when others then null;
end;
/
-- this query populates some sqlplus variables required for dynamic compilation used below
with mod_banner as (
select
replace(banner,'9.','09.') banner
from
v$version
where rownum = 1
)
select
case when substr(banner, instr(banner, 'Release ')+8,2) >= '11' then '' else '--' end snapper_ora11higher,
case when substr(banner, instr(banner, 'Release ')+8,2) < '11' then '' else '--' end snapper_ora11lower,
nvl(:v, '/* dbms_system is not accessible') dbms_system_accessible,
nvl(:x, '--') x_accessible,
case when substr( banner, instr(banner, 'Release ')+8, instr(substr(banner,instr(banner,'Release ')+8),' ') ) >= '10.2' then '' else '--' end yes_blk_inst,
case when substr( banner, instr(banner, 'Release ')+8, instr(substr(banner,instr(banner,'Release ')+8),' ') ) >= '10.2' then '--' else '' end no_blk_inst,
case when substr( banner, instr(banner, 'Release ')+8, instr(substr(banner,instr(banner,'Release ')+8),' ') ) >= '10.2.0.3' then '' else '--' end yes_plsql_obj_id,
case when substr( banner, instr(banner, 'Release ')+8, instr(substr(banner,instr(banner,'Release ')+8),' ') ) >= '10.2.0.3' then '--' else '' end no_plsql_obj_id,
case when lower('&snapper_options') like '%,begin%' or lower('&snapper_options') like 'begin%' or lower('&snapper_options') like '%,end%' or lower('&snapper_options') like 'end%' then '' else '--' end manual_snapshot,
case when lower('&snapper_options') like '%,begin%' or lower('&snapper_options') like 'begin%' or lower('&snapper_options') like '%,end%' or lower('&snapper_options') like 'end%' then '--' else '' end use_dbms_lock,
:sid_filter sid_filter,
:inst_filter inst_filter
from
mod_banner
/
set termout on serveroutput on size 1000000 format wrapped
prompt Sampling SID &4 with interval &snapper_sleep seconds, taking &snapper_count snapshots...
-- let the Snapping start!!!
-- main()
declare
-- Snapper start
-- forward declarations
procedure output(p_txt in varchar2);
procedure fout;
function tptformat( p_num in number,
p_stype in varchar2 default 'STAT',
p_precision in number default 2,
p_base in number default 10,
p_grouplen in number default 3
)
return varchar2;
function getopt( p_parvalues in varchar2,
p_extract in varchar2,
p_delim in varchar2 default ','
)
return varchar2;
-- type, constant, variable declarations
-- trick for holding 32bit UNSIGNED event and stat_ids in 32bit SIGNED PLS_INTEGER
pls_adjust constant number(10,0) := power(2,31) - 1;
type srec is record (ts timestamp, stype varchar2(4), inst_id number, sid number, statistic# number, value number, event_count number );
type stab is table of srec index by pls_integer;
type ltab is table of srec index by varchar2(100); -- lookup tab for various average calculation
s1 stab;
s2 stab;
l1 ltab;
l2 ltab;
type snrec is record (stype varchar2(4), statistic# number, name varchar2(100));
type sntab is table of snrec index by pls_integer;
sn_tmp sntab;
sn sntab;
type sntab_reverse is table of snrec index by varchar2(100); -- used for looking up stat id from stat name
sn_reverse sntab_reverse;
tmp_varchar2 varchar2(1000); -- misc
function get_useful_average(c in srec /* curr_metric */, p in srec /* all_prev_metrics */) return varchar2;
type tmp_sestab is table of gv$session%rowtype index by pls_integer;
type sestab is table of gv$session%rowtype index by varchar2(20);
g_sessions sestab;
g_empty_sessions sestab;
type hc_tab is table of number index by pls_integer; -- index is sql hash value
type ses_hash_tab is table of hc_tab index by pls_integer; -- index is SID
g_ses_hash_tab ses_hash_tab;
g_empty_ses_hash_tab ses_hash_tab;
-- dbms_debug_vc2coll is a built-in collection present in every oracle db
g_ash sys.dbms_debug_vc2coll := new sys.dbms_debug_vc2coll();
g_empty_ash sys.dbms_debug_vc2coll := new sys.dbms_debug_vc2coll();
g_snap1 sys.dbms_debug_vc2coll;
g_snap2 sys.dbms_debug_vc2coll;
g_ash_samples_taken number := 0;
g_count_statname number;
g_count_eventname number;
g_mysid number;
i number;
a number;
b number;
c number;
delta number;
evcnt number;
changed_values number;
pagesize number:=99999999999999;
missing_values_s1 number := 0;
missing_values_s2 number := 0;
disappeared_sid number := 0;
lv_curr_sid number := 0; -- used for determining whether to print an empty line between session stats
d1 timestamp(6);
d2 timestamp(6);
ash_date1 date;
ash_date2 date;
lv_gather varchar2(1000);
gv_header_string varchar2(1000);
lv_data_string varchar2(1000);
lv_ash varchar2(1000);
lv_stats varchar2(1000);
gather_stats number := 0;
gather_ash number := 0;
g_snap_begin varchar2(1000);
g_snap_end varchar2(1000);
-- CONFIGURABLE STUFF --
-- this sets what are the default ash sample TOP reporting group by columns
g_ash_columns varchar2(1000) := 'inst_id + sql_id + sql_child_number + event + wait_class';
g_ash_columns1 varchar2(1000) := 'inst_id + event + p1 + wait_class';
g_ash_columns2 varchar2(1000) := 'inst_id + sid + user + machine + program';
g_ash_columns3 varchar2(1000) := 'inst_id + plsql_object_id + plsql_subprogram_id + sql_id';
-- output column configuration
output_header number := 0; -- 1=true 0=false
output_username number := 1; -- v$session.username
output_inst number := 0; -- inst
output_sid number := CASE WHEN dbms_utility.is_cluster_database = TRUE THEN 0 ELSE 1 END; -- just sid
output_inst_sid number := CASE WHEN dbms_utility.is_cluster_database = TRUE THEN 1 ELSE 0 END; -- inst_id and sid together
output_time number := 0; -- time of snapshot start
output_seconds number := 0; -- seconds in snapshot (shown in footer of each snapshot too)
output_stype number := 1; -- statistic type (WAIT,STAT,TIME,ENQG,LATG,...)
output_sname number := 1; -- statistic name
output_delta number := 1; -- raw delta
output_delta_s number := 0; -- raw delta normalized to per second
output_hdelta number := 0; -- human readable delta
output_hdelta_s number := 1; -- human readable delta normalized to per second
output_percent number := 1; -- percent of total time/samples
output_eventcnt number := 1; -- wait event count
output_eventcnt_s number := 1; -- wait event count
output_eventavg number := 1; -- average wait duration
output_pcthist number := 1; -- percent of total visual bar (histogram) -- Histograms seem to work for me on 9.2.0.7 + - JBJ2)
-- column widths in ASH report output
w_inst_id number := 4;
w_sid number := 6;
w_username number := 20;
w_machine number := 20;
w_terminal number := 20;
w_program number := 25;
w_event number := 35;
w_wait_class number := 15;
w_state number := 8;
w_p1 number := 20;
w_p2 number := 20;
w_p3 number := 20;
w_row_wait_obj# number := 10;
w_row_wait_file# number := 6;
w_row_wait_block# number := 10;
w_row_wait_row# number := 6;
w_blocking_session_status number := 15;
w_blocking_instance number := 12;
w_blocking_session number := 12;
w_sql_hash_value number := 12;
w_sql_id number := 15;
w_sql_child_number number := 9;
w_plsql_entry_object_id number := 10;
w_plsql_entry_subprogram_id number := 10;
w_plsql_object_id number := 10;
w_plsql_subprogram_id number := 10;
w_module number := 25;
w_action number := 25;
w_client_identifier number := 25;
w_service_name number := 25;
w_activity_pct number := 7;
-- END CONFIGURABLE STUFF --
-- constants for ash collection extraction from the vc2 collection
s_inst_id constant number := 1 ;
s_sid constant number := 2 ;
s_username constant number := 3 ;
s_machine constant number := 4 ;
s_terminal constant number := 5 ;
s_program constant number := 6 ;
s_event constant number := 7 ;
s_wait_class constant number := 8 ;
s_state constant number := 9 ;
s_p1 constant number := 10 ;
s_p2 constant number := 11 ;
s_p3 constant number := 12 ;
s_row_wait_obj# constant number := 13 ;
s_row_wait_file# constant number := 14 ;
s_row_wait_block# constant number := 15 ;
s_row_wait_row# constant number := 16 ;
s_blocking_session_status constant number := 17 ;
s_blocking_instance constant number := 18 ;
s_blocking_session constant number := 19 ;
s_sql_hash_value constant number := 20 ;
s_sql_id constant number := 21 ;
s_sql_child_number constant number := 22 ;
s_plsql_entry_object_id constant number := 23 ;
s_plsql_entry_subprogram_id constant number := 24 ;
s_plsql_object_id constant number := 25 ;
s_plsql_subprogram_id constant number := 26 ;
s_module constant number := 27 ;
s_action constant number := 28 ;
s_client_identifier constant number := 29 ;
s_service_name constant number := 30 ;
-- constants for ash collection reporting, which columns to show in report
c_inst_id constant number := power(2, s_inst_id );
c_sid constant number := power(2, s_sid );
c_username constant number := power(2, s_username );
c_machine constant number := power(2, s_machine );
c_terminal constant number := power(2, s_terminal );
c_program constant number := power(2, s_program );
c_event constant number := power(2, s_event );
c_wait_class constant number := power(2, s_wait_class );
c_state constant number := power(2, s_state );
c_p1 constant number := power(2, s_p1 );
c_p2 constant number := power(2, s_p2 );
c_p3 constant number := power(2, s_p3 );
c_row_wait_obj# constant number := power(2, s_row_wait_obj# );
c_row_wait_file# constant number := power(2, s_row_wait_file# );
c_row_wait_block# constant number := power(2, s_row_wait_block# );
c_row_wait_row# constant number := power(2, s_row_wait_row# );
c_blocking_session_status constant number := power(2, s_blocking_session_status );
c_blocking_instance constant number := power(2, s_blocking_instance );
c_blocking_session constant number := power(2, s_blocking_session );
c_sql_hash_value constant number := power(2, s_sql_hash_value );
c_sql_id constant number := power(2, s_sql_id );
c_sql_child_number constant number := power(2, s_sql_child_number );
c_plsql_entry_object_id constant number := power(2, s_plsql_entry_object_id );
c_plsql_entry_subprogram_id constant number := power(2, s_plsql_entry_subprogram_id);
c_plsql_object_id constant number := power(2, s_plsql_object_id );
c_plsql_subprogram_id constant number := power(2, s_plsql_subprogram_id );
c_module constant number := power(2, s_module );
c_action constant number := power(2, s_action );
c_client_identifier constant number := power(2, s_client_identifier );
c_service_name constant number := power(2, s_service_name );
/*---------------------------------------------------
-- proc for outputting data to trace or dbms_output
---------------------------------------------------*/
procedure output(p_txt in varchar2) is
begin
if (getopt('&snapper_options', 'out') is not null)
or
(getopt('&snapper_options', 'out') is null and getopt('&snapper_options', 'trace') is null)
then
dbms_output.put_line(p_txt);
end if;
-- The block below is a sqlplus trick for conditionally commenting out PL/SQL code
&_IF_DBMS_SYSTEM_ACCESSIBLE
if getopt('&snapper_options', 'trace') is not null then
sys.dbms_system.ksdwrt(1, p_txt);
sys.dbms_system.ksdfls;
end if;
-- */
end; -- output
/*---------------------------------------------------
-- function for converting interval datatype to microseconds
---------------------------------------------------*/
function get_seconds(i interval day to second) return number
as
s NUMBER;
begin
s := to_number(extract(second from i)) +
to_number(extract(minute from i)) * 60 +
to_number(extract(hour from i)) * 60 * 60 +
to_number(extract(day from i)) * 60 * 60 * 24;
--output('get_seconds '||to_char(i)||' = '||to_char(s));
return s;
end get_seconds;
/*---------------------------------------------------
-- proc for outputting data, utilizing global vars
---------------------------------------------------*/
procedure fout is
l_output_username VARCHAR2(100);
gsid varchar2(20);
begin
--if s2(b).stype='WAIT' then output( 'DEBUG WAIT ' || sn(s2(b).statistic#).name || ' ' || delta ); end if;
--output( 'DEBUG, Entering fout(), b='||to_char(b)||' sn(s2(b).statistic#='||s2(b).statistic# );
--output( 'DEBUG, In fout(), a='||to_char(a)||' b='||to_char(b)||' s1.count='||s1.count||' s2.count='||s2.count||' s2.count='||s2.count);
gsid := trim(to_char(s2(b).inst_id))||','||trim(to_char(s2(b).sid));
if output_username = 1 then
begin
l_output_username := nvl( g_sessions(gsid).username, substr(g_sessions(gsid).program, instr(g_sessions(gsid).program,'(')) );
exception
when no_data_found then l_output_username := 'error';
when others then raise;
end;
end if;
-- DEBUG
--output('before');
--output (CASE WHEN output_eventavg = 1 THEN CASE WHEN s2(b).stype IN ('WAIT') THEN lpad(tptformat(delta / CASE WHEN evcnt = 0 THEN 1 ELSE evcnt END, s2(b).stype), 10, ' ')||' average wait' ELSE get_useful_average(s2(b), s1(a)) END END);
--output('after');
output( CASE WHEN output_header = 1 THEN 'SID= ' END
|| CASE WHEN output_inst = 1 THEN to_char(s2(b).inst_id, '9999')||', ' END
|| CASE WHEN output_sid = 1 THEN to_char(s2(b).sid,'999999')||', ' END
|| CASE WHEN output_inst_sid = 1 THEN to_char(s2(b).sid,'99999')||' '||lpad('@'||trim(to_char(s2(b).inst_id, '99')),3)||', ' END
|| CASE WHEN output_username = 1 THEN rpad(CASE s2(b).sid WHEN -1 THEN ' ' ELSE NVL(l_output_username, ' ') END, 10)||', ' END
|| CASE WHEN output_time = 1 THEN to_char(d1, 'YYYYMMDD HH24:MI:SS')||', ' END
|| CASE WHEN output_seconds = 1 THEN to_char(case get_seconds(d2-d1) when 0 then &snapper_sleep else get_seconds(d2-d1) end, '9999999')||', ' END
|| CASE WHEN output_stype = 1 THEN s2(b).stype||', ' END
|| CASE WHEN output_sname = 1 THEN rpad(sn(s2(b).statistic#).name, 58, ' ')||', ' END
|| CASE WHEN output_delta = 1 THEN to_char(delta, '999999999999')||', ' END
|| CASE WHEN output_delta_s = 1 THEN to_char(delta/(case get_seconds(d2-d1) when 0 then &snapper_sleep else get_seconds(d2-d1) end),'999999999')||', ' END
|| CASE WHEN output_hdelta = 1 THEN lpad(tptformat(delta, s2(b).stype), 10, ' ')||', ' END
|| CASE WHEN output_hdelta_s = 1 THEN lpad(tptformat(delta/(case get_seconds(d2-d1) when 0 then &snapper_sleep else get_seconds(d2-d1) end ), s2(b).stype), 10, ' ')||', ' END
|| CASE WHEN output_percent = 1 THEN CASE WHEN s2(b).stype IN ('TIME','WAIT') THEN to_char(delta/CASE get_seconds(d2-d1) WHEN 0 THEN &snapper_sleep ELSE get_seconds(d2-d1) END / 10000, '9999.9')||'%' ELSE ' ' END END||', '
|| CASE WHEN output_pcthist = 1 THEN CASE WHEN s2(b).stype IN ('TIME','WAIT') THEN rpad(rpad('[', ceil(round(delta/CASE get_seconds(d2-d1) WHEN 0 THEN &snapper_sleep ELSE get_seconds(d2-d1) END / 100000,1))+1, CASE WHEN s2(b).stype IN ('WAIT') THEN 'W' WHEN sn(s2(b).statistic#).name = 'DB CPU' THEN '@' ELSE '#' END),11,' ')||']' ELSE ' ' END END||', '
|| CASE WHEN output_eventcnt = 1 THEN CASE WHEN s2(b).stype IN ('WAIT') THEN to_char(evcnt, '99999999') ELSE ' ' END END||', '
|| CASE WHEN output_eventcnt_s = 1 THEN CASE WHEN s2(b).stype IN ('WAIT') THEN lpad(tptformat((evcnt / case get_seconds(d2-d1) when 0 then &snapper_sleep else get_seconds(d2-d1) end ), 'STAT' ), 10, ' ') ELSE ' ' END END||', '
|| CASE WHEN output_eventavg = 1 THEN CASE WHEN s2(b).stype IN ('WAIT') THEN lpad(tptformat(delta / CASE WHEN evcnt = 0 THEN 1 ELSE evcnt END, s2(b).stype), 10, ' ')||' average wait' ELSE get_useful_average(s2(b), s1(a)) END END
);
end;
/*---------------------------------------------------
-- lookup stat delta helper calculator (l2.value - l1.value)
---------------------------------------------------*/
function get_delta(metric_id in varchar2) return number
is
rec1 srec;
rec2 srec;
val1 number;
val2 number;
d number;
begin
begin
val1 := l1(metric_id).value;
exception
when no_data_found then val1 := 0;
end;
begin
val2 := l2(metric_id).value;
exception
when no_data_found then val2 := 0;
end;
d := val2 - NVL(val1, 0);
return d;
end get_delta;
/*---------------------------------------------------
-- delta helper function for convenience - it allows to specify any metric delta, if not specified then get current one
---------------------------------------------------*/
function gd(c in srec, metric_type in varchar2 DEFAULT NULL, metric_name in varchar2 DEFAULT NULL) return number
is
str varchar2(1000);
tmp_delta number;
begin
if metric_type || metric_name is null then
str := c.stype||','||trim(to_char(c.inst_id))||','||trim(to_char(c.sid))||','||trim(to_char(c.statistic#,'999999999999999999999999'));
else
begin
str := trim(metric_type)||','||trim(to_char(c.inst_id))||','||trim(to_char(c.sid))||','||trim(to_char(sn_reverse(metric_type||','||metric_name).statistic#));
exception
when no_data_found then return 0;
end;
end if;
tmp_delta := get_delta(str);
--output('tmp_delta '||c.stype||' '||tmp_delta);
return tmp_delta;
-- return get_delta(str);
end;
/*---------------------------------------------------
-- function for calculating useful averages and ratios between metrics
---------------------------------------------------*/
function get_useful_average(c in srec /* curr_metric */, p in srec /* all_prev_metrics */) return varchar2
is
ret varchar2(1000);
mt varchar2(100) := c.stype; -- metric_type
mn varchar2(100) := sn(c.statistic#).name; -- metric_name
begin
case
when mt = 'STAT' then
case
when mn = 'file io service time' then ret := lpad( tptformat(gd(c) / nullif(gd(c, 'STAT', 'physical read total IO requests')+gd(c, 'STAT', 'physical write total IO requests'),0), 'TIME'), 10) || ' bad guess of IO service time per IO request';
when mn = 'file io wait time' then ret := lpad( tptformat(gd(c) / nullif(gd(c, 'STAT', 'physical read total IO requests')+gd(c, 'STAT', 'physical write total IO requests'),0), 'TIME'), 10) || ' bad guess of IO wait time per IO request';
when mn = 'redo synch time overhead (usec)' then ret := lpad( tptformat(gd(c) / nullif(gd(c, 'STAT', 'redo synch writes' ),0), 'TIME'), 10) || ' FG wakeup overhead per log file sync';
when mn = 'redo write time' then ret := lpad( tptformat(gd(c) * 10000 / nullif(gd(c, 'STAT', 'redo writes' ),0), 'TIME'), 10) || ' per redo write';
when mn = 'recursive calls' then ret := lpad( tptformat(gd(c, 'STAT', 'recursive cpu usage') * 10000 / nullif(gd(c), 0), 'TIME'), 10) || ' recursive CPU per recursive call';
when mn = 'recursive cpu usage' then ret := lpad( tptformat(gd(c) * 10000, 'TIME'), 10) || ' total recursive CPU usage';
when mn = 'parse time cpu' then ret := lpad( tptformat(gd(c) * 10000, 'TIME'), 10) || ' total parse time CPU';
when mn = 'parse time elapsed' then ret := lpad( tptformat(gd(c) * 10000, 'TIME'), 10) || ' total parse time elapsed';
when mn = 'physical write IO requests' then ret := lpad( tptformat(gd(c, 'STAT', 'physical write bytes') / nullif(gd(c),0), mt), 10) || ' bytes per request' ;
when mn = 'physical total write IO requests' then ret := lpad( tptformat(gd(c, 'STAT', 'physical total write bytes') / nullif(gd(c),0), mt), 10) || ' bytes per request' ;
when mn = 'physical read IO requests' then ret := lpad( tptformat(gd(c, 'STAT', 'physical read bytes' ) / nullif(gd(c),0), mt), 10) || ' bytes per request' ;
when mn = 'physical total read IO requests' then ret := lpad( tptformat(gd(c, 'STAT', 'physical total read bytes' ) / nullif(gd(c),0), mt), 10) || ' bytes per request' ;
when mn = 'bytes sent via SQL*Net to client' then ret := lpad( tptformat(gd(c) / nullif(gd(c, 'STAT', 'SQL*Net roundtrips to/from client'),0), mt), 10) || ' bytes per roundtrip' ;
when mn = 'bytes received via SQL*Net from client' then ret := lpad( tptformat(gd(c) / nullif(gd(c, 'STAT', 'SQL*Net roundtrips to/from client'),0), mt), 10) || ' bytes per roundtrip' ;
when mn = 'bytes sent via SQL*Net to dblink' then ret := lpad( tptformat(gd(c) / nullif(gd(c, 'STAT', 'SQL*Net roundtrips to/from dblink'),0), mt), 10) || ' bytes per roundtrip' ;
when mn = 'bytes received via SQL*Net from dblink' then ret := lpad( tptformat(gd(c) / nullif(gd(c, 'STAT', 'SQL*Net roundtrips to/from dblink'),0), mt), 10) || ' bytes per roundtrip' ;
when mn = 'redo size' then ret := lpad( tptformat(gd(c) / nullif(gd(c, 'STAT', 'user commits' ),0), mt), 10) || ' bytes per user commit';
when mn = 'execute count' then ret := lpad( tptformat(gd(c) / nullif(gd(c, 'STAT', 'parse count (total)' ),0), mt), 10) || ' executions per parse';
when mn = 'parse count (total)' then ret := lpad( tptformat(gd(c) / nullif(gd(c, 'STAT', 'parse count (hard)' ),0), mt), 10) || ' softparses per hardparse';
when mn = 'session cursor cache hits' then ret := lpad( tptformat(gd(c) - (gd(c, 'STAT', 'parse count (total)' ) ), mt), 10) || ' softparses avoided thanks to cursor cache';
when mn = 'session logical reads' then ret := lpad( tptformat(gd(c) + (gd(c, 'STAT', 'buffer is pinned count' ) ), mt), 10) || ' total buffer visits';
when mn = 'buffer is pinned count' then ret := lpad( tptformat(gd(c) / nullif(gd(c) + gd(c, 'STAT', 'session logical reads'),0) * 100, mt), 10) || ' % buffer gets avoided thanks to buffer pin caching';
else ret := lpad( tptformat(gd(c) / nullif(gd(c, 'STAT', 'execute count'),0), mt), 10) || ' per execution' ;
end case; -- mt=stat, mn
when mt = 'TIME' then
-- this is ugly and wrong at the moment - will refactor some day
case
when mn = 'DB time' then ret := lpad(tptformat((get_seconds(d2 - d1)*1000000 - (
gd(c)
/*+ gd(c, 'DB CPU', 'TIME') */
+ gd(c, 'WAIT', 'pmon timer')
+ gd(c, 'WAIT', 'VKTM Logical Idle Wait')
+ gd(c, 'WAIT', 'VKTM Init Wait for GSGA')
+ gd(c, 'WAIT', 'IORM Scheduler Slave Idle Wait')
+ gd(c, 'WAIT', 'rdbms ipc message')
+ gd(c, 'WAIT', 'i/o slave wait')
+ gd(c, 'WAIT', 'VKRM Idle')
+ gd(c, 'WAIT', 'wait for unread message on broadcast channel')
+ gd(c, 'WAIT', 'wait for unread message on multiple broadcast channels')
+ gd(c, 'WAIT', 'class slave wait')
+ gd(c, 'WAIT', 'KSV master wait')
+ gd(c, 'WAIT', 'PING')
+ gd(c, 'WAIT', 'watchdog main loop')
+ gd(c, 'WAIT', 'DIAG idle wait')
+ gd(c, 'WAIT', 'ges remote message')
+ gd(c, 'WAIT', 'gcs remote message')
+ gd(c, 'WAIT', 'heartbeat monitor sleep')
+ gd(c, 'WAIT', 'GCR sleep')
+ gd(c, 'WAIT', 'SGA: MMAN sleep for component shrink')
+ gd(c, 'WAIT', 'MRP redo arrival')
+ gd(c, 'WAIT', 'LNS ASYNC archive log')
+ gd(c, 'WAIT', 'LNS ASYNC dest activation')
+ gd(c, 'WAIT', 'LNS ASYNC end of log')
+ gd(c, 'WAIT', 'simulated log write delay')
+ gd(c, 'WAIT', 'LGWR real time apply sync')
+ gd(c, 'WAIT', 'parallel recovery slave idle wait')
+ gd(c, 'WAIT', 'LogMiner builder: idle')
+ gd(c, 'WAIT', 'LogMiner builder: branch')
+ gd(c, 'WAIT', 'LogMiner preparer: idle')
+ gd(c, 'WAIT', 'LogMiner reader: log (idle)')
+ gd(c, 'WAIT', 'LogMiner reader: redo (idle)')
+ gd(c, 'WAIT', 'LogMiner client: transaction')
+ gd(c, 'WAIT', 'LogMiner: other')
+ gd(c, 'WAIT', 'LogMiner: activate')
+ gd(c, 'WAIT', 'LogMiner: reset')
+ gd(c, 'WAIT', 'LogMiner: find session')
+ gd(c, 'WAIT', 'LogMiner: internal')
+ gd(c, 'WAIT', 'Logical Standby Apply Delay')
+ gd(c, 'WAIT', 'parallel recovery coordinator waits for slave cleanup')
+ gd(c, 'WAIT', 'parallel recovery control message reply')
+ gd(c, 'WAIT', 'parallel recovery slave next change')
+ gd(c, 'WAIT', 'PX Deq: Txn Recovery Start')
+ gd(c, 'WAIT', 'PX Deq: Txn Recovery Reply')
+ gd(c, 'WAIT', 'fbar timer')
+ gd(c, 'WAIT', 'smon timer')
+ gd(c, 'WAIT', 'PX Deq: Metadata Update')
+ gd(c, 'WAIT', 'Space Manager: slave idle wait')
+ gd(c, 'WAIT', 'PX Deq: Index Merge Reply')
+ gd(c, 'WAIT', 'PX Deq: Index Merge Execute')
+ gd(c, 'WAIT', 'PX Deq: Index Merge Close')
+ gd(c, 'WAIT', 'PX Deq: kdcph_mai')
+ gd(c, 'WAIT', 'PX Deq: kdcphc_ack')
+ gd(c, 'WAIT', 'shared server idle wait')
+ gd(c, 'WAIT', 'dispatcher timer')
+ gd(c, 'WAIT', 'cmon timer')
+ gd(c, 'WAIT', 'pool server timer')
+ gd(c, 'WAIT', 'JOX Jit Process Sleep')
+ gd(c, 'WAIT', 'jobq slave wait')
+ gd(c, 'WAIT', 'pipe get')
+ gd(c, 'WAIT', 'PX Deque wait')
+ gd(c, 'WAIT', 'PX Idle Wait')
+ gd(c, 'WAIT', 'PX Deq: Join ACK')
+ gd(c, 'WAIT', 'PX Deq Credit: need buffer')
+ gd(c, 'WAIT', 'PX Deq Credit: send blkd')
+ gd(c, 'WAIT', 'PX Deq: Msg Fragment')
+ gd(c, 'WAIT', 'PX Deq: Parse Reply')
+ gd(c, 'WAIT', 'PX Deq: Execute Reply')
+ gd(c, 'WAIT', 'PX Deq: Execution Msg')
+ gd(c, 'WAIT', 'PX Deq: Table Q Normal')
+ gd(c, 'WAIT', 'PX Deq: Table Q Sample')
+ gd(c, 'WAIT', 'Streams fetch slave: waiting for txns')
+ gd(c, 'WAIT', 'Streams: waiting for messages')
+ gd(c, 'WAIT', 'Streams capture: waiting for archive log')
+ gd(c, 'WAIT', 'single-task message')
+ gd(c, 'WAIT', 'SQL*Net message from client')
+ gd(c, 'WAIT', 'SQL*Net vector message from client')
+ gd(c, 'WAIT', 'SQL*Net vector message from dblink')
+ gd(c, 'WAIT', 'PL/SQL lock timer')
+ gd(c, 'WAIT', 'Streams AQ: emn coordinator idle wait')
+ gd(c, 'WAIT', 'EMON slave idle wait')
+ gd(c, 'WAIT', 'Streams AQ: waiting for messages in the queue')
+ gd(c, 'WAIT', 'Streams AQ: waiting for time management or cleanup tasks')
+ gd(c, 'WAIT', 'Streams AQ: delete acknowledged messages')
+ gd(c, 'WAIT', 'Streams AQ: deallocate messages from Streams Pool')
+ gd(c, 'WAIT', 'Streams AQ: qmn coordinator idle wait')
+ gd(c, 'WAIT', 'Streams AQ: qmn slave idle wait')
+ gd(c, 'WAIT', 'Streams AQ: RAC qmn coordinator idle wait')
+ gd(c, 'WAIT', 'HS message to agent')
+ gd(c, 'WAIT', 'ASM background timer')
+ gd(c, 'WAIT', 'auto-sqltune: wait graph update')
+ gd(c, 'WAIT', 'WCR: replay client notify')
+ gd(c, 'WAIT', 'WCR: replay clock')
+ gd(c, 'WAIT', 'WCR: replay paused')
+ gd(c, 'WAIT', 'JS external job')
+ gd(c, 'WAIT', 'cell worker idle')
)) / (get_seconds(d2 - d1)*1000000) * 100
, 'STAT'), 10) || ' % unaccounted time' ;
else null;
end case; -- mt=time, mn
else null;
end case; -- mt
return ret;
end get_useful_average;
/*---------------------------------------------------
-- function for converting large numbers to human-readable format
---------------------------------------------------*/
function tptformat( p_num in number,
p_stype in varchar2 default 'STAT',
p_precision in number default 2,
p_base in number default 10, -- for KiB/MiB formatting use
p_grouplen in number default 3 -- p_base=2 and p_grouplen=10
)
return varchar2
is
begin
if p_num = 0 then return '0'; end if;
if p_num IS NULL then return '~'; end if;
if p_stype in ('WAIT','TIME') then
return
round(
p_num / power( p_base , trunc(log(p_base,abs(p_num)))-trunc(mod(log(p_base,abs(p_num)),p_grouplen)) ), p_precision
)
|| case trunc(log(p_base,abs(p_num)))-trunc(mod(log(p_base,abs(p_num)),p_grouplen))
when 0 then 'us'
when 1 then 'us'
when p_grouplen*1 then 'ms'
when p_grouplen*2 then 's'
when p_grouplen*3 then 'ks'
when p_grouplen*4 then 'Ms'
else '*'||p_base||'^'||to_char( trunc(log(p_base,abs(p_num)))-trunc(mod(log(p_base,abs(p_num)),p_grouplen)) )||' us'
end;
else
return
round(
p_num / power( p_base , trunc(log(p_base,abs(p_num)))-trunc(mod(log(p_base,abs(p_num)),p_grouplen)) ), p_precision
)
|| case trunc(log(p_base,abs(p_num)))-trunc(mod(log(p_base,abs(p_num)),p_grouplen))
when 0 then ''
when 1 then ''
when p_grouplen*1 then 'k'
when p_grouplen*2 then 'M'
when p_grouplen*3 then 'G'
when p_grouplen*4 then 'T'
when p_grouplen*5 then 'P'
when p_grouplen*6 then 'E'
else '*'||p_base||'^'||to_char( trunc(log(p_base,abs(p_num)))-trunc(mod(log(p_base,abs(p_num)),p_grouplen)) )
end;
end if;
end; -- tptformat
/*---------------------------------------------------
-- simple function for parsing arguments from parameter string
---------------------------------------------------*/
function getopt( p_parvalues in varchar2,
p_extract in varchar2,
p_delim in varchar2 default ','
) return varchar2
is
ret varchar(1000) := NULL;
begin
-- dbms_output.put('p_parvalues = ['||p_parvalues||'] ' );
-- dbms_output.put('p_extract = ['||p_extract||'] ' );
if lower(p_parvalues) like lower(p_extract)||'%'