A SQL that sometimes just uses a plan you prefer it to not use is fairly common. There are always reasons for unstable plans, but more important than knowing the reason is often to make it stick to a certain plan.
This has historically been done with a lots of ways from changing the SQL, adding hints, adding incorrect stats to influence the optimizer, using any of the techniques that came before SQL Plan Management (SPM). All of these has their issues. The first two required code changes and if it was in a product you cannot modify you could not use them. Using the previous techniques for locking a plan had their issues, hence they were superseded with new ones.
SPM comes with a whole set of APIs one can use to achieve what one want. It has support for full automatic mode to full manual mode. In this blog post I’ll look at just the manual way to set a SQL to use a specific plan.
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_baselinesLet’s set up a table and som data.
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;
/With 10000 rows we’ll have enough for our test case. With this setup we’re bound to get a full table scan.
var a number;
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 | 83 | -------------------------------------------
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. Or use “set auto trace on sql_id” to get the sql_id from every SQL you run in SQLcl. Or pull it by searching for the SQL in v$sql or for your session in v$session. Or any other way you prefer.
var cnt number;
exec :cnt := dbms_spm.load_plans_from_cursor_cache(sql_id => '846t2s6wt24bw');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_4vfmhtuyt0sah60236d92 YES
Now create an index so we can get another plan for the same SQL to let us pick which we want to use.
create index b on a (id);Now let’s run the SQL again and see that we get a plan using the index.
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 | -------------------------------------------
But wait… Full table scan? Didn’t we just create an index? How odd! Not at all, we just proved that SPM does work. Remember the plan we captured? It is enabled and is now making this SQL always use a full scan. Let’s disable it and see if we get index access.
But first look at the output from auto trace, assuming you are using SQLcl. It has a hint of this in the notes section.
- SQL plan baseline SQL_PLAN_4vfmhtuyt0sah60236d92 used for this statement
That is the one we want to disable.
exec :cnt := dbms_spm.alter_sql_plan_baseline -
( sql_handle => 'SQL_4dba70cebd906150' -
, plan_name => 'SQL_PLAN_4vfmhtuyt0sah60236d92' -
, attribute_name => 'enabled' -
, attribute_value => 'NO');Now with that disabled we can try the query again.
select * from a where id = :a;------------------------------------------------------------- | Id | Operation | Name | E-Rows | ------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| A | 1 | |* 2 | INDEX RANGE SCAN | B | 1 | -------------------------------------------------------------
Great, now we get the index to be used as expected and we have a new plan we can capture. Let’s get that plan also.
exec :cnt := dbms_spm.load_plans_from_cursor_cache(sql_id => '846t2s6wt24bw');
select sql_handle, plan_name, enabled from dba_sql_plan_baselines;SQL_HANDLE PLAN_NAME ENABLED _______________________ _________________________________ __________ SQL_4dba70cebd906150 SQL_PLAN_4vfmhtuyt0sah60236d92 NO SQL_4dba70cebd906150 SQL_PLAN_4vfmhtuyt0sah760b7ebb YES
So we have the one with the full scan that we set to not be enabled and the new one that we just captured. A captured plan is by default enabled. Running the select now gets us the same plan using the index we created. There is just one slight difference, it is now using the enabled plan. Autotrace in SQLcl will show this in the notes.
- SQL plan baseline SQL_PLAN_4vfmhtuyt0sah760b7ebb used for this statement
Same thing, it just shows that we have a SQL Plan for the SQL now.
SQLcl is of course not magical, at least not in this sense, the data about the SQL-plan being used is available in Oracle V$-views. If you look up the SQL by sql_id in v$sql, you’ll notice that the column sql_plan_baseline is populated with the name of this SQL-plan.
If we consider the indexed plan undesirable for some reason and the full scan desirable for this particular SQL, we can flip what plan it uses by just changing the SPM-captured plans. To achieve that we need to disable the one that is using the index and enable the one that is doing a full scan. That is, the column enabled needs to be reversed on the plans shown above.
exec :cnt := dbms_spm.alter_sql_plan_baseline -
( sql_handle => 'SQL_4dba70cebd906150' -
, plan_name => 'SQL_PLAN_4vfmhtuyt0sah60236d92' -
, attribute_name => 'enabled' -
, attribute_value => 'YES');
exec :cnt := dbms_spm.alter_sql_plan_baseline -
( sql_handle => 'SQL_4dba70cebd906150' -
, plan_name => 'SQL_PLAN_4vfmhtuyt0sah760b7ebb' -
, attribute_name => 'enabled' -
, attribute_value => 'NO');
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
That looks like expected, will we now get the plan we have defined as the one we want. That is we should get a full scan and a note that SQL_PLAN_dtxx1jqu44h7t60236d92 is being used.
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 | -------------------------------------------
Exactly what we expected and wanted to achieve. We have successfully gotten a different plan picked than the one the optimiser picks without the SPM-setup we have introduced. The note in the explain plan from auto trace in SQLcl shows that it is using the SQL-plan.
- SQL plan baseline SQL_PLAN_4vfmhtuyt0sah60236d92 used for this statement
There are tons more that can be done with SPM, but this concludes the scope of setting up a manual picked plan we want to be used. 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 sometimes uses a plan that is not performing as good as the alternative. There is much more to say about SPM, but this technique is in my experience where most people starts their learning of what can be achieved. Have fun with it and do play around with it to get experience with how to grab both good and bad SQL-plans so you can instruct the optimiser about which plan you prefer it to use.