While one of the performance best practices that is always recommended is the use of bind variables over the literals, Optimizer plan unstability was one of the problems faced by many Oracle 10g shops. When queries that normally perform well, suddenly stopped performing, very often it is found that this bad performance is being observed only for a few or specific parameters or values. And this is particularly so on tables with highly skewed data especially the columns which are being referenced via the WHERE clause.
In Oracle 11g however, with Adaptive Cursor Sharing, if the optimizer observes a sub-optimal plan for a particular bind variable value, it will enable particular bind variables or range of bind variable values to use a different execution plan for the same SQL statement.
To illustrate the 11g Adaptive Cursor Sharing, we will create a table called MYOBJECTS which is based on the ALL_OBJECTS view. The data in the OBJECT_TYPE column is skewed – for example there are more than 50,000 rows with the value ‘SYNONYM’ and only 1 row with the value ‘RULE’. The table has about 100,000 rows.
SQL> conn sh/sh Connected. SQL> create table myobjects 2 as select * from all_objects; Table created. SQL> create index myobject_ind on myobjects (object_type); Index created.
We then gather statistics on the table and see that histograms have been created on the table which has the skewed data.
SQL> exec dbms_stats.gather_table_stats(null,'MYOBJECTS', METHOD_OPT => 'FOR ALL COLUMNS SIZE 10'); PL/SQL procedure successfully completed. SQL> SELECT column_name, histogram FROM user_tab_cols WHERE table_name ='MYOBJECTS'; COLUMN_NAME HISTOGRAM ------------------------------ --------------- OWNER HEIGHT BALANCED OBJECT_NAME HEIGHT BALANCED SUBOBJECT_NAME HEIGHT BALANCED OBJECT_ID HEIGHT BALANCED DATA_OBJECT_ID HEIGHT BALANCED OBJECT_TYPE HEIGHT BALANCED CREATED HEIGHT BALANCED LAST_DDL_TIME HEIGHT BALANCED TIMESTAMP HEIGHT BALANCED STATUS FREQUENCY TEMPORARY FREQUENCY GENERATED FREQUENCY SECONDARY FREQUENCY
If we query the MYOBJECTS Table using the predicate ‘RULE’, we find that an INDEX RANGE SCAN using the MYOBJECTS_IND index is being carried out by the optimizer.
If we change the predicate to ‘SYNONYM’ we find that a FULL TABLE SCAN is being performed instead which is the expected behaviour given the distribution of rows in the table.
SQL> explain plan for 2 select * from myobjects where object_type='RULE'; Explained. SQL> set linesize 120 SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------- Plan hash value: 2533318700 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1612 | 147K| 56 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| MYOBJECTS | 1612 | 147K| 56 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | MYOBJECT_IND | 1612 | | 5 (0)| 00:00:01 | -------------------------------------------------------------------------------------------- SQL> explain plan for 2 select * from myobjects where object_type='SYNONYM'; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------ Plan hash value: 2581838392 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 34398 | 3157K| 284 (2)| 00:00:04 | |* 1 | TABLE ACCESS FULL| MYOBJECTS | 34398 | 3157K| 284 (2)| 00:00:04 | -------------------------------------------------------------------------------
Let us now see how the optimizer handles bind variables and how it performs the task of bind variable peeking. In the first case, the variable v_obj_type is assigned the value ‘SYNONYM’ and in the second case the same variable is assigned the value ‘RULE’.
Remember, when literals were being used, using the value ‘RULE’ in the WHERE clause resulted in an index range scan, while usage of the value ‘SYNONYM’ resulted in a full table scan.
SQL> VARIABLE v_obj_type VARCHAR2(10) SQL> EXEC :v_obj_type := 'SYNONYM'; PL/SQL procedure successfully completed. SQL> select * from myobjects where object_type=:v_obj_type; 40156 rows selected. SQL> select * from table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------- SQL_ID 5utj7m6zxjdsc, child number 0 ------------------------------------- select * from myobjects where object_type=:v_obj_type Plan hash value: 2581838392 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 284 (100)| | |* 1 | TABLE ACCESS FULL| MYOBJECTS | 34398 | 3157K| 284 (2)| 00:00:04 | -------------------------------------------------------------------------------
Although the predicate is now the value ‘RULE’ based on the bind variable that is being used here, we find that the optimizer is still performing a FULL TABLE SCAN, while in fact it should be actually performing an index scan.
But since bind peeking is being performed here, the optimizer will use the same plan even though the value of the variable is different. If the data is skewed as in this case, it can cause a lot of performance problems and optimizer unstability depending on which bind variable value is assigned at hard parse.
SQL> VARIABLE v_obj_type VARCHAR2(10) SQL> EXEC :v_obj_type := 'RULE'; PL/SQL procedure successfully completed. SQL> select * from myobjects where object_type=:v_obj_type; no rows selected SQL> / no rows selected SQL> / no rows selected SQL> select * from table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------- SQL_ID 5utj7m6zxjdsc, child number 0 ------------------------------------- select * from myobjects where object_type=:v_obj_type Plan hash value: 2581838392 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 284 (100)| | |* 1 | TABLE ACCESS FULL| MYOBJECTS | 34398 | 3157K| 284 (2)| 00:00:04 | -------------------------------------------------------------------------------
But if we query the V$SQL view, we find that for this particular SQL_ID, the column IS_BIND_SENSITIVE shows ‘Y’ but the column ‘IS_BIND_AWARE’ still shows ‘N’.
This means that Oracle is aware that this query can require different execution plans based on the bind variable values but has not yet acted on this at this stage.
SQL> SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware from v$sql WHERE sql_text ='select * from myobjects where object_type=:v_obj_type'; 2 3 SQL_ID CHILD_NUMBER I I ------------- ------------ - - 5utj7m6zxjdsc 0 Y N
We then execute the same SQL statement once again using the same bind variable value, and we find that this time a more optimal execution plan has been chosen which in this case is an INDEX RANGE SCAN
SQL> VARIABLE v_obj_type VARCHAR2(10) SQL> EXEC :v_obj_type := 'RULE'; PL/SQL procedure successfully completed. SQL> select * from myobjects where object_type=:v_obj_type; no rows selected SQL> select * from table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------- SQL_ID 5utj7m6zxjdsc, child number 1 ------------------------------------- select * from myobjects where object_type=:v_obj_type Plan hash value: 2745750972 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 30 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| MYOBJECTS | 823 | 83946 | 30 (0)| 00:00:01 | PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------- |* 2 | INDEX RANGE SCAN | MYOBJECTS_IND | 823 | | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------
If we now query the V$SQL view, we find that this optimizer change is also reflected in the column IS_BIND_SENSITIVE showing the value ‘Y’ instead of ‘N’ which was the earlier value.
SQL> SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware from v$sql WHERE sql_text ='select * from myobjects where object_type=:v_obj_type'; 2 3 SQL_ID CHILD_NUMBER I I ------------- ------------ - - 5utj7m6zxjdsc 0 Y N 5utj7m6zxjdsc 1 Y Y
Gavin Soorma
