The last post showed how to lock the plan for a SQL that switched plan every now and then. Another common issue one can encounter is that of a SQL that uses a suboptimal plan and for which you’d want it to use a different plan. The way to instruct the optimiser to use a different plan is to use a hint to cause it to change how it accesses the table.

But often the SQL is in application code you may not be able to change right away or even in a packaged application where you have no access to the source code. For those situations it would be convenient to just inject a hint into the SQLand have it take effect anytime the original SQL is executed.

We’ll use SQL Plan Management (SPM) to achieve that too. We’ll first load the plan we don’t want to use and set it to no enabled and then we’ll add a hinted SQL as enabled that we want to use.

We start with the same setup as last time. That setup works to prove this case also

For this to work there is just one parameter we need to have set. optimizer_use_sql_plan_baselines needs to be set to true. It has been the default since SPM was introduced in version 11.

You can verify the setting in your database with:

sho parameter optimizer_use_sql_plan_baselines

Let’s set up a table and add some data + an index.

create table a ( id   number(10)    not null
               , text varchar2(200) not null);

declare
  type ti is table of pls_integer index by pls_integer;
  ai ti := ti();
  type tv is table of varchar2(200) index by pls_integer;
  av tv := tv();
begin
  for i in 1..10000 loop
    ai(i) := i;
    av(i) := dbms_random.string(opt=>'p', len => 200);
  end loop;
  forall i in 1..10000
    insert into a (id, text) values (ai(i), av(i));
  commit;
end;
/
create index b on a (id);

With 10000 rows we’ll have enough for our test case. With this setup we’re bound to get an index lookup.

var a number;
exec :a := trunc(dbms_random.value(1,10000));
select * from a where id = :a;

SQL_ID  846t2s6wt24bw, child number 0                                                
Plan hash value: 1556772684                                                          
-------------------------------------------------------------                        
| Id  | Operation                           | Name | E-Rows |                        
-------------------------------------------------------------                        
|   0 | SELECT STATEMENT                    |      |        |                        
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| A    |      1 |                        
|*  2 |   INDEX RANGE SCAN                  | B    |      1 |                        
------------------------------------------------------------- 

For this case we assume that what we want is to not use the index. We want a full scan. All we want to prove is to add a hint and make it take effect. If it actually is better is not relevant.

So far so good. Now let’s capture this plan for SPM. For this we need SQL_ID. Turn autotrace on (set auto trace on) in SQLcl and you get it directly after the SQL.

This time we combine the loading of the plan with the setting of it to not be enabled.

var cnt number;
exec :cnt := dbms_spm.load_plans_from_cursor_cache( sql_id => '846t2s6wt24bw' -
                                                  , enabled => 'No')

We can now see the plan we have captured.

select sql_handle, plan_name, enabled from dba_sql_plan_baselines;

SQL_HANDLE              PLAN_NAME                         ENABLED    
SQL_4dba70cebd906150    SQL_PLAN_4vfmhtuyt0sah760b7ebb    NO         

Our next step is to execute a hinted version of the SQL and then load that as an enabled SQL.

First execute the SQL with a hint.

exec :a := trunc(dbms_random.value(1,10000));
select /*+ full(a) */ * from a where id = :a;

SQL_ID  fkztgs54b40h3, child number 0
Plan hash value: 2248738933
-------------------------------------------
| Id  | Operation         | Name | E-Rows |
-------------------------------------------
|   0 | SELECT STATEMENT  |      |        |
|*  1 |  TABLE ACCESS FULL| A    |      1 |
-------------------------------------------

That is it, we have the SQL running with a full scan. Exactly what we wanted and exactly how that hint should perform. But now we want the original SQL to use this plan.

Let’s load the sql with the new sql_id and the plan_hash_value it got to the sql_handle we have from the original loaded SQL and set it to be enabled. Enabled has a default of YES, so that part is just to be more declarative in our intent.

exec :cnt := dbms_spm.load_plans_from_cursor_cache - 
           ( sql_id          => 'fkztgs54b40h3' -
           , plan_hash_value => 2248738933 -
           , sql_handle      => 'SQL_4dba70cebd906150' -
           , enabled         => 'YES');

Net’s first check that we got one more plan loaded to our SQL Handle.

select sql_handle, plan_name, enabled from dba_sql_plan_baselines;

SQL_HANDLE              PLAN_NAME                         ENABLED    
_______________________ _________________________________ __________ 
SQL_4dba70cebd906150    SQL_PLAN_4vfmhtuyt0sah60236d92    YES        
SQL_4dba70cebd906150    SQL_PLAN_4vfmhtuyt0sah760b7ebb    NO       

Great, we added another plan and it is enabled. Time to take it out for a spin. We run the original version of the SQL – the one without the hint – and if it works we’ll see it use a full table scan.

exec :a := trunc(dbms_random.value(1,10000));
select * from a where id = :a;

-------------------------------------------
| Id  | Operation         | Name | E-Rows |
-------------------------------------------
|   0 | SELECT STATEMENT  |      |        |
|*  1 |  TABLE ACCESS FULL| A    |      1 |
-------------------------------------------

Fantastic, we have managed to change the plan used for a SQL that we’re unable to modify the SQL for. With this we can in a matter of minutes adjust then plan used for a SQL Not that the auto trace now shows this happening.

   - SQL plan baseline SQL_PLAN_4vfmhtuyt0sah60236d92 used for this statement        

This is of course usable for any kind of SQL. I’m just using a very simple one to show the mechanics and not be bogged down in the intricacies of a complex SQLs many possible execution plans.

We’ve still just scratched the surface of what is possible with SPM. But when it comes to manually controlling SQL plans the last post and this will probably cover at least 90% of the issues you need to deal with.

All we have left is to remove the plans so there are no remnants after the test of SPM.

exec :cnt := dbms_spm.drop_sql_plan_baseline -
           ( sql_handle => 'SQL_4dba70cebd906150');
select sql_handle, plan_name, enabled from dba_sql_plan_baselines;

Since both plans had the same handle, both were dropped by only providing the handle in the drop. The SQL proves it by not returning any rows.

This is a technique that often is very useful when you have SQL that uses a plan that is not performing as good as the alternative. If the SQL in question cannot be modifies due to release windows or because it is not in code your control, being able to adjust the plan used is very efficient.