Solving a 10g Bind Variable Peeking problem by granting and revoking privileges

The Cost Based optimizer peeks at the values of user-defined bind variables on the first invocation of a cursor. Based on this, the optimizer determines the selectivity of the WHERE clause condition as if literals have been used instead of bind variables. On subsequent invocations of the cursor, no bind peeking takes place, and the cursor is shared even if subsequent invocations use different bind values.

If the data is skewed in the table especially in the columns used in the WHERE clause, based on the first value ‘peeked’, the optimizer may make a wrong choice of a full table scan or index range scan when the opposite would have been more optimal for the execution of that particular SQL statement.

To force a hard parse (by invalidating the cursor) so that the CBO does another fresh ‘peek’ at the bind variables chosen for a query (and so that a new execution plan gets loaded in the SQL area of the shared pool) we can do any if the following:

1. Issue the command Alter System Flush Shared_Pool.
2. Gather statistics on the underlying objects again.
3. Grant and/or revoke privileges on any of the cursor underlying objects.
4. Alter cursor underlying objects.
5. Bounce the instance.

In some cases we have seen that flushing the shared pool does not help and gathering fresh statistics on large tables could prove to be time consuming.

Let us see how we can use option 3 which is to grant and revoke object privileges to help us force a new loading of the SQL statement in the shared pool.

NOTE: Oracle 11g CBO handles this in a much better and optimized fashion.

Create a table and populate it with 80,000 rows. Note that more than 99% of the records in the table are belonging to one value – “SnowBoots”

Create Table Products 
(Prod_Id Number(5), 
Prod_Name Varchar2(10) 
) 
/ 

SQL> 
Begin
For i in 1..79998 Loop
Insert Into Products
Values (i, 'SnowBoots');
End Loop;
Insert Into Products Values (79999, 'Football');
Insert Into Products Values(80000, 'Baseball');
End;
/   

PL/SQL procedure successfully completed.

Create an index and gather statistics

SQL> Create index products_ind on products(prod_name);

Index created.

SQL> Exec DBMS_Stats.Gather_Table_Stats(OwnName =>'SYSTEM',TabName=>'Products',Method_Opt => 'For All Indexed Columns Size Auto', Cascade => True);

PL/SQL procedure successfully completed.

Set up session level environment to force bind peeking

SQL> Alter Session Set Optimizer_Mode='ALL_ROWS';

Session altered.

SQL> Alter Session Set "_optim_peek_user_binds" = True;

Session altered.

SQL> Alter Session Set Cursor_Sharing=Exact;

Session altered.

This is the SQL statement which we will be running to test our use case scenario

Select Count(*) from products where prod_name= :prodname;

Assign a value for the bind variable :prodname – this should result in the CBO choosing an index range scan as there is only one record in the table.

SQL> var prodname varchar2(10)
SQL> exec :prodname := 'Baseball'

PL/SQL procedure successfully completed.

SQL> Select Count(*) from products where prod_name= :prodname;

  COUNT(*)
----------
         1

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  f0r4p39kka2cq, child number 0
-------------------------------------
Select Count(*) from products where prod_name= :prodname

Plan hash value: 3118775239

------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost  |
------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |       |       |     1 |
|   1 |  SORT AGGREGATE   |              |     1 |    10 |       |
|*  2 |   INDEX RANGE SCAN| PRODUCTS_IND |     1 |    10 |     1 |

Assign a new value for the bind variable which should have resulted in a different execution plan

This should ideally cause the optimizer to invoke a full table scan as 79,988 rows are being returned by the query. But we see that the optimizer is still using the same execution plan which involved an index range scan as it has no way of knowing the data distribution based on just peeking at the bind variable chosen.

SQL> exec :prodname := 'SnowBoots'

PL/SQL procedure successfully completed.

SQL> Select Count(*) from products where prod_name= :prodname;

  COUNT(*)
----------
     79998

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  f0r4p39kka2cq, child number 0
-------------------------------------
Select Count(*) from products where prod_name= :prodname

Plan hash value: 3118775239

------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost  |
------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |       |       |     1 |
|   1 |  SORT AGGREGATE   |              |     1 |    10 |       |
|*  2 |   INDEX RANGE SCAN| PRODUCTS_IND |     1 |    10 |     1 |
------------------------------------------------------------------

From another SQL session grant and revoke privileges on the underlying table.

SQL> grant select on system.products to public;

Grant succeeded.

SQL> revoke  select on system.products from public;

Revoke succeeded.

Run the same SQL statement again with the same bind variable assignment – note the new execution plan

We run the same query with the same bind variable assignment of “SnowBoots” and we see that this time the optimizer has done a hard parse of the statement and assesses the selectivity of the WHERE clause again based on value of the bind variable and correctly chooses the path of a full table scan over an index scan.

SQL> exec :prodname := 'SnowBoots'

PL/SQL procedure successfully completed.

SQL> Select Count(*) from products where prod_name= :prodname;

  COUNT(*)
----------
     79998

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  f0r4p39kka2cq, child number 0
-------------------------------------
Select Count(*) from products where prod_name= :prodname

Plan hash value: 941484917

---------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost  |
---------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |       |       |    23 |
|   1 |  SORT AGGREGATE    |          |     1 |    10 |       |
|*  2 |   TABLE ACCESS FULL| PRODUCTS | 79106 |   772K|    23 |
---------------------------------------------------------------