Abdul hafeez kalsekar -- OCS/OCE/OCP: April 2014

In past post you can find how to check different plan for same sql ID . No we can step forward to check
how to force sql plan hash value. Basically till 10g it was done using Sql Profile and sql outlines and from 11g onward to can be done using sql baselines . I am yet to explore outlines but will be posting here steps to force sql using sql profile

There are multiple options to do so

1) Manually creating using dbms_sqltune.import_sql_profile pl/sql ( easiest for me )
2) Manually create using outline hint
3) Manually create sql profile adding hint to sqlprof_attr
4) Manually create using oracle coe script ( you can also get script from sqlt )
5) Create SQL PROFILE in Grid Control :

There are certain case where plan hash value is null

-- when sql is cexecuted inside procedure/plsql

-- when we fetch datra from dblink

Manually creating using dbms_sqltune.import_sql_profile pl/sql

If good plan is not there in memory use dba_hist_sql_plan

declare

pln_sql_id varchar2(20) := 'sqlid';

pln_plan_hash_value number := goodpan;

orig_sql_id varchar2(20) := 'sqlid';

new_prof_name varchar2(20) := 'SP_sqlid'';

ar_profile_hints sys.sqlprof_attr;

cl_sql_text clob;

begin

select extractvalue(value(d), '/hint') as outline_hints

bulk collect into ar_profile_hints

from xmltable('/*/outline_data/hint'

passing (select xmltype(other_xml) as xmlval from v$sql_plan

where sql_id = pln_sql_id and plan_hash_value = pln_plan_hash_value

and other_xml is not null)) d;

select sql_text into cl_sql_text

from v$sql_plan where sql_id = orig_sql_id;

dbms_sqltune.import_sql_profile(

sql_text => cl_sql_text,

profile => ar_profile_hints,

name => new_prof_name,

force_match => true);

end;

declare

pln_sql_id varchar2(20) := 'sqlid';

pln_plan_hash_value number := goodpan;

orig_sql_id varchar2(20) := 'sqlid';

new_prof_name varchar2(20) := 'SP_sqlid'';

ar_profile_hints sys.sqlprof_attr;

cl_sql_text clob;

begin

select extractvalue(value(d), '/hint') as outline_hints

bulk collect into ar_profile_hints

from xmltable('/*/outline_data/hint'

passing (select xmltype(other_xml) as xmlval from dba_hist_sql_plan

where sql_id = pln_sql_id and plan_hash_value = pln_plan_hash_value

and other_xml is not null)) d;

select sql_text into cl_sql_text

from dba_hist_sqltext where sql_id = orig_sql_id;

dbms_sqltune.import_sql_profile(

sql_text => cl_sql_text,

profile => ar_profile_hints,

name => new_prof_name,

force_match => true);

end;

Manually create using outline hint :

explain plan for

select * from table(dbms_xplan.display_cursor(sql_id => '7cm8cz0k1y0zc', cursor_child_info =>0, format=>'OUTLINE'));

add below outline hint in bad sql which is retrieved from good sql above :

Outline
Data

-------------

/*+

BEGIN_OUTLINE_DATA

     
USE_HASH_AGGREGATION(@"SEL$639F1A6F")

     
FULL(@"SEL$639F1A6F" "T_ORDER_OR"@"SEL$2")

     
USE_NL(@"SEL$1" "INNER_VIEW"@"SEL$1")

     
LEADING(@"SEL$1" "T_SUPPLIER_SU"@"SEL$1"
"INNER_VIEW"@"SEL$1")

     
NO_ACCESS(@"SEL$1" "INNER_VIEW"@"SEL$1")

     
FULL(@"SEL$1" "T_SUPPLIER_SU"@"SEL$1")

     
OUTLINE(@"SEL$1")

     
OUTLINE(@"SEL$2")

     
OUTLINE_LEAF(@"SEL$1")

     
PUSH_PRED(@"SEL$1" "INNER_VIEW"@"SEL$1" 1)

     
OUTLINE_LEAF(@"SEL$639F1A6F")

     
ALL_ROWS

     
DB_VERSION('11.2.0.3')

     
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')

     
IGNORE_OPTIM_EMBEDDED_HINTS

     
END_OUTLINE_DATA

*/

The
optimizer hints / context we retrieved here above are used together with the
attribute sqlprof_attr.

DECLARE

    
l_sql              
clob;

BEGIN

     l_sql := q'!select
su_pk,su_name,su_comment,inner_view.maxamount from

          
      t_supplier_su,

             
   ( select max(or_totalamount) maxamount,su_fk from t_order_or group
by su_fk ) inner_view

                
where t_supplier_su.su_pk = inner_view.su_fk(+) and t_supplier_su.su_name is
not null!';

    
dbms_sqltune.import_sql_profile( sql_text => l_sql,

        
                           
name => 'SQLPROFILE_01',

            
                       
profile => sqlprof_attr

(q'!USE_HASH_AGGREGATION(@"SEL$639F1A6F")!',

    
        q'!FULL(@"SEL$639F1A6F"
"T_ORDER_OR"@"SEL$2")!',

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

            
q'!LEADING(@"SEL$1" "T_SUPPLIER_SU"@"SEL$1"
"INNER_VIEW"@"SEL$1")!',

            
q'!NO_ACCESS(@"SEL$1" "INNER_VIEW"@"SEL$1")!',

            
q'!FULL(@"SEL$1" "T_SUPPLIER_SU"@"SEL$1")!',

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

            
q'!OUTLINE(@"SEL$2")!',

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

    
        q'!PUSH_PRED(@"SEL$1"
"INNER_VIEW"@"SEL$1" 1)!',

            
q'!OUTLINE_LEAF(@"SEL$639F1A6F")!',

            
q'!ALL_ROWS!',

            
q'!DB_VERSION('11.2.0.3')!',

            
q'!OPTIMIZER_FEATURES_ENABLE('11.2.0.3')!',

            
q'!IGNORE_OPTIM_EMBEDDED_HINTS!')

,

            
force_match => true );

end;
/

Manually create sql profile adding hint to sqlprof_attr

By query v$sql we found the sql_id, child_number and plan_hash_value also checked there isn't a sql_profile attached to the SQL.

