SQL Profile - Play with Execution plan

Although i don't have too much experience working with outlines, but these days i am working significantly with SQL Profile. My Favourite blogger Kerry osborne has written a wonderfull blog on
this topic which gives me very clear idea of sql profile.

Outlines basically locks the profile , but sql profile plays with emperical value of the sql plan which normaly known as fudge factore. It's give the flexibility to optimizter to look for alternate plan.
But we can say sql profile advantages will fade with time.

Hints used in sql profile, these hinsts are very likely documents so wanted to give as much information as possible:

1. Setting Optimer Mode : ALL_ROWS

2. Disable hints present in sql : IGNORE_OPTIM_EMBEDDED_HINTS

3.Setting OPTIMIZER_FEATURES_ENABLE to it's default Value i.e active all available feature :OPTIMIZER_FEATURES_ENABLE (default)

4.Adjusting number of rows returned from the table:
OPT_ESTIMATE(@"SEL$1", TABLE, "L"@"SEL$1", SCALE_ROWS=10)
 Eg : 10 times as many rows are expected to return

5. Adjusting number of rows returned from index scan:
OPT_ESTIMATE(@"SEL$1", INDEX_SKIP_SCAN, "A"@"SEL$1", PK_TABLE_XXXX_ASSIGNMENT, SCALE_ROWS=.4)
Eg: 4 times fewer rows will be returned using index skip scan.

6. Table, index and column statistics adusted using below hints:
COLUMN_STATS("APP_OWNER"."TABLE_XXXX_ASSIGNMENT", "COL3", scale, length=12 distinct=2 nulls=0)
TABLE_STATS("APP_OWNER"."TABLE_XXXX_ASSIGNMENT", scale, blocks=5 rows=2400)
OPTIMIZER_FEATURES_ENABLE(default)
Below are some scripts to find out hints from a sql profile and awr snapshots ( scripts are borrowed from kerry blogs )

SQL> set echo on
SQL> @sql_profile_hints
set lines 155
col hint for a150
select attr_val hint
from dba_sql_profiles p, sqlprof$attr h
where p.signature = h.signature
and name like ('&profile_name')
order by attr#
 /
Enter value for profile_name: PROFILE_0qa98gcnnza7h

HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
OPT_ESTIMATE(@"SEL$1", TABLE, "L"@"SEL$1", SCALE_ROWS=0.0536172171)
OPT_ESTIMATE(@"SEL$1", INDEX_SKIP_SCAN, "A"@"SEL$1", PK_TABLE_XXXX_ASSIGNMENT, SCALE_ROWS=4)
COLUMN_STATS("APP_OWNER"."TABLE_XXXX_ASSIGNMENT", "COL1", scale, length=6 distinct=1234 nulls=0 min=1000000014 max=1026369632)
COLUMN_STATS("APP_OWNER"."TABLE_XXXX_ASSIGNMENT", "COL2", scale, length=12 distinct=2 nulls=0)
COLUMN_STATS("APP_OWNER"."TABLE_XXXX_ASSIGNMENT", "COL3", scale, length=12 distinct=2 nulls=0)
TABLE_STATS("APP_OWNER"."TABLE_XXXX_ASSIGNMENT", scale, blocks=5 rows=2400)
OPTIMIZER_FEATURES_ENABLE(default)

SQL> @sql_hints_awr
 select
 extractvalue(value(d), '/hint') as outline_hints
 from
 xmltable('/*/outline_data/hint'
 passing (
 select
 xmltype(other_xml) as xmlval
 from
 dba_hist_sql_plan
where
sql_id = '&sql_id'
and plan_hash_value = &plan_hash_value
and other_xml is not null
)
) d;
Enter value for sql_id: 0qa98gcnnza7h
Enter value for plan_hash_value: 568322376

OUTLINE_HINTS
-----------------------------------------------------------------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "SKEW"@"SEL$1")

Scripts to create sql profile ( Kerry osborne blog reference ) :

create_sql_profile.sql – uses cursor from the shared pool
create_sql_profile_awr.sql – uses AWR tables
sql_profile_hints.sql – shows the hints in a SQL Profile for 10g


Basic Commands:
DBMS_SQLTUNE.ACCEPT_SQL_PROFILE
DBMS_SQLTUNE.ALTER_SQL_PROFILE
DBMS_SQLTUNE.DROP_SQL_PROFILE


set verify off exec dbms_sqltune.accept_sql_profile(task_name => '&task_name',category => '&category');




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

Reference Links :

http://kerryosborne.oracle-guy.com/2009/04/oracle-sql-profiles/