In the same vein as the preceding blog post, in this second and last post pf the series I will provide three differents scripts to a bind aware cursor that owes its bind sensitiveness property from a bind variable value having a Hybrid histogram. The first one gives the selectivity cube of a popular Hybrid histogram value. The second script do the same thing for a non-popular Hybrid histogram having an endpoint number. The third and last script gives the selectivity cube of a non captured Hybrid histogram value.
1. Cursor Selectivity cube for a popular Hybrid histogram
In order to put all this in action I am going to use the model I have found in this article:
SQL> desc acs_test_tab
Name Null? Type
------------------- -------- -------------
ID NOT NULL NUMBER
RECORD_TYPE NUMBER
DESCRIPTION VARCHAR2(50)
SQL> alter session set cursor_sharing=force;
SQL> select
column_name
,num_distinct
,num_buckets
,sample_size
,histogram
from
user_tab_col_statistics
where table_name = 'ACS_TEST_TAB'
and column_name = 'RECORD_TYPE';
COLUMN_NAME NUM_DISTINCT NUM_BUCKETS SAMPLE_SIZE HISTOGRAM
------------ ------------ ----------- ----------- ------------
RECORD_TYPE 50080 254 5407 HYBRID
As you can see the RECORD_TYPE column has a HYBRID histogram with the following popular and non-popular values distribution:
select
endpoint_number
,endpoint_actual_value
,endpoint_repeat_count
--,bucket_size
,case when Popularity > 0 then 'Pop'
else 'Non-Pop'
end Popularity
from
(
select
uth.endpoint_number
,uth.endpoint_actual_value
,uth.endpoint_repeat_count
,ucs.sample_size/ucs.num_buckets bucket_size
,(uth.endpoint_repeat_count - ucs.sample_size/ucs.num_buckets) Popularity
from
user_tab_histograms uth
,user_tab_col_statistics ucs
where
uth.table_name = ucs.table_name
and uth.column_name = ucs.column_name
and uth.table_name = 'ACS_TEST_TAB'
and uth.column_name = 'RECORD_TYPE'
)
order by endpoint_number;
ENDPOINT_NUMBER ENDPOINT_A ENDPOINT_REPEAT_COUNT POPULAR
--------------- ---------- --------------------- -------
1 1 1 Non-Pop
2684 2 2683 Pop -- we will use this
2695 569 1 Non-Pop -- we wiil use this
2706 1061 1 Non-Pop
2717 1681 1 Non-Pop
2727 1927 1 Non-Pop
../..
5364 98501 1 Non-Pop
5375 98859 1 Non-Pop
5386 99187 1 Non-Pop
5396 99641 1 Non-Pop
5407 99999 1 Non-Pop
254 rows selected.
There are 254 endpoint actual values of which only one value is popular(2). The following query will be used all over this article to show how we can compute the cursor selectivity cube for the three types of Hybrid histogram mentioned in the introduction:
-- as the ambassador of popular values SELECT MAX(id) FROM acs_test_tab WHERE record_type = 2;
We will start by getting the selectivity cube of the popular value 2 as shown below (script can be found at the end of this blog post):
SQL> @CurSelCubeHybridPop PL/SQL procedure successfully completed. Enter value for bind: 2 BIND LOW HIGH ---------- ---------- ---------- 2 ,446588 ,545829
Now that we have figured out what will be the value of the cursor selectivity cube of a bind aware cursor using the popular value 2, let’s see whether we have been right or wrong:
SELECT MAX(id) FROM acs_test_tab WHERE record_type = 1;-- only for ACS warmup
SELECT MAX(id) FROM acs_test_tab WHERE record_type = 2;
SELECT MAX(id) FROM acs_test_tab WHERE record_type = 2;
SQL_ID 9vu42gjuudpvj, child number 1
-------------------------------------
---------------------------------------------------
| Id | Operation | Name | Rows |
---------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | TABLE ACCESS FULL| ACS_TEST_TAB | 49621 |
---------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 – filter("RECORD_TYPE"=:SYS_B_0)
select
child_number
,range_id
,low
,high
from
gv$sql_cs_selectivity
where
sql_id ='9vu42gjuudpvj';
CHILD_NUMBER RANGE_ID LOW HIGH
------------ ---------- ---------- ----------
1 0 0.446588 0.545829
Spot how the low-high value of child cursor n°1 matches perfectly the low and high value given by the CurSelCubeHybridPop.sql script.
2. Cursor Selectivity cube for a non-popular Hybrid histogram value having an endpoint number
Let’s now consider a non-popular value having an endpoint number in the histogram table and let’s figure out what would be the selectivity cube of its underlying bind aware cursor:
SQL> @CurSelCubeHybridNonPop PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. Enter value for bind: 569 BIND LOW HIGH ---------- ---------- ---------- 569 ,000166 ,000203
And now we are ready to execute the corresponding query, get its execution plan and present the low and high value of the bind aware cursor when ran against this Hybrid non-popular value:
SELECT MAX(id) FROM acs_test_tab WHERE record_type = 569;
SQL_ID 9vu42gjuudpvj, child number 2 –- new execution plan
-------------------------------------
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| ACS_TEST_TAB | 18 |
|* 3 | INDEX RANGE SCAN | ACS_TEST_TAB_RECORD_TYPE_I | 18 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 – access("RECORD_TYPE"=:SYS_B_0)
select
child_number
,range_id
,low
,high
from
gv$sql_cs_selectivity
where
sql_id ='9vu42gjuudpvj';
CHILD_NUMBER RANGE_ID LOW HIGH
------------ ---------- ---------- ----------
2 0 0.000166 0.000203 –- new execution plan
1 0 0.446588 0.545829
Spot again the precision of the forecast. The low and high value of child cursor n°2 correspond exactly to the selectivity cube of the Hybrid non-popular value anticipated by the CurSelCubeHybridNonPop.sql script.
3. Cursor Selectivity cube for a non-popular Hybrid histogram value without an endpoint number
A Hybrid histogram value without an endpoint number is a value that exists for the column but which has not been captured by the Histogram gathering program for reasons I am not going to expose here. We can get all those values via an appropriate query. 41 is one value among the not captured ones. Let’s use it in the following demonstration:
Firt we will get its expected selectivity cube:
SQL> @CurSelCubeHybridNonPopWithoutEndPoint
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Enter value for bind: 41
41 LOW HIGH
---------- ---------- ----------
41 ,000009 ,000011
This selectivity range is not included in the selectivity range of child cursor n°1 nor in that of child cursor n°2. This is why if we use it in the following query it will certainly force ECS to hard parse a new execution plan as the following proves:
SQL> SELECT MAX(id) FROM acs_test_tab WHERE record_type = 41;
SQL_ID 9vu42gjuudpvj, child number 3 -- new execution plan
-------------------------------------
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| ACS_TEST_TAB | 1 |
|* 3 | INDEX RANGE SCAN | ACS_TEST_TAB_RECORD_TYPE_I | 1 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("RECORD_TYPE"=:SYS_B_0)
As it has been again correctly expected a new execution plan (child cursor n°3) has been compiled. But since the new execution plan is identical to an existing one (child cursor n°2) Oracle has merged the selectivities of these two cursors, kept shareable the last compiled one and deleted the old plan as the following proves:
select
child_number
,range_id
,low
,high
from
gv$sql_cs_selectivity
where
sql_id ='9vu42gjuudpvj';
CHILD_NUMBER RANGE_ID LOW HIGH
------------ ---------- ---------- ----------
3 0 0.000009 0.000203 -- new plan with merge selectivity
2 0 0.000166 0.000203
1 0 0.446588 0.545829
select
child_number
,is_shareable
from
gv$sql
where
sql_id = '9vu42gjuudpvj';
CHILD_NUMBER I
------------ -
0 N
1 Y
2 N –- deleted
3 Y – selectivity cube merged
4. Conclusion
The first blog post of this series provided a script with which we can anticipate the cursor selectivity cube range of a bind aware cursor when its bind sensitivenes is due to a predicate having a Frequency histogram. In this article we presented three new scripts giving the same anticipation for a bind aware cursor that owes its bind sensitivenes respectively to a popular Hybrid histogram, a non-popular Hybrid histogram having and endpoint number and a non captured Hybrid histogram. This concludes the series
CurSelCubeHybridNonPop
Mohamed Houri’s Oracle Notes 
Leave a comment