SQL> select sql_id, child_number, sql_profile, plan_hash_value, sql_text from v$sql where sql_id IN ('63cg18v928540', '0tjtg6yqqbbxk');

Displaying the execution plan of the 2nd SQL with the outline option

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('0tjtg6yqqbbxk', 0, 'outline'));

Outline Data

————-

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('11.2.0.1')

DB_VERSION('11.2.0.1')

ALL_ROWS

OUTLINE_LEAF(@”SEL$1″)

FULL(@”SEL$1″ “E”@”SEL$1″)

END_OUTLINE_DATA

From the outline generated used the hint to add to first SQL to change its execution plan i.e. full table scan.

define SQL_ID = '63cg18v928540';

DECLARE

clsql_text CLOB;

BEGIN

SELECT sql_fulltext INTO clsql_text FROM V$sqlarea where sql_id = '&SQL_ID';

dbms_sqltune.import_sql_profile(sql_text => clsql_text,

profile=> sqlprof_attr('FULL(@SEL$1 E@SEL$1)'),

name=>'PROFILE_&SQL_ID',

force_match=>true);

end;

How to add hint when sql id changes :

When there are multiple sqlid in case of bind aware we have below 2 options

Option 1)

Modify your sql generated form COE script to add hint or add hint

to create sql profile statement . Optionally you can add hint to explain plan

statement with outline and use same outline to generate sql profile

DECLARE

l_sql clob;

BEGIN

l_sql := q'!select su_pk,su_name,su_comment,inner_view.maxamount from

t_supplier_su,

( select max(or_totalamount) maxamount,su_fk from t_order_or group by su_fk ) inner_view

where t_supplier_su.su_pk = inner_view.su_fk(+) and t_supplier_su.su_name is not null!';

dbms_sqltune.import_sql_profile( sql_text => l_sql,

name => 'SQLPROFILE_01',

profile => sqlprof_attr(q'!USE_HASH_AGGREGATION(@"SEL$639F1A6F")!',

q'!FULL(@"SEL$639F1A6F" "T_ORDER_OR"@"SEL$2")!',

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

q'!LEADING(@"SEL$1" "T_SUPPLIER_SU"@"SEL$1" "INNER_VIEW"@"SEL$1")!',

q'!NO_ACCESS(@"SEL$1" "INNER_VIEW"@"SEL$1")!',

q'!FULL(@"SEL$1" "T_SUPPLIER_SU"@"SEL$1")!',

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

q'!OUTLINE(@"SEL$2")!',

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

q'!PUSH_PRED(@"SEL$1" "INNER_VIEW"@"SEL$1" 1)!',

q'!OUTLINE_LEAF(@"SEL$639F1A6F")!',

q'!ALL_ROWS!',

q'!DB_VERSION('11.2.0.3')!',

q'!OPTIMIZER_FEATURES_ENABLE('11.2.0.3')!',

q'!IGNORE_OPTIM_EMBEDDED_HINTS!'),

force_match => true );

end;

Option 2 )

This is undocumented method to update sqlobj$ which your client will not allow . First create a sql profile with any available

sql plan . Then update sqlobj$ to add int to sql profile

declare

pln_sql_id varchar2(20) :='4sdd3343222';

pln_plan_hash_value number := 2949544139;

orig_sql_id varchar2(20) := '4sdd3343222';

new_prof_name varchar2(20) := 'SQL_PROFILE_2';

ar_profile_hints sys.sqlprof_attr;

cl_sql_text clob;

begin

select extractvalue(value(d), '/hint') as outline_hints

bulk collect into ar_profile_hints

from xmltable('/*/outline_data/hint'

passing (select xmltype(other_xml) as xmlval from v$sql_plan

where sql_id = pln_sql_id and plan_hash_value = pln_plan_hash_value

and other_xml is not null)) d;

select sql_fulltext into cl_sql_text

from v$sql where sql_id = orig_sql_id;

dbms_sqltune.import_sql_profile(

sql_text => cl_sql_text,

profile => ar_profile_hints,

name => new_prof_name,

force_match => true);

end;

select comp_data from sqlobj$data where signature=(select signature from sqlobj$ where name='SQL_PROFILE_2');

- For 10.2, use the following sql to get profile hints :

-- select attr#, attr_val from sqlprof$attr where signature=(select signature from sqlprof$ where sp_name='SQL_PROFILE_2');

update sqlobj$data set

comp_data='';

1 row updated.

-- For 10.2 use the below update statement :

-- update sqlprof$attr set ATTR_VAL='FULL(@"SEL$1" "EMP"@"SEL$1")

NOPARALLEL(@"SEL$1" "EMP"@"SEL$1")' where attr#=5 and

signature='784334333455334';

commit;

alter system flush shared_pool;

Script to pull the hint associated with a SQL Profile

----------------------------------------------------------------------------------------

-- File name: profile_hints.sql

-- Purpose: Show hints associated with a SQL Profile.

-- Author: Kerry Osborne

-- Usage: This scripts prompts for one value.

-- profile_name: the name of the profile to be modified

-- Description: This script pulls the hints associated with a SQL Profile.

-- Mods: Modified to check for 10g or 11g as the hint structure changed.

-- Modified to join on category as well as signature.

-- See kerryosborne.oracle-guy.com for additional information.

---------------------------------------------------------------------------------------

set sqlblanklines on

set feedback off

accept profile_name -

prompt 'Enter value for profile_name: ' -

default 'X0X0X0X0'

declare

ar_profile_hints sys.sqlprof_attr;

cl_sql_text clob;

version varchar2(3);

l_category varchar2(30);

l_force_matching varchar2(3);

b_force_matching boolean;

begin

select regexp_replace(version,'\..*') into version from v$instance;

if version = '10' then

-- dbms_output.put_line('version: '||version);

execute immediate -- to avoid 942 error

'select attr_val as outline_hints '||

'from dba_sql_profiles p, sqlprof$attr h '||

'where p.signature = h.signature '||

'and p.category = h.category '||

'and name like (''&&profile_name'') '||

'order by attr#'

bulk collect

into ar_profile_hints;

elsif version = '11' then

-- dbms_output.put_line('version: '||version);

execute immediate -- to avoid 942 error

'select hint as outline_hints '||

'from (select p.name, p.signature, p.category, row_number() '||

