使用SQL Profile稳定SQL语句的执行计划

最新推荐文章于 2022-04-20 20:43:43 发布

Alley_Li

最新推荐文章于 2022-04-20 20:43:43 发布

阅读量721

点赞数 1

分类专栏： Oracle Tuning 文章标签： SQL Profile

本文链接：https://blog.csdn.net/goodlisf/article/details/51605500

版权

Oracle Tuning 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

SQL> @C:\Users\kzvz914\Desktop\AFG\coe_xfr_sql_profile.SQL

Parameter 1:
SQL_ID (required)

Enter value for 1: bbp1a0d9xkagg

PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
3403809735 21.621
406886572 816.097

Parameter 2:
PLAN_HASH_VALUE (required)

Enter value for 2: 3403809735

生成COE_XFR_SQL_PROFILE_bbp1a0d9xkagg_3403809735.sql

SQL>@coe_xfr_sql_profile_bbp1a0d9xkagg_3403809735.sql

SQL>SET SERVEROUT ON SIZE UNL;
SQL>REM
SQL>REM $Header: 215187.1 coe_xfr_sql_profile.sql 11.4.3.5 2011/08/10 carlos.sierra $
SQL>REM
SQL>REM Copyright (c) 2000-2011, Oracle Corporation. All rights reserved.
SQL>REM
SQL>REM AUTHOR
SQL>REM   carlos.sierra@oracle.com
SQL>REM
SQL>REM SCRIPT
SQL>REM   coe_xfr_sql_profile.sql
SQL>REM
SQL>REM DESCRIPTION
SQL>REM   This script generates another that contains the commands to
SQL>REM   create a manual custom SQL Profile out of a known plan from
SQL>REM   memory or AWR. The manual custom profile can be implemented
SQL>REM   into the same SOURCE system where the plan was retrieved,
SQL>REM   or into another similar TARGET system that has same schema
SQL>REM   objects referenced by the SQL that generated the known plan.
SQL>REM
SQL>REM PRE-REQUISITES
SQL>REM   1. Oracle Tuning Pack license.
SQL>REM
SQL>REM PARAMETERS
SQL>REM   1. SQL_ID (required)
SQL>REM   2. Plan Hash Value for which a manual custom SQL Profile is
SQL>REM      needed (required). A list of known plans is presented.
SQL>REM
SQL>REM EXECUTION
SQL>REM   1. Connect into SQL*Plus as SYSDBA or user with access to
SQL>REM      data dictionary.
SQL>REM   2. Execute script coe_xfr_sql_profile.sql passing SQL_ID and
SQL>REM      plan hash value (parameters can be passed inline or until
SQL>REM      requested).
SQL>REM
SQL>REM EXAMPLE
SQL>REM   # sqlplus system
SQL>REM   SQL> START coe_xfr_sql_profile.sql [SQL_ID] [PLAN_HASH_VALUE];
SQL>REM   SQL> START coe_xfr_sql_profile.sql gnjy0mn4y9pbm 2055843663;
SQL>REM   SQL> START coe_xfr_sql_profile.sql gnjy0mn4y9pbm;
SQL>REM   SQL> START coe_xfr_sql_profile.sql;
SQL>REM
SQL>REM NOTES
SQL>REM   1. For possible errors see coe_xfr_sql_profile.log
SQL>REM   2. If SQLT is installed in SOURCE, you can use instead:
SQL>REM      sqlt/utl/sqltprofile.sql
SQL>REM   3. Be aware that using DBMS_SQLTUNE requires a license for
SQL>REM      Oracle Tuning Pack.
SQL>REM
SQL>SET TERM ON ECHO OFF;

Parameter 1:
SQL_ID (required)

Enter value for 1: bbp1a0d9xkagg

PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
3403809735 17.007
406886572 708.367

Parameter 2:
PLAN_HASH_VALUE (required)

Enter value for 2: 3403809735

Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID : "bbp1a0d9xkagg"
PLAN_HASH_VALUE: " 3403809735"

SQL>WHENEVER SQLERROR EXIT SQL.SQLCODE;
SQL>
SQL>-- trim parameters
SQL>COL sql_id NEW_V sql_id FOR A30;
SQL>COL plan_hash_value NEW_V plan_hash_value FOR A30;
SQL>SELECT TRIM('&&sql_id.') sql_id, TRIM('&&plan_hash_value.') plan_hash_value FROM DUAL;

SQL_ID                         PLAN_HASH_VALUE
------------------------------ ------------------------------
bbp1a0d9xkagg                  3403809735
SQL>
SQL>VAR sql_text CLOB;
SQL>VAR other_xml CLOB;
SQL>EXEC :sql_text := NULL;
SQL>EXEC :other_xml := NULL;
SQL>
SQL>-- get sql_text from memory
SQL>DECLARE
2    l_sql_text VARCHAR2(32767);
3 BEGIN -- 10g see bug 5017909
4    FOR i IN (SELECT DISTINCT piece, sql_text
5      FROM gv$sqltext_with_newlines
6     WHERE sql_id = TRIM('&&sql_id.')
7     ORDER BY 1, 2)
8    LOOP
9 IF :sql_text IS NULL THEN
10     DBMS_LOB.CREATETEMPORARY(:sql_text, TRUE);
11     DBMS_LOB.OPEN(:sql_text, DBMS_LOB.LOB_READWRITE);
12 END IF;
13 l_sql_text := REPLACE(i.sql_text, CHR(00), ' ');
14 DBMS_LOB.WRITEAPPEND(:sql_text, LENGTH(l_sql_text), l_sql_text);
15    END LOOP;
16    IF :sql_text IS NOT NULL THEN
17 DBMS_LOB.CLOSE(:sql_text);
18    END IF;
19 EXCEPTION
20    WHEN OTHERS THEN
21 DBMS_OUTPUT.PUT_LINE('getting sql_text from memory: '||SQLERRM);
22 :sql_text := NULL;
23 END;
24 /
SQL>
SQL>-- get sql_text from awr
SQL>BEGIN
2    IF :sql_text IS NULL OR NVL(DBMS_LOB.GETLENGTH(:sql_text), 0) = 0 THEN
3 SELECT REPLACE(sql_text, CHR(00), ' ')
4     INTO :sql_text
5     FROM dba_hist_sqltext
6    WHERE sql_id = TRIM('&&sql_id.')
7      AND sql_text IS NOT NULL
8      AND ROWNUM = 1;
9    END IF;
10 EXCEPTION
11    WHEN OTHERS THEN
12 DBMS_OUTPUT.PUT_LINE('getting sql_text from awr: '||SQLERRM);
13 :sql_text := NULL;
14 END;
15 /
SQL>
SQL>SELECT :sql_text FROM DUAL;

:SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT
'PNL-2003',
EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.VIRTUAL_METRIC_CONTEXT_CODE,
EDWM_AMZ_FINANCE.MH_ORGANISATION_LEVELS.REPORTING_ORG_CODE,
EDWM_AMZ_FINANCE.MH_ORGANISATION_LEVELS.ORGANISATION_LEVEL,
EDWM_AMZ_FINANCE.MH_ORGANISATION_LEVELS.ORGANISATION_HIER_NAME
,
EDWM_AMZ_FINANCE.MH_ORGANISATION_LEVELS.ORGANISATION_NAME,
   EDWM_AMZ_FINANCE_MD_CURRENCY2.CURRENCY_CODE,
EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.BUSINESS_METRIC_CODE,
EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.BUSINESS_METRIC_NAME,
   EDWM_AMZ_FINANCE.MD_BUDROX_RATES.EXCHANGE_RATE,
EDWM_AMZ_FINANCE.MD_MONTH.MONTH_NUMBER,
EDWM_AMZ_FINANCE.MD_MONTH.MONTH_DESC_SHORT,
EDWM_AMZ_FINANCE.MD_COST_CENTRE.COST_CENTRE_L1_NAME
,
EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE.METRIC_COMPONENT_FACTOR,
EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_NAME
,
EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_CODE
,
Sum(CASE WHEN EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_CODE= 'ACTUAL' AND EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.MEASURE_TYPE_NAME='Value' THEN EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.METRIC_VALUE_CM * EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE.METRIC_COMPONENT_FACTOR* ( EDWM_AMZ_FINANCE.MD_BUDROX_RATES.EXCHANGE_RATE ) END)
,
( Sum(CASE WHEN EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_CODE= 'BUDGET' AND EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.MEASURE_TYPE_NAME='Value' THEN EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.METRIC_VALUE_FY * EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE.METRIC_COMPONENT_FACTOR* ( EDWM_AMZ_FINANCE.MD_BUDROX_RATES.EXCHANGE_RATE ) END) ) - ( Sum(CASE WHEN EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_CODE= 'ACTUAL' AND EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.MEASURE_TYPE_NAME='Value' THEN EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.METRIC_VALUE_YTD * EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE.METRIC_COMPONENT_FACTOR* ( EDWM_AMZ_FINANCE.MD_BUDROX_RATES.EXCHANGE_RATE ) END) ),
Sum(CASE WHEN EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_CODE= 'ACTUAL' AND EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.MEASURE_TYPE_NAME='Value' THEN EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.METRIC_VALUE_YTD * EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE.METRIC_COMPONENT_FACTOR* ( EDWM_AMZ_FINANCE.MD_BUDROX_RATES.EXCHANGE_RATE ) END),
Sum(CASE WHEN EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_CODE= 'ACTUAL' AND EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.MEASURE_TYPE_NAME='Value' THEN EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.METRIC_VALUE_PY_CM * EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE.METRIC_COMPONENT_FACTOR* ( EDWM_AMZ_FINANCE.MD_BUDROX_RATES.EXCHANGE_RATE ) END),
Sum(CASE WHEN EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_CODE= 'ACTUAL' AND EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.MEASURE_TYPE_NAME='Value' THEN EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.METRIC_VALUE_PY_YTD * EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE.METRIC_COMPONENT_FACTOR* ( EDWM_AMZ_FINANCE.MD_BUDROX_RATES.EXCHANGE_RATE ) END),
Sum(CASE WHEN EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_CODE= 'ACTUAL' AND EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.MEASURE_TYPE_NAME='Value' THEN EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.METRIC_VALUE_FYPY * EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE.METRIC_COMPONENT_FACTOR* ( EDWM_AMZ_FINANCE.MD_BUDROX_RATES.EXCHANGE_RATE ) END),
( Sum(CASE WHEN EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_CODE= 'ACTUAL' AND EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.MEASURE_TYPE_NAME='Value' THEN EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.METRIC_VALUE_FYPY * EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE.METRIC_COMPONENT_FACTOR* ( EDWM_AMZ_FINANCE.MD_BUDROX_RATES.EXCHANGE_RATE ) END) )-( Sum(CASE WHEN EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_CODE= 'ACTUAL' AND EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.MEASURE_TYPE_NAME='Value' THEN EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.METRIC_VALUE_PY_YTD * EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE.METRIC_COMPONENT_FACTOR* ( EDWM_AMZ_FINANCE.MD_BUDROX_RATES.EXCHANGE_RATE ) END) ),
Sum(CASE WHEN EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_CODE= 'BUDGET' AND EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.MEASURE_TYPE_NAME='Value' THEN EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.METRIC_VALUE_CM * EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE.METRIC_COMPONENT_FACTOR* ( EDWM_AMZ_FINANCE.MD_BUDROX_RATES.EXCHANGE_RATE ) END),
Sum(CASE WHEN EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_CODE= 'BUDGET' AND EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.MEASURE_TYPE_NAME='Value' THEN EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.METRIC_VALUE_YTD * EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE.METRIC_COMPONENT_FACTOR* ( EDWM_AMZ_FINANCE.MD_BUDROX_RATES.EXCHANGE_RATE ) END),
Sum(CASE WHEN EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_CODE= 'BUDGET' AND EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.MEASURE_TYPE_NAME='Value' THEN EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.METRIC_VALUE_PY_CM * EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE.METRIC_COMPONENT_FACTOR* ( EDWM_AMZ_FINANCE.MD_BUDROX_RATES.EXCHANGE_RATE ) END),
Sum(CASE WHEN EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_CODE= 'BUDGET' AND EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.MEASURE_TYPE_NAME='Value' THEN EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.METRIC_VALUE_PY_YTD * EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE.METRIC_COMPONENT_FACTOR* ( EDWM_AMZ_FINANCE.MD_BUDROX_RATES.EXCHANGE_RATE ) END),
Sum(CASE WHEN EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_CODE= 'BUDGET' AND EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.MEASURE_TYPE_NAME='Value' THEN EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.METRIC_VALUE_FY * EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE.METRIC_COMPONENT_FACTOR* ( EDWM_AMZ_FINANCE.MD_BUDROX_RATES.EXCHANGE_RATE ) END),
Sum(CASE WHEN EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_CODE= 'BUDGET' AND EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.MEASURE_TYPE_NAME='Value' THEN EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.METRIC_VALUE_FYPY * EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE.METRIC_COMPONENT_FACTOR* ( EDWM_AMZ_FINANCE.MD_BUDROX_RATES.EXCHANGE_RATE ) END),
( Sum(CASE WHEN EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_CODE= 'ACTUAL' AND EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.MEASURE_TYPE_NAME='Value' THEN EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.METRIC_VALUE_CM * EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE.METRIC_COMPONENT_FACTOR* ( EDWM_AMZ_FINANCE.MD_BUDROX_RATES.EXCHANGE_RATE ) END)
)-( Sum(CASE WHEN EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_CODE= 'BUDGET' AND EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.MEASURE_TYPE_NAME='Value' THEN EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.METRIC_VALUE_CM * EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE.METRIC_COMPONENT_FACTOR* ( EDWM_AMZ_FINANCE.MD_BUDROX_RATES.EXCHANGE_RATE ) END) ),
(( Sum(CASE WHEN EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_CODE= 'ACTUAL' AND EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.MEASURE_TYPE_NAME='Value' THEN EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.METRIC_VALUE_YTD * EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE.METRIC_COMPONENT_FACTOR* ( EDWM_AMZ_FINANCE.MD_BUDROX_RATES.EXCHANGE_RATE ) END) ) - ( Sum(CASE WHEN EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_CODE= 'BUDGET' AND EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.MEASURE_TYPE_NAME='Value' THEN EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.METRIC_VALUE_YTD * EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE.METRIC_COMPONENT_FACTOR* ( EDWM_AMZ_FINANCE.MD_BUDROX_RATES.EXCHANGE_RATE ) END) )),
Sum(CASE WHEN EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_CODE= 'RBU1' AND EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.MEASURE_TYPE_NAME='Value' THEN EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.METRIC_VALUE_CM * EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE.METRIC_COMPONENT_FACTOR* ( EDWM_AMZ_FINANCE.MD_BUDROX_RATES.EXCHANGE_RATE ) END),
Sum(CASE WHEN EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_CODE= 'RBU1' AND EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.MEASURE_TYPE_NAME='Value' THEN EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.METRIC_VALUE_FY * EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE.METRIC_COMPONENT_FACTOR* ( EDWM_AMZ_FINANCE.MD_BUDROX_RATES.EXCHANGE_RATE ) END),
Sum(CASE WHEN EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_CODE= 'RBU1' AND EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.MEASURE_TYPE_NAME='Value' THEN EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.METRIC_VALUE_FYPY * EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE.METRIC_COMPONENT_FACTOR* ( EDWM_AMZ_FINANCE.MD_BUDROX_RATES.EXCHANGE_RATE ) END),
Sum(CASE WHEN EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_CODE= 'RBU1' AND EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.MEASURE_TYPE_NAME='Value' THEN EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.METRIC_VALUE_YTD * EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE.METRIC_COMPONENT_FACTOR* ( EDWM_AMZ_FINANCE.MD_BUDROX_RATES.EXCHANGE_RATE ) END),
Sum(CASE WHEN EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_CODE= 'RBU2' AND EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.MEASURE_TYPE_NAME='Value' THEN EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.METRIC_VALUE_CM * EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE.METRIC_COMPONENT_FACTOR* ( EDWM_AMZ_FINANCE.MD_BUDROX_RATES.EXCHANGE_RATE ) END),
Sum(CASE WHEN EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_CODE= 'RBU2' AND EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.MEASURE_TYPE_NAME='Value' THEN EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.METRIC_VALUE_FY * EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE.METRIC_COMPONENT_FACTOR* ( EDWM_AMZ_FINANCE.MD_BUDROX_RATES.EXCHANGE_RATE ) END),
Sum(CASE WHEN EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_CODE= 'RBU2' AND EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.MEASURE_TYPE_NAME='Value' THEN EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.METRIC_VALUE_FYPY * EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE.METRIC_COMPONENT_FACTOR* ( EDWM_AMZ_FINANCE.MD_BUDROX_RATES.EXCHANGE_RATE ) END),
Sum(CASE WHEN EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_CODE= 'RBU2' AND EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.MEASURE_TYPE_NAME='Value' THEN EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.METRIC_VALUE_YTD * EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE.METRIC_COMPONENT_FACTOR* ( EDWM_AMZ_FINANCE.MD_BUDROX_RATES.EXCHANGE_RATE ) END),
Sum(CASE WHEN EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_CODE= 'ABU' AND EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.MEASURE_TYPE_NAME='Value' THEN EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.METRIC_VALUE_CM * EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE.METRIC_COMPONENT_FACTOR* ( EDWM_AMZ_FINANCE.MD_BUDROX_RATES.EXCHANGE_RATE ) END),
Sum(CASE WHEN EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_CODE= 'ABU' AND EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.MEASURE_TYPE_NAME='Value' THEN EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.METRIC_VALUE_FY * EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE.METRIC_COMPONENT_FACTOR* ( EDWM_AMZ_FINANCE.MD_BUDROX_RATES.EXCHANGE_RATE ) END),
Sum(CASE WHEN EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_CODE= 'ABU' AND EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.MEASURE_TYPE_NAME='Value' THEN EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.METRIC_VALUE_FYPY * EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE.METRIC_COMPONENT_FACTOR* ( EDWM_AMZ_FINANCE.MD_BUDROX_RATES.EXCHANGE_RATE ) END),
( Sum(CASE WHEN EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_CODE= 'RBU1' AND EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.MEASURE_TYPE_NAME='Value' THEN EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.METRIC_VALUE_FY * EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE.METRIC_COMPONENT_FACTOR* ( EDWM_AMZ_FINANCE.MD_BUDROX_RATES.EXCHANGE_RATE ) END) ) - ( Sum(CASE WHEN EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_CODE= 'ACTUAL' AND EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.MEASURE_TYPE_NAME='Value' THEN EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.METRIC_VALUE_YTD * EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE.METRIC_COMPONENT_FACTOR* ( EDWM_AMZ_FINANCE.MD_BUDROX_RATES.EXCHANGE_RATE ) END) )
,
( Sum(CASE WHEN EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_CODE= 'RBU2' AND EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.MEASURE_TYPE_NAME='Value' THEN EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.METRIC_VALUE_FY * EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE.METRIC_COMPONENT_FACTOR* ( EDWM_AMZ_FINANCE.MD_BUDROX_RATES.EXCHANGE_RATE ) END) ) - ( Sum(CASE WHEN EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_CODE= 'ACTUAL' AND EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.MEASURE_TYPE_NAME='Value' THEN EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.METRIC_VALUE_YTD * EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE.METRIC_COMPONENT_FACTOR* ( EDWM_AMZ_FINANCE.MD_BUDROX_RATES.EXCHANGE_RATE ) END) ),
( Sum(CASE WHEN EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_CODE= 'ABU' AND EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.MEASURE_TYPE_NAME='Value' THEN EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.METRIC_VALUE_FY * EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE.METRIC_COMPONENT_FACTOR* ( EDWM_AMZ_FINANCE.MD_BUDROX_RATES.EXCHANGE_RATE ) END) ) - ( Sum(CASE WHEN EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_CODE= 'ACTUAL' AND EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.MEASURE_TYPE_NAME='Value' THEN EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.METRIC_VALUE_YTD * EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE.METRIC_COMPONENT_FACTOR* ( EDWM_AMZ_FINANCE.MD_BUDROX_RATES.EXCHANGE_RATE ) END) ),
Sum(CASE WHEN EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_CODE= 'ABU' AND EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.MEASURE_TYPE_NAME='Value' THEN EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.METRIC_VALUE_YTD * EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE.METRIC_COMPONENT_FACTOR* ( EDWM_AMZ_FINANCE.MD_BUDROX_RATES.EXCHANGE_RATE ) END),
EDWM_AMZ_FINANCE.MD_MONTH.YEAR_NUMBER,
trim(to_char(EDWM_AMZ_FINANCE.MD_MONTH.MONTH_CODE,'999999')),
Sum(CASE WHEN EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_CODE= 'ACTUAL' AND EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.MEASURE_TYPE_NAME='Value' THEN EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.METRIC_VALUE_M_PREV1 * EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE.METRIC_COMPONENT_FACTOR* ( EDWM_AMZ_FINANCE.MD_BUDROX_RATES.EXCHANGE_RATE ) END),
Sum(CASE WHEN EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_CODE= 'ACTUAL' AND EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.MEASURE_TYPE_NAME='Value' THEN EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.METRIC_VALUE_M_PREV2 * EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE.METRIC_COMPONENT_FACTOR* ( EDWM_AMZ_FINANCE.MD_BUDROX_RATES.EXCHANGE_RATE ) END),
Sum(CASE WHEN EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_CODE= 'ACTUAL' AND EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.MEASURE_TYPE_NAME='Value' THEN EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.METRIC_VALUE_M_PREV3 * EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE.METRIC_COMPONENT_FACTOR* ( EDWM_AMZ_FINANCE.MD_BUDROX_RATES.EXCHANGE_RATE ) END),
Decode(EDWM_AMZ_FINANCE.MH_ORGANISATION_LEVELS.ORGANISATION_LEVEL,'Region',0,'Region -1',1,'Region -2',2,'Region -3',3,'Region -4',4,'Region -5',5,'Region -6',6,EDWM_AMZ_FINANCE.MH_ORGANISATION_LEVELS.SORT_ORDER),
EDWM_AMZ_FINANCE.MH_ORGANISATION_LEVELS.DISPLAY_NAME,
EDWM_AMZ_FINANCE.MD_COST_CENTRE.COST_CENTRE_L2_Code
,
EDWM_AMZ_FINANCE.MD_COST_CENTRE.COST_CENTRE_L2_Name
,
EDWM_AMZ_FINANCE.MD_COST_CENTRE. COST_CENTRE_L1_Code,
Sum(CASE WHEN EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_CODE= 'OTHERS' AND EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.MEASURE_TYPE_NAME='Value' THEN EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.METRIC_VALUE_CM * EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE.METRIC_COMPONENT_FACTOR* ( EDWM_AMZ_FINANCE.MD_BUDROX_RATES.EXCHANGE_RATE ) END),
Sum(CASE WHEN EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_CODE='OTHERS' AND EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.MEASURE_TYPE_NAME='Value' THEN EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.METRIC_VALUE_CM * EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE.METRIC_COMPONENT_FACTOR* ( EDWM_AMZ_FINANCE.MD_BUDROX_RATES.EXCHANGE_RATE ) END),
Sum(CASE WHEN EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_CODE='OTHERS' AND EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.MEASURE_TYPE_NAME='Value' THEN EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.METRIC_VALUE_FYPY * EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE.METRIC_COMPONENT_FACTOR* ( EDWM_AMZ_FINANCE.MD_BUDROX_RATES.EXCHANGE_RATE ) END),
Sum(CASE WHEN EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_CODE= 'OTHERS' AND EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.MEASURE_TYPE_NAME='Value' THEN EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.METRIC_VALUE_YTD * EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE.METRIC_COMPONENT_FACTOR* ( EDWM_AMZ_FINANCE.MD_BUDROX_RATES.EXCHANGE_RATE ) END)
FROM
EDWM_AMZ_FINANCE.MF_AFG_MONTHLY,
EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC,
EDWM_AMZ_FINANCE.MH_ORGANISATION_LEVELS,
EDWM_AMZ_FINANCE.MD_CURRENCY EDWM_AMZ_FINANCE_MD_CURRENCY2,
EDWM_AMZ_FINANCE.MD_BUDROX_RATES,
EDWM_AMZ_FINANCE.MD_MONTH,
EDWM_AMZ_FINANCE.MD_COST_CENTRE,
EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE,
EDWM_AMZ_FINANCE.MD_SCENARIO,
EDWM_AMZ_FINANCE.MD_BUSINESS_METRIC
WHERE
( EDWM_AMZ_FINANCE.MD_MONTH.MONTH_KEY=EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.MONTH_KEY )
AND ( EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.SCENARIO_KEY=EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_KEY )
AND ( EDWM_AMZ_FINANCE.MD_COST_CENTRE.COST_CENTRE_KEY=EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.COST_CENTRE_KEY )
AND ( EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.VIRTUAL_METRIC_VARIANT_KEY=EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE.VIRTUAL_METRIC_VARIANT_KEY )
AND ( EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.METRIC_VARIANT_KEY=EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE.METRIC_VARIANT_KEY )
AND ( EDWM_AMZ_FINANCE.MH_ORGANISATION_LEVELS.FACT_ORG_KEY=EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.ORGANISATION_KEY )
AND ( EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.CURRENCY_KEY=EDWM_AMZ_FINANCE.MD_BUDROX_RATES.CURRENCY_KEY_BASE )
AND ( EDWM_AMZ_FINANCE.MD_BUSINESS_METRIC.BUSINESS_METRIC_KEY=EDWM_AMZ_FINANCE.MF_AFG_MONTHLY.BUSINESS_METRIC_KEY )
AND ( EDWM_AMZ_FINANCE.MD_BUDROX_RATES.CURRENCY_KEY_TARGET=EDWM_AMZ_FINANCE_MD_CURRENCY2.CURRENCY_KEY )
AND
(
   ( ( case when ( EDWM_AMZ_FINANCE.MD_BUSINESS_METRIC.BUSINESS_METRIC_CODE ) NOT IN ('OTHEREXTMARGIN') then 'Exclude OEM (WD1-3)' end ) in 'Exclude OEM (WD1-3)'
OR
'Include OEM (WD4+)' in 'Exclude OEM (WD1-3)'

SQL>
SQL>-- validate sql_text
SQL>SET TERM ON;
SQL>BEGIN
2    IF :sql_text IS NULL THEN
3 RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');
4    END IF;
5 END;
6 /
SQL>SET TERM OFF;
SQL>
SQL>-- to avoid errors when sql_text lacks LFs and is more than 2000 bytes
SQL>BEGIN
2    :sql_text := REPLACE(:sql_text, ')', ')'||CHR(10));
3    :sql_text := REPLACE(:sql_text, ',', ','||CHR(10));
4    -- remove consecutive LFs
5    :sql_text := REPLACE(:sql_text, CHR(10)||CHR(10)||CHR(10)||CHR(10)||CHR(10), CHR(10));
6    :sql_text := REPLACE(:sql_text, CHR(10)||CHR(10)||CHR(10), CHR(10));
7    :sql_text := REPLACE(:sql_text, CHR(10)||CHR(10), CHR(10));
8 END;
9 /
SQL>
SQL>SELECT :sql_text FROM DUAL;

EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.VIRTUAL_METRIC_CONTEXT_CODE,

EDWM_AMZ_FINANCE.MH_ORGANISATION_LEVELS.REPORTING_ORG_CODE,

EDWM_AMZ_FINANCE.MH_ORGANISATION_LEVELS.ORGANISATION_LEVEL,

EDWM_AMZ_FINANCE.MH_ORGANISATION_LEVELS.ORGANISATION_HIER_NAME
,

EDWM_AMZ_FINANCE.MH_ORGANISATION_LEVELS.ORGANISATION_NAME,

EDWM_AMZ_FINANCE_MD_CURRENCY2.CURRENCY_CODE,

EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.BUSINESS_METRIC_CODE,

EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC.BUSINESS_METRIC_NAME,

EDWM_AMZ_FINANCE.MD_BUDROX_RATES.EXCHANGE_RATE,

EDWM_AMZ_FINANCE.MD_MONTH.MONTH_NUMBER,

EDWM_AMZ_FINANCE.MD_MONTH.MONTH_DESC_SHORT,

EDWM_AMZ_FINANCE.MD_COST_CENTRE.COST_CENTRE_L1_NAME
,

EDWM_AMZ_FINANCE.MD_VIRTUAL_METRIC_STRUCTURE.METRIC_COMPONENT_FACTOR,

EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_NAME
,

EDWM_AMZ_FINANCE.MD_SCENARIO.SCENARIO_CODE
,

EDWM_AMZ_FINANCE.MD_MONTH.YEAR_NUMBER,

trim(to_char(EDWM_AMZ_FINANCE.MD_MONTH.MONTH_CODE,
'999999')
)
,

Decode(EDWM_AMZ_FINANCE.MH_ORGANISATION_LEVELS.ORGANISATION_LEVEL,
'Region',
0,
'Region -1',
1,
'Region -2',
2,
'Region -3',
3,
'Region -4',
4,
'Region -5',
5,
'Region -6',
6,
EDWM_AMZ_FINANCE.MH_ORGANISATION_LEVELS.SORT_ORDER)
,

EDWM_AMZ_FINANCE.MH_ORGANISATION_LEVELS.DISPLAY_NAME,

EDWM_AMZ_FINANCE.MD_COST_CENTRE.COST_CENTRE_L2_Code
,

EDWM_AMZ_FINANCE.MD_COST_CENTRE.COST_CENTRE_L2_Name
,

EDWM_AMZ_FINANCE.MD_COST_CENTRE. COST_CENTRE_L1_Code

SQL>
SQL>-- get other_xml from memory
SQL>BEGIN
2    FOR i IN (SELECT other_xml
3      FROM gv$sql_plan
4     WHERE sql_id = TRIM('&&sql_id.')
5       AND plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.'))
6       AND other_xml IS NOT NULL
7     ORDER BY
8    child_number, id)
9    LOOP
10 :other_xml := i.other_xml;
11 EXIT; -- 1st
12    END LOOP;
13 EXCEPTION
14    WHEN OTHERS THEN
15 DBMS_OUTPUT.PUT_LINE('getting other_xml from memory: '||SQLERRM);
16 :other_xml := NULL;
17 END;
18 /
SQL>
SQL>-- get other_xml from awr
SQL>BEGIN
2    IF :other_xml IS NULL OR NVL(DBMS_LOB.GETLENGTH(:other_xml), 0) = 0 THEN
3 FOR i IN (SELECT other_xml
4        FROM dba_hist_sql_plan
5       WHERE sql_id = TRIM('&&sql_id.')
6         AND plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.'))
7         AND other_xml IS NOT NULL
8       ORDER BY
9      id)
10 LOOP
11     :other_xml := i.other_xml;
12     EXIT; -- 1st
13 END LOOP;
14    END IF;
15 EXCEPTION
16    WHEN OTHERS THEN
17 DBMS_OUTPUT.PUT_LINE('getting other_xml from awr: '||SQLERRM);
18 :other_xml := NULL;
19 END;
20 /
SQL>
SQL>SELECT :other_xml FROM DUAL;

:OTHER_XML
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<other_xml><info type="nodeid/pflags">000047000017</info><info type="nodeid/pflags">000042000017</info><info type="nodeid/pflags">000037000017</info><info type="nodeid/pflags">000032000017</info><info type="nodeid/pflags">000025000017</info><info type="cbqt_star_transformation">yes</info><info type="db_version">11.2.0.3</info><info type="parse_schema"><![CDATA["EDWM_AMZ_FINANCE_BO"]]></info><info type="plan_hash">3403809735</info><info type="plan_hash_2">1574633730</info><outline_data><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('11.2.0.3')]]></hint><hint><![CDATA[DB_VERSION('11.2.0.3')]]></hint><hint><![CDATA[OPT_PARAM('star_transformation_enabled' 'true')]]></hint><hint><![CDATA[ALL_ROWS]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$2")]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$9A51E2B0")]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$F7FA570B")]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$5")]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$F36490C4")]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$6D6F9C88")]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$E1F9C76C")]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$E1F9C76D")]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$2C311650")]]></hint><hint><![CDATA[TABLE_LOOKUP_BY_NL(@"SEL$1BAA8C08" "MF_AFG_MONTHLY"@"SEL$1")]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$F6FE73F1")]]></hint><hint><![CDATA[OUTLINE(@"SEL$1")]]></hint><hint><![CDATA[OUTLINE(@"SEL$1BAA8C08")]]></hint><hint><![CDATA[OUTLINE(@"SEL$35E0D4BC")]]></hint><hint><![CDATA[ELIMINATE_JOIN(@"SEL$1" "MD_BUSINESS_METRIC"@"SEL$1")]]></hint><hint><![CDATA[OUTLINE(@"SEL$5208623C")]]></hint><hint><![CDATA[STAR_TRANSFORMATION(@"SEL$1" "MF_AFG_MONTHLY"@"SEL$1" SUBQUERIES(("MD_MONTH"@"SEL$1") ("MD_BUSINESS_METRIC"@"SEL$1") TEMP_TABLE("MH_ORGANISATION_LEVELS"@"SEL$1") TEMP_TABLE("MD_VIRTUAL_METRIC_STRUCTURE"@"SEL$1" "MD_VIRTUAL_METRIC"@"SEL$1")))]]></hint><hint><![CDATA[NO_ACCESS(@"SEL$F6FE73F1" "VW_ST_B08BFF26"@"SEL$F6FE73F1")]]></hint><hint><![CDATA[FU
LL(@"SEL$F6FE73F1" "MD_MONTH"@"SEL$1")]]></hint><hint><![CDATA[FULL(@"SEL$F6FE73F1" "T1"@"SEL$9F8A3293")]]></hint><hint><![CDATA[FULL(@"SEL$F6FE73F1" "T1"@"SEL$58C43D77")]]></hint><hint><![CDATA[FULL(@"SEL$F6FE73F1" "MD_SCENARIO"@"SEL$1")]]></hint><hint><![CDATA[FULL(@"SEL$F6FE73F1" "MD_COST_CENTRE"@"SEL$1")]]></hint><hint><![CDATA[INDEX_RS_ASC(@"SEL$F6FE73F1" "EDWM_AMZ_FINANCE_MD_CURRENCY2"@"SEL$1" ("MD_CURRENCY"."CURRENCY_CODE"))]]></hint><hint><![CDATA[INDEX_RS_ASC(@"SEL$F6FE73F1" "MD_BUDROX_RATES"@"SEL$1" "XAK1MD_BUDROX_RATES2")]]></hint><hint><![CDATA[LEADING(@"SEL$F6FE73F1" "VW_ST_B08BFF26"@"SEL$F6FE73F1" "MD_MONTH"@"SEL$1" "T1"@"SEL$9F8A3293" "T1"@"SEL$58C43D77" "MD_SCENARIO"@"SEL$1" "MD_COST_CENTRE"@"SEL$1" "EDWM_AMZ_FINANCE_MD_CURRENCY2"@"SEL$1" "MD_BUDROX_RATES"@"SEL$1")]]></hint><hint><![CDATA[USE_HASH(@"SEL$F6FE73F1" "MD_MONTH"@"SEL$1")]]></hint><hint><![CDATA[USE_HASH(@"SEL$F6FE73F1" "T1"@"SEL$9F8A3293")]]></hint><hint><![CDATA[USE_HASH(@"SEL$F6FE73F1" "T1"@"SEL$58C43D77")]]></hint><hint><![CDATA[USE_HASH(@"SEL$F6FE73F1" "MD_SCENARIO"@"SEL$1")]]></hint><hint><![CDATA[USE_HASH(@"SEL$F6FE73F1" "MD_COST_CENTRE"@"SEL$1")]]></hint><hint><![CDATA[USE_MERGE_CARTESIAN(@"SEL$F6FE73F1" "EDWM_AMZ_FINANCE_MD_CURRENCY2"@"SEL$1")]]></hint><hint><![CDATA[USE_HASH(@"SEL$F6FE73F1" "MD_BUDROX_RATES"@"SEL$1")]]></hint><hint><![CDATA[USE_HASH_AGGREGATION(@"SEL$F6FE73F1")]]></hint><hint><![CDATA[PUSH_SUBQ(@"SEL$5")]]></hint><hint><![CDATA[BITMAP_TREE(@"SEL$2C311650" "MF_AFG_MONTHLY"@"SEL$1" AND(("MF_AFG_MONTHLY"."ORGANISATION_KEY") ("MF_AFG_MONTHLY"."METRIC_VARIANT_KEY") ("MF_AFG_MONTHLY"."BUSINESS_METRIC_KEY") ("MF_AFG_MONTHLY"."MONTH_KEY")))]]></hint><hint><![CDATA[ROWID(@"SEL$2C311650" "SYS_CP_MF_AFG_MONTHLY"@"SEL$2C311650")]]></hint><hint><![CDATA[LEADING(@"SEL$2C311650" "MF_AFG_MONTHLY"@"SEL$1" "SYS_CP_MF_AFG_MONTHLY"@"SEL$2C311650")]]></hint><hint><![CDATA[USE_NL(@"SEL$2C311650" "SYS_CP_MF_AFG_MONTHLY"@"SEL$2C311650")]]></hint><hint><![CDATA[FULL(@"SEL$E1F9C76D" "T1"@
"SEL$E1F9C76D")]]></hint><hint><![CDATA[SEMIJOIN_DRIVER(@"SEL$E1F9C76D")]]></hint><hint><![CDATA[FULL(@"SEL$E1F9C76C" "T1"@"SEL$E1F9C76C")]]></hint><hint><![CDATA[SEMIJOIN_DRIVER(@"SEL$E1F9C76C")]]></hint><hint><![CDATA[FULL(@"SEL$6D6F9C88" "MD_BUSINESS_METRIC"@"SEL$6D6F9C88")]]></hint><hint><![CDATA[SEMIJOIN_DRIVER(@"SEL$6D6F9C88")]]></hint><hint><![CDATA[FULL(@"SEL$F36490C4" "MD_MONTH"@"SEL$F36490C4")]]></hint><hint><![CDATA[SEMIJOIN_DRIVER(@"SEL$F36490C4")]]></hint><hint><![CDATA[FULL(@"SEL$5" "AFG_DEFAULT_BUDROX_YEAR"@"SEL$5")]]></hint><hint><![CDATA[FULL(@"SEL$F7FA570B" "MD_VIRTUAL_METRIC"@"SEL$F7FA570B")]]></hint><hint><![CDATA[FULL(@"SEL$F7FA570B" "MD_VIRTUAL_METRIC_STRUCTURE"@"SEL$F7FA570B")]]></hint><hint><![CDATA[LEADING(@"SEL$F7FA570B" "MD_VIRTUAL_METRIC"@"SEL$F7FA570B" "MD_VIRTUAL_METRIC_STRUCTURE"@"SEL$F7FA570B")]]></hint><hint><![CDATA[USE_HASH(@"SEL$F7FA570B" "MD_VIRTUAL_METRIC_STRUCTURE"@"SEL$F7FA570B")]]></hint><hint><![CDATA[SEMIJOIN_DRIVER(@"SEL$F7FA570B")]]></hint><hint><![CDATA[BITMAP_TREE(@"SEL$9A51E2B0" "MH_ORGANISATION_LEVELS"@"SEL$9A51E2B0" AND(("MH_ORGANISATION_LEVELS"."ORGANISATION_LEVEL") ("MH_ORGANISATION_LEVELS"."ORGANISATION_HIER_NAME")))]]></hint><hint><![CDATA[SEMIJOIN_DRIVER(@"SEL$9A51E2B0")]]></hint><hint><![CDATA[INDEX_RS_ASC(@"SEL$2" "MD_ORG_DESCENDANTS"@"SEL$2" ("MD_ORG_DESCENDANTS"."ORG_UNIT_KEY_FACT" "MD_ORG_DESCENDANTS"."ORG_UNIT_REPORTING_CODE"))]]></hint></outline_data></other_xml>

