Oracle 通过sql profile为sql语句加hint(2)


-----coe_xfr_sql_profile.sql
    SPO coe_xfr_sql_profile.log;
    SET DEF ON TERM OFF ECHO ON FEED OFF VER OFF HEA ON LIN 2000 PAGES 100 LONG 8000000 LONGC 800000 TRIMS ON TI OFF TIMI OFF SERVEROUT ON SIZE 1000000 NUMF "" SQLP SQL>;
    SET SERVEROUT ON SIZE UNL;
    REM
    REM $Header: 215187.1 coe_xfr_sql_profile.sql 11.4.3.5 2011/08/10 carlos.sierra $
    REM
    REM Copyright (c) 2000-2011, Oracle Corporation. All rights reserved.
    REM
    REM AUTHOR
    REM carlos.sierra@oracle.com
    REM
    REM SCRIPT
    REM coe_xfr_sql_profile.sql
    REM
    REM DESCRIPTION
    REM This script generates another that contains the commands to
    REM create a manual custom SQL Profile out of a known plan from
    REM memory or AWR. The manual custom profile can be implemented
    REM into the same SOURCE system where the plan was retrieved,
    REM or into another similar TARGET system that has same schema
    REM objects referenced by the SQL that generated the known plan.
    REM
    REM PRE-REQUISITES
    REM 1. Oracle Tuning Pack license.
    REM
    REM PARAMETERS
    REM 1. SQL_ID (required)
    REM 2. Plan Hash Value for which a manual custom SQL Profile is
    REM needed (required). A list of known plans is presented.
    REM
    REM EXECUTION
    REM 1. Connect into SQL*Plus as SYSDBA or user with access to
    REM data dictionary.
    REM 2. Execute script coe_xfr_sql_profile.sql passing SQL_ID and
    REM plan hash value (parameters can be passed inline or until
    REM requested).
    REM
    REM EXAMPLE
    REM # sqlplus system
    REM SQL> START coe_xfr_sql_profile.sql [SQL_ID] [PLAN_HASH_VALUE];
    REM SQL> START coe_xfr_sql_profile.sql gnjy0mn4y9pbm 2055843663;
    REM SQL> START coe_xfr_sql_profile.sql gnjy0mn4y9pbm;
    REM SQL> START coe_xfr_sql_profile.sql;
    REM
    REM NOTES
    REM 1. For possible errors see coe_xfr_sql_profile.log
    REM 2. If SQLT is installed in SOURCE, you can use instead:
    REM sqlt/utl/sqltprofile.sql
    REM 3. Be aware that using DBMS_SQLTUNE requires a license for
    REM Oracle Tuning Pack.
    REM
    SET TERM ON ECHO OFF;
    PRO
    PRO Parameter 1:
    PRO SQL_ID (required)
    PRO
    DEF sql_id = '&1';
    PRO
    WITH
    p AS (
    SELECT plan_hash_value
      FROM gv$sql_plan
    WHERE sql_id = TRIM('&&sql_id.')
      AND other_xml IS NOT NULL
    UNION
    SELECT plan_hash_value
      FROM dba_hist_sql_plan
    WHERE sql_id = TRIM('&&sql_id.')
      AND other_xml IS NOT NULL ),
    m AS (
    SELECT plan_hash_value,
          SUM(elapsed_time)/SUM(executions) avg_et_secs
      FROM gv$sql
    WHERE sql_id = TRIM('&&sql_id.')
      AND executions > 0
    GROUP BY
          plan_hash_value ),
    a AS (
    SELECT plan_hash_value,
          SUM(elapsed_time_total)/SUM(executions_total) avg_et_secs
      FROM dba_hist_sqlstat
    WHERE sql_id = TRIM('&&sql_id.')
      AND executions_total > 0
    GROUP BY
          plan_hash_value )
    SELECT p.plan_hash_value,
          ROUND(NVL(m.avg_et_secs, a.avg_et_secs)/1e6, 3) avg_et_secs
      FROM p, m, a
    WHERE p.plan_hash_value = m.plan_hash_value(+)
      AND p.plan_hash_value = a.plan_hash_value(+)
    ORDER BY
          avg_et_secs NULLS LAST;
    PRO
    PRO Parameter 2:
    PRO PLAN_HASH_VALUE (required)
    PRO
    DEF plan_hash_value = '&2';
    PRO
    PRO Values passed to coe_xfr_sql_profile:
    PRO ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    PRO SQL_ID : "&&sql_id."
    PRO PLAN_HASH_VALUE: "&&plan_hash_value."
    PRO
    SET TERM OFF ECHO ON;
    WHENEVER SQLERROR EXIT SQL.SQLCODE;

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/d692a27dc785683b30b328174c7a4800.html