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 carlos.sierra@oracle.com

REM mauro.pagano@oracle.com

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 '\'

SELECT TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') unique_id FROM DUAL;

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".');

DEF script = 'pxhcdr';

DEF method = 'PXHCDR';

DEF mos_doc = '1460440.1';

DEF doc_ver = '12.1.09';

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=';

ALTER SESSION SET STATISTICS_LEVEL = 'ALL';

ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

-- get database name (up to 10, stop before first '.', no special characters)

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 ''`~!@#$%^*()-_=+[]{}\|;:",.<>/?'||CHR(0)||CHR(9)||CHR(10)||CHR(13)||CHR(38),

'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789') host_name_short FROM DUAL;

COL rdbms_version NEW_V rdbms_version FOR A17;

SELECT version rdbms_version FROM v$instance;

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;

COL instance_number NEW_V instance_number FOR A10;

SELECT TO_CHAR(instance_number) instance_number FROM v$instance;

COL time_stamp NEW_V time_stamp FOR A15;

SELECT TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') time_stamp FROM DUAL;

COL time_stamp2 NEW_V time_stamp2 FOR A20;

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD/HH24:MI:SS') time_stamp2 FROM DUAL;

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';

COL sys_cpu NEW_V sys_cpu FOR A17;

SELECT value sys_cpu FROM v$system_parameter2 WHERE LOWER(name) = 'cpu_count';

COL sys_ofe NEW_V sys_ofe FOR A17;

SELECT value sys_ofe FROM v$system_parameter2 WHERE LOWER(name) = 'optimizer_features_enable';

COL sys_ds NEW_V sys_ds FOR A10;

SELECT value sys_ds FROM v$system_parameter2 WHERE LOWER(name) = 'optimizer_dynamic_sampling';

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;

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;

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;

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;

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;

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: carlos.sierra@oracle.com -->

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>

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 -->

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 -->

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 -->

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 -->

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 -->

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 -->

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 -->

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 -->

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 -->

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 -->

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 -->

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 -->

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 -->

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

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

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

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

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

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

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

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>