[Oracle] adaptive optimizer (Adaptive Cursor Sharing) cursor sharing function

Label: OracleACSOptimizerSQLTuning
7068 reading comment(4) Collection Report
Classification:

Summary

We know that the bound variables (Bind Peeking), a SQL paper in hard parsing (Hard Parse), will then bind variable values to estimate the selection of base (cardinality) and made the same execution plan, and after the SQL in the implementation process, will make the share of first time execution plan.

If the data distribution table data or uneven tilt, and later used to estimate the values of the variables used in the implementation of the estimated value of variable selection base (cardinality) differences, by bound variables (Bind Peeking) on a function execution plan for some variables may perform is optimal, even cause serious performance problems.

Therefore, the optimizer after the 11.1 version, in order to solve the bound variables (Bind Peeking) on the problem, introduced the adaptive cursor sharing (Adaptive Cursor Sharing referred to as ACS), the same SQL statements contain bound variables in the execution times, do not blindly shared execution plan, and according to the binding the values of variables and feedback of information collection in the course of execution, you can use a number of different execution plan, avoid performance problems.

Here are a few examples to learn more about ACS.

Example 1 (ACS invalid)

First of all, we look at the Peeking (Bind) function is valid, but the adaptive cursor sharing function is invalid, the implementation of the following SQL action. (test based on 10.2.0.5 version)

0 prepare test tables and data

SQL> conn scott/tiger
Connected.
SQL>CreateTable TEST_ACS (C1 number, C2 VARCHAR2)One hundred));

Table created.

SQL>BEGIN 
    FORIIN One.One thousand LOOP 
        FORJIN One.Ten LOOP 
INSERTINTOTEST_ACS
VALUES (I),'a');
COMMIT;
        END LOOP;
    END LOOP;
END;
/Two    Three    Four    Five    Six    Seven    Eight    Nine   Ten  

PL/SQLProcedure Successfully Completed.

SQL>Create Index Ind1 On TEST_ACS(C1);

IndexCreated.

SQL> exec dbms_stats.gather_table_stats (ownname = >'SCOTT'Tabname = >.'TEST_ACS'CASCADE = >.TRUE);

PL/SQLProcedure Successfully Completed.

We prepared a table containing 10000 data,C1Column has the value of 1~1000, and each value has 10 data.
--- and inC1Column has an indexInd1

SQL>SELECT Count(C1),Count(DISTINCTC1),Min(C1),Max(C1) FROM TEST_ACS;

COUNT (C1) COUNT (DISTINCTC1) MIN (C1) MAX (C1)
-----------------------------------------------
     Ten thousand              One thousand          One       One thousand

1 first we specify a variable value of 3 and execute the following SQL

SQL>VarX number;
SQL> exec: x = =Three;

PL/SQLProcedure Successfully Completed.

SQL>SELECT*FROM TEST_ACS WHERE C1< =:X;

C1 C2
--------------------------
     OneA
...
     ThreeA

ThirtyRows selected.

SQL>

2 because the bind variable Peeking (Bind) function, so hard to select the implementation plan,
The 3 bind variable values into the calculation of the base SQL in this paper, SQL paper chose 30 data in the 10000 data,
So, select the index IND1 for INDEX RANGE SCAN.

SQL> set line 200
SQL> set PageSize 9999
SQL> select * from table (DBMS)_xplan.display_Cursor (null, null),'NOTE ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID cuf4u4th4w0nz, child number 0
Similiarly
SELECT * FROM TEST_ACS WHERE C1: X

Plan hash value: 3882350253

---------------------------------------------------------
Id Operation Name | | | | E-Rows |
---------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 TABLE ACCESS BY INDEX ROWID| TEST |_ACS | 30 |
2 INDEX RANGE SCAN |* | | IND1 | 30 |
---------------------------------------------------------

Predicate Information (identified by operation ID):
Away

2 - access (C1 < = X)
...

25 rows selected.

3 we then specify the variable value of 9, and then execute the following SQL.
Although this article SQL in the 10000 data choose 9000 data, select the rate as high as 90%, but because the SQL has been parsed, it will continue to use before the execution of the plan, or 30 E-Rows, using INDEX RANGE SCAN.

SQL> exec: x = 9;

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TEST_ACS WHERE C1: x;

C1 C2
.Employed
1 A
...
   900 a

9000 rows selected.

SQL>
SQL> set line 200
SQL> set PageSize 9999
SQL> select * from table (DBMS)_xplan.display_Cursor (null, null),'NOTE ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
SQL_ID cuf4u4th4w0nz, child number 0
Similiarly
SELECT * FROM TEST_ACS WHERE C1: X

Plan hash value: 3882350253

---------------------------------------------------------
Id Operation Name | | | | E-Rows |
---------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 TABLE ACCESS BY INDEX ROWID| TEST |_ACS | 30 |
2 INDEX RANGE SCAN |* | | IND1 | 30 |
---------------------------------------------------------