' over (partition by sd.signature, sd.category order by sd.signature) row_num, '||

' extractValue(value(t), ''/hint'') hint '||

'from sqlobj$data sd, dba_sql_profiles p, '||

' table(xmlsequence(extract(xmltype(sd.comp_data), '||

' ''/outline_data/hint''))) t '||

'where sd.obj_type = 1 '||

'and p.signature = sd.signature '||

'and p.category = sd.category '||

'and p.name like (''&&profile_name'')) '||

'order by row_num'

bulk collect

into ar_profile_hints;

end if;

dbms_output.put_line(' ');

dbms_output.put_line('HINT');

dbms_output.put_line('------------------------------------------------------------------------------------------------------------------------------------------------------');

for i in 1..ar_profile_hints.count loop

dbms_output.put_line(ar_profile_hints(i));

end loop;

dbms_output.put_line(' ');

dbms_output.put_line(ar_profile_hints.count||' rows selected.');

dbms_output.put_line(' ');

end;

undef profile_name

set feedback on

To Alter hint in existing Sql Profile :

----------------------------------------------------------------------------------------

-- File name: fix_sql_profile_hint.sql

-- Purpose: Replaces a hint in a sql profile.

-- Author: Kerry Osborne

-- Usage: This scripts prompts for three values.

-- profile_name: the name of the profile to be modified

-- bad_hint: the hint to be replaced (cut and paste hint from listing

-- using sql_profile_hints.sql)

-- good_hint: the hint to replace the bad_hint

-- Description: This script was written becuase Oracle decided to start using a index

-- hints that don't specifiy the index name. This allows the optimizer a

-- great deal of flexibility, which is not desirable when you are trying

-- "lock" a plan.

-- See kerryosborne.oracle-guy.com for additional information.

-- http://kerryosborne.oracle-guy.com/2009/10/13/how-to-attach-a-sql-profile-to-a-different-statement-take-2/

---------------------------------------------------------------------------------------

-- WARNING: don't use this script if you don't know what you're doing!

accept profile_name -

prompt 'Enter value for profile_name: ' -

default 'X0X0X0X0'

accept bad_hint -

prompt 'Enter value for bad_hint: ' -

default '&%$&%$X0X0X0X0!.*&$5#'

accept good_hint -

prompt 'Enter value for good_hint: ' -

default ' '

declare

ar_profile_hints sys.sqlprof_attr;

cl_sql_text clob;

version varchar2(3);

l_category varchar2(30);

l_force_matching varchar2(3);

b_force_matching boolean;

begin

select regexp_replace(version,'\..*') into version from v$instance;

if version = '10' then

dbms_output.put_line('version: '||version);

execute immediate -- to avoid 942 error

'select replace(attr_val,''&&bad_hint'',''&&good_hint'') as outline_hints '||

'from dba_sql_profiles p, sqlprof$attr h '||

'where p.signature = h.signature '||

'and name like (''&&profile_name'') '||

'order by attr#'

bulk collect

into ar_profile_hints;

elsif version = '11' then

dbms_output.put_line('version: '||version);

execute immediate -- to avoid 942 error

'select replace(hint,''&&bad_hint'',''&&good_hint'') as outline_hints '||

'from (select p.name, p.signature, p.category, row_number() '||

' over (partition by sd.signature, sd.category order by sd.signature) row_num, '||

' extractValue(value(t), ''/hint'') hint '||

'from sqlobj$data sd, dba_sql_profiles p, '||

' table(xmlsequence(extract(xmltype(sd.comp_data), '||

' ''/outline_data/hint''))) t '||

'where sd.obj_type = 1 '||

'and p.signature = sd.signature '||

'and p.name like (''&&profile_name'')) '||

'order by row_num'

bulk collect

into ar_profile_hints;

end if;

select

sql_text, category, force_matching

into

cl_sql_text, l_category, l_force_matching

from

dba_sql_profiles

where name like ('&&profile_name');

if l_force_matching = 'YES' then

b_force_matching := TRUE;

else

b_force_matching := FALSE;

end if;

dbms_sqltune.import_sql_profile(

sql_text => cl_sql_text

, profile => ar_profile_hints

, name => '&&profile_name'

, description => 'Warning: hints modified by fix_sql_profile_hint.sql'

, category => l_category

, force_match => b_force_matching

, replace => TRUE

, validate => TRUE

);

end;

undef profile_name

undef bad_hint

undef good_hint

To Copy Access path/hint from one sqlid to another sqlid ( will create sqlid on new sqlid)

-- http://kerryosborne.oracle-guy.com/2009/07/28/how-to-attach-a-sql-profile-to-a-different-statement/

accept sql_id_from -

prompt 'Enter value for sql_id to generate profile from: ' -

default 'X0X0X0X0'

accept child_no_from -

prompt 'Enter value for child_no to generate profile from: '

accept sql_id_to -

prompt 'Enter value for sql_id to attach profile to: ' -

default 'X0X0X0X0'

accept child_no_to -

prompt 'Enter value for child_no to attach profile to: '

accept category -

prompt 'Enter value for category: ' -

default 'DEFAULT'

accept force_matching -

prompt 'Enter value for force_matching: ' -

default 'false'

@rg_sqlprof3 '&sql_id_from' &child_no_from '&sql_id_to' &child_no_to '&category' '&force_matching'

undef sql_id_from

undef child_no_from

undef sql_id_to

undef child_no_to

undef category

undef force_matching

-- rg_sqlprof3.sql

/* Randolf Giest */

-- creates a sql profile from shared pool

-- sql_id_from child_no_from sql_id_to child_no_to category force_matching

declare

ar_profile_hints sys.sqlprof_attr;

cl_sql_text clob;

begin

select

extractvalue(value(d), '/hint') as outline_hints

bulk collect

into

ar_profile_hints

from

xmltable('/*/outline_data/hint'

passing (

select

xmltype(other_xml) as xmlval

from

v$sql_plan

where

sql_id = '&&1'

and child_number = &&2

and other_xml is not null

)

) d;

select

sql_fulltext

into

cl_sql_text

from

v$sql

where

sql_id = '&&3'

and child_number = &&4;

