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
Thanks for the article! How did you know that you needed level 1281 for your trace? I understand that you can find the information about events in $ORACLE_HOME/rdbms/mesg/oraus.msg, but where is the information about possible levels?
No magic. I’m sorry. Just trying with (2^32)-1. If something more is shown, then it’s quite easy to find which values are involved. In the case of that particular event there are few more bits that provide addition information. But not relevant for what I wanted to show.