News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

11g Adaptive Cursor Sharing

  • Posted by Gavin Soorma
  • On August 21, 2009
  • 0 Comments

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
 

0 Comments

Leave Reply

Your email address will not be published. Required fields are marked *