Mohamed Houri’s Oracle Notes

November 6, 2014

Bind Aware – Part I

Filed under: cursor sharing — hourim @ 4:14 pm

If you don’t want to use the /*+ bind-aware */ hint to make your cursor immediately bind aware, you may find that your initial cursor needs a certain number of executions to reach the bind awareness status. How many of such initial executions your cursor needs in its warming up period? And how this number of executions is monitored? Those are the two questions I aim to answer via a two parts articles

In my answering path I will start by creating the following t_cs table with a special data pattern

create table t_acs(n1  number, vc2  varchar2(10));

BEGIN
     for j in 1..1200150 loop
      if j = 1 then
       insert into t_acs values (j, 'j1');
      elsif j>1 and j<=101 then
       insert into t_acs values(j, 'j100');
      elsif j>101 and j<=1101 then
       insert into t_acs values (j, 'j1000');
      elsif j>10001 and j<= 110001 then
      insert into t_acs values(j,'j10000');
     else
      insert into t_acs values(j, 'j>million');
     end if;
    end loop;
   commit;
END;
/

create index t_acs_i1 on t_acs(vc2);

select vc2, count(1) from t_acs group by vc2 order by 2;

VC2          COUNT(1)
---------- ----------
j1                  1
j100              100
j1000            1000
j10000         100000
j>million     1099049

As you can notice above, the values of the vc2 column indicate the number of their occurrence in the t_cs table:

	‘j1’        means   select count(1) from t_acs where vc2 = ‘j1’         generates  1 rows
	‘j100’      means   select count(1) from t_acs where vc2 = ‘j100’       generates 100 rows
	‘j1000’     means   select count(1) from t_acs where vc2 = ‘j1000’      generates 1000 rows
	‘j10000’    means   select count(1) from t_acs where vc2 = ‘j10000’     generates 10000 rows
	‘j>million’ means   select count(1) from t_acs where vc2 = ‘j>million’  generates more than a million of rows

I will explain later why I built such a data skew set and such a naming of the vc2 column values.
There are three views that are used to monitor a cursor candidate to the adaptive cursor sharing feature:

SQL> desc v$sql_cs_statistics

           Name                            Null?    Type
           ------------------------------- -------- --------------------
    1      ADDRESS                                  RAW(8)
    2      HASH_VALUE                               NUMBER
    3      SQL_ID                                   VARCHAR2(13)
    4      CHILD_NUMBER                             NUMBER        --> emphasize this
    5      BIND_SET_HASH_VALUE                      NUMBER
    6      PEEKED                                   VARCHAR2(1)
    7      EXECUTIONS                               NUMBER
    8      ROWS_PROCESSED                           NUMBER        --> emphasize this
    9      BUFFER_GETS                              NUMBER
   10      CPU_TIME                                 NUMBER

SQL> desc v$sql_cs_histogram

           Name                            Null?    Type
           ------------------------------- -------- --------------------
    1      ADDRESS                                  RAW(8)
    2      HASH_VALUE                               NUMBER
    3      SQL_ID                                   VARCHAR2(13)
    4      CHILD_NUMBER                             NUMBER        --> emphasize this
    5      BUCKET_ID                                NUMBER        --> emphasize this
    6      COUNT                                    NUMBER        --> emphasize this

SQL> desc v$sql_cs_selectivity

           Name                            Null?    Type
           ------------------------------- -------- --------------------
    1      ADDRESS                                  RAW(8)
    2      HASH_VALUE                               NUMBER
    3      SQL_ID                                   VARCHAR2(13)
    4      CHILD_NUMBER                             NUMBER
    5      PREDICATE                                VARCHAR2(40)
    6      RANGE_ID                                 NUMBER
    7      LOW                                      VARCHAR2(10)
    8      HIGH                                     VARCHAR2(10)

The third view starts to be useful only when the cursor becomes bind aware. At this step of the article, let’s concentrate only on the rows_processed column of the first view and on the couple (bucket_id, count) columns of the second one. They go hand-in-hand in the monitoring process of the same child cursor

