GotoDBA Database Development,Database Tuning,Features Using SPM to Control A Single SQL Execution Plan

Using SPM to Control A Single SQL Execution Plan

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):

  1. I rebooted the server and removed the existing SQL baseline
  2. I executed the query with the good plan (FTS in this case)
  3. Then used the 2 SPM functions as shown above (while the
    “bad” plan was never executed)
  4. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Post