Predicate Information (identified by operation ID):
Away

2 - access (C1 < = X)
....

25 rows selected.

SQL>
SQL> select child_number, executions, buffer_gets, plan_hash_value
From v$sql
Where sql_id ='cuf4u4th4w0nz'; 23

CHILD_NUMBER EXECUTIONS BUFFER_GETS PLAN_HASH_VALUE
------------------------------------------------
0212393882350253

4 we re specify the variable value of 9 and execute the following SQL.
At this time SQL text or 10000 data in the selection of the 9000 data, but because the SQL text has been resolved, it will continue to use the previous implementation plan, the use of INDEX RANGE SCAN

SQL> exec: x = =Nine hundred;

PL/SQLProcedure Successfully Completed.

SQL>SELECT*FROM TEST_ACS WHERE C1< =:X;

C1 C2
--------------------------
     OneA

...
   Nine hundredA

Nine thousandRows selected.

SQL>

5 no matter what the value of the variable is specified in the future, the execution plan is executed with the original execution plan.
Thus, the execution of the Peeking (Bind) function can be performed to some variable values, which may not be the best, and may even cause serious performance problems.

SQL> exec: x = 800;

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TEST_ACS WHERE C1: x;

C1 C2
.Employed
1 A
...
SQL> select * from table (DBMS)_xplan.display_Cursor (null, null),'NOTE ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID cuf4u4th4w0nz, child number 0
Similiarly
SELECT * FROM TEST_ACS WHERE C1: X

Plan hash value: 3882350253

---------------------------------------------------------
Id Operation Name | | | | E-Rows |
---------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 TABLE ACCESS BY INDEX ROWID| TEST |_ACS | 30 |
2 INDEX RANGE SCAN |* | | IND1 | 30 |
---------------------------------------------------------

Predicate Information (identified by operation ID):
Away

2 - access (C1 < = X)

...

25 rows selected.

In the example above, we see the bound variables (Bind Peeking) on the influence of the variable value is 9, although this is the Scan Full Table should choose the optimal execution plan, or use the first execution according to the variable value of 3 made the execution plan.
Therefore, when the initial value of the variable value cannot be used to represent the characteristics of most variables, the production system can sometimes be catastrophic.

Example 2 (ACS)

Let us take a look at the 11g version of the adaptive cursor sharing (Adaptive Cursor Sharing referred to as ACS) when the function is effective, how to improve this problem.

ACS related dynamic views

In the future, we can see what happens in the process by observing the relevant dynamic views.
First, we introduce the following in the process of using the main 4 views, the following is a brief introduction of the relevant content.

V$SQL view
There are 3 ACS related columns in this view:
Is_bind_sensitive: is used to monitor whether the SQL cursor is bound to sensitive (bind sensitive), whether it is available ACS.
Is_bind_aware: used to monitor whether the SQL cursor is aware of the need to select an execution plan (bind aware) based on the value of the bound variable.
Is_shareable: used to monitor whether SQL cursors can be shared.

V$SQL_SHARED_CURSOR
Reason for viewing SQL cursor not shared.

V$SQL_CS_SELECTIVITY view
Mainly used to monitor the SQL cursor of each variable selection rate range (SELECTIVITY CUBE).
It contains information about the conditional predicates, bound variables, and their maximum and minimum values.

V$SQL_CS_HISTOGRAM
According to the number of rows to be operated, the number of times that each child cursor executes.

V$SQL_CS_STATISTICS
Execution status of each child cursor. (sampling information)

For more information on the definition and content of the above view can refer to the online documentation.

Oracle Database Documentation Release 2 (11.2) / Database Reference 11g (Online)

V$SQL
V$SQL_SHARED_CURSOR
V$SQL_CS_SELECTIVITY
V$SQL_CS_HISTOGRAM
V$SQL_CS_STATISTICS

Here's our test (based on the 11.2.0.4 version):

Prepare test tables and data

0 prepare test tables and data

SQL>
SQL> conn scott/tiger
Connected.
SQL>
SQL> drop table TEST_ACS;
Table dropped.
SQL>CreateTable TEST_ACS (C1 number, C2 VARCHAR2)One hundred));
Table created.
SQL>
SQL>BEGIN
      Two      FORIIN One.One thousand LOOP
      Three      FORJIN One.Ten LOOP
      FourINSERTINTOTEST_ACS
      FiveVALUES (I),'a');
      SixCOMMIT;
      Seven      END LOOP;
      Eight      END LOOP;
      Nine  END;
     Ten/
PL/SQLProcedure Successfully Completed.
    SQL>
    SQL>Create Index Ind1 On TEST_ACS(C1);    
    IndexCreated.
SQL> exec dbms_stats.gather_table_stats (ownname = >'SCOTT'Tabname = >.'TEST_ACS'CASCADE = >.TRUE);
PL/SQLProcedure Successfully Completed.

