The Oracle (tm) Users' Co-Operative FAQ

How to generate an Execution Plan with Bind Variable Peeking?


Author's name: Jaromir D.B. Nemec

Author's Email: office AT db-nemec.com

Date written: 11.03.2006

Oracle version(s): 10.2.0.1.0

Bind variable peeking introduced in Oracle Release 9 can be extremely helpful in some situations where the additional information leads to a better execution plan. On the other side this feature makes it much more difficult to see the 'real' execution plan using explain plan or autotrace for statements with bind variables as those tools don't perform the peek. The trivial answer to the question above is to substitute the bind variable with a literal value but there are some subtle issues with variable data type that could lead to a different plan.

Back to index of questions


Illustration Example

Let's illustrate the problem on a small example. We define a table T with a column N. Column N contains numbers from 0 to 100 but is extremely skew as the value 0 repeats 100 times. We define an index on N and collect statistics. Note the usage of high pctfree to make the table and index brighter.

 
SQL> create table t (n number) pctfree 99;
 
Table created.
 
SQL> insert into t
  2  select 0 from dual connect by level <= 100
  3  union all
  4  select rownum from dual  connect by level <= 100
  5  ;
 
200 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> create index t_ix1 on t (n)  pctfree 99;
 
Index created.
SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'t', method_opt=>'for all columns size 254', cascade => true);
PL/SQL procedure successfully completed.

Let's assume that our productive application issues following SQL statement:

 
variable x number;
exec :x := 0;
select * from t where n =  :x;

How can we check which execution plan is used for the statement above? Let's start asking with explain plan:

 
SQL> EXPLAIN PLAN  SET STATEMENT_ID = 'N1' into plan_table  FOR
  2  select * from t where n = :x;
 
Explained.
 
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY('plan_table', 'N1','ALL'));
 
PLAN_TABLE_OUTPUT                                                                                                       
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4152321325                                                                                             
                                                                                                                        
--------------------------------------------------------------------------                                              
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |                                              
--------------------------------------------------------------------------                                              
|   0 | SELECT STATEMENT |       |     2 |     4 |     2   (0)| 00:00:01 |                                              
|*  1 |  INDEX RANGE SCAN| T_IX1 |     2 |     4 |     2   (0)| 00:00:01 |                                              
--------------------------------------------------------------------------
--- output cut out to save space --- 

Well, we see that index range scan is used, let's confirm our observation running the statement with autotrace option.

 
SQL> variable x number;
SQL> exec :x := 0;
 
PL/SQL procedure successfully completed.
 
SQL> set autotrace traceonly;
SQL> select /* unique_string */  * from t where n =  :x;
 
100 rows selected.
 
 
Execution Plan
----------------------------------------------------------                                                              
Plan hash value: 4152321325                                                                                             
                                                                                                                        
--------------------------------------------------------------------------                                              
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |                                              
--------------------------------------------------------------------------                                              
|   0 | SELECT STATEMENT |       |     2 |     4 |     2   (0)| 00:00:01 |                                              
|*  1 |  INDEX RANGE SCAN| T_IX1 |     2 |     4 |     2   (0)| 00:00:01 |                                              
--------------------------------------------------------------------------                                              
--- output cut out to save space --- 

Well, again we see index range scan is used to access the table. Let's examine the v$sql_plan view to be absolutely sure which plan was used.

Execution Plan from V$SQL_PLAN

As you probably observed, we placed a special comment in the select above containing a string unique_string. This string has two roles. First it guaranties that the select will be hard parsed, as there certainly isn't any identical select in the shared pool. Secondly we will use this string to find our statement in the view v$sqltext. From v$sqltext we switch to v$sql_plan, transfer the data to the plan_table and finally use the dbms_xplan to display the execution plan. Note that due to incompatibility issues between v$sql_plan and plan table some transformation is required, the presented syntax is suitable for the 10g plan_table. You will need to comment out some of the columns while using the release 9i.

 
SQL> delete from plan_table where statement_id = 'xx';
 
2 rows deleted.
 
SQL> insert into plan_table
  2  select
  3         'xx' statement_id, 0 plan_id,  timestamp, NULL remarks,
  4         a.operation, a.options, a.object_node, a.object_owner, a.object_name,
  5         object_alias,  null object_instance, object_type, a.optimizer,
  6         a.search_columns, a.id, a.parent_id, a.depth, a.position, a.cost,
  7         a.cardinality, a.bytes, a.other_tag, a.partition_start,
  8         a.partition_stop, a.partition_id, a.other, a.distribution,
  9         a.cpu_cost, a.io_cost, a.temp_space, a.access_predicates,
 10         a.filter_predicates, projection, time
 11        ,qblock_name ,OTHER_XML
 12  from v$sql_plan a
 13  where
 14  (a.hash_value,a.address) in
 15  (select HASH_VALUE,ADDRESS from v$sqltext
 16  -- use upper case UNIQUE_STRING here to avoid other statements with the unique string!
 17  where   sql_text like lower('%UNIQUE\_STRING%') escape '\');
 
2 rows created.
 
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY('plan_table', 'xx','ALL'));
 
