sqlhc.sql - 墨天轮文档

sqlhc.sql

肖雪松

1242

142页

19次

2020-04-26

免费下载

SPO sqlhc.log

SET DEF ^ TERM OFF ECHO ON AUTOP OFF VER OFF SERVEROUT ON SIZE 1000000;

REM

REM $Header: 1366133.1 sqlhc.sql 12.1.06 2014/01/30 carlos.sierra mauro.pagano $

REM

REM AUTHOR

REM carlos.sierra@oracle.com

REM mauro.pagano@oracle.com

REM

REM SCRIPT

REM sqlhc.sql SQL Health-Check (extract mode)

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

DEF sql_monitor_reports = '12';

REM

DEF script = 'sqlhc';

DEF method = 'SQLHC';

DEF mos_doc = '1366133.1';

DEF doc_ver = '12.1.06';

DEF doc_date = '2014/01/30';

-- sqldx_output: HTML/CSV/BOTH/NONE

DEF sqldx_output = 'CSV';

********************************************************************************

******************/

EXEC DBMS_APPLICATION_INFO.SET_MODULE(module_name => '^^method. ^^doc_ver.',

action_name => '^^script..sql');

EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO(client_info => '^^method.');

VAR health_checks CHAR(1);

EXEC :health_checks := '^^health_checks.';

VAR shared_cursor CHAR(1);

EXEC :shared_cursor := '^^shared_cursor.';

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 License: "^^input_license."

PRO SQL_ID : "^^input_sql_id."

PRO

--SET TERM OFF;

-- get dbid

COL dbid NEW_V dbid;

of 142

免费下载

关注

评论