forcing a hinted plan on a non-hinted statement
- From: Job Miller <jobmiller@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 15 Mar 2010 07:57:09 -0700 (PDT)
I saw this and thought it might be helpful to someone.
If you have a statement that you can hint to a correct plan, and you want that
hinted plan to be the correct plan all the time for the non-hinted statement,
use SPM in 11g to associate a good plan (from the hinted execution) with a
particular non-hinted SQL statement.
1. Run the non-hinted SQL producing the bad plan
2. Find the SQL_ID
3. Create a SQL Plan Baseline for the statment
variable cnt number;
execute :cnt:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'...');
4. Disabling that plan.. requires sql_hanlde and plan_name:
select sql_handle, sql_text, plan_name, enabled from dba_sql_plan_baselines;
5. Alter SQL Plan Base to disabled:
variable cnt number;
exec :cnt:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
SQL_HANDLE=> :sql_handle,
PLAN_NAME=>:plan_name
ATTRIBUTE_NAME=>'enabled'
ATTRIBUTE_VALUE=>'NO');
6. Modify SQL Statement using hints and execute it.
7. Fin new SQL_ID and PLAN_HASH_VALUE
select SQL_ID, SQL_FULLTEXT from v$sql where SQL_TEXT like
'%YOUR_HINTED_STATEMENT%'
8. create new accepted plan for original SQL stmt by associating the modified
plan to the original statement's SQL Handle
dbms_spm.load_plans_from_cursor_cache(sql_id=>:sql_id,plan_hash_value=>:plan_hash,sql_handle=>
:sql_handle);
9. verify hinted plan is in baseline and enabled
select sql_handle, sql_text, plan_name, enabled from dba_sql_plan_baselines;
Good luck..
--
//www.freelists.org/webpage/oracle-l
Other related posts:
