SQL Plan Management: SQL Plan Baseline Example

At my site, we are upgrading Oracle Application from 11.5.9 to Rel12.1.3 and many of Oracle's program are performing very poorly. We logged service requests with Oracle support but it was little time consuming to get the fix, so we decided to experiment with sql plan baselines for sql statements with bad plan.

This is what i did to see how sql plan baselines work.

Create table SKM_SQL_PLAN_MGMT_TEST as select * from dba_objects so that we have a table with good number of rows.

Execute “select * from SKM_SQL_PLAN_MGMT_TEST where objecT_name = 'AP_INVOICES_ALL';”. At this moment there is no index on SKM_SQL_PLAN_MGMT_TEST table, so execution plan will use full table scan on the table SKM_SQL_PLAN_MGMT_TEST. This is the plan used by sql

SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  4v8quwv000yrr, child number 0
-------------------------------------
select * from SKM_SQL_PLAN_MGMT_TEST where objecT_name =
'AP_INVOICES_ALL'

Plan hash value: 325872721

--------------------------------------------------------------------------------------------
| Id  | Operation         | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                        |       |       |  1677 (100)|          |
|*  1 |  TABLE ACCESS FULL| SKM_SQL_PLAN_MGMT_TEST |    68 | 14076 |  1677   (1)| 00:00:21 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_NAME"='AP_INVOICES_ALL')

Note
-----
   - dynamic sampling used for this statement (level=2)


23 rows selected.

Run this query to get sql_id for the above sql statement

select sql_id, a.* from gv$sql a where sql_text = 'select * from SKM_SQL_PLAN_MGMT_TEST where objecT_name = ''AP_INVOICES_ALL'''; 

Use the sql_id returned by the above sql and run following script. (Sql_id in this case was 4v8quwv000yrr)

declare
   l_ret_value NUMBER;
begin
   l_ret_value :=DBMS_SPM.load_plans_from_cursor_cache(sql_id=> ‘4v8quwv000yrr’);
end;
/

It will create a base line plan that is enabled and accepted. Detail of this baseline can be seen using this query

SELECT * FROM dba_sql_plan_baselines;

Now create an index on object_name column of SKM_SQL_PLAN_MGMT_TEST

create index SKM_SQL_PLAN_MGMT_TEST_N1 on  db_gx3mn0.SKM_SQL_PLAN_MGMT_TEST (object_name)
/

Now run “select * from SKM_SQL_PLAN_MGMT_TEST where objecT_name = 'AP_INVOICES_ALL';”.

Now check the execution plan used by the above query
set long 20000
set pages 100
set lines 132
select * from table(dbms_xplan.display_cursor());

You will see following output :

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  4v8quwv000yrr, child number 1
-------------------------------------
select * from SKM_SQL_PLAN_MGMT_TEST where objecT_name = 'AP_INVOICES_ALL'

Plan hash value: 325872721

--------------------------------------------------------------------------------------------
| Id  | Operation         | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                        |       |       |  1677 (100)|          |
|*  1 |  TABLE ACCESS FULL| SKM_SQL_PLAN_MGMT_TEST |  5017 |  1014K|  1677   (1)| 00:00:21 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_NAME"='AP_INVOICES_ALL')

Note
-----

- SQL plan baseline SQL_PLAN_068dj65793y565b6edfd3 used for this statement


23 rows selected.


Although index access provides better execution plan but because of baseline defined for sql statement, query uses bad plan. This demonstrates that once you define baseline for sql statement, that baseline plan will get executed even if Oracle optimizer find a supposedly better plan.

Now if you run the query “select * from dba_sql_plan_baselines where to_char(sql_text) = 'select * from SKM_SQL_PLAN_MGMT_TEST where objecT_name = ''AP_INVOICES_ALL'''”, you will find that one more sql plan baseline is added and it has low cost but value of ACCEPTED column is “NO”. so plan is available for review and could marked as accept for future use. To make plan acceptable, do the following

Run sql “select sql_handle, plan_name, a.* from dba_sql_plan_baselines a where to_char(sql_text) = 'select * from SKM_SQL_PLAN_MGMT_TEST where objecT_name = ''AP_INVOICES_ALL''';”

Now drop the sql plan baseline that does full table scan using following script

declare
  l_ret_value NUMBER;
begin
  l_ret_value := DBMS_SPM.DROP_SQL_PLAN_BASELINE
                 ( SQL_HANDLE      => 'SQL_0321b1314e91f8a6'
                 , PLAN_NAME       => 'SQL_PLAN_068dj65793y565b6edfd3'
                 );
end;
/

Now make the better plan that was generated automatically “ACCEPTED” using following script

declare

  l_clob CLOB;
begin
  l_clob      := DBMS_SPM.evolve_sql_plan_baseline
                 ( sql_handle   => 'SQL_0321b1314e91f8a6'
                 , plan_name    => 'SQL_PLAN_068dj65793y56ae570ac6'
                 , time_limit   => 10
                 , verify       => 'YES'
                 , commit       => 'YES'
                 ) ;
end;
/

Now run the query “select * from SKM_SQL_PLAN_MGMT_TEST where objecT_name = 'AP_INVOICES_ALL';”

Now run “select * from table(dbms_xplan.display_cursor());”, you will see following output

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  4v8quwv000yrr, child number 0
-------------------------------------
select * from SKM_SQL_PLAN_MGMT_TEST where objecT_name = 'AP_INVOICES_ALL'

Plan hash value: 3071631708

----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                           |       |       |     5 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| SKM_SQL_PLAN_MGMT_TEST    |     3 |   621 |     5   (0)| 00:00:01
|*  2 |   INDEX RANGE SCAN          | SKM_SQL_PLAN_MGMT_TEST_N1 |     3 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_NAME"='AP_INVOICES_ALL')

Note
-----
   - dynamic sampling used for this statement (level=2)

- SQL plan baseline SQL_PLAN_068dj65793y56ae570ac6 used for this statement


25 rows selected.

Please notice that new sql baseline plan is used for this execution.