Peeked binds and cursor_sharing

This post is just a brief demo post inspired by an idea that was posted on the OTN by Franck Pachot https://community.oracle.com/ideas/17321. I’ll show that cursor_sharing=similar allows binds to be peeked when a frequency histogram exists against the column. The behaviour depends on Adaptive Cursor Sharing which is one of those complex beasts that works very nicely in your test environments but in the real world is a bit of a mystery.

The problem is that a cursor is only marked bind aware when certain combinations of bind variables are used, in testing you might do lowly selective bind, highly selective bind, highly selective bind and ACS will kick in on the third. If you do lowly selective bind, lowly selective bind, highly selective bind, highly selective bind: ACS won’t kick in yet so you may dismiss it as not working. If you want to learn more then https://www.amazon.co.uk/d/Books/Database-Problem-Solving-Troubleshooting-Handbook/0134429206 has a chapter on the behaviour.

But let’s not get side tracked, on to the demo. First we create a table that obviously has some skew over one of the columns, I’ve used a hard coded rownum limit so that if you were to copy, you’d get the same output. I’ll also do some setup here to make sure we have a frequency histogram and I’ll turn off serverout so we can easily see our plans.

set serverout off
set feed off
drop table skewed_data;
create table skewed_data
as
select rownum pk_col
      ,trunc(ln(rownum)) skew_col
      ,dbms_random.string('x',500) padding_col
from   all_objects where rownum <=93502;
create unique index skewed_data_pk on skewed_data(pk_col);
create index skewed_data_skew_idx on skewed_data (skew_col);
select count(*) from skewed_data;
 COUNT(*)
---------
    93502
select column_name, num_distinct, histogram 
from   user_tab_columns 
where  table_name = 'SKEWED_DATA';
COLUMN_NAME         NUM_DISTINCT HISTOGRAM
------------------- ------------ ---------------
PK_COL                     93502 NONE
SKEW_COL                      12 NONE
PADDING_COL                93312 NONE
select skew_col 
      ,count(*)
from   skewed_data
group by skew_col
order by 1;
  SKEW_COL   COUNT(*)
---------- ----------
         0          2
         1          5
         2         13
         3         34
         4         94
         5        255
         6        693
         7       1884
         8       5123
         9      13923
        10      37848
        11      33628
exec dbms_stats.gather_table_stats(user,'SKEWED_DATA',method_opt=>'for columns size 254 skew_col')
select column_name, num_distinct, histogram 
from   user_tab_columns 
where  table_name = 'SKEWED_DATA';
COLUMN_NAME         NUM_DISTINCT HISTOGRAM
------------------- ------------ ---------------
PADDING_COL                93312 NONE
SKEW_COL                      12 FREQUENCY
PK_COL                     93502 NONE
Next to demonstrate that the skew in our skew_col is enough to lead to different plans being used for values 1 and 10.
explain plan for
select max(padding_col) 
from   skewed_data
where  skew_col = 1;
select * from table(dbms_xplan.display);
Plan hash value: 4257315069
------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                      |     5 |  2545 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| SKEWED_DATA          |     5 |  2545 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | SKEWED_DATA_SKEW_IDX |     5 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SKEW_COL"=1)
explain plan for
select max(padding_col) 
from   skewed_data
where  skew_col = 10;
select * from table(dbms_xplan.display);

Plan hash value: 746880940

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             | 37848 |    18M|  1844   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| SKEWED_DATA | 37848 |    18M|  1844   (1)| 00:00:01 |
---------------------------------------------------------------------------------

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

   1 - filter("SKEW_COL"=10)
So far, so good. Next we want to demonstrate that if we were to use binds, ACS kicks in on our third execution if we do lowly-highly-highly:
var bSkew number
exec :bSkew := 1
select max(padding_col)
from   skewed_data
where  skew_col = :bSkew;
select * from table(dbms_xplan.display_cursor(format=>'advanced -projection -alias -outline'));
Plan hash value: 1617942411
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE                      |                      |     1 |   504 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| SKEWED_DATA          |     5 |  2520 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | SKEWED_DATA_SKEW_IDX |     5 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------

   1 - :BSKEW (NUMBER): 1

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

   3 - access("SKEW_COL"=:BSKEW)

exec :bSkew := 10
select max(padding_col)
from   skewed_data
where  skew_col = :bSkew;
select * from table(dbms_xplan.display_cursor(format=>'advanced -projection -alias -outline'));

Plan hash value: 1617942411

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE                      |                      |     1 |   504 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| SKEWED_DATA          |     5 |  2520 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | SKEWED_DATA_SKEW_IDX |     5 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :BSKEW (NUMBER): 1

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

   3 - access("SKEW_COL"=:BSKEW)

exec :bSkew := 10
select max(padding_col)
from   skewed_data
where  skew_col = :bSkew;
select * from table(dbms_xplan.display_cursor(format=>'advanced -projection -alias -outline'));

Plan hash value: 3993148105

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |       |       |  1844 (100)|          |
|   1 |  SORT AGGREGATE    |             |     1 |   504 |            |          |
|*  2 |   TABLE ACCESS FULL| SKEWED_DATA | 37848 |    18M|  1844   (1)| 00:00:01 |
----------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :BSKEW (NUMBER): 10

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

   2 - filter("SKEW_COL"=:BSKEW)