dbms_sqltune.import_sql_profile(

sql_text => cl_sql_text

, profile => ar_profile_hints

, category => '&&5'

, name => 'PROFILE_'||'&&3'||'_attach'

-- use force_match => true

-- to use CURSOR_SHARING=SIMILAR

-- behaviour, i.e. match even with

-- differing literals

, force_match => &&6

);

end;

To Copy Sql profile to another Sqlid :

----------------------------------------------------------------------------------------

-- File name: move_sql_profile.sql

-- Purpose: Moves a SQL Profile from one statement to another.

-- Author: Kerry Osborne

-- Usage: This scripts prompts for four values.

-- profile_name: the name of the profile to be attached to a new statement

-- sql_id: the sql_id of the statement to attach the profile to

-- category: the category to assign to the new profile

-- force_macthing: a toggle to turn on or off the force_matching feature

-- Description: This script is based on a script originally written by Randolf Giest.

-- It's purpose is to allow a statements text to be manipulated in whatever

-- manner necessary (typically with hints) to get the desired plan. Then

-- once a SQL Profile has been created on the new statement, it's SQL Profile

-- can be moved (or attached) to the orignal statement with unmodified text.

-- Mods: This script should now work wirh all flavors of 10g and 11g.

-- See kerryosborne.oracle-guy.com for additional information.

-----------------------------------------------------------------------------------------

accept profile_name -

prompt 'Enter value for profile_name: ' -

default 'X0X0X0X0'

accept sql_id -

prompt 'Enter value for sql_id: ' -

default 'X0X0X0X0'

accept category -

prompt 'Enter value for category (DEFAULT): ' -

default 'DEFAULT'

accept force_matching -

prompt 'Enter value for force_matching (false): ' -

default 'false'

----------------------------------------------------------------------------------------

-- File name: profile_hints.sql

---------------------------------------------------------------------------------------

set sqlblanklines on

declare

ar_profile_hints sys.sqlprof_attr;

cl_sql_text clob;

version varchar2(3);

l_category varchar2(30);

l_force_matching varchar2(3);

b_force_matching boolean;

begin

select regexp_replace(version,'\..*') into version from v$instance;

if version = '10' then

-- dbms_output.put_line('version: '||version);

execute immediate -- to avoid 942 error

'select attr_val as outline_hints '||

'from dba_sql_profiles p, sqlprof$attr h '||

'where p.signature = h.signature '||

'and name like (''&&profile_name'') '||

'order by attr#'

bulk collect

into ar_profile_hints;

elsif version = '11' then

-- dbms_output.put_line('version: '||version);

execute immediate -- to avoid 942 error

'select hint as outline_hints '||

'from (select p.name, p.signature, p.category, row_number() '||

' over (partition by sd.signature, sd.category order by sd.signature) row_num, '||

' extractValue(value(t), ''/hint'') hint '||

'from sqlobj$data sd, dba_sql_profiles p, '||

' table(xmlsequence(extract(xmltype(sd.comp_data), '||

' ''/outline_data/hint''))) t '||

'where sd.obj_type = 1 '||

'and p.signature = sd.signature '||

'and p.name like (''&&profile_name'')) '||

'order by row_num'

bulk collect

into ar_profile_hints;

end if;

select

sql_fulltext

into

cl_sql_text

from

v$sqlarea

where

sql_id = '&&sql_id';

dbms_sqltune.import_sql_profile(

sql_text => cl_sql_text

, profile => ar_profile_hints

, category => '&&category'

, name => 'PROFILE_'||'&&sql_id'||'_moved'

-- use force_match => true

-- to use CURSOR_SHARING=SIMILAR

-- behaviour, i.e. match even with

-- differing literals

, force_match => &&force_matching

);

end;

undef profile_name

undef sql_id

undef category

undef force_matching

DROP OR DISABLE SQL PROFILE:

EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE (name => '&profile_name', attribute_name => 'STATUS', value => 'DISABLED');

BEGIN

DBMS_SQLTUNE.drop_sql_profile (

name => '&profile_name',

ignore => TRUE);

END;

EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE (name => '&profile_name', attribute_name => 'STATUS', value => 'ENABLED');

Manually create using oracle coe script ( you can also get script from sqlt )

Make sql file using below script eg. profile.sql . When you will run profile.sql it will ask for 2 parameter.

It will automatically create sql profile to use good plan . It is not my own made, but extracted from sqlt . a you need to later run coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql generated by below COE script

sqlid :

plan hash value : --> need to pass hash value of good plan

Sometimes coe profile script might raise ORA-06502 as documented in 2043600.1 . For that we are asked to get latest sql profile script from Document 215187.1 . If with new script also we are facing issues then we need to check if we have sqlt already installed in database , if yes same issue can be faced due to version mismatch of sqlt installed and sqlprofile coe script used and we might have to re-install sqlt or use coe sqpfile script from same binary that is used to install sqlt .

Encouraging CBO to Pickup a Better Execution Plan Using the COE XFR SQL Profile Script (Doc ID 1955195.1)

Same script exists under utils directory for sqlt

SPO coe_xfr_sql_profile.log;

SET DEF ON TERM OFF ECHO ON FEED OFF VER OFF HEA ON LIN 2000 PAGES 100 LONG 8000000 LONGC 800000 TRIMS ON TI OFF TIMI OFF SERVEROUT ON SIZE 1000000 NUMF "" SQLP SQL>;

SET SERVEROUT ON SIZE UNL;

REM

REM $Header: 215187.1 coe_xfr_sql_profile.sql 11.4.5.5 2013/03/01 carlos.sierra $

REM

REM AUTHOR

REM carlos.sierra@oracle.com

REM

REM SCRIPT

REM coe_xfr_sql_profile.sql

REM

REM DESCRIPTION

REM This script generates another that contains the commands to

REM create a manual custom SQL Profile out of a known plan from

REM memory or AWR. The manual custom profile can be implemented

REM into the same SOURCE system where the plan was retrieved,

REM or into another similar TARGET system that has same schema

REM objects referenced by the SQL that generated the known plan.

REM

REM PRE-REQUISITES

REM 1. Oracle Tuning Pack license.

REM

REM PARAMETERS

REM 1. SQL_ID (required)

REM 2. Plan Hash Value for which a manual custom SQL Profile is

