Mohamed Houri’s Oracle Notes

October 28, 2017

Cursor selectivity cube -Part II

Filed under: Oracle — hourim @ 1:40 pm

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

CurSelCubeHybridNonPopWithoutEndPoint

CurSelCubeHybridPop

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

%d bloggers like this: