分享一个比SQLHC还要厉害的脚本

分享一个比SQLHC还要厉害的脚本，直接在oracle用户下执行即可。

-- File Name : SQLHC.sql

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYYMMDD';
SET SERVEROUTPUT ON SIZE UNLIMITED
SET LINESIZE 200
SET PAGESIZE 1000
SET VERIFY OFF
SET FEEDBACK OFF
SET TRIMSPOOL ON
SET ECHO OFF

-- 定义版本控制和配置
DEFINE _SQL_MONITOR = "  "
DEFINE _VERSION_11  = "--"
DEFINE _VERSION_12  = "--" 
DEFINE _VERSION_10  = "--"
DEFINE _CDB_MODE    = "--"

-- 版本检测
COL pdbname    NOPRINT NEW_VALUE _PDBNAME
COL version12  NOPRINT NEW_VALUE _VERSION_12  
COL version11  NOPRINT NEW_VALUE _VERSION_11
COL version10  NOPRINT NEW_VALUE _VERSION_10

SELECT 
    CASE WHEN version >= '12.1' THEN '  ' ELSE '--' END version12,
    CASE WHEN version >= '11.2' THEN '  ' ELSE '--' END version11,
    CASE WHEN version >= '10.2' AND version < '11.2' THEN '  ' ELSE '--' END version10
FROM (
    SELECT TO_NUMBER(SUBSTR(banner, INSTR(banner, 'Release ')+8, 
               INSTR(SUBSTR(banner, INSTR(banner, 'Release ')+8), '.')-1)) version
    FROM v$version 
    WHERE banner LIKE 'Oracle Database%'
    AND ROWNUM = 1
);

-- 动态生成输出文件名
COLUMN output_file NEW_VALUE output_file NOPRINT
SELECT 'sql_optimize_' || 
       LOWER((SELECT instance_name FROM v$instance)) || '_' ||
       '&&sqlid' || '_' ||
       TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '.log' AS output_file
FROM DUAL;

SPOOL &output_file

PROMPT ================================================================================
PROMPT SQL Optimizer Report - &&sqlid
PROMPT Generated: &_DATE
PROMPT Instance: &_INSTANCE_NAME  
PROMPT ================================================================================

UNDEFINE sqlid
ACCEPT sqlid CHAR PROMPT 'Enter SQL_ID: '

-- 格式定义
COL CPU_TIME                HEADING "CPU|TIME"           FOR 999999,999,999
COL ELAPSED_TIME            HEADING "ELAPSED|TIME"       FOR 999999,999,999
COL DISK_READS              HEADING "DISK|READS"         FOR 999999,999,999
COL BUFFER_GETS             HEADING "BUFFER|GETS"        FOR 999999,999,999
COL PLAN_HASH_VALUE         HEADING "PLAN|HASH VALUE"    FOR 999999999999
COL PARSING_SCHEMA_NAME     HEADING "SCHEMA"             FOR A15
COL CHILD_NUMBER            HEADING "CHILD"              FOR 999
COL EXECUTIONS              HEADING "EXECS"              FOR 999,999,999
COL ROWS_PROCESSED          HEADING "ROWS"               FOR 999,999,999

PROMPT
PROMPT ****************************************************************************************
PROMPT 1. LITERAL SQL WITH BIND VARIABLES
PROMPT ****************************************************************************************

DECLARE
    CURSOR c_binds IS
        SELECT DISTINCT child_number
        FROM v$sql_bind_capture 
        WHERE sql_id = '&&sqlid'
        ORDER BY child_number;
        
    CURSOR c_bind_details(p_child NUMBER) IS
        SELECT name, position, datatype_string, value_string
        FROM v$sql_bind_capture
        WHERE sql_id = '&&sqlid' 
        AND child_number = p_child
        ORDER BY position;
        
    lv_sql_text      VARCHAR2(32767);
    lv_final_sql     VARCHAR2(32767);
    lv_parsing_schema VARCHAR2(30);
    ln_bind_count    NUMBER;
