SQL Plan Management (SPM) is a powerful tool. It can do many things to control and evolve SQL execution plans to allow the most stable system.
In this scenario I’ll show you a very simple way to use it to control the execution plan for a single SQL statement.
Background
The incentive to do that was a client case where once in a while (we couldn’t figure out when) the optimizer decided to change the plan of a specific SQL. This SQL was executed many many times and normally took about 0.1s to complete. When the problem occurs, the SQL with the new plan took about 5s to complete, and as this was executed thousands of time, you can understand that we had a problem with that.
Conventional Options
Usually in these cases I start a SQL tuning process, check cardinality, etc. etc. In this case I couldn’t find why the optimizer, in some cases, switched from nested loops to merge join cartesian for a result set with 1 row joined with a result set with a few. I did find that it’s related to view merging, but adding a hint (which seems to work) wasn’t an option.
So I turned to a different solution: SPM
SPM
As I said, SPM can do many things, but in this case I wanted a very specific action. With Nigel Bayliss‘ help in this post, I changed the scenario a bit and got what I wanted to achieve.
Test Case
For this we need a single SQL with 2 different plans but the same sql_id (because this is my case). So I decided to run a very simple query, but use the OPTIMIZER_INDEX_COST_ADJ parameter to make the optimizer change the plan.
Creating the objects
SQL> create table spm_test(id number, name varchar2(100));
Table created.
SQL> insert into spm_test
2 select mod(rownum,1000),'name'||rownum
3 from dual connect by level<=100000;
100000 rows created.
SQL> create index spm_idx on spm_test(id);
Index created.
Testing the query
With the default OPTIMIZER_INDEX_COST_ADJ=100:
SQL> select * from spm_test where id=50;
ID NAME
---------- ------------------
50 name19050
50 name88050
50 name91050
......
50 name15050
100 rows selected.
SQL> select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID a3s3uadk2ssdj, child number 0
-------------------------------------
select * from spm_test where id=50
Plan hash value: 505163394
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 86 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| SPM_TEST | 100 | 6500 | 86 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | SPM_IDX | 100 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=50)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
And with the updated OPTIMIZER_INDEX_COST_ADJ=10000:
SQL> alter session set optimizer_index_cost_adj=10000;
Session altered.
SQL> select * from spm_test where id=50;
ID NAME
---------- ----------------------
50 name1050
50 name2050
50 name50
......
50 name97050
100 rows selected.
SQL> select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID a3s3uadk2ssdj, child number 1
-------------------------------------
select * from spm_test where id=50
Plan hash value: 278342638
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 103 (100)| |
|* 1 | TABLE ACCESS FULL| SPM_TEST | 100 | 6500 | 103 (1)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=50)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Getting the plan hash values
SQL> select plan_hash_value,operation,object_name
2 from v$sql_plan
3 where sql_id='a3s3uadk2ssdj'
4 order by plan_hash_value,id
PLAN_HASH_VALUE OPERATION OBJECT_NAME
--------------- ------------------------------ ------------------------------
278342638 SELECT STATEMENT
278342638 TABLE ACCESS SPM_TEST
505163394 SELECT STATEMENT
505163394 TABLE ACCESS SPM_TEST
505163394 INDEX SPM_IDX
Use SPM
What I’m going to do now, in a similar way to what Nigel did in his post, is to disable all plans of this SQL_ID and enable only the “full table scan” one. Then I’ll run the query again with different values of OPTIMIZER_INDEX_COST_ADJ and we’ll see what happens.
The first statement disables the plans for this specific SQL, while the second statement allows the plan that I chose. Note that I used “fixed=>yes” so it will tell SPM that this is the final plan and there is no need to go through the “evolve” process.
SQL> declare
2 rc pls_integer;
3 begin
4 rc:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'a3s3uadk2ssdj',enabled=>'NO');
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> declare
2 rc pls_integer;
3 begin
4 rc:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'a3s3uadk2ssdj',plan_hash_value=>278342638, fixed=>'YES',enabled=>'YES');
5 end;
6 /
PL/SQL procedure successfully completed.
Testing
SQL> alter session set optimizer_index_cost_adj=100;
Session altered.
set pages 2000 lines 200
SQL> select * from spm_test where id=50;
ID NAME
---------- --------------------
50 name1050
50 name2050
50 name50
.....
50 name97050
100 rows selected.
SQL> select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID a3s3uadk2ssdj, child number 5
-------------------------------------
select * from spm_test where id=50
Plan hash value: 278342638
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 103 (100)| |
|* 1 | TABLE ACCESS FULL| SPM_TEST | 100 | 6500 | 103 (1)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=50)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- SQL plan baseline SQL_PLAN_92rbp7fxf3f5kf2fc655a used for this statement
Another Interesting Test
Another question that I had is what happens with other plans or if the “bad” plan is not in memory. So I did the test (I won’t show it here as it is the same as the above but in a different order):
- I rebooted the server and removed the existing SQL baseline
- I executed the query with the good plan (FTS in this case)
- Then used the 2 SPM functions as shown above (while the
“bad” plan was never executed) - What happens if I try to run the query with OPTIMIZER_INDEX_COST_ADJ=100? It uses the baseline!
It seems that even if the “bad” plan is not in memory, the fact that there is a “chosen” plan will cause the optimizer to use it.
Dictionary Views
And lastly, instead of searching this up, the view that contains the information about the SQL plan baselines is: DBA_SQL_PLAN_BASELINES
Conclusion
So, SPM is a great tool for an overall performance stability as well as a very selective fixed to specific SQL statements. In this case I could pick a specific plan and be sure that the optimizer will not choose other plans.
Now I’ll need to get approval to implement this in production and check that it really does fix the issue that we have.