Anand Blogspot: How to create sql profile

Note that it needs Enterprise edition with Diagnostic and Tuning pack

-- START

-- This script creates a SQL Profile for a statement from a hint

-- Run as SYSTEM

-- Step 1 - Remove and prior SQL PROFILEs for target statement

SELECT NAME,

CATEGORY,

SIGNATURE,

CREATED,

LAST_MODIFIED,

DESCRIPTION,

TYPE,

STATUS,

FORCE_MATCHING,

TASK_ID,

TASK_EXEC_NAME,

TASK_OBJ_ID,

TASK_FND_ID,

TASK_REC_ID,

TO_CHAR (SUBSTR (dsp.sql_text, 1, 4000))

FROM dba_sql_profiles dsp;

BEGIN

DBMS_SQLTUNE.DROP_SQL_PROFILE (name => 'PROFILE_cr11zbr8nbr2v');

END;

-- Step 2 Clear shared pool

ALTER SYSTEM FLUSH SHARED_POOL;

-- Step 3 - Execute the original statement

-- This will need to run from another session as : SCPOMGR

Select ds.dmdunit, ds.dmdGroup, ds.dfuloc, ds.model, ds.Item, ds.skuLoc, ds.eff, ds.disc, ds.fcstType, ds.supersedesw, ds.convfactor, ds.allocfactor from DFUTOSKU ds, processdfu pd, (select item, skuloc from DFUTOSKU ds1, processdfu pd1 where pd1.stage = 0 AND pd1.processID = :1 and ds1.dmdunit = pd1.dmdunit AND ds1.dmdgroup = pd1.dmdgroup AND ds1.dfuloc = pd1.Loc AND ds1.model = pd1.model group by(item, skuloc) having count(*)=1) s where ds.dmdunit = pd.dmdunit AND ds.dmdgroup = pd.dmdgroup AND ds.dfuloc = pd.Loc AND ds.model = pd.model AND pd.stage = 0 AND pd.processID = :2 AND pd.BatchNum = :3 AND ds.item = s.item AND ds.skuloc = s.skuloc; INTERNAL AND CONFIDENTIAL 2015_SHOPRITE_SCPO80_PERFORMANCEINVESTIGATION_20151126 (V1.6) 30

-- Step 4 - Find SQL ID and CHILD NUMBER for the original statement

SELECT sql_id,

child_number,

sql_text,

sql_fulltext

FROM v$sql

WHERE sql_id LIKE '%'

AND UPPER(sql_text) LIKE 'SELECT%DFU%'

AND sql_text NOT LIKE 'SELECT sql_id%' -- this statement

;

-- Step 5 - Create a SQL PROFILE for hinted statement

DECLARE

in_sql_id V$SQL.SQL_ID%TYPE := 'cr11zbr8nbr2v';

in_child_number V$SQL.CHILD_NUMBER%TYPE := '0';

in_profile_hints SYS.sqlprof_attr

:= sqlprof_attr (

'USE_HASH_AGGREGATION(@"SEL$F5BB74E1")',

'USE_HASH(@"SEL$F5BB74E1" "PD1"@"SEL$2")',

'USE_NL(@"SEL$F5BB74E1" "DS1"@"SEL$2")',

'USE_NL(@"SEL$F5BB74E1" "DS"@"SEL$1")'

);

-- use force_match => true to use CURSOR_SHARING=SIMILAR behaviour, i.e. match even with differing literals

in_force_match BOOLEAN := TRUE;

var_sql_text CLOB;

var_profile_name DBA_SQL_PROFILES.NAME%TYPE;

CURSOR cur_profiles

SELECT dsp.NAME, TO_CHAR (SUBSTR (dsp.sql_text, 1, 4000))

FROM dba_sql_profiles dsp

WHERE dsp.name = var_profile_name;

BEGIN

var_profile_name := 'PROFILE_' || in_sql_id;

FOR prof IN cur_profiles

LOOP

DBMS_SQLTUNE.DROP_SQL_PROFILE (name => prof.name);

END LOOP;

SELECT sql_fulltext

INTO var_sql_text

FROM v$sql

WHERE sql_id = in_sql_id AND child_number = in_child_number;

DBMS_OUTPUT.Put_line (

'_________________________________________________________________');

DBMS_OUTPUT.Put_line ('Creating Profile');

DBMS_OUTPUT.Put_line (' SQL ID :' || in_sql_id);

DBMS_OUTPUT.Put_line (' Child Number :' || in_child_number);

DBMS_OUTPUT.Put_line (' SQL TEXT :' || var_sql_text);

DBMS_OUTPUT.Put_line (' Profile Name :' || var_profile_name);

DBMS_SQLTUNE.import_sql_profile (sql_text => var_sql_text,

profile => in_profile_hints, INTERNAL AND CONFIDENTIAL 2015_SHOPRITE_SCPO80_PERFORMANCEINVESTIGATION_20151126 (V1.6) 31

category => 'DEFAULT',

name => var_profile_name -- use force_match => true

-- to use CURSOR_SHARING=SIMILAR

-- behaviour, i.e. match even with

-- differing literals

force_match => in_force_match);