Do SQL Plan Baselines Use Hints? – Take 2

Warning – This post is almost pure speculation!

You may have read a previous post where I said I thought Baselines didn’t use hints. We’ll I’ve changed my mind. I no longer think that Baselines store an actual plan for a SQL statement. (See my previous post – Do SQL Plan Baselines Use Hints?) I never could find a place where the plan data was stored (only the plan_hash_value). So I had a closer look at some more 10053 trace files. Here’s a quick run down of what I found. First the set up:

SQL> @flush_pool
 
System altered.
 
SQL> select avg(pk_col) from kso.little_skew where col1 = 1;
 
AVG(PK_COL)
-----------
 310999.357
 
SQL> @find_sql
Enter value for sql_text: %little%
Enter value for sql_id: 
 
SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME      AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
9df852xw86wxt      0 3498336203          1           .18        3,437 select avg(pk_col) from kso.little_skew where col1 = 1
 
SQL> @dplan
Enter value for sql_id: 9df852xw86wxt
Enter value for child_no: 0
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9df852xw86wxt, child number 0
-------------------------------------
select avg(pk_col) from kso.little_skew where col1 = 1
 
Plan hash value: 3498336203
 
----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |       |       |   907 (100)|          |
|   1 |  SORT AGGREGATE    |             |     1 |     9 |            |          |
|*  2 |   TABLE ACCESS FULL| LITTLE_SKEW |   620K|  5449K|   907   (2)| 00:00:11 |
----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("COL1"=1)
 
 
19 rows selected.
 
SQL> select /*+ index (a little_skew_col1) */ avg(pk_col) from kso.little_skew a where col1 = 1;
 
AVG(PK_COL)
-----------
 310999.357
 
SQL> @find_sql
Enter value for sql_text: %little%
Enter value for sql_id: 
 
SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME      AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
9df852xw86wxt      0 3498336203          1           .18        3,437 select avg(pk_col) from kso.little_skew where col1 = 1
fbnx1bws6n4xu      0 2709260180          1           .86        5,278 select /*+ index (a little_skew_col1) */ avg(pk_col) from ks
                                                                      o.little_skew a where col1 = 1
 
 
SQL> @dplan
Enter value for sql_id: fbnx1bws6n4xu
Enter value for child_no: 0
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fbnx1bws6n4xu, child number 0
-------------------------------------
select /*+ index (a little_skew_col1) */ avg(pk_col) from
kso.little_skew a where col1 = 1
 
Plan hash value: 2709260180
 
-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |       |       | 20835 (100)|          |
|   1 |  SORT AGGREGATE              |                  |     1 |     9 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| LITTLE_SKEW      |   620K|  5449K| 20835   (1)| 00:04:11 |
|*  3 |    INDEX RANGE SCAN          | LITTLE_SKEW_COL1 |   620K|       |  3213   (1)| 00:00:39 |
-------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("COL1"=1)
 
 
21 rows selected.
 
SQL> -- need to create baseline for original statement to get SQL_HANDLE
SQL>
SQL> !cat create_baseline.sql
var ret number
exec :ret := dbms_spm.load_plans_from_cursor_cache(-
    sql_id=>'&sql_id', -
    plan_hash_value=>&plan_hash_value,-
    fixed=>'&fixed');
 
 
 
 
SQL> @create_baseline
Enter value for sql_id: 9df852xw86wxt
Enter value for plan_hash_value: 3498336203
Enter value for fixed: NO
 
PL/SQL procedure successfully completed.
 
SQL> -- now add the other plan as a fixed Baseline
SQL>
SQL> !cat create_baseline2.sql
var ret number
exec :ret := dbms_spm.load_plans_from_cursor_cache(-
    sql_id=>'&sql_id', -
    plan_hash_value=>&plan_hash_value,-
    sql_handle=>'&sql_handle',-
    fixed=>'&fixed');
 
 
SQL> @create_baseline2
Enter value for sql_id: fbnx1bws6n4xu
Enter value for plan_hash_value: 2709260180
Enter value for sql_handle: SYS_SQL_19fcc3effcce06c0
Enter value for fixed: YES
 
PL/SQL procedure successfully completed.
 
SQL> @baselines
Enter value for sql_text: 
Enter value for name: 
Enter value for plan_name: 
 
SQL_HANDLE                     PLAN_NAME                      SQL_TEXT                                           ENABLED ACC FIX LAST_EXECUTED
------------------------------ ------------------------------ -------------------------------------------------- ------- --- --- ----------------
SYS_SQL_19fcc3effcce06c0       SYS_SQL_PLAN_fcce06c08b1420d2  select avg(pk_col) from kso.little_skew where col1 YES     YES NO
SYS_SQL_19fcc3effcce06c0       SYS_SQL_PLAN_fcce06c02fdfd157  select avg(pk_col) from kso.little_skew where col1 YES     YES YES
 
SQL> select avg(pk_col) from kso.little_skew where col1 = 1;
 
AVG(PK_COL)
-----------
 310999.357
 
SQL> /        
 
AVG(PK_COL)
-----------
 310999.357
 
SQL> -- check to see if it's using the fixed Baseline
SQL> @find_sql
Enter value for sql_text: %little%
Enter value for sql_id: 
 
SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME      AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
9df852xw86wxt      0 3498336203          2           .19        3,279 select avg(pk_col) from kso.little_skew where col1 = 1
fbnx1bws6n4xu      0 2709260180          1           .86        5,278 select /*+ index (a little_skew_col1) */ avg(pk_col) from ks
                                                                      o.little_skew a where col1 = 1
 
 
SQL> 
SQL> @dplan
Enter value for sql_id: 9df852xw86wxt
Enter value for child_no: 0
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9df852xw86wxt, child number 0
-------------------------------------
select avg(pk_col) from kso.little_skew where col1 = 1
 
Plan hash value: 3498336203
 
----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |       |       |   907 (100)|          |
|   1 |  SORT AGGREGATE    |             |     1 |     9 |            |          |
|*  2 |   TABLE ACCESS FULL| LITTLE_SKEW |   620K|  5449K|   907   (2)| 00:00:11 |
----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("COL1"=1)
 
 
19 rows selected.
 
SQL> -- no it's not using the Baseline, why not?

So we created a Fixed Baseline on a statement that used a hint to force an index based access path, attached it to another statement with no hints, and then re-ran the un-hinted statement. It did not pick up the Fixed Baseline. The next step was to have a look at a 10053 trace file. Here are some excerpts from the 10053 trace file.

 
...
 
SPM: statement found in SMB
 
...
 
******************************************
----- Current SQL Statement for this session (sql_id=9df852xw86wxt) -----
select avg(pk_col) from kso.little_skew where col1 = 1
*******************************************
 
...
 
SPM: cost-based plan found in the plan baseline, planId = 2333352146
SPM: fixed planId's of plan baseline are: 803197271
SPM: using qksan to reproduce, cost and select accepted plan, sig = 1872586980585244352
SPM: reproduce plan round 1, using session OFE=11010007
SPM: using qksan to reproduce accepted plan, planId = 803197271
Registered qb: SEL$1 0xf6d872ec (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=SEL$1 nbfros=1 flg=0
    fro(0): flg=4 objn=90376 hint_alias="LITTLE_SKEW"@"SEL$1"
 
DOP: Automatic degree of parallelism is disabled: Parameter.
PM: Considering predicate move-around in query block SEL$1 (#0)
**************************
Predicate Move-Around (PM)
**************************
OPTIMIZER INFORMATION
 
******************************************
----- Current SQL Statement for this session (sql_id=7039h2744jjrv) -----
/* SQL Analyze(110,0) */ select avg(pk_col) from kso.little_skew where col1 = 1
*******************************************
 
...
 
SPM: planId in plan baseline = 803197271, planId of reproduced plan = 2333352146
------- START SPM Plan Dump -------
SPM: failed to reproduce the plan using the following info:
  parse_schema name        : SYS
  plan_baseline signature  : 1872586980585244352
  plan_baseline plan_id    : 803197271
  plan_baseline hintset    :
    hint num  1 len 27 text: IGNORE_OPTIM_EMBEDDED_HINTS
    hint num  2 len 37 text: OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
    hint num  3 len 22 text: DB_VERSION('11.1.0.7')
    hint num  4 len  8 text: ALL_ROWS
    hint num  5 len 22 text: OUTLINE_LEAF(@"SEL$1")
    hint num  6 len 57 text: INDEX_RS_ASC(@"SEL$1" "A"@"SEL$1" ("LITTLE_SKEW"."COL1"))
SPM: generated non-matching plan:
----- Explain Plan Dump -----
----- Plan Table -----
 
============
Plan Table
============
-----------------------------------------+-----------------------------------+
| Id  | Operation           | Name       | Rows  | Bytes | Cost  | Time      |
-----------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT    |            |       |       |   907 |           |
| 1   |  SORT AGGREGATE     |            |     1 |     9 |       |           |
| 2   |   TABLE ACCESS FULL | LITTLE_SKEW|  605K | 5449K |   907 |  00:00:11 |
-----------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - filter("COL1"=1)
 
Content of other_xml column
===========================
  db_version     : 11.1.0.7
  parse_schema   : SYS
  plan_hash      : 3498336203
  plan_hash_2    : 2333352146
  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
      DB_VERSION('11.1.0.7')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "LITTLE_SKEW"@"SEL$1")
    END_OUTLINE_DATA
  */
 
------- END SPM Plan Dump -------

So it appears that the Baseline is found in the SMB (SQL Management Base). It then appears to try to reproduce the plan in a separate session using the hints. It then announces that it came up with a different plan and spits out the plan data that it came up with. So it appears that the plan_hash_value is just used for comparison to see if it came up with the same plan as the one that was in place when the Baseline was generated. If not, then it falls back to the regular (no Baseline) behavior. Not at all what I had hoped for!

It may be that 11gR1 is an intermediate step and that a future version (11gR2 maybe, I hope) will actually store the plan details instead of just the plan hash value. But as I said in the intro, this is complete speculation on my part.

9 Comments

  1. Mike says:

    Interesting. It appears there can be an SMB “statement match”, but an attempt to “reproduce accepted plan” failed with “SPM: generated non-matching plan”. The important bit for me was seeing the 10053 and two types of “matches”, e.g. statement match and matching/non-matching plan.

    In the documentation, it says “non-match” might happen if you remove an index from all accepted plans. E.g. “if a change in the system (such as a dropped index) causes all accepted plans to become non-reproducible, the optimizer will select the best-cost plan”. I think you created the opposite case with same effect using a hint, e.g. forcing an index with hint. Another test case might limit using a variable from the plan history such as bind variables, etc. E.g. “plan history will include relevant information used by the optimizer to reproduce an execution plan, such as the SQL text, outline, bind variables, and compilation environment”.

    Good luck.

  2. Mike Tefft says:

    You say ‘I never could find a place where the plan data was stored (only the plan_hash_value).’ Where did you find it?

    I can see the plan_hash_value in the output from dbms_xplan.display_sql_plan_baseline but view dba_sql_plan_baselines does not have a plan_hash_value. It has a plan_name but it does not look anything like a plan_hash_value and I do not know where any cross-reference is.

    Thanks,
    Mike

  3. osborne says:

    Mike,

    DBA_SQL_PLAN_BALSELINES is based in part on sqlobj$ which has a column (not exposed in DBA_SQL_PLAN_BASELINES) called plan_id. It does not match the actual plan_hash_value column from v$sql but I believe it is basically the same thing but possibly with additional columns from the v$sql_plan table included the hash calculation. I have not found any cross reference unfortunately. If you Wolfgang a statement that uses a Baseline you will see a line in the trace file something like this:

    planId in plan baseline = 2333352146, planId of reproduced plan = 2333352146

    This line let’s you know whether the plan reproduced using the Baseline hints matched or whether it fell back to re-optimizing the plan from scratch.

    Here’s the definition of the DBA_SQL_PLAN_BASELINES view and a describe on sqlobj$:

    
    SQL> @view_def
    Enter value for view_name: DBA_SQL_PLAN_BASELINES
    
    VIEW_NAME                      TEXT
    ------------------------------ --------------------------------------------------------------------------------
    DBA_SQL_PLAN_BASELINES         SELECT /*+ dynamic_sampling(3) */
                                       so.signature,
                                       st.sql_handle,
                                       st.sql_text,
                                       so.name,
                                       ad.creator,
                                       DECODE(ad.origin, 1, 'MANUAL-LOAD',
                                                         2, 'AUTO-CAPTURE',
                                                         3, 'MANUAL-SQLTUNE',
                                                         4, 'AUTO-SQLTUNE',
                                                            'UNKNOWN'),
                                       ad.parsing_schema_name,
                                       ad.description,
                                       ad.version,
                                       ad.created,
                                       ad.last_modified,
                                       so.last_executed,
                                       ad.last_verified,
                                       DECODE(BITAND(so.flags, 1), 1, 'YES', 'NO'),
                                       DECODE(BITAND(so.flags, 2), 2, 'YES', 'NO'),
                                       DECODE(BITAND(so.flags, 4), 4, 'YES', 'NO'),
                                       DECODE(BITAND(so.flags, 8), 8, 'YES', 'NO'),
                                       ad.optimizer_cost,
                                       ad.module,
                                       ad.action,
                                       ad.executions,
                                       ad.elapsed_time,
                                       ad.cpu_time,
                                       ad.buffer_gets,
                                       ad.disk_reads,
                                       ad.direct_writes,
                                       ad.rows_processed,
                                       ad.fetches,
                                       ad.end_of_fetch_count
                                   FROM
                                       sqlobj$        so,
                                       sqlobj$auxdata ad,
                                       sql$text       st
                                   WHERE
                                       so.signature = st.signature AND
                                       ad.signature = st.signature AND
                                       so.signature = ad.signature AND
                                       so.plan_id = ad.plan_id AND
                                       so.obj_type = 2 AND
                                       ad.obj_type = 2
    
    
    SQL> desc sqlobj$
     Name                                                                                   Null?    Type
     -------------------------------------------------------------------------------------- -------- ----------------------------------------------------------
     SIGNATURE                                                                              NOT NULL NUMBER
     CATEGORY                                                                               NOT NULL VARCHAR2(30)
     OBJ_TYPE                                                                               NOT NULL NUMBER
     PLAN_ID                                                                                NOT NULL NUMBER
     NAME                                                                                   NOT NULL VARCHAR2(30)
     FLAGS                                                                                  NOT NULL NUMBER
     LAST_EXECUTED                                                                                   TIMESTAMP(6)
     SPARE1                                                                                          NUMBER
     SPARE2                                                                                          CLOB
    

    Hope that helps.

    Kerry

  4. Larry Thomas says:

    We have also noticed cases in which fixed plans are not being used. In each case the scenario was identical to your test case. A baseline was created by auto-capturing or manually inserting a plan in a baseline. A hinted statement was then run to create the desired plan and that was added to the baseline as the fixed plan. In each case where we had problems, we found that we had aliased a table in the hinted statement (as you did in the example above) and had not had a corresponding alias in the original statement. Removing the alias from the hinted plan solved the problem.

    In your test case above removing the alias and using the table name in the hint seems to allow the unhinted statement to use the fixed baseline plan.

    Larry Thomas

  5. osborne says:

    Thanks for the comment Larry.

    You’re exactly correct. It all comes down to whether a hint can be used or not. The format of many of the hints specify an object in a query block like the INDEX hint in our example above:

    INDEX_RS_ASC(@”SEL$1″ “A”@”SEL$1″ (“LITTLE_SKEW”.”COL1″)

    As you pointed out, if you use an alias (in this case we used A), then the original statement can apply that hint because it doesn’t have an object (“A”) in the specified query block (“SEL$1″).

    Kerry

  6. [...] Osborne 1. Do SQL Plan Baselines use hints (pt.1) 2. Do SQL Plan Baselines use hints (pt. 2) 3. SQL Plan Management – SQL Plan Baselines 4. Why isn’t Oracle using my outline/ SQL [...]

  7. Raja says:

    Thanks for the detailed information. What are the major differences between SQL profile and SQL baselines?

  8. osborne says:

    Hi Raja,

    Major differences:

    1. Profiles allow you to add any set of hints you want to a statement via dbms_sqltune.import_sql_profile (search this blog and you’ll see many examples). Baselines do not have such a feature.

    2. Profiles have a force_matching flag that allows them to be applied to more than one statement (i.e. to statements that use literals instead of bind variables). Baselines don’t have that feature.

    3. Baselines know what plan they are trying to reproduce (they store a plan hash value). If the set of hints stored by the baseline do not cause the optimizer to pick the original plan, the optimizer tries again with another baseline or without any hints at all. Profiles blindly apply the hints and have no knowledge of the plan they are trying to get.

    I did a presentation at the Hotsos Symposium a couple of years ago with a lot more details.
    Here’s a link to the slides: Controlling Execution Plans

Leave a Reply