We prepared a table containing 10000 data,C1Column has the value of 1~1000, and each value has 10 data.
--- and inC1Column has an indexInd1
    SQL>SELECT Count(C1),Count(DISTINCTC1),Min(C1),Max(C1) FROM TEST_ACS;
COUNT (C1) COUNT (DISTINCTC1) MIN (C1) MAX (C1)
-----------------------------------------------
     Ten thousand  One thousand  One   One thousand

Test1: specifies a variable value of 3 for the first time

First, we specify the variable value of 3, the first implementation of the following SQL

SQL> var x number;
SQL> exec: x = 3;

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TEST_ACS WHERE C1: x;

C1 C2
--------------------------
1 A
...
3 A

30 rows selected.

SQL>

1 because bound variables (Bind Peeking) on the functional effects, so choose the hard parse execution plan is to bind variable values into the calculation of the base 3 SQL in this paper, SQL paper chose 30 data in the 10000 data, so choose the index IND1 INDEX RANGE SCAN.

SQL> set line 200
SQL> set PageSize 9999
SQL> select * from table (null, null,'NOTE ALLSTATS LAST')) dbms_xplan.display_cursor;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
SQL_ID cuf4u4th4w0nz, child number 0
Similiarly
SELECT * FROM TEST_ACS WHERE C1: X

Plan hash value: 3882350253

---------------------------------------------------------
Id Operation Name | | | | E-Rows |
---------------------------------------------------------
| 0 | SELECT STATEMENT| ||
| 1 TABLE ACCESS BY INDEX ROWID| TEST_ACS | | 30 |
2 INDEX RANGE SCAN |* | | IND1 | 30 |
---------------------------------------------------------

Predicate Information (identified by operation ID):
Away

2 - access (C1 < = X)

When the SQL was first performed, because bound variables (Bind Peeking) on the functional effects, so choose the hard parse execution plan is to bind variable values into 3 SQL the selection rate calculation.
Because the SQL chose 30 data in the 10000 data, selection rate is 0.03, so the optimizer has chosen to access the data through the index IND1 (INDEX RANGE SCAN).

SQL> col PREDICATE format A20
SQL> ---sql_id:cuf4u4th4w0nz
SQL> select sql_id, child_number, executions, buffer_gets,
2 is_bind_sensitive BS, is_bind_aware BA, IS_SHAREABLE SH, plan_hash_value
3 from v$sql
4 where sql_id ='cuf4u4th4w0nz';

SQL_ID CHILD_NUMBER BUFFER_GETS B B S EXECUTIONS PLAN_HASH_VALUE
----------------------------------------------------------------
Cuf4u4th4w0nz0 17 Y Y N 3882350253

---1.V$SQL.is_bind_sensitive is Y, the cursor is marked as bind-sensitive, you can use ACS.
---V$SQL.IS_SHAREABLE is Y, cursors can be shared
---V$SQL.is_bind_aware is N, not aware of the different binding variables will affect the execution plan

SQL>
SQL> select sql_id, child_number, BIND_EQUIV_FAILURE, LOAD_OPTIMIZER_STATS
2 from V$SQL_SHARED_CURSOR
3 where sql_id ='cuf4u4th4w0nz';

SQL_IDCHILD_NUMBER B L
---------------------------
Cuf4u4th4w0nz0 N N

---1. cursor sharing information V$SQL_SHARED_CURSOR.BIND_EQUIV_FAILURE is N
---V$SQL_SHARED_CURSOR.LOAD_OPTIMIZER_STATS for N (for later comparison)

SQL>
SQL> select *
2 from v$sql_cs_statistics
3 where sql_id='cuf4u4th4w0nz';

ADDRESS HASH_VALUE BIND_SET_HASH_VALUE P ROWS_PROCESSED BUFFER_GETS EXECUTIONS SQL_IDCHILD_NUMBER CPU_TIME
--------------------------------------------------------------------------------------------------------------------
000000008A976370 1615725215 cuf4u4th4w0nz0 2706503459 Y 16070

---1.v$sql_cs_statistics sampling information, records the execution of the cursor execution, including the number of rows processed, consumption of Buffer and other information

SQL>
SQL> ---no in V$SQL_CS_SELECTIVITY the first for data time.
SQL> select *
2 from v$sql_cs_selectivity
3 where sql_id ='cuf4u4th4w0nz';

No rows selected

There is no information in ---1.V$SQL_CS_SELECTIVITY.

SQL>
SQL> select *
2 from V$SQL_CS_HISTOGRAM
3 where sql_id='cuf4u4th4w0nz';

ADDRESS HASH_VALUE SQL_IDCHILD_NUMBER BUCKET_ID COUNT
-----------------------------------------------------------------------
000000008A976370 1615725215 cuf4u4th4w0nz0 01.
000000008A976370 1615725215 cuf4u4th4w0nz0 10
000000008A976370 1615725215 cuf4u4th4w0nz0 20

