How to pin a desired Plan for a query in Oracle database using SQL Profile

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