forked from fatdba/Oracle-Database-Scripts
-
Notifications
You must be signed in to change notification settings - Fork 0
/
PXHCDR.sql
2429 lines (2229 loc) · 72.4 KB
/
PXHCDR.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
-- Oracle provided script
SPO pxhcdr.log
SET DEF ^ TERM OFF ECHO ON VER OFF SERVEROUT ON SIZE 1000000;
REM
REM $Header: 1460440.1 pxhcdr.sql 12.1.09 2013/06/13 carlos.sierra mauro.pagano $
REM
REM Copyright (c) 2000-2013, Oracle Corporation. All rights reserved.
REM
REM AUTHOR
REM
REM SCRIPT
REM pxhcdr.sql
REM
REM DESCRIPTION
REM Parallel Execution Health-Checks and Diagnostics Reports.
REM
REM This read-only script performs two functions with regard to
REM system-wide parallel execution:
REM 1. Reports on a set of commonly used health-checks.
REM 2. Generates a set of diagnostics reports based on PX
REM performance views, PX static views and system tables.
REM
REM Since pxhcdr.sql is a read-only script, which installs nothing
REM and updates nothing, it is safe to use on any Oracle database
REM 10g or higher, including Dataguard and read-only systems.
REM
REM PRE-REQUISITES
REM 1. Execute as SYS or user with DBA role or user with access
REM to data dictionary views.
REM
REM PARAMETERS
REM 1. Oracle Pack license (Tuning or Diagnostics or None) T|D|N
REM 2. Target directory path for output (optional). Default is cuurent.
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 pxhcdr.sql passing values for parameter.
REM
REM EXAMPLE
REM # sqlplus / as sysdba
REM SQL> START [path]pxhcdr.sql [T|D|N] [target_path]
REM
REM NOTES
REM 1. For possible errors see pxhcdr.log.
REM 2. If site has both Tuning and Diagnostics licenses then
REM specified T (Oracle Tuning pack includes Oracle Diagnostics)
REM
DEF monitor_reports = '25';
DEF small_table_threshold = '1e9';
/**************************************************************************************************/
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
PRO Parameter 2:
PRO Target directory path for script output (optional)
PRO
DEF output_path = '^2';
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 full_path NEW_V full_path;
SELECT TRIM('^^output_path.')||CASE
WHEN INSTR('^^output_path.', '/') > 0 THEN '/'
WHEN INSTR('^^output_path.', '\') > 0 THEN '\'
END full_path
FROM DUAL;
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 Value passed:
PRO ~~~~~~~~~~~~
PRO License: "^^input_license."
PRO
SET ECHO ON TIMI ON;
DEF script = 'pxhcdr';
DEF method = 'PXHCDR';
DEF mos_doc = '1460440.1';
DEF doc_ver = '12.1.09';
DEF doc_date = '2014/06/13';
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';
/**************************************************************************************************/
/* -------------------------
*
* assembly title
*
* ------------------------- */
-- 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;
/**************************************************************************************************/
/* -------------------------
*
* main report
*
* ------------------------- */
-- setup to produce report
SET ECHO OFF FEED OFF VER OFF SHOW OFF HEA OFF LIN 2000 NEWP NONE PAGES 0 NUM 20 LONG 2000000 LONGC 2000 SQLC MIX TAB ON TRIMS ON TI OFF TIMI OFF ARRAY 100 NUMF "" SQLP SQL> SUF sql BLO . RECSEP OFF APPI OFF AUTOT OFF;
/* -------------------------
*
* gv$sql_shared_cursor
*
* ------------------------- */
SPO sql_shared_cursor.sql;
PRO SELECT /* ^^script..sql Cursor Sharing as per Reason */
PRO CHR(10)||'<tr>'||CHR(10)||
PRO '<td class="r">'||ROWNUM||'</td>'||CHR(10)||
PRO '<td>'||v2.reason||'</td>'||CHR(10)||
PRO '<td class="c">'||v2.inst_id||'</td>'||CHR(10)||
PRO '<td class="r">'||v2.cursors||'</td>'||CHR(10)||
PRO '</tr>'
PRO FROM (
SELECT (CASE WHEN ROWNUM > 1 THEN 'UNION ALL'||CHR(10) END)||
'SELECT '''||v.column_name||''' reason, inst_id, COUNT(*) cursors FROM gv$sql_shared_cursor WHERE '||v.column_name||' = ''Y'' GROUP BY inst_id' line
FROM (
SELECT /*+ NO_MERGE */
column_name
FROM dba_tab_cols
WHERE owner = 'SYS'
AND table_name = 'GV_$SQL_SHARED_CURSOR'
AND data_type = 'VARCHAR2'
AND data_length = 1
ORDER BY
column_name ) v;
PRO ORDER BY reason, inst_id ) v2;;
SPO OFF;
/* -------------------------
*
* heading
*
* ------------------------- */
SPO ^^script._^^database_name_short._^^host_name_short._^^rdbms_version._^^time_stamp._main.html;
PRO <html>
PRO <!-- $Header: ^^mos_doc. ^^script..sql ^^doc_ver. ^^doc_date. carlos.sierra $ -->
PRO <!-- Copyright (c) 2000-2012, Oracle Corporation. All rights reserved. -->
PRO <!-- Author: [email protected] -->
PRO
PRO <head>
PRO <title>^^script._^^database_name_short._^^host_name_short._^^rdbms_version._^^time_stamp._main.html</title>
PRO
PRO <style type="text/css">
PRO body {font:10pt Arial,Helvetica,Verdana,Geneva,sans-serif; color:black; background:white;}
PRO a {font-weight:bold; color:#663300;}
PRO pre {font:8pt Monaco,"Courier New",Courier,monospace;} /* for code */
PRO h1 {font-size:16pt; font-weight:bold; color:#336699;}
PRO h2 {font-size:14pt; font-weight:bold; color:#336699;}
PRO h3 {font-size:12pt; font-weight:bold; color:#336699;}
PRO li {font-size:10pt; font-weight:bold; color:#336699; padding:0.1em 0 0 0;}
PRO table {font-size:8pt; color:black; background:white;}
PRO th {font-weight:bold; background:#cccc99; color:#336699; vertical-align:bottom; padding-left:3pt; padding-right:3pt; padding-top:1pt; padding-bottom:1pt;}
PRO td {text-align:left; background:#fcfcf0; vertical-align:top; padding-left:3pt; padding-right:3pt; padding-top:1pt; padding-bottom:1pt;}
PRO td.c {text-align:center;} /* center */
PRO td.l {text-align:left;} /* left (default) */
PRO td.r {text-align:right;} /* right */
PRO font.n {font-size:8pt; font-style:italic; color:#336699;} /* table footnote in blue */
PRO font.f {font-size:8pt; color:#999999;} /* footnote in gray */
PRO </style>
PRO
PRO </head>
PRO <body>
PRO <h1><a target="MOS" href="^^doc_link.^^mos_doc.">^^mos_doc.</a> ^^method.
PRO ^^doc_ver. Report: ^^script._^^database_name_short._^^host_name_short._^^rdbms_version._^^time_stamp._main.html</h1>
PRO
PRO <pre>
PRO License : ^^input_license.
PRO RDBMS : ^^rdbms_version.
PRO Platform : ^^platform.
PRO Instance : ^^instance_number.
PRO CPU Count : ^^sys_cpu.
PRO Block Size: ^^sys_db_block_size.
PRO OFE : ^^sys_ofe.
PRO DYN_SAMP : ^^sys_ds.
PRO EBS : "^^is_ebs."
PRO SIEBEL : "^^is_siebel."
PRO PSFT : "^^is_psft."
PRO Date : ^^time_stamp2.
PRO </pre>
PRO <ul>
PRO <li><a href="#obs">Observations</a></li>
PRO <li><a href="#pdlm">Parallel Degree Limit Method</a></li>
PRO <li><a href="#buf_adv">PX Buffer Advice</a></li>
PRO <li><a href="#pq_syssta">PQ System Statistics</a></li>
PRO <li><a href="#px_syssta">PX System Statistics</a></li>
PRO <li><a href="#par_syssta">System Statistics</a></li>
PRO <li><a href="#pq_slaves">PQ Slaves</a></li>
PRO <li><a href="#px_sess">PX Sessions</a></li>
PRO <li><a href="#services">Services</a></li>
PRO <li><a href="#io_cal">I/O Calibration Results</a></li> <!-- 11g -->
PRO <li><a href="#osstat">Operating System Statistics</a></li>
PRO <li><a href="#sysstat">System Statistics</a></li>
PRO <li><a href="#sysstath">System Statistics History</a></li>
PRO <li><a href="#sgastat">System Global Area (SGA) Statistics</a></li>
PRO <li><a href="#sgastath">System Global Area (SGA) Statistics History</a></li>
PRO <li><a href="#sys_params">System Parameters with Non-Default or Modified Values</a></li>
PRO <li><a href="#inst_params">Instance Parameters</a></li>
PRO <li><a href="#sql_monitor">SQL Monitor</a></li> <!-- 11g -->
PRO <li><a href="#share_vc">Version Count as per Cursor Sharing</a></li>
PRO <li><a href="#share_r">Cursor Sharing and Reason</a></li>
PRO </ul>
/* -------------------------
*
* observations
*
* ------------------------- */
PRO <a name="obs"></a><h2>Observations</h2>
PRO
PRO Observations below are the outcome of several heath-checks on your system with regard to Parallel Execution (PX).<br>
PRO Review them carefully and take action when appropriate.<br>
PRO Note: Ignore possible errors about dba_rsrc_io_calibrate and gv$io_calibration_status on 10g:
PRO
PRO <table>
PRO
PRO <tr>
PRO <th>Type</th>
PRO <th>Name</th>
PRO <th>Observation</th>
PRO <th>Details</th>
PRO </tr>
PRO
SELECT '<!-- '||TO_CHAR(SYSDATE, 'YYYY-MM-DD/HH24:MI:SS')||' -->' FROM dual;
PRO <!-- Please Wait -->
-- parallel_instance_group not in services or instance_groups
SELECT CHR(10)||'<tr>'||CHR(10)||
'<td>System Parameter</td>'||CHR(10)||
'<td>parallel_instance_group</td>'||CHR(10)||
'<td>Value "'||pig.value||'" on instance '||pig.inst_id||' does not exist as a service or instance group.</td>'||CHR(10)||
'<td>Unset this parallel_instance_group or verify it matches a valid service or a value in instance_groups parameter.<br>'||CHR(10)||
'See <a target="MOS" href="^^doc_link.7352775.8">7352775.8</a>, <a target="MOS" href="^^doc_link.750645.1">750645.1</a> and <a target="MOS" href="^^bug_link.13940162">13940162</a>.</td>'||CHR(10)||
'</tr>'
FROM gv$system_parameter2 pig
WHERE LOWER(pig.name) = 'parallel_instance_group'
AND TRIM(REPLACE(REPLACE(pig.value, ''''), '"')) IS NOT NULL
AND NOT EXISTS (
SELECT NULL
FROM gv$services gsv
WHERE gsv.name = pig.value )
AND NOT EXISTS (
SELECT NULL
FROM gv$system_parameter2 igr
WHERE LOWER(igr.name) = 'instance_groups'
AND igr.value = pig.value );
SELECT '<!-- '||TO_CHAR(SYSDATE, 'YYYY-MM-DD/HH24:MI:SS')||' -->' FROM dual;
PRO <!-- Please Wait -->
-- parallel_instance_group is set or modified to a null value
SELECT CHR(10)||'<tr>'||CHR(10)||
'<td>System Parameter</td>'||CHR(10)||
'<td>parallel_instance_group</td>'||CHR(10)||
'<td>A NULL value of this parameter on instance '||pig.inst_id||' is not valid.</td>'||CHR(10)||
'<td>Unset this parallel_instance_group parameter altogether.</td>'||CHR(10)||
'</tr>'
FROM gv$system_parameter2 pig
WHERE LOWER(pig.name) = 'parallel_instance_group'
AND TRIM(REPLACE(REPLACE(pig.value, ''''), '"')) IS NULL
AND (pig.isdefault = 'FALSE' OR pig.ismodified <> 'FALSE');
SELECT '<!-- '||TO_CHAR(SYSDATE, 'YYYY-MM-DD/HH24:MI:SS')||' -->' FROM dual;
PRO <!-- Please Wait -->
-- parallel_max_servers > cpu_count * 4 * parallel_threads_per_cpu
WITH
pms AS (SELECT /*+ MATERIALIZE */ inst_id, TO_NUMBER(value) value FROM gv$system_parameter2 WHERE LOWER(name) = 'parallel_max_servers'),
cpc AS (SELECT /*+ MATERIALIZE */ inst_id, TO_NUMBER(value) value FROM gv$system_parameter2 WHERE LOWER(name) = 'cpu_count'),
tpc AS (SELECT /*+ MATERIALIZE */ inst_id, TO_NUMBER(value) value FROM gv$system_parameter2 WHERE LOWER(name) = 'parallel_threads_per_cpu'),
wsp AS (SELECT /*+ MATERIALIZE */ inst_id, CASE WHEN value = 'AUTO' THEN 2 ELSE 1 END value FROM gv$system_parameter2 WHERE LOWER(name) = 'workarea_size_policy'),
stg AS (SELECT /*+ MATERIALIZE */ inst_id, CASE WHEN value > 0 THEN 4 ELSE NULL END value FROM gv$system_parameter2 WHERE LOWER(name) = 'sga_target'),
mtg AS (SELECT /*+ MATERIALIZE */ inst_id, CASE WHEN value > 0 THEN 4 ELSE NULL END value FROM gv$system_parameter2 WHERE LOWER(name) = 'memory_target'),
vrs AS (SELECT /*+ MATERIALIZE */ substr(version,1,4) version from gv$instance)
SELECT CHR(10)||'<tr>'||CHR(10)||
'<td>System Parameter</td>'||CHR(10)||
'<td>parallel_max_servers</td>'||CHR(10)||
'<td>A value of '||pms.value||' for this parameter on instance '||pms.inst_id||' seems too high.</td>'||CHR(10)||
'<td>Consider reducing this value to at most cpu_count * parallel_threads_per_cpu '||CASE WHEN vrs.version >= '11.2' THEN '* 5 * concurrent_parallel_users' ELSE '* 4' END ||'.<br>'||CHR(10)||
'Current value for cpu_count is '||cpc.value||' and for parallel_threads_per_cpu is '||tpc.value||'.</td>'||CHR(10)||
'</tr>'
FROM pms, cpc, tpc, wsp, stg, mtg, vrs
WHERE pms.inst_id = cpc.inst_id
AND cpc.inst_id = tpc.inst_id
AND tpc.inst_id = pms.inst_id
AND cpc.inst_id = wsp.inst_id
AND cpc.inst_id = stg.inst_id(+)
AND cpc.inst_id = mtg.inst_id(+)
AND pms.value > CASE WHEN vrs.version >= '11.2' THEN cpc.value * 5 * tpc.value * NVL(mtg.value,NVL(stg.value,wsp.value)) ELSE cpc.value * 4 * tpc.value END;
SELECT '<!-- '||TO_CHAR(SYSDATE, 'YYYY-MM-DD/HH24:MI:SS')||' -->' FROM dual;
PRO <!-- Please Wait -->
-- parallel_servers_target > 0.75 * parallel_max_servers
WITH
pst AS (SELECT /*+ MATERIALIZE */ inst_id, TO_NUMBER(value) value FROM gv$system_parameter2 WHERE LOWER(name) = 'parallel_servers_target'),
pms AS (SELECT /*+ MATERIALIZE */ inst_id, TO_NUMBER(value) value FROM gv$system_parameter2 WHERE LOWER(name) = 'parallel_max_servers')
SELECT CHR(10)||'<tr>'||CHR(10)||
'<td>System Parameter</td>'||CHR(10)||
'<td>parallel_servers_target</td>'||CHR(10)||
'<td>A value of '||pst.value||' for this parameter on instance '||pst.inst_id||' seems too high.</td>'||CHR(10)||
'<td>Consider reducing this value to at most 0.75 * parallel_max_servers.<br>'||CHR(10)||
'Current value for parallel_max_servers is '||pms.value||'.</td>'||CHR(10)||
'</tr>'
FROM pst, pms
WHERE pst.inst_id = pms.inst_id
AND pst.value > 0.75 * pms.value;
SELECT '<!-- '||TO_CHAR(SYSDATE, 'YYYY-MM-DD/HH24:MI:SS')||' -->' FROM dual;
PRO <!-- Please Wait -->
-- parallel_adaptive_multi_user is set
SELECT CHR(10)||'<tr>'||CHR(10)||
'<td>System Parameter</td>'||CHR(10)||
'<td>parallel_adaptive_multi_user</td>'||CHR(10)||
'<td>Parallel adaptive muti-user is enabled on instance '||amu.inst_id||'.</td>'||CHR(10)||
'<td>Be aware that degree of parallelism (DOP) may be reduced at time of execution.</td>'||CHR(10)||
'</tr>'
FROM gv$system_parameter2 amu
WHERE LOWER(amu.name) = 'parallel_adaptive_multi_user'
AND amu.value = 'TRUE';
SELECT '<!-- '||TO_CHAR(SYSDATE, 'YYYY-MM-DD/HH24:MI:SS')||' -->' FROM dual;
PRO <!-- Please Wait -->
-- parallel_automatic_tuning is set
SELECT CHR(10)||'<tr>'||CHR(10)||
'<td>System Parameter</td>'||CHR(10)||
'<td>parallel_automatic_tuning</td>'||CHR(10)||
'<td>Parallel automatic tuning is enabled on instance '||aut.inst_id||'.</td>'||CHR(10)||
'<td>This parameter is deprecated as of 10g. Avoid using it.</td>'||CHR(10)||
'</tr>'
FROM gv$system_parameter2 aut
WHERE LOWER(aut.name) = 'parallel_automatic_tuning'
AND aut.value = 'TRUE';
SELECT '<!-- '||TO_CHAR(SYSDATE, 'YYYY-MM-DD/HH24:MI:SS')||' -->' FROM dual;
PRO <!-- Please Wait -->
-- parallel_execution_message_size has different values
WITH
ems AS (SELECT /*+ MATERIALIZE */ MIN(TO_NUMBER(value)) min_value, MAX(TO_NUMBER(value)) max_value FROM gv$system_parameter2 WHERE LOWER(name) = 'parallel_execution_message_size')
SELECT CHR(10)||'<tr>'||CHR(10)||
'<td>System Parameter</td>'||CHR(10)||
'<td>parallel_execution_message_size</td>'||CHR(10)||
'<td>Parallel execution message size ranges between '||ems.min_value||' and '||ems.max_value||'.</td>'||CHR(10)||
'<td>All RAC nodes must have same value. Fix this error immediately.<br>'||CHR(10)||
'See <a target="MOS" href="^^doc_link.752967.1">752967.1</a>, <a target="MOS" href="^^doc_link.1374088.1">1374088.1</a> and <a target="MOS" href="^^bug_link.7486699">7486699</a>.</td>'||CHR(10)||
'</tr>'
FROM ems
WHERE ems.min_value <> ems.max_value;
SELECT '<!-- '||TO_CHAR(SYSDATE, 'YYYY-MM-DD/HH24:MI:SS')||' -->' FROM dual;
PRO <!-- Please Wait -->
-- parallel_execution_message_size < 8K
WITH
ems AS (SELECT /*+ MATERIALIZE */ inst_id, TO_NUMBER(value) value FROM gv$system_parameter2 WHERE LOWER(name) = 'parallel_execution_message_size')
SELECT CHR(10)||'<tr>'||CHR(10)||
'<td>System Parameter</td>'||CHR(10)||
'<td>parallel_execution_message_size</td>'||CHR(10)||
'<td>Parallel execution message size of '||ems.value||' in instance '||ems.inst_id||' is too small.</td>'||CHR(10)||
'<td>A size smaller than 8K may produce message fragmentation "PX Deq: Msg Fragment". Consider increasing the message size.<br>'||CHR(10)||
'See <a target="MOS" href="^^doc_link.254760.1">254760.1</a> and <a target="MOS" href="^^doc_link.9792010.8">9792010.8</a>.</td>'||CHR(10)||
'</tr>'
FROM ems
WHERE ems.value < 8192;
SELECT '<!-- '||TO_CHAR(SYSDATE, 'YYYY-MM-DD/HH24:MI:SS')||' -->' FROM dual;
PRO <!-- Please Wait -->
-- 11g dba_rsrc_io_calibrate
SELECT CHR(10)||'<tr>'||CHR(10)||
'<td>IO Calibration Results</td>'||CHR(10)||
'<td>dba_rsrc_io_calibrate</td>'||CHR(10)||
'<td>There seems to be no I/O Calibration results.</td>'||CHR(10)||
'<td>Consider using DBMS_RESOURCE_MANAGER.CALIBRATE_IO.</td>'||CHR(10)||
'</tr>'
FROM dba_rsrc_io_calibrate
WHERE '^^rdbms_version.' LIKE '11%'
HAVING COUNT(*) = 0;
SELECT '<!-- '||TO_CHAR(SYSDATE, 'YYYY-MM-DD/HH24:MI:SS')||' -->' FROM dual;
PRO <!-- Please Wait -->
-- 11g gv$io_calibration_status
SELECT CHR(10)||'<tr>'||CHR(10)||
'<td>IO Calibration Status</td>'||CHR(10)||
'<td>gv$io_calibration_status</td>'||CHR(10)||
'<td>'||status||' for instance '||inst_id||'.</td>'||CHR(10)||
'<td>Time of last calibration: '||calibration_time||'.</td>'||CHR(10)||
'</tr>'
FROM gv$io_calibration_status
ORDER BY
inst_id;
SELECT '<!-- '||TO_CHAR(SYSDATE, 'YYYY-MM-DD/HH24:MI:SS')||' -->' FROM dual;
PRO <!-- Please Wait -->
-- parallel_degree_policy is set to AUTO
SELECT CHR(10)||'<tr>'||CHR(10)||
'<td>System Parameter</td>'||CHR(10)||
'<td>parallel_degree_policy</td>'||CHR(10)||
'<td>Parallel degree policy is set to AUTO in '||COUNT(*)||' instance(s).</td>'||CHR(10)||
'<td>AUTO enables automatic degree of parallelism, statement queuing, and in-memory parallel execution.</td>'||CHR(10)||
'</tr>'
FROM gv$system_parameter2 pdp
WHERE LOWER(pdp.name) = 'parallel_degree_policy'
AND pdp.value = 'AUTO'
HAVING COUNT(*) > 0;
SELECT '<!-- '||TO_CHAR(SYSDATE, 'YYYY-MM-DD/HH24:MI:SS')||' -->' FROM dual;
PRO <!-- Please Wait -->
-- tables < 1G with dop <> 1
WITH
tables AS (
SELECT /*+ MATERIALIZE */
t.owner,
t.table_name,
TRIM(t.degree)table_degree,
t.blocks,
t.partitioned tab_part,
t.temporary
FROM dba_tables t
WHERE t.degree IS NOT NULL
AND TRIM(t.degree) <> '1'
AND t.blocks * ^^sys_db_block_size. < ^^small_table_threshold.
)
SELECT CHR(10)||'<tr>'||CHR(10)||
'<td>Degree of Parallelism</td>'||CHR(10)||
'<td>Non-default DOP</td>'||CHR(10)||
'<td>Schema '||owner||' contains '||COUNT(*)||' small table(s) with DOP set.</td>'||CHR(10)||
'<td>Setting DOP in small tables (smaller than '||ROUND(^^small_table_threshold./1e9, 3)||' GB) may promote PX plans for which a serial plan may be more convenient. Review DOP report.</td>'||CHR(10)||
'</tr>'
FROM tables
GROUP BY
owner
ORDER BY
owner;
SELECT '<!-- '||TO_CHAR(SYSDATE, 'YYYY-MM-DD/HH24:MI:SS')||' -->' FROM dual;
PRO <!-- Please Wait -->
-- tables dop <> indexes dop
WITH
tables_n_indexes AS (
SELECT /*+ MATERIALIZE */
t.owner,
t.table_name,
TRIM(t.degree) table_degree,
TRIM(t.instances) table_instances,
t.blocks,
t.partitioned tab_part,
t.temporary,
i.index_name,
i.index_type,
TRIM(i.degree) index_degree,
TRIM(i.instances) index_instances,
i.leaf_blocks,
i.partitioned idx_part
FROM dba_tables t,
dba_indexes i
WHERE t.degree IS NOT NULL
AND i.table_owner = t.owner
AND i.table_name = t.table_name
AND i.table_type = 'TABLE'
AND i.index_type <> 'LOB'
AND i.degree IS NOT NULL
AND TRIM(i.degree) <> TRIM(t.degree)
),
tables AS (
SELECT /*+ MATERIALIZE */
owner,
table_name
FROM tables_n_indexes
GROUP BY
owner,
table_name
)
SELECT CHR(10)||'<tr>'||CHR(10)||
'<td>Degree of Parallelism</td>'||CHR(10)||
'<td>DOP mismatch</td>'||CHR(10)||
'<td>Schema '||owner||' contains '||COUNT(*)||' table(s) with DOP different than one or more index(es).</td>'||CHR(10)||
'<td>This DOP mismatch between tables and indexes is more probably by accident. It may produce some unexpected PX plans. Review DOP report.</td>'||CHR(10)||
'</tr>'
FROM tables
GROUP BY
owner
ORDER BY
owner;
PRO
PRO <tr>
PRO <th>Type</th>
PRO <th>Name</th>
PRO <th>Observation</th>
PRO <th>Details</th>
PRO </tr>
PRO
PRO </table>
PRO
/* -------------------------
*
* parallel degree limit method
*
* ------------------------- */
PRO <a name="pdlm"></a><h2>Parallel Degree Limit Method</h2>
PRO
PRO Collected from GV$PARALLEL_DEGREE_LIMIT_MTH.
PRO
PRO <table>
PRO
PRO <tr>
PRO <th>#</th>
PRO <th>Inst</th>
PRO <th>Name</th>
PRO </tr>
PRO
SELECT '<!-- '||TO_CHAR(SYSDATE, 'YYYY-MM-DD/HH24:MI:SS')||' -->' FROM dual;
PRO <!-- Please Wait -->
SELECT CHR(10)||'<tr>'||CHR(10)||
'<td class="r">'||ROWNUM||'</td>'||CHR(10)||
'<td class="c">'||v.inst_id||'</td>'||CHR(10)||
'<td>'||v.name||'</td>'||CHR(10)||
'</tr>'
FROM (
SELECT /*+ NO_MERGE */
inst_id,
name
FROM gv$parallel_degree_limit_mth
ORDER BY
inst_id,
name ) v;
PRO
PRO <tr>
PRO <th>#</th>
PRO <th>Inst</th>
PRO <th>Name</th>
PRO </tr>
PRO
PRO </table>
PRO
/* -------------------------
*
* px buffer advice
*
* ------------------------- */
PRO <a name="buf_adv"></a><h2>PX Buffer Advice</h2>
PRO
PRO Collected from GV$PX_BUFFER_ADVICE.
PRO
PRO <table>
PRO
PRO <tr>
PRO <th>#</th>
PRO <th>Inst</th>
PRO <th>Statistic</th>
PRO <th>Value</th>
PRO </tr>
PRO
SELECT '<!-- '||TO_CHAR(SYSDATE, 'YYYY-MM-DD/HH24:MI:SS')||' -->' FROM dual;
PRO <!-- Please Wait -->
SELECT CHR(10)||'<tr>'||CHR(10)||
'<td class="r">'||ROWNUM||'</td>'||CHR(10)||
'<td class="c">'||v.inst_id||'</td>'||CHR(10)||
'<td>'||v.statistic||'</td>'||CHR(10)||
'<td class="r">'||value||'</td>'||CHR(10)||
'</tr>'
FROM (
SELECT /*+ NO_MERGE */
inst_id,
statistic,
value
FROM gv$px_buffer_advice
ORDER BY
inst_id,
statistic ) v;
PRO
PRO <tr>
PRO <th>#</th>
PRO <th>Inst</th>
PRO <th>Statistic</th>
PRO <th>Value</th>
PRO </tr>
PRO
PRO </table>
PRO
/* -------------------------
*
* pq system statistics
*
* ------------------------- */
PRO <a name="pq_syssta"></a><h2>PQ System Statistics</h2>
PRO
PRO Collected from GV$PQ_SYSSTAT.
PRO
PRO <table>
PRO
PRO <tr>
PRO <th>#</th>
PRO <th>Inst</th>
PRO <th>Statistic</th>
PRO <th>Value</th>
PRO </tr>
PRO
SELECT '<!-- '||TO_CHAR(SYSDATE, 'YYYY-MM-DD/HH24:MI:SS')||' -->' FROM dual;
PRO <!-- Please Wait -->
SELECT CHR(10)||'<tr>'||CHR(10)||
'<td class="r">'||ROWNUM||'</td>'||CHR(10)||
'<td class="c">'||v.inst_id||'</td>'||CHR(10)||
'<td>'||v.statistic||'</td>'||CHR(10)||
'<td class="r">'||v.value||'</td>'||CHR(10)||
'</tr>'
FROM (
SELECT /*+ NO_MERGE */
inst_id,
statistic,
value
FROM gv$pq_sysstat
ORDER BY
inst_id,
statistic ) v;
PRO
PRO <tr>
PRO <th>#</th>
PRO <th>Inst</th>
PRO <th>Statistic</th>
PRO <th>Value</th>
PRO </tr>
PRO
PRO </table>
PRO
/* -------------------------
*
* px system statistics
*
* ------------------------- */
PRO <a name="px_syssta"></a><h2>PX System Statistics</h2>
PRO
PRO Collected from GV$PX_PROCESS_SYSSTAT.
PRO
PRO <table>
PRO
PRO <tr>
PRO <th>#</th>
PRO <th>Inst</th>
PRO <th>Statistic</th>
PRO <th>Value</th>
PRO </tr>
PRO
SELECT '<!-- '||TO_CHAR(SYSDATE, 'YYYY-MM-DD/HH24:MI:SS')||' -->' FROM dual;
PRO <!-- Please Wait -->
SELECT CHR(10)||'<tr>'||CHR(10)||
'<td class="r">'||ROWNUM||'</td>'||CHR(10)||
'<td class="c">'||v.inst_id||'</td>'||CHR(10)||
'<td>'||v.statistic||'</td>'||CHR(10)||
'<td class="r">'||v.value||'</td>'||CHR(10)||
'</tr>'
FROM (
SELECT /*+ NO_MERGE */
inst_id,
statistic,
value
FROM gv$px_process_sysstat
ORDER BY
inst_id,
statistic ) v;
PRO
PRO <tr>
PRO <th>#</th>
PRO <th>Inst</th>
PRO <th>Statistic</th>
PRO <th>Value</th>
PRO </tr>
PRO
PRO </table>
PRO
/* -------------------------
*
* system statistics
*
* ------------------------- */
PRO <a name="par_syssta"></a><h2>System Statistics</h2>
PRO
PRO Collected from GV$SYSSTAT.
PRO
PRO <table>
PRO
PRO <tr>
PRO <th>#</th>
PRO <th>Inst</th>
PRO <th>Statistic</th>
PRO <th>Value</th>
PRO </tr>
PRO
SELECT '<!-- '||TO_CHAR(SYSDATE, 'YYYY-MM-DD/HH24:MI:SS')||' -->' FROM dual;
PRO <!-- Please Wait -->
SELECT CHR(10)||'<tr>'||CHR(10)||
'<td class="r">'||ROWNUM||'</td>'||CHR(10)||
'<td class="c">'||v.inst_id||'</td>'||CHR(10)||
'<td>'||v.name||'</td>'||CHR(10)||
'<td class="r">'||v.value||'</td>'||CHR(10)||
'</tr>'
FROM (
SELECT /*+ NO_MERGE */
inst_id,
name,
value
FROM gv$sysstat
ORDER BY
inst_id,
name ) v;
PRO
PRO <tr>
PRO <th>#</th>
PRO <th>Inst</th>
PRO <th>Statistic</th>
PRO <th>Value</th>
PRO </tr>
PRO
PRO </table>
PRO
/* -------------------------
*
* pq slaves
*
* ------------------------- */
PRO <a name="pq_slaves"></a><h2>PQ Slaves</h2>
PRO
PRO Collected from GV$PQ_SLAVE.
PRO
PRO <table>
PRO
PRO <tr>
PRO <th>#</th>
PRO <th>Inst</th>
PRO <th>Slave<br>Name</th>
PRO <th>Status</th>
PRO <th>Sessions</th>
PRO <th>Idle<br>Time<br>Total</th>
PRO <th>Busy<br>Time<br>Total</th>
PRO <th>CPU<br>Secs<br>Total</th>
PRO <th>Msgs<br>Sent<br>Total</th>
PRO <th>Msgs<br>Rcvd<br>Total</th>
PRO </tr>
PRO
SELECT '<!-- '||TO_CHAR(SYSDATE, 'YYYY-MM-DD/HH24:MI:SS')||' -->' FROM dual;
PRO <!-- Please Wait -->
SELECT CHR(10)||'<tr>'||CHR(10)||
'<td class="r">'||ROWNUM||'</td>'||CHR(10)||
'<td class="c">'||v.inst_id||'</td>'||CHR(10)||
'<td>'||v.slave_name||'</td>'||CHR(10)||
'<td>'||v.status||'</td>'||CHR(10)||
'<td class="r">'||v.sessions||'</td>'||CHR(10)||
'<td class="r">'||v.idle_time_total||'</td>'||CHR(10)||
'<td class="r">'||v.busy_time_total||'</td>'||CHR(10)||
'<td class="r">'||v.cpu_secs_total||'</td>'||CHR(10)||
'<td class="r">'||v.msgs_sent_total||'</td>'||CHR(10)||
'<td class="r">'||v.msgs_rcvd_total||'</td>'||CHR(10)||
'</tr>'
FROM (
SELECT /*+ NO_MERGE */
inst_id,
slave_name,
status,
sessions,
idle_time_total,
busy_time_total,
cpu_secs_total,
msgs_sent_total,
msgs_rcvd_total
FROM gv$pq_slave
ORDER BY
inst_id,
slave_name ) v;
PRO
PRO <tr>
PRO <th>#</th>
PRO <th>Inst</th>
PRO <th>Slave<br>Name</th>
PRO <th>Status</th>
PRO <th>Sessions</th>
PRO <th>Idle<br>Time<br>Total</th>
PRO <th>Busy<br>Time<br>Total</th>
PRO <th>CPU<br>Secs<br>Total</th>
PRO <th>Msgs<br>Sent<br>Total</th>
PRO <th>Msgs<br>Rcvd<br>Total</th>
PRO </tr>
PRO
PRO </table>
PRO
/* -------------------------
*
* px sessions
*
* ------------------------- */
PRO <a name="px_sess"></a><h2>PX Sessions</h2>
PRO
PRO Collected from GV$PX_SESSION.
PRO
PRO <table>
PRO
PRO <tr>
PRO <th>#</th>
PRO <th>Inst</th>
PRO <th>QC<br>SID</th>
PRO <th>Server<br>Name</th>
PRO <th>SID</th>
PRO <th>Serial#</th>
PRO <th>PID</th>
PRO <th>SPID</th>
PRO <th>Server<br>Group</th>
PRO <th>Server<br>Set</th>
PRO <th>Server#</th>
PRO <th>Degree</th>
PRO <th>Req<br>Degree</th>
PRO <th>Wait Event</th>
PRO <th>SQL_ID</th>
PRO <th>Child#</th>
PRO <th>Resource<br>Consumer<br>Group</th>
PRO <th>Module</th>
PRO <th>Action</th>
PRO </tr>
PRO
SELECT '<!-- '||TO_CHAR(SYSDATE, 'YYYY-MM-DD/HH24:MI:SS')||' -->' FROM dual;
PRO <!-- Please Wait -->
SELECT /* ^^script..sql PX Sessions */
CHR(10)||'<tr>'||CHR(10)||
'<td class="r">'||ROWNUM||'</td>'||CHR(10)||
v.line||
'</tr>'
FROM (
SELECT /*+ NO_MERGE */
'<td class="c">'||pxs.inst_id||'</td>'||CHR(10)||
'<td class="c">'||pxs.qcsid||'</td>'||CHR(10)||
'<td>'||NVL(pxp.server_name, 'QC')||'</td>'||CHR(10)||
'<td class="c">'||pxs.sid||'</td>'||CHR(10)||
'<td class="c">'||pxs.serial#||'</td>'||CHR(10)||
'<td class="c">'||NVL(pxp.pid, pro.pid)||'</td>'||CHR(10)||
'<td class="c">'||NVL(pxp.spid, pro.spid)||'</td>'||CHR(10)||
'<td class="r">'||pxs.server_group||'</td>'||CHR(10)||
'<td class="r">'||pxs.server_set||'</td>'||CHR(10)||
'<td class="r">'||pxs.server#||'</td>'||CHR(10)||
'<td class="r">'||pxs.degree||'</td>'||CHR(10)||
'<td class="r">'||pxs.req_degree||'</td>'||CHR(10)||
'<td>'||swt.event||'</td>'||CHR(10)||