---1.V$SQL_CS_HISTOGRAM according to the value of the value of the implementation of the histogram of the number of times, the implementation of the calculation to BUCKET_ID:0, 1 times.

Based on the above information, we can know that some of the following actions occur when the SQL text containing the binding variable information is executed for the first time:

SQL hard text analysis (HARD Parse)
Evaluate the selection rate (Bind Peeking) based on the initial bound variable value of 3
Cursor is marked as bind-sensitive
Select the best execution plan

Preservation:
Record the execution of the cursor execution, including the number of rows processed, the consumption of Buffer and other information
Record the number of cursor execution histograms

Test2: Specifies that the variable value is 9, and executes the SQL

Below we pass a variable 9, again SQL.

SQL> --var x number;
SQL> exec: x = 900;

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TEST_ACS WHERE C1: x;

C1 C2
--------------------------
1 A
1 A
...
900 a
900 a

9000 rows selected.

Or use the original execution plan index scan.

SQL> set line 2000
SQL> set PageSize 9999
SQL> select * from table (null, null,'NOTE ALLSTATS LAST')) dbms_xplan.display_cursor;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
SQL_ID cuf4u4th4w0nz, child number 0
Similiarly
SELECT * FROM TEST_ACS WHERE C1: X

Plan hash value: 3882350253

---------------------------------------------------------
Id Operation Name | | | | E-Rows |
---------------------------------------------------------
| 0 | SELECT STATEMENT| ||
| 1 TABLE ACCESS BY INDEX ROWID| TEST_ACS | | 30 |
2 INDEX RANGE SCAN |* | | IND1 | 30 |
---------------------------------------------------------

Predicate Information (identified by operation ID):
Away

2 - access (C1 < = X)

Although the SQL text in the 10000 data in the selection of 9000 data, the selection rate should be 0.9,
However, because the SQL text has been hard to parse, so this is a soft parse, using the previous implementation plan, through the index IND1 access data (INDEX RANGE SCAN).

Observe the change of dynamic view again:

SQL>
SQL> col PREDICATE format A20
SQL> ---sql_id:cuf4u4th4w0nz
SQL> select sql_id, child_number, executions, buffer_gets,
2 is_bind_sensitive BS, is_bind_aware BA, IS_SHAREABLE SH, plan_hash_value
3 from v$sql
4 where sql_id ='cuf4u4th4w0nz';

SQL_IDCHILD_NUMBER EXECUTIONS B B S BUFFER_GETS PLAN_HASH_VALUE
----------------------------------------------------------------
Cuf4u4th4w0nz0 21239 Y Y N 3882350253

---2.V$SQL does not change the content, and the specified variable value is 3.

SQL>
SQL> select sql_id, child_number, BIND_EQUIV_FAILURE, LOAD_OPTIMIZER_STATS
2 from V$SQL_SHARED_CURSOR
3 where sql_id ='cuf4u4th4w0nz';

SQL_IDCHILD_NUMBER B L
---------------------------
Cuf4u4th4w0nz0 N Y

----2. but because of the selection rate (0.03) and the actual implementation of the selection rate (0.9) gap is very large,
V$SQL_SHARED_CURSOR cursor sharing information in LOAD_OPTIMIZER_STATS into Y

SQL> select *
2 from v$sql_cs_statistics
3 where sql_id='cuf4u4th4w0nz';

ADDRESS HASH_VALUE BIND_SET_HASH_VALUE P ROWS_PROCESSED BUFFER_GETS EXECUTIONS SQL_IDCHILD_NUMBER CPU_TIME
--------------------------------------------------------------------------------------------------------------------
000000008A976370 1615725215 cuf4u4th4w0nz0 2706503459 Y 16070

---2.v$sql_cs_statistics sampling information has not changed. (because it is sampled, so it does not always change)

SQL>
SQL> select *
2 from v$sql_cs_selectivity
3 where sql_id ='cuf4u4th4w0nz';

No rows selected

There is no information in ---2.V$SQL_CS_SELECTIVITY.

SQL> select *
2 from V$SQL_CS_HISTOGRAM
3 where sql_id='cuf4u4th4w0nz';

ADDRESS HASH_VALUE SQL_IDCHILD_NUMBER BUCKET_ID COUNT
-----------------------------------------------------------------------
000000008A976370 1615725215 cuf4u4th4w0nz0 01
000000008A976370 1615725215 cuf4u4th4w0nz0 11
000000008A976370 1615725215 cuf4u4th4w0nz0 20

---2.V$SQL_CS_HISTOGRAM according to the value of the value of the implementation of the histogram of the number of times, the implementation of the calculation to BUCKET_ID:1, 1 times.

Based on the above information, we can know that when the SQL text second times,
And some of the following actions occur when the value of the bound variable and the value of the previous bound variable are large:

