I'll start with the difference between Literals and Bind Variables. A literal means the values being compared in the SQL statement are hard coded in the statement itself. An example of a literal value would be, select name from emp where id=10.
A bind variable is created and set to a particular value, which is then used by a SQL statement. This allows the developer to assign any value to the bind variable and the SQL statement will use the new value. For example, select name from emp where id=:id. The :id is the bind variable and the values being passed into the statement can change as the developer needs.
The use of literal values will cause many unique statements to be cached as each literal value causes the statement to be different. This will cause more space to be used in the Share Pool. With the use of bind variables the statement remains the same, therefore there is only one statement cached as opposed to many.
When the Oracle server process receives a new SQL statement from the client it generates a new cursor and places it in the Shared Pool (cached). The Oracle server process will also generate an execution plan to be used by this new cursor. Once this cursor and execution plan have been initially set, they are not changed unless some operation invalidates the cursor in the Shared Pool, or the Shared Pool is flushed, or the cursor is aged out of the Shared Pool. All these conditions would cause the Oracle server process to re-create the cursor and regenerate the execution plan. When using literal values in SQL statements, there will be many cursors in the Shared Pool as the statements are all different. With each cursor in the Shared Pool there will be an execution plan. When using bind variables there will be only one cursor and one execution plan in the Shared Pool regardless of the value being send to the bind variable. If the bind variable is selecting from a column which is highly skewed then the execution plan may not be optimal.
Bind Variable Peeking
Bind Variable Peeking was introduced in Oracle 9i. Prior to Bind Variable Peeking the Oracle optimizer did not know the value being used in the statement and could end up choosing a poor execution plan. Bind Variable Peeking will look at the value of the bind variable the very first time the statement is executed and generate an execution plan. The cached execution plan will be optimal based on the first value sent to the bind variable. Subsequent executions of this statement will use the same execution plan regardless of the value of the bind variable. Using Bind Variable Peeking is good if the bind variable is selecting from a column which is not skewed. This means the initial execution plan for the statement will be optimal for all values of the bind variable. For example, a bind variable for the emp.id is a good idea as the value is unique in the table. The initial execution plan will be good for all statements against id. On the other hand, using a bind variable for emp.deptid could pose problems. Let say there are two departments. Department 20 has 3 employees and department 21 has 10,000 employees. As the emp.deptid data is skewed, the initial execution and execution plan of the statement may not be optimal for subsequent executions of the statement. Looking at select name from emp where deptid=:deptid, with deptid set to 20 returns 3 rows. If this was the initial statement, the optimizer would create an execution plan which would use an index. If deptid is then set to 21, where 10,000 rows are returned, the optimizer will still use the initial plan and use the index. Using the index in this case is not optimal as a large percentage of the table will be returned. A full table scan would be better. So you see the problem with bind variable peeking. Oracle 11g overcomes the Bind Variable Peeking problem with the new Adaptive Cursor Sharing feature. Due to the Bind Peeking problem, some developers will purposely use literal values, for fields with highly skewed data, to avoid the Bind Variable Peeking problem. When they use literal values it forces Oracle to create a single cursor with its own execution plan. This ensures the query will be executed optimally.
Cursor Sharing
The cursor_sharing parameter can be set to one of three values, FORCE, EXACT or SIMILAR. This parameter is really telling the Oracle server process how to handle statements which have literal values. If the parameter is set to FORCE or SIMILAR the Oracle server process will strip out the literal values in the SQL statements and generate system generated bind variables. With FORCE or SIMILAR all statements will go through the Bind Variable Peeking process. At first I though both SIMILAR and FORCE will expose and amplify the Bind Peeking problem. In my testing I determined that SIMILAR does not expose the Bind Peeking problem, but FORCE does. If the parameter is set to EXACT, the Oracle server processes the query as it is and generates an execution plan based on the query. With EXACT literal values are maintained. Here are the tests I performed. Notice that both SIMILAR and EXACT act the same when literal and bind variables are used.
SQL> connect bvp/oracle
Connected.
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> show parameter optimizer;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 10.2.0.4
optimizer_index_caching integer 90
optimizer_index_cost_adj integer 25
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
SQL> show parameter cursor_sharing;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
SQL> -- Generate statistics with histograms.
SQL> exec dbms_stats.gather_table_stats(ownname=>'BVP',tabname=>'DTREE',estimate_percent=>NULL,
method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO',cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL> select table_name, column_name, num_buckets from user_tab_columns;
TABLE_NAME COLUMN_NAME NUM_BUCKETS
------------------------------ ------------------------------ -----------
DTREE SUBTYPE 3
DTREE DATAID
SQL> select subtype, count(*) from dtree group by subtype;
SUBTYPE COUNT(*)
---------- ----------
144 74998
141 1
0 1
SQL> -- Test 1: EXACT + Literals
SQL> select count(*) from dtree where subtype=144;
COUNT(*)
----------
74998
SQL> set linesize 120
SQL> set pagesize 50000
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID b199gz2s1x8zn, child number 0
-------------------------------------
select count(*) from dtree where subtype=144
Plan hash value: 3375002384
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 38 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX FAST FULL SCAN| DTREE_SUBTYPE | 74998 | 292K| 38 (6)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("SUBTYPE"=144)
19 rows selected.
SQL> select count(*) from dtree where subtype=141;
COUNT(*)
----------
1
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID 90gn0x6fjjccv, child number 0
-------------------------------------
select count(*) from dtree where subtype=141
Plan hash value: 1248639843
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX RANGE SCAN| DTREE_SUBTYPE | 1 | 4 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SUBTYPE"=141)
19 rows selected.
SQL> -- Test 2: EXACT + Bind Variable
SQL> variable subtype number;
SQL> exec :subtype := 141;
PL/SQL procedure successfully completed.
SQL> select count(*) from dtree where subtype=:subtype;
COUNT(*)
----------
1
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID gv59k4a054s34, child number 0
-------------------------------------
select count(*) from dtree where subtype=:subtype
Plan hash value: 1248639843
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX RANGE SCAN| DTREE_SUBTYPE | 1 | 4 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SUBTYPE"=:SUBTYPE)
19 rows selected.
SQL> exec :subtype := 144;
PL/SQL procedure successfully completed.
SQL> select count(*) from dtree where subtype=:subtype;
COUNT(*)
----------
74998
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID gv59k4a054s34, child number 0
-------------------------------------
select count(*) from dtree where subtype=:subtype
Plan hash value: 1248639843
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX RANGE SCAN| DTREE_SUBTYPE | 1 | 4 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SUBTYPE"=:SUBTYPE)
19 rows selected.
SQL> -- Test 3: SIMILAR + Bind Variable
SQL> alter system set cursor_sharing='SIMILAR' scope=memory;
System altered.
SQL> show parameter cursor_sharing;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string SIMILAR
SQL> alter system flush shared_pool;
System altered.
SQL> print subtype;
SUBTYPE
----------
144
SQL> select count(*) from dtree where subtype=:subtype;
COUNT(*)
----------
74998
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID gv59k4a054s34, child number 0
-------------------------------------
select count(*) from dtree where subtype=:subtype
Plan hash value: 3375002384
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 38 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX FAST FULL SCAN| DTREE_SUBTYPE | 74998 | 292K| 38 (6)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("SUBTYPE"=:SUBTYPE)
19 rows selected.
SQL> exec :subtype := 141;
PL/SQL procedure successfully completed.
SQL> select count(*) from dtree where subtype=:subtype;
COUNT(*)
----------
1
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID gv59k4a054s34, child number 0
-------------------------------------
select count(*) from dtree where subtype=:subtype
Plan hash value: 3375002384
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 38 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX FAST FULL SCAN| DTREE_SUBTYPE | 74998 | 292K| 38 (6)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("SUBTYPE"=:SUBTYPE)
19 rows selected.
SQL> -- Test 4: SIMILAR + Literals
SQL> select count(*) from dtree where subtype=144;
COUNT(*)
----------
74998
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID 8xh6udukqrd0u, child number 0
-------------------------------------
select count(*) from dtree where subtype=:"SYS_B_0"
Plan hash value: 3375002384
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 38 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX FAST FULL SCAN| DTREE_SUBTYPE | 74998 | 292K| 38 (6)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("SUBTYPE"=:SYS_B_0)
19 rows selected.
SQL> select count(*) from dtree where subtype=141;
COUNT(*)
----------
1
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID 8xh6udukqrd0u, child number 1
-------------------------------------
select count(*) from dtree where subtype=:"SYS_B_0"
Plan hash value: 1248639843
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX RANGE SCAN| DTREE_SUBTYPE | 1 | 4 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SUBTYPE"=:SYS_B_0)
19 rows selected.
Statistics
During the Bind Variable Peeking process, the initial bind variable value will be used to generate an execution plan. If there are histograms the optimizer takes these into account when generating an execution plan. If there are no histograms then the optimizer will assume an even distribution of the values in the table and generate an execution plan based on this fact.
To generate statistics without histograms use the following.
execute dbms_stats.gather_schema_stats
(
ownname=>'SCHEMAOWNER',
estimate_percent=>NULL,
cascade=>TRUE,
degree=>dbms_stats.default_degree,
method_opt=>'FOR ALL INDEXED COLUMNS SIZE 1'
)
To generate statistics and histograms use the following.
execute dbms_stats.gather_schema_stats
(
ownname=>'SCHEMAOWNER',
estimate_percent=>NULL,
cascade=>TRUE,
degree=>dbms_stats.default_degree,
method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO'
)