REM needed (required). A list of known plans is presented.

REM You may choose from list provided or enter a valid phv

REM from a version of the SQL modified with Hints.

REM

REM EXECUTION

REM 1. Connect into SQL*Plus as user with access to data dictionary.

REM Do not use SYS.

REM 2. Execute script coe_xfr_sql_profile.sql passing SQL_ID and

REM plan hash value (parameters can be passed inline or until

REM requested).

REM

REM EXAMPLE

REM # sqlplus system

REM SQL> START coe_xfr_sql_profile.sql [SQL_ID] [PLAN_HASH_VALUE];

REM SQL> START coe_xfr_sql_profile.sql gnjy0mn4y9pbm 2055843663;

REM SQL> START coe_xfr_sql_profile.sql gnjy0mn4y9pbm;

REM SQL> START coe_xfr_sql_profile.sql;

REM

REM NOTES

REM 1. For possible errors see coe_xfr_sql_profile.log

REM 2. If SQLT is installed in SOURCE, you can use instead:

REM sqlt/utl/sqltprofile.sql

REM 3. Be aware that using DBMS_SQLTUNE requires a license for

REM Oracle Tuning Pack.

REM 4. Use a DBA user but not SYS.

REM 5. If you get "ORA-06532: Subscript outside of limit, ORA-06512: at line 1"

REM Then you may consider this change (only in a test and disposable system):

REM create or replace TYPE sys.sqlprof_attr AS VARRAY(5000) of VARCHAR2(500);

REM

SET TERM ON ECHO OFF;

PRO

PRO Parameter 1:

PRO SQL_ID (required)

PRO

DEF sql_id = '&1';

PRO

WITH

p AS (

SELECT plan_hash_value

FROM gv$sql_plan

WHERE sql_id = TRIM('&&sql_id.')

AND other_xml IS NOT NULL

UNION

SELECT plan_hash_value

FROM dba_hist_sql_plan

WHERE sql_id = TRIM('&&sql_id.')

AND other_xml IS NOT NULL ),

m AS (

SELECT plan_hash_value,

SUM(elapsed_time)/SUM(executions) avg_et_secs

FROM gv$sql

WHERE sql_id = TRIM('&&sql_id.')

AND executions > 0

GROUP BY

plan_hash_value ),

a AS (

SELECT plan_hash_value,

SUM(elapsed_time_total)/SUM(executions_total) avg_et_secs

FROM dba_hist_sqlstat

WHERE sql_id = TRIM('&&sql_id.')

AND executions_total > 0

GROUP BY

plan_hash_value )

SELECT p.plan_hash_value,

ROUND(NVL(m.avg_et_secs, a.avg_et_secs)/1e6, 3) avg_et_secs

FROM p, m, a

WHERE p.plan_hash_value = m.plan_hash_value(+)

AND p.plan_hash_value = a.plan_hash_value(+)

ORDER BY

avg_et_secs NULLS LAST;

PRO

PRO Parameter 2:

PRO PLAN_HASH_VALUE (required)

PRO

DEF plan_hash_value = '&2';

PRO

PRO Values passed to coe_xfr_sql_profile:

PRO ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

PRO SQL_ID : "&&sql_id."

PRO PLAN_HASH_VALUE: "&&plan_hash_value."

PRO

SET TERM OFF ECHO ON;

WHENEVER SQLERROR EXIT SQL.SQLCODE;

-- trim parameters

COL sql_id NEW_V sql_id FOR A30;

COL plan_hash_value NEW_V plan_hash_value FOR A30;

SELECT TRIM('&&sql_id.') sql_id, TRIM('&&plan_hash_value.') plan_hash_value FROM DUAL;

VAR sql_text CLOB;

VAR sql_text2 CLOB;

VAR other_xml CLOB;

EXEC :sql_text := NULL;

EXEC :sql_text2 := NULL;

EXEC :other_xml := NULL;

-- get sql_text from memory

DECLARE

l_sql_text VARCHAR2(32767);

BEGIN -- 10g see bug 5017909

FOR i IN (SELECT DISTINCT piece, sql_text

FROM gv$sqltext_with_newlines

WHERE sql_id = TRIM('&&sql_id.')

ORDER BY 1, 2)

LOOP

IF :sql_text IS NULL THEN

DBMS_LOB.CREATETEMPORARY(:sql_text, TRUE);

DBMS_LOB.OPEN(:sql_text, DBMS_LOB.LOB_READWRITE);

END IF;

-- removes NUL characters

l_sql_text := REPLACE(i.sql_text, CHR(00), ' ');

-- adds a NUL character at the end of each line

DBMS_LOB.WRITEAPPEND(:sql_text, LENGTH(l_sql_text) + 1, l_sql_text||CHR(00));

END LOOP;

-- if found in memory then sql_text is not null

IF :sql_text IS NOT NULL THEN

DBMS_LOB.CLOSE(:sql_text);

END IF;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('getting sql_text from memory: '||SQLERRM);

:sql_text := NULL;

END;

SELECT :sql_text FROM DUAL;

-- get sql_text from awr

DECLARE

l_sql_text VARCHAR2(32767);

l_clob_size NUMBER;

l_offset NUMBER;

BEGIN

IF :sql_text IS NULL OR NVL(DBMS_LOB.GETLENGTH(:sql_text), 0) = 0 THEN

SELECT sql_text

INTO :sql_text2

FROM dba_hist_sqltext

WHERE sql_id = TRIM('&&sql_id.')

AND sql_text IS NOT NULL

AND ROWNUM = 1;

END IF;

-- if found in awr then sql_text2 is not null

IF :sql_text2 IS NOT NULL THEN

l_clob_size := NVL(DBMS_LOB.GETLENGTH(:sql_text2), 0);

l_offset := 1;

DBMS_LOB.CREATETEMPORARY(:sql_text, TRUE);

DBMS_LOB.OPEN(:sql_text, DBMS_LOB.LOB_READWRITE);

-- store in clob as 64 character pieces plus a NUL character at the end of each piece

WHILE l_offset < l_clob_size

LOOP

IF l_clob_size - l_offset > 64 THEN

l_sql_text := REPLACE(DBMS_LOB.SUBSTR(:sql_text2, 64, l_offset), CHR(00), ' ');

