SQL Profile Creation with hints and outlines

SQL Profile can be manually created using below procedure from SQLT

Using SQLT oracle support provided tool

 

1. Please navigate to below directory location under SQLT folder

               cd sqlt/utl

1. Run the script “coe_xfr_sql_profile.sql “  by passing SQL_ID and Desired Plan Hash Value as Parameters

             @coe_xfr_sql_profile.sql <sql_id> <plan_hash_value>

         Ex: @coe_xfr_sql_profile.sql gpm6wzuy8yabq 1950795681

 The above script generates a script with name like below. Please run it which will create the SQL Profile 

            @coe_xfr_sql_profile_sql_id_plan_hash_value.sql

              Ex: @coe_xfr_sql_profile_gpm6wzuy8yabq_1950795681.sql

Without SQLT

 You can create the SQL Profile without SQLT by manually pulling the outline data hints from the execution plan and manually passing them to the SQL.

• Getting outline data from explain plan in Memory

set linesize 1000

set pagesize 900

set long 2000000000

select * from table (dbms_xplan.display_cursor('1afgp23tqc7f3',0, 'ALL +outline')); 

The above output should give you all the explain plans for the sql along with outline data below it. Please select the outline data from the desired plan.

• Getting Outline Data from AWR

            set linesize 1000

            set pagesize 900

            set long 2000000000

            select * from table(dbms_xplan.display_awr('6y6kfwf7c23gx8', null,         

            null,'outline peeked_binds'));

The above output should give you all the explain plans for the sql along with outline data below it. Please select the outline data from the desired plan.

      Once you get the outline data manually add the outline hints in the below procedure (at       

             the “outline hints” below)

DECLARE

clsql_text CLOB;

BEGIN

select sql_text into clsql_text from dba_hist_sqltext where sql_id = 'xxxxxx';  

DBMS_SQLTUNE.IMPORT_SQL_PROFILE(

sql_text => clsql_text,

      profile => sqlprof_attr(q'!outline hints!',

      q'!outline hints!'

      q'!outline hints!'),

name => 'desired name',

      force_match => TRUE

);

END;

/

Example:

DECLARE

   clsql_text   CLOB;

BEGIN

   SELECT sql_text

     INTO clsql_text

     FROM dba_hist_sqltext

    WHERE sql_id = '6y6kfwf7c23gx8';

 

   DBMS_SQLTUNE.IMPORT_SQL_PROFILE (

      sql_text      => clsql_text,

      profile       => sqlprof_attr (

                         q'!BEGIN_OUTLINE_DATA!',

                         q'!IGNORE_OPTIM_EMBEDDED_HINTS!',

                         q'!OPTIMIZER_FEATURES_ENABLE('12.1.0.2')!',

                         q'!DB_VERSION('12.1.0.2')!',

                         q'!OPT_PARAM('optimizer_dynamic_sampling' 11)!',

                         q'!FIRST_ROWS!',

                         q'!OUTLINE_LEAF(@"SEL$1")!',                                                 

                         q'!USE_NL(@"SEL$1" "B"@"SEL$1")!',

                         q'!USE_NL(@"SEL$1" "F"@"SEL$1")!',

                         q'!USE_NL(@"SEL$1" "C"@"SEL$1")!',

                         q'!USE_NL(@"SEL$1" "E"@"SEL$1")!',

                         q'!USE_NL(@"SEL$1" "D"@"SEL$1")!',

                         q'!NLJ_BATCHING(@"SEL$1" "D"@"SEL$1")!',

                         q'!END_OUTLINE_DATA!'),

      name          => 'SQLPROFILE_77tusxuddphkn',

      force_match   => TRUE);

END;

/

Dropping the SQL Profile

begin dbms_sqltune.drop_sql_profile('SQLPROFILE_77tusxuddphkn'); end;

Check if SQL is using profile

select slq_id, sql_profile from v$sql;

Note: sql_profile should not be null if the sql is using the profile.