SQL text for soft parsing (Soft Parse)
Use the previous implementation plan
Comparison of statistical information (estimated selection rate: 0.03 and the actual implementation of the selection rate: 0.9)
If the gap is large, the V$SQL_SHARED_CURSOR cursor sharing information in LOAD_OPTIMIZER_STATS becomes Y. (activate ECS)

Preservation:
Record the execution of the cursor execution, including the number of rows processed, the consumption of Buffer and other information
Record the number of cursors performed by the cursor (BUCKET)
SQL changed to BIND Aware

Test3: Specifies that the variable value is 9, and executes SQL again

Below we pass a variable 9, again SQL:

SQL> --var x number;
SQL> exec: x = 900;

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TEST_ACS WHERE C1: x;

C1 C2
--------------------------
67 A
...
900 a

9000 rows selected.

SQL>

Scan through full table.

SQL> set line 2000
SQL> set PageSize 9999
SQL> select * from table (null, null,'NOTE ALLSTATS LAST')) dbms_xplan.display_cursor;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
SQL_ID cuf4u4th4w0nz, child number 1
Similiarly
SELECT * FROM TEST_ACS WHERE C1: X

Plan hash value: 3781643149

-----------------------------------------------
Id Operation Name | | | | E-Rows |
-----------------------------------------------
| 0 | SELECT STATEMENT | ||
1 TABLE ACCESS FULL| TEST_ACS |* | | 9009 |
-----------------------------------------------

Predicate Information (identified by operation ID):
Away

1 - filter (C1 < = X)

Found since the last execution, due to the impact of the variables, the SQL selection rate forecast value and the actual implementation of the difference (Bind Aware), so this paper re execute SQL compiler, execute change plan execution through a full table scan.

Observe the change of dynamic view again:

SQL> col PREDICATE format A20
SQL> ---sql_id:cuf4u4th4w0nz
SQL> select sql_id, child_number, executions, buffer_gets,
2 is_bind_sensitive BS, is_bind_aware BA, IS_SHAREABLE SH, plan_hash_value
3 from v$sql
4 where sql_id ='cuf4u4th4w0nz';

SQL_IDCHILD_NUMBER EXECUTIONS B B S BUFFER_GETS PLAN_HASH_VALUE
----------------------------------------------------------------
Cuf4u4th4w0nz0 21239 Y N N 3882350253
Cuf4u4th4w0nz1 1622 Y Y Y 3781643149

A new cursor was generated in ---3.V$SQL,
And the newly generated cursor 1IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE are Y.
The original cursor 0, IS_SHAREABLE to N.

SQL> select sql_id, child_number, BIND_EQUIV_FAILURE, LOAD_OPTIMIZER_STATS
2 from V$SQL_SHARED_CURSOR
3 where sql_id ='cuf4u4th4w0nz';

SQL_IDCHILD_NUMBER B L
---------------------------
Cuf4u4th4w0nz0 N Y
Cuf4u4th4w0nz1 Y N

---3. V$SQL_SHARED_CURSOR to generate a BIND_EQUIV_FAILURE cursor for Y.
The selection rate for the bound variable value is not consistent with the presence of the cursor

SQL> select *
2 from v$sql_cs_statistics
3 where sql_id='cuf4u4th4w0nz';

ADDRESS HASH_VALUE BIND_SET_HASH_VALUE P ROWS_PROCESSED BUFFER_GETS EXECUTIONS SQL_IDCHILD_NUMBER CPU_TIME
--------------------------------------------------------------------------------------------------------------------
000000008A976370 1615725215 cuf4u4th4w0nz1 3116944019 Y 190006220
000000008A976370 1615725215 cuf4u4th4w0nz0 2706503459 Y 16070.

A new sample is added to the ---3. v$sql_cs_statistics sampling information

SQL> select *
2 from v$sql_cs_selectivity
3 where sql_id ='cuf4u4th4w0nz';

ADDRESS HASH_VALUE PREDICATE RANGE_ID SQL_IDCHILD_NUMBER LOWHIGH
-----------------------------------------------------------------------------------------------------
000000008A976370 1615725215 cuf4u4th4w0nz1 <=X 0.810810 0.990990

The rate of selection of the newly added cursor was recorded in ---3. V$SQL_CS_SELECTIVITY (depending on the value of the bound variable)

SQL>
SQL> select *
2 from V$SQL_CS_HISTOGRAM
3 where sql_id='cuf4u4th4w0nz';

ADDRESS HASH_VALUE SQL_IDCHILD_NUMBER BUCKET_ID COUNT
-----------------------------------------------------------------------
000000008A976370 1615725215 cuf4u4th4w0nz1 00
000000008A976370 1615725215 cuf4u4th4w0nz1 11.
000000008A976370 1615725215 cuf4u4th4w0nz1 20
000000008A976370 1615725215 cuf4u4th4w0nz0 01
000000008A976370 1615725215 cuf4u4th4w0nz0 11
000000008A976370 1615725215 cuf4u4th4w0nz0 20

