SQL Profile Creation with hints and outlines
SQL Profile
can be manually created using below procedure from SQLT
Using SQLT oracle support provided tool
- Please navigate to below directory location under SQLT folder
cd sqlt/utl- 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.
