Adaptive cursor sharing in Oracle -- adaptive cursor sharing

In 11g, Oracle introduced a new feature: adaptive cursor sharing. This feature is mainly used to improve the execution plan of SQL statements with variable binding. It also causes SQL statements with variable binding to generate multiple cursors. In 9i, Oracle introduced the bind peeking technology, which allows the optimizer to determine the selectivity of the where clause during the first hard parsing of SQL statements, this improves the quality of the generated execution plan. However, the execution plan generated using the variable scanning technology is usually not universal when the table data distribution is uneven. (See :)

The introduction of the adaptive cursor sharing function can effectively solve this problem.

First, let's take a look at our test environment:

SQL> desc acs_test_tab
Is the name empty? Type
-------------------------------------------------------------------------------------------------
ID NOT NULL NUMBER
RECORD_TYPE NUMBER
DESCRIPTION VARCHAR2 (50)

SQL> select count (*) from acs_test_tab;

COUNT (*)
----------
100000

SQL> select count (*) from acs_test_tab where record_type = 2;

COUNT (*)
----------
50000

SQL> select count (distinct record_type) from acs_test_tab;

COUNT (DISTINCTRECORD_TYPE)
--------------------------
50001

The table acs_test_Tab is unevenly distributed on the record_type column. Collect statistics:

SQL> exec dbms_stats.gather_Table_Stats (user, 'acs _ test_Tab ', cascade => true, method_opt =>' for all columns size auto ');

The PL/SQL process is successfully completed.

SQL> select column_name, histogram from user_tab_cols where table_name = 'acs _ TEST_TAB ';

COLUMN_NAME HISTOGRAM
---------------------------------------------
ID NONE
RECORD_TYPE HEIGHT BALANCED
DESCRIPTION NONE

First, we can query the columns with record_type 1.

SQL> select count (*) from acs_test_tab where record_type = 1;

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

SQL> alter system flush shared_pool;

The system has been changed.

SQL> var v number;
SQL> exec: v: = 1

The PL/SQL process is successfully completed.

SQL> select sum (id) from acs_test_tab where record_type =: v;

SUM (ID)
----------
1

SQL> select * from table (dbms_xplan.display_cursor );

PLAN_TABLE_OUTPUT
Certificate ----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL _ID 3p66zbwtm19bs, child number 0
-------------------------------------
Select sum (id) from acs_test_tab where record_type =: v

Plan hash value: 3987223107

Bytes -----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes -----------------------------------------------------------------------------------------------------------
| 0 | select statement | 4 (100) |
| 1 | sort aggregate | 1 | 9 |
| 2 | table access by index rowid | ACS_TEST_TAB | 1 | 9 | 4 (0) | 00:00:01 |
| * 3 | index range scan | ACS_TEST_TAB_RECORD_TYPE_ I | 1 | 3 (0) | 00:00:01 |
Bytes -----------------------------------------------------------------------------------------------------------

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

3-access ("RECORD_TYPE" =: V)

You have selected 20 rows.

SQL> select child_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware
2 from v $ SQL
3 where SQL _text like 'select sum (id) % ';

CHILD_NUMBER EXECUTIONS BUFFER_GETS I
-----------------------------------
0 1 218 Y N

Next we will query the record whose record_type is 2,

SQL> exec: v: = 2

The PL/SQL process is successfully completed.

SQL> select sum (id) from acs_test_tab where record_type =: v;

SUM (ID)
----------
2500050000

SQL> select * from table (dbms_xplan.display_cursor );

PLAN_TABLE_OUTPUT
Certificate ----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL _ID 3p66zbwtm19bs, child number 0
-------------------------------------
Select sum (id) from acs_test_tab where record_type =: v

Plan hash value: 3987223107

Bytes -----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes -----------------------------------------------------------------------------------------------------------
| 0 | select statement | 4 (100) |
| 1 | sort aggregate | 1 | 9 |
| 2 | table access by index rowid | ACS_TEST_TAB | 1 | 9 | 4 (0) | 00:00:01 |
| * 3 | index range scan | ACS_TEST_TAB_RECORD_TYPE_ I | 1 | 3 (0) | 00:00:01 |
Bytes -----------------------------------------------------------------------------------------------------------

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

3-access ("RECORD_TYPE" =: V)

You have selected 20 rows.