6 rows selected.

---3. V$SQL_CS_HISTOGRAM recorded a new addition to the histogram of the cursor, and record the number of times to BUCKET_ID:1, 1 times.

Based on the above information, we can know that when the SQL text is executed third times (and the same variable for the second time), there will be some of the following actions:

SQL hard text analysis (Hard Parse)
Generate new execution plan
V$SQL_SHARED_CURSOR cursor sharing information in BIND_EQUIV_FAILURE to Y.
V$SQL generated in IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE are new cursor 1 Y
The original cursor 0, IS_SHAREABLE to N

Preservation:
Record the execution status of the cursor, including the number of rows to be processed, the consumption of Buffer, etc.
Record the number of times the cursor executes (BUCKET)
SQL changed to BIND Aware
Record cursor selection rate range (selectivity cube)

Test4: Specifies that the variable value is 3, and executes the SQL text again (SELECTIVITY CUBE)

Specifies that the value of the variable is 3 (as it was initially executed), and then SQL

SQL> --var x number;
SQL> exec: x = 3;

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TEST_ACS WHERE C1: x;

C1 C2
--------------------------
1 A
...
3 A

30 rows selected.

---SQL text re parsing, through index scan execution

SQL> set line 200
SQL> set PageSize 9999
SQL> select * from table (null, null,'NOTE ALLSTATS LAST')) dbms_xplan.display_cursor;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
SQL_ID cuf4u4th4w0nz, child number 2
Similiarly
SELECT * FROM TEST_ACS WHERE C1: X

Plan hash value: 3882350253

---------------------------------------------------------
Id Operation Name | | | | E-Rows |
---------------------------------------------------------
| 0 | SELECT STATEMENT| ||
| 1 TABLE ACCESS BY INDEX ROWID| TEST_ACS | | 30 |
2 INDEX RANGE SCAN |* | | IND1 | 30 |
---------------------------------------------------------

Predicate Information (identified by operation ID):
Away

2 - access (C1 < = X)

The implementation of the plan has changed, SQL text re parsing, through index scan execution.
Observe the change of dynamic view again:

SQL> ---sql_id:cuf4u4th4w0nz
SQL> col PREDICATE format A20
SQL> ---sql_id:cuf4u4th4w0nz
SQL> select sql_id, child_number, executions, buffer_gets,
2 is_bind_sensitive BS, is_bind_aware BA, IS_SHAREABLE SH, plan_hash_value
3 from v$sql
4 where sql_id ='cuf4u4th4w0nz';

SQL_ID CHILD_NUMBER BUFFER_GETS B B S EXECUTIONS PLAN_HASH_VALUE
----------------------------------------------------------------
Cuf4u4th4w0nz0 21239 Y N N 3882350253
Cuf4u4th4w0nz1 1622 Y Y Y 3781643149
Cuf4u4th4w0nz2 17 Y Y Y 3882350253.

---4.V$SQL generates a new cursor CHILD# 2,
And the newly generated cursor IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE are Y.

SQL> select *
2 from v$sql_cs_statistics
3 where sql_id='cuf4u4th4w0nz';

ADDRESS HASH_VALUE BIND_SET_HASH_VALUE P ROWS_PROCESSED BUFFER_GETS EXECUTIONS SQL_IDCHILD_NUMBER CPU_TIME
--------------------------------------------------------------------------------------------------------------------
000000008A976370 1615725215 cuf4u4th4w0nz2 2706503459 Y 16070.
000000008A976370 1615725215 cuf4u4th4w0nz1 3116944019 Y 190006220
000000008A976370 1615725215 cuf4u4th4w0nz0 2706503459 Y 16070

A new sample is added to the ---4. v$sql_cs_statistics sampling information

SQL>
SQL> ---select CHILD_NUMBER, PREDICATE, RANGE_ID, LOW, HIGH
SQL> select *
2 from v$sql_cs_selectivity
3 where sql_id ='cuf4u4th4w0nz';

ADDRESS HASH_VALUE CHILD_NUMBER PREDICATE RANGE_ID SQL_ID LOWHIGH
-----------------------------------------------------------------------------------------------------
000000008A976370 1615725215 cuf4u4th4w0nz2 0.002702 <=X 0.003302.
000000008A976370 1615725215 cuf4u4th4w0nz1 <=X 0.810810 0.990990

---4. V$SQL_CS_SELECTIVITY has recorded a new option for the additional cursor CHILD# 2 (depending on the value of the bound variable)

SQL> select *
2 from V$SQL_CS_HISTOGRAM
3 where sql_id='cuf4u4th4w0nz';