Let’s investigate how these two views interact with each other. I will be using, all over this article, the below query which is:

SQL> select count(1) from t_acs where vc2 = :lvc;

Where :lvc is a bind variable which I will declare and set (the first time) as follows:

SQL> var lvc varchar2(10);
SQL> exec :lvc := 'j100';

To be bind aware, a cursor requires from the column used in its predicate part to possess a histogram:

BEGIN
       dbms_stats.gather_table_stats
                   (user
                   ,'t_acs'
                   ,method_opt       => 'for all columns size skewonly'
                   ,estimate_percent => dbms_stats.auto_sample_size
                   ,cascade          => true
                   ,no_invalidate    => false
                   );
END;
/

I used ‘’size skewonly’’ for the method_opt parameter to collect histogram on the vc2 column. There was no previous query using the vc2 column in a where clause so that using the ‘’size auto’’ parameter in the above dbms_stats package call would have been more appropriate.

As mentioned above ‘j100’ means that my initial cursor will be processing 100 rows. This is what we can see in the two monitoring views when we execute the above query:

SQL> select
         child_number
         ,executions
         ,rows_processed
    from v$sql_cs_statistics
    where sql_id = '6f6wzmu3yzm43' ;

SQL> select
        child_number
        ,bucket_id
        ,count
    from
         v$sql_cs_histogram
    where  sql_id = '6f6wzmu3yzm43' ;

CHILD_NUMBER EXECUTIONS ROWS_PROCESSED
------------ ---------- --------------
           0          1            101

CHILD_NUMBER  BUCKET_ID      COUNT
------------ ---------- ----------
           0          0          1 --> incremented
           0          1          0
           0          2          0

The child cursor n° 0 processed 101 rows and Oracle incremented the count of its bucket_id n° 0.

What happens now if I execute the same query using a different bind variable value (‘j10000’)?

SQL> exec :lvc := 'j10000';

CHILD_NUMBER EXECUTIONS ROWS_PROCESSED
------------ ---------- --------------
           0          1         101

I know that ‘j10000’ bind variable produces 10,000 rows; so why my shared cursor n°0 is showing only 101 rows?

This is not completely true. We will see below, that in contrast to v$sql_cs_histogram view, the rows_processed column of the v$sql_cs_statistics view is updated only when a new execution plan is compiled. This means that, the rows_processed column is not updated when a child cursor is shared. At this step we are still sharing the child cursor n°0 and this is why the number of processed rows of the last second (last) execution is not corect.

Hopefully, we have the v$sql_cs_histogram:

CHILD_NUMBER  BUCKET_ID      COUNT
------------ ---------- ----------
           0          0          1
           0          1          1 --> incremented
           0          2          0

Oracle is showing, via this view, that we are still sharing the same child cursor n° 0, but, in contrast to the first execution where it was the bucket_id n° 0 that has seen its count incremented to 1, for the second query execution, it is actually the bucket_id n°1 that has been incremented.

What does this mean?

Oracle is telling us that the second query execution has actually processed more than the indicated 101 rows. We will see at the end of this article the proof of that claim.

Now, let’s use a new bind variable value (‘j>million’) and re-execute the same query?

SQL> exec :lvc := 'j>million';

CHILD_NUMBER EXECUTIONS ROWS_PROCESSED
----------- ---------- --------------
           1          1        1099050
           0          1         101

CHILD_NUMBER  BUCKET_ID      COUNT
------------ ---------- ----------
           1          0          0
           1          1          0
           1          2          1 --> incremented
           0          0          1
           0          1          1
           0          2          0

Notice this time that Oracle produces a new child cursor n°1, which is a sign that our cursor is now bind aware. It has processed 1,099,050 rows and it is the bucket_id number 2 of this new compiled child cursor n° 1 that has seen its count incremented to 1.

In order to show you that the bind variable ‘j10000’ has effectively generated more than 10,000 rows, let’s re-execute the initial query using this bind variable and observe what the monitoring views will show us:

SQL> exec :lvc := 'j10000';

CHILD_NUMBER EXECUTIONS ROWS_PROCESSED
------------ ---------- --------------
           2          1         100001
           1          1        1099050
           0          1            101