PLAN_TABLE_OUTPUT                                                                                                       
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1601196873                                                                                             
                                                                                                                        
---------------------------------------------------------------                                                         
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)|                                                         
---------------------------------------------------------------                                                         
|   0 | SELECT STATEMENT  |      |       |       |     4 (100)|                                                         
|*  1 |  TABLE ACCESS FULL| T    |   100 |   200 |     4   (0)|                                                         
---------------------------------------------------------------

We see that the used execution plan performs full table scan. Both explain plan and autotrace reported a misleading plan. The bind variable peeking effectively changed the execution plan.

Explain Plan with Bind Variable Peeking without Executing the Statement

The disadvantage of the approach above is that it is necessary to execute the statement before we see the used execution plan. This could be undesirable in some cases for example if the tested query is long running. How can we populate the view v$sql_plan without an execution the statement? A simple possibility is to define the query as a cursor in a PL/SQL block. The trick is to only open and close the cursor without fetching the rows from it. The whole operation is illustrated in the example below. Check the reference section below for the credit of this idea. Note that we use the unique string in a hint like comment as the ordinary comment will be cut off while unifying the select statement within the PL/SQL statement transformation. The access of the execution plan from the v$sql_plan is identical with the preceding example.

 
SQL> declare
  2  num   number := 0;
  3  --
  4  cursor c1 is
  5  select /*+ new_unique_string */  * from t where n = num;
  6  begin
  7      open c1;
  8      close c1;
  9  
 10  end;
 11  /
 
PL/SQL procedure successfully completed.
 
SQL> delete from plan_table where statement_id = 'xx';
 
2 rows deleted.
 
SQL> insert into plan_table
  2  select
  3         'xx' statement_id, 0 plan_id,  timestamp, NULL remarks,
  4         a.operation, a.options, a.object_node, a.object_owner, a.object_name,
  5         object_alias,  null object_instance, object_type, a.optimizer,
  6         a.search_columns, a.id, a.parent_id, a.depth, a.position, a.cost,
  7         a.cardinality, a.bytes, a.other_tag, a.partition_start,
  8         a.partition_stop, a.partition_id, a.other, a.distribution,
  9         a.cpu_cost, a.io_cost, a.temp_space, a.access_predicates,
 10         a.filter_predicates, projection, time
 11        ,qblock_name ,OTHER_XML
 12  from v$sql_plan a
 13  where
 14  (a.hash_value,a.address) in
 15  (select HASH_VALUE,ADDRESS from v$sqltext
 16  where   sql_text like lower('%NEW\_UNIQUE\_STRING%') escape '\');
 
2 rows created.
 
SQL> ---
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY('plan_table', 'xx','ALL'));
 
PLAN_TABLE_OUTPUT                                                                                                       
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1601196873                                                                                             
                                                                                                                        
---------------------------------------------------------------                                                         
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)|                                                         
---------------------------------------------------------------                                                         
|   0 | SELECT STATEMENT  |      |       |       |     4 (100)|                                                         
|*  1 |  TABLE ACCESS FULL| T    |   100 |   200 |     4   (0)|                                                         
---------------------------------------------------------------

Conclusion

The proposed technique allows to generate the real execution plan using the bind variable peeking without the necessity of executing the statement. This 'higher precision' of execution plan is required in following situations where the missing of bind variable peeking can lead to a different execution plan:

In general a care should be taken as the bind variable peeking (as a result from either bugs or features) doesn't work for all clients. I tried initially to trigger the hard parse with bind variable peeking using the dbms_sql.parse but the 10053 trace showed no effect. Apparently (as in 10g R2) the dbms_sql package doesn't support bind variable peeking.


Further reading: Oracle Documentation

The original idea of the open / close of cursor to trigger the hard parse is from Tom Kyte and can be found on AskTom, an other threads on this side discuss bind variable peeking and JDBC.

Further work: This approach doesn't address the problem of explaining of DML statements with bind variables - in this case is probably even more important to avoid the execution of the statement while explaining it.


Back to top

Back to index of questions