-
Notifications
You must be signed in to change notification settings - Fork 5
/
Copy pathsnapper.sql
1777 lines (1554 loc) · 109 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
-- Purpose: An easy to use Oracle session-level performance snapshot utility
--
-- NB! This script does NOT require 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 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).
--
-- The output is formatted the way it could be easily post-processed
-- by either Unix string manipulation tools or loaded to spreadsheet.
--
--
-- Author: Tanel Poder
-- Copyright: (c) E2SN - http://tech.e2sn.com - All rights reserved.
--
--
-- Thanks to: Adrian Billington and Jamey Johnston for sending some useful fixes
--
--------------------------------------------------------------------------------
--
-- The Session Snapper v3.15
-- (c) Tanel Poder ( http://tech.e2sn.com )
--
--
-- +-----=====O=== Welcome to The Session Snapper! (Yes, you are looking at a cheap ASCII
-- / imitation of a fish and a fishing rod.
-- | Nevertheless the PL/SQL code below the
-- | fish itself should be helpful for quick
-- | catching of relevant Oracle performance
-- | information.
-- | So I wish you happy... um... snapping?
-- | )
-- | ......
-- | iittii,,....
-- ¿ iiffffjjjjtttt,,
-- ..;;ttffLLLLffLLLLLLffjjtt;;..
-- ..ttLLGGGGGGLLffLLLLLLLLLLLLLLffjjii,, ..ii,,
-- ffGGffLLLLLLjjttjjjjjjjjffLLLLLLLLLLjjii.. ..iijj;;....
-- ffGGLLiittjjttttttiittttttttttffLLLLLLGGffii.. ;;LLLLii;;;;..
-- ffEEGGffiittiittttttttttiiiiiiiittjjjjffLLGGLLii.. iiLLLLLLttiiii,,
-- ;;ffDDLLiiiitt,,ttttttttttttiiiiiiiijjjjjjffLLLLffttiiiiffLLGGLLjjtttt;;..
-- ..ttttjjiitt,,iiiiiittttttttjjjjttttttttjjjjttttjjttttjjjjffLLDDGGLLttii..
-- iittiitttt, ;;iittttttttjjjjjjjjjjttjjjjjjffffffjjjjjjjjjjLLDDGGLLtt;;..
-- jjjjttttii:. ..iiiiffLLGGLLLLLLLLffffffLLLLLLLLLLLLLLLLffffffLLLLLLfftt,,
-- iittttii,,;;,,ttiiiiLLLLffffffjjffffLLLLLLLLffLLffjjttttttttttjjjjffjjii..
-- ,,iiiiiiiiiittttttiiiiiiiiiijjffffLLLLLLLLffLLffttttttii;;;;iiiitttttttt;;..
-- ..iittttttffffttttiiiiiiiiiittttffjjjjffffffffttiittii:: ....,,;;iittii;;
-- ..;;iittttttttttttttttiiiiiittttttttttjjjjjjtttttt;; ..;;ii;;..
-- ..;;;;iittttttjjttiittttttttttttttjjttttttttii.. ....
-- ....;;;;ttjjttttiiiiii;;;;;;iittttiiii..
-- ..;;ttttii;;.... ..;;;;....
-- ..iiii;;..
-- ..;;,,
-- ....
--
--
-- 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 v$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 v$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 + (as comma is a snapper parameter
-- separator, not 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 v$sesstat,v$sess_time_model,v$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 v$sesstat
-- t - Session Time model info from v$sess_time_model
-- w - Session Wait statistics from v$session_event and v$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 V$SESSTAT stats which match the
-- LIKE pattern of sinclude (REGEXP_LIKE in 10g+)
-- linclude - if specified, then show only V$LATCH latch stats which match the
-- LIKE pattern of linclude (REGEXP_LIKE in 10g+)
-- tinclude - if specified, then show only V$SESS_TIME_MODEL stats which match the
-- LIKE pattern of tinclude (REGEXP_LIKE in 10g+)
-- winclude - if specified, then show only V$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 used "select sid from v$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 v$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 v$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, v$sesstat and v$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 v$sesstat and v$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 299
-- Get parameters
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
-- different Oracle versions, script parameters or granted privileges some
-- statements might not compile if not adjusted properly.
define _IF_ORA10_OR_HIGHER="--"
define _IF_ORA11_OR_HIGHER="--"
define _IF_LOWER_THAN_ORA11="--"
define _IF_DBMS_SYSTEM_ACCESSIBLE="/* dbms_system is not accessible" /*dummy*/
define _IF_X_ACCESSIBLE="--"
define _YES_PLSQL_OBJ_ID="--" -- plsql_object_id columns available in v$session (from 10.2.0.3)
define _NO_PLSQL_OBJ_ID=""
define _YES_BLK_INST="--" -- blocking_instance available in v$session (from 10.2)
define _NO_BLK_INST=""
col snapper_ora9 noprint new_value _IF_ORA9
col snapper_ora10higher noprint new_value _IF_ORA10_OR_HIGHER
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 snapper_sid noprint new_value snapper_sid
-- 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)
var v varchar2(100)
var x varchar2(10)
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;
begin
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 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
select * from (
select
case
when trim(lower('&ssid_begin')) like 'sid=%' then trim(replace('&ssid_begin','sid=',''))
when trim(lower('&ssid_begin')) like 'user=%' then 'select sid from v$session where lower(username) like '''||lower(trim(replace('&ssid_begin','user=','')))||''''
when trim(lower('&ssid_begin')) like 'username=%' then 'select sid from v$session where lower(username) like '''||lower(trim(replace('&ssid_begin','username=','')))||''''
when trim(lower('&ssid_begin')) like 'machine=%' then 'select sid from v$session where lower(machine) like '''||lower(trim(replace('&ssid_begin','machine=','')))||''''
when trim(lower('&ssid_begin')) like 'program=%' then 'select sid from v$session where lower(program) like '''||lower(trim(replace('&ssid_begin','program=','')))||''''
when trim(lower('&ssid_begin')) like 'service=%' then 'select sid from v$session where lower(service_name) like '''||lower(trim(replace('&ssid_begin','service=','')))||''''
when trim(lower('&ssid_begin')) like 'module=%' then 'select sid from v$session where lower(module) like '''||lower(trim(replace('&ssid_begin','module=','')))||''''
when trim(lower('&ssid_begin')) like 'action=%' then 'select sid from v$session where lower(action) like '''||lower(trim(replace('&ssid_begin','action=','')))||''''
when trim(lower('&ssid_begin')) like 'osuser=%' then 'select sid from v$session where lower(osuser) like '''||lower(trim(replace('&ssid_begin','osuser=','')))||''''
when trim(lower('&ssid_begin')) like 'client_id=%' then 'select sid from v$session where lower(client_identifier) like '''||lower(trim(replace('&ssid_begin','client_id=','')))||''''
when trim(lower('&ssid_begin')) like 'spid=%' then 'select sid from v$session where paddr in (select addr from v$process where spid in ('||lower(trim(replace('&ssid_begin','spid=','')))||'))'
when trim(lower('&ssid_begin')) like 'ospid=%' then 'select sid from v$session where paddr in (select addr from v$process where spid in ('||lower(trim(replace('&ssid_begin','ospid=','')))||'))'
when trim(lower('&ssid_begin')) like 'pid=%' then 'select sid from v$session where paddr in (select addr from v$process where spid in ('||lower(trim(replace('&ssid_begin','pid=','')))||'))'
when trim(lower('&ssid_begin')) like 'qcsid=%' then 'select sid from v$px_session where qcsid in ('||lower(trim(replace('&ssid_begin','qcsid=','')))||')'
when trim(lower('&ssid_begin')) like 'qc=%' then 'select sid from v$px_session where qcsid in ('||lower(trim(replace('&ssid_begin','qc=','')))||')'
when trim(lower('&ssid_begin')) = 'all' then 'select sid from v$session'
when trim(lower('&ssid_begin')) = 'bg' then 'select sid from v$session where type=''BACKGROUND'''
when trim(lower('&ssid_begin')) = 'fg' then 'select sid from v$session where type=''USER'''
when trim(lower('&ssid_begin')) = 'lgwr' then 'select sid from v$session where program like ''%(LGWR)%'''
when trim(lower('&ssid_begin')) = 'dbwr' then 'select sid from v$session where program like ''%(DBW%)%'''
when trim(lower('&ssid_begin')) like 'select%' then null
when trim(lower('&ssid_begin')) like 'with%' then null
else null
end snapper_sid -- put the result back to snapper_sid sqlplus value (if its not null)
from
dual
)
where
snapper_sid is not null -- snapper_sid sqlplus variable value will not be replaced if this query doesnt return any rows
/
-- 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
decode(substr(banner, instr(banner, 'Release ')+8,2), '09', '', '--') snapper_ora9,
decode(substr(banner, instr(banner, 'Release ')+8,1), '1', '', '--') snapper_ora10higher,
decode(substr(banner, instr(banner, 'Release ')+8,2), '11', '', '--') snapper_ora11higher,
decode(substr(banner, instr(banner, 'Release ')+8,2), '11', '--', '') 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
from
mod_banner
/
set termout on serverout on size 1000000 format wrapped
prompt Sampling SID &4 with interval &snapper_sleep seconds, taking &snapper_count snapshots...
-- main()
-- let the Snapping start!!!
declare
-- 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 (stype varchar2(4), sid number, statistic# number, value number );
type stab is table of srec index by pls_integer;
s1 stab;
s2 stab;
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 sestab is table of v$session%rowtype index by pls_integer;
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_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;
changed_values number;
pagesize number:=99999999999999;
missing_values_s1 number := 0;
missing_values_s2 number := 0;
disappeared_sid number := 0;
d1 date;
d2 date;
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;
-- CONFIGURABLE STUFF --
-- this sets what are the default ash sample TOP reporting group by columns
g_ash_columns varchar2(1000) := 'sql_id + child + event + wait_class';
g_ash_columns1 varchar2(1000) := 'event + wait_class';
g_ash_columns2 varchar2(1000) := 'sid + username + sql_id';
g_ash_columns3 varchar2(1000) := '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_sid number := 1; -- sid
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 := 0; -- raw delta
output_delta_s number := 0; -- raw delta normalized to per second
output_hdelta number := 1; -- 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_pcthist number := 1; -- percent of total visual bar (histogram)
-- column widths in ASH report output
w_sid number := 6;
w_username number := 20;
w_machine number := 20;
w_terminal number := 20;
w_program number := 35;
w_event number := 25;
w_wait_class number := 15;
w_state number := 8;
w_p1 number := 16;
w_p2 number := 16;
w_p3 number := 16;
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 := 10;
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_sid constant number := 1 ;
s_username constant number := 2 ;
s_machine constant number := 3 ;
s_terminal constant number := 4 ;
s_program constant number := 5 ;
s_event constant number := 6 ;
s_wait_class constant number := 7 ;
s_state constant number := 8 ;
s_p1 constant number := 9 ;
s_p2 constant number := 10 ;
s_p3 constant number := 11 ;
s_row_wait_obj# constant number := 12 ;
s_row_wait_file# constant number := 13 ;
s_row_wait_block# constant number := 14 ;
s_row_wait_row# constant number := 15 ;
s_blocking_session_status constant number := 16 ;
s_blocking_instance constant number := 17 ;
s_blocking_session constant number := 18 ;
s_sql_hash_value constant number := 19 ;
s_sql_id constant number := 20 ;
s_sql_child_number constant number := 21 ;
s_plsql_entry_object_id constant number := 22 ;
s_plsql_entry_subprogram_id constant number := 23 ;
s_plsql_object_id constant number := 24 ;
s_plsql_subprogram_id constant number := 25 ;
s_module constant number := 26 ;
s_action constant number := 27 ;
s_client_identifier constant number := 28 ;
s_service_name constant number := 29 ;
-- constants for ash collection reporting, which columns to show in report
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 );
-- bitfield specifying which columns to group by in sampled session activity (ASH)
g_ash_grouping number := 63; -- test
/*---------------------------------------------------
-- 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
/*---------------------------------------------------
-- proc for outputting data, utilizing global vars
---------------------------------------------------*/
procedure fout is
l_output_username VARCHAR2(100);
begin
-- 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);
if output_username = 1 then
begin
l_output_username := nvl( g_sessions(s2(b).sid).username, substr(g_sessions(s2(b).sid).program, instr(g_sessions(s2(b).sid).program,'(')) );
exception
when no_data_found then l_output_username := 'error';
when others then raise;
end;
end if;
output( CASE WHEN output_header = 1 THEN 'SID= ' END
|| CASE WHEN output_sid = 1 THEN to_char(s2(b).sid,'999999')||', ' END
|| CASE WHEN output_username = 1 THEN rpad(CASE s2(b).sid WHEN -1 THEN ' ' ELSE 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 (d2-d1) when 0 then &snapper_sleep else (d2-d1) * 86400 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 (d2-d1) when 0 then &snapper_sleep else (d2-d1) * 86400 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 (d2-d1) when 0 then &snapper_sleep else (d2-d1)* 86400 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 (d2-d1) WHEN 0 THEN &snapper_sleep ELSE (d2-d1) * 86400 END / 10000, '9999.9')||'%,' END END
|| CASE WHEN output_pcthist = 1 THEN CASE WHEN s2(b).stype IN ('TIME','WAIT') THEN rpad(' '||rpad('|', ceil(round(delta/CASE (d2-d1) WHEN 0 THEN &snapper_sleep ELSE (d2-d1) * 86400 END / 100000,1))+1, '@'),12,' ')||'|' END END
);
end;
/*---------------------------------------------------
-- 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_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)||'%'
or lower(p_parvalues) like '%'||p_delim||lower(p_extract)||'%' then
ret :=
nvl (
substr(p_parvalues,
instr(p_parvalues, p_extract)+length(p_extract),
case
instr(
substr(p_parvalues,
instr(p_parvalues, p_extract)+length(p_extract)
)
, p_delim
)
when 0 then length(p_parvalues)
else
instr(
substr(p_parvalues,
instr(p_parvalues, p_extract)+length(p_extract)
)
, p_delim
) - 1
end
)
, chr(0) -- in case parameter was specified but with no value
);
else
ret := null; -- no parameter found
end if;
-- dbms_output.put_line('ret = ['||replace(ret,chr(0),'\0')||']');
return ret;
end; -- getopt
/*---------------------------------------------------
-- proc for getting session list with username, osuser, machine etc
---------------------------------------------------*/
procedure get_sessions is
tmp_sessions sestab;
begin
select
*
bulk collect into
tmp_sessions
from
v$session
where
sid in (&snapper_sid);
g_sessions := g_empty_sessions;
for i in 1..tmp_sessions.count loop
g_sessions(tmp_sessions(i).sid) := tmp_sessions(i);
end loop;
end; -- get_sessions
/*---------------------------------------------------
-- function for getting session list with username, osuser, machine etc
-- this func does not update the g_sessions global array but returns session info as return value
---------------------------------------------------*/
function get_sessions return sestab is
tmp_sessions sestab;
l_return_sessions sestab;
begin
select
*
bulk collect into
tmp_sessions
from
v$session
where
sid in (&snapper_sid);
for i in 1..tmp_sessions.count loop
--output('get_sessions i='||i||' sid='||tmp_sessions(i).sid);
l_return_sessions(tmp_sessions(i).sid) := tmp_sessions(i);
end loop;
return l_return_sessions;
end; -- get_sessions
/*---------------------------------------------------
-- functions for extracting and converting v$session
-- records to varchar2
---------------------------------------------------*/
function sitem(p in varchar2) return varchar2 as
begin
return '<'||translate(p, '<>', '__')||'>';
end; -- sitem varchar2
function sitem(p in number) return varchar2 as
begin
return '<'||to_char(p)||'>';
end; -- sitem number
function sitem(p in date) return varchar2 as
begin
return '<'||to_char(p, 'YYYY-MM-DD HH24:MI:SS')||'>';
end; -- sitem date
function sitem_raw(p in raw) return varchar2 as
begin
return '<'||upper(rawtohex(p))||'>';
end; -- sitem_raw
/*---------------------------------------------------
-- proc for resetting the snapper ash array
---------------------------------------------------*/
procedure reset_ash is
begin
g_ash_samples_taken := 0;
-- clear g_ash
g_ash := new sys.dbms_debug_vc2coll();
end; -- reset_ash
/*---------------------------------------------------
-- proc for getting ash style samples from v$session
-- (and v$session_wait in 9i)
---------------------------------------------------*/
procedure extract_ash is
ash_i number;
s v$session%rowtype;
begin
-- keep track how many times we sampled v$session so we could calculate averages later on
g_ash_samples_taken := g_ash_samples_taken + 1;
--output('g_sessions.count='||g_sessions.count);
ash_i := g_sessions.first;
while ash_i is not null loop
s := g_sessions(ash_i);
-- only extract active sessions, TODO: get rid of wait_class for 9i compatibility
if -- active, on cpu
(s.status = 'ACTIVE' and s.state != 'WAITING' and s.sid != g_mysid)
or -- active, waiting for non-idle wait
(s.status = 'ACTIVE' and s.state = 'WAITING' and s.wait_class != 'Idle' and s.sid != g_mysid)
then
--output('extract_ash: i='||i||' sid='||s.sid||' hv='||s.sql_hash_value||' sqlid='||s.sql_id);
-- if not actually waiting for anything, clear the past wait event details
if s.state != 'WAITING' then
s.state:='ON CPU';
s.event:='ON CPU';
s.wait_class:='ON CPU';
s.p1:=NULL;
s.p2:=NULL;
s.p3:=NULL;
end if;
g_ash.extend;
-- max length 1000 bytes (due to dbms_debug_vc2coll)
g_ash(g_ash.count) := substr(
sitem(s.sid) -- 1
||sitem(s.username) -- 2 -- 30 bytes
||sitem(s.machine) -- 3 -- 64 bytes
||sitem(s.terminal) -- 4 -- 30 bytes
||sitem(s.program) -- 5 -- 48 bytes
||sitem(s.event) -- 6 -- 64 bytes
||sitem(s.wait_class) -- 7 -- 64 bytes, 10g+
||sitem(s.state) -- 8
||sitem(s.p1) -- 9
||sitem(s.p2) -- 10
||sitem(s.p3) -- 11
||sitem(s.row_wait_obj#) -- 12
||sitem(s.row_wait_file#) -- 13
||sitem(s.row_wait_block#) -- 14
||sitem(s.row_wait_row#) -- 15
||sitem(s.blocking_session_status) -- 16 -- 10g+
&_NO_BLK_INST ||sitem('N/A') -- 17 -- 10gR2+
&_YES_BLK_INST ||sitem(s.blocking_instance) -- 17 -- 10gR2+
||sitem(s.blocking_session) -- 18 -- 10g+
||sitem(s.sql_hash_value) -- 19
||sitem(s.sql_id) -- 20
||sitem(s.sql_child_number) -- 21 -- 10g+
&_NO_PLSQL_OBJ_ID ||sitem('N/A') -- 22
&_NO_PLSQL_OBJ_ID ||sitem('N/A') -- 23
&_NO_PLSQL_OBJ_ID ||sitem('N/A') -- 24
&_NO_PLSQL_OBJ_ID ||sitem('N/A') -- 25
&_YES_PLSQL_OBJ_ID ||sitem(s.plsql_entry_object_id) -- 22
&_YES_PLSQL_OBJ_ID ||sitem(s.plsql_entry_subprogram_id) -- 23
&_YES_PLSQL_OBJ_ID ||sitem(s.plsql_object_id) -- 24
&_YES_PLSQL_OBJ_ID ||sitem(s.plsql_subprogram_id) -- 25
||sitem(s.module) -- 26 -- 48 bytes
||sitem(s.action) -- 27 -- 32 bytes
||sitem(s.client_identifier) -- 28 -- 64 bytes
||sitem(s.service_name) -- 29 -- 64 bytes
, 1, 1000);
end if; -- sample is of an active session
ash_i := g_sessions.next(ash_i);
end loop;
exception
when no_data_found then output('error in extract_ash(): no_data_found for item '||i);
end; -- extract_ash
/*---------------------------------------------------
-- proc for querying performance data into collections
---------------------------------------------------*/
procedure snap( p_snapdate in out date, p_stats in out stab ) is
lv_include_stat varchar2(1000) := nvl( lower(getopt('&snapper_options', 'sinclude=' )), '%');
lv_include_latch varchar2(1000) := nvl( lower(getopt('&snapper_options', 'linclude=' )), '%');
lv_include_time varchar2(1000) := nvl( lower(getopt('&snapper_options', 'tinclude=' )), '%');
lv_include_wait varchar2(1000) := nvl( lower(getopt('&snapper_options', 'winclude=' )), '%');
begin
p_snapdate := sysdate;
select *
bulk collect into p_stats
from (
select 'STAT' stype, sid, statistic# - pls_adjust statistic#, value
from v$sesstat
where sid in (&snapper_sid)
and (lv_gather like '%s%' or lv_gather like '%a%')
and statistic# in (select /*+ no_unnest */ statistic# from v$statname
where lower(name) like '%'||lv_include_stat||'%'
&_IF_ORA10_OR_HIGHER or regexp_like (name, lv_include_stat, 'i')
)
--
union all
select
'WAIT', sw.sid,
en.event# + (select count(*) from v$statname) + 1 - pls_adjust,
nvl(se.time_waited_micro,0) + ( decode(se.event||sw.state, sw.event||'WAITING', sw.seconds_in_wait, 0) * 1000000 ) value
from v$session_wait sw, v$session_event se, v$event_name en
where sw.sid = se.sid
and se.event = en.name
and se.sid in (&snapper_sid)
and (lv_gather like '%w%' or lv_gather like '%a%')
and event# in (select event# from v$event_name
where lower(name) like '%'||lv_include_wait||'%'
&_IF_ORA10_OR_HIGHER or regexp_like (name, lv_include_wait, 'i')
)
--
&_IF_ORA10_OR_HIGHER union all
&_IF_ORA10_OR_HIGHER select 'TIME' stype, sid, stat_id - pls_adjust statistic#, value
&_IF_ORA10_OR_HIGHER from v$sess_time_model
&_IF_ORA10_OR_HIGHER where sid in (&snapper_sid)
&_IF_ORA10_OR_HIGHER and (lv_gather like '%t%' or lv_gather like '%a%')
&_IF_ORA10_OR_HIGHER and stat_id in (select stat_id from v$sys_time_model
&_IF_ORA10_OR_HIGHER where lower(stat_name) like '%'||lv_include_time||'%'
&_IF_ORA10_OR_HIGHER or regexp_like (stat_name, lv_include_time, 'i')
&_IF_ORA10_OR_HIGHER )
--
union all
select 'LATG', -1 sid,
l.latch# +
(select count(*) from v$statname) +
(select count(*) from v$event_name) +
1 - pls_adjust statistic#,
l.gets + l.immediate_gets value
from v$latch l
where
(lv_gather like '%l%' or lv_gather like '%a%')
and latch# in (select latch# from v$latchname
where lower(name) like '%'||lv_include_latch||'%'
&_IF_ORA10_OR_HIGHER or regexp_like (name, lv_include_latch, 'i')
)
--
&_IF_X_ACCESSIBLE &_IF_LOWER_THAN_ORA11 union all
&_IF_X_ACCESSIBLE &_IF_LOWER_THAN_ORA11 select 'BUFG', -1 sid,
&_IF_X_ACCESSIBLE &_IF_LOWER_THAN_ORA11 s.indx +
&_IF_X_ACCESSIBLE &_IF_LOWER_THAN_ORA11 (select count(*) from v$statname) +
&_IF_X_ACCESSIBLE &_IF_LOWER_THAN_ORA11 (select count(*) from v$event_name) +
&_IF_X_ACCESSIBLE &_IF_LOWER_THAN_ORA11 (select count(*) from v$latch) +
&_IF_X_ACCESSIBLE &_IF_LOWER_THAN_ORA11 1 - pls_adjust statistic#,
&_IF_X_ACCESSIBLE &_IF_LOWER_THAN_ORA11 s.why0+s.why1+s.why2 value
&_IF_X_ACCESSIBLE &_IF_LOWER_THAN_ORA11 from x$kcbsw s, x$kcbwh w
&_IF_X_ACCESSIBLE &_IF_LOWER_THAN_ORA11 where
&_IF_X_ACCESSIBLE &_IF_LOWER_THAN_ORA11 s.indx = w.indx
&_IF_X_ACCESSIBLE &_IF_LOWER_THAN_ORA11 and s.why0+s.why1+s.why2 > 0
&_IF_X_ACCESSIBLE &_IF_LOWER_THAN_ORA11 and (lv_gather like '%b%' or lv_gather like '%a%')
--
&_IF_X_ACCESSIBLE &_IF_ORA11_OR_HIGHER union all
&_IF_X_ACCESSIBLE &_IF_ORA11_OR_HIGHER select 'BUFG', -1 sid,
&_IF_X_ACCESSIBLE &_IF_ORA11_OR_HIGHER sw.indx +
&_IF_X_ACCESSIBLE &_IF_ORA11_OR_HIGHER (select count(*) from v$statname) +
&_IF_X_ACCESSIBLE &_IF_ORA11_OR_HIGHER (select count(*) from v$event_name) +
&_IF_X_ACCESSIBLE &_IF_ORA11_OR_HIGHER (select count(*) from v$latch) +