Query using Bind Variables is suddenly slow (Bind variable Peeking concept)

Query using Bind Variables is suddenly slow (Bind variable Peeking concept)
===============================================================================


You are running on a database at 9i or above, and have observed that sometimes,
for no apparent reason, some SQL which has been running fine suddenly runs very poorly. You have made no changesto the data, the SQL, or the statistics for the objects involved.

On further examination of the SQL it can be seen that it is using bind variables.



bind peeking
============
With this feature the query optimizer peeks at the values of user-defined bind variables on the first invocation of a cursor. This feature lets the optimizer determine the selectivity of any WHERE clause condition, based on this value just as if a literal had been used instead of a bind variable. On subsequent invocations of the cursor, no peeking takes place, and the cursor is shared, based on
the standard cursor-sharing criteria(Enabling bind peeking feature
by default "_optim_peek_user_binds" = True and Cursor_Sharing=Exact ), even if subsequent invocations use different bind values.
Thus if the first set of bind values that happen to be used when the cursor is first hard-parsed are not representative then the plan may be inappropriate for subsequent executions.


Oracle 10.2 Database Performance Tuning Guide manual Chapter 13 "The Query Optimizer" says the following about peeking bind variables :-

"When bind variables are used in a statement, it is assumed that cursor sharing is intended and that different invocations are supposed to use the same execution plan. If different invocations of the cursor would significantly benefit from different execution plans, then bind variables may have been used inappropriately in the SQL statement."

Consider the following simple example where
test_table has 10,00000 rows and id has an index.

SELECT count(*)
FROM test_table
WHERE id BETWEEN :bind1 AND :bind2;


if this sql is use with bind values 321 and 909999 then we would be getting the vast majority of the rows and a full table scan would be more appropriate
and if this sql is use with bind values 321 and 322 then pick out 2 rows from the
10,00000 rows in the table then using an index would be the obvious choice.
But the optimizer cannot now know this,and does not change the plan accordingly.

just see the example here:-




SQL*Plus: Release 11.1.0.6.0 - Production on Wed Jul 20 12:13:31 2011

Copyright (c) 1982, 2007, Oracle. All rights reserved.

SQL> select * from v$version ;
SP2-0640: Not connected
SQL> conn scott@ORCL105
Enter password:
Connected.
SQL>
SQL>
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 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL>
SQL> show parameter Optimizer_Mode

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string ALL_ROWS
SQL>

SQL> show parameter Optimizer_Features_Enable

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_features_enable string 10.2.0.4
SQL>
SQL>
SQL> show parameter Cursor_Sharing

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
SQL>
SQL>
SQL>
SQL>
SQL> create table test_table (id number , name varchar2(20));

Table created.

SQL>

SQL> ed
Wrote file afiedt.buf

1 Begin
2 For i in 1..999997 Loop
3 Insert Into test_table Values (i, 'halim');
4 end loop ;
5 Insert Into test_table Values (999998, 'halim');
6 Insert Into test_table Values (999999, 'halim');
7 Insert Into test_table Values (1000000, 'halim');
8 commit ;
9* end ;
10 /

PL/SQL procedure successfully completed.

SQL> create index ind_id on test_table(id);

Index created.

SQL>
SQL>
SQL>
SQL> var bind1 number
SQL> var bind2 number
SQL>

SQL> exec :bind1:= 321 ;

PL/SQL procedure successfully completed.

SQL> exec :bind2:=909999 ;

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> SELECT count(*)
2 FROM test_table
3 WHERE id BETWEEN :bind1 AND :bind2;

COUNT(*)
----------
909679

SQL> Select * From Table(DBMS_XPlan.Display_Cursor);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------
SQL_ID 4pd57gfq54vdy, child number 0
-------------------------------------
SELECT count(*) FROM test_table WHERE id BETWEEN :bind1 AND :bind2

Plan hash value: 1901445275

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 512 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | FILTER | | | | | |
|* 3 | TABLE ACCESS FULL| TEST_TABLE | 847K| 10M| 512 (4)| 00:00:07 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(:BIND1<=:BIND2)
3 - filter(("ID">=:BIND1 AND "ID"<=:BIND2))

