Binding Execution Plan Sql_plan_baseline

--because the production environment executes a faster SQL change, version release is more frequent, resulting in a SQL execution plan is not very stable, there are often some performance-looking SQL appears
--For these SQL, we can use Sql_plan_baseline to bind the execution plan so that the execution plan is fixed
--If SQL is best to use bound variables, even if there are no bound variables, determine the value of the field will not change, because it is a binding for sql_id, if the SQL text changes, binding is meaningless

Specific steps:
--1, find the problem SQL, if query SQL execution plan, if have the appropriate execution plan, bind directly
--Query the SQL execution plan for the corresponding Plan_hash_value
SELECT DISTINCT (plan_hash_value) from V$sql_plan t WHERE sql_id = ' 010CV4DVF6SWV ' and child_number= ' 0 '
-A well-bundled execution plan:
Declare
L_PLS number;
Begin
L_pls: = dbms_spm. Load_plans_from_cursor_cache (sql_id = ' 524wzct86gu1d ',
Plan_hash_value = 2554538542,
Enabled = + ' YES ');
End
/

2, if there is no suitable execution plan, it is necessary to analyze and use some hint to make SQL produce a better execution plan.
--SQL that needs to be bound
--oldsql (ID plan_hash_value)
524wzct86gu1d
2554538542

--New SQL
--newsql (id plan_hash_value)
010CV4DVF6SWV
756701203
--Query new execution Plan sql_id
Select * from v$ SQL where sql_text like '%zhruoyu% '-finds

by adding a special character to the hint.

---Create a new sqlid baseline based on Old_sql Id,plan_hash_value
Declare
  L_PLS number;
Begin
  L_PLS: = DBMS_SPM. Load_plans_from_cursor_cache (sql_id    = ' 524wzct86gu1d ',
                                                    Plan_hash_value = 2554538542,
                                                    enabled         = ' NO ');  -note that this is no
end;
/

---Determine the sql_handle of the original execution plan
Select Sql_handle, Plan_name, origin, enabled, Accepted,fixed,creator,optimizer_cost,sql_text
From Dba_sql_plan_baselines where origin = ' manual-load ' ORDER by created DESC

Sql_handle:sql_66108ad9595208fc
Plan_name:sql_plan_6c44av5cp427w65e519aa

---associated with the right execution plan
Declare
L_PLS number;
Begin
L_pls: = Dbms_spm.load_plans_from_cursor_cache (sql_id = ' 010CV4DVF6SWV ',--new_sql_id '
Plan_hash_value = 756701203,--new_plan_hash_value
sql_handle = ' SQL_66108AD9595208FC '--old_handle
);
End
/

---Delete the wrong execution plan
Declare
L_PLS number;
Begin
L_pls: = dbms_spm. Drop_sql_plan_baseline (sql_handle = ' SQL_66108AD9595208FC ',--sql_handle_for_original
plan_name = ' SQL_PLAN_6C44AV5CP427W65E519AA '--sql_plan_name_for_original
);
End
/

--Check it out.
Select Sql_handle, Plan_name, origin, enabled, Accepted,fixed,creator,optimizer_cost,sql_text
From Dba_sql_plan_baselines where origin = ' manual-load ' and sql_handle= ' SQL_66108AD9595208FC '

--Complete

Binding Execution Plan Sql_plan_baseline