Cool, the second execution didn’t use the full table scan plan because at this point the cursor was only marked as bind sensitive, not bind aware (I haven’t shown this but the column exists in v$sql, you can check for yourself if you like). Next we want to turn on cursor_sharing=similar and demonstrate the same behaviour occurs when we use literals.
alter session set cursor_sharing=similar;
select max(padding_col)
from   skewed_data
where  skew_col = 1;
select * from table(dbms_xplan.display_cursor(format=>'advanced -projection -alias -outline'));

Plan hash value: 1617942411

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE                      |                      |     1 |   504 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| SKEWED_DATA          |     5 |  2520 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | SKEWED_DATA_SKEW_IDX |     5 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :SYS_B_0 (NUMBER): 1

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

   3 - access("SKEW_COL"=:SYS_B_0)

exec :bSkew := 10
select max(padding_col)
from   skewed_data
where  skew_col = 10;
select * from table(dbms_xplan.display_cursor(format=>'advanced -projection -alias -outline'));

Plan hash value: 1617942411

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE                      |                      |     1 |   504 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| SKEWED_DATA          |     5 |  2520 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | SKEWED_DATA_SKEW_IDX |     5 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :SYS_B_0 (NUMBER): 1

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

   3 - access("SKEW_COL"=:SYS_B_0)

exec :bSkew := 10
select max(padding_col)
from   skewed_data
where  skew_col = 10;
select * from table(dbms_xplan.display_cursor(format=>'advanced -projection -alias -outline'));

Plan hash value: 3993148105

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |       |       |  1844 (100)|          |
|   1 |  SORT AGGREGATE    |             |     1 |   504 |            |          |
|*  2 |   TABLE ACCESS FULL| SKEWED_DATA | 37848 |    18M|  1844   (1)| 00:00:01 |
----------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :SYS_B_0 (NUMBER): 10

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

   2 - filter("SKEW_COL"=:SYS_B_0)
And there you have it, two different plans for our similar cursor sharing. Of course, one of the biggest difficulties in getting this to occur on production databases is that your real users may not use the right predicates in the right order (those pesky real users!). To get around this you might want to use the bind_aware hint which makes the peeked behaviour always occur.
select /*+ BIND_AWARE */ max(padding_col)
from   skewed_data
where  skew_col = 1;
select * from table(dbms_xplan.display_cursor(format=>'advanced -projection -alias -outline'));



Plan hash value: 1617942411

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE                      |                      |     1 |   504 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| SKEWED_DATA          |     5 |  2520 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | SKEWED_DATA_SKEW_IDX |     5 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :SYS_B_0 (NUMBER): 1

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

   3 - access("SKEW_COL"=:SYS_B_0)
select /*+ BIND_AWARE */ max(padding_col)
from   skewed_data
where  skew_col = 10;
select * from table(dbms_xplan.display_cursor(format=>'advanced -projection -alias -outline'));

Plan hash value: 3993148105

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |       |       |  1844 (100)|          |
|   1 |  SORT AGGREGATE    |             |     1 |   504 |            |          |
|*  2 |   TABLE ACCESS FULL| SKEWED_DATA | 37848 |    18M|  1844   (1)| 00:00:01 |
----------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :SYS_B_0 (NUMBER): 10

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

   2 - filter("SKEW_COL"=:SYS_B_0)
The peeked bind behaviour might not be so good for your shared pool:
select /*+ BIND_AWARE */ max(padding_col)
from   skewed_data
where  skew_col = 9;
select * from table(dbms_xplan.display_cursor(format=>'advanced -projection -alias -outline'));

SQL_ID  gczdkuca2dzt2, child number 2
-------------------------------------
select /*+ BIND_AWARE */ max(padding_col) from   skewed_data where
skew_col = :"SYS_B_0"

Plan hash value: 1617942411

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                      |       |       |  1023 (100)|          |
|   1 |  SORT AGGREGATE                      |                      |     1 |   504 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| SKEWED_DATA          | 13923 |  6852K|  1023   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | SKEWED_DATA_SKEW_IDX | 13923 |       |    28   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :SYS_B_0 (NUMBER): 9

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

   3 - access("SKEW_COL"=:SYS_B_0)


select /*+ BIND_AWARE */ max(padding_col)
from   skewed_data
where  skew_col = 11;
select * from table(dbms_xplan.display_cursor(format=>'advanced -projection -alias -outline'));
SQL_ID  gczdkuca2dzt2, child number 3
-------------------------------------
select /*+ BIND_AWARE */ max(padding_col) from   skewed_data where
skew_col = :"SYS_B_0"

Plan hash value: 3993148105

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |       |       |  1844 (100)|          |
|   1 |  SORT AGGREGATE    |             |     1 |   504 |            |          |
|*  2 |   TABLE ACCESS FULL| SKEWED_DATA | 33628 |    16M|  1844   (1)| 00:00:01 |
----------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :SYS_B_0 (NUMBER): 11

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

   2 - filter("SKEW_COL"=:SYS_B_0)
See how we’ve got an extra child cursor per bind, this behaviour also occurs without the hint. Perhaps the behaviour would be nicer with a height balanced histogram where ranges of values will share the same selectivity. I’ll leave that as homework for the reader.
For now, I think this satisfies the requirement that Franck is after, you get roughly the same behaviour as if you had used literals (when you account for ACS).
Advertisements