-
Notifications
You must be signed in to change notification settings - Fork 64
/
cs_ash_analytics.sql
executable file
·1607 lines (1607 loc) · 91.8 KB
/
cs_ash_analytics.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: aa.sql | cs_ash_analytics.sql
--
-- Purpose: Poor-man's version of ASH Analytics for all Timed Events (Average Active Sessions AAS)
--
-- Author: Carlos Sierra
--
-- Version: 2022/06/15
--
-- Usage: Execute connected to CDB or PDB
--
-- Enter range of dates and filters when requested.
--
-- Example: $ sqlplus / as sysdba
-- SQL> @cs_ash_analytics.sql
--
-- Notes: Developed and tested on 12.1.0.2.
--
---------------------------------------------------------------------------------------
--
@@cs_internal/cs_primary.sql
@@cs_internal/cs_set.sql
@@cs_internal/cs_def.sql
@@cs_internal/cs_file_prefix.sql
--
DEF cs_script_name = 'cs_ash_analytics';
DEF cs_script_acronym = 'aa.sql | ';
--
DEF cs_hours_range_default = '3';
--
@@cs_internal/cs_sample_time_from_and_to.sql
@@cs_internal/cs_snap_id_from_and_to.sql
--
--@@cs_internal/&&cs_set_container_to_cdb_root.
--
COL cs_ash_cut_off_date NEW_V cs_ash_cut_off_date NOPRI;
SELECT TO_CHAR(CAST(PERCENTILE_DISC(0.05) WITHIN GROUP (ORDER BY sample_time) AS DATE) + (1/24), 'YYYY-MM-DD"T"HH24:MI') AS cs_ash_cut_off_date FROM v$active_session_history;
--SELECT TO_CHAR(TRUNC(TRUNC(SYSDATE, 'HH') + FLOOR(TO_NUMBER(TO_CHAR(SYSDATE, 'MI')) / 15) * 15 / (24*60), 'MI'), 'YYYY-MM-DD"T"HH24:MI') AS cs_ash_cut_off_date FROM DUAL;
--
COL cs2_granularity_list NEW_V cs2_granularity_list NOPRI;
COL cs2_default_granularity NEW_V cs2_default_granularity NOPRI;
SELECT CASE
WHEN TO_NUMBER('&&cs_from_to_seconds.') / 3660 <= 0.2 AND TO_DATE('&&cs_sample_time_to.', '&&cs_datetime_full_format.') < TO_DATE('&&cs_ash_cut_off_date.', 'YYYY-MM-DD"T"HH24:MI') THEN '[{10s}|1s|5s|10s|15s|1m|5m|15m|1h|1d|s|m|h|d]' -- < 12m (up to 72 samples)
WHEN TO_NUMBER('&&cs_from_to_seconds.') / 3660 <= 0.2 THEN '[{1s}|1s|5s|10s|15s|1m|5m|15m|1h|1d|s|m|h|d]' -- < 12m (up to 720 samples)
WHEN TO_NUMBER('&&cs_from_to_seconds.') / 3600 <= 1 AND TO_DATE('&&cs_sample_time_to.', '&&cs_datetime_full_format.') < TO_DATE('&&cs_ash_cut_off_date.', 'YYYY-MM-DD"T"HH24:MI') THEN '[{10s}|1s|5s|10s|15s|1m|5m|15m|1h|1d|s|m|h|d]' -- < 1h (up to 360 samples)
WHEN TO_NUMBER('&&cs_from_to_seconds.') / 3600 <= 1 THEN '[{5s}|1s|5s|10s|15s|1m|5m|15m|1h|1d|s|m|h|d]' -- < 1h (up to 720 samples)
WHEN TO_NUMBER('&&cs_from_to_seconds.') / 3600 <= 2 THEN '[{10s}|1s|5s|10s|15s|1m|5m|15m|1h|1d|s|m|h|d]' -- < 2h (up to 720 samples)
WHEN TO_NUMBER('&&cs_from_to_seconds.') / 3600 <= 3 THEN '[{15s}|1s|5s|10s|15s|1m|5m|15m|1h|1d|s|m|h|d]' -- < 3h (up to 720 samples)
WHEN TO_NUMBER('&&cs_from_to_seconds.') / 3600 <= 12 THEN '[{1m}|1s|5s|10s|15s|1m|5m|15m|1h|1d|s|m|h|d]' -- < 12h (up to 720 samples)
WHEN TO_NUMBER('&&cs_from_to_seconds.') / 3600 <= 60 THEN '[{5m}|1s|5s|10s|15s|1m|5m|15m|1h|1d|s|m|h|d]' -- < 60h (2.5d) (up to 720 samples)
WHEN TO_NUMBER('&&cs_from_to_seconds.') / 3600 <= 180 THEN '[{15m}|1s|5s|10s|15s|1m|5m|15m|1h|1d|s|m|h|d]' -- < 180h (7.5d) (up to 720 samples)
WHEN TO_NUMBER('&&cs_from_to_seconds.') / 3600 <= 720 THEN '[{1h}|1s|5s|10s|15s|1m|5m|15m|1h|1d|s|m|h|d]' -- < 720h (30d) (up to 720 samples)
ELSE '[{1d}|1s|5s|10s|15s|1m|5m|15m|1h|1d|s|m|h|d]'
END AS cs2_granularity_list,
CASE
WHEN TO_NUMBER('&&cs_from_to_seconds.') / 3600 <= 0.2 AND TO_DATE('&&cs_sample_time_to.', '&&cs_datetime_full_format.') < TO_DATE('&&cs_ash_cut_off_date.', 'YYYY-MM-DD"T"HH24:MI') THEN '10s' -- < 12m (up to 72 samples)
WHEN TO_NUMBER('&&cs_from_to_seconds.') / 3600 <= 0.2 THEN '1s' -- < 12m (up to 720 samples)
WHEN TO_NUMBER('&&cs_from_to_seconds.') / 3600 <= 1 AND TO_DATE('&&cs_sample_time_to.', '&&cs_datetime_full_format.') < TO_DATE('&&cs_ash_cut_off_date.', 'YYYY-MM-DD"T"HH24:MI') THEN '10s' -- < 1h (up to 360 samples)
WHEN TO_NUMBER('&&cs_from_to_seconds.') / 3600 <= 1 THEN '5s' -- < 1h (up to 720 samples)
WHEN TO_NUMBER('&&cs_from_to_seconds.') / 3600 <= 2 THEN '10s' -- < 2h (up to 720 samples)
WHEN TO_NUMBER('&&cs_from_to_seconds.') / 3600 <= 3 THEN '15s' -- < 3h (up to 720 samples)
WHEN TO_NUMBER('&&cs_from_to_seconds.') / 3600 <= 12 THEN '1m' -- < 12h (up to 720 samples)
WHEN TO_NUMBER('&&cs_from_to_seconds.') / 3600 <= 60 THEN '5m' -- < 60h (2.5d) (up to 720 samples)
WHEN TO_NUMBER('&&cs_from_to_seconds.') / 3600 <= 180 THEN '15m' -- < 180h (7.5d) (up to 720 samples)
WHEN TO_NUMBER('&&cs_from_to_seconds.') / 3600 <= 720 THEN '1h' -- < 720h (30d) (up to 720 samples)
ELSE '1d'
END AS cs2_default_granularity
FROM DUAL
/
PRO
PRO 3. Granularity: &&cs2_granularity_list.
DEF cs2_granularity = '&3.';
UNDEF 3;
COL cs2_granularity NEW_V cs2_granularity NOPRI;
SELECT NVL(LOWER(TRIM('&&cs2_granularity.')), '&&cs2_default_granularity.') cs2_granularity FROM DUAL;
SELECT CASE
WHEN '&&cs2_granularity.' = 's' THEN '1s'
WHEN '&&cs2_granularity.' = 'm' THEN '1m'
WHEN '&&cs2_granularity.' = 'h' THEN '1h'
WHEN '&&cs2_granularity.' = 'd' THEN '1d'
WHEN '&&cs2_granularity.' IN ('1s', '5s', '10s', '15s', '1m', '5m', '15m', '1h', '1d') THEN '&&cs2_granularity.'
ELSE '&&cs2_default_granularity.'
END cs2_granularity
FROM DUAL
/
--
COL cs2_fmt NEW_V cs2_fmt NOPRI;
SELECT CASE '&&cs2_granularity.'
WHEN '1s' THEN 'SS' -- (1/24/3600) 1 second
WHEN '5s' THEN 'SS' -- (5/24/3600) 5 seconds
WHEN '10s' THEN 'SS' -- (10/24/3600) 10 seconds
WHEN '15s' THEN 'SS' -- (15/24/3600) 15 seconds
WHEN '1m' THEN 'MI' -- (1/24/60) 1 minute
WHEN '5m' THEN 'MI' -- (5/24/60) 5 minutes
WHEN '15m' THEN 'MI' -- (15/24/60) 15 minutes
WHEN '1h' THEN 'HH' -- (1/24) 1 hour
WHEN '1d' THEN 'DD' -- 1 day
ELSE 'XX' -- error
END cs2_fmt
FROM DUAL
/
--
COL cs2_plus_days NEW_V cs2_plus_days NOPRI;
SELECT CASE '&&cs2_granularity.'
WHEN '1s' THEN '(1/24/3600)' -- (1/24/3600) 1 second
WHEN '5s' THEN '(5/24/3600)' -- (5/24/3600) 5 seconds
WHEN '10s' THEN '(10/24/3600)' -- (10/24/3600) 10 seconds
WHEN '15s' THEN '(15/24/3600)' -- (15/24/3600) 15 seconds
WHEN '1m' THEN '(1/24/60)' -- (1/24/60) 1 minute
WHEN '5m' THEN '(5/24/60)' -- (5/24/60) 5 minutes
WHEN '15m' THEN '(15/24/60)' -- (15/24/60) 15 minutes
WHEN '1h' THEN '(1/24)' -- (1/24) 1 hour
WHEN '1d' THEN '1' -- 1 day
ELSE 'XX' -- error
END cs2_plus_days
FROM DUAL
/
--
COL cs2_samples NEW_V cs2_samples NOPRI;
SELECT TO_CHAR(CEIL((TO_DATE('&&cs_sample_time_to.', '&&cs_datetime_full_format.') - TO_DATE('&&cs_sample_time_from.', '&&cs_datetime_full_format.')) / &&cs2_plus_days.)) AS cs2_samples FROM DUAL
/
--
PRO
PRO 4. Reporting Dimension: [{event}|wait_class|machine|sql_id|plan_hash_value|top_level_sql_id|sid|blocking_session|current_obj#|module|program|sql_opname|appl_cat|pdb_name|p1|p2|p3]
DEF cs2_dimension = '&4.';
UNDEF 4;
COL cs2_dimension NEW_V cs2_dimension NOPRI;
-- SELECT NVL(LOWER(TRIM('&&cs2_dimension.')), 'event') cs2_dimension FROM DUAL;
SELECT CASE WHEN LOWER(TRIM('&&cs2_dimension.')) IN ('event', 'wait_class', 'machine', 'sql_id', 'plan_hash_value', 'top_level_sql_id', 'sid', 'blocking_session', 'current_obj#', 'module', 'program', 'sql_opname', 'appl_cat', 'pdb_name', 'p1', 'p2', 'p3') THEN LOWER(TRIM('&&cs2_dimension.')) ELSE 'event' END cs2_dimension FROM DUAL;
--
COL use_oem_colors_series NEW_V use_oem_colors_series NOPRI;
SELECT CASE '&&cs2_dimension.' WHEN 'wait_class' THEN NULL ELSE '//' END AS use_oem_colors_series FROM DUAL;
--
COL aas FOR 999,990.000 HEA 'Average Active|Sessions (AAS)';
COL db_seconds FOR 999,999,990 HEA 'DB Seconds';
COL session_state FOR A13 HEA 'Session|State';
BREAK ON REPORT;
COMPUTE SUM OF aas db_seconds ON REPORT;
--
WITH
ash_awr AS (
SELECT /*+ MATERIALIZE NO_MERGE */
h.session_state,
10 * COUNT(*) AS db_seconds
FROM dba_hist_active_sess_history h
WHERE h.sample_time <= TO_TIMESTAMP('&&cs_ash_cut_off_date.', 'YYYY-MM-DD"T"HH24:MI')
AND h.sample_time >= TO_TIMESTAMP('&&cs_sample_time_from.', '&&cs_datetime_full_format.')
AND h.sample_time < TO_TIMESTAMP('&&cs_sample_time_to.', '&&cs_datetime_full_format.')
AND h.dbid = TO_NUMBER('&&cs_dbid.')
AND h.instance_number = TO_NUMBER('&&cs_instance_number.')
AND h.snap_id BETWEEN TO_NUMBER('&&cs_snap_id_from.') AND TO_NUMBER('&&cs_snap_id_to.') + 1
GROUP BY
h.session_state
),
ash_mem AS (
SELECT /*+ MATERIALIZE NO_MERGE */
h.session_state,
1 * COUNT(*) AS db_seconds
FROM v$active_session_history h
WHERE h.sample_time > TO_TIMESTAMP('&&cs_ash_cut_off_date.', 'YYYY-MM-DD"T"HH24:MI')
AND h.sample_time >= TO_TIMESTAMP('&&cs_sample_time_from.', '&&cs_datetime_full_format.')
AND h.sample_time < TO_TIMESTAMP('&&cs_sample_time_to.', '&&cs_datetime_full_format.')
GROUP BY
h.session_state
),
ash_all AS (
SELECT session_state, db_seconds FROM ash_awr
UNION ALL
SELECT session_state, db_seconds FROM ash_mem
)
SELECT ROUND(SUM(db_seconds) / TO_NUMBER('&&cs_from_to_seconds.'), 3) AS aas,
SUM(db_seconds) AS db_seconds,
session_state
FROM ash_all
GROUP BY
session_state
ORDER BY
1 DESC
/
--
PRO
PRO 5. Session State (opt):
DEF cs2_session_state = '&5.';
UNDEF 5;
DEF cs2_instruct_to_skip = '(opt)';
COL cs2_instruct_to_skip NEW_V cs2_instruct_to_skip NOPRI;
SELECT '(hit "Return" to skip this patameter since Session State is "ON CPU")' AS cs2_instruct_to_skip FROM DUAL WHERE '&&cs2_session_state.' = 'ON CPU'
/
--
COL wait_class HEA 'Wait Class';
--
WITH
ash_awr AS (
SELECT /*+ MATERIALIZE NO_MERGE */
h.session_state,
h.wait_class,
10 * COUNT(*) AS db_seconds
FROM dba_hist_active_sess_history h
WHERE h.sample_time <= TO_TIMESTAMP('&&cs_ash_cut_off_date.', 'YYYY-MM-DD"T"HH24:MI')
AND h.sample_time >= TO_TIMESTAMP('&&cs_sample_time_from.', '&&cs_datetime_full_format.')
AND h.sample_time < TO_TIMESTAMP('&&cs_sample_time_to.', '&&cs_datetime_full_format.')
AND h.dbid = TO_NUMBER('&&cs_dbid.')
AND h.instance_number = TO_NUMBER('&&cs_instance_number.')
AND h.snap_id BETWEEN TO_NUMBER('&&cs_snap_id_from.') AND TO_NUMBER('&&cs_snap_id_to.') + 1
AND ('&&cs2_session_state.' IS NULL OR h.session_state = '&&cs2_session_state.')
AND NVL('&&cs2_session_state.', 'X') <> 'ON CPU'
GROUP BY
h.session_state,
h.wait_class
),
ash_mem AS (
SELECT /*+ MATERIALIZE NO_MERGE */
h.session_state,
h.wait_class,
1 * COUNT(*) AS db_seconds
FROM v$active_session_history h
WHERE h.sample_time > TO_TIMESTAMP('&&cs_ash_cut_off_date.', 'YYYY-MM-DD"T"HH24:MI')
AND h.sample_time >= TO_TIMESTAMP('&&cs_sample_time_from.', '&&cs_datetime_full_format.')
AND h.sample_time < TO_TIMESTAMP('&&cs_sample_time_to.', '&&cs_datetime_full_format.')
AND ('&&cs2_session_state.' IS NULL OR h.session_state = '&&cs2_session_state.')
AND NVL('&&cs2_session_state.', 'X') <> 'ON CPU'
GROUP BY
h.session_state,
h.wait_class
),
ash_all AS (
SELECT session_state, wait_class, db_seconds FROM ash_awr
UNION ALL
SELECT session_state, wait_class, db_seconds FROM ash_mem
)
SELECT ROUND(SUM(db_seconds) / TO_NUMBER('&&cs_from_to_seconds.'), 3) AS aas,
SUM(db_seconds) AS db_seconds,
wait_class,
session_state
FROM ash_all
GROUP BY
wait_class,
session_state
ORDER BY
1 DESC
/
--
PRO
PRO 6. Wait Class &&cs2_instruct_to_skip.:
DEF cs2_wait_class = '&6.';
UNDEF 6;
--
COL cs2_group NEW_V cs2_group NOPRI;
SELECT CASE '&&cs2_dimension.'
WHEN 'wait_class' THEN q'[CASE h.session_state WHEN 'ON CPU' THEN h.session_state ELSE h.wait_class END]'
WHEN 'event' THEN CASE WHEN '&&cs2_wait_class.' IS NULL THEN q'[CASE h.session_state WHEN 'ON CPU' THEN h.session_state ELSE h.wait_class||' - '||h.event END]' ELSE q'[h.event]' END
WHEN 'machine' THEN q'[h.machine]'
WHEN 'sql_id' THEN q'[h.sql_id]'
WHEN 'plan_hash_value' THEN q'[TO_CHAR(h.sql_plan_hash_value)]'
WHEN 'top_level_sql_id' THEN q'[h.top_level_sql_id]'
WHEN 'sid' THEN q'[TO_CHAR(h.session_id)]'
-- WHEN 'blocking_session' THEN q'[h.blocking_session||CASE WHEN h.blocking_session IS NOT NULL THEN ','||h.blocking_session_serial# END]' -- 19c: ORA-00979: not a GROUP BY expression
WHEN 'blocking_session' THEN q'[TO_CHAR(h.blocking_session)]'
-- WHEN 'current_obj#' THEN q'[h.current_obj#||CASE WHEN h.current_obj# IS NOT NULL THEN ' ('||h.con_id||')' END]' -- 19c: ORA-00979: not a GROUP BY expression
WHEN 'current_obj#' THEN q'[TO_CHAR(h.current_obj#)]'
WHEN 'module' THEN q'[h.module]'
WHEN 'program' THEN q'[h.program]'
WHEN 'sql_opname' THEN q'[h.sql_opname]'
WHEN 'appl_cat' THEN q'[application_category(t.sql_text, h.sql_opname)]'
WHEN 'pdb_name' THEN q'[TO_CHAR(h.con_id)]'
WHEN 'p1' THEN q'[h.p1text||':'||h.p1]'
WHEN 'p2' THEN q'[h.p2text||':'||h.p2]'
WHEN 'p3' THEN q'[h.p3text||':'||h.p3]'
END AS cs2_group
FROM DUAL
/
--
COL event HEA 'Event';
--
WITH
ash_awr AS (
SELECT /*+ MATERIALIZE NO_MERGE */
h.event,
h.wait_class,
h.session_state,
10 * COUNT(*) AS db_seconds
FROM dba_hist_active_sess_history h
WHERE h.sample_time <= TO_TIMESTAMP('&&cs_ash_cut_off_date.', 'YYYY-MM-DD"T"HH24:MI')
AND h.sample_time >= TO_TIMESTAMP('&&cs_sample_time_from.', '&&cs_datetime_full_format.')
AND h.sample_time < TO_TIMESTAMP('&&cs_sample_time_to.', '&&cs_datetime_full_format.')
AND h.dbid = TO_NUMBER('&&cs_dbid.')
AND h.instance_number = TO_NUMBER('&&cs_instance_number.')
AND h.snap_id BETWEEN TO_NUMBER('&&cs_snap_id_from.') AND TO_NUMBER('&&cs_snap_id_to.') + 1
AND ('&&cs2_session_state.' IS NULL OR h.session_state = '&&cs2_session_state.')
AND NVL('&&cs2_session_state.', 'X') <> 'ON CPU'
AND ('&&cs2_wait_class.' IS NULL OR h.wait_class = '&&cs2_wait_class.')
GROUP BY
h.event,
h.wait_class,
h.session_state
),
ash_mem AS (
SELECT /*+ MATERIALIZE NO_MERGE */
h.event,
h.wait_class,
h.session_state,
1 * COUNT(*) AS db_seconds
FROM v$active_session_history h
WHERE h.sample_time > TO_TIMESTAMP('&&cs_ash_cut_off_date.', 'YYYY-MM-DD"T"HH24:MI')
AND h.sample_time >= TO_TIMESTAMP('&&cs_sample_time_from.', '&&cs_datetime_full_format.')
AND h.sample_time < TO_TIMESTAMP('&&cs_sample_time_to.', '&&cs_datetime_full_format.')
AND ('&&cs2_session_state.' IS NULL OR h.session_state = '&&cs2_session_state.')
AND NVL('&&cs2_session_state.', 'X') <> 'ON CPU'
AND ('&&cs2_wait_class.' IS NULL OR h.wait_class = '&&cs2_wait_class.')
GROUP BY
h.event,
h.wait_class,
h.session_state
),
ash_all AS (
SELECT event, wait_class, session_state, db_seconds FROM ash_awr
UNION ALL
SELECT event, wait_class, session_state, db_seconds FROM ash_mem
)
SELECT ROUND(SUM(db_seconds) / TO_NUMBER('&&cs_from_to_seconds.'), 3) AS aas,
SUM(db_seconds) AS db_seconds,
event,
wait_class,
session_state
FROM ash_all
GROUP BY
event,
wait_class,
session_state
ORDER BY
1 DESC
FETCH FIRST 30 ROWS ONLY
/
--
PRO
PRO 7. Event &&cs2_instruct_to_skip.:
DEF cs2_event = '&7.';
UNDEF 7;
--
COL machine HEA 'Machine';
--
WITH
ash_awr AS (
SELECT /*+ MATERIALIZE NO_MERGE */
h.machine,
10 * COUNT(*) AS db_seconds
FROM dba_hist_active_sess_history h
WHERE h.sample_time <= TO_TIMESTAMP('&&cs_ash_cut_off_date.', 'YYYY-MM-DD"T"HH24:MI')
AND h.sample_time >= TO_TIMESTAMP('&&cs_sample_time_from.', '&&cs_datetime_full_format.')
AND h.sample_time < TO_TIMESTAMP('&&cs_sample_time_to.', '&&cs_datetime_full_format.')
AND h.dbid = TO_NUMBER('&&cs_dbid.')
AND h.instance_number = TO_NUMBER('&&cs_instance_number.')
AND h.snap_id BETWEEN TO_NUMBER('&&cs_snap_id_from.') AND TO_NUMBER('&&cs_snap_id_to.') + 1
AND ('&&cs2_session_state.' IS NULL OR h.session_state = '&&cs2_session_state.')
AND ('&&cs2_wait_class.' IS NULL OR h.wait_class = '&&cs2_wait_class.')
AND ('&&cs2_event.' IS NULL OR h.event LIKE CHR(37)||'&&cs2_event.'||CHR(37))
GROUP BY
h.machine
),
ash_mem AS (
SELECT /*+ MATERIALIZE NO_MERGE */
h.machine,
1 * COUNT(*) AS db_seconds
FROM v$active_session_history h
WHERE h.sample_time > TO_TIMESTAMP('&&cs_ash_cut_off_date.', 'YYYY-MM-DD"T"HH24:MI')
AND h.sample_time >= TO_TIMESTAMP('&&cs_sample_time_from.', '&&cs_datetime_full_format.')
AND h.sample_time < TO_TIMESTAMP('&&cs_sample_time_to.', '&&cs_datetime_full_format.')
AND ('&&cs2_session_state.' IS NULL OR h.session_state = '&&cs2_session_state.')
AND ('&&cs2_wait_class.' IS NULL OR h.wait_class = '&&cs2_wait_class.')
AND ('&&cs2_event.' IS NULL OR h.event LIKE CHR(37)||'&&cs2_event.'||CHR(37))
GROUP BY
h.machine
),
ash_all AS (
SELECT machine, db_seconds FROM ash_awr
UNION ALL
SELECT machine, db_seconds FROM ash_mem
)
SELECT ROUND(SUM(db_seconds) / TO_NUMBER('&&cs_from_to_seconds.'), 3) AS aas,
SUM(db_seconds) AS db_seconds,
machine
FROM ash_all
GROUP BY
machine
ORDER BY
1 DESC
FETCH FIRST 30 ROWS ONLY
/
--
PRO
PRO 8. Machine (opt):
DEF cs2_machine = '&8.';
UNDEF 8;
--
PRO
PRO 9. SQL Text piece (e.g.: ScanQuery, getValues, TableName, IndexName):
DEF cs2_sql_text_piece = '&9.';
UNDEF 9;
--
COL sql_text FOR A60 TRUNC;
--
WITH
sql_txt AS (
SELECT /*+ MATERIALIZE NO_MERGE */ sql_id, MAX(sql_text) AS sql_text
FROM (
SELECT sql_id, REPLACE(REPLACE(SUBSTR(sql_text, 1, 100), CHR(10), CHR(32)), CHR(9), CHR(32)) AS sql_text
FROM v$sql
WHERE '&&cs2_sql_text_piece.' IS NOT NULL
AND UPPER(sql_text) LIKE CHR(37)||UPPER('&&cs2_sql_text_piece.')||CHR(37)
AND ROWNUM >= 1
UNION ALL
SELECT sql_id, REPLACE(REPLACE(DBMS_LOB.substr(sql_text, 100), CHR(10), CHR(32)), CHR(9), CHR(32)) AS sql_text
FROM dba_hist_sqltext
WHERE '&&cs2_sql_text_piece.' IS NOT NULL
AND UPPER(DBMS_LOB.substr(sql_text, 1000)) LIKE CHR(37)||UPPER('&&cs2_sql_text_piece.')||CHR(37)
AND dbid = &&cs_dbid.
AND ROWNUM >= 1
)
GROUP BY sql_id
),
ash_awr AS (
SELECT /*+ MATERIALIZE NO_MERGE */
h.sql_id,
10 * COUNT(*) AS db_seconds
FROM dba_hist_active_sess_history h
WHERE h.sample_time <= TO_TIMESTAMP('&&cs_ash_cut_off_date.', 'YYYY-MM-DD"T"HH24:MI')
AND h.sample_time >= TO_TIMESTAMP('&&cs_sample_time_from.', '&&cs_datetime_full_format.')
AND h.sample_time < TO_TIMESTAMP('&&cs_sample_time_to.', '&&cs_datetime_full_format.')
AND h.dbid = TO_NUMBER('&&cs_dbid.')
AND h.instance_number = TO_NUMBER('&&cs_instance_number.')
AND h.snap_id BETWEEN TO_NUMBER('&&cs_snap_id_from.') AND TO_NUMBER('&&cs_snap_id_to.') + 1
AND ('&&cs2_session_state.' IS NULL OR h.session_state = '&&cs2_session_state.')
AND ('&&cs2_wait_class.' IS NULL OR h.wait_class = '&&cs2_wait_class.')
AND ('&&cs2_event.' IS NULL OR h.event LIKE CHR(37)||'&&cs2_event.'||CHR(37))
AND ('&&cs2_machine.' IS NULL OR h.machine LIKE CHR(37)||'&&cs2_machine.'||CHR(37))
AND ('&&cs2_sql_text_piece.' IS NULL OR h.sql_id IN (SELECT /*+ NO_MERGE */ t.sql_id FROM sql_txt t))
GROUP BY
h.sql_id
),
ash_mem AS (
SELECT /*+ MATERIALIZE NO_MERGE */
h.sql_id,
1 * COUNT(*) AS db_seconds
FROM v$active_session_history h
WHERE h.sample_time > TO_TIMESTAMP('&&cs_ash_cut_off_date.', 'YYYY-MM-DD"T"HH24:MI')
AND h.sample_time >= TO_TIMESTAMP('&&cs_sample_time_from.', '&&cs_datetime_full_format.')
AND h.sample_time < TO_TIMESTAMP('&&cs_sample_time_to.', '&&cs_datetime_full_format.')
AND ('&&cs2_session_state.' IS NULL OR h.session_state = '&&cs2_session_state.')
AND ('&&cs2_wait_class.' IS NULL OR h.wait_class = '&&cs2_wait_class.')
AND ('&&cs2_event.' IS NULL OR h.event LIKE CHR(37)||'&&cs2_event.'||CHR(37))
AND ('&&cs2_machine.' IS NULL OR h.machine LIKE CHR(37)||'&&cs2_machine.'||CHR(37))
AND ('&&cs2_sql_text_piece.' IS NULL OR h.sql_id IN (SELECT /*+ NO_MERGE */ t.sql_id FROM sql_txt t))
GROUP BY
h.sql_id
),
ash_all AS (
SELECT sql_id, db_seconds FROM ash_awr
UNION ALL
SELECT sql_id, db_seconds FROM ash_mem
)
SELECT ROUND(SUM(db_seconds) / TO_NUMBER('&&cs_from_to_seconds.'), 3) AS aas,
SUM(db_seconds) AS db_seconds,
sql_id,
(SELECT s.sql_text FROM sql_txt s WHERE s.sql_id = a.sql_id AND ROWNUM = 1) AS sql_text
FROM ash_all a
GROUP BY
sql_id
ORDER BY
1 DESC
FETCH FIRST 30 ROWS ONLY
/
--
PRO
PRO 10. SQL_ID (opt):
DEF cs2_sql_id = '&10.';
UNDEF 10;
--
DEF spool_id_chart_footer_script = 'cs_ash_analytics_footer.sql';
COL rn FOR 999;
COL dimension_group FOR A64 TRUNC;
DEF series_01 = ' ';
DEF series_02 = ' ';
DEF series_03 = ' ';
DEF series_04 = ' ';
DEF series_05 = ' ';
DEF series_06 = ' ';
DEF series_07 = ' ';
DEF series_08 = ' ';
DEF series_09 = ' ';
DEF series_10 = ' ';
DEF series_11 = ' ';
DEF series_12 = ' ';
DEF series_13 = ' ';
COL series_01 NEW_V series_01 FOR A64 TRUNC NOPRI;
COL series_02 NEW_V series_02 FOR A64 TRUNC NOPRI;
COL series_03 NEW_V series_03 FOR A64 TRUNC NOPRI;
COL series_04 NEW_V series_04 FOR A64 TRUNC NOPRI;
COL series_05 NEW_V series_05 FOR A64 TRUNC NOPRI;
COL series_06 NEW_V series_06 FOR A64 TRUNC NOPRI;
COL series_07 NEW_V series_07 FOR A64 TRUNC NOPRI;
COL series_08 NEW_V series_08 FOR A64 TRUNC NOPRI;
COL series_09 NEW_V series_09 FOR A64 TRUNC NOPRI;
COL series_10 NEW_V series_10 FOR A64 TRUNC NOPRI;
COL series_11 NEW_V series_11 FOR A64 TRUNC NOPRI;
COL series_12 NEW_V series_12 FOR A64 TRUNC NOPRI;
COL series_13 NEW_V series_13 FOR A64 TRUNC NOPRI;
DEF aas_01 = ' ';
DEF aas_02 = ' ';
DEF aas_03 = ' ';
DEF aas_04 = ' ';
DEF aas_05 = ' ';
DEF aas_06 = ' ';
DEF aas_07 = ' ';
DEF aas_08 = ' ';
DEF aas_09 = ' ';
DEF aas_10 = ' ';
DEF aas_11 = ' ';
DEF aas_12 = ' ';
DEF aas_13 = ' ';
COL aas_01 NEW_V aas_01 FOR A9 TRUNC NOPRI;
COL aas_02 NEW_V aas_02 FOR A9 TRUNC NOPRI;
COL aas_03 NEW_V aas_03 FOR A9 TRUNC NOPRI;
COL aas_04 NEW_V aas_04 FOR A9 TRUNC NOPRI;
COL aas_05 NEW_V aas_05 FOR A9 TRUNC NOPRI;
COL aas_06 NEW_V aas_06 FOR A9 TRUNC NOPRI;
COL aas_07 NEW_V aas_07 FOR A9 TRUNC NOPRI;
COL aas_08 NEW_V aas_08 FOR A9 TRUNC NOPRI;
COL aas_09 NEW_V aas_09 FOR A9 TRUNC NOPRI;
COL aas_10 NEW_V aas_10 FOR A9 TRUNC NOPRI;
COL aas_11 NEW_V aas_11 FOR A9 TRUNC NOPRI;
COL aas_12 NEW_V aas_12 FOR A9 TRUNC NOPRI;
COL aas_13 NEW_V aas_13 FOR A9 TRUNC NOPRI;
--
WITH
FUNCTION get_sql_text (p_sql_id IN VARCHAR2)
RETURN VARCHAR2
IS
l_sql_text VARCHAR2(4000);
BEGIN
SELECT MAX(REPLACE(REPLACE(SUBSTR(sql_text, 1, 100), CHR(10), CHR(32)), CHR(9), CHR(32))) AS sql_text
INTO l_sql_text
FROM v$sql
WHERE sql_id = p_sql_id
AND ROWNUM = 1;
--
IF l_sql_text IS NOT NULL THEN
RETURN REPLACE(REPLACE(l_sql_text, ':'), '''');
END IF;
--
SELECT MAX(REPLACE(REPLACE(DBMS_LOB.substr(sql_text, 100), CHR(10), CHR(32)), CHR(9), CHR(32))) AS sql_text
INTO l_sql_text
FROM dba_hist_sqltext
WHERE sql_id = p_sql_id
AND dbid = &&cs_dbid.
AND ROWNUM = 1;
--
RETURN REPLACE(REPLACE(l_sql_text, ':'), '''');
END get_sql_text;
--
FUNCTION get_pdb_name (p_con_id IN VARCHAR2)
RETURN VARCHAR2
IS
l_pdb_name VARCHAR2(4000);
BEGIN
SELECT name
INTO l_pdb_name
FROM v$containers
WHERE con_id = TO_NUMBER(p_con_id);
--
RETURN l_pdb_name;
END get_pdb_name;
--
FUNCTION application_category (
p_sql_text IN VARCHAR2,
p_command_name IN VARCHAR2 DEFAULT NULL
)
RETURN VARCHAR2
IS
k_appl_handle_prefix CONSTANT VARCHAR2(30) := CHR(37)||'/*'||CHR(37);
k_appl_handle_suffix CONSTANT VARCHAR2(30) := CHR(37)||'*/'||CHR(37);
BEGIN
IF p_sql_text LIKE k_appl_handle_prefix||'Transaction Processing'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'addTransactionRow'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'checkEndRowValid'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'checkStartRowValid'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'deleteValue'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'exists'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'Fetch commit by idempotency token'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'Fetch latest transactions for cache'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'Find lower commit id for transaction cache warm up'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'findMatchingRow'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'getMaxTransactionCommitID'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'getNewTransactionID'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'getTransactionProgress'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'lockForCommit'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'lockKievTransactor'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'putBucket'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'readTransactionsSince'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'recordTransactionState'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'setValue'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'SPM:CP'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'updateIdentityValue'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'updateNextKievTransID'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'updateTransactorState'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'upsert_transactor_state'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'writeTransactionKeys'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'QueryTransactorHosts'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'WriteBucketValues'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'batch commit'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'batch mutation log'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'fetchAllIdentities'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'fetch_epoch'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'readFromTxorStateBeginTxn'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'readOnlyBeginTxn'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'validateTransactorState'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'getDataStoreMaxTransaction'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'legacyGetDataStoreMaxTransaction'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'isPartitionDropDisabled'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'getWithVersionOffsetSql'||k_appl_handle_suffix
OR LOWER(p_sql_text) LIKE CHR(37)||'lock table kievtransactions'||CHR(37)
--
OR p_sql_text LIKE k_appl_handle_prefix||'Set ddl lock timeout for session'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'delete.leases'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'delete.workflow_instances'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'delete.step_instances'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'delete.historical_assignments'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'delete.workflow_definitions'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'delete.step_definitions'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'delete.leases_types'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'getForUpdate.dataplane_alias'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'insert.leases_types'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'insert.leases'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'insert.workflow_instances'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'insert.step_instances'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'insert.historical_assignments'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'insert.workflow_definitions'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'insert.step_definitions'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'update.dataplane_alias'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'update.leases'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'update.workflow_instances'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'update.step_instances'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'update.historical_assignments'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'update.workflow_definitions'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'Drop partition'||k_appl_handle_suffix
THEN RETURN 'TP'; /* Transaction Processing */
--
ELSIF p_sql_text LIKE k_appl_handle_prefix||'Read Only'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'bucketIndexSelect'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'bucketKeySelect'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'bucketValueSelect'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'countTransactions'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'Fetch snapshots'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'Get system time'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'getAutoSequences'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'getNextIdentityValue'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'getValues'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'Lock row Bucket_Snapshot'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'longFromDual'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'performContinuedScanValues'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'performFirstRowsScanQuery'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'performScanQuery'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'performSnapshotScanQuery'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'performStartScanValues'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'selectBuckets'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'Fetch latest revisions'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'Fetch max sequence for'||k_appl_handle_suffix -- streaming
OR p_sql_text LIKE k_appl_handle_prefix||'Fetch partition interval for'||k_appl_handle_suffix -- streaming
OR p_sql_text LIKE k_appl_handle_prefix||'Find High value for'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'Find partitions for'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'Init lock name for snapshot'||k_appl_handle_suffix -- snapshot
OR p_sql_text LIKE k_appl_handle_prefix||'List snapshot tables.'||k_appl_handle_suffix -- snapshot
OR p_sql_text LIKE k_appl_handle_prefix||'Tail read bucket'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'performSegmentedScanQuery'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'listArchiveStatusByIndexName'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'listAssignmentsByIndexName'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'listHosts'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'updateHost'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'updateArchiveStatus'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'updateOperationLock'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'get KIEVWORKFLOWS table indexes'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'GetOldestLsn'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'GetStreamRecords'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'Check if another workflow is running'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'Delete old workflows from'||k_appl_handle_suffix
--
OR p_sql_text LIKE k_appl_handle_prefix||'getFutureWorkflowDefinition'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'getPriorWorkflowDefinition'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'enumerateLeases'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'enumerateLeaseTypes'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'getHistoricalAssignments'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'getAllWorkflowDefinitions'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'getVersionHistory'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'getInstances'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'Find interval partitions for schema'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'getStepInstances'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'getOldestGcWorkflowInstance'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'getNextRecordID'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'get.dataplane_alias'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'getLeaseNonce'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'get.leases_types'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'get.leases'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'get.workflow_instances'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'getByKey.workflow_instances'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'getRecordId.workflow_instances'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'getLast.historical_assignments'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'get.historical_assignments'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'isPartitionDropDisabled'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'Check if there are active rows for partition'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'getRunningInstancesCount'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'get.workflow_definitions'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'getLatestWorkflowDefinition'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'getLast.step_instances'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'get.workflow_instances'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'get.step_instances'||k_appl_handle_suffix
THEN RETURN 'RO'; /* Read Only */
--
ELSIF p_sql_text LIKE k_appl_handle_prefix||'Background'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'Bootstrap snapshot table Kiev_S'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'bucketIdentitySelect'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'checkMissingTables'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'countAllBuckets'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'countAllRows'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'countKievTransactionRows'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'countKtkRows'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'Delete garbage'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'Delete rows from'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'deleteBucketGarbage'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'enumerateSequences'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'Fetch config'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'fetch_leader_heartbeat'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'gcEventMaxId'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'gcEventTryInsert'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'Get txn at time'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'get_leader'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'getCurEndTime'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'getDBSchemaVersion'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'getEndTimeOlderThan'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'getGCLogEntries'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'getMaxTransactionOlderThan'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'getSchemaMetadata'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'getSupportedLibVersions'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'hashBucket'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'hashSnapshot'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'Populate workspace'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'populateBucketGCWorkspace'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'primeTxCache'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'readOnlyRoleExists'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'Row count between transactions'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'secondsSinceLastGcEvent'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'sync_leadership'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'Test if table Kiev_S'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'Update snapshot metadata'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'update_heartbeat'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'validateIfWorkspaceEmpty'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'verify_is_leader'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'Checking existence of Mutation Log Table'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'Checks if KIEVTRANSACTIONKEYS table is empty'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'Checks if KIEVTRANSACTIONS table is empty'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'Fetch partition interval for KT'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'Fetch partition interval for KTK'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'Insert dynamic config'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'createProxyUser'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'createSequence'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'deregister_host'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'dropAutoSequenceMetadata'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'dropBucketFromMetadata'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'dropSequenceMetadata'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'get KievTransactionKeys table indexes'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'get KievTransactions table indexes'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'get session count'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'initializeMetadata'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'isKtPartitioned'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'isPartitioned'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'log'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'register_host'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'updateSchemaVersionInDB'||k_appl_handle_suffix
--
OR p_sql_text LIKE k_appl_handle_prefix||'getUnownedLeases'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'getFutureWorks'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'getMinorVersionsAtAndAfter'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'getLeaseDecorators'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'getUnownedLeasesByFiFo'||k_appl_handle_suffix
THEN RETURN 'BG'; /* Background */
--
ELSIF p_sql_text LIKE k_appl_handle_prefix||'Ignore'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'enumerateKievPdbs'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'getJDBCSuffix'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'MV_REFRESH'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'null'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'selectColumnsForTable'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'selectDatastoreMd'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'SQL Analyze('||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'validateDataStoreId'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'countMetadata'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'countSequenceInstances'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'iod-telemetry'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'insert snapshot metadata'||k_appl_handle_suffix
OR p_sql_text LIKE k_appl_handle_prefix||'OPT_DYN_SAMP'||k_appl_handle_suffix
THEN RETURN 'IG'; /* Ignore */
--
ELSIF p_command_name IN ('INSERT', 'UPDATE')
THEN RETURN 'TP'; /* Transaction Processing */
--
ELSIF p_command_name = 'DELETE'
THEN RETURN 'BG'; /* Background */
--
ELSIF p_command_name = 'SELECT'
THEN RETURN 'RO'; /* Read Only */
--
ELSE RETURN 'UN'; /* Unknown */
END IF;
END application_category;
--
wait_classes AS (
SELECT 1 AS rn, 'ON CPU' AS dimension_group FROM DUAL
UNION SELECT 2 AS rn, 'User I/O' AS dimension_group FROM DUAL
UNION SELECT 3 AS rn, 'System I/O' AS dimension_group FROM DUAL
UNION SELECT 4 AS rn, 'Cluster' AS dimension_group FROM DUAL
UNION SELECT 5 AS rn, 'Commit' AS dimension_group FROM DUAL
UNION SELECT 6 AS rn, 'Concurrency' AS dimension_group FROM DUAL
UNION SELECT 7 AS rn, 'Application' AS dimension_group FROM DUAL
UNION SELECT 8 AS rn, 'Administrative' AS dimension_group FROM DUAL
UNION SELECT 9 AS rn, 'Configuration' AS dimension_group FROM DUAL
UNION SELECT 10 AS rn, 'Network' AS dimension_group FROM DUAL
UNION SELECT 11 AS rn, 'Queueing' AS dimension_group FROM DUAL
UNION SELECT 12 AS rn, 'Scheduler' AS dimension_group FROM DUAL
UNION SELECT 13 AS rn, 'Other' AS dimension_group FROM DUAL
),
sql_txt AS (
SELECT /*+ MATERIALIZE NO_MERGE */ sql_id, MAX(sql_text) AS sql_text
FROM (
SELECT /*+ MATERIALIZE NO_MERGE */ sql_id, REPLACE(REPLACE(SUBSTR(sql_text, 1, 100), CHR(10), CHR(32)), CHR(9), CHR(32)) AS sql_text
FROM v$sql
WHERE '&&cs2_sql_text_piece.' IS NOT NULL
AND UPPER(sql_text) LIKE CHR(37)||UPPER('&&cs2_sql_text_piece.')||CHR(37)
AND ROWNUM >= 1
UNION ALL
SELECT /*+ MATERIALIZE NO_MERGE */ sql_id, REPLACE(REPLACE(DBMS_LOB.substr(sql_text, 100), CHR(10), CHR(32)), CHR(9), CHR(32)) AS sql_text
FROM dba_hist_sqltext
WHERE '&&cs2_sql_text_piece.' IS NOT NULL
AND UPPER(DBMS_LOB.substr(sql_text, 1000)) LIKE CHR(37)||UPPER('&&cs2_sql_text_piece.')||CHR(37)
AND dbid = &&cs_dbid.
AND ROWNUM >= 1
)
GROUP BY sql_id
),
ash_awr AS (
SELECT /*+ MATERIALIZE NO_MERGE */
&&cs2_group. AS dimension_group,
10 * COUNT(*) AS db_seconds
FROM dba_hist_active_sess_history h,
sql_txt t
WHERE h.sample_time <= TO_TIMESTAMP('&&cs_ash_cut_off_date.', 'YYYY-MM-DD"T"HH24:MI')
AND h.sample_time >= TO_TIMESTAMP('&&cs_sample_time_from.', '&&cs_datetime_full_format.')
AND h.sample_time < TO_TIMESTAMP('&&cs_sample_time_to.', '&&cs_datetime_full_format.')
AND h.dbid = TO_NUMBER('&&cs_dbid.')
AND h.instance_number = TO_NUMBER('&&cs_instance_number.')
AND h.snap_id BETWEEN TO_NUMBER('&&cs_snap_id_from.') AND TO_NUMBER('&&cs_snap_id_to.') + 1
AND ('&&cs2_session_state.' IS NULL OR h.session_state = '&&cs2_session_state.')
AND ('&&cs2_wait_class.' IS NULL OR h.wait_class = '&&cs2_wait_class.')
AND ('&&cs2_event.' IS NULL OR h.event LIKE CHR(37)||'&&cs2_event.'||CHR(37))
AND ('&&cs2_machine.' IS NULL OR h.machine LIKE CHR(37)||'&&cs2_machine.'||CHR(37))
AND ('&&cs2_sql_text_piece.' IS NULL OR h.sql_id IN (SELECT /*+ NO_MERGE */ t.sql_id FROM sql_txt t))
AND ('&&cs2_sql_id.' IS NULL OR h.sql_id = '&&cs2_sql_id.')
AND t.sql_id(+) = h.sql_id
AND ROWNUM >= 1 /* MATERIALIZE */
GROUP BY
&&cs2_group.
),
ash_mem AS (
SELECT /*+ MATERIALIZE NO_MERGE */
&&cs2_group. AS dimension_group,
1 * COUNT(*) AS db_seconds
FROM v$active_session_history h,
sql_txt t
WHERE h.sample_time > TO_TIMESTAMP('&&cs_ash_cut_off_date.', 'YYYY-MM-DD"T"HH24:MI')
AND h.sample_time >= TO_TIMESTAMP('&&cs_sample_time_from.', '&&cs_datetime_full_format.')
AND h.sample_time < TO_TIMESTAMP('&&cs_sample_time_to.', '&&cs_datetime_full_format.')
AND ('&&cs2_session_state.' IS NULL OR h.session_state = '&&cs2_session_state.')
AND ('&&cs2_wait_class.' IS NULL OR h.wait_class = '&&cs2_wait_class.')
AND ('&&cs2_event.' IS NULL OR h.event LIKE CHR(37)||'&&cs2_event.'||CHR(37))
AND ('&&cs2_machine.' IS NULL OR h.machine LIKE CHR(37)||'&&cs2_machine.'||CHR(37))
AND ('&&cs2_sql_text_piece.' IS NULL OR h.sql_id IN (SELECT /*+ NO_MERGE */ t.sql_id FROM sql_txt t))
AND ('&&cs2_sql_id.' IS NULL OR h.sql_id = '&&cs2_sql_id.')
AND t.sql_id(+) = h.sql_id
AND ROWNUM >= 1 /* MATERIALIZE */
GROUP BY
&&cs2_group.
),
ash_all AS (
SELECT dimension_group, db_seconds FROM ash_awr
UNION ALL
SELECT dimension_group, db_seconds FROM ash_mem
),
ash_by_dim AS (
SELECT /*+ MATERIALIZE NO_MERGE */
ROUND(SUM(db_seconds) / TO_NUMBER('&&cs_from_to_seconds.'), 3) AS aas,
SUM(db_seconds) AS db_seconds,
dimension_group,
ROW_NUMBER() OVER(ORDER BY SUM(db_seconds) DESC) AS rn
FROM ash_all a
GROUP BY
dimension_group
),
top AS (
SELECT /*+ MATERIALIZE NO_MERGE */
rn, -- up to 12
aas,
db_seconds,
SUBSTR(CASE
WHEN TRIM(dimension_group) IS NULL /*OR TRIM(dimension_group) = ','*/ THEN '"null"'
WHEN '&&cs2_dimension.' IN ('sql_id', 'top_level_sql_id') THEN dimension_group||' '||get_sql_text(dimension_group)
WHEN '&&cs2_dimension.' = 'pdb_name' THEN dimension_group||' '||get_pdb_name(dimension_group)
ELSE dimension_group
END, 1, 64) AS dimension_group
FROM ash_by_dim
WHERE rn < (SELECT MAX(rn) FROM wait_classes) -- 13
),
max_top AS (
SELECT /*+ MATERIALIZE NO_MERGE */ MAX(rn) AS max_rn FROM top
),
bottom AS (
SELECT /*+ MATERIALIZE NO_MERGE */
(1 + max_top.max_rn) AS bottom_rn, -- up to 13
ROUND(SUM(a.db_seconds) / TO_NUMBER('&&cs_from_to_seconds.'), 3) AS aas,
SUM(a.db_seconds) AS db_seconds,
'"all others"' AS dimension_group
FROM ash_by_dim a, max_top
WHERE a.rn >= max_top.max_rn
GROUP BY
max_top.max_rn
),
wait_classes2 AS (
SELECT /*+ MATERIALIZE NO_MERGE */
w.rn,
NVL(t.aas, 0) AS aas,
NVL(t.db_seconds, 0) AS db_seconds,
w.dimension_group
FROM wait_classes w,
top t
WHERE '&&cs2_dimension.' = 'wait_class'
AND t.dimension_group(+) = w.dimension_group
),
top_and_bottom AS (
SELECT rn, aas, db_seconds, dimension_group
FROM top
WHERE '&&cs2_dimension.' <> 'wait_class'
UNION ALL
SELECT rn, aas, db_seconds, dimension_group
FROM wait_classes2
WHERE '&&cs2_dimension.' = 'wait_class'
UNION ALL
SELECT bottom_rn AS rn, aas, db_seconds, dimension_group
FROM bottom
WHERE '&&cs2_dimension.' <> 'wait_class'
),
list AS (
SELECT /*+ MATERIALIZE NO_MERGE */
rn, LPAD(TRIM(TO_CHAR(ROUND(aas, 3), '9,990.000')), 9) AS aas, db_seconds, dimension_group
FROM top_and_bottom
)
SELECT rn, aas, db_seconds, dimension_group,
COALESCE((SELECT dimension_group FROM list WHERE rn = 1), ' ') AS series_01,
COALESCE((SELECT dimension_group FROM list WHERE rn = 2), ' ') AS series_02,
COALESCE((SELECT dimension_group FROM list WHERE rn = 3), ' ') AS series_03,
COALESCE((SELECT dimension_group FROM list WHERE rn = 4), ' ') AS series_04,
COALESCE((SELECT dimension_group FROM list WHERE rn = 5), ' ') AS series_05,
COALESCE((SELECT dimension_group FROM list WHERE rn = 6), ' ') AS series_06,
COALESCE((SELECT dimension_group FROM list WHERE rn = 7), ' ') AS series_07,
COALESCE((SELECT dimension_group FROM list WHERE rn = 8), ' ') AS series_08,
COALESCE((SELECT dimension_group FROM list WHERE rn = 9), ' ') AS series_09,
COALESCE((SELECT dimension_group FROM list WHERE rn = 10), ' ') AS series_10,
COALESCE((SELECT dimension_group FROM list WHERE rn = 11), ' ') AS series_11,
COALESCE((SELECT dimension_group FROM list WHERE rn = 12), ' ') AS series_12,
COALESCE((SELECT dimension_group FROM list WHERE rn = 13), ' ') AS series_13,
(SELECT aas FROM list WHERE rn = 1) AS aas_01,
(SELECT aas FROM list WHERE rn = 2) AS aas_02,
(SELECT aas FROM list WHERE rn = 3) AS aas_03,
(SELECT aas FROM list WHERE rn = 4) AS aas_04,
(SELECT aas FROM list WHERE rn = 5) AS aas_05,
(SELECT aas FROM list WHERE rn = 6) AS aas_06,
(SELECT aas FROM list WHERE rn = 7) AS aas_07,
(SELECT aas FROM list WHERE rn = 8) AS aas_08,
(SELECT aas FROM list WHERE rn = 9) AS aas_09,
(SELECT aas FROM list WHERE rn = 10) AS aas_10,
(SELECT aas FROM list WHERE rn = 11) AS aas_11,
(SELECT aas FROM list WHERE rn = 12) AS aas_12,
(SELECT aas FROM list WHERE rn = 13) AS aas_13
FROM list
ORDER BY
rn
/
--
SELECT '&&cs_file_prefix._&&cs_script_name.' cs_file_name FROM DUAL;
--
DEF report_title = 'Average Active Sessions by "&&cs2_dimension." between &&cs_sample_time_from. and &&cs_sample_time_to. UTC';
DEF chart_title = '&&report_title.';
DEF vaxis_title = 'Average Active Sessions (AAS)';
DEF xaxis_title = '';