Note
-----
- dynamic sampling used for this statement


25 rows selected.

SQL>
SQL>
SQL> exec :bind1:= 321 ;
SQL> exec :bind2:=322 ;

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> SELECT count(*)
2 FROM test_table
3 WHERE id BETWEEN :bind1 AND :bind2;

COUNT(*)
----------
2
SQL>
SQL> Select * From Table(DBMS_XPlan.Display_Cursor);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------
SQL_ID 4pd57gfq54vdy, child number 0
-------------------------------------
SELECT count(*) FROM test_table WHERE id BETWEEN :bind1 AND :bind2

Plan hash value: 1901445275

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 512 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | FILTER | | | | | |
|* 3 | TABLE ACCESS FULL| TEST_TABLE | 847K| 10M| 512 (4)| 00:00:07 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(:BIND1<=:BIND2)
3 - filter(("ID">=:BIND1 AND "ID"<=:BIND2))

Note
-----
- dynamic sampling used for this statement


25 rows selected.

SQL>
SQL>




==============================================================================
but after Flush Shared_Pool we can find appropriate plan accordingly.
==============================================================================




SQL>
SQL>
SQL>
SQL> Alter System Flush Shared_Pool;

System altered.

SQL> SELECT count(*)
2 FROM test_table
3 WHERE id BETWEEN :bind1 AND :bind2;

COUNT(*)
----------
2

SQL> Select * From Table(DBMS_XPlan.Display_Cursor);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------
SQL_ID d8qy8v2wwwa5g, child number 0
-------------------------------------
SELECT count(*) FROM test_table WHERE id BETWEEN :bind1 AND :bind2

Plan hash value: 916475704

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | FILTER | | | | | |
|* 3 | INDEX RANGE SCAN| IND_ID | 2 | 26 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(:BIND1<=:BIND2)
3 - access("ID">=:BIND1 AND "ID"<=:BIND2)

Note
-----
- dynamic sampling used for this statement


25 rows selected.

SQL>


===================================================
or after using hits it giving me actual plan
===================================================

/*+ Hard parse me please */


SQL>
SQL>
SQL>
SQL> SELECT /*+ Hard parse me please */ count(*)
2 FROM test_table
3 WHERE id BETWEEN :bind1 AND :bind2;

COUNT(*)
----------
2
SQL>
SQL>
SQL> Select * From Table(DBMS_XPlan.Display_Cursor);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------
SQL_ID fx67qtjb4wyrw, child number 0
-------------------------------------
SELECT /*+ Hard parse me please */ count(*) FROM test_table
WHERE id BETWEEN :bind1 AND :bind2

Plan hash value: 916475704

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | FILTER | | | | | |
|* 3 | INDEX RANGE SCAN| IND_ID | 2 | 26 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(:BIND1<=:BIND2)
3 - access("ID">=:BIND1 AND "ID"<=:BIND2)

Note
-----
- dynamic sampling used for this statement


26 rows selected.

SQL>


In this situation
=======================================


In this sort of situation it would perhaps be a good idea to modify the application and have two separate modules/sections each with the above SQL, but with a variation (perhaps modified with a hint) that will result in the desired plan. The appropriate module could then be invoked programmatic-ally. An example might be
a situation where you use essentially the same SQL to query the pay for one employee or all 10,000 employees. The query for one employee should use indexes, and the query for all employees should do a full table scan.

N.B. The cursor will be moved out of the Shared Pool and therefore require a hard parse on subsequent invocation in a number of circumstances, such as :-

1) database shutdown/restart

2) cursor not in use by any session, and aged out by LRU algorithm

3) change to the stats associated with any referenced object (eg following a gather stats job)

4) change to the structure of any referenced object (eg alter table)

5) Granting/revoking privileges on a referenced object

6) using hits for hard parse /*+ Hard parse me please */

7) Alter System Flush Shared_Pool;

It will NOT get moved out by flushing the Shared Pool if it is pinned .