SQL> select child_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware
2 from v $ SQL
3 where SQL _text like 'select sum (id) % ';

CHILD_NUMBER EXECUTIONS BUFFER_GETS I
-----------------------------------
0 2 832 Y N

We found that the execution plan had not changed, but the statistics had a great jump.

Execute the preceding statement again

SQL> select sum (id) from acs_test_tab where record_type =: v;

SUM (ID)
----------
2500050000

SQL> select * from table (dbms_xplan.display_cursor );

PLAN_TABLE_OUTPUT
Certificate ----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL _ID 3p66zbwtm19bs, child number 1
-------------------------------------
Select sum (id) from acs_test_tab where record_type =: v

Plan hash value: 509473618

Bytes -----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes -----------------------------------------------------------------------------------
| 0 | select statement | 137 (100) |
| 1 | sort aggregate | 1 | 9 |
| * 2 | table access full | ACS_TEST_TAB | 48425 | shard k | 137 (1) | 00:00:02 |
Bytes -----------------------------------------------------------------------------------

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

2-filter ("RECORD_TYPE" =: V)

19 rows have been selected.

SQL> select child_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware
2 from v $ SQL
3 where SQL _text like 'select sum (id) % ';

CHILD_NUMBER EXECUTIONS BUFFER_GETS I
-----------------------------------
0 2 832 Y N
1 510 Y

This execution plan has changed because oracle found that different variable values cause different data access features through the previous two operations. Therefore, the cursor is set to bind_aware, in this case, oracle selects different execution plans based on the selectivity of the bound variables. If a selected execution plan does not exist, a new execution plan is generated, execution plans with a sub-cursor of 0 will be set to nosharable, no longer used and gradually discarded. As shown below:

SQL> exec: v: = 1

The PL/SQL process is successfully completed.

SQL> select sum (id) from acs_test_tab where record_type =: v;

SUM (ID)
----------
1

SQL> select * from table (dbms_xplan.display_cursor );

PLAN_TABLE_OUTPUT
Certificate ----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL _ID 3p66zbwtm19bs, child number 2
-------------------------------------
Select sum (id) from acs_test_tab where record_type =: v

Plan hash value: 3987223107

Bytes -----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes -----------------------------------------------------------------------------------------------------------
| 0 | select statement | 4 (100) |
| 1 | sort aggregate | 1 | 9 |
| 2 | table access by index rowid | ACS_TEST_TAB | 1 | 9 | 4 (0) | 00:00:01 |
| * 3 | index range scan | ACS_TEST_TAB_RECORD_TYPE_ I | 1 | 3 (0) | 00:00:01 |
Bytes -----------------------------------------------------------------------------------------------------------

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

3-access ("RECORD_TYPE" =: V)

You have selected 20 rows.

SQL> select child_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware, is_retriable
2 from v $ SQL
3 where SQL _text like 'select sum (id) % ';

CHILD_NUMBER EXECUTIONS BUFFER_GETS I
------------------------------------
0 2 832 Y N
1 510 Y
2 1 131 Y

Next, let's summarize:

Adaptive cursor sharing is introduced to solve the problem that the execution plan does not have common rows and the efficiency is low because of the skewed columns of data distribution.

When a cursor is set to BIND_SENSITIVE (this cursor may have different efficiency performance due to different values of the bound variable, oracle will monitor the bind_sensitive cursor );

When oracle finds that the bind_sensitive cursor does show different efficiency (such as logical read jump) due to different values of the bound variable, oracle records the cursor. When the cursor is called next time, oracle generates a new cursor Based on the bound variable value. The new cursor is marked as BIND_AWARE, and the shared identifier of the cursor is set to NO, that is, the cursor will be gradually discarded and replaced with the memory.

When oracle executes this statement again, the selectivity is calculated based on the value of the binding convenience (for example, through the histogram). If the calculated selectivity already exists in the previous sub-game model, the subcursor is called. Otherwise, a new subcursor is created;

If the new sub-cursor is the same as the old sub-cursor execution plan, oracle merges it, uses the new sub-cursor, and gradually discards the old sub-cursor.

Note:

Bind_sensitive: Binding variables may affect the execution plan, which must be monitored by oracle.

Bind_aware: Binding variables affects the execution plan. oracle selects or produces new execution plans based on different binding variables.