ADDRESS HASH_VALUE SQL_IDCHILD_NUMBER BUCKET_ID COUNT
-----------------------------------------------------------------------
000000008A976370 1615725215 cuf4u4th4w0nz2 01.
000000008A976370 1615725215 cuf4u4th4w0nz2 10
000000008A976370 1615725215 cuf4u4th4w0nz2 20
000000008A976370 1615725215 cuf4u4th4w0nz1 00
000000008A976370 1615725215 cuf4u4th4w0nz1 11
000000008A976370 1615725215 cuf4u4th4w0nz1 20
000000008A976370 1615725215 cuf4u4th4w0nz0 01
000000008A976370 1615725215 cuf4u4th4w0nz0 11
000000008A976370 1615725215 cuf4u4th4w0nz0 20

9 rows selected.

---4. V$SQL_CS_HISTOGRAM recorded in the CHILD# 2 histogram, and record the number of times to BUCKET_ID:0, 1 times.

Based on the above information, we can know that when the SQL text is executed fourth times (and the same variable for the first time),
Since the first generated cursor becomes IS_SHAREABLE to N, some of the following actions will occur:

SQL hard text analysis (Hard Parse)
Generate new execution plan
V$SQL generated in IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE are new cursors Y

Preservation:
Record the execution status of the cursor, including the number of rows to be processed, the consumption of Buffer, etc.
Record the number of times the cursor executes (BUCKET)
Record cursor selection rate range (selectivity cube)

Test5: Specifies that the variable value is 5, and SQL (Merge) is executed again

Specifies that the value of the variable is 5, and SQL is executed again
We know that for the SQL the variable value is 5 in line with the conditions of the selection rate is 50/10000=0.005, is not performed before the cursor selection rate range (the value of the variable selection rate of 3 for a range of 0.002702 to 0.003302; the value of the variable selection rate of 9 for a range of 0.810810 to 0.990990),
So SQL text is still a hard parse.

SQL> --var x number;
SQL> exec: x = 5;

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TEST_ACS WHERE C1: x;

C1 C2
--------------------------
1 A
...
5 A
5 A

50 rows selected.
The ---SQL text selects the optimal execution plan through index scans.
SQL> set line 200
SQL> set PageSize 9999
SQL> select * from table (null, null,'NOTE ALLSTATS LAST')) dbms_xplan.display_cursor;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
SQL_ID cuf4u4th4w0nz, child number 3
Similiarly
SELECT * FROM TEST_ACS WHERE C1: X

Plan hash value: 3882350253

---------------------------------------------------------
Id Operation Name | | | | E-Rows |
---------------------------------------------------------
| 0 | SELECT STATEMENT| ||
| 1 TABLE ACCESS BY INDEX ROWID| TEST_ACS | | 50 |
2 INDEX RANGE SCAN |* | | IND1 | 50 |
---------------------------------------------------------

Predicate Information (identified by operation ID):
Away

2 - access (C1 < = X)

SQL text re parsing, through index scan execution. Observe the change of dynamic view again:

SQL> col PREDICATE format A20
SQL> ---sql_id:cuf4u4th4w0nz
SQL> select sql_id, child_number, executions, buffer_gets,
2 is_bind_sensitive BS, is_bind_aware BA, IS_SHAREABLE SH, plan_hash_value
3 from v$sql
4 where sql_id ='cuf4u4th4w0nz';

SQL_IDCHILD_NUMBER EXECUTIONS B B S BUFFER_GETS PLAN_HASH_VALUE
----------------------------------------------------------------
Cuf4u4th4w0nz0 21239 Y N N 3882350253
Cuf4u4th4w0nz1 1622 Y Y Y 3781643149
Cuf4u4th4w0nz2 17 Y N Y 3882350253
Cuf4u4th4w0nz3 111 Y Y Y 3882350253.

---5.V$SQL generates a new cursor CHILD#3,
CHILD#3 cursor IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE are Y;
And we note that the CHILD#2 with the new generation of CHILD#3 that has the same execution plan as IS_SHAREABLE becomes N,
Also said that CHILD#2 is no longer shared into the age out queue.

SQL> select *
2 from v$sql_cs_statistics
3 where sql_id='cuf4u4th4w0nz';

ADDRESS HASH_VALUE BIND_SET_HASH_VALUE P ROWS_PROCESSED BUFFER_GETS EXECUTIONS SQL_IDCHILD_NUMBER CPU_TIME
--------------------------------------------------------------------------------------------------------------------
000000008A976370 1615725215 cuf4u4th4w0nz3 365452098 Y 1100110.
000000008A976370 1615725215 cuf4u4th4w0nz2 2706503459 Y 16070
000000008A976370 1615725215 cuf4u4th4w0nz1 3116944019 Y 190006220
000000008A976370 1615725215 cuf4u4th4w0nz0 2706503459 Y 16070

A new sample is added to the ---5. v$sql_cs_statistics sampling information

SQL> select *
2 from v$sql_cs_selectivity
3 where sql_id ='cuf4u4th4w0nz';

