Cursor selectivity cube -Part I

Bind variable selectivity is the building block on which the Extended Cursor Sharing Layer code reasons to compile a new good enough execution plan or share an existing one. It kicks in only for a bind aware cursor. The underlying child cursor is given a selectivity interval comprised between a low and a high value derived from the bind variable selectivity that initiates it. This is what Oracle refers to as a cursor selectivity cube shown in the following picture:

The ECS layer code launches the bind-aware cursor matching algorithm at each soft parse of a bind aware cursor. If the new bind variable value selectivity is outside an existing selectivity cube (low-high exposed in gv$sql_cs_selectivity) then a new hard parse is done and a new child cursor with a new selectivity cube is created. If, however, the peeked bind variable selectivity falls into a range of an existing child cursor selectivity cube, ECS will then share the corresponding child cursor’s execution plan. Finally if a new hard parsed execution plan is equivalent to an existing one then both child cursors will be merged. The selectivity cube of the last created child cursor will be adjusted while the previous cursor which served the merge process will be marked un-shareable in order to save space in the memory and reduce the time spent during cursor pruning activity.

The rest of this article shows, first, how the selectivity cube (low-high value) is computed for a bind variable value with a Frequency histogram. It then explains how two cursors with the same execution plan but different selectivity cubes are merged to form a single child cursor with an updated low-high range interval.

1. Cursor Selectivity cube

For simplicity’s sake I am going to use my old and helpful model:

create table t_acs(n1 number, n2 number);

begin
for j in 1..1200150 loop
   if j=1 then
      insert into t_acs values (j,1);
   elsif j> 1 and j <= 101 then          
    insert into t_acs values (j,100);     
    elsif j> 101 and j <= 1101 then          
    insert into t_acs values (j,1000);     
    elsif j> 10001 and j <= 110001 then          
    insert into t_acs values (j,10000);     
    else insert into t_acs values (j,1000000);     
   end if;  end loop;  commit;  
end  
/  
create index t_acs_idx1 on t_acs(n2); 
begin 
dbms_stats.gather_table_stats
        (user, 't_acs' 
        ,method_opt => 'for all columns size skewonly'
        ,cascade => true
	    ,estimate_percent => dbms_stats.auto_sample_size);
end;
/

The above data set contains 1,200,150 rows of which the n2 column has 5 distinct highly skewed values as shown below:

SQL> select n2, count(1) from t_acs group by n2 order by 2 ;

        N2   COUNT(1)
---------- ----------
         1          1
       100        100
      1000        910
     10000     100000
   1000000    1099139

The n2 column has a FREQUENCY histogram.

SQL> select column_name, histogram
     from user_tab_col_statistics
     where table_name ='T_ACS'
     and column_name  ='N2';

 COLUMN_NAM HISTOGRAM
---------- -----------
N2         FREQUENCY

The selectivity of the numeric n2 column is then computed via the following formula:

SQL> select
        endpoint_actual_value bind
       ,round( endpoint_actual_value/1200150,6) bind_sel
       ,endpoint_number
       ,endpoint_number –(lag(endpoint_number,1,0)
                         over(order by endpoint_number)) value_count
     from user_tab_histograms
     where table_name ='T_ACS'
     and column_name  ='N2'
     order by endpoint_number;

BIND         BIND_SEL ENDPOINT_NUMBER VALUE_COUNT
---------- ---------- --------------- -----------
1             ,000001               1           1
100           ,000083             101         100
1000          ,000833            1011         910
10000         ,008332          101011      100000
1000000       ,833229         1200150     1099139

The cursor selectivity cube is computed using the selectivity of the n2 bind variable value and an offset of +- 10% far from that selectivity forming  the x and y abscises of the cursor selectivity cube(see the above figure):

SQL> select
    bind
   ,round((sel_of_bind - offset),6) low
   ,round((sel_of_bind + offset),6) high
from
   (select
      bind
     ,value_count/1200150 sel_of_bind
	 ,0.1*(value_count/1200150) offset
     from
     (
      select
        endpoint_actual_value bind
       ,round( endpoint_actual_value/1200150,6) bind_sel
       ,endpoint_number
       ,endpoint_number –(lag(endpoint_number,1,0)
                         over(order by endpoint_number)) value_count
     from user_tab_histograms
     where table_name ='T_ACS'
     and column_name  ='N2'
     order by endpoint_number
     )
    )
    ;

BIND              LOW       HIGH
---------- ---------- ----------
1             ,000001    ,000001
100           ,000075    ,000092
1000          ,000682    ,000834
10000         ,074991    ,091655
1000000       ,824251   1,007418

Let’s put this select into a sql script and name it CurSelCubeFreq.sql.

We will be back to this script later in this article. For this moment, we will put it on hold and we will embark on the cursor merge section.

2. Cursor merge

The cursor of the query I am going to execute is not yet bind aware as the following proves:

select
    child_number
	,range_id
   ,low
   ,high
 from
   gv$sql_cs_selectivity
 where
    sql_id ='42wmc4buuh6wb';

no rows selected

But the next execution will mark it bind aware (cursor sharing parameter is set to FORCE) and will generate a new (full table scan) execution plan:

SQL> select count(1) from t_acs where N2 = 1e6;

  COUNT(1)
----------
   1099139

SQL> start xpsimp

SQL_ID  42wmc4buuh6wb, child number 1
-------------------------------------
--------------------------------------------
| Id  | Operation          | Name  | Rows  |
--------------------------------------------
|   0 | SELECT STATEMENT   |       |       |
|   1 |  SORT AGGREGATE    |       |     1 |
|*  2 |   TABLE ACCESS FULL| T_ACS |  1099K|
--------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("N2"=:SYS_B_1)

