-
Notifications
You must be signed in to change notification settings - Fork 2
Expand file tree
/
Copy pathsqlhc.sql
More file actions
5700 lines (5402 loc) · 217 KB
/
sqlhc.sql
File metadata and controls
5700 lines (5402 loc) · 217 KB
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
SPO sqlhc.log
SET DEF ^ TERM OFF ECHO ON VER OFF SERVEROUT ON SIZE 1000000;
REM
REM $Header: 1366133.1 sqlhc.sql 11.4.4.7 2012/07/02 carlos.sierra $
REM
REM Copyright (c) 2000-2012, Oracle Corporation. All rights reserved.
REM
REM AUTHOR
REM carlos.sierra@oracle.com
REM
REM SCRIPT
REM sqlhc.sql
REM
REM DESCRIPTION
REM Produces an HTML report with a list of observations based on
REM health-checks performed in and around a SQL statement that
REM may be performing poorly.
REM
REM Inputs a memory-resident SQL_ID.
REM
REM In addition to the health_check report, it generates some
REM additional diagnostics files regarding SQL performance.
REM
REM This script does not install any objects in the database.
REM It does not perform any DDL commands.
REM It only performs DML commands against the PLAN_TABLE then it
REM rolls back those temporary inserts.
REM It can be used in Dataguard or any read-only database.
REM
REM PRE-REQUISITES
REM 1. Execute as SYS or user with DBA role or user with access
REM to data dictionary views.
REM 2. The SQL for which this script is executed must be
REM memory-resident.
REM
REM PARAMETERS
REM 1. Oracle Pack license (Tuning or Diagnostics or None) T|D|N
REM 2. SQL_ID of interest.
REM
REM EXECUTION
REM 1. Start SQL*Plus connecting as SYS or user with DBA role or
REM user with access to data dictionary views.
REM 2. Execute script sqlhc.sql passing values for parameters.
REM
REM EXAMPLE
REM # sqlplus / as sysdba
REM SQL> START [path]sqlhc.sql [T|D|N] [SQL_ID]
REM SQL> START sqlhc.sql T 51x6yr9ym5hdc
REM
REM NOTES
REM 1. For possible errors see sqlhc.log.
REM 2. If site has both Tuning and Diagnostics licenses then
REM specified T (Oracle Tuning pack includes Oracle Diagnostics)
REM 3. On a read-only instance, the "Observations" section with the
REM results of the health-checks will be missing.
REM
DEF health_checks = 'Y';
DEF sql_monitor_reports = '12';
/**************************************************************************************************/
VAR health_checks CHAR(1);
EXEC :health_checks := '^^health_checks.';
SET TERM ON ECHO OFF;
PRO
PRO Parameter 1:
PRO Oracle Pack License (Tuning, Diagnostics or None) [T|D|N] (required)
PRO
DEF input_license = '^1';
PRO
SET TERM OFF;
COL license NEW_V license FOR A1;
SELECT UPPER(SUBSTR(TRIM('^^input_license.'), 1, 1)) license FROM DUAL;
VAR license CHAR(1);
EXEC :license := '^^license.';
COL unique_id NEW_V unique_id FOR A15;
SELECT TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') unique_id FROM DUAL;
SET TERM ON;
WHENEVER SQLERROR EXIT SQL.SQLCODE;
BEGIN
IF '^^license.' IS NULL OR '^^license.' NOT IN ('T', 'D', 'N') THEN
RAISE_APPLICATION_ERROR(-20100, 'Oracle Pack License (Tuning, Diagnostics or None) must be specified as "T" or "D" or "N".');
END IF;
END;
/
WHENEVER SQLERROR CONTINUE;
PRO
PRO Parameter 2:
PRO SQL_ID of the SQL to be analyzed (required)
PRO
DEF input_sql_id = '^2';
DEF input_parameter = '^^input_sql_id.';
PRO
PRO Values passed:
PRO ~~~~~~~~~~~~~
PRO License: "^^input_license."
PRO SQL_ID : "^^input_sql_id."
PRO
SET TERM OFF;
-- get dbid
COL dbid NEW_V dbid;
SELECT dbid FROM v$database;
COL sql_id NEW_V sql_id FOR A13;
SELECT sql_id
FROM gv$sqlarea
WHERE sql_id = TRIM('^^input_sql_id.')
UNION
SELECT sql_id
FROM dba_hist_sqltext
WHERE :license IN ('T', 'D')
AND dbid = ^^dbid.
AND sql_id = TRIM('^^input_sql_id.');
VAR sql_id VARCHAR2(13);
EXEC :sql_id := '^^sql_id.';
SET TERM ON;
WHENEVER SQLERROR EXIT SQL.SQLCODE;
BEGIN
IF '^^sql_id.' IS NULL THEN
IF :license IN ('T', 'D') THEN
RAISE_APPLICATION_ERROR(-20200, 'SQL_ID "^^input_sql_id." not found in memory nor in AWR.');
ELSE
RAISE_APPLICATION_ERROR(-20200, 'SQL_ID "^^input_sql_id." not found in memory.');
END IF;
END IF;
END;
/
WHENEVER SQLERROR CONTINUE;
SET ECHO ON TIMI ON;
DEF script = 'sqlhc';
DEF method = 'SQLHC';
DEF mos_doc = '1366133.1';
/**************************************************************************************************
*
* begin_common: from begin_common to end_common sqlhc.sql and sqlhcxec.sql are identical
*
**************************************************************************************************/
DEF doc_ver = '11.4.4.7';
DEF doc_date = '2012/07/02';
DEF doc_link = 'https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=';
DEF bug_link = 'https://support.oracle.com/CSP/main/article?cmd=show&type=BUG&id=';
-- tracing script in case it takes long to execute so we can diagnose it
ALTER SESSION SET TRACEFILE_IDENTIFIER = "^^script._^^unique_id.";
ALTER SESSION SET STATISTICS_LEVEL = 'ALL';
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';
/**************************************************************************************************/
/* -------------------------
*
* get sql_text
*
* ------------------------- */
VAR sql_text CLOB;
EXEC :sql_text := NULL;
-- get sql_text from memory
DECLARE
l_sql_text VARCHAR2(32767);
BEGIN -- 10g see bug 5017909
DBMS_OUTPUT.PUT_LINE('getting sql_text from memory');
FOR i IN (SELECT DISTINCT piece, sql_text
FROM gv$sqltext_with_newlines
WHERE sql_id = '^^sql_id.'
ORDER BY 1, 2)
LOOP
IF :sql_text IS NULL THEN
DBMS_LOB.CREATETEMPORARY(:sql_text, TRUE);
DBMS_LOB.OPEN(:sql_text, DBMS_LOB.LOB_READWRITE);
END IF;
l_sql_text := REPLACE(i.sql_text, CHR(00), ' ');
DBMS_LOB.WRITEAPPEND(:sql_text, LENGTH(l_sql_text), l_sql_text);
END LOOP;
IF :sql_text IS NOT NULL THEN
DBMS_LOB.CLOSE(:sql_text);
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('getting sql_text from memory: '||SQLERRM);
:sql_text := NULL;
END;
/
-- get sql_text from awr
BEGIN
IF :license IN ('T', 'D') AND (:sql_text IS NULL OR NVL(DBMS_LOB.GETLENGTH(:sql_text), 0) = 0) THEN
DBMS_OUTPUT.PUT_LINE('getting sql_text from awr');
SELECT REPLACE(sql_text, CHR(00), ' ')
INTO :sql_text
FROM dba_hist_sqltext
WHERE :license IN ('T', 'D')
AND dbid = ^^dbid.
AND sql_id = '^^sql_id.'
AND sql_text IS NOT NULL
AND ROWNUM = 1;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('getting sql_text from awr: '||SQLERRM);
:sql_text := NULL;
END;
/
SELECT :sql_text FROM DUAL;
/* -------------------------
*
* assembly title
*
* ------------------------- */
-- signature (force=false)
COL signature NEW_V signature FOR A20;
SELECT TO_CHAR(DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(:sql_text)) signature FROM DUAL;
-- signature (force=true)
VAR signaturef NUMBER;
EXEC :signaturef := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(:sql_text, TRUE);
COL signaturef NEW_V signaturef FOR A20;
SELECT TO_CHAR(:signaturef) signaturef FROM DUAL;
-- get database name (up to 10, stop before first '.', no special characters)
COL database_name_short NEW_V database_name_short FOR A10;
SELECT SUBSTR(SYS_CONTEXT('USERENV', 'DB_NAME'), 1, 10) database_name_short FROM DUAL;
SELECT SUBSTR('^^database_name_short.', 1, INSTR('^^database_name_short..', '.') - 1) database_name_short FROM DUAL;
SELECT TRANSLATE('^^database_name_short.',
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789 ''`~!@#$%^*()-_=+[]{}\|;:",.<>/?'||CHR(0)||CHR(9)||CHR(10)||CHR(13)||CHR(38),
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789') database_name_short FROM DUAL;
-- get host name (up to 30, stop before first '.', no special characters)
COL host_name_short NEW_V host_name_short FOR A30;
SELECT SUBSTR(SYS_CONTEXT('USERENV', 'SERVER_HOST'), 1, 30) host_name_short FROM DUAL;
SELECT SUBSTR('^^host_name_short.', 1, INSTR('^^host_name_short..', '.') - 1) host_name_short FROM DUAL;
SELECT TRANSLATE('^^host_name_short.',
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789 ''`~!@#$%^*()-_=+[]{}\|;:",.<>/?'||CHR(0)||CHR(9)||CHR(10)||CHR(13)||CHR(38),
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789') host_name_short FROM DUAL;
-- get rdbms version
COL rdbms_version NEW_V rdbms_version FOR A17;
SELECT version rdbms_version FROM v$instance;
-- get platform
COL platform NEW_V platform FOR A80;
SELECT UPPER(TRIM(REPLACE(REPLACE(product, 'TNS for '), ':' ))) platform FROM product_component_version WHERE product LIKE 'TNS for%' AND ROWNUM = 1;
-- get instance
COL instance_number NEW_V instance_number FOR A10;
SELECT TO_CHAR(instance_number) instance_number FROM v$instance;
-- YYYYMMDD_HH24MISS
COL time_stamp NEW_V time_stamp FOR A15;
SELECT TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') time_stamp FROM DUAL;
-- YYYY-MM-DD/HH24:MI:SS
COL time_stamp2 NEW_V time_stamp2 FOR A20;
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD/HH24:MI:SS') time_stamp2 FROM DUAL;
-- get db_block_size
COL sys_db_block_size NEW_V sys_db_block_size FOR A17;
SELECT value sys_db_block_size FROM v$system_parameter2 WHERE LOWER(name) = 'db_block_size';
-- get cpu_count
COL sys_cpu NEW_V sys_cpu FOR A17;
SELECT value sys_cpu FROM v$system_parameter2 WHERE LOWER(name) = 'cpu_count';
-- get ofe
COL sys_ofe NEW_V sys_ofe FOR A17;
SELECT value sys_ofe FROM v$system_parameter2 WHERE LOWER(name) = 'optimizer_features_enable';
-- get ds
COL sys_ds NEW_V sys_ds FOR A10;
SELECT value sys_ds FROM v$system_parameter2 WHERE LOWER(name) = 'optimizer_dynamic_sampling';
/* -------------------------
*
* application vendor
*
* ------------------------- */
-- ebs
COL is_ebs NEW_V is_ebs FOR A1;
COL ebs_owner NEW_V ebs_owner FOR A30;
SELECT 'Y' is_ebs, owner ebs_owner
FROM dba_tab_columns
WHERE table_name = 'FND_PRODUCT_GROUPS'
AND column_name = 'RELEASE_NAME'
AND data_type = 'VARCHAR2'
AND ROWNUM = 1;
-- siebel
COL is_siebel NEW_V is_siebel FOR A1;
COL siebel_owner NEW_V siebel_owner FOR A30;
SELECT 'Y' is_siebel, owner siebel_owner
FROM dba_tab_columns
WHERE '^^is_ebs.' IS NULL
AND table_name = 'S_REPOSITORY'
AND column_name = 'ROW_ID'
AND data_type = 'VARCHAR2'
AND ROWNUM = 1;
-- psft
COL is_psft NEW_V is_psft FOR A1;
COL psft_owner NEW_V psft_owner FOR A30;
SELECT 'Y' is_psft, owner psft_owner
FROM dba_tab_columns
WHERE '^^is_ebs.' IS NULL
AND '^^is_siebel.' IS NULL
AND table_name = 'PSSTATUS'
AND column_name = 'TOOLSREL'
AND data_type = 'VARCHAR2'
AND ROWNUM = 1;
/* -------------------------
*
* find tables and indexes
*
* ------------------------- */
-- transaction begins here. it will be rolled back after generating spool file
SAVEPOINT save_point_1;
-- this script uses the gtt plan_table as a temporary staging place to store results of health-checks
DELETE plan_table;
-- record tables
INSERT INTO plan_table (object_type, object_owner, object_name)
WITH object AS (
SELECT /*+ MATERIALIZE */
object_owner owner, object_name name
FROM gv$sql_plan
WHERE :health_checks = 'Y'
AND inst_id IN (SELECT inst_id FROM gv$instance)
AND sql_id = :sql_id
AND object_owner IS NOT NULL
AND object_name IS NOT NULL
UNION
SELECT object_owner owner, object_name name
FROM dba_hist_sql_plan
WHERE :health_checks = 'Y'
AND :license IN ('T', 'D')
AND dbid = ^^dbid.
AND sql_id = :sql_id
AND object_owner IS NOT NULL
AND object_name IS NOT NULL
)
SELECT 'TABLE', t.owner, t.table_name
FROM dba_tab_statistics t, -- include fixed objects
object o
WHERE :health_checks = 'Y'
AND t.owner = o.owner
AND t.table_name = o.name
UNION
SELECT 'TABLE', i.table_owner, i.table_name
FROM dba_indexes i,
object o
WHERE :health_checks = 'Y'
AND i.owner = o.owner
AND i.index_name = o.name;
-- list tables
SELECT object_owner owner, object_name table_name
FROM plan_table
WHERE :health_checks = 'Y'
AND object_type = 'TABLE'
ORDER BY 1, 2;
-- record indexes from known plans
INSERT INTO plan_table (object_type, object_owner, object_name, other_tag)
SELECT 'INDEX', object_owner owner, object_name index_name, 'YES'
FROM gv$sql_plan
WHERE :health_checks = 'Y'
AND inst_id IN (SELECT inst_id FROM gv$instance)
AND sql_id = :sql_id
AND object_owner IS NOT NULL
AND object_name IS NOT NULL
AND (object_type LIKE '%INDEX%' OR operation LIKE '%INDEX%')
UNION
SELECT 'INDEX', object_owner owner, object_name index_name, 'YES'
FROM dba_hist_sql_plan
WHERE :health_checks = 'Y'
AND :license IN ('T', 'D')
AND dbid = ^^dbid.
AND sql_id = :sql_id
AND object_owner IS NOT NULL
AND object_name IS NOT NULL
AND (object_type LIKE '%INDEX%' OR operation LIKE '%INDEX%');
-- record indexes from tables in plan
INSERT INTO plan_table (object_type, object_owner, object_name, other_tag)
SELECT 'INDEX', owner, index_name, 'NO'
FROM plan_table t,
dba_indexes i
WHERE :health_checks = 'Y'
AND t.object_type = 'TABLE'
AND t.object_owner = i.table_owner
AND t.object_name = i.table_name
MINUS
SELECT 'INDEX', object_owner, object_name, 'NO'
FROM plan_table t
WHERE :health_checks = 'Y'
AND object_type = 'INDEX';
COL in_plan FOR A7;
-- list indexes
SELECT object_owner owner, object_name index_name, other_tag in_plan
FROM plan_table
WHERE :health_checks = 'Y'
AND object_type = 'INDEX'
ORDER BY 1, 2;
/* -------------------------
*
* record type enumerator
*
* ------------------------- */
-- constants
VAR E_GLOBAL NUMBER;
VAR E_EBS NUMBER;
VAR E_SIEBEL NUMBER;
VAR E_PSFT NUMBER;
VAR E_TABLE NUMBER;
VAR E_INDEX NUMBER;
VAR E_1COL_INDEX NUMBER;
VAR E_TABLE_PART NUMBER;
VAR E_INDEX_PART NUMBER;
VAR E_TABLE_COL NUMBER;
EXEC :E_GLOBAL := 01;
EXEC :E_EBS := 02;
EXEC :E_SIEBEL := 03;
EXEC :E_PSFT := 04;
EXEC :E_TABLE := 05;
EXEC :E_INDEX := 06;
EXEC :E_1COL_INDEX := 07;
EXEC :E_TABLE_PART := 08;
EXEC :E_INDEX_PART := 09;
EXEC :E_TABLE_COL := 10;
/**************************************************************************************************/
/* -------------------------
*
* global hc
*
* ------------------------- */
-- 5969780 STATISTICS_LEVEL = ALL on LINUX
INSERT INTO plan_table (id, operation, object_alias, other_tag, remarks, projection)
SELECT :E_GLOBAL, 'CBO PARAMETER', SYSTIMESTAMP, 'STATISTICS_LEVEL',
'Parameter STATISTICS_LEVEL is set to ALL on ^^platform. platform.',
'STATISTICS_LEVEL = ALL provides valuable metrics like A-Rows. Be aware of Bug <a target="MOS" href="^^bug_link.5969780">5969780</a> CPU overhead.<br>'||CHR(10)||
'Use a value of ALL only at the session level. You could use CBO hint /*+ gather_plan_statistics */ to accomplish the same.'
FROM v$system_parameter2
WHERE :health_checks = 'Y'
AND UPPER(name) = 'STATISTICS_LEVEL'
AND UPPER(value) = 'ALL'
AND '^^rdbms_version.' LIKE '10%'
AND '^^platform.' LIKE '%LINUX%';
-- cbo parameters with non-default values at sql level
INSERT INTO plan_table (id, operation, object_alias, other_tag, remarks, projection)
SELECT :E_GLOBAL, 'CBO PARAMETER', SYSTIMESTAMP, UPPER(name),
'CBO initialization parameter "'||name||'" with a non-default value of "'||value||'" as per V$SQL_OPTIMIZER_ENV.',
'Review the correctness of this non-default value "'||value||'" for SQL_ID '||:sql_id||'.'
FROM (
SELECT DISTINCT name, value
FROM v$sql_optimizer_env
WHERE :health_checks = 'Y'
AND sql_id = :sql_id
AND isdefault = 'NO' );
-- cbo parameters with non-default values at system level
INSERT INTO plan_table (id, operation, object_alias, other_tag, remarks, projection)
SELECT :E_GLOBAL, 'CBO PARAMETER', SYSTIMESTAMP, UPPER(g.name),
'CBO initialization parameter "'||g.name||'" with a non-default value of "'||g.value||'" as per V$SYS_OPTIMIZER_ENV.',
'Review the correctness of this non-default value "'||g.value||'".<br>'||CHR(10)||
'Unset this parameter unless there is a strong reason for keeping its current value.<br>'||CHR(10)||
'Default value is "'||g.default_value||'" as per V$SYS_OPTIMIZER_ENV.'
FROM v$sys_optimizer_env g
WHERE :health_checks = 'Y'
AND g.isdefault = 'NO'
AND NOT EXISTS (
SELECT NULL
FROM v$sql_optimizer_env s
WHERE :health_checks = 'Y'
AND s.sql_id = :sql_id
AND s.isdefault = 'NO'
AND s.name = g.name
AND s.value = g.value );
-- optimizer_features_enable <> rdbms_version at system level
INSERT INTO plan_table (id, operation, object_alias, other_tag, remarks, projection)
SELECT :E_GLOBAL, 'CBO PARAMETER', SYSTIMESTAMP, 'OPTIMIZER_FEATURES_ENABLE',
'DB version ^^rdbms_version. and OPTIMIZER_FEATURES_ENABLE ^^sys_ofe. do not match as per V$SYSTEM_PARAMETER2.',
'Be aware that you are using a prior version of the optimizer. New CBO features in your DB version may not be used.'
FROM DUAL
WHERE :health_checks = 'Y'
AND SUBSTR('^^rdbms_version.', 1, LEAST(LENGTH('^^rdbms_version.'), LENGTH('^^sys_ofe.'))) <> SUBSTR('^^sys_ofe.', 1, LEAST(LENGTH('^^rdbms_version.'), LENGTH('^^sys_ofe.')));
-- optimizer_features_enable <> rdbms_version at sql level
INSERT INTO plan_table (id, operation, object_alias, other_tag, remarks, projection)
SELECT :E_GLOBAL, 'CBO PARAMETER', SYSTIMESTAMP, 'OPTIMIZER_FEATURES_ENABLE',
'DB version ^^rdbms_version. and OPTIMIZER_FEATURES_ENABLE '||v.value||' do not match for SQL_ID '||:sql_id||' as per V$SQL_OPTIMIZER_ENV.',
'Be aware that you are using a prior version of the optimizer. New CBO features in your DB version may not be used.'
FROM (
SELECT DISTINCT value
FROM v$sql_optimizer_env
WHERE :health_checks = 'Y'
AND sql_id = :sql_id
AND LOWER(name) = 'optimizer_features_enable'
AND SUBSTR('^^rdbms_version.', 1, LEAST(LENGTH('^^rdbms_version.'), LENGTH(value))) <> SUBSTR(value, 1, LEAST(LENGTH('^^rdbms_version.'), LENGTH(value))) ) v;
-- optimizer_dynamic_sampling between 1 and 3 at system level
INSERT INTO plan_table (id, operation, object_alias, other_tag, remarks, projection)
SELECT :E_GLOBAL, 'CBO PARAMETER', SYSTIMESTAMP, 'OPTIMIZER_DYNAMIC_SAMPLING',
'Dynamic Sampling is set to small value of ^^sys_ds. as per V$SYSTEM_PARAMETER2.',
'Be aware that using such a small value may produce statistics of poor quality.<br>'||CHR(10)||
'If you rely on this functionality consider using a value no smaller than 4.'
FROM plan_table pt,
dba_tables t
WHERE :health_checks = 'Y'
AND TO_NUMBER('^^sys_ds.') BETWEEN 1 AND 3
AND pt.object_type = 'TABLE'
AND pt.object_owner = t.owner
AND pt.object_name = t.table_name
AND t.temporary = 'N'
AND (t.last_analyzed IS NULL OR t.num_rows IS NULL)
AND ROWNUM = 1;
-- db_file_multiblock_read_count should not be set
INSERT INTO plan_table (id, operation, object_alias, other_tag, remarks, projection)
SELECT :E_GLOBAL, 'CBO PARAMETER', SYSTIMESTAMP, 'DB_FILE_MULTIBLOCK_READ_COUNT',
'MBRC Parameter is set to "'||value||'" overriding its default value.',
'The default value of this parameter is a value that corresponds to the maximum I/O size that can be performed efficiently.<br>'||CHR(10)||
'This value is platform-dependent and is 1MB for most platforms.<br>'||CHR(10)||
'Because the parameter is expressed in blocks, it will be set to a value that is equal to the maximum I/O size that can be performed efficiently divided by the standard block size.'
FROM v$system_parameter2
WHERE :health_checks = 'Y'
AND UPPER(name) = 'DB_FILE_MULTIBLOCK_READ_COUNT'
AND (isdefault = 'FALSE' OR ismodified <> 'FALSE');
-- nls_sort is not binary (session)
INSERT INTO plan_table (id, operation, object_alias, other_tag, remarks, projection)
SELECT :E_GLOBAL, 'NLS PARAMETER', SYSTIMESTAMP, 'NLS_SORT',
'NLS_SORT Session Parameter is set to "'||value||'" in V$NLS_PARAMETERS.',
'Setting NLS_SORT to anything other than BINARY causes a sort to use a full table scan, regardless of the path chosen by the optimizer.'
FROM v$nls_parameters
WHERE :health_checks = 'Y'
AND UPPER(parameter) = 'NLS_SORT'
AND UPPER(value) <> 'BINARY';
-- nls_sort is not binary (instance)
INSERT INTO plan_table (id, operation, object_alias, other_tag, remarks, projection)
SELECT :E_GLOBAL, 'NLS PARAMETER', SYSTIMESTAMP, 'NLS_SORT',
'NLS_SORT Instance Parameter is set to "'||value||'" in V$SYSTEM_PARAMETER.',
'Setting NLS_SORT to anything other than BINARY causes a sort to use a full table scan, regardless of the path chosen by the optimizer.'
FROM v$system_parameter
WHERE :health_checks = 'Y'
AND UPPER(name) = 'NLS_SORT'
AND UPPER(value) <> 'BINARY';
-- nls_sort is not binary (global)
INSERT INTO plan_table (id, operation, object_alias, other_tag, remarks, projection)
SELECT :E_GLOBAL, 'NLS PARAMETER', SYSTIMESTAMP, 'NLS_SORT',
'NLS_SORT Global Parameter is set to "'||value||'" in NLS_DATABASE_PARAMETERS.',
'Setting NLS_SORT to anything other than BINARY causes a sort to use a full table scan, regardless of the path chosen by the optimizer.'
FROM nls_database_parameters
WHERE :health_checks = 'Y'
AND UPPER(parameter) = 'NLS_SORT'
AND UPPER(value) <> 'BINARY';
-- DBMS_STATS automatic gathering on 10g
INSERT INTO plan_table (id, operation, object_alias, other_tag, remarks, projection)
SELECT :E_GLOBAL, 'DBMS_STATS', SYSTIMESTAMP, 'DBA_SCHEDULER_JOBS',
'Automatic gathering of CBO statistics is enabled.',
CASE
WHEN '^^is_ebs.' = 'Y' THEN
'Disable this job immediately and re-gather statistics for all affected schemas using FND_STATS or coe_stats.sql.<br>'||CHR(10)||
'See <a target="MOS" href="^^doc_link.156968.1">156968.1</a>.'
WHEN '^^is_siebel.' = 'Y' THEN
'Disable this job immediately and re-gather statistics for all affected schemas using coe_siebel_stats.sql.<br>'||CHR(10)||
'See <a target="MOS" href="^^doc_link.781927.1">781927.1</a>.'
WHEN '^^is_psft.' = 'Y' THEN
'Disable this job immediately and re-gather statistics for all affected schemas using pscbo_stats.sql.<br>'||CHR(10)||
'See <a target="MOS" href="^^doc_link.1322888.1">1322888.1</a>.'
ELSE
'Be aware that small sample sizes could produce poor quality histograms,<br>'||CHR(10)||
'which combined with bind sensitive predicates could render suboptimal plans.<br>'||CHR(10)||
'See <a target="MOS" href="^^doc_link.465787.1">465787.1</a>.'
END
FROM dba_scheduler_jobs
WHERE :health_checks = 'Y'
AND job_name = 'GATHER_STATS_JOB'
AND enabled = 'TRUE';
-- DBMS_STATS automatic gathering on 11g
INSERT INTO plan_table (id, operation, object_alias, other_tag, remarks, projection)
SELECT :E_GLOBAL, 'DBMS_STATS', SYSTIMESTAMP, 'DBA_AUTOTASK_CLIENT',
'Automatic gathering of CBO statistics is enabled.',
CASE
WHEN '^^is_ebs.' = 'Y' THEN
'Disable this job immediately and re-gather statistics for all affected schemas using FND_STATS or coe_stats.sql.<br>'||CHR(10)||
'See <a target="MOS" href="^^doc_link.156968.1">156968.1</a>.'
WHEN '^^is_siebel.' = 'Y' THEN
'Disable this job immediately and re-gather statistics for all affected schemas using coe_siebel_stats.sql.<br>'||CHR(10)||
'See <a target="MOS" href="^^doc_link.781927.1">781927.1</a>.'
WHEN '^^is_psft.' = 'Y' THEN
'Disable this job immediately and re-gather statistics for all affected schemas using pscbo_stats.sql.<br>'||CHR(10)||
'See <a target="MOS" href="^^doc_link.1322888.1">1322888.1</a>.'
ELSE
'Be aware that small sample sizes could produce poor quality histograms,<br>'||CHR(10)||
'which combined with bind sensitive predicates could render suboptimal plans.<br>'||CHR(10)||
'See <a target="MOS" href="^^doc_link.465787.1">465787.1</a>.'
END
FROM dba_autotask_client
WHERE :health_checks = 'Y'
AND client_name = 'auto optimizer stats collection'
AND status = 'ENABLED';
-- multiple CBO environments in SQL Area
INSERT INTO plan_table (id, operation, object_alias, other_tag, remarks, projection)
SELECT :E_GLOBAL, 'PLAN', SYSTIMESTAMP, 'OPTIMIZER_ENV',
'SQL Area references '||COUNT(DISTINCT optimizer_env_hash_value)||' distinct CBO Enviornments for this one SQL.',
'Distinct CBO Environments may produce different Plans.'
FROM gv$sqlarea_plan_hash
WHERE :health_checks = 'Y'
AND sql_id = :sql_id
HAVING COUNT(*) > 1;
-- multiple CBO environments in GV$SQL
INSERT INTO plan_table (id, operation, object_alias, other_tag, remarks, projection)
SELECT :E_GLOBAL, 'PLAN', SYSTIMESTAMP, 'OPTIMIZER_ENV',
'GV$SQL references '||COUNT(DISTINCT optimizer_env_hash_value)||' distinct CBO Enviornments for this one SQL.',
'Distinct CBO Environments may produce different Plans.'
FROM gv$sql
WHERE :health_checks = 'Y'
AND sql_id = :sql_id
HAVING COUNT(*) > 1;
-- multiple CBO environments in AWR
INSERT INTO plan_table (id, operation, object_alias, other_tag, remarks, projection)
SELECT :E_GLOBAL, 'PLAN', SYSTIMESTAMP, 'OPTIMIZER_ENV',
'AWR references '||COUNT(DISTINCT optimizer_env_hash_value)||' distinct CBO Enviornments for this one SQL.',
'Distinct CBO Environments may produce different Plans.'
FROM dba_hist_sqlstat
WHERE :health_checks = 'Y'
AND :license IN ('T', 'D')
AND dbid = ^^dbid.
AND sql_id = :sql_id
HAVING COUNT(*) > 1;
-- plans with implicit data_type conversion
INSERT INTO plan_table (id, operation, object_alias, other_tag, remarks, projection)
SELECT :E_GLOBAL, 'PLAN', SYSTIMESTAMP, 'PLAN_HASH_VALUE',
'Plan '||v.plan_hash_value||' may have implicit data_type conversion functions in Filter Predicates.',
'Review Execution Plans.<br>'||CHR(10)||
'If Filter Predicates for '||v.plan_hash_value||' include unexpected INTERNAL_FUNCTION to perform an implicit data_type conversion,<br>'||CHR(10)||
'be sure it is not preventing a column from being used as an Access Predicate.'
FROM (
SELECT DISTINCT plan_hash_value
FROM gv$sql_plan
WHERE :health_checks = 'Y'
AND inst_id IN (SELECT inst_id FROM gv$instance)
AND sql_id = :sql_id
AND filter_predicates LIKE '%INTERNAL_FUNCTION%'
ORDER BY 1) v;
-- plan operations with cost 0 and card 1
INSERT INTO plan_table (id, operation, object_alias, other_tag, remarks, projection)
SELECT :E_GLOBAL, 'PLAN', SYSTIMESTAMP, 'PLAN_HASH_VALUE',
'Plan '||v.plan_hash_value||' has operations with Cost 0 and Card 1. Possible incorrect Selectivity.',
'Review Execution Plans.<br>'||CHR(10)||
'Look for Plan operations in '||v.plan_hash_value||' where Cost is 0 and Estimated Cardinality is 1.<br>'||CHR(10)||
'Suspect predicates out of range or incorrect statistics.'
FROM (
SELECT plan_hash_value
FROM gv$sql_plan
WHERE :health_checks = 'Y'
AND inst_id IN (SELECT inst_id FROM gv$instance)
AND sql_id = :sql_id
AND cost = 0
AND cardinality = 1
UNION
SELECT plan_hash_value
FROM dba_hist_sql_plan
WHERE :health_checks = 'Y'
AND :license IN ('T', 'D')
AND dbid = ^^dbid.
AND sql_id = :sql_id
AND cost = 0
AND cardinality = 1) v;
-- high version count
INSERT INTO plan_table (id, operation, object_alias, other_tag, remarks, projection)
SELECT :E_GLOBAL, 'VERSION COUNT', SYSTIMESTAMP, 'VERSION COUNT',
'This SQL shows evidence of high version count of '||MAX(v.version_count)||'.',
'Review Execution Plans for details.'
FROM (
SELECT MAX(version_count) version_count
FROM gv$sqlarea_plan_hash
WHERE :health_checks = 'Y'
AND sql_id = :sql_id
UNION
SELECT MAX(version_count) version_count
FROM dba_hist_sqlstat
WHERE :health_checks = 'Y'
AND :license IN ('T', 'D')
AND dbid = ^^dbid.
AND sql_id = :sql_id ) v
HAVING MAX(v.version_count) > 20;
-- first rows
INSERT INTO plan_table (id, operation, object_alias, other_tag, remarks, projection)
SELECT :E_GLOBAL, 'OPTIMZER MODE', SYSTIMESTAMP, 'FIRST_ROWS',
'OPTIMIZER_MODE was set to FIRST_ROWS in '||v.pln_count||' Plan(s).',
'The optimizer uses a mix of cost and heuristics to find a best plan for fast delivery of the first few rows.<br>'||CHR(10)||
'Using heuristics sometimes leads the query optimizer to generate a plan with a cost that is significantly larger than the cost of a plan without applying the heuristic.<br>'||CHR(10)||
'FIRST_ROWS is available for backward compatibility and plan stability; use FIRST_ROWS_n instead.'
FROM (
SELECT COUNT(*) pln_count
FROM (
SELECT plan_hash_value
FROM gv$sql
WHERE :health_checks = 'Y'
AND sql_id = :sql_id
AND optimizer_mode = 'FIRST_ROWS'
UNION
SELECT plan_hash_value
FROM dba_hist_sqlstat
WHERE :health_checks = 'Y'
AND :license IN ('T', 'D')
AND dbid = ^^dbid.
AND sql_id = :sql_id
AND optimizer_mode = 'FIRST_ROWS') v) v
WHERE :health_checks = 'Y'
AND v.pln_count > 0;
-- fixed objects missing stats
INSERT INTO plan_table (id, operation, object_alias, other_tag, remarks, projection)
SELECT :E_GLOBAL, 'FIXED OBJECTS', SYSTIMESTAMP, 'DBA_TAB_COL_STATISTICS',
'There exist(s) '||v.tbl_count||' Fixed Object(s) accessed by this SQL without CBO statistics.',
'Consider gathering statistics for fixed objects using DBMS_STATS.GATHER_FIXED_OBJECTS_STATS.<br>'||CHR(10)||
'See <a target="MOS" href="^^doc_link.465787.1">465787.1</a>.'
FROM (
SELECT COUNT(*) tbl_count
FROM plan_table pt,
dba_tab_statistics t
WHERE :health_checks = 'Y'
AND pt.object_type = 'TABLE'
AND pt.object_owner = t.owner
AND pt.object_name = t.table_name
AND t.object_type = 'FIXED TABLE'
AND NOT EXISTS (
SELECT NULL
FROM dba_tab_cols c
WHERE :health_checks = 'Y'
AND t.owner = c.owner
AND t.table_name = c.table_name )) v
WHERE :health_checks = 'Y'
AND v.tbl_count > 0;
-- system statistics not gathered
INSERT INTO plan_table (id, operation, object_alias, other_tag, remarks, projection)
SELECT :E_GLOBAL, 'DBMS_STATS', SYSTIMESTAMP, 'SYSTEM STATISTICS',
'Workload CBO System Statistics are not gathered. CBO is using default values.',
'Consider gathering workload system statistics using DBMS_STATS.GATHER_SYSTEM_STATS.<br>'||CHR(10)||
'See also <a target="MOS" href="^^doc_link.465787.1">465787.1</a>.'
FROM sys.aux_stats$
WHERE :health_checks = 'Y'
AND sname = 'SYSSTATS_MAIN'
AND pname = 'CPUSPEED'
AND pval1 IS NULL;
-- mreadtim < sreadtim
INSERT INTO plan_table (id, operation, object_alias, other_tag, remarks, projection)
SELECT :E_GLOBAL, 'DBMS_STATS', SYSTIMESTAMP, 'SYSTEM STATISTICS',
'Multi-block read time of '||a1.pval1||'ms seems too small compared to single-block read time of '||a2.pval1||'ms.',
'Consider gathering workload system statistics using DBMS_STATS.GATHER_SYSTEM_STATS or adjusting SREADTIM and MREADTIM using DBMS_STATS.SET_SYSTEM_STATS.<br>'||CHR(10)||
'See also <a target="MOS" href="^^doc_link.465787.1">465787.1</a>.'
FROM sys.aux_stats$ a1, sys.aux_stats$ a2
WHERE :health_checks = 'Y'
AND a1.sname = 'SYSSTATS_MAIN'
AND a1.pname = 'MREADTIM'
AND a2.sname = 'SYSSTATS_MAIN'
AND a2.pname = 'SREADTIM'
AND a1.pval1 < a2.pval1;
-- sreadtim < 2
INSERT INTO plan_table (id, operation, object_alias, other_tag, remarks, projection)
SELECT :E_GLOBAL, 'DBMS_STATS', SYSTIMESTAMP, 'SYSTEM STATISTICS',
'Single-block read time of '||pval1||' milliseconds seems too small.',
'Consider gathering workload system statistics using DBMS_STATS.GATHER_SYSTEM_STATS or adjusting SREADTIM using DBMS_STATS.SET_SYSTEM_STATS.<br>'||CHR(10)||
'See also <a target="MOS" href="^^doc_link.465787.1">465787.1</a>.'
FROM sys.aux_stats$
WHERE :health_checks = 'Y'
AND sname = 'SYSSTATS_MAIN'
AND pname = 'SREADTIM'
AND pval1 < 2;
-- mreadtim < 3
INSERT INTO plan_table (id, operation, object_alias, other_tag, remarks, projection)
SELECT :E_GLOBAL, 'DBMS_STATS', SYSTIMESTAMP, 'SYSTEM STATISTICS',
'Multi-block read time of '||pval1||' milliseconds seems too small.',
'Consider gathering workload system statistics using DBMS_STATS.GATHER_SYSTEM_STATS or adjusting MREADTIM using DBMS_STATS.SET_SYSTEM_STATS.<br>'||CHR(10)||
'See also <a target="MOS" href="^^doc_link.465787.1">465787.1</a>.'
FROM sys.aux_stats$
WHERE :health_checks = 'Y'
AND sname = 'SYSSTATS_MAIN'
AND pname = 'MREADTIM'
AND pval1 < 3;
-- sreadtim > 18
INSERT INTO plan_table (id, operation, object_alias, other_tag, remarks, projection)
SELECT :E_GLOBAL, 'DBMS_STATS', SYSTIMESTAMP, 'SYSTEM STATISTICS',
'Single-block read time of '||pval1||' milliseconds seems too large.',
'Consider gathering workload system statistics using DBMS_STATS.GATHER_SYSTEM_STATS or adjusting SREADTIM using DBMS_STATS.SET_SYSTEM_STATS.<br>'||CHR(10)||
'See also <a target="MOS" href="^^doc_link.465787.1">465787.1</a> and Bug <a target="MOS" href="^^bug_link.9842771">9842771</a>.'
FROM sys.aux_stats$
WHERE :health_checks = 'Y'
AND sname = 'SYSSTATS_MAIN'
AND pname = 'SREADTIM'
AND pval1 > 18;
-- mreadtim > 522
INSERT INTO plan_table (id, operation, object_alias, other_tag, remarks, projection)
SELECT :E_GLOBAL, 'DBMS_STATS', SYSTIMESTAMP, 'SYSTEM STATISTICS',
'Multi-block read time of '||pval1||' milliseconds seems too large.',
'Consider gathering workload system statistics using DBMS_STATS.GATHER_SYSTEM_STATS or adjusting MREADTIM using DBMS_STATS.SET_SYSTEM_STATS.<br>'||CHR(10)||
'See also <a target="MOS" href="^^doc_link.465787.1">465787.1</a> and Bug <a target="MOS" href="^^bug_link.9842771">9842771</a>.'
FROM sys.aux_stats$
WHERE :health_checks = 'Y'
AND sname = 'SYSSTATS_MAIN'
AND pname = 'MREADTIM'
AND pval1 > 522;
-- sql with policies as per v$vpd_policy
INSERT INTO plan_table (id, operation, object_alias, other_tag, remarks, projection)
SELECT :E_GLOBAL, 'VDP', SYSTIMESTAMP, 'V$VPD_POLICY',
'Virtual Private Database. There is one or more policies affecting this SQL.',
'Review Execution Plans and look for their injected predicates.'
FROM v$vpd_policy
WHERE :health_checks = 'Y'
AND sql_id = :sql_id
HAVING COUNT(*) > 0;
-- materialized views with rewrite enabled
INSERT INTO plan_table (id, operation, object_alias, other_tag, remarks, projection)
SELECT :E_GLOBAL, 'MAT_VIEW', SYSTIMESTAMP, 'REWRITE_ENABLED',
'There are '||COUNT(*)||' materialized views with rewrite enabled.',
'A large number of materialized views could affect parsing time since CBO would have to evaluate each during a hard-parse.'
FROM v$system_parameter2 p,
dba_mviews m
WHERE :health_checks = 'Y'
AND UPPER(p.name) = 'QUERY_REWRITE_ENABLED'
AND UPPER(p.value) = 'TRUE'
AND m.rewrite_enabled = 'Y'
HAVING COUNT(*) > 1;
-- table with bitmap index(es)
INSERT INTO plan_table (id, operation, object_alias, other_tag, remarks, projection)
SELECT :E_GLOBAL, 'INDEX', SYSTIMESTAMP, 'BITMAP',
'Your DML statement references '||COUNT(DISTINCT pt.object_name||pt.object_owner)||' Table(s) with at least one Bitmap index.',
'Be aware that frequent DML operations operations in a Table with Bitmap indexes may produce contention where concurrent DML operations are common. If your SQL suffers of "TX-enqueue row lock contention" suspect this situation.'
FROM plan_table pt,
dba_indexes i
WHERE :health_checks = 'Y'
AND pt.object_type = 'TABLE'
AND pt.object_owner = i.table_owner
AND pt.object_name = i.table_name
AND i.index_type = 'BITMAP'
AND EXISTS (
SELECT NULL
FROM gv$sqlarea s
WHERE :health_checks = 'Y'
AND s.sql_id = :sql_id
AND s.command_type IN (2, 6, 7)) -- INSERT, UPDATE, DELETE
HAVING COUNT(*) > 0;
-- index in plan no longer exists
INSERT INTO plan_table (id, operation, object_alias, other_tag, remarks, projection)
SELECT DISTINCT :E_INDEX, 'INDEX', SYSTIMESTAMP, pt.object_owner||'.'||pt.object_name,
'Index referenced by an Execution Plan no longer exists.',
'If a Plan references a missing index then this Plan can no longer be generated by the CBO.'
FROM plan_table pt
WHERE :health_checks = 'Y'
AND pt.object_type = 'INDEX'
AND pt.other_tag = 'YES'
AND NOT EXISTS (
SELECT NULL
FROM dba_indexes i
WHERE :health_checks = 'Y'
AND pt.object_owner = i.owner
AND pt.object_name = i.index_name );
-- index in plan is now unusable
INSERT INTO plan_table (id, operation, object_alias, other_tag, remarks, projection)
SELECT DISTINCT :E_INDEX, 'INDEX', SYSTIMESTAMP, pt.object_owner||'.'||pt.object_name,
'Index referenced by an Execution Plan is now unusable.',
'If a Plan references an unusable index then this Plan cannot be generated by the CBO.<br>'||CHR(10)||
'If you need to enable tha Plan that references this index you need to rebuild it first.'
FROM plan_table pt,
dba_indexes i
WHERE :health_checks = 'Y'
AND pt.object_type = 'INDEX'
AND pt.other_tag = 'YES'
AND pt.object_owner = i.owner
AND pt.object_name = i.index_name
AND i.partitioned = 'NO'
AND i.status = 'UNUSABLE';
-- index in plan has now unusable partitions
INSERT INTO plan_table (id, operation, object_alias, other_tag, remarks, projection)
SELECT DISTINCT :E_INDEX, 'INDEX', SYSTIMESTAMP, pt.object_owner||'.'||pt.object_name,
'Index referenced by an Execution Plan has now unusable partitions.',
'If a Plan references an index with unusable partitions then this Plan cannot be generated by the CBO.<br>'||CHR(10)||
'If you need to enable tha Plan that references this index you need to rebuild the unusable partitions first.'
FROM plan_table pt,
dba_indexes i,
dba_ind_partitions p
WHERE :health_checks = 'Y'
AND pt.object_type = 'INDEX'
AND pt.other_tag = 'YES'
AND pt.object_owner = i.owner
AND pt.object_name = i.index_name
AND i.partitioned = 'YES'
AND pt.object_owner = p.index_owner
AND pt.object_name = p.index_name
AND p.status = 'UNUSABLE';
-- index in plan has now unusable subpartitions
INSERT INTO plan_table (id, operation, object_alias, other_tag, remarks, projection)
SELECT DISTINCT :E_INDEX, 'INDEX', SYSTIMESTAMP, pt.object_owner||'.'||pt.object_name,
'Index referenced by an Execution Plan has now unusable subpartitions.',
'If a Plan references an index with unusable subpartitions then this Plan cannot be generated by the CBO.<br>'||CHR(10)||
'If you need to enable tha Plan that references this index you need to rebuild the unusable subpartitions first.'
FROM plan_table pt,
dba_indexes i,
dba_ind_subpartitions p
WHERE :health_checks = 'Y'
AND pt.object_type = 'INDEX'
AND pt.other_tag = 'YES'
AND pt.object_owner = i.owner
AND pt.object_name = i.index_name
AND i.partitioned = 'YES'
AND pt.object_owner = p.index_owner
AND pt.object_name = p.index_name
AND p.status = 'UNUSABLE';
-- index in plan is now invisible
INSERT INTO plan_table (id, operation, object_alias, other_tag, remarks, projection)
SELECT DISTINCT :E_INDEX, 'INDEX', SYSTIMESTAMP, pt.object_owner||'.'||pt.object_name,
'Index referenced by an Execution Plan is now invisible.',
'If a Plan references an invisible index then this Plan cannot be generated by the CBO.<br>'||CHR(10)||
'If you need to enable tha Plan that references this index you need to make this index visible.'
FROM plan_table pt,
dba_indexes i
WHERE :health_checks = 'Y'
AND pt.object_type = 'INDEX'
AND pt.other_tag = 'YES'
AND pt.object_owner = i.owner
AND pt.object_name = i.index_name
AND i.partitioned = 'NO'
AND i.visibility = 'INVISIBLE';
-- unusable indexes
INSERT INTO plan_table (id, operation, object_alias, other_tag, remarks, projection)
SELECT :E_GLOBAL, 'INDEX', SYSTIMESTAMP, 'UNUSABLE',
'There are '||COUNT(*)||' unusable index(es) in tables being accessed by your SQL.',
'Unusable indexes cannot be used by the CBO. This may cause Execution Plans to change.'
FROM plan_table pt,
dba_indexes i
WHERE :health_checks = 'Y'
AND pt.object_type = 'INDEX'
AND pt.object_owner = i.owner
AND pt.object_name = i.index_name
AND i.partitioned = 'NO'
AND i.status = 'UNUSABLE'
HAVING COUNT(*) > 0;
-- unusable index partitions