SQL>
SQL>-- validate other_xml
SQL>SET TERM ON;
SQL>BEGIN
2 IF :other_xml IS NULL THEN
3 RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');
4 END IF;
5 END;
6 /
SQL>SET TERM OFF;
SQL>
SQL>-- generates script that creates sql profile in target system:
SQL>SET ECHO OFF;
coe_xfr_sql_profile_bbp1a0d9xkagg_3403809735.sql.

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

-- trim parameters
COL sql_id NEW_V sql_id FOR A30;
COL plan_hash_value NEW_V plan_hash_value FOR A30;
SELECT TRIM('&&sql_id.') sql_id, TRIM('&&plan_hash_value.') plan_hash_value FROM DUAL;

VAR sql_text CLOB;
VAR other_xml CLOB;
EXEC :sql_text := NULL;
EXEC :other_xml := NULL;

-- get sql_text from memory
DECLARE
l_sql_text VARCHAR2(32767);
BEGIN -- 10g see bug 5017909
FOR i IN (SELECT DISTINCT piece, sql_text
              FROM gv$sqltext_with_newlines
             WHERE sql_id = TRIM('&&sql_id.')
             ORDER BY 1, 2)
LOOP
    IF :sql_text IS NULL THEN
      DBMS_LOB.CREATETEMPORARY(:sql_text, TRUE);
      DBMS_LOB.OPEN(:sql_text, DBMS_LOB.LOB_READWRITE);
    END IF;
    l_sql_text := REPLACE(i.sql_text, CHR(00), ' ');
    DBMS_LOB.WRITEAPPEND(:sql_text, LENGTH(l_sql_text), l_sql_text);