ADDRESS HASH_VALUE PREDICATE RANGE_ID LOW SQL_IDCHILD_NUMBER HIGH
-----------------------------------------------------------------------------------------------------
000000008A976370 1615725215 cuf4u4th4w0nz3 0.002702 <=X 0.005504.
000000008A976370 1615725215 cuf4u4th4w0nz2 <=X 0.002702 0.003302
000000008A976370 1615725215 cuf4u4th4w0nz1 <=X 0.810810 0.990990

---5. V$SQL_CS_SELECTIVITY recorded a new additional CHILD# cursor selection rate of 3 (range depends on the bound variable value 5) and the choice of CHILD#3 (0.002702 ~ 0.005504) rate of the CHILD#2 selection rate range also includes, (0.002702 ~ 0.003302).

SQL> select *
2 from V$SQL_CS_HISTOGRAM
3 where sql_id='cuf4u4th4w0nz';

ADDRESS HASH_VALUE CHILD_NUMBER BUCKET_ID SQL_ID COUNT
-----------------------------------------------------------------------
000000008A976370 1615725215 cuf4u4th4w0nz 301.
000000008A976370 1615725215 cuf4u4th4w0nz 310.
000000008A976370 1615725215 cuf4u4th4w0nz 320.
000000008A976370 1615725215 cuf4u4th4w0nz 201
000000008A976370 1615725215 cuf4u4th4w0nz 210
000000008A976370 1615725215 cuf4u4th4w0nz 220
000000008A976370 1615725215 cuf4u4th4w0nz 100
000000008A976370 1615725215 cuf4u4th4w0nz 111
000000008A976370 1615725215 cuf4u4th4w0nz 120
000000008A976370 1615725215 cuf4u4th4w0nz 001
000000008A976370 1615725215 cuf4u4th4w0nz 011
000000008A976370 1615725215 cuf4u4th4w0nz 020

12 rows selected.

---5. V$SQL_CS_HISTOGRAM recorded in the CHILD# 3 histogram, and record the number of times to BUCKET_ID:0, 1 times.

Based on the above information, we can know that when the SQL text fifth times (not in the range of the value of the variable value before the selection), the following actions will occur:

SQL hard text analysis (Hard Parse)
Generate new execution plan
V$SQL generated in IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE are new cursors Y
And mark IS_SHAREABLE, which has the same execution plan as N, as the object of age out.
Merge cursors with the same plan (Merge)


Preservation:
Record the execution status of the cursor, including the number of rows to be processed, the consumption of Buffer, etc.
Record the number of times the cursor executes (BUCKET)
Record the cursor selection rate (selectivity cube) after merging

ACS through the combination of such operations, you can minimize the number of sub cursors, to avoid the generation of too many sub cursors, memory and other resources to reduce waste.

supplement

We also see that in the V$SQL_CS_HISTOGRAM view, each sub cursor has 3 lines of histogram data were Bucket, 0, Bucket 1 and Bucket 2, for each SQL implementation, Oracle will record the cursor in the corresponding Bucket sub line of the execution times.

What is the number of times the child cursor is recorded in the Bucket?
In fact, Oracle is based on the actual implementation of the number of Processing (Row Source) to generate the histogram.

Generally more than the number of records in the following manner:

Bucket 0: <1024 (1K) line,
Bucket 1: greater than or equal to 1024 lines less than 1024*1024 (1M) line,
Bucket 2: greater than or equal to 1024*1024 (1M) line.

And the biggest role of this histogram is to compare the height of the Child#0 SQL Bucket (COUNT), if the height of the Bucket to meet certain conditions, it will enable ACS (Bind Aware).
Please note:
This is only the simplest way to judge logic, as well as other more complex logic of judgment.

ACS processing flow

The following reference to the Oracle ACE Houri to do a ACS flow chart, to review the overall process of ACS Mohamed.

Here to write picture description

summary

Through the above example, we have a basic understanding of the basic process of ACS and the benefits of ACS:
Solve the performance problems caused by the bind variable peek function.

Reference resources

Oracle Database Documentation Release 1 (12.1) / Database SQL Tuning Guide Online (12c)
>Adaptive Cursor Sharing

Copyright statement: This article for the original article, please indicate the source, thank you.Http://prog3.com/sbdm/blog/lukeunique

top
Zero
step on
Zero

Reference knowledge base

Guess you are looking for
View comments
* the above user comments represent their personal views and do not represent the views or positions of the CSDN website
    personal data
    • Visit:48204 times
    • Integral:Eight hundred and eleven
    • Grade:
    • Ranking:A thousand miles away
    • Original:31
    • Reprint:0
    • Translation:0
    • Comment:12
    About This Blog
    The goal of this blog is to study. The views expressed on this blog are my own and do not necessarily reflect the views of any company and its affiliates. The views and opinions expressed by visitors on this blog are theirs solely and may not reflect mine.
    WeChat public

    Welcome to WeChat subscription number: TeacherWhat
    Here to write picture description

    Blog column
    Latest comments