One simple example about Oracle SPM

The profiles are a new (relatively) word in a database systems. Profiles of load on the file system, of a historical sessions behaviour, of SQL … Finally, the baseline or Oracle SQL Plan Management is a new (again relatively :) ) instrument for working with explain plan of queries during the lifetime of the application that can manage the choose among  explain plans and keep track of their evolutions.

There are a lot of technical surveys about it:

  1. Documentation http://docs.oracle.com/cd/E11882_01/server.112/e23633/preup.htm#UPGRD00232
  2. Maria Kolgan https://blogs.oracle.com/optimizer/entry/sql_plan_management_part_1_of_4_creating_sql_plan_baselines
  3. Johathan Lewis http://jonathanlewis.wordpress.com/2011/01/12/fake-baselines/
  4. NoCOUG 2012, №8 SQL Plan Management for Performance Testing
  5. http://www.oracle-base.com/articles/11g/sql-plan-management-11gr1.php
  6. http://coskan.wordpress.com/2012/04/11/when-dbms_xplan-display_sql_plan_baseline-fails-to-show-the-plan

Let’s consider the principles of SPM on one simple example.

What is the main difference between SPM and outlines? The SPM does not keep neither an explain plan nor whatever optimizer hints. Doubtless it does not need. The environment can change and optimizer hints won’t work or will work not as expected.

SPM keeps the explain plan unique ID i.e. in fact it is objective function. And now it is possible to specify not only FIRST_ROWS or ALL_ROWS as a goal of optimization but also the explain plan ID that we would like to get.

Suppose we have a query (1):

SELECT EMP.ENAME
FROM SCOTT.EMP
WHERE EMP.DEPTNO <> ALL
(SELECT DEPTNO FROM SCOTT.DEPT WHERE LOC='DALLAS')

with hash anti-join in the explain plan

SQL_ID  6gcraaf1apfws, child number 0
Plan hash value: 1543991079
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     7 (100)|          |
|*  1 |  HASH JOIN ANTI    |      |     5 |   100 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   126 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| DEPT |     3 |    33 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

And we would like to use nested loop instead of hash anti-join. So I added a couple of hints into the query (2):

SELECT /*+ USE_NL(DEPT@Q1 EMP@SEL$1) */EMP.ENAME
FROM SCOTT.EMP
WHERE EMP.DEPTNO <> ALL
(SELECT /*+ QB_NAME(Q1) */ DEPTNO FROM SCOTT.DEPT WHERE LOC='DALLAS')

SQL_ID  000q6z5d9z48h, child number 0
Plan hash value: 2649839948
----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |       |       |    17 (100)|          |
|   1 |  NESTED LOOPS ANTI           |         |     5 |   100 |    17   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL          | EMP     |    14 |   126 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     2 |    22 |     1   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)|          |
----------------------------------------------------------------------------------------

It is easy to create a baseline with appropriate explain plan for query (2). But what if we would do same for the query (1)? Let’s use the “fake baseline”:

declare
 i number;
 n clob;
begin
 select sql_text into n from v$sql where sql_id = '6gcraaf1apfws' and child_number = 0;
 i:= DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
     sql_id => '000q6z5d9z48h', 
     plan_hash_value => 2649839948, 
     sql_text => n , 
     fixed => 'YES');
end; 

Sometimes it’s enough. But can see for yourself it does not work for query (1).

We can use event 10053 for investigation. But before let’s see on the prospective explain plan of baseline:

SELECT *
FROM TABLE(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'SQL_PLAN_d4jm9zdn0kydz9b8c4c13'));


--------------------------------------------------------------------------------
SQL handle: SQL_d24669fb680979bf
SQL text: SELECT EMP.ENAME FROM SCOTT.EMP WHERE EMP.DEPTNO  ALL (SELECT DEPTNO
          FROM SCOTT.DEPT WHERE LOC='DALLAS')
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_d4jm9zdn0kydz9b8c4c13         Plan id: 2609662995
Enabled: YES     Fixed: YES     Accepted: YES     Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
 
Plan hash value: 2809975276
----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     9 |    81 |     6   (0)| 00:00:01 |
|*  1 |  FILTER                      |         |       |       |            |          |
|   2 |   TABLE ACCESS FULL          | EMP     |    14 |   126 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    11 |     1   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

As we can see the plan is a little bit different as we expected (FILTER operation instead of nested loop). There is no mistake. Let’s see the SPM section in a 10053 trace file:

SPM: planId in plan baseline = 2609662995, planId of reproduced plan = 1763180032
------- START SPM Plan Dump -------
SPM: failed to reproduce the plan using the following info:
  parse_schema name        : SYS
  plan_baseline signature  : 15151914524837771711
  plan_baseline plan_id    : 2609662995
  plan_baseline hintset    :
    hint num  1 len 27 text: IGNORE_OPTIM_EMBEDDED_HINTS
    hint num  2 len 37 text: OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
    hint num  3 len 22 text: DB_VERSION('11.2.0.3')
    hint num  4 len  8 text: ALL_ROWS
    hint num  5 len 29 text: OUTLINE_LEAF(@"SEL$AF79D2C9")
    hint num  6 len 13 text: UNNEST(@"Q1")
    hint num  7 len 17 text: OUTLINE(@"SEL$1")
    hint num  8 len 14 text: OUTLINE(@"Q1")
    hint num  9 len 35 text: FULL(@"SEL$AF79D2C9" "EMP"@"SEL$1")
    hint num 10 len 59 text: INDEX_RS_ASC(@"SEL$AF79D2C9" "DEPT"@"Q1" ("DEPT"."DEPTNO"))
    hint num 11 len 50 text: LEADING(@"SEL$AF79D2C9" "EMP"@"SEL$1" "DEPT"@"Q1")
    hint num 12 len 35 text: USE_NL(@"SEL$AF79D2C9" "DEPT"@"Q1")
SPM: generated non-matching plan:
----- Explain Plan Dump -----
----- Plan Table -----

What has happened? The optimizer on a best plan choose stage determined that there was a SPM for sql_id. Based on the set of hints from SPM profile, the optimizer tried to reproduce the required plan and could not do it. The same we could see when we tried to view explain plan of SPM with help of DBMS_XPLAN package. It was impossible to say on that moment, what would the explain plan be (It had been known only plan ID). Reliably known that it was impossible to get explain plan with these ID in a current environment. So it’s been chosen the plan with best cost.

The root of this kind of behaviour is optimizer heuristics. The some of operations have never been considered. We were able to get nested loop operation in query (2) with help of hints. But when we tried to force use these plan for query (1) we failed because that plan have never been reviewed in normal situation.

Another reason of not using the SPM:
1. mismatch sql_id. sql_id changes after any modifications of query. It happens if do not use bind variables, change the sort order or add additional columns to a select list.
2. Has changed the list of database objects. For example was added or deleted one of the indexes.
3. Has changed the type of database objects. For example, one of the columns has changed datatype, one of the bind variables has changed datatype or one of the columns became not null.
4. Has changed the optimizer environment. For example, query transformation group-by placement was disabled

SPM does not depend on statistical information (good news :) )

P.S. What does DBMS_XPLAN view in that case? Explain plan which has been got by help of SPM profile hints.

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s