ELSE -- last piece

l_sql_text := REPLACE(DBMS_LOB.SUBSTR(:sql_text2, l_clob_size - l_offset + 1, l_offset), CHR(00), ' ');

END IF;

DBMS_LOB.WRITEAPPEND(:sql_text, LENGTH(l_sql_text) + 1, l_sql_text||CHR(00));

l_offset := l_offset + 64;

END LOOP;

DBMS_LOB.CLOSE(:sql_text);

END IF;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('getting sql_text from awr: '||SQLERRM);

:sql_text := NULL;

END;

SELECT :sql_text2 FROM DUAL;

SELECT :sql_text FROM DUAL;

-- validate sql_text

SET TERM ON;

BEGIN

IF :sql_text IS NULL THEN

RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');

END IF;

END;

SET TERM OFF;

-- get other_xml from memory

BEGIN

FOR i IN (SELECT other_xml

FROM gv$sql_plan

WHERE sql_id = TRIM('&&sql_id.')

AND plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.'))

AND other_xml IS NOT NULL

ORDER BY

child_number, id)

LOOP

:other_xml := i.other_xml;

EXIT; -- 1st

END LOOP;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('getting other_xml from memory: '||SQLERRM);

:other_xml := NULL;

END;

-- get other_xml from awr

BEGIN

IF :other_xml IS NULL OR NVL(DBMS_LOB.GETLENGTH(:other_xml), 0) = 0 THEN

FOR i IN (SELECT other_xml

FROM dba_hist_sql_plan

WHERE sql_id = TRIM('&&sql_id.')

AND plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.'))

AND other_xml IS NOT NULL

ORDER BY

id)

LOOP

:other_xml := i.other_xml;

EXIT; -- 1st

END LOOP;

END IF;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('getting other_xml from awr: '||SQLERRM);

:other_xml := NULL;

END;

-- get other_xml from memory from modified SQL

BEGIN

IF :other_xml IS NULL OR NVL(DBMS_LOB.GETLENGTH(:other_xml), 0) = 0 THEN

FOR i IN (SELECT other_xml

FROM gv$sql_plan

WHERE plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.'))

AND other_xml IS NOT NULL

ORDER BY

child_number, id)

LOOP

:other_xml := i.other_xml;

EXIT; -- 1st

END LOOP;

END IF;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('getting other_xml from memory: '||SQLERRM);

:other_xml := NULL;

END;

-- get other_xml from awr from modified SQL

BEGIN

IF :other_xml IS NULL OR NVL(DBMS_LOB.GETLENGTH(:other_xml), 0) = 0 THEN

FOR i IN (SELECT other_xml

FROM dba_hist_sql_plan

WHERE plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.'))

AND other_xml IS NOT NULL

ORDER BY

id)

LOOP

:other_xml := i.other_xml;

EXIT; -- 1st

END LOOP;

END IF;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('getting other_xml from awr: '||SQLERRM);

:other_xml := NULL;

END;

SELECT :other_xml FROM DUAL;

-- validate other_xml

SET TERM ON;

BEGIN

IF :other_xml IS NULL THEN

RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');

END IF;

END;

SET TERM OFF;

-- generates script that creates sql profile in target system:

SET ECHO OFF;

PRO coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql.

SET FEED OFF LIN 666 TRIMS ON TI OFF TIMI OFF SERVEROUT ON SIZE 1000000 FOR WOR;

SET SERVEROUT ON SIZE UNL FOR WOR;

SPO OFF;

SPO coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql;

DECLARE

l_pos NUMBER;

l_clob_size NUMBER;

l_offset NUMBER;

l_sql_text VARCHAR2(32767);

l_len NUMBER;

l_hint VARCHAR2(32767);

BEGIN

DBMS_OUTPUT.PUT_LINE('SPO coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..log;');

DBMS_OUTPUT.PUT_LINE('SET ECHO ON TERM ON LIN 2000 TRIMS ON NUMF 99999999999999999999;');

DBMS_OUTPUT.PUT_LINE('REM');

DBMS_OUTPUT.PUT_LINE('REM $Header: 215187.1 coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql 11.4.4.4 '||TO_CHAR(SYSDATE, 'YYYY/MM/DD')||' carlos.sierra $');

DBMS_OUTPUT.PUT_LINE('REM');

DBMS_OUTPUT.PUT_LINE('REM AUTHOR');

DBMS_OUTPUT.PUT_LINE('REM carlos.sierra@oracle.com');

DBMS_OUTPUT.PUT_LINE('REM');

DBMS_OUTPUT.PUT_LINE('REM SCRIPT');

DBMS_OUTPUT.PUT_LINE('REM coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql');

DBMS_OUTPUT.PUT_LINE('REM');

DBMS_OUTPUT.PUT_LINE('REM DESCRIPTION');

DBMS_OUTPUT.PUT_LINE('REM This script is generated by coe_xfr_sql_profile.sql');

DBMS_OUTPUT.PUT_LINE('REM It contains the SQL*Plus commands to create a custom');

DBMS_OUTPUT.PUT_LINE('REM SQL Profile for SQL_ID &&sql_id. based on plan hash');

DBMS_OUTPUT.PUT_LINE('REM value &&plan_hash_value..');

DBMS_OUTPUT.PUT_LINE('REM The custom SQL Profile to be created by this script');

DBMS_OUTPUT.PUT_LINE('REM will affect plans for SQL commands with signature');

DBMS_OUTPUT.PUT_LINE('REM matching the one for SQL Text below.');

DBMS_OUTPUT.PUT_LINE('REM Review SQL Text and adjust accordingly.');

DBMS_OUTPUT.PUT_LINE('REM');

DBMS_OUTPUT.PUT_LINE('REM PARAMETERS');

DBMS_OUTPUT.PUT_LINE('REM None.');

DBMS_OUTPUT.PUT_LINE('REM');

DBMS_OUTPUT.PUT_LINE('REM EXAMPLE');

DBMS_OUTPUT.PUT_LINE('REM SQL> START coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql;');

DBMS_OUTPUT.PUT_LINE('REM');

DBMS_OUTPUT.PUT_LINE('REM NOTES');

DBMS_OUTPUT.PUT_LINE('REM 1. Should be run as SYSTEM or SYSDBA.');

