SQL Tuning with SQL Plan Management

SQL Tuning with SQL Plan Management

 ------------------------------------------------------------------------

[oracle@localhost ~]$ sqlplus pmuser/oracle

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jun 12 09:48:13 2014

Copyright (c) 1982, 2013, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

PDB1@ORCL> create table t as select * from dba_objects;

Table created.

PDB1@ORCL> exec DBMS_STATS.GATHER_SCHEMA_STATS ('PMUSER');

PL/SQL procedure successfully completed.

Step 1: Verify that OPTIMIZER_USE_SQL_BLAN_BASELINES is set to true (the default)

PDB1@ORCL> show parameter baselines




NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE

step 2: enable auto capture for this session, run a statement twice and disable auto capture again.

PDB1@ORCL> ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;

Session altered.

PDB1@ORCL> variable var42 varchar2(42);
PDB1@ORCL> exec :var42 := 'PMUSER';

PL/SQL procedure successfully completed.

PDB1@ORCL> select count(*) from t where owner= :var42;

COUNT(*)
----------
      5

PDB1@ORCL> select count(*) from t where owner= :var42;

COUNT(*)
----------
      5

PDB1@ORCL> ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE;

Session altered.

Now we should have a baseline for this sql:

PDB1@ORCL> set linesize 300
PDB1@ORCL> column sql_handle format a20
PDB1@ORCL> column plan_name format a42
PDB1@ORCL> column sql_text format a42
PDB1@ORCL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;

SQL_HANDLE           PLAN_NAME                       SQL_TEXT                                   ENA ACC FIX
-------------------- ------------------------------  ------------------------------------------ --- --- ---
SQL_abdfaaa7e926cf0a SQL_PLAN_arrxanznkdmsa3fdbb376  select count(*) from t where owner= :var42 YES YES NO 

Notice how there is one baseline for this statement and it is automatically set to ACCEPTED. Now we create an index, re-run the query with auto capture enabled to collect a new baseline with the index scan.

PDB1@ORCL> create index t_idx on t (owner);

Index created.

PDB1@ORCL> exec dbms_stats.gather_schema_stats ('PMUSER');

PL/SQL procedure successfully completed.

PDB1@ORCL> alter system flush shared_pool;

System altered.

PDB1@ORCL> ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;

Session altered.

PDB1@ORCL> select count(*) from t where owner= :var42;

COUNT(*)
----------
      5

PDB1@ORCL> select count(*) from t where owner= :var42;

COUNT(*)
----------
      5

PDB1@ORCL> ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE;

Session altered.

Check the plan that is used to execute the query and notice how the explain plan mentions the use of a baseline:

PDB1@ORCL> set pagesize 1000
PDB1@ORCL> select count(*) from t where owner = :var42;

COUNT(*)
----------
      5

PDB1@ORCL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 364z0straymuv, child number 0
-------------------------------------
select count(*) from t where owner = :var42

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id | Operation        | Name | Rows | Bytes | Cost (%CPU)| Time         |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT  |      |      |       | 426 (100)  |              |
| 1 | SORT AGGREGATE    |      | 1    | 6     |            |              |
|* 2 | TABLE ACCESS FULL| T    | 5    | 30    | 426 (1)    | 00:00:01     |
---------------------------------------------------------------------------

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

2 - filter("OWNER"=:VAR42)

Note
-----
- SQL plan baseline SQL_PLAN_arrxanznkdmsa3fdbb376 used for this statement


23 rows selected.

Check the baselines table for the newly created but unaccepted baseline. Notice how the header mentions if the plan is accepted or not.

PDB1@ORCL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;

SQL_HANDLE           PLAN_NAME                       SQL_TEXT                                   ENA ACC FIX
-------------------- ------------------------------  ------------------------------------------ --- --- ---
SQL_abdfaaa7e926cf0a SQL_PLAN_arrxanznkdmsa3fdbb376  select count(*) from t where owner= :var42 YES YES NO
SQL_abdfaaa7e926cf0a SQL_PLAN_arrxanznkdmsaded8ae2f  select count(*) from t where owner= :var42 YES NO  NO

Have a look at both execution plans for this sql handle:

PDB1@ORCL> select * from table(dbms_xplan.display_sql_plan_baseline('SQL_abdfaaa7e926cf0a'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
SQL handle: SQL_abdfaaa7e926cf0a
SQL text: select count(*) from t where owner= :var42
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_arrxanznkdmsa3fdbb376 Plan id: 1071362934
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
Plan rows: From dictionary
--------------------------------------------------------------------------------

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id | Operation         | Name     | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT   |          | 1    | 6     | 426 (1)    | 00:00:01 |
| 1 | SORT AGGREGATE     |          | 1    | 6     |            |          |
|* 2 | TABLE ACCESS FULL | T        | 3143 | 18858 | 426 (1)    | 00:00:01 |
---------------------------------------------------------------------------

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

2 - filter("OWNER"=:VAR42)

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_arrxanznkdmsaded8ae2f Plan id: 3738742319
Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE
Plan rows: From dictionary
--------------------------------------------------------------------------------

Plan hash value: 293504097

---------------------------------------------------------------------------
| Id | Operation       | Name     | Rows | Bytes | Cost (%CPU)| Time      |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT |          | 1    | 6     | 1 (0)      | 00:00:01  |
| 1 | SORT AGGREGATE   |          | 1    | 6     |            |           |
|* 2| INDEX RANGE SCAN | T_IDX    | 5    | 30    | 1 (0)      | 00:00:01  |
---------------------------------------------------------------------------

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

2 - access("OWNER"=:VAR42)

47 rows selected.

Create the evolve report but do not actually change the ACCEPTED flag yet by setting commit=>no like this:

  set serveroutput on
  declare evolve_out CLOB;
  begin
  evolve_out := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE ( SQL_HANDLE => 'SQL_abdfaaa7e926cf0a', COMMIT => 'NO' );
  dbms_output.put_line(evolve_out);
  end; 
/

This is what the report should look like:

GENERAL INFORMATION SECTION
---------------------------------------------------------------------------------------------

Task Information:
---------------------------------------------
Task Name : TASK_21
Task Owner : PMUSER

Execution Name : EXEC_131
Execution Type : SPM EVOLVE
Scope : COMPREHENSIVE
Status : COMPLETED
Started : 07/03/2014 10:03:15
Finished : 07/03/2014 10:03:16
Last Updated :
07/03/2014 10:03:16
Global Time Limit : 2147483646
Per-Plan Time Limit : UNUSED
Number of Errors : 0
---------------------------------------------------------------------------------------------

SUMMARY
SECTION
---------------------------------------------------------------------------------------------
Number of plans processed : 1
Number of findings : 1
Number of recommendations : 1
Number of errors : 0

---------------------------------------------------------------------------------------------

DETAILS SECTION
---------------------------------------------------------------------------------------------
Object ID : 2
Test Plan
Name : SQL_PLAN_arrxanznkdmsaded8ae2f
Base Plan Name : SQL_PLAN_arrxanznkdmsa3fdbb376
SQL Handle : SQL_abdfaaa7e926cf0a
Parsing Schema : PMUSER

Test Plan Creator : PMUSER
SQL Text : select count(*) from t where owner= :var42

Bind Variables:
-----------------------------
1 - (VARCHAR2(128)): PMUSER


Execution Statistics:
-----------------------------
                     Base Plan                    Test Plan
                     ---------------------------- ----------------------------
Elapsed Time (s):    .00099                       .000002
CPU Time (s):        .000489                       0
Buffer Gets:         153                           0
Optimizer Cost:      426                           1
Disk Reads:          0                             0

Direct Writes:       0                             0
Rows Processed:      0                             0
Executions:          10                            10


FINDINGS
SECTION
---------------------------------------------------------------------------------------------

Findings (1):
-----------------------------
1. The plan was verified in 0.29000 seconds. It passed the benefit criterion
because its verified performance was 767.74440 times better than that of
the baseline plan.

Recommendation:
-----------------------------
Consider accepting the plan.


EXPLAIN PLANS
SECTION
---------------------------------------------------------------------------------------------

Baseline Plan
-----------------------------
Plan Id :         1
Plan Hash Value : 1071362934

----------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost | Time         |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT   |      | 1    | 6     | 426  | 00:00:01     |
| 1 | SORT AGGREGATE     |      | 1    | 6     |      |              |
| * 2| TABLE ACCESS FULL | T    | 5    | 30    | 426  | 00:00:01     |
----------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - filter("OWNER"=:VAR42)


Test Plan
-----------------------------
Plan Id : 2
Plan
Hash Value : 3738742319

----------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes  | Cost | Time       |
----------------------------------------------------------------------
| 0  | SELECT STATEMENT   |       | 1   |  6     | 1    | 00:00:01   |
| 1 | SORT AGGREGATE      |       | 1   |  6     |      |            |
| * 2 | INDEX RANGE SCAN  | T_IDX | 5   | 30     | 1    | 00:00:01   |
----------------------------------------------------------------------

Predicate Information (identified by operation
id):
------------------------------------------
* 2 - access("OWNER"=:VAR42)

---------------------------------------------------------------------------------------------

PL/SQL procedure successfully completed.

After the review, run the evolve again but set commit to yes this time to actually evolve the new baseline.

set serveroutput on
declare evolve_out CLOB;
begin
evolve_out := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE ( SQL_HANDLE => 'SQL_abdfaaa7e926cf0a', COMMIT => 'YES' ); 
dbms_output.put_line(evolve_out); 
end; 
/

Check the baseline table again and notice how both plans are now accepted.

PDB1@ORCL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;

SQL_HANDLE            PLAN_NAME                       SQL_TEXT                                    ENA ACC FIX
--------------------  ------------------------------  ------------------------------------------  --- --- ---
SQL_abdfaaa7e926cf0a  SQL_PLAN_arrxanznkdmsa3fdbb376  select count(*) from t where owner= :var42  YES YES NO
SQL_abdfaaa7e926cf0a  SQL_PLAN_arrxanznkdmsaded8ae2f  select count(*) from t where owner= :var42  YES YES NO

Let's verify that this new plan is used for queries from now on:

PDB1@ORCL> set autotrace on
PDB1@ORCL> select count(*) from t where owner = :var42;

COUNT(*)
----------
      5


Execution Plan
----------------------------------------------------------
Plan hash value: 293504097

---------------------------------------------------------------------------
| Id | Operation          | Name  | Rows | Bytes | Cost (%CPU)| Time      |
---------------------------------------------------------------------------
| 0  | SELECT STATEMENT   |       | 1    | 6     | 8 (0)      | 00:00:01  |
| 1  | SORT AGGREGATE     |       | 1    | 6     |            |           |
|* 2 | INDEX RANGE SCAN   |T_IDX  | 3143 | 18858 | 8 (0)      | 00:00:01  |
---------------------------------------------------------------------------

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

2 - access("OWNER"=:VAR42)

Note
-----
- SQL plan baseline "SQL_PLAN_arrxanznkdmsaded8ae2f" used for this statement

The last step would be to think about what to do with the original baseline. Since both plans are accepted now, both are eligible for execution which could be helpful in situations where a adaptive cursor sharing kicks in and the full scan would actually be the better plan. In this case though I decided to delete the old baseline:

 declare 
 drop_result pls_integer;
 begin 
 drop_result := DBMS_SPM.DROP_SQL_PLAN_BASELINE( 
 sql_handle => 'SQL_abdfaaa7e926cf0a',  
 plan_name => 'SQL_PLAN_arrxanznkdmsa3fdbb376'); 
 dbms_output.put_line(drop_result);    
 end; 
/

This showed how to create a SQL Plan Baseline for a query with auto capture. It demonstrated how even after adding an index, only the accepted baseline (with the full scan) was actually being used until we reviewed and evolved the new baseline.