4. Adaptive Cursor Sharing
When a new query is issued, the database server makes a syntactic check to determine the legality of the SQL statement. If this new query is found to be semantically equivalent to an existing one already hashed and currently available in the library cache, it is executed using the execution plan of the earlier query.
Such a sharing mechanism is possible when using bind variables (or literals with CURSOR_SHARING set to FORCE). However, cursor sharing and SQL optimization might be diametrically opposed. Whereas bind variables avoid reoptimization by sharing the existing child cursor, they are not necessarily going to do the same amount of work and henceforth they might create a performance issue.
Oracle Database 11g introduces adaptive cursor sharing (ACS; also known as extended cursor sharing) to address this conflicting issue between sharing resources and optimizing SQL. Multiple optimal execution plans per SQL statement can be generated depending on the value of the peeked bind variable and certain other criteria presented in this chapter. The chapter first examines the prerequisites for ACS, its working mechanism, and dynamic views to monitor it. Next, it outlines the secret sauce used to mark a cursor bind aware. Finally, it shows a practical case taken from a running system where a bind-aware cursor becomes dramatically nonperformant, generating a high number of child cursors and thereby damaging considerably the library cache of the application database.
ACS Working Algorithm
Figure 4.1 depicts an algorithm for triggering ACS. When a SQL query using bind variables is launched, if its underlying parent cursor is bind sensitive (this cursor property is explained shortly), the query is monitored by Oracle via the ACS feature so that an optimal execution plan is generated and used to execute the query. This cursor is then said to be adaptive because it fulfills two conditions: it is bind sensitive and it is bind aware.
The next section presents the bind-sensitive property and its prerequisites. Then we show how a cursor transits from this property to a bind-aware status in which its underlying SQL query will start to be executed via an optimal execution plan depending on its bind variable value’s selectivity.
Bind Sensitiveness with Range Predicate
The model used in this chapter is based primarily on a simple two-column heap table that has a B-tree single-column index. Basic statistics for the table, without histograms, have also been gathered, as shown in the following:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE 12.1.0.1.0 Production
TNS for 64-bit Windows: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 – Production
SQL> create table t_acs(n1 number, n2 number);
SQL> BEGIN
for j in 1..1200150 loop
if j = 1 then
insert into t_acs values (j, 1);
elsif j>1 and j<=101 then
insert into t_acs values(j, 100);
elsif j>101 and j<=1101 then
insert into t_acs values (j, 1000);
elsif j>10001 and j<= 110001 then
insert into t_acs values(j,10000);
else
insert into t_acs values(j, 1000000);
end if;
end loop;
commit;
END;
/
SQL> create index t_acs_i1 on t_acs(n2);
SQL> BEGIN
dbms_stats.gather_table_stats
(user
,'t_acs'
,method_opt => 'for all columns size 1'
,cascade => true
,estimate_percent => dbms_stats.auto_sample_size
);
END;
/
The data in column N2 is highly skewed, as shown in the following:
SQL> select n2, count(1) from t_acs group by n2 order by 2;
N2 COUNT(1)
---------- ----------
1 1
100 100
1000 1000
10000 100000
1000000 1099049
We explore the effect of skewness of data in the N2 column on the bind sensitiveness of its underlying cursor later in this chapter.
When a cursor uses a bind variable in a range predicate of a where clause, it is marked bind sensitive as follows:
SQL> var ln2 number;
SQL> exec :ln2 := 100;
SQL> select count(1) from t_acs where n2 <= :ln2;
COUNT(1)
----------
101
SQL> select * from table(dbms_xplan.display_cursor);
----------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
----------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | SORT AGGREGATE | | 1 | 3 |
|* 2 | TABLE ACCESS FULL| T_ACS | 397K| 1165K|
-----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("N2"<=:LN2)
SQL> select
sql_id
,child_number
,is_bind_sensitive
from
v$sql
where
sql_id = 'ct0yv82p15jdw';
SQL_ID CHILD_NUMBER IS_BIND_SENSITIVE
------------- ------------ -----------------
ct0yv82p15jdw 0 Y
As you can see, the corresponding child cursor is bind sensitive.
Thus, although column N2, when used in a range predicate, doesn’t need a histogram to be bind sensitive, it nevertheless requires having simple statistics gathered on it. To demonstrate, the following simply flushes the shared pool, deletes the statistics, and requeries using the same value of the bind variable:
SQL> alter system flush shared_pool;
SQL> exec dbms_stats.delete_table_stats(user,'t_acs');
SQL> select count(1) from t_acs where n2 <= :ln2;
SQL> select * from table(dbms_xplan.display_cursor);
SQL_ID ct0yv82p15jdw, child number 0
------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | SORT AGGREGATE | | 1 | 13 |
|* 2 | INDEX RANGE SCAN| T_ACS_I1 | 101 | 1313 |
------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("N2"<=:LN2)
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> select
sql_id
,child_number
,is_bind_sensitive
from
v$sql
where
sql_id = 'ct0yv82p15jdw';
SQL_ID CHILD_NUMBER IS_BIND_SENSITIVE
------------- ------------ -----------------
ct0yv82p15jdw 0 N
Notice that the child cursor 0 has not been marked bind sensitive in this case because column N2 does not have any statistics at all. The note about dynamic sampling should normally give you a clue as to why your cursor is not bind sensitive when you think it should be.
Bind Sensitiveness with Equality Predicate and Histogram
A cursor can also be marked bind sensitive when the column appears in an equality predicate and has a histogram, as shown in the following:
SQL> BEGIN
dbms_stats.gather_table_stats
(user
,'t_acs'
,method_opt => 'for all columns size auto'
,cascade => true
,estimate_percent => dbms_stats.auto_sample_size
);
END;
/
SQL> SELECT
column_name,
histogram
FROM user_tab_col_statistics
WHERE table_name = 'T_ACS'
AND column_name = 'N2';
COLUMN_NAME HISTOGRAM
------------------------------ ---------------
N2 FREQUENCY
SQL> select count(1) from t_acs where n2 = :ln2;
SQL> select * from table(dbms_xplan.display_cursor);
SQL_ID f2pmwazy1rnfd, child number 0
-------------------------------------
------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | SORT AGGREGATE | | 1 | 3 |
|* 2 | INDEX RANGE SCAN| T_ACS_I1 | 1372 | 4116 |
------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("N2"=:LN2)
SQL> select
sql_id
,child_number
,is_bind_sensitive
from
v$sql
where
sql_id = 'f2pmwazy1rnfd';
SQL_ID CHILD_NUMBER IS_BIND_SENSITIVE
------------- ------------ -----------------
f2pmwazy1rnfd 0 Y
Having gathered a histogram on column N2, a new query was executed using an equality predicate, and a bind-sensitive property of the corresponding child cursor was set to Y (yes).
Bind Sensitiveness with Partition Keys
Finally, a cursor can be marked bind sensitive when a SQL query uses a partition key in its predicate part. To demonstrate, the following creates a range partitioned table, populates it with appropriate data, and collects statistics without a histogram:
SQL> create table t_acs_part
(n1 number, n2 number)
partition by range (n2)
(partition p1 values less than (100)
,partition p2 values less than (1000)
,partition p3 values less than (10000)
,partition p4 values less than (100000)
,partition p5 values less than (1000000)
,partition p6 values less than (10000000)
);
SQL> BEGIN
for j in 1..1200150 loop
if j = 1 then
insert into t_acs_part values (j, 1);
elsif j>1 and j<=101 then
insert into t_acs_part values(j, 100);
elsif j>101 and j<=1101 then
insert into t_acs_part values (j, 1000);
elsif j>10001 and j<= 110001 then
insert into t_acs_part values(j,10000);
else
insert into t_acs_part values(j, 1000000);
end if;
end loop;
commit;
END;
/
SQL> BEGIN
dbms_stats.gather_table_stats
(user
,'t_acs_part'
,method_opt => 'for all columns size 1'
,cascade => true
,estimate_percent => dbms_stats.auto_sample_size
);
END;
/
SQL> SELECT
column_name,
histogram
FROM user_tab_col_statistics
WHERE table_name = 'T_ACS_PART'
AND column_name = 'N2';
COLUMN_NAME HISTOGRAM
------------ -----------
N2 NONE
A query against this table using the partition key (N2) in the where clause causes the cursor to be marked bind sensitive:
SQL> select count(1) from t_acs_part where n2 = :ln2;
COUNT(1)
----------
100
SQL> select * from table(dbms_xplan.display_cursor);
SQL_ID byztzuffb65n9, child number 0
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Pstart| Pstop |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | PARTITION RANGE SINGLE| | 100 | KEY | KEY |
|* 3 | TABLE ACCESS FULL | T_ACS_PART | 100 | KEY | KEY |
----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("N2"=:LN2)
SQL> select
sql_id
,child_number
,is_bind_sensitive
from
v$sql
where
sql_id = 'byztzuffb65n9';
SQL_ID CHILD_NUMBER IS_BIND_SENSITIVE
------------- ------------ -----------------
byztzuffb65n9 0 Y
In this case, too, the cursor has been marked bind sensitive.
ACS in Action
Now that we know about various prerequisites a SQL cursor should fulfill in order to be marked bind sensitive, let’s look at how Oracle generates multiple optimal execution plans for the same cursor using different bind variable values.
Let’s first see again how the data in column N2 is distributed in the nonpartitioned table t_acs:
SQL> select n2, count(1) from t_acs group by n2 order by 2;
N2 COUNT(1)
---------- ----------
1 1
100 100
1000 1000
10000 100000
1000000 1099049
The following demonstration has been carried out on the nonpartitioned table t_acs with skewed data distribution in column N2, as shown earlier. Since this column will be used in a query having an equality predicate, histograms have been gathered on column N2. This query uses two bind variable values in the equality predicate: 100 and 1000000. The value 100 favors an index range scan path, while the value 1000000 favors a full table scan. Let’s start the experiment:
SQL> alter system flush shared_pool;
SQL> exec :ln2 := 100
SQL> select count(1) from t_acs where n2 = :ln2;
COUNT(1)
----------
100
SQL> select * from table(dbms_xplan.display_cursor);
SQL_ID f2pmwazy1rnfd, child number 0
------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | SORT AGGREGATE | | 1 | 3 |
|* 2 | INDEX RANGE SCAN| T_ACS_I1 | 1372 | 4116 |
------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("N2"=:LN2)
As expected, the optimizer has generated a plan employing an index range scan for the bind variable value of 100.
Let’s now change the bind variable value to 1000000 for which we expect to have a full table scan plan:
SQL> exec :ln2 := 1000000
SQL> select count(1) from t_acs where n2 = :ln2;
COUNT(1)
----------
1099049
SQL> select * from table(dbms_xplan.display_cursor);
SQL_ID f2pmwazy1rnfd, child number 0
------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | SORT AGGREGATE | | 1 | 3 |
|* 2 | INDEX RANGE SCAN| T_ACS_I1 | 1372 | 4116 |
------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("N2"=:LN2)
Despite the numerous additional rows for the new value of the bind variable, we still use the same index range scan execution plan as we used earlier. Let’s check first whether this cursor is bind sensitive:
SQL> select
sql_id
,child_number
,is_bind_sensitive
,is_bind_aware
,is_shareable
from
v$sql
where
sql_id = 'f2pmwazy1rnfd';
SQL_ID CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE
------------- ------------ ----------------- ---------------- -------------
f2pmwazy1rnfd 0 Y N Y
As you can see, the cursor is bind sensitive but not bind aware yet. Let’s execute the same query again with the same bind variable value (1000000):
SQL> select count(1) from t_acs where n2 = :ln2;
COUNT(1)
----------
1099049
SQL> select * from table(dbms_xplan.display_cursor);
SQL_ID f2pmwazy1rnfd, child number 1
----------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
----------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | SORT AGGREGATE | | 1 | 3 |
|* 2 | TABLE ACCESS FULL| T_ACS | 1096K| 3212K|
----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("N2"=:LN2)
Finally, after two executions, Oracle Optimizer has generated a new execution plan that better suits the current bind variable value. If we look at the child cursor properties, we will find that a new child cursor 1 has been generated and is marked bind aware. Also, the existing child cursor 0 has been assigned a “not shareable” status signaling that it can be aged out away from the library cache under space pressure, as shown in the following:
SQL> select
sql_id
,child_number
,is_bind_sensitive
,is_bind_aware
,is_shareable
from
v$sql
where
sql_id = 'f2pmwazy1rnfd';
SQL_ID CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE
------------- ------------ ----------------- ---------------- -------------
f2pmwazy1rnfd 0 Y N N
f2pmwazy1rnfd 1 Y Y Y
Now that the cursor has been marked bind aware, if we assign the value of 1000 to the bind variable, which normally favors an index range scan, would Oracle produce the expected execution plan? Let’s see:
SQL> exec :ln2 := 1000
SQL> select count(1) from t_acs where n2 = :ln2;
COUNT(1)
----------
1000
SQL> select * from table(dbms_xplan.display_cursor);
SQL_ID f2pmwazy1rnfd, child number 2
------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | SORT AGGREGATE | | 1 | 3 |
|* 2 | INDEX RANGE SCAN| T_ACS_I1 | 2747 | 8241 |
------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("N2"=:LN2)
SQL> select
sql_id
,child_number
,is_bind_sensitive
,is_bind_aware
,is_shareable
from
v$sql
where
sql_id = 'f2pmwazy1rnfd';
SQL_ID CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE
------------- ------------ ----------------- ---------------- -------------
f2pmwazy1rnfd 0 Y N N
f2pmwazy1rnfd 1 Y Y Y
f2pmwazy1rnfd 2 Y Y Y
Indeed, Oracle has compiled a new bind-aware execution plan (cursor child number 2) based on an optimal index range scan access path.
The preceding demonstrates, in a nutshell, how the ACS feature works. When a cursor is bind sensitive for any of the reasons explained earlier (range predicate, equality predicate with histogram and partition key), all subsequent cursor executions will be monitored so that, after a warm-up period, the cursor becomes bind aware and an optimal plan is generated (or shared depending on the bind variable selectivity) for each bind variable value.
However, how many executions does the warm-up period need during which the cursor has to execute suboptimally before an optimal plan will be generated? This question is answered in the next section, which shows how these executions are monitored and what Oracle uses to mark a cursor bind aware.
ACS Bind-Awareness Monitoring
The /*+ bind_aware */ undocumented hint has the effect of making the cursor immediately bind aware, as shown via the following simple example:
SQL> create table t1
as select
rownum n1
,trunc((rownum -1)/3) n2
from dual
connect by level <=1e3;
SQL> exec dbms_stats.gather_table_stats(user, 't1');
SQL> select /*+ bind_aware */
count(*)
from t1
where n2 = :ln2;
COUNT(*)
----------
0
SQL> select * from table(dbms_xplan.display_cursor);
SQL_ID 5gz8nu7ru5gh0, child number 0
---------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
---------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | SORT AGGREGATE | | 1 | 4 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 4 |
---------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("N2"=:LN2)
SQL> SQL> select
sql_id
,child_number
,is_bind_sensitive
,is_bind_aware
,is_shareable
from
v$sql
where
sql_id = '5gz8nu7ru5gh0';
SQL_ID CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE
------------- ------------ ----------------- ---------------- ------------
5gz8nu7ru5gh0 0 Y Y Y
But, if you don’t use this hint, you may find that your initial cursor needs a certain number of executions before reaching the bind-awareness status. How many such initial executions does a cursor need in its warming-up period? And how are these executions monitored? Those are the two questions answered in this section.
Let’s continue using the t_asc table that was engineered with a special data pattern to make the monitoring explanation obvious. As shown earlier and reproduced here, the data distribution in column N2 is highly skewed:
SQL> select n2, count(1) from t_acs group by n2 order by 2;
N2 COUNT(1)
---------- ----------
1 1
100 100
1000 1000
10000 100000
1000000 1099049
BUCKET_ID and COUNT Relationship
Oracle provides three views that are used to monitor a cursor that is a candidate for an ACS 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
5 BIND_SET_HASH_VALUE NUMBER
6 PEEKED VARCHAR2(1)
7 EXECUTIONS NUMBER
8 ROWS_PROCESSED NUMBER
9 BUFFER_GETS NUMBER
10 CPU_TIME NUMBER
11 CON_ID NUMBER
The V$SQL_CS_STATISTICS view lists the statistics of the number of processed rows by the corresponding child cursor, the number of executions it has undergone so far, and the number of consumed buffers and CPU time. Although this view was reporting something in Oracle 11g, it seems it has not been used since Oracle 12c, since no rows were discovered when investigating the bind awareness in Oracle 12c. Even in Oracle 11g, the number of rows processed is not updated before a cursor is bind aware.
The second ACS monitoring view is V$SQL_CS_HISTOGRAM:
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
5 BUCKET_ID NUMBER
6 COUNT NUMBER
7 CON_ID NUMBER
The V$SQL_CS_HISTOGRAM view stores the number of cursor executions per bucket and is used to decide when it is time to mark a cursor bind aware, as we will detail later in this section.
The third and last ACS monitoring view is V$SQL_CS_SELECTIVITY:
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)
9 CON_ID NUMBER
The V$SQL_CS_SELECTIVITY view begins to be useful only when a cursor has been marked bind aware. It contains information about the selectivity of the bind variable’s values, including a low and high value range per child cursor.
The goal of this section is to define the relationship that exists between the number of rows processed by a child cursor and the couple (BUCKET_ID, COUNT) of the V$SQL_CS_HISTOGRAM view. The most important columns are highlighted in bold. Let’s first flush the shared pool and issue the same query using three different bind variable values: 100, 10000, and 1000000. For each execution, let’s check the content of the V$SQL_CS_HISTOGRAM monitoring view:
SQL> alter system flush shared_pool;
SQL> exec :ln2 := 100;
SQL> select count(1) from t_acs where n2 = :ln2;
COUNT(1)
----------
100
If we check the V$SQL_CS_STATISTICS view, it does not report any records in Oracle 12.1.0.1.0, although it did report something in Oracle 11g, as shown in the following:
SQL> select
child_number
,executions
,rows_processed
from v$sql_cs_statistics
where sql_id = 'f2pmwazy1rnfd' ;
no rows selected
It seems that this view has been kept only for backward compatibility, which is why it is not discussed further. Even in Oracle 11g, this view did not report a correct value in the ROWS_PROCESSED column prior to the corresponding cursor becoming bind aware.
The V$SQL_CS_HISTOGRAM view, however, is showing interesting information. The child cursor 0, used to honor the query, has three BUCKET_IDs: 0, 1, and 2. And it can be seen that for the child cursor 0, which has processed 100 rows, the COUNT of BUCKET_ID 0 has been incremented to 1:
SQL> select
child_number
,bucket_id
,count
from
v$sql_cs_histogram
where sql_id = 'f2pmwazy1rnfd' ;
CHILD_NUMBER BUCKET_ID COUNT
------------ ---------- ----------
0 0 1 → incremented because processed rows <1000
0 1 0
0 2 0
If we execute the same query twice more, the COUNT of the BUCKET_ID 0 increments to 3, as follows:
SQL> select count(1) from t_acs where n2 = :ln2;
SQL> select count(1) from t_acs where n2 = :ln2;
SQL> select
child_number
,bucket_id
,count
from
v$sql_cs_histogram
where sql_id = 'f2pmwazy1rnfd' ;
CHILD_NUMBER BUCKET_ID COUNT
------------ ---------- ----------
0 0 3 → incremented because processed rows <1000
0 1 0
0 2 0
Now, we will change the bind variable value to 10000 with 100,000 rows, run the same query again, and check the contents of V$SQL_CS_HISTOGRAM. This time, the COUNT of BUCKET_ID 1 is incremented to 1:
SQL> exec :ln2 := 10000;
SQL> select count(1) from t_acs where n2 = :ln2;
COUNT(1)
----------
100000
SQL> select
child_number
,bucket_id
,count
from
v$sql_cs_histogram
where sql_id = 'f2pmwazy1rnfd' ;
CHILD_NUMBER BUCKET_ID COUNT
------------ ---------- ----------
0 0 3
0 1 1 → incremented because 1000<=processed rows <1e6
0 2 0
Finally, if we execute the same query using the bind variable value 1000000, it returns 1099049 rows. If we check V$SQL_CS_HISTOGRAM, we will see that Oracle increments the COUNT of BUCKET_ID 2:
SQL> exec :ln2 := 1000000;
SQL> select count(1) from t_acs where n2 = :ln2;
COUNT(1)
----------
1099049
SQL> select
child_number
,bucket_id
,count
from
v$sql_cs_histogram
where sql_id = 'f2pmwazy1rnfd' ;
CHILD_NUMBER BUCKET_ID COUNT
------------ ---------- ----------
0 0 3
0 1 1
0 2 1 → incremented because processed rows >=1e6
The preceding experiments show us clearly that Oracle is using the following heuristic methods to increment the COUNT of the three BUCKET_IDs belonging to any compiled child cursor:
If the number of processed rows is between 0 and 1000, then increment the COUNT of BUCKET_ID 0.
If the number of processed rows is between 1000 and 1 million, then increment the COUNT of BUCKET_ID 1.
If the number of processed rows is greater than 1 million, then increment the COUNT of BUCKET_ID 2.
Marking Cursors Bind Aware
The relationship previously discussed (BUCKET_ID, COUNT) has paved the way to uncovering the secret sauce Oracle uses to mark a cursor bind aware. Let’s continue using the pair (BUCKET_ID, COUNT) to figure out that secret sauce. The following sections look at three test cases:
Case 1 looks at two adjacent buckets that have a COUNT greater than 0, while the COUNT of the remaining bucket is equal to 0.
Case 2 involves two nonadjacent BUCKET_IDs (BUCKET_ID 0 and 2) that have a COUNT greater than 0, while the COUNT of BUCKET_ID 1 is equal to 0.
Case 3 provides a scenario in which all BUCKET_IDs have a COUNT greater than 0.
Case 1: Adjacent Buckets (0 and 1 or 1 and 2) with a COUNT Greater Than Zero
This is the simplest case. We execute the initial query five times using the first bind variable value 100 and then five more times using the second bind variable value 10000. The goal is to observe when the cursor is marked bind aware.
SQL> alter system flush shared_pool;
SQL> exec :ln2 := 100
SQL> select count(1) from t_acs where n2 = :ln2;
COUNT(1)
---------
100
SQL> –- repeat 4 times
SQL> select
child_number
,bucket_id
,count
from
v$sql_cs_histogram
where sql_id = 'f2pmwazy1rnfd' ;
CHILD_NUMBER BUCKET_ID COUNT
------------ ---------- ----------
0 0 5 → incremented 5 times
0 1 0
0 2 0
SQL> exec :ln2 := 10000
SQL> select count(1) from t_acs where n2 = :ln2;
COUNT(1)
----------
100000
SQL> –- repeat 4 times
SQL> select
child_number
,bucket_id
,count
from
v$sql_cs_histogram
where sql_id = 'f2pmwazy1rnfd' ;
CHILD_NUMBER BUCKET_ID COUNT
------------ ---------- ----------
0 0 5
0 1 5 → incremented 5 times
0 2 0
After five executions using a different bind variable value, which processes a high number of records and causes the COUNT of BUCKET_ID 1 to rise to 5, we are still sharing the same child cursor 0 (only one child cursor in V$SQL_CS_HISTOGRAM). Let’s try a sixth execution with the current bind variable value of 10000:
SQL> select count(1) from t_acs where n2 = :ln2;
COUNT(1)
----------
100000
The sixth execution at BUCKET_ID 1 (:ln2 = 10000) has made the cursor bind aware, and a new execution plan (CHILD_NUMBER 1) is compiled as follows:
SQL> select
child_number
,bucket_id
,count
from
v$sql_cs_histogram
where sql_id = 'f2pmwazy1rnfd' ;
CHILD_NUMBER BUCKET_ID COUNT
------------ ---------- ----------
1 0 0
1 1 1
1 2 0
0 0 5 → COUNT of BUCKET_ID 0 = 5
0 1 5 → COUNT of BUCKET_ID 1 = 5
0 2 0
This is confirmed via the following select against v$sql:
SQL> select
sql_id
,child_number
,is_bind_sensitive
,is_bind_aware
,is_shareable
from
v$sql
where
sql_id = 'f2pmwazy1rnfd';
SQL_ID CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE
------------- ------------ ----------------- ------------- -------------
f2pmwazy1rnfd 0 Y N N
f2pmwazy1rnfd 1 Y Y Y
The same observation was found when a similar experiment was conducted with adjacent buckets 1 and 2 having a COUNT greater than 0, while the COUNT of bucket 0 was equal to 0.
Therefore, the secret sauce for the first case considered is: When the COUNT of a BUCKET_ID reaches the COUNT of its neighboring BUCKET_ID, the next execution marks the original cursor bind aware and a new child cursor is compiled. This is true provided the COUNT of the third BUCKET_ID equals 0.
Case 2: Distant Buckets 0 and 2 with a COUNT Greater Than Zero
We start this second experiment by executing the same query nine times (nine is a random number, which we can replace with any other number without altering the final result) using the bind variable value 100 (favoring bucket 0). Then we change the bind variable to 1000000 (favoring bucket 2) and requery, checking after each execution whether the cursor has been marked bind aware:
SQL> alter system flush shared_pool;
SQL> exec :ln2 := 100
SQL> select count(1) from t_acs where n2 = :ln2;
COUNT(1)
----------
100
SQL> repeat this 8 times
SQL> select
child_number
,bucket_id
,count
from
v$sql_cs_histogram
where sql_id = 'f2pmwazy1rnfd' ;
CHILD_NUMBER BUCKET_ID COUNT
------------ ---------- ----------
0 0 9 → incremented 9 times = 9 executions
0 1 0
0 2 0
-- change the bind variable value
SQL> exec :ln2 := 1000000
SQL> select count(1) from t_acs where n2 = :ln2;
COUNT(1)
----------
1099049
SQL> repeat this query 2 times
After three executions using a bind variable value of 1000000, which processes more than a million rows and causes an increase in the COUNT of the distant BUCKET_ID 2), we are still sharing the same child cursor 0:
SQL> select
child_number
,bucket_id
,count
from
v$sql_cs_histogram
where sql_id = 'f2pmwazy1rnfd' ;
CHILD_NUMBER BUCKET_ID COUNT
------------ ---------- ----------
0 0 9 → ceil(9/3) = 3 = COUNT of BUCKET_ID 2
0 1 0
0 2 3 → incremented 3 times = 3 executions
Let’s try a fourth execution using this current bind variable value of 1000000:
SQL> select count(1) from t_acs where n2 = :ln2;
COUNT(1)
----------
1099049
SQL> select
child_number
,bucket_id
,count
from
v$sql_cs_histogram
where sql_id = 'f2pmwazy1rnfd' ;
CHILD_NUMBER BUCKET_ID COUNT
------------ ---------- ----------
1 0 0
1 1 0 → bucket 1 not involved
1 2 1
0 0 9 → bucket 0 incremented 9 times: ceil(9/3)= 3
0 1 0
0 2 3 → bucket 2 incremented 3 times: 3 = ceil (9/3)
That’s it! The fourth execution at BUCKET_ID 2 has marked the cursor bind aware, and a new execution plan (CHILD_NUMBER 1) has been compiled.
The preceding experiment uses several different numbers of initial executions using bind variables favoring BUCKET_IDs 0 and 2. We started by incrementing the BUCKET_ID 0 and then BUCKET_ID 2 (and vice versa). All those experiments explain that the secret sauce used by Oracle to mark a cursor bind aware in this case is: when the COUNT of BUCKET_ID 2 reaches ceil (COUNT of BUCKET_ID 0/3), then the next execution at BUCKET_ID 2 will mark the cursor bind aware and compile a new execution plan. This is true provided the COUNT of BUCKET_ID 1 remains at its initial value of 0.
Case 3: All Buckets with a COUNT Greater Than Zero
In this last series of tests, we’ll try to figure out how Oracle decides that it is time to compile a new execution plan when the COUNTs of all buckets are greater than 0. Despite the huge number of tests carried out in this case, we won’t be able to reliably derive the secret sauce being used by Oracle to mark a cursor bind aware where all the buckets have a COUNT exceeding 0. Nevertheless, let’s see what we can learn from our observations.
Let’s start with the simple example where the COUNTs of all buckets are greater than 0:
SQL> select
child_number
,bucket_id
,count
from
v$sql_cs_histogram
where sql_id = 'f2pmwazy1rnfd';
CHILD_NUMBER BUCKET_ID COUNT
------------ ---------- ----------
0 0 3
0 1 1
0 2 1
It goes without saying that to get the preceding results, we flushed the shared pool, executed the query at BUCKET_ID 0 (:ln2 = 100) three times, once at BUCKET_ID 1 (:ln2 = 10000), and finally once at BUCKET_ID 2 (:ln2 = 1000000).
You can see from this situation that any execution at any BUCKET_ID (i.e., using any value among the three bind variables’ values) will mark the cursor bind aware:
SQL> exec :ln2 := 100
SQL> select count(1) from t_acs where n2 = :ln2;
CHILD_NUMBER BUCKET_ID COUNT
------------ ---------- ----------
1 0 1 → when the 6th execution is for :ln2=100
1 1 0
1 2 0
0 0 3
0 1 1
0 2 1
SQL> exec :ln2 := 10000
SQL> select count(1) from t_acs where n2 = :ln2;
CHILD_NUMBER BUCKET_ID COUNT
------------ ---------- ----------
1 0 0
1 1 1 → when the 6th execution is for :ln2 = 10000
1 2 0
0 0 3
0 1 1
0 2 1
SQL> exec :ln2 := 1000000
SQL> select count(1) from t_acs where n2 = :ln2;
CHILD_NUMBER BUCKET_ID COUNT
------------ ---------- ----------
1 0 0
1 1 0
1 2 1 → when the 6th execution is for:ln2 =1000000
0 0 3
0 1 1
0 2 1
Unfortunately, we can’t derive the bind-aware secret sauce in this case.
In the following case, we manage to have 11 executions at BUCKET_ID 0 (:ln2 = 100), 4 executions at BUCKET_ID 2 (:ln2 = 10000), and 3 executions at BUCKET_ID 3 (:ln2 = 1000000) in that order of execution:
CHILD_NUMBER BUCKET_ID COUNT
------------ ---------- ----------
0 0 11
0 1 4
0 2 3
Before reaching this bucket COUNT distribution, we would need to take great care to avoid the cases mentioned earlier. For example, had we started with 11 executions at BUCKET_ID 0 and then executed the query five (> ceil (11/3)) times at the distant BUCKET_ID 2, then the cursor would have been marked bind aware even before we could have attempted an execution at BUCKET_ID 1.
This next execution at BUCKET_ID 2 makes the cursor bind aware, and a new execution plan (CHILD_NUMBER 1) is generated as follows:
SQL> exec :ln2 := 1000000
SQL> select count(1) from t_acs where n2 = :ln2;
SQL> select
child_number
,bucket_id
,count
from
v$sql_cs_histogram
where sql_id = 'f2pmwazy1rnfd' ;
CHILD_NUMBER BUCKET_ID COUNT
------------ ---------- ----------
1 0 0
1 1 0
1 2 1 → execution done at this bucket
0 0 11
0 1 4
0 2 3
Again, we are unable to derive the bind-aware secret sauce in this case. However, in the blog article “Bind aware secret sauce (again)” (https://hourim.wordpress.com/2015/09/05/bind-aware-secret-sauce-again), you can find a Procedural Language/Structured Query Language (PL/SQL) function, fv_will_cs_be_bind_aware, which might give you an indication of when a cursor is marked bind when the COUNTs of all buckets are greater than 0. For example, when this function is applied to the preceding two situations, it gives the following:
SQL> select fv_will_cs_be_bind_aware(3,1,1) IS_BIND_AWARE from dual;
IS_BIND_AWARE
-------------
Y
SQL> select fv_will_cs_be_bind_aware(11,4,3) IS_BIND_AWARE from dual;
IS_BIND_AWARE
-------------
Y
The Bind-Aware Cursor
As we have seen, before a cursor becomes bind aware and optimal execution plans are generated on the basis of the selectivity of the bind variable values, it has to go wrong (share the initial plan whatever the bind variable value may be). This warm-up period can dramatically vary depending on the number of executions done at the three different buckets. We can’t avoid this warm-up period. Once the cursor becomes bind aware, the optimal plans are generated following a new algorithm that has nothing to do with the (COUNT, BUCKET_ID) tandem exposed extensively previously. Of course, this is true provided that the parent–child cursors are still in the shared pool.
Once a cursor is bind aware, a record is inserted into the V$SQL_CS_SELECTIVITY dynamic view for the first time, as shown in the following, using the simplest case of two adjacent BUCKET_IDs:
SQL> alter system flush shared_pool;
SQL> exec :ln2 := 100
SQL> select count(1) from t_acs where n2 = :ln2;
SQL> exec :ln2 := 10000
SQL> select count(1) from t_acs where n2 = :ln2;
SQL> select
child_number
,predicate
,low
,high
from
v$sql_cs_selectivity
where
sql_id = 'f2pmwazy1rnfd';
no rows selected
The no rows selected line indicates that the cursor is still not bind aware. Let’s execute the query once more and check the contents of the V$SQL_CS_SELECTIVITY view:
SQL> print :ln2
LN2
----------
10000
SQL> select count(1) from t_acs where n2 = :ln2;
SQL> select
child_number
,predicate
,low
,high
from
v$sql_cs_selectivity
where
sql_id = 'f2pmwazy1rnfd';
CHILD_NUMBER PREDICATE LOW HIGH
------------ ---------------------------------------- ---------- ----------
1 =LN2 0.074579 0.091152
When this view gets populated, it indicates that the cursor CHILD_NUMBER 1 has been marked bind aware and from now on, for every execution, using the extended cursor sharing layer code, Oracle Optimizer will peek at the bind variable value and check its selectivity. If the selectivity of this bind variable value is found to be between the LOW and HIGH columns values, it will share the execution plan represented by the existing CHILD_NUMBER 1. Otherwise, a new plan with a new range of selectivity (low and high) is generated as follows:
SQL> exec :ln2 := 100
SQL> select count(1) from t_acs where n2 = :ln2;
COUNT(1)
----------
100
SQL> select
child_number
,predicate
,low
,high
from
v$sql_cs_selectivity
where
sql_id = 'f2pmwazy1rnfd';
CHILD_NUMBER PREDICATE LOW HIGH
------------ ---------------------------------------- ---------- ----------
2 =LN2 0.000361 0.091152
1 =LN2 0.074579 0.091152
Notice that a new plan (CHILD_NUMBER 2) has been generated, and its LOW and HIGH values have been updated to reflect the selectivity of the new peeked bind variable (:ln2 = 100).
Let’s now use the extreme bind variable value, which favors a full table scan and increments the COUNT of BUCKET_ID 2 (remember that at this stage the [BUCKET_ID, COUNT] tandem does not play any role):
SQL> exec :ln2 := 1000000
SQL> select count(1) from t_acs where n2 = :ln2;
COUNT(1)
----------
1099049
SQL> select
child_number
,predicate
,low
,high
from
v$sql_cs_selectivity
where
sql_id = 'f2pmwazy1rnfd';
CHILD_NUMBER PREDICATE LOW HIGH
------------ ---------------------------------------- ---------- ----------
3 =LN2 0.823886 1.006972
2 =LN2 0.000361 0.091152
1 =LN2 0.074579 0.091152
Note again that a new plan (CHILD_NUMBER 3) has been generated and its LOW and HIGH values have been updated accordingly to reflect the selectivity of the new peeked bind variable (:ln2 = 1000000).
The Oracle documentation says that when the selectivity for the peeked bind variable value is not found within an existing low and high value range, a new plan is generated. If this new plan is found to be equivalent to an existing one, the LOW and HIGH values of that existing plan will be updated without creating a new bind-aware plan. We won’t test this case in this chapter. It is, nevertheless, important to emphasize here that there are two stages in the ACS feature:
Stage 1: The cursor is not marked bind aware.
Stage 2: The cursor is marked bind aware.
It is during the first stage that ACS layer code is in action using the (BUCKET_ID, COUNT) relationship to mark a cursor bind aware. The extended cursor sharing layer code kicks in during the second stage, which peeks at each bind variable, gets its selectivity, and checks whether a child cursor exists within the selectivity range of the specified bind value. If it exists, the child cursor is shared. Otherwise, a new child cursor is generated with a new range of selectivity. If the new child cursor (in fact, the new execution plan) is found to be equivalent to an existing one, then the new selectivity will be merged with that of the existing equivalent child cursor.
Finally, Figure 4.2 summarizes the ACS–ECS triggering algorithm.
A Practical Case
Earlier, this chapter proclaimed that ACS is an answer to sharing cursors and optimizing SQL. The ACS principles we demonstrated, when applied against gentle models like the one used throughout this chapter, seem perfect—that is, until we start troubleshooting the following performance issue in a real-life running system. We realize, surprisingly, that the root cause of this performance issue is the ACS feature.
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 – Production
SQL> select
sql_id
,count(1)
from
v$sql
where executions < 2
group by sql_id
having count(1) > 10
order by 2 desc;
SQL_ID COUNT(1)
------------- ----------
7zwq7z1nj7vga 44217
39ax31acw29z6 75
0v3dvmc22qnam 29
412j04p609svj 25
5s34t44u10q4g 17
c8gnrhxma4tas 16
g8m7zdgak6pmm 16
gjm43un5cy843 16
6wdu577suw74s 15
23nad9x295gkf 14
848dyu9288c3h 14
6wm3n4d7bnddg 14
2am60vd2kw8ux 14
0ctk7jpux5chm 14
3x13xht9dh5c3 14
gdn3ysuyssf82 13
gg17hgzmtttbu 13
53ps5ua5ms44q 13
1z2rnd9bubah9 13
What caused the first SQL_ID (7zwq7z1nj7vga) to have 44,217 versions in V$SQL? An odd phenomenon is happening in this system, considering that the application is using bind variables and therefore should normally reuse cursors and share resources. The first and obvious thing to do in this case is figure out why sharing of child cursors is preempted. Tanel Poder script, nonshared, when applied to this particular SQL_ID, gives us the following result:
SQL> @nonshared 7zwq7z1nj7vga
Show why existing SQL child cursors were not reused (V$SQL_SHARED_CURSOR)...
-----------------
SQL_ID : 7zwq7z1nj7vga
ADDRESS : 000000406DBB30F8
CHILD_ADDRESS : 00000042CE36F7E8
CHILD_NUMBER : 0
BIND_EQUIV_FAILURE : Y
REASON :<ChildNode><ChildNumber>0</ChildNumber><ID>40</ID>
<reason>Bindmismatch(33)</reason><size>2x4</size>
<init_ranges_in_first_pass>0</init_ranges_in_first_pass>
<selectivity>1097868685</selectivity>
</ChildNode>
-----------------
SQL_ID : 7zwq7z1nj7vga
ADDRESS : 000000406DBB30F8
CHILD_ADDRESS : 00000045B5C5E478
CHILD_NUMBER : 1
BIND_EQUIV_FAILURE : Y
REASON :<ChildNode><ChildNumber>1</ChildNumber><ID>40</ID>
<reason>Bindmismatch(33)</reason><size>2x4</size>
<init_ranges_in_first_pass>0</init_ranges_in_first_pass>
<selectivity>915662630</selectivity>
</ChildNode>
-----------------
SQL_ID : 7zwq7z1nj7vga
ADDRESS : 000000406DBB30F8
CHILD_ADDRESS : 00000038841E2868
CHILD_NUMBER : 2
BIND_EQUIV_FAILURE : Y
REASON :<ChildNode><ChildNumber>2</ChildNumber><ID>40</ID>
<reason>Bindmismatch(33)</reason><size>2x4</size>
<init_ranges_in_first_pass>0</init_ranges_in_first_pass>
<selectivity>163647208</selectivity>
</ChildNode>
-----------------
SQL_ID : 7zwq7z1nj7vga
ADDRESS : 000000406DBB30F8
CHILD_ADDRESS : 00000038841E2708
CHILD_NUMBER : 3
BIND_EQUIV_FAILURE : Y
REASON :<ChildNode><ChildNumber>3</ChildNumber><ID>40</ID>
<reason>Bindmismatch(33)</reason><size>2x4</size>
<init_ranges_in_first_pass>0</init_ranges_in_first_pass>
<selectivity>4075662961</selectivity>
</ChildNode>
.../...
-----------------
SQL_ID : 7zwq7z1nj7vga
ADDRESS : 000000406DBB30F8
CHILD_ADDRESS : 00000042DD3D7208
CHILD_NUMBER : 97
BIND_EQUIV_FAILURE : Y
REASON :<ChildNode><ChildNumber>97</ChildNumber><ID>40</ID>
<reason>Bindmismatch(33)</reason><size>2x4</size>
<init_ranges_in_first_pass>0</init_ranges_in_first_pass>
<selectivity>3246589452</selectivity>
</ChildNode>
-----------------
SQL_ID : 7zwq7z1nj7vga
ADDRESS : 000000406DBB30F8
CHILD_ADDRESS : 00000042DD3D70A8
CHILD_NUMBER : 98
BIND_EQUIV_FAILURE : Y
REASON :<ChildNode><ChildNumber>98</ChildNumber><ID>40</ID>
<reason>Bindmismatch(33)</reason><size>2x4</size>
<init_ranges_in_first_pass>0</init_ranges_in_first_pass>
<selectivity>3246589452</selectivity>
</ChildNode>
-----------------
SQL_ID : 7zwq7z1nj7vga
ADDRESS : 000000406DBB30F8
CHILD_ADDRESS : 00000045B5C5E5D8
CHILD_NUMBER : 99
BIND_EQUIV_FAILURE : Y
REASON :<ChildNode><ChildNumber>99</ChildNumber><ID>40</ID>
<reason>Bindmismatch(33)</reason><size>2x4</size>
<init_ranges_in_first_pass>0</init_ranges_in_first_pass>
<selectivity>3246589452</selectivity>
</ChildNode>
There are 100 (0–99) nonshared child cursors due to BIND_EQUIV_FAILURE:
SQL> select
count(1)
from
v$sql_shared_cursor
where SQL_ID = '7zwq7z1nj7vga';
COUNT(1)
----------
45125
SQL> select
count(1)
from
v$sql_shared_cursor
where sql_id = '7zwq7z1nj7vga'
and BIND_EQUIV_FAILURE = 'Y';
COUNT(1)
----------
45121
In almost all (99%) of the cases, the reason sharing is preempted for SQL_ID 7zwq7z1nj7vga is BIND_EQUIV_FAILURE. The official Oracle documentation defines this reason: “The bind value’s selectivity does not match that used to optimize the existing child cursor.” This definition gives a clue to the reason Oracle is failing to share an existing cursor. As explained earlier in this chapter, there are two layers of code implemented to manage sharing of cursors: the ACS layer code is responsible for marking a cursor bind aware and the extended cursor sharing (ECS) layer code is responsible for attaching (or compiling) a child cursor corresponding to the bind variable selectivity. We observed that when the cursor becomes bind aware, the ECS layer code kicks in for each execution of the underlying query. It peeks at the bind variables (there might be several) and matches their selectivity with the one in the V$SQL_CS_SELECTIVITY dynamic view.
Let’s see what we can find in this selectivity table for the SQL_ID 7zwq7z1nj7vga:
SQL> select
count(1)
from
v$sql_cs_selectivity
where
sql_id = '7zwq7z1nj7vga';
COUNT(1)
----------
16,847,320
This table contains a huge number of records, which might dramatically affect the execution of the initial query. Each time we run the query, Oracle has to issue a select, behind the scenes, against this huge V$SQL_CS_SELECTIVITY table in order to check if any existing child cursor already covers the selectivity of the bind variables.
What makes the situation even more dramatic is that, among those 100 child cursors, there are only four distinct execution plans, as the following shows:
SQL> select * from table(dbms_xplan.display_awr('7zwq7z1nj7vga'));
Plan hash value: 587060143
---------------------------------------------------------------
| Id | Operation | Name | Rows |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | TABLE ACCESS BY INDEX ROWID| MHO_TABLES_ACS | 159K|
| 2 | INDEX RANGE SCAN | TABL_ACS_INDX1 | 159K|
---------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=4)
Plan hash value: 1114469665
-------------------------------------------------------------------
| Id | Operation | Name | Rows |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | TABLE ACCESS BY INDEX ROWID| MHO_TABLES_ACS | 1 |
| 2 | INDEX RANGE SCAN | TABL_ACS_INDX_PK | 1 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=4)
--------------------
Plan hash value: 2117864734
-------------------------------------------------------------------
| Id | Operation | Name | Rows |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | TABLE ACCESS BY INDEX ROWID| MHO_TABLES_ACS | 1 |
| 2 | INDEX RANGE SCAN | TABL_ACS_INDX2 | 1 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=4)
--------------------
Plan hash value: 3054136074
--------------------------------------------------------------------
| Id | Operation | Name | Rows |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | TABLE ACCESS BY INDEX ROWID| MHO_TABLES_ACS | 159K|
| 2 | INDEX RANGE SCAN | TABL_ACS_INDX3 | 159K|
--------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=4)
Finally, let’s look at the SQL code of this query, which might give us a clue why we have been in such a situation:
SELECT
{list_of_columns}
FROM
mho_tables_acs
WHERE
col_1 =:1
AND col_2 =:2
AND col_3 =:3
AND col_4 =:4
AND col_5 =:5
AND col_6 =:6
AND col_7 =:7
AND col_8 =:8
AND col_9 >=:9;
This query shows us two important points we have not investigated. First, will a cursor using more than 14 bind variables be bind aware and therefore not be affected by ACS? Second, will a query like this be handled with a bind-sensitive cursor if all nine columns do not possess a histogram but the unique range predicate applied on COL_9 suffices to mark a cursor bind sensitive?
Summary
Beginning with Oracle Database 11g, ACS makes it possible for a SQL statement using bind variables to have multiple optimal execution plans. Certain prerequisites need to be fulfilled for ACS to kick in. More important, Oracle monitors the cursor to decide when it is to be marked bind aware and subsequently generates a new plan. Finally, in this “sharing and optimizing” conflict, there are two layers of code playing different roles: while the ACS layer is responsible for marking a cursor bind aware, the ECS layer peeks at the bind variables and, based on their selectivity, decides whether to share an existing plan or generate a new one.
There is, however, a limitation of ACS within PL/SQL that was intentionally not discussed in this chapter in order to keep it concise and reserved only for SQL queries.