END LOOP;
IF :sql_text IS NOT NULL THEN
    DBMS_LOB.CLOSE(:sql_text);
END IF;
EXCEPTION
WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('getting sql_text from memory: '||SQLERRM);
    :sql_text := NULL;
END;
/

-- get sql_text from awr
BEGIN
IF :sql_text IS NULL OR NVL(DBMS_LOB.GETLENGTH(:sql_text), 0) = 0 THEN
    SELECT REPLACE(sql_text, CHR(00), ' ')
      INTO :sql_text
      FROM dba_hist_sqltext
     WHERE sql_id = TRIM('&&sql_id.')
       AND sql_text IS NOT NULL
       AND ROWNUM = 1;
END IF;
EXCEPTION
WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('getting sql_text from awr: '||SQLERRM);
    :sql_text := NULL;
END;
/

SELECT :sql_text FROM DUAL;

-- validate sql_text
SET TERM ON;
BEGIN
IF :sql_text IS NULL THEN
RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');
END IF;
END;
/
SET TERM OFF;

-- to avoid errors when sql_text lacks LFs and is more than 2000 bytes
BEGIN
:sql_text := REPLACE(:sql_text, ')', ')'||CHR(10));
:sql_text := REPLACE(:sql_text, ',', ','||CHR(10));
-- remove consecutive LFs
:sql_text := REPLACE(:sql_text, CHR(10)||CHR(10)||CHR(10)||CHR(10)||CHR(10), CHR(10));
:sql_text := REPLACE(:sql_text, CHR(10)||CHR(10)||CHR(10), CHR(10));
:sql_text := REPLACE(:sql_text, CHR(10)||CHR(10), CHR(10));
END;
/

