When you find yourself very often typing the same set of sql statements you will end up by writing a sql script in which will be collected those sql statements. As such, you will have avoided repetitive sql typing.
When you find yourself very often writing the same set of phrases to explain an Oracle concept you will end up by writing a blog article in which will be collected those phrases. As such, you will be referring to that blog article instead of re-typing the same phrases.
When it is question of pros and cons of using literals, bind variables and cursor sharing, I believe, I’ve reached the point, where writing down my corresponding repetitive phrases become necessary. So, please, take this article as a summary for me and for those who want to deepen a little bit their knowledge of these interacting concepts.
Let’s start now.
If you want to develop a non scalable and a non available Oracle application running slowly, then you have only one thing to do: “don’t use bind variable’’. Oracle architecture is so that sharing memory (SGA-Library cache) represents a crucial aspect Oracle engineers have to know and to master. However, as it is always the case with Oracle database, despite this feature is very important it has few drawbacks that are worth to be known. While bind variables allow sharing of parent cursors (SQL code) they also allow sharing of execution plans (child cursor). Sharing the same execution plan for different bind variables is not always optimal as far as different bind variables can generate different data volume. This is why Oracle introduces bind variable peeking feature which allows Oracle to peek at the bind variable value and give it the best execution plan possible. However, bind variable peeking occurs only at hard parse time which means as far as the query is not hard parsed it will share the same execution plan that corresponds to the last hard parsed bind variable. In order to avoid such situation Oracle introduces in its 11gR2 release, Adaptive Cursor Sharing allowing Oracle to adapt itself to the bind variable when necessary without having to wait for a hard parse of the query.
1.Using Literal variables
SQL> select /*+ literal_variable */ count(*), max(col2) from t1 where flag = 'Y1';
COUNT(*) MAX(COL2)
---------- -----------------------------------------
1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Plan hash value: 761479741
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 30 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("FLAG"='Y1')
SQL> select /*+ literal_variable */ count(*), max(col2) from t1 where flag = 'N1';
COUNT(*) MAX(COL2)
---------- ---------------------------------------------
49998 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Plan hash value: 3693069535
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 216 (100)| |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
|* 2 | TABLE ACCESS FULL| T1 | 55095 | 1614K| 216 (2)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"='N1')
SQL> select /*+ literal_variable */ count(*), max(col2) from t1 where flag = 'Y2';
COUNT(*) MAX(COL2)
---------- -----------------------------------------
1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Plan hash value: 761479741
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 30 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("FLAG"='Y2')
SQL> select /*+ literal_variable */ count(*), max(col2) from t1 where flag = 'N2';
COUNT(*) MAX(COL2)
---------- --------------------------------------------------
49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Plan hash value: 3693069535
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 216 (100)| |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
|* 2 | TABLE ACCESS FULL| T1 | 55251 | 1618K| 216 (2)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"='N2')
I executed the same query using 4 different hard coded variables. For each literal variable I got the adequat execution plan. That’s very nice from this point of view. But, if I consult the library cache I will see the damage I have caused
SQL> select sql_id, substr(sql_text,1,30), executions 2 from v$sql 3 where sql_text like '%literal_variable%' 4 and sql_text not like '%v$sql%'; SQL_ID SUBSTR(SQL_TEXT,1,30) EXECUTIONS ------------- ------------------------------ ---------- axuhh2rjx0jc7 select /*+ literal_variable */ 1---> sql code is not re-executed c6yy4pad9fd0x select /*+ literal_variable */ 1---> sql code is not shared 45h3507q5r318 select /*+ literal_variable */ 1---> the same sql seems for the CBO 76q7p8q473cdq select /*+ literal_variable */ 1---> to be a new sql statement
There is 1 record for each execution. If you repeat the same sql statement changing only the value of the flag you will end up by having as much as records in v$sql as the number of different literal values you will used.
2. Using bind variables
So what will I point out if I prefer using bind variables instead of these literal ones?
SQL> var n varchar2(2);
SQL> exec :n := ’Y1’ ---> bind favoring index range scan
SQL> select /*+ bind_variable */ count(*), max(col2) from t1 where flag = :n;
-------------------------------------
SQL_ID 8xujk8a1g65x6, child number 0
-------------------------------------
Plan hash value: 761479741
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 54 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 54 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("FLAG"=:N)
SQL> exec :n := ’N1’ ---> bind favoring full table scan
SQL> select /*+ bind_variable */ count(*), max(col2) from t1 where flag = :n;
-------------------------------------
SQL_ID 8xujk8a1g65x6, child number 0
-------------------------------------
Plan hash value: 761479741
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 54 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 54 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("FLAG"=:N)
SQL> exec :n := ’Y2’ ---> bind favoring index range scan
SQL> select /*+ bind_variable */ count(*), max(col2) from t1 where flag = :n;
-------------------------------------
SQL_ID 8xujk8a1g65x6, child number 0
-------------------------------------
Plan hash value: 761479741
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 54 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 54 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("FLAG"=:N)
SQL> exec :n := ’N2’ ---> bind favoring table scan
SQL> select /*+ bind_variable */ count(*), max(col2) from t1 where flag = :n;
-------------------------------------
SQL_ID 8xujk8a1g65x6, child number 0
-------------------------------------
Plan hash value: 761479741
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 54 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 54 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("FLAG"=:N)
Have you already pointed out something very clear? They (4 selects) share the same execution plan which is the plan that was generated for the first hard parsed bind variable ‘Y1′. As far as this one favors an index range scan access it shares that plan with all successive identical queries having the same sql_id. But spot with me how the library cache looks now very attractive
SQL> select sql_id, substr(sql_text,1,30), executions 2 from v$sql 3 where sql_text like '%bind_variable%' 4 and sql_text not like '%v$sql%'; SQL_ID SUBSTR(SQL_TEXT,1,30) EXECUTIONS ------------- ------------------------------ ---------- 8xujk8a1g65x6 select /*+ bind_variable */ co 4 ---> one sql code and 4 executions
Let me, at this particular step, make a break point.
- SQL statements using literal variables represent a non-sharable SQL which can get the best execution plans each time at a cost in optimization overheads (memory, CPU and latching).
- SQL statements using bind variables are represented by a unique sql_id (or a very small number of copies) in the library cache statement that are re-executed saving memory and CPU parse time. But this resource saving makes SQL statements sharing the same execution plan; that is the plan corresponding to the first bind value Oracle peeked at for the plan optimization during the hard parse time even if this plan is not optimal for the next bind variable value.
So what? Shall we use literal or bind variables? The best answer I have found to this question is that of Tom Kyte “If I were to write a book about how to build non-scalable Oracle applications, then Don’t use bind variables would be the first and the last chapter”.
3. Adaptive cursor sharing came to the rescue
Adaptive cursor sharing (ACS) is a feature introduced in the Oracle 11g release to allow, under certain circumstances, the Cost Based Optimizer (CBO) to adapt itself, peeks at the bind variable and generate the best plan possible without waiting for a hard parse to occur. Below is presented the ACS working algorithm:
So far we are using bind variables. Our SQL query is then bind sensitive. Ins’t it?
SQL> alter system flush shared_pool; SQL> exec :n := 'N1'; SQL> select /*+ bind_variable */ count(*), max(col2) from t1 where flag = :n; Plan hash value: 3724264953 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 275 (100)| | | 1 | SORT AGGREGATE | | 1 | 30 | | | |* 2 | TABLE ACCESS FULL| T1 | 46667 | 1367K| 275 (2)| 00:00:04 | --------------------------------------------------------------------------- SQL> exec :n := 'Y1'; SQL> select /*+ bind_variable */ count(*), max(col2) from t1 where flag = :n; Plan hash value: 3724264953 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 275 (100)| | | 1 | SORT AGGREGATE | | 1 | 30 | | | |* 2 | TABLE ACCESS FULL| T1 | 46667 | 1367K| 275 (2)| 00:00:04 | ---------------------------------------------------------------------------
Let’s see now after several executions of the same query if ACS kicks off or not. Remember that the first condition for ACS to kick off is that our cursor has to be bind sensitive. In the next query you should read the “I” prompts as Is_bind_aware , Is_bind_sensitive and Is_shareable respectively:
SQL> @c:\is_bind_sens SQL_ID CHILD_NUMBER I I I SIG EXECUTIONS PLAN_HASH_VALUE ------------- ------------ - - - ------------------- ---------- --------------- 8xujk8a1g65x6 0 N N Y 9686445671300360182 5 3724264953
After 5 executions the cursor is still not bind sensitive. In fact, to be so, the bind variable should have histograms
SQL> exec dbms_stats.gather_table_stats(USER,'T1',method_opt=>'FOR COLUMNS flag SIZE AUTO',no_invalidate=>FALSE); SQL> exec :n := 'Y1'; SQL> select /*+ bind_variable */ count(*), max(col2) from t1 where flag = :n; COUNT(*) MAX(COL2) ---------- -------------------------------------------------- 1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX Plan hash value: 3625400295 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | SORT AGGREGATE | | 1 | 30 | | | | 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 30 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | I1 | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------
We got an index range scan for the variable that favors an index range scan. That’s fine. Let’s see now if our cursor is bind sensitive
SQL> @c:\is_bind_sens SQL_ID CHILD_NUMBER I I I SIG EXECUTIONS PLAN_HASH_VALUE ------------- ------------ - - - ------------------- ----------- --------------- 8xujk8a1g65x6 0 N Y Y 9686445671300360182 1 3625400295
Yes it is. But it is not yet bind aware.
SQL> exec :n := 'N2'; SQL> select /*+ bind_variable */ count(*), max(col2) from t1 where flag = :n; Plan hash value: 3625400295 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | SORT AGGREGATE | | 1 | 30 | | | | 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 30 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | I1 | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------
I executed the query with the bind variable that favors a full table scan but I shares the preceding execution plan. Let’s see if our cursor is bind aware
SQL> @c:\is_bind_sens SQL_ID CHILD_NUMBER I I I SIG EXECUTIONS PLAN_HASH_VALUE ------------- ------------ - - - ---------------------------------------- ---------- --------------- 8xujk8a1g65x6 0 N Y Y 9686445671300360182 2 3625400295
Still not. The query needs a warm up period before being bind aware. So let’s execute again
SQL> select /*+ bind_variable */ count(*), max(col2) from t1 where flag = :n; SQL_ID 8xujk8a1g65x6, child number 1 Plan hash value: 3724264953 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 275 (100)| | | 1 | SORT AGGREGATE | | 1 | 30 | | | |* 2 | TABLE ACCESS FULL| T1 | 50894 | 1491K| 275 (2)| 00:00:04 | ---------------------------------------------------------------------------
Finally we got a full table scan. Is this due to ACS?
SQL> @c:\is_bind_sens SQL_ID CHILD_NUMBER I I I SIG EXECUTIONS PLAN_HASH_VALUE ------------- ------------ - - - ---------------------------------------- ---------- --------------- 8xujk8a1g65x6 0 N Y Y 9686445671300360182 2 3625400295 8xujk8a1g65x6 1 Y Y Y 9686445671300360182 1 3724264953
Yes it is. Look how the second line (child number 1) is bind sensitive, bind aware and shareable. This is how ACS works.
Now, if I execute the same query with a bind variable that favors an index range scan, ACS will give me the INDEX RANGE SCAN plan
SQL> exec :n := 'Y2'; SQL> select /*+ bind_variable */ count(*), max(col2) from t1 where flag = :n; SQL_ID 8xujk8a1g65x6, child number 2 ------------------------------------- Plan hash value: 3625400295 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | SORT AGGREGATE | | 1 | 30 | | | | 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 30 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | I1 | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- SQL> @c:\is_bind_sens SQL_ID CHILD_NUMBER I I I SIG EXECUTIONS PLAN_HASH_VALUE ------------- ------------ - - - ---------------------------------------- ---------- --------------- 8xujk8a1g65x6 0 N Y N 9686445671300360182 2 3625400295 8xujk8a1g65x6 1 Y Y Y 9686445671300360182 2 3724264953 8xujk8a1g65x6 2 Y Y Y 9686445671300360182 1 3625400295
Spot how a new child cursor (child number 2) has been created and it is bind sensitive, bind aware and shareable. Playing with those bind variables values combinations I ended up by having two child cursors, one for (3724264953) full table scan, and the other one (3625400295) for the index range scan that are both shareable. Thanks to these two child cursors (until they are flushed out, or something disturbs their good working), the CBO will be alternating between the two executions plans giving each bind variable its corresponding execution plan.
For those who want to play with this example, you can use Dominic brooks model reproduced below:
create table t1
(col1 number
,col2 varchar2(50)
,flag varchar2(2));
insert into t1
select rownum
, lpad('X',50,'X')
, case when rownum = 1
then 'Y1'
when rownum = 2
then 'Y2'
when mod(rownum,2) = 0
then 'N1'
else 'N2'
end
from dual
connect by rownum <= 100000;
create index i1 on t1 (flag);
And the is_bind_sens.sql script is
select sql_id , child_number , is_bind_aware , is_bind_sensitive , is_shareable , to_char(exact_matching_signature) sig , executions , plan_hash_value from v$sql where sql_text like '%bind_variable %' and sql_text not like '%v$sql%';