BEGIN
    -- 检查绑定变量是否存在
    SELECT COUNT(*) INTO ln_bind_count 
    FROM v$sql_bind_capture 
    WHERE sql_id = '&&sqlid' 
    AND ROWNUM = 1;

    -- 获取原始SQL文本
    BEGIN
        SELECT sql_text, parsing_schema_name 
        INTO lv_sql_text, lv_parsing_schema
        FROM v$sql 
        WHERE sql_id = '&&sqlid' 
        AND ROWNUM = 1;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('ERROR: SQL_ID &&sqlid not found in shared pool');
            RETURN;
    END;

    IF ln_bind_count = 0 THEN
        DBMS_OUTPUT.PUT_LINE('Schema: ' || lv_parsing_schema);
        DBMS_OUTPUT.PUT_LINE(lv_sql_text);
        DBMS_OUTPUT.PUT_LINE('--- No bind variables found ---');
        RETURN;
    END IF;

    -- 处理每个child cursor的绑定变量
    FOR r_child IN c_binds LOOP
        BEGIN
            SELECT sql_text, parsing_schema_name 
            INTO lv_sql_text, lv_parsing_schema
            FROM v$sql 
            WHERE sql_id = '&&sqlid' 
            AND child_number = r_child.child_number
            AND ROWNUM = 1;
            
            lv_final_sql := lv_sql_text;
            
            -- 替换绑定变量
            FOR r_bind IN c_bind_details(r_child.child_number) LOOP
                lv_final_sql := REPLACE(lv_final_sql, r_bind.name,
                    CASE 
                        WHEN r_bind.value_string IS NULL THEN 'NULL'
                        WHEN r_bind.datatype_string = 'NUMBER' THEN r_bind.value_string
                        ELSE '''' || REPLACE(r_bind.value_string, '''', '''''') || ''''
                    END);
            END LOOP;
            
            DBMS_OUTPUT.PUT_LINE('Child: ' || r_child.child_number || ', Schema: ' || lv_parsing_schema);
            DBMS_OUTPUT.PUT_LINE(lv_final_sql);
            DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------');
            
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                DBMS_OUTPUT.PUT_LINE('Child ' || r_child.child_number || ' not found');
        END;
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
END;
/

PROMPT
PROMPT ****************************************************************************************
PROMPT 2. EXECUTION PLANS
PROMPT ****************************************************************************************

PROMPT
PROMPT 2.1 Current Cursor Plan:
PROMPT =======================

SELECT t.*
FROM v$sql s,
     TABLE(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number, 'ADVANCED ALLSTATS LAST')) t
WHERE s.sql_id = '&&sqlid'
AND ROWNUM = 1;

PROMPT
PROMPT 2.2 AWR Plan (if available):
PROMPT ===========================
-- SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('&&sqlid',NULL,NULL,'ADVANCED ALLSTATS LAST'));

PROMPT
PROMPT ****************************************************************************************
PROMPT 3. SQL PERFORMANCE STATISTICS
PROMPT ****************************************************************************************

PROMPT
PROMPT 3.1 From V$SQLAREA (Aggregated):
PROMPT ================================

SELECT 
    plan_hash_value,
    executions,
    ROUND(cpu_time/1000000, 2) || 's' as cpu_time,
    ROUND(elapsed_time/1000000, 2) || 's' as elapsed_time, 
    buffer_gets,
    disk_reads,
    rows_processed,
    sql_profile,
    parsing_schema_name
FROM v$sqlarea 
WHERE sql_id = '&&sqlid';

PROMPT
PROMPT 3.2 From V$SQL (By Child Cursor):
PROMPT ==================================

SELECT 
    child_number,
    plan_hash_value, 
    executions,
    ROUND(cpu_time/1000000/NULLIF(executions,0), 4) || 's' as cpu_per_exec,
    ROUND(elapsed_time/1000000/NULLIF(executions,0), 4) || 's' as elapsed_per_exec,
    ROUND(buffer_gets/NULLIF(executions,0), 2) as gets_per_exec,
    ROUND(disk_reads/NULLIF(executions,0), 2) as reads_per_exec,
    ROUND(rows_processed/NULLIF(executions,0), 2) as rows_per_exec
FROM v$sql 
WHERE sql_id = '&&sqlid'
ORDER BY child_number;

PROMPT
PROMPT 3.3 From AWR History (Last 7 days):
PROMPT ====================================

SELECT 
    TO_CHAR(end_interval_time, 'MM-DD HH24:MI') as snapshot_time,
    plan_hash_value,
    executions_delta as executions,
    ROUND(elapsed_time_delta/1000000/NULLIF(executions_delta,0), 4) || 's' as ela_per_exec,
    ROUND(buffer_gets_delta/NULLIF(executions_delta,0), 2) as gets_per_exec,
    ROUND(disk_reads_delta/NULLIF(executions_delta,0), 2) as reads_per_exec
FROM dba_hist_sqlstat ss, dba_hist_snapshot sn
WHERE ss.sql_id = '&&sqlid'
AND ss.snap_id = sn.snap_id
AND ss.instance_number = sn.instance_number
AND end_interval_time > SYSDATE - 7
ORDER BY end_interval_time DESC;

PROMPT
PROMPT ****************************************************************************************
PROMPT 4. SQL MONITOR REPORT (11g+)
PROMPT ****************************************************************************************

&_VERSION_11 SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(
&_VERSION_11    sql_id => '&&sqlid',
&_VERSION_11    type => 'TEXT',
&_VERSION_11    report_level => 'BASIC') as monitor_report
&_VERSION_11 FROM DUAL;

PROMPT
PROMPT ****************************************************************************************
PROMPT 5. WAIT EVENTS ANALYSIS
PROMPT ****************************************************************************************

SELECT 
    event,
    wait_class,
    COUNT(*) as total_samples,
    ROUND(COUNT(*) * 100 / SUM(COUNT(*)) OVER (), 2) as pct
FROM v$active_session_history
WHERE sql_id = '&&sqlid'
AND sample_time > SYSDATE - 1/24  -- Last 1 hour
GROUP BY event, wait_class
ORDER BY total_samples DESC
FETCH FIRST 10 ROWS ONLY;