CHILD_NUMBER  BUCKET_ID      COUNT
------------ ---------- ----------
           2          0          0
           2          1          1 --> incremented
           2          2          0
           1          0          0
           1          1          0
           1          2          1
           0          0          1
           0          1          1
           0          2          0

A new child cursor n°2 has been compiled for the ‘j10000’ bind variable value and this is why we see the correct number of rows_processed (10,001) in the v$sql_cs_statistics view. Note also that for this child cursor n°2, it is the bucket_id n° 1 that has been incremented to 1

Keep away, for this moment, the fact that cursor child n°1 and 2 are bind aware and that cursor child n°0 became obsolete (not shareable anymore) as shown below:

select  sql_id
       ,child_number
       ,is_bind_aware
       ,is_bind_sensitive
       ,is_shareable
       ,executions
from   v$sql
where  sql_id ='6f6wzmu3yzm43';

SQL_ID        CHILD_NUMBER I I I EXECUTIONS
------------- ------------ - - - ----------
6f6wzmu3yzm43            0 N Y N          2
6f6wzmu3yzm43            1 Y Y Y          1
6f6wzmu3yzm43            2 Y Y Y          1

And notice that, with what preceds, I hope that I have demonstrated the relationship between the child cursor, the number of rows it has processed and the corresponding incremented bucket_id. This relationship obeys to the following algorithm:

             0   < ROWS_PROCESSED <= 1000  --> COUNT of BUCKET_ID  0 will be incremented
	       1000 < ROWS_PROCESSED <= 1e6   --> COUNT of BUCKET_ID  1 will be incremented
	              ROWS_PROCESSED > 1e6   --> COUNT of BUCKET_ID  2  will be incremented

In part II of this series we will see how the couple (bucket_id, count) influences the bind aware status of the initial child cursor. This couple of column ceases to play their initial role when the cursor becomes bind aware; in which case the third v$view (v$sql_cs_selectivity) enters the bind awareness scene and starts using the selectivity of the bind variable to decide whether to share an existing child cursor or optimize a new one.

Advertisements

9 Comments »

  1. Well, I’ve just checked the documentation for both 11g & 12c, and I can’t find that hint documented anywhere. How did you discover it?

    http://docs.oracle.com/cd/E11882_01/server.112/e41573/hintsref.htm#PFGRF005
    http://docs.oracle.com/database/121/SQLRF/sql_elements006.htm#SQLRF51098

    Comment by Martin Rose — November 6, 2014 @ 6:24 pm | Reply

  2. Martin,

    Good observation

    Randolf Geist(http://oracle-randolf.blogspot.be/) asked the same question to the Oracle Optimizer Group when they have published a blog article about this hint

    https://blogs.oracle.com/optimizer/entry/how_do_i_force_a

    Unfortunately, his question has not been answered. So in my opinion it is not documented and need not to be used in PRODUCTION if one wants a support from Oracle

    Best regards

    Comment by hourim — November 7, 2014 @ 7:55 am | Reply

    • If it’s been in Oracle since version 11.1.0.7 (as the article says) and it still doesn’t appear in the documention, my advice to you (& everybody else reading this) is to simply pretend it doesn’t exist.

      Oracle Corp. are very good at updating their manuals, and if they haven’t included this then they haven’t done so for a very good reason (ie; they don’t want the general public to use it). The same applies to the MATERIALIZE hint. Don’t use that either.

      If you do use undocumented hints, then the outcome may well be unpredictable. It might work as you expect it to in some circumstances, but then not properly in other circumstances.

      Only ever use documented hints.

      Comment by Martin Rose — November 8, 2014 @ 4:05 pm | Reply

  3. Hi Mohamed,

    Thanks for this post,
    The adaptive cursor sharing can work on tables have not statistics ?

    I have a table without stats and my query based on this table used binds.

    Thanks
    Cherif.

    Comment by cherif — November 7, 2014 @ 9:53 am | Reply

    • Cherif,

      Thanks for your excellent question.

      I tested and realized that at least we need to have statistics on the column in the predicate. Otherwise cursor can’t be bind sensitive and hence not bind aware (have to test also in this case if using the hint bind_aware it will bypass the bind sensitiveness prerequisite)

      
      SQL> exec dbms_stats.delete_table_stats(user ,'t_acs');
      
      SQL> exec :lvc := 'j100';
      
      SQL> select count(1) from t_acs where vc2 >= :lvc;
      
      SQL_ID  1jmujrygtdzqr, child number 0
      -------------------------------------
      --------------------------------------------
      | Id  | Operation          | Name  | Rows  |
      --------------------------------------------
      |   0 | SELECT STATEMENT   |       |       |
      |   1 |  SORT AGGREGATE    |       |     1 |
      |*  2 |   TABLE ACCESS FULL| T_ACS |  1348K|
      --------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
         2 - filter("VC2">=:LVC)
      
      Note
      -----
         - dynamic sampling used for this statement (level=2)
      
      SQL> select  sql_id
                 ,child_number
                 ,is_bind_aware
                 ,is_bind_sensitive
          from   v$sql
          where  sql_id ='1jmujrygtdzqr';
      
      SQL_ID        CHILD_NUMBER I I
      ------------- ------------ - -
      1jmujrygtdzqr            0 N N
      

      So my cursor is not bind sensitive even thought I have applied a range predicate (>= :lvc)

      I will now compute statistics only for table and not for column (do not do that in PROD), execute again the same query with a range predicate and see if my cursor become bind sensitive or not

      
      SQL> begin
          dbms_stats.gather_table_stats
           (user 
           ,'t_acs'
           ,method_opt => null
           ,cascade => true
           ,no_invalidate => false);
          end;
          /
      
      SQL_ID        CHILD_NUMBER I I I EXECUTIONS
      ------------- ------------ - - - ----------
      1jmujrygtdzqr            0 N N Y          1
      
      

      My cursor is still not bind sensitive as shown below

      Now I will collect statistics for the columns without histogram, execute the same query and check my cursor

      
      SQL> begin
             dbms_stats.gather_table_stats
             (user 
             ,'t_acs'
             ,method_opt => ‘for all columns size’
             ,cascade => true
            ,no_invalidate => false);
          end;
          /
      
      SQL_ID        CHILD_NUMBER I I I EXECUTIONS
      ------------- ------------ - - - ----------
      1jmujrygtdzqr            0 N Y Y          1
      

      And this time my cursor is bind sensitive.

      Bottom line: your cursor can’t be bind aware if you don’t have at least simple statistics on the predicate column.

      Comment by hourim — November 7, 2014 @ 3:53 pm | Reply

  4. […] part I we saw how the count of the bucket_id in the v$cs_histogram view is incremented according to the […]

    Pingback by Bind Aware – Part II | Mohamed Houri’s Oracle Notes — November 8, 2014 @ 1:49 pm | Reply

  5. […] than 0. For those who want to know what bucket and count represent they can start by reading part I and part […]

    Pingback by Bind aware secret sauce (again) | Mohamed Houri’s Oracle Notes — September 5, 2015 @ 8:21 am | Reply

  6. Just wondering why you dont use connect by to build your test data? Much much faster then the context switching caused when inserting in the loop.

    4 seconds vs 2 minutes on my machine.

    insert into t_acs
    select  level n1
    ,       case
              when level = 1 then 'j1'
              when level > 1 and level <= 101 then 'j100'
              when level > 101 and level <= 1101 then 'j1000'
              when level > 10001 and level <= 11000 then 'j10000'
              else 'j>million'
            end vc1
    from    dual
    connect by level < 1200150;
    

    Only reason I can think of is memory issues on a VM perhaps.

    Comment by daithiwalker — February 7, 2016 @ 2:05 pm | Reply

  7. daithiwalker,

    Thanks for your comment. No there is no particular reason for me to not use connect by. Though that the connect by way is more elegant than a Pl/SQL block

    Best regards

    Comment by hourim — February 7, 2016 @ 5:46 pm | Reply


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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Create a free website or 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: