Key Concepts - Troubleshooting Oracle Performance - page 32

Of course, as shown in the following example, if the first execution takes place with the value 10, the query
optimizer chooses an execution plan with an index scan—and that, once more, occurs for both queries. Note that to
avoid sharing the cursor used for the previous example, the queries were written in lowercase letters.
SQL> EXECUTE :id := 10 ;
SQL> select count(pad) from t where id < :id;
COUNT(PAD)
----------
9
-----------------------------------------------------
| Id | Operation | Name | Rows |
-----------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 9 |
| 3 | INDEX RANGE SCAN | T_PK | 9 |
-----------------------------------------------------
SQL> EXECUTE :id := 990 ;
SQL> select count(pad) from t where id < :id;
COUNT(PAD)
----------
989
-----------------------------------------------------
| Id | Operation | Name | Rows |
-----------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 9 |
| 3 | INDEX RANGE SCAN | T_PK | 9 |
-----------------------------------------------------
It's essential to understand that as long as the cursor remains in the library cache and can be shared, it will be
reused. This occurs regardless of the efficiency of the execution plan related to it.
To solve this problem, as of version 11.1, the database engine uses a new feature called adaptive cursor sharing
(also known as bind-aware cursor sharing ). Its purpose is to automatically recognize when the reutilization of an
already available cursor leads to inefficient executions. To understand how this feature works, let's start by looking at
some information provided by the v$sql view about it. The following new columns are available as of version 11.1:
is_bind_sensitive indicates not only whether bind variable peeking was used to generate
the execution plan but also whether adaptive cursor sharing might be considered. If this is
the case, the column is set to Y; otherwise, it's set to N.
is_bind_aware indicates whether the cursor is using adaptive cursor sharing. If yes,
the column is set to Y; if not, it's set to N.
is_shareable indicates whether the cursor can be shared. If it can, the column is set to Y;