PROMPT
PROMPT ****************************************************************************************
PROMPT 6. ACCESSED OBJECTS INFORMATION
PROMPT ****************************************************************************************

WITH sql_objects AS (
    SELECT DISTINCT object_owner, object_name, object_type
    FROM v$sql_plan 
    WHERE sql_id = '&&sqlid' 
    AND object_name IS NOT NULL
    AND object_owner IS NOT NULL
),
object_details AS (
    SELECT 
        o.owner,
        o.object_name,
        o.object_type,
        o.status,
        s.bytes/1024/1024 as size_mb,
        s.blocks,
        CASE o.object_type 
            WHEN 'TABLE' THEN (SELECT num_rows FROM dba_tables WHERE owner = o.owner AND table_name = o.object_name)
            WHEN 'INDEX' THEN (SELECT num_rows FROM dba_indexes WHERE owner = o.owner AND index_name = o.object_name)
            ELSE NULL 
        END as num_rows
    FROM sql_objects so
    JOIN dba_objects o ON so.object_owner = o.owner AND so.object_name = o.object_name
    LEFT JOIN dba_segments s ON o.owner = s.owner AND o.object_name = s.segment_name
)
SELECT 
    owner,
    object_name,
    object_type,
    status,
    size_mb,
    blocks,
    num_rows
FROM object_details
ORDER BY owner, object_type, object_name;

PROMPT
PROMPT 6.1 Table Statistics:
PROMPT =====================

WITH sql_tables AS (
    SELECT DISTINCT object_owner as owner, object_name as table_name
    FROM v$sql_plan 
    WHERE sql_id = '&&sqlid' 
    AND object_type LIKE 'TABLE%'
    AND object_name IS NOT NULL
)
SELECT 
    t.owner,
    t.table_name,
    t.num_rows,
    t.blocks,
    ROUND(t.blocks * ts.block_size/1024/1024, 2) as size_mb,
    t.last_analyzed,
    t.partitioned
FROM sql_tables st
JOIN dba_tables t ON st.owner = t.owner AND st.table_name = t.table_name
JOIN dba_tablespaces ts ON t.tablespace_name = ts.tablespace_name
ORDER BY t.owner, t.table_name;

PROMPT
PROMPT 6.2 Index Statistics:
PROMPT =====================

WITH sql_indexes AS (
    SELECT DISTINCT object_owner as owner, object_name as index_name
    FROM v$sql_plan 
    WHERE sql_id = '&&sqlid' 
    AND object_type LIKE 'INDEX%'
    AND object_name IS NOT NULL
)
SELECT 
    i.owner,
    i.index_name,
    i.table_name,
    i.uniqueness,
    i.blevel,
    i.leaf_blocks,
    i.distinct_keys,
    i.clustering_factor,
    i.status
FROM sql_indexes si
JOIN dba_indexes i ON si.owner = i.owner AND si.index_name = i.index_name
ORDER BY i.owner, i.table_name, i.index_name;

PROMPT
PROMPT ****************************************************************************************
PROMPT 7. BIND VARIABLES INFORMATION
PROMPT ****************************************************************************************

SELECT 
    child_number,
    name,
    position,
    datatype_string,
    value_string,
    last_captured
FROM v$sql_bind_capture
WHERE sql_id = '&&sqlid'
ORDER BY child_number, position;

PROMPT
PROMPT ****************************************************************************************
PROMPT 8. EXECUTION HISTORY FROM ASH
PROMPT ****************************************************************************************

SELECT 
    TO_CHAR(sample_time, 'YYYY-MM-DD HH24:MI') as sample_time,
    session_id,
    session_serial#,
    program,
    event,
    wait_time,
    time_waited
FROM v$active_session_history
WHERE sql_id = '&&sqlid'
AND sample_time > SYSDATE - 1/24  -- Last 1 hour
ORDER BY sample_time DESC
FETCH FIRST 20 ROWS ONLY;

PROMPT
PROMPT ****************************************************************************************
PROMPT 9. MEMORY USAGE
PROMPT ****************************************************************************************

SELECT 
    child_number,
    sharable_mem,
    persistent_mem, 
    runtime_mem,
    sorts,
    loaded_versions,
    open_versions,
    users_opening,
    users_executing
FROM v$sql
WHERE sql_id = '&&sqlid'
ORDER BY child_number;

PROMPT
PROMPT ****************************************************************************************
PROMPT 10. SQL TEXT (Full)
PROMPT ****************************************************************************************

SELECT sql_fulltext
FROM v$sqlarea
WHERE sql_id = '&&sqlid'
AND ROWNUM = 1;

PROMPT
PROMPT ================================================================================
PROMPT End of SQL Optimizer Report
PROMPT ================================================================================

SPOOL OFF

-- 清理格式
CLEAR COLUMNS
CLEAR BREAKS

PROMPT
PROMPT Report generated: &output_file
PROMPT

纯属个人意见，大家不喜勿喷！