DBMS_OUTPUT.PUT_LINE('REM 2. User must have CREATE ANY SQL PROFILE privilege.');

DBMS_OUTPUT.PUT_LINE('REM 3. SOURCE and TARGET systems can be the same or similar.');

DBMS_OUTPUT.PUT_LINE('REM 4. To drop this custom SQL Profile after it has been created:');

DBMS_OUTPUT.PUT_LINE('REM EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(''coe_&&sql_id._&&plan_hash_value.'');');

DBMS_OUTPUT.PUT_LINE('REM 5. Be aware that using DBMS_SQLTUNE requires a license');

DBMS_OUTPUT.PUT_LINE('REM for the Oracle Tuning Pack.');

DBMS_OUTPUT.PUT_LINE('REM 6. If you modified a SQL putting Hints in order to produce a desired');

DBMS_OUTPUT.PUT_LINE('REM Plan, you can remove the artifical Hints from SQL Text pieces below.');

DBMS_OUTPUT.PUT_LINE('REM By doing so you can create a custom SQL Profile for the original');

DBMS_OUTPUT.PUT_LINE('REM SQL but with the Plan captured from the modified SQL (with Hints).');

DBMS_OUTPUT.PUT_LINE('REM');

DBMS_OUTPUT.PUT_LINE('WHENEVER SQLERROR EXIT SQL.SQLCODE;');

DBMS_OUTPUT.PUT_LINE('REM');

DBMS_OUTPUT.PUT_LINE('VAR signature NUMBER;');

DBMS_OUTPUT.PUT_LINE('VAR signaturef NUMBER;');

DBMS_OUTPUT.PUT_LINE('REM');

DBMS_OUTPUT.PUT_LINE('DECLARE');

DBMS_OUTPUT.PUT_LINE('sql_txt CLOB;');

DBMS_OUTPUT.PUT_LINE('h SYS.SQLPROF_ATTR;');

DBMS_OUTPUT.PUT_LINE('PROCEDURE wa (p_line IN VARCHAR2) IS');

DBMS_OUTPUT.PUT_LINE('BEGIN');

DBMS_OUTPUT.PUT_LINE('DBMS_LOB.WRITEAPPEND(sql_txt, LENGTH(p_line), p_line);');

DBMS_OUTPUT.PUT_LINE('END wa;');

DBMS_OUTPUT.PUT_LINE('BEGIN');

DBMS_OUTPUT.PUT_LINE('DBMS_LOB.CREATETEMPORARY(sql_txt, TRUE);');

DBMS_OUTPUT.PUT_LINE('DBMS_LOB.OPEN(sql_txt, DBMS_LOB.LOB_READWRITE);');

DBMS_OUTPUT.PUT_LINE('-- SQL Text pieces below do not have to be of same length.');

DBMS_OUTPUT.PUT_LINE('-- So if you edit SQL Text (i.e. removing temporary Hints),');

DBMS_OUTPUT.PUT_LINE('-- there is no need to edit or re-align unmodified pieces.');

l_clob_size := NVL(DBMS_LOB.GETLENGTH(:sql_text), 0);

l_offset := 1;

WHILE l_offset < l_clob_size

LOOP

l_pos := DBMS_LOB.INSTR(:sql_text, CHR(00), l_offset);

IF l_pos > 0 THEN

l_len := l_pos - l_offset;

ELSE -- last piece

l_len := l_clob_size - l_pos + 1;

END IF;

l_sql_text := DBMS_LOB.SUBSTR(:sql_text, l_len, l_offset);

/* cannot do such 3 replacement since a line could end with a comment using "--"

l_sql_text := REPLACE(l_sql_text, CHR(10), ' '); -- replace LF with SP

l_sql_text := REPLACE(l_sql_text, CHR(13), ' '); -- replace CR with SP

l_sql_text := REPLACE(l_sql_text, CHR(09), ' '); -- replace TAB with SP

l_offset := l_offset + l_len + 1;

IF l_len > 0 THEN

IF INSTR(l_sql_text, '''[') + INSTR(l_sql_text, ']''') = 0 THEN

l_sql_text := '['||l_sql_text||']';

ELSIF INSTR(l_sql_text, '''{') + INSTR(l_sql_text, '}''') = 0 THEN

l_sql_text := '{'||l_sql_text||'}';

ELSIF INSTR(l_sql_text, '''<') + INSTR(l_sql_text, '>''') = 0 THEN

l_sql_text := '<'||l_sql_text||'>';

ELSIF INSTR(l_sql_text, '''(') + INSTR(l_sql_text, ')''') = 0 THEN

l_sql_text := '('||l_sql_text||')';

ELSIF INSTR(l_sql_text, '''"') + INSTR(l_sql_text, '"''') = 0 THEN

l_sql_text := '"'||l_sql_text||'"';

ELSIF INSTR(l_sql_text, '''|') + INSTR(l_sql_text, '|''') = 0 THEN

l_sql_text := '|'||l_sql_text||'|';

ELSIF INSTR(l_sql_text, '''~') + INSTR(l_sql_text, '~''') = 0 THEN

l_sql_text := '~'||l_sql_text||'~';

ELSIF INSTR(l_sql_text, '''^') + INSTR(l_sql_text, '^''') = 0 THEN

l_sql_text := '^'||l_sql_text||'^';

ELSIF INSTR(l_sql_text, '''@') + INSTR(l_sql_text, '@''') = 0 THEN

l_sql_text := '@'||l_sql_text||'@';

ELSIF INSTR(l_sql_text, '''#') + INSTR(l_sql_text, '#''') = 0 THEN

l_sql_text := '#'||l_sql_text||'#';

ELSIF INSTR(l_sql_text, '''%') + INSTR(l_sql_text, '%''') = 0 THEN

l_sql_text := '%'||l_sql_text||'%';

ELSIF INSTR(l_sql_text, '''$') + INSTR(l_sql_text, '$''') = 0 THEN

l_sql_text := '$'||l_sql_text||'$';

ELSE

l_sql_text := CHR(96)||l_sql_text||CHR(96);

END IF;

DBMS_OUTPUT.PUT_LINE('wa(q'''||l_sql_text||''');');

END IF;

END LOOP;

DBMS_OUTPUT.PUT_LINE('DBMS_LOB.CLOSE(sql_txt);');

DBMS_OUTPUT.PUT_LINE('h := SYS.SQLPROF_ATTR(');

DBMS_OUTPUT.PUT_LINE('q''[BEGIN_OUTLINE_DATA]'',');

FOR i IN (SELECT /*+ opt_param('parallel_execution_enabled', 'false') */