select
    child_number
	,range_id
   ,low
   ,high
 from
   gv$sql_cs_selectivity
 where
    sql_id ='42wmc4buuh6wb';

CHILD_NUMBER   RANGE_ID LOW        HIGH
------------ ---------- ---------- ----------
           1          0 0.824251   1.007418

And, for the sake of clarity, let’s print again the content of the CurSelCubeFre.sql (slightly updated):

SQL> @CurSelCubeFreq
Enter value for bind: 1e6

BIND              LOW       HIGH
---------- ---------- ----------
1000000       ,824251   1,007418

Spot the coincidence 🙂

Suppose now that I want to know whether a less selective bind variable value (1) will force a new hard parse or share an existing execution plan. For that, I will first get the selectivity cube of this bind variable as shown below:

-- The selectivity cube of bind variable 1
SQL> @CurSelCubeFreq
Enter value for bind: 1

BIND              LOW       HIGH
---------- ---------- ----------
1             ,000001    ,000001

As you can see this bind variable value has a selectivity outside outside of that of the existing child cursor n°1. This is why ECS will fairly likely trigger a new hard parse as the following proves:

SQL> select count(1) from t_acs where N2 = 1;

  COUNT(1)
----------
         1

SQL> start xpsimp

SQL_ID  42wmc4buuh6wb, child number 2 -- new execution plan
-------------------------------------
------------------------------------------------
| Id  | Operation         | Name       | Rows  |
------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |
|   1 |  SORT AGGREGATE   |            |     1 |
|*  2 |   INDEX RANGE SCAN| T_ACS_IDX1 |     1 |
------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("N2"=:SYS_B_1)

select
    child_number
	,range_id
   ,low
   ,high
 from
   gv$sql_cs_selectivity
 where
    sql_id ='42wmc4buuh6wb';

CHILD_NUMBER   RANGE_ID LOW        HIGH
------------ ---------- ---------- ----------
           2          0 0.000001   0.000001  --> new execution plan
           1          0 0.824251   1.007418

Notice that we have got, as expected, a new child cursor n°2 with a new range of selectivity that has produced an index range scan execution plan.

Finally, what if, in the next run, I will use a bind variable value(10000) having a different selectivity but producing the same index range scan execution plan?

SQL> @CurSelCubeFreq
Enter value for bind: 10000

BIND              LOW       HIGH
---------- ---------- ----------
10000         ,074991    ,091655

SQL> select count(1) from t_acs where N2 = 10000;

  COUNT(1)
----------
    100000

SQL> start xpsimp

SQL_ID  42wmc4buuh6wb, child number 3
-------------------------------------
------------------------------------------------
| Id  | Operation         | Name       | Rows  |
------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |
|   1 |  SORT AGGREGATE   |            |     1 |
|*  2 |   INDEX RANGE SCAN| T_ACS_IDX1 |   100K|
------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("N2"=:SYS_B_1)

select
    child_number
	,range_id
   ,low
   ,high
 from
   gv$sql_cs_selectivity
 where
    sql_id ='42wmc4buuh6wb';

CHILD_NUMBER   RANGE_ID LOW        HIGH
------------ ---------- ---------- ----------
           3          0 0.000001   0.091655  --> new child cursor
           2          0 0.000001   0.000001
           1          0 0.824251   1.007418

Notice how the low value of child cursor n°3 (0.000001) corresponds to the low value of child cursor n°2 and not to the low selectivity of the bind variable value for which it has been compiled (.074991). This is because the selectivity of child cursor n°2 has been merged with that of child cursor n°3 since their execution plans are identical. While the selectivity cube of child cursor n°3 has been enlarged child cursor n°2 has been deleted (put in a non-shareable status) as shown below:

select
   child_number
  ,is_bind_aware
  ,is_shareable
 from
   gv$sql
 where
   sql_id ='42wmc4buuh6wb'; 

CHILD_NUMBER I I
------------ - -
           0 N N
           1 Y Y
           2 Y N → is not shareable
           3 Y Y → includes the selectivity of child n°2

If we want to know whether the bind variable value 100 will share an existing execution plan or force a new one we have to check if its selectivity falls into an existing child cursor selectivity cube or not:

SQL> @CurSelCubeFreq
Enter value for bind: 100

BIND              LOW       HIGH
---------- ---------- ----------
100           ,000075    ,000092

This is going to share the child cursor n°3 since its selectivity falls into the low-high range of that cursor:

SQL> select count(1) from t_acs where N2 = 100;

  COUNT(1)
----------
       100

SQL> start xpsimp

SQL_ID  42wmc4buuh6wb, child number 3
-------------------------------------
------------------------------------------------
| Id  | Operation         | Name       | Rows  |
------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |
|   1 |  SORT AGGREGATE   |            |     1 |
|*  2 |   INDEX RANGE SCAN| T_ACS_IDX1 |   100K|
------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 – access("N2"=:SYS_B_1)

3. Conclusion

That is how the Extended Cursor Sharing layer code works. A combination of bind variable selectivities, with a possibly extendable cursor selectivity cube, allows Oracle to decide, at each execution of a bind aware cursor, whether to share an existing execution plan, compile a brand new one, or merge two cursors to form a single one to the detriment of a deleted one. This last action reduces the memory usage and the number of child cursors during the non-innocent child cursor pruning that occurs when a shareable parent cursor with multiple childs is soft parsed.