If you are using Oracle 11g database and SQL Plan baselines you can use the SQL Plan even that is optimal if you can’t change the application code.
Your database should have db parameter optimizer_use_sql_plan_baselines = TRUE (Which is default)
- Open SQL Plus session
- If you have db parameter set to optimizer_capture_sql_plan_baselines = FALSE (which is default) – execute following to capture sql plans that would be generated by sql command
alter session Set optimizer_capture_sql_plan_baselines = TRUE;
- Execute the SQL statement (with hint) so it generates and captures the plan by SPM.
- Now session can be disconnected as new plan is generated and stored in the baselines.
- Obtain the SQL_Handle and Plan_name using the below sql statement
select sql_handle, plan_name, sql_text, enabled, accepted, fixed from DBA_SQL_PLAN_BASELINES
where upper(sql_text) like '%<portion of your sql to identify>%'
- Mark the appropriate plan as FIXED using following command
SET SERVEROUTPUT ON
DECLARE
l_plans_altered PLS_INTEGER;
BEGIN
l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
sql_handle => '<sql_handle obtained from above query?',
plan_name => '<Obtain right plan name from above query for the plan you want to use>',
attribute_name => 'fixed',
attribute_value => 'YES');
DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
END;
/
Now this plan is marked as fixed, it will be used for feature executions of this SQL statement.
Leave a comment