SUBSTR(EXTRACTVALUE(VALUE(d), '/hint'), 1, 4000) hint

FROM TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(:other_xml), '/*/outline_data/hint'))) d)

LOOP

l_hint := i.hint;

WHILE NVL(LENGTH(l_hint), 0) > 0

LOOP

IF LENGTH(l_hint) <= 500 THEN

DBMS_OUTPUT.PUT_LINE('q''['||l_hint||']'',');

l_hint := NULL;

ELSE

l_pos := INSTR(SUBSTR(l_hint, 1, 500), ' ', -1);

DBMS_OUTPUT.PUT_LINE('q''['||SUBSTR(l_hint, 1, l_pos)||']'',');

l_hint := ' '||SUBSTR(l_hint, l_pos);

END IF;

END LOOP;

DBMS_OUTPUT.PUT_LINE('q''[END_OUTLINE_DATA]'');');

DBMS_OUTPUT.PUT_LINE(':signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);');

DBMS_OUTPUT.PUT_LINE(':signaturef := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE);');

DBMS_OUTPUT.PUT_LINE('DBMS_SQLTUNE.IMPORT_SQL_PROFILE (');

DBMS_OUTPUT.PUT_LINE('sql_text => sql_txt,');

DBMS_OUTPUT.PUT_LINE('profile => h,');

DBMS_OUTPUT.PUT_LINE('name => ''coe_&&sql_id._&&plan_hash_value.'',');

DBMS_OUTPUT.PUT_LINE('description => ''coe &&sql_id. &&plan_hash_value. ''||:signature||'' ''||:signaturef||'''',');

DBMS_OUTPUT.PUT_LINE('category => ''DEFAULT'',');

DBMS_OUTPUT.PUT_LINE('validate => TRUE,');

DBMS_OUTPUT.PUT_LINE('replace => TRUE,');

DBMS_OUTPUT.PUT_LINE('force_match => TRUE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );');

DBMS_OUTPUT.PUT_LINE('DBMS_LOB.FREETEMPORARY(sql_txt);');

DBMS_OUTPUT.PUT_LINE('END;');

DBMS_OUTPUT.PUT_LINE('/');

DBMS_OUTPUT.PUT_LINE('WHENEVER SQLERROR CONTINUE');

DBMS_OUTPUT.PUT_LINE('SET ECHO OFF;');

DBMS_OUTPUT.PUT_LINE('PRINT signature');

DBMS_OUTPUT.PUT_LINE('PRINT signaturef');

DBMS_OUTPUT.PUT_LINE('PRO');

DBMS_OUTPUT.PUT_LINE('PRO ... manual custom SQL Profile has been created');

DBMS_OUTPUT.PUT_LINE('PRO');

DBMS_OUTPUT.PUT_LINE('SET TERM ON ECHO OFF LIN 80 TRIMS OFF NUMF "";');

DBMS_OUTPUT.PUT_LINE('SPO OFF;');

DBMS_OUTPUT.PUT_LINE('PRO');

DBMS_OUTPUT.PUT_LINE('PRO COE_XFR_SQL_PROFILE_&&sql_id._&&plan_hash_value. completed');

END;

SPO OFF;

SET DEF ON TERM ON ECHO OFF FEED 6 VER ON HEA ON LIN 80 PAGES 14 LONG 80 LONGC 80 TRIMS OFF TI OFF TIMI OFF SERVEROUT OFF NUMF "" SQLP SQL>;

SET SERVEROUT OFF;

PRO

PRO Execute coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql

PRO on TARGET system in order to create a custom SQL Profile

PRO with plan &&plan_hash_value linked to adjusted sql_text.

PRO

UNDEFINE 1 2 sql_id plan_hash_value

CL COL

PRO

PRO COE_XFR_SQL_PROFILE completed.

Create SQL PROFILE in Grid Control :

1. Login into Grid Control and select the target database

2. Click ‘Performance’ tab to go to Performance page

3. In the Performance page, click ‘Top Activity’

4. Select a problem statement by clicking the ‘SQL ID’

5. Click ‘Schedule SQL Tuning Advisor’

6. In order to get SQL PROFILE recommendation, ‘Comprehensive’ has to be checked. It takes awhile for the tuning process runs.

7. Check the ‘Benefit’ percentage and check ‘Compare Explain Plans’ by clicking the glasses icon. If the SQL PROFILE improves the performance, click ‘Implement’.

8. After implementation, query dba_sql_profiles to check the information:

(select name, SQL_TEXT, LAST_MODIFIED, DESCRIPTION, STATUS from dba_sql_profiles order by 1;)

9. Change SQL PROFILE

a. To disable a SQL profile:

exec dbms_sqltune.alter_sql_profile('', 'STATUS', 'DISABLED');

b. To add description to a SQL profile:

exec DBMS_SQLTUNE.alter_sql_profile('sqlprofile_name','DESCRIPTION','this is a test sql profile');

10. To delete SQL Profile:

exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_0132f8432cbc0000');

Purge Sql ID from shared pool :

Once we are done with pinning good plan we need purge sql from shared pool .

-- select 'exec dbms_shared_pool.purge('''||ADDRESS||','||HASH_VALUE||''',''C'')' from V$SQLAREA where SQL_ID='&sqlid';


-- SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '7yc%';

ADDRESS 	 HASH_VALUE
---------------- ----------
000000085FD77CF0  808321886

SQL> exec DBMS_SHARED_POOL.PURGE ('000000085FD77CF0, 808321886', 'C');

PL/SQL procedure successfully completed.

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '7yc%';

no rows selected

References :

https://docs.oracle.com/database/121/TGSQL/tgsql_profiles.htm#TGSQL610

Abdul hafeez kalsekar -- OCS/OCE/OCP

Thursday, April 24, 2014

Oracle Sql Profile to Fix Sql Plan issue