SELECT :sql_text FROM DUAL;

-- get other_xml from memory
BEGIN
FOR i IN (SELECT other_xml
              FROM gv$sql_plan
             WHERE sql_id = TRIM('&&sql_id.')
               AND plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.'))
               AND other_xml IS NOT NULL
             ORDER BY
                   child_number, id)
LOOP
    :other_xml := i.other_xml;
    EXIT; -- 1st
END LOOP;
EXCEPTION
WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('getting other_xml from memory: '||SQLERRM);
    :other_xml := NULL;
END;
/

-- get other_xml from awr
BEGIN
IF :other_xml IS NULL OR NVL(DBMS_LOB.GETLENGTH(:other_xml), 0) = 0 THEN
    FOR i IN (SELECT other_xml
                FROM dba_hist_sql_plan
               WHERE sql_id = TRIM('&&sql_id.')
                 AND plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.'))
                 AND other_xml IS NOT NULL
               ORDER BY
                     id)
    LOOP
      :other_xml := i.other_xml;
      EXIT; -- 1st
    END LOOP;
END IF;
EXCEPTION
WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('getting other_xml from awr: '||SQLERRM);
    :other_xml := NULL;
END;
/

SELECT :other_xml FROM DUAL;

-- validate other_xml
SET TERM ON;
BEGIN
IF :other_xml IS NULL THEN
RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');
END IF;
END;
/
SET TERM OFF;

-- generates script that creates sql profile in target system:
SET ECHO OFF;
PRO coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql.
SET FEED OFF LIN 666 TRIMS ON TI OFF TIMI OFF SERVEROUT ON SIZE 1000000 FOR WOR;
SET SERVEROUT ON SIZE UNL FOR WOR;
SPO OFF;
SPO coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql;
DECLARE
l_pos NUMBER;
l_hint VARCHAR2(32767);
BEGIN
DBMS_OUTPUT.PUT_LINE('SPO coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..log;');
DBMS_OUTPUT.PUT_LINE('SET ECHO ON TERM ON LIN 2000 TRIMS ON NUMF 99999999999999999999;');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('REM $Header: 215187.1 coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql 11.4.3.5 '||TO_CHAR(SYSDATE, 'YYYY/MM/DD')||' carlos.sierra $');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('REM Copyright (c) 2000-2011, Oracle Corporation. All rights reserved.');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('REM AUTHOR');
DBMS_OUTPUT.PUT_LINE('REM   carlos.sierra@oracle.com');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('REM SCRIPT');
DBMS_OUTPUT.PUT_LINE('REM   coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('REM DESCRIPTION');
DBMS_OUTPUT.PUT_LINE('REM   This script is generated by coe_xfr_sql_profile.sql');
DBMS_OUTPUT.PUT_LINE('REM   It contains the SQL*Plus commands to create a custom');
DBMS_OUTPUT.PUT_LINE('REM   SQL Profile for SQL_ID &&sql_id. based on plan hash');
DBMS_OUTPUT.PUT_LINE('REM   value &&plan_hash_value..');
DBMS_OUTPUT.PUT_LINE('REM   The custom SQL Profile to be created by this script');
DBMS_OUTPUT.PUT_LINE('REM   will affect plans for SQL commands with signature');
DBMS_OUTPUT.PUT_LINE('REM   matching the one for SQL Text below.');
DBMS_OUTPUT.PUT_LINE('REM   Review SQL Text and adjust accordingly.');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('REM PARAMETERS');
DBMS_OUTPUT.PUT_LINE('REM   None.');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('REM EXAMPLE');
DBMS_OUTPUT.PUT_LINE('REM   SQL> START coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql;');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('REM NOTES');
DBMS_OUTPUT.PUT_LINE('REM   1. Should be run as SYSTEM or SYSDBA.');
DBMS_OUTPUT.PUT_LINE('REM   2. User must have CREATE ANY SQL PROFILE privilege.');
DBMS_OUTPUT.PUT_LINE('REM   3. SOURCE and TARGET systems can be the same or similar.');
DBMS_OUTPUT.PUT_LINE('REM   4. To drop this custom SQL Profile after it has been created:');
DBMS_OUTPUT.PUT_LINE('REM      EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(''coe_&&sql_id._&&plan_hash_value.'');');
DBMS_OUTPUT.PUT_LINE('REM   5. Be aware that using DBMS_SQLTUNE requires a license');
DBMS_OUTPUT.PUT_LINE('REM      for the Oracle Tuning Pack.');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('WHENEVER SQLERROR EXIT SQL.SQLCODE;');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('VAR signature NUMBER;');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('DECLARE');
DBMS_OUTPUT.PUT_LINE('sql_txt CLOB;');
DBMS_OUTPUT.PUT_LINE('h       SYS.SQLPROF_ATTR;');
DBMS_OUTPUT.PUT_LINE('BEGIN');
DBMS_OUTPUT.PUT_LINE('sql_txt := q''[');
WHILE NVL(LENGTH(:sql_text), 0) > 0
LOOP
    l_pos := INSTR(:sql_text, CHR(10));
    IF l_pos > 0 THEN
      DBMS_OUTPUT.PUT_LINE(SUBSTR(:sql_text, 1, l_pos - 1));
      :sql_text := SUBSTR(:sql_text, l_pos + 1);
    ELSE
      DBMS_OUTPUT.PUT_LINE(:sql_text);
      :sql_text := NULL;
    END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(']'';');
DBMS_OUTPUT.PUT_LINE('h := SYS.SQLPROF_ATTR(');
DBMS_OUTPUT.PUT_LINE('q''[BEGIN_OUTLINE_DATA]'',');
FOR i IN (SELECT /*+ opt_param('parallel_execution_enabled', 'false') */
                   SUBSTR(EXTRACTVALUE(VALUE(d), '/hint'), 1, 4000) hint
              FROM TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(:other_xml), '/*/outline_data/hint'))) d)
LOOP
    l_hint := i.hint;
    WHILE NVL(LENGTH(l_hint), 0) > 0
    LOOP
      IF LENGTH(l_hint) <= 500 THEN
        DBMS_OUTPUT.PUT_LINE('q''['||l_hint||']'',');
        l_hint := NULL;
      ELSE
        l_pos := INSTR(SUBSTR(l_hint, 1, 500), ' ', -1);
        DBMS_OUTPUT.PUT_LINE('q''['||SUBSTR(l_hint, 1, l_pos)||']'',');
        l_hint := '   '||SUBSTR(l_hint, l_pos);
      END IF;
    END LOOP;
END LOOP;
DBMS_OUTPUT.PUT_LINE('q''[END_OUTLINE_DATA]'');');
DBMS_OUTPUT.PUT_LINE(':signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);');
DBMS_OUTPUT.PUT_LINE('DBMS_SQLTUNE.IMPORT_SQL_PROFILE (');
DBMS_OUTPUT.PUT_LINE('sql_text    => sql_txt,');
DBMS_OUTPUT.PUT_LINE('profile     => h,');
DBMS_OUTPUT.PUT_LINE('name        => ''coe_&&sql_id._&&plan_hash_value.'',');
DBMS_OUTPUT.PUT_LINE('description => ''coe &&sql_id. &&plan_hash_value. ''||:signature||'''',');
DBMS_OUTPUT.PUT_LINE('category    => ''DEFAULT'',');
DBMS_OUTPUT.PUT_LINE('validate    => TRUE,');
DBMS_OUTPUT.PUT_LINE('replace     => TRUE,');
DBMS_OUTPUT.PUT_LINE('force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );');
DBMS_OUTPUT.PUT_LINE('END;');
DBMS_OUTPUT.PUT_LINE('/');
DBMS_OUTPUT.PUT_LINE('WHENEVER SQLERROR CONTINUE');
DBMS_OUTPUT.PUT_LINE('SET ECHO OFF;');
DBMS_OUTPUT.PUT_LINE('PRINT signature');
DBMS_OUTPUT.PUT_LINE('PRO');
DBMS_OUTPUT.PUT_LINE('PRO ... manual custom SQL Profile has been created');
DBMS_OUTPUT.PUT_LINE('PRO');
DBMS_OUTPUT.PUT_LINE('SET TERM ON ECHO OFF LIN 80 TRIMS OFF NUMF "";');
DBMS_OUTPUT.PUT_LINE('SPO OFF;');
DBMS_OUTPUT.PUT_LINE('PRO');
DBMS_OUTPUT.PUT_LINE('PRO COE_XFR_SQL_PROFILE_&&sql_id._&&plan_hash_value. completed');
END;
/
SPO OFF;
SET DEF ON TERM ON ECHO OFF FEED 6 VER ON HEA ON LIN 80 PAGES 14 LONG 80 LONGC 80 TRIMS OFF TI OFF TIMI OFF SERVEROUT OFF NUMF "" SQLP SQL>;
SET SERVEROUT OFF;
PRO
PRO Execute coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql
PRO on TARGET system in order to create a custom SQL Profile
PRO with plan &&plan_hash_value linked to adjusted sql_text.
PRO
UNDEFINE 1 2 sql_id plan_hash_value
CL COL
PRO
PRO COE_XFR_SQL_PROFILE completed.

Alley_Li

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
使用SQL Profile稳定SQL语句的执行计划

SQL> @C:\Users\kzvz914\Desktop\AFG\coe_xfr_sql_profile.SQLParameter 1:SQL_ID (required)Enter value for 1: bbp1a0d9xkaggPLAN_HASH_VALUE AVG_ET_SECS--------------- ----------- 3403
复制链接

扫一扫