Execution plan fixing in Oracle databases

In some cases in Oracle databases, queries may run with bad plans due to optimizers not being able to make the right decision. If there is a better plan, fixing may be required for the query to run with this plan. A query’s plan can be fixed by following the steps below. In other words, the plan fix operation can be performed.

First, check that the values ​​of the following parameters in the database are as specified.

OPTIMIZER_USE_SQL_PLAN_BASELINES = TRUE

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE

If the parameters are not like this, change them. Then check the query plans regarding the sql_id information with the query below.

select inst_id,
       address,
       hash_value,
       sql_id,
       plan_hash_value,
       Operation,
       optimizer,
       cost
  from gv$sql_plan
 where id = 0
   and sql_id = 'XXXXXXXXXXX';

Afterwards, let’s load the existing plan of the relevant SQL statement in the cursor cache with the pl/sql block below.

declare
  my_plans pls_integer;
begin
  my_plans := dbms_spm.load_plans_from_cursor_cache(sql_id => 'XXXXXXXXXXX',
                                                        plan_hash_value => 'XXXXX',
                                                       fixed => 'NO');
end;

or

declare
  my_plans pls_integer;
begin
  my_plans := dbms_spm.load_plans_from_cursor_cache(sql_id => 'XXXXXXXXXXX');
end;

Afterwards, let’s determine the sql_handle and plan_name information from the created sql baseline information with the following query.

select * from dba_sql_plan_baselines;

Let’s fix the plan of our query with the sql_handle and plan_name information returned from the query above, using the pl/sql block below.

DECLARATION
  l_plans_altered PLS_INTEGER;
BEGIN
  l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(sql_handle => 'SQL_aaa4f03be3519ae5',
                                                      plan_name => 'SQL_PLAN_ap97h7gjp36r5e4cd1512',
                                                      attribute_name => 'fixed',
                                                      attribute_value => 'YES');
  DBMS_OUTPUT.put_line('Plan fixed : ' || l_plans_altered);
END;
/

You can delete the created sql baseline as follows.

DECLARE l_plans_dropped PLS_INTEGER;
BEGIN
l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (sql_handle => 'SQL_aaa4f03be3519ae5',plan_name => 'SQL_PLAN_ap97h7gjp36r5e4cd1512'); DBMS_OUTPUT.put_line('Plans Dropped: ' || l_plans_dropped);
END; /