Overview
Starting since 10g people started preferring SQL Profile to pin the plan which seems to be more efficient than the traditional tool - STORED Outline
SQL Profile is more efficient, simple to use, more integrated to AWR.
Personally I feel SQL Profile is more convenient to use and my preferred tool to use as it is very quick and effective.
Methods to use
Method 1 - Below script uses SQL Profile to pin a plan to sqlid provided the plan is either available in memory or in AWR
PROMPT ============================== SQL Profile Inputs ==============================
set serveroutput on
ACCEPT dst_sql_id PROMPT "Enter Destination SQL_ID: "
ACCEPT src_sql_id PROMPT "Enter Source SQL_ID: "
ACCEPT plan_hash_value PROMPT "Enter Source PLAN_HASH_VALUE: "
ACCEPT force_match PROMPT "Enter FORCE_MATCH option (TRUE/FALSE), with TRUE equivalent to CURSOR_SHARING of FORCE: "
PROMPT ================================================================================
DECLARE
c_prof_name_prefix CONSTANT VARCHAR2(5) := 'SQLT';
c_category CONSTANT VARCHAR2(10) := 'DEFAULT';
c_validate CONSTANT BOOLEAN := TRUE;
c_replace CONSTANT BOOLEAN := TRUE;
l_dst_sql_id VARCHAR2(20) := '&&dst_sql_id';
l_src_sql_id VARCHAR2(20) := '&&src_sql_id';
l_plan_hash_value NUMBER := &&plan_hash_value;
l_plan_hash_value_chk NUMBER;
l_force_match_str VARCHAR2(10) := '&&force_match';
l_profile_hints SYS.SQLPROF_ATTR;
l_dst_sql_text CLOB;
l_src_sql_text CLOB;
l_signature NUMBER;
l_force_match BOOLEAN;
l_profile_name VARCHAR2(30) := c_prof_name_prefix || '_' || l_dst_sql_id || '_' || l_plan_hash_value;
l_profile_desc VARCHAR2(50);
err_dst_sql_id EXCEPTION;
err_src_sql_id EXCEPTION;
err_src_plan_hash_value EXCEPTION;
CURSOR sc_dst_sql_text IS
SELECT sql_text
FROM (SELECT sql_fulltext sql_text
FROM gv$sql
WHERE sql_id = l_dst_sql_id
UNION ALL
SELECT sql_text
FROM dba_hist_sqltext
WHERE sql_id = l_dst_sql_id
)
WHERE ROWNUM = 1;
CURSOR sc_src_sql_text IS
SELECT sql_text
FROM (SELECT sql_fulltext sql_text
FROM gv$sql
WHERE sql_id = l_src_sql_id
UNION ALL
SELECT sql_text
FROM dba_hist_sqltext
WHERE sql_id = l_src_sql_id
)
WHERE ROWNUM = 1;
CURSOR sc_src_plan_hash_value IS
SELECT plan_hash_value
FROM gv$sql_plan
WHERE sql_id = l_src_sql_id
AND plan_hash_value = l_plan_hash_value
AND other_xml IS NOT NULL
UNION
SELECT plan_hash_value
FROM dba_hist_sql_plan
WHERE sql_id = l_src_sql_id
AND plan_hash_value = l_plan_hash_value
AND other_xml IS NOT NULL;
CURSOR sc_profile_attr IS
SELECT category
, type
, created
, last_modified
, status
, force_matching
, signature
, description
FROM dba_sql_profiles
WHERE name = l_profile_name;
CURSOR sc_prof_hints IS
SELECT SUBSTR(attr_val, 1, 120) hint
FROM sys.sqlprof$attr
WHERE signature = l_signature
ORDER BY category
, attr#;
BEGIN
OPEN sc_dst_sql_text;
FETCH sc_dst_sql_text INTO l_dst_sql_text;
CLOSE sc_dst_sql_text;
IF l_dst_sql_text IS NULL THEN
RAISE err_dst_sql_id;
END IF;
OPEN sc_src_sql_text;
FETCH sc_src_sql_text INTO l_src_sql_text;
CLOSE sc_src_sql_text;
IF l_src_sql_text IS NULL THEN
RAISE err_src_sql_id;
END IF;
OPEN sc_src_plan_hash_value;
FETCH sc_src_plan_hash_value INTO l_plan_hash_value_chk;
CLOSE sc_src_plan_hash_value;
IF l_plan_hash_value_chk IS NULL THEN
RAISE err_src_plan_hash_value;
END IF;
SELECT EXTRACTVALUE(VALUE(sub1), '/hint') AS outline_hints
BULK COLLECT
INTO l_profile_hints
FROM XMLTABLE('/*/outline_data/hint'
PASSING (SELECT xmltype(other_xml) AS xmlval
FROM (SELECT other_xml
FROM gv$sql_plan
WHERE sql_id = l_src_sql_id
AND plan_hash_value = l_plan_hash_value
AND other_xml IS NOT NULL
UNION ALL
SELECT other_xml
FROM dba_hist_sql_plan
WHERE sql_id = l_src_sql_id
AND plan_hash_value = l_plan_hash_value
AND other_xml IS NOT NULL
)
WHERE ROWNUM = 1
)
) sub1;
IF UPPER(l_force_match_str) = 'TRUE' THEN
l_force_match := TRUE;
ELSE
l_force_match := FALSE;
END IF;
l_signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(l_dst_sql_text);
l_profile_desc := l_profile_name || '_' || l_signature;
DBMS_SQLTUNE.IMPORT_SQL_PROFILE(sql_text => l_dst_sql_text
, profile => l_profile_hints
, name => l_profile_name
, description => l_profile_desc
, category => c_category
, validate => c_validate
, replace => c_replace
, force_match => l_force_match
);
DBMS_OUTPUT.PUT_LINE('SQL Profile ' || l_profile_name || ' successfully created');
DBMS_OUTPUT.PUT_LINE('................................................................................');
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('Destination SQL Text');
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE(l_dst_sql_text);
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('Source SQL Text');
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE(l_src_sql_text);
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('Attributes');
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------------');
FOR x IN sc_profile_attr LOOP
DBMS_OUTPUT.PUT_LINE('Category: ' || x.category);
DBMS_OUTPUT.PUT_LINE('Type: ' || x.type);
DBMS_OUTPUT.PUT_LINE('Created: ' || x.created);
DBMS_OUTPUT.PUT_LINE('Last Modified: ' || x.last_modified);
DBMS_OUTPUT.PUT_LINE('Status: ' || x.status);
DBMS_OUTPUT.PUT_LINE('Force Matching: ' || x.force_matching);
DBMS_OUTPUT.PUT_LINE('Signature: ' || x.signature);
DBMS_OUTPUT.PUT_LINE('Description: ' || x.description);
END LOOP;
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('Hints');
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------------');
FOR x IN sc_prof_hints LOOP
DBMS_OUTPUT.PUT_LINE(x.hint);
END LOOP;
DBMS_OUTPUT.PUT_LINE('================================================================================');
EXCEPTION
WHEN err_dst_sql_id THEN
DBMS_OUTPUT.PUT_LINE('Error: Destination SQL_ID "' || l_dst_sql_id || '" not found.');
WHEN err_src_sql_id THEN
DBMS_OUTPUT.PUT_LINE('Error: Source SQL_ID "' || l_src_sql_id || '" not found.');
WHEN err_src_plan_hash_value THEN
DBMS_OUTPUT.PUT_LINE('Error: Source PLAN_HASH_VALUE "' || l_plan_hash_value || '" not found.');
WHEN OTHERS THEN
RAISE;
END;
/
Method 2 - There is also a backdoor method to create a SQL profile if the plan is not available in either place, which can be done using SQLT, which again uses IMPORT_SQL_PROFILE with manually created SQLPROF_ATTR (i.e. SQL Profile Attribute)
Below example need to properly converted according to your scenario.
SET DEF ^ ECHO ON TERM ON LIN 2000 TRIMS ON SERVEROUT ON SIZE 1000000 TIM OFF SQLP SQL>;
SPO SQLT_2wskdsyt6w2ty_p4150230984.log;
SET ECHO OFF;
PRO
PRO ... Generating manual custom SQL Profile for SQL text provided
PRO
SET TERM OFF HEA ON TIM OFF;
WHENEVER SQLERROR EXIT SQL.SQLCODE;
VAR signature NUMBER;
DECLARE
sql_txt VARCHAR2(32767);
h SYS.SQLPROF_ATTR;
BEGIN
sql_txt := q'[SELECT /*YANTRA*/ YFS_ORDER_HEADER.ORDER_HEADER_KEY FROM YFS_ORDER_HEADER YFS_ORDER_HEADER WHERE PAYMENT_STATUS IN ( :"SYS_B_00",:"SYS_B_01",:"SYS_B_02",:"SYS_B_03",:"SYS_B_04",:"SYS_B_05",:"SYS_B_06",:"SYS_B_07",:"SYS_B_08",:"SYS_B_09",:"SYS_B_10",:"SYS_B_11") AND AUTHORIZATION_EXPIRATION_DATE <= :"SYS_B_12" AND ENTERPRISE_KEY = :"SYS_B_13" AND DOCUMENT_TYPE = :"SYS_B_14" AND NOT EXISTS ( SELECT :"SYS_B_15" FROM YFS_ORDER_HOLD_TYPE WHERE YFS_ORDER_HOLD_TYPE.ORDER_HEADER_KEY = YFS_ORDER_HEADER.ORDER_HEADER_KEY AND HOLD_TYPE IN ( :"SYS_B_16",:"SYS_B_17" ) AND STATUS < :"SYS_B_18") ]';
h := SYS.SQLPROF_ATTR(
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('10.2.0.4')]',
q'[OPT_PARAM('optimizer_dynamic_sampling' 1)]',
q'[OPT_PARAM('optimizer_index_cost_adj' 50)]',
q'[OUTLINE_LEAF(@"SEL$5DA710D3")]',
q'[UNNEST(@"SEL$2")]',
q'[OUTLINE(@"SEL$1")]',
q'[OUTLINE(@"SEL$2")]',
q'[INDEX(@"SEL$5DA710D3"
mailto:%22YFS_ORDER_HEADER%22@%22SEL$1" ("YFS_ORDER_HEADER"."ENTERPRISE_KEY" "YFS_ORDER_HEADER"."DOCUMENT_TYPE" "YFS_ORDER_HEADER"."PAYMENT_STATUS" "YFS_ORDER_HEADER"."AUTHORIZATION_EXPIRATION_DATE" "YFS_ORDER_HEADER"."ORDER_HEADER_KEY"))]',
q'[INDEX_RS_ASC(@"SEL$5DA710D3"
mailto:%22YFS_ORDER_HOLD_TYPE%22@%22SEL$2" ("YFS_ORDER_HOLD_TYPE"."ORDER_HEADER_KEY" "YFS_ORDER_HOLD_TYPE"."ORDER_LINE_KEY" "YFS_ORDER_HOLD_TYPE"."HOLD_TYPE"))]',
q'[LEADING(@"SEL$5DA710D3"
mailto:%22YFS_ORDER_HEADER%22@%22SEL$1"
mailto:%22YFS_ORDER_HOLD_TYPE%22@%22SEL$2%22)]',
q'[USE_NL(@"SEL$5DA710D3"
mailto:%22YFS_ORDER_HOLD_TYPE%22@%22SEL$2%22)]' );
:signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
sql_text => sql_txt,
profile => h,
name => 'SQLT_2wskdsyt6w2ty_p4150230984',
description => 'SQLT 2wskdsyt6w2ty p4150230984'||:signature,
category => 'DEFAULT',
validate => TRUE,
replace => TRUE,
force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
END;
/
