Search

Top 60 Oracle Blogs

Recent comments

V$SQL_CS_HISTOGRAMS: What Are the Buckets’ Thresholds?

The contents of the V$SQL_CS_HISTOGRAM view is used by the SQL engine to decide when a cursor is made bind aware, and therefore, when it should use adaptive cursor sharing. For each child cursor, the view shows three buckets. It is of general knowledge that the first one (BUCKET_ID equal 0) is associated with the executions that process up to and including 1,000 rows, the second one (BUCKET_ID equal 1) with the executions that processes between 1,001 and 1,000,000 rows, and the third one (BUCKET_ID equal 2) with the executions that processes more than 1,000,000 rows. The idea is that after an execution, the SQL engine associates (that is, increments the COUNT column) the execution to one of the three buckets. Then, depending on the distribution, decides whether a cursor has to be made bind aware.

As far as I know, Oracle does not document the value associated to the two thresholds (1,000 and 1,000,000 rows). A way of getting a “confirmation” about their value is to activate event 10507 at level 1 and to execute a SQL statement that requires an adaptive sharing context being initialized. The following is an example illustrates:

CREATE TABLE t 
AS 
SELECT rownum AS id, rpad('*',100,'*') AS pad 
FROM dual
CONNECT BY level <= 10000;

CREATE INDEX i ON t (id);

EXECUTE dbms_stats.gather_table_stats(ownname => user, tabname => 't');

VARIABLE id NUMBER

ALTER SESSION SET events '10507 trace name context forever, level 1';

EXECUTE :id := 42;

SELECT pad FROM t WHERE id <= :id;

After executing the previous SQL statements, the trace file associated to the session executing them contains, among others, information about the initialization of the adaptive sharing context:

Adaptive Sharing Context [sql_id=0bd3skz8qu74b]
  Peeked Binds:
    Bind Set:
      Hash: 2355667773
  Peeked Bind Bucket: none
  Execution Histogram:
    1: 0
    1000: 0
    1000000: 0
  Cumulative Execution Stats:
    Exec Stats:
      Rows Processed: 0
      Buffer Gets: 0
      CPU Time: 0
      # Executions: 0

As you can see, the execution histogram provides three buckets with the expected thresholds.

Even though the situation seems to be crystal clear, I observed a number of situations where executions are associated to a bucket having a greater BUCKET_ID. For example, based on the same test table just used, let’s execute the following SQL statements:

EXECUTE :id := 666;

SELECT /*+ gather_plan_statistics */ pad FROM t WHERE id <= :id;

SELECT * FROM table(dbms_xplan.display_cursor(format=>'rowstats last'));

SELECT child_number, bucket_id, count
FROM v$sql_cs_histogram
WHERE sql_id = '916d2bdv6up4m'
ORDER BY child_number, bucket_id;

The execution plan and the associated runtime statistics shows that 666 rows were processed (or, better, returned). But, according to the V$SQL_CS_HISTOGRAM view, the execution was associated to the bucket number 1 (instead of the expected one, bucket number 0).

SQL_ID  916d2bdv6up4m, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ pad FROM t WHERE id <= :id

Plan hash value: 3241032591 

-------------------------------------------------------------------------------
| Id  | Operation                           | Name | Starts | E-Rows | A-Rows |
-------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T    |      1 |    666 |    666 |
|*  2 |   INDEX RANGE SCAN                  | I    |      1 |    666 |    666 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"<=:ID)

CHILD_NUMBER  BUCKET_ID      COUNT
------------ ---------- ----------
           0          0          0
           0          1          1
           0          2          0

If we check the information written by event 10507 into the trace file, we observe the same thing:

Adaptive Sharing Context [sql_id=916d2bdv6up4m]
  Peeked Binds:
    Bind Set:
      Hash: 1666119184
  Peeked Bind Bucket: 1
  Execution Histogram:
    1: 0
    1000: 1
    1000000: 0
  Cumulative Execution Stats:
    Exec Stats:
      Rows Processed: 666
      Buffer Gets: 388
      CPU Time: 0
      # Executions: 1

Also note that the number of rows processed corresponds to the actual number of rows returned by the test query.

Mhmmm…

One essential thing to point out is that the number of rows returned by a query is not necessarily usable to select the bucket. For example, in case of a COUNT(*) executed against the data returned by one table, the size of the result set is always 1. But, of course, there might be several execution plans that can be used to get the data. A much more sensible value would be the size of the result set before the aggregation. To illustrate, let’s execute the following SQL statements:

EXECUTE :id := 666;

SELECT /*+ gather_plan_statistics */ count(pad) FROM t WHERE id <= :id;

SELECT * FROM table(dbms_xplan.display_cursor(format=>'rowstats last'));

SELECT child_number, bucket_id, count
FROM v$sql_cs_histogram
WHERE sql_id = '58pgx22tbschb'
ORDER BY child_number, bucket_id;

As you can see, even though the test query returns a single row, the execution is associated to the bucket number 1:

SQL_ID  58pgx22tbschb, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ count(pad) FROM t WHERE id <= :id

Plan hash value: 4237238475

--------------------------------------------------------------------------------
| Id  | Operation                            | Name | Starts | E-Rows | A-Rows |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |      |      1 |        |      1 |
|   1 |  SORT AGGREGATE                      |      |      1 |      1 |      1 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T    |      1 |    666 |    666 |
|*  3 |    INDEX RANGE SCAN                  | I    |      1 |    666 |    666 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("ID"<=:ID)

CHILD_NUMBER  BUCKET_ID      COUNT
------------ ---------- ----------
           0          0          0
           0          1          1
           0          2          0

Even more interesting is the information provided by event 10507 into the trace file:

Adaptive Sharing Context [sql_id=58pgx22tbschb]
  Peeked Binds:
    Bind Set:
      Hash: 1666119184
  Peeked Bind Bucket: none
  Execution Histogram:
    1: 0
    1000: 1
    1000000: 0
  Cumulative Execution Stats:
    Exec Stats:
      Rows Processed: 1
      Buffer Gets: 344
      CPU Time: 0
      # Executions: 1

As shown, the number of rows processed is equal to 1! In other words, it seems that the number of rows used to update the “Cumulative Execution Stats” part is probably not the one used for the selection of the bucket.

To get more information, what we can do is to enable event 10507 at the level 1281.

ALTER SESSION SET events '10507 trace name context forever, level 1281';

When doing so we also get information about the non-cumulated execution statistics (note that for a reason I ignore, I am able to get them only for the first execution of a specific child cursor):

ACS: Storing execution stats [sql_id=58pgx22tbschb]
  Bind Set:
    Hash: 1666119184
  Exec Stats:
    Rows Processed: 2666
    Buffer Gets: 344
    CPU Time: 0
    # Executions: 1

The key thing to point out is that the number of rows processed is equal to 2666! Therefore, based on them, it makes sense to associate the execution to the bucket number 1.

However, the question is: why 2666? The query selected only 666 rows...

Two are the reasons I found:

  • The number of rows processed by every row source operations are counted. For example, in the previous execution plan they are 1 + 666 + 666 = 1333 (the operation number 0 is ignored because it’s not a real operation).
  • The gathering of runtime statistics multiplies the number of rows processed by 2.

Those are the general rules. But, according to my observations, as the execution plan gets complex, I saw situations were they don’t exactly apply.

Summary

Even though the values associated to the two thresholds are 1,000 rows and 1,000,000 rows, depending on the execution plan and on whether the runtime statistics are gathered, the actual number of rows used to select the bucket can vary. Therefore, do not be surprised if you see associations that at first seem wrong.

PS: I asked myself whether this behavior is the reason why Oracle, since version 12.0.0.1, suppressed the contents of the V$SQL_CS_STATISTICS view, which is known as bug 24441377.

PPS: In case you are interested, here are few additional examples based on the same test table used through this post:

IRS

EXECUTE :id := 6666;
SELECT pad FROM t WHERE id <=  :id ORDER BY id;

-----------------------------------------------------------------------
| Id  | Operation                   | Name | Starts | E-Rows | A-Rows |
-----------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID| T    |      1 |   6667 |   6666 |
|*  2 |   INDEX RANGE SCAN          | I    |      1 |   6667 |   6666 |
-----------------------------------------------------------------------

ACS: Storing execution stats [sql_id=fuy2b4uu148t0]
  Bind Set:
    Hash: 2150090960
  Exec Stats:
    Rows Processed: 13332
    Buffer Gets: 562
    CPU Time: 0
    # Executions: 1
kkocsStoreBindAwareStats: updated Adaptive Sharing Ctx ****
Adaptive Sharing Context [sql_id=fuy2b4uu148t0]
  Peeked Binds:
    Bind Set:
      Hash: 2150090960
  Peeked Bind Bucket: none
  Execution Histogram:
    1: 0
    1000: 1
    1000000: 0
  Cumulative Execution Stats:
    Exec Stats:
      Rows Processed: 6666
      Buffer Gets: 562
      CPU Time: 0
      # Executions: 1

IFFS

EXECUTE :id := 6666;
SELECT id FROM t WHERE id <= :id;

----------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |
----------------------------------------------------------------
|*  1 |  INDEX FAST FULL SCAN| I    |      1 |   6667 |   6666 |
----------------------------------------------------------------

ACS: Storing execution stats [sql_id=ffz9vz4z059gq]
  Bind Set:
    Hash: 2150090960
  Exec Stats:
    Rows Processed: 6666
    Buffer Gets: 249
    CPU Time: 0
    # Executions: 1
kkocsStoreBindAwareStats: updated Adaptive Sharing Ctx ****
Adaptive Sharing Context [sql_id=ffz9vz4z059gq]
  Peeked Binds:
    Bind Set:
      Hash: 2150090960
  Peeked Bind Bucket: none
  Execution Histogram:
    1: 0
    1000: 1
    1000000: 0
  Cumulative Execution Stats:
    Exec Stats:
      Rows Processed: 6666
      Buffer Gets: 249
      CPU Time: 0
      # Executions: 1

IFFS+COUNT

EXECUTE :id := 6666;
SELECT count(id) FROM t WHERE id <= :id;

-----------------------------------------------------------------
| Id  | Operation             | Name | Starts | E-Rows | A-Rows |
-----------------------------------------------------------------
|   1 |  SORT AGGREGATE       |      |      1 |      1 |      1 |
|*  2 |   INDEX FAST FULL SCAN| I    |      1 |   6667 |   6666 |
-----------------------------------------------------------------

ACS: Storing execution stats [sql_id=bjyq7u2xg0vur]
  Bind Set:
    Hash: 2150090960
  Exec Stats:
    Rows Processed: 6667
    Buffer Gets: 27
    CPU Time: 0
    # Executions: 1
kkocsStoreBindAwareStats: updated Adaptive Sharing Ctx ****
Adaptive Sharing Context [sql_id=bjyq7u2xg0vur]
  Peeked Binds:
    Bind Set:
      Hash: 2150090960
  Peeked Bind Bucket: none
  Execution Histogram:
    1: 0
    1000: 1
    1000000: 0
  Cumulative Execution Stats:
    Exec Stats:
      Rows Processed: 1
      Buffer Gets: 27
      CPU Time: 0
      # Executions: 1

FTS

EXECUTE :id := 6666;
SELECT pad FROM t WHERE id <= :id;

-------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |
-------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| T    |      1 |   6667 |   6666 |
-------------------------------------------------------------

ACS: Storing execution stats [sql_id=9y6bd4wvpf3a9]
  Bind Set:
    Hash: 2150090960
  Exec Stats:
    Rows Processed: 6666
    Buffer Gets: 379
    CPU Time: 0
    # Executions: 1
kkocsStoreBindAwareStats: updated Adaptive Sharing Ctx ****
Adaptive Sharing Context [sql_id=9y6bd4wvpf3a9]
  Peeked Binds:
    Bind Set:
      Hash: 2150090960
  Peeked Bind Bucket: none
  Execution Histogram:
    1: 0
    1000: 1
    1000000: 0
  Cumulative Execution Stats:
    Exec Stats:
      Rows Processed: 6666
      Buffer Gets: 379
      CPU Time: 0
      # Executions: 1

FTS+SORT

EXECUTE :id := 6666;
SELECT pad FROM t WHERE id <=  :id ORDER BY pad;

--------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |
--------------------------------------------------------------
|   1 |  SORT ORDER BY     |      |      1 |   6667 |   6666 |
|*  2 |   TABLE ACCESS FULL| T    |      1 |   6667 |   6666 |
--------------------------------------------------------------

ACS: Storing execution stats [sql_id=fdvq2qwgf92m8]
  Bind Set:
    Hash: 2150090960
  Exec Stats:
    Rows Processed: 13332
    Buffer Gets: 157
    CPU Time: 0
    # Executions: 1
kkocsStoreBindAwareStats: updated Adaptive Sharing Ctx ****
Adaptive Sharing Context [sql_id=fdvq2qwgf92m8]
  Peeked Binds:
    Bind Set:
      Hash: 2150090960
  Peeked Bind Bucket: none
  Execution Histogram:
    1: 0
    1000: 1
    1000000: 0
  Cumulative Execution Stats:
    Exec Stats:
      Rows Processed: 6666
      Buffer Gets: 157
      CPU Time: 0
      # Executions: 1

UNION ALL

EXECUTE :id := 666;
SELECT to_char(id) FROM t WHERE id <= :id UNION ALL SELECT pad FROM t WHERE id <= :id;

--------------------------------------------------------------------------------
| Id  | Operation                            | Name | Starts | E-Rows | A-Rows |
--------------------------------------------------------------------------------
|   1 |  UNION-ALL                           |      |      1 |        |   1332 |
|*  2 |   INDEX RANGE SCAN                   | I    |      1 |    666 |    666 |
|   3 |   TABLE ACCESS BY INDEX ROWID BATCHED| T    |      1 |    666 |    666 |
|*  4 |    INDEX RANGE SCAN                  | I    |      1 |    666 |    666 |
--------------------------------------------------------------------------------

ACS: Storing execution stats [sql_id=bdgg6b849h9x6]
  Bind Set:
    Hash: 1666119184
  Exec Stats:
    Rows Processed: 3330
    Buffer Gets: 83
    CPU Time: 0
    # Executions: 1
kkocsStoreBindAwareStats: updated Adaptive Sharing Ctx ****
Adaptive Sharing Context [sql_id=bdgg6b849h9x6]
  Peeked Binds:
    Bind Set:
      Hash: 1666119184
  Peeked Bind Bucket: none
  Execution Histogram:
    1: 0
    1000: 1
    1000000: 0
  Cumulative Execution Stats:
    Exec Stats:
      Rows Processed: 1332
      Buffer Gets: 83
      CPU Time: 0
      # Executions: 1

JOIN1

EXECUTE :id := 666;
SELECT count(t1.pad) FROM t t1 JOIN t t2 ON t1.id = t2.id WHERE t1.id <= :id AND t2.id <= :id;

---------------------------------------------------------------------------------
| Id  | Operation                             | Name | Starts | E-Rows | A-Rows |
---------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE                       |      |      1 |      1 |      1 |
|*  2 |   HASH JOIN                           |      |      1 |    665 |    666 |
|*  3 |    INDEX RANGE SCAN                   | I    |      1 |    666 |    666 |
|   4 |    TABLE ACCESS BY INDEX ROWID BATCHED| T    |      1 |    666 |    666 |
|*  5 |     INDEX RANGE SCAN                  | I    |      1 |    666 |    666 |
---------------------------------------------------------------------------------

ACS: Storing execution stats [sql_id=d7raw7xj5r0pk]
  Bind Set:
    Hash: 1666119184
  Exec Stats:
    Rows Processed: 2665
    Buffer Gets: 17
    CPU Time: 0
    # Executions: 1
kkocsStoreBindAwareStats: updated Adaptive Sharing Ctx ****
Adaptive Sharing Context [sql_id=d7raw7xj5r0pk]
  Peeked Binds:
    Bind Set:
      Hash: 1666119184
  Peeked Bind Bucket: none
  Execution Histogram:
    1: 0
    1000: 1
    1000000: 0
  Cumulative Execution Stats:
    Exec Stats:
      Rows Processed: 1
      Buffer Gets: 17
      CPU Time: 0
      # Executions: 1

JOIN2

EXECUTE :id := 42;
SELECT count(t1.pad) FROM t t1 JOIN t t2 ON t1.id <= t2.id WHERE t1.id <= :id AND t2.id <= :id;

-------------------------------------------------------
| Id  | Operation                     | Name | E-Rows |
-------------------------------------------------------
|   1 |  SORT AGGREGATE               |      |      1 |
|   2 |   MERGE JOIN                  |      |    904 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T    |     42 |
|*  4 |     INDEX RANGE SCAN          | I    |     42 |
|*  5 |    SORT JOIN                  |      |     42 |
|*  6 |     INDEX RANGE SCAN          | I    |     42 |
-------------------------------------------------------

ACS: Storing execution stats [sql_id=7s62bdrbhpt4s]
  Bind Set:
    Hash: 2355667773
  Exec Stats:
    Rows Processed: 1072
    Buffer Gets: 5
    CPU Time: 0
    # Executions: 1
kkocsStoreBindAwareStats: updated Adaptive Sharing Ctx ****
Adaptive Sharing Context [sql_id=7s62bdrbhpt4s]
  Peeked Binds:
    Bind Set:
      Hash: 2355667773
  Peeked Bind Bucket: none
  Execution Histogram:
    1: 0
    1000: 1
    1000000: 0
  Cumulative Execution Stats:
    Exec Stats:
      Rows Processed: 1
      Buffer Gets: 5
      CPU Time: 0
      # Executions: 1

JOIN3

EXECUTE :id := 42;
SELECT t1.pad FROM t t1 JOIN t t2 ON t1.id <= t2.id WHERE t1.id <= :id AND t2.id <= :id;

------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | E-Rows | A-Rows |
------------------------------------------------------------------------
|   1 |  MERGE JOIN                  |      |      1 |    904 |    903 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |      1 |     42 |     42 |
|*  3 |    INDEX RANGE SCAN          | I    |      1 |     42 |     42 |
|*  4 |   SORT JOIN                  |      |     42 |     42 |    903 |
|*  5 |    INDEX RANGE SCAN          | I    |      1 |     42 |     42 |
------------------------------------------------------------------------

ACS: Storing execution stats [sql_id=4rd7542fvrut2]
  Bind Set:
    Hash: 2355667773
  Exec Stats:
    Rows Processed: 1071
    Buffer Gets: 61
    CPU Time: 0
    # Executions: 1
kkocsStoreBindAwareStats: updated Adaptive Sharing Ctx ****
Adaptive Sharing Context [sql_id=4rd7542fvrut2]
  Peeked Binds:
    Bind Set:
      Hash: 2355667773
  Peeked Bind Bucket: none
  Execution Histogram:
    1: 0
    1000: 1
    1000000: 0
  Cumulative Execution Stats:
    Exec Stats:
      Rows Processed: 903
      Buffer Gets: 61
      CPU Time: 0
      # Executions: 1