Coskan’s Approach to Oracle

March 23, 2012

Adaptive Cursor Sharing with SQL Plan Baselines – Bind Sensitiveness

Filed under: Performance, Plan Stability — coskan @ 11:16 am

Today I saw a post from my friend Dominic Brooks (orastory) whom I work together at the same place for different environments (Wish he is working on the environments I look after together we could have made a great performance team :(). His post is about how Adaptive Cursor Sharing and SQL Plan Baselines work together . Post looks  long but easy to read and enlightening about the issue with one small addition/correction/observation needed. I think he covered well enough but finished his tests a bit soon. I will not repeat what he has done but I need to cover the additional observation/correction part. After the introduction, half way through wrote a section “How then do ACS and SQL Plan Baselines work together?” (wish he uses some marking for subtopics – thats why we have bold and headings Dom). At the end of that section he says “We’re using one of our baseline plans but we’ve lost our bind-awareness.” which is not complete  truth according to  my tests on 11.2.0.1.

I assume table is created, index is created, stats are gathered, two sql_plan_baseline is generated and cursor is flushed as below

HR@ORACOS> select sql_id
    ,      child_number
    ,      is_bind_aware
    ,      is_bind_sensitive
    ,      is_shareable
    ,      to_char(exact_matching_signature) sig
    ,      executions
   ,      plan_hash_value,
   sql_plan_baseline
   from   v$sql
  2    3    4    5    6    7    8    9   10   11    where  sql_id = '731b98a8u0knf';

no rows selected

HR@ORACOS> select to_char(signature) signature, sql_handle, plan_name, enabled, accepted
    from   dba_sql_plan_baselines
  2    3     where   signature = 1292784087274697613;

SIGNATURE                                SQL_HANDLE                     PLAN_NAME                      ENA ACC
---------------------------------------- ------------------------------ ------------------------------ --- ---
1292784087274697613                      SYS_SQL_11f0e4472549338d       SQL_PLAN_13w748wknkcwd616acf47 YES YES
1292784087274697613                      SYS_SQL_11f0e4472549338d       SQL_PLAN_13w748wknkcwd8576eb1f YES YES

First Run — Full table scan —To make the post short I named v$sql query as sqlstat

var n varchar2(2)
exec :n := 'N1'
select count(*), max(col2) from t1 where flag = :n;
HR@ORACOS>
PL/SQL procedure successfully completed.

HR@ORACOS>
  COUNT(*) MAX(COL2)
---------- --------------------------------------------------
     49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

HR@ORACOS> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  731b98a8u0knf, child number 0
-------------------------------------
select count(*), max(col2) from t1 where flag = :n

Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   203 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    30 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 49298 |  1444K|   203   (1)| 00:00:02 |
---------------------------------------------------------------------------

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

   2 - filter("FLAG"=:N)

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

23 rows selected.

HR@ORACOS> @sqlstat

SQL_ID        CHILD_NUMBER I I I SIG                                      EXECUTIONS PLAN_HASH_VALUE SQL_PLAN_BASELINE
------------- ------------ - - - ---------------------------------------- ---------- --------------- ------------------------------
731b98a8u0knf            0 N Y Y 1292784087274697613                               1      3724264953 SQL_PLAN_13w748wknkcwd616acf47

Statement is not bind aware which is expected but is bind sensitive and sharable and using baseline

Second Run — Index Scan

  exec :n := 'Y1'
select count(*), max(col2) from t1 where flag = :n;

PL/SQL procedure successfully completed.

HR@ORACOS>
  COUNT(*) MAX(COL2)
---------- --------------------------------------------------
         1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

HR@ORACOS> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  731b98a8u0knf, child number 0
-------------------------------------
select count(*), max(col2) from t1 where flag = :n

Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   203 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    30 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 49298 |  1444K|   203   (1)| 00:00:02 |
---------------------------------------------------------------------------

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

   2 - filter("FLAG"=:N)

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

23 rows selected.

HR@ORACOS> @sqlstat

SQL_ID        CHILD_NUMBER I I I SIG                                      EXECUTIONS PLAN_HASH_VALUE SQL_PLAN_BASELINE
------------- ------------ - - - ---------------------------------------- ---------- --------------- ------------------------------
731b98a8u0knf            0 N Y Y 1292784087274697613                               2      3724264953 SQL_PLAN_13w748wknkcwd616acf47

Statement is not bind aware which is “normal” as Optimizer group already wrote about in their optimizer blog and cursor is still sharable.
Optimizer group said

"Oracle monitors the behavior of the queries, and determined that the different bind values caused the data volumes
manipulated by the query to be significantly different. Based on this difference, Oracle "adapts" its behavior so that
the same plan is not always shared for this query.
Hence on the next execution, a new plan is generated based on the current bind value."

Lets see if it is like that when baselines are in place

Third Run — Index Scan

  exec :n := 'Y1'
select count(*), max(col2) from t1 where flag = :n;

PL/SQL procedure successfully completed.

HR@ORACOS>
  COUNT(*) MAX(COL2)
---------- --------------------------------------------------
         1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

HR@ORACOS> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  731b98a8u0knf, child number 1
-------------------------------------
select count(*), max(col2) from t1 where flag = :n

Plan hash value: 3625400295

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |    30 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |     1 |    30 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I1   |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   3 - access("FLAG"=:N)

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

24 rows selected.

HR@ORACOS> @sqlstat

SQL_ID        CHILD_NUMBER I I I SIG                                      EXECUTIONS PLAN_HASH_VALUE SQL_PLAN_BASELINE
------------- ------------ - - - ---------------------------------------- ---------- --------------- ------------------------------
731b98a8u0knf            0 N Y Y 1292784087274697613                               2      3724264953 SQL_PLAN_13w748wknkcwd616acf47
731b98a8u0knf            1 Y Y Y 1292784087274697613                               1      3625400295 SQL_PLAN_13w748wknkcwd8576eb1f

Now on the second run of index favouring bind, new cursor is generated and bind aware also using the index scan baseline which is correct behaviour according to the optimizer groups statement.

Fourth Run — Table Scan

exec :n := 'N1'
select count(*), max(col2) from t1 where flag = :n;

PL/SQL procedure successfully completed.

HR@ORACOS>
  COUNT(*) MAX(COL2)
---------- --------------------------------------------------
     49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

HR@ORACOS> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  731b98a8u0knf, child number 2
-------------------------------------
select count(*), max(col2) from t1 where flag = :n

Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   203 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    30 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 49298 |  1444K|   203   (1)| 00:00:02 |
---------------------------------------------------------------------------

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

   2 - filter("FLAG"=:N)

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

23 rows selected.

HR@ORACOS> @sqlstat

SQL_ID        CHILD_NUMBER I I I SIG                                      EXECUTIONS PLAN_HASH_VALUE SQL_PLAN_BASELINE
------------- ------------ - - - ---------------------------------------- ---------- --------------- ------------------------------
731b98a8u0knf            0 N Y N 1292784087274697613                               2      3724264953 SQL_PLAN_13w748wknkcwd616acf47
731b98a8u0knf            1 Y Y Y 1292784087274697613                               1      3625400295 SQL_PLAN_13w748wknkcwd8576eb1f
731b98a8u0knf            2 Y Y Y 1292784087274697613                               1      3724264953 SQL_PLAN_13w748wknkcwd616acf47

Now on the second run of table scan again new cursor is generated and again bind aware also using the right full table scan baseline. Please also look at the first cursor which is not sharable anymore.

Fifth Run — Index Scan

   exec :n := 'Y1'
select count(*), max(col2) from t1 where flag = :n;

PL/SQL procedure successfully completed.

HR@ORACOS>
  COUNT(*) MAX(COL2)
---------- --------------------------------------------------
         1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

HR@ORACOS> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  731b98a8u0knf, child number 1
-------------------------------------
select count(*), max(col2) from t1 where flag = :n

Plan hash value: 3625400295

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |    30 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |     1 |    30 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I1   |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   3 - access("FLAG"=:N)

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

24 rows selected.

HR@ORACOS> @sqlstat

SQL_ID        CHILD_NUMBER I I I SIG                                      EXECUTIONS PLAN_HASH_VALUE SQL_PLAN_BASELINE
------------- ------------ - - - ---------------------------------------- ---------- --------------- ------------------------------
731b98a8u0knf            0 N Y N 1292784087274697613                               2      3724264953 SQL_PLAN_13w748wknkcwd616acf47
731b98a8u0knf            1 Y Y Y 1292784087274697613                               2      3625400295 SQL_PLAN_13w748wknkcwd8576eb1f
731b98a8u0knf            2 Y Y Y 1292784087274697613                               1      3724264953 SQL_PLAN_13w748wknkcwd616acf47

ORacle uses correct child cursor which is first child and hasn’t got any of is bind awareness or sensitivity changed

Sixth Run — Table Scan

HR@ORACOS>
  COUNT(*) MAX(COL2)
---------- --------------------------------------------------
     49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

HR@ORACOS> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  731b98a8u0knf, child number 2
-------------------------------------
select count(*), max(col2) from t1 where flag = :n

Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   203 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    30 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 49298 |  1444K|   203   (1)| 00:00:02 |
---------------------------------------------------------------------------

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

   2 - filter("FLAG"=:N)

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

23 rows selected.

HR@ORACOS> @sqlstat

SQL_ID        CHILD_NUMBER I I I SIG                                      EXECUTIONS PLAN_HASH_VALUE SQL_PLAN_BASELINE
------------- ------------ - - - ---------------------------------------- ---------- --------------- ------------------------------
731b98a8u0knf            0 N Y N 1292784087274697613                               2      3724264953 SQL_PLAN_13w748wknkcwd616acf47
731b98a8u0knf            1 Y Y Y 1292784087274697613                               2      3625400295 SQL_PLAN_13w748wknkcwd8576eb1f
731b98a8u0knf            2 Y Y Y 1292784087274697613                               2      3724264953 SQL_PLAN_13w748wknkcwd616acf47

ORacle uses correct child cursor which is second child and hasn’t got any of is bind awareness or sensitivity changed

Bonus Run with different Bind N2 which should again favour full scan

exec :n := 'N2'
select count(*), max(col2) from t1 where flag = :n;
select * from table(dbms_xplan.display_cursor);

@sqlstat

PL/SQL procedure successfully completed.

HR@ORACOS>
  COUNT(*) MAX(COL2)
---------- --------------------------------------------------
     49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

HR@ORACOS>
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  731b98a8u0knf, child number 2
-------------------------------------
select count(*), max(col2) from t1 where flag = :n

Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   203 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    30 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 49298 |  1444K|   203   (1)| 00:00:02 |
---------------------------------------------------------------------------

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

   2 - filter("FLAG"=:N)

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

23 rows selected.

HR@ORACOS> HR@ORACOS>
SQL_ID        CHILD_NUMBER I I I SIG                                      EXECUTIONS PLAN_HASH_VALUE SQL_PLAN_BASELINE
------------- ------------ - - - ---------------------------------------- ---------- --------------- ------------------------------
731b98a8u0knf            0 N Y N 1292784087274697613                               2      3724264953 SQL_PLAN_13w748wknkcwd616acf47
731b98a8u0knf            1 Y Y Y 1292784087274697613                               2      3625400295 SQL_PLAN_13w748wknkcwd8576eb1f
731b98a8u0knf            2 Y Y Y 1292784087274697613                               3      3724264953 SQL_PLAN_13w748wknkcwd616acf47

Oracle is doing the right thing by using the child no 1 with right baseline

Bonus Run with different Bind Y2 which should again favour Index Scan

   exec :n := 'Y2'
select count(*), max(col2) from t1 where flag = :n;
select * from table(dbms_xplan.display_cursor);
HR@ORACOS>
PL/SQL procedure successfully completed.

HR@ORACOS>
  COUNT(*) MAX(COL2)
---------- --------------------------------------------------
         1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

HR@ORACOS>
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  731b98a8u0knf, child number 1
-------------------------------------
select count(*), max(col2) from t1 where flag = :n

Plan hash value: 3625400295

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |    30 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |     1 |    30 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I1   |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   3 - access("FLAG"=:N)

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

24 rows selected.

HR@ORACOS> @sqlstat

SQL_ID        CHILD_NUMBER I I I SIG                                      EXECUTIONS PLAN_HASH_VALUE SQL_PLAN_BASELINE
------------- ------------ - - - ---------------------------------------- ---------- --------------- ------------------------------
731b98a8u0knf            0 N Y N 1292784087274697613                               2      3724264953 SQL_PLAN_13w748wknkcwd616acf47
731b98a8u0knf            1 Y Y Y 1292784087274697613                               3      3625400295 SQL_PLAN_13w748wknkcwd8576eb1f
731b98a8u0knf            2 Y Y Y 1292784087274697613                               3      3724264953 SQL_PLAN_13w748wknkcwd616acf47

Oracle is doing the right thing by using the second child no 2 with right baseline.

Long story short I personally think they work perfectly fine together but I also wish if Oracle gives option to hold this runtime monitoring info in SYSAUX for env where people can accommodate more data in SYSAUX. This will save a lot of time for the initial loads.

To do a fair comparison I needed to do the tests on 11.2.0.1 but Don’t have 11.2.0.2 right now but I’m assuming it should be the same (I think I lost my appetite of installing new oracle version just for fun of it :) )

The Silver is the New Black Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 193 other followers