Shulou(Shulou.com)05/31 Report--

This article mainly explains "Adaptive Cursor Sharing Analysis". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "Adaptive Cursor Sharing Analysis" together.

ACS best practices

Although ACS can solve the problems caused by binding variable snooping, it also has some disadvantages:

1) once a SQL is marked as binding-sensitive, the optimizer monitors the number of rows processed by the SQL statement, and updates the v$sql_cs_histogram view once the number of rows changes dramatically. But this doesn't seem to cost much, because the v$sql_cs_histogram view is updated only after the SQL execution is complete and does not take up parsing time, so the update can be asynchronous.

2) after Cursor is marked as bind aware, in the parsing phase, the value of the variable is snooped and the selection rate of predicates is calculated. The calculated value is compared with the value in the corresponding v$sql_cs_selectivity view to see whether it is already within the existing range of selection rate. If not, hard parsing will occur. In order to snoop on the value of variables, calculate the selection rate, to a certain extent, increase the time of analysis. Parsing here refers to soft parsing. But the impact may not be that great, because not all SQL in a database will be marked as bind aware, and only SQL that has undergone a significant change in the dataset of the operation will be marked as bind aware.

3) there is an unstable period when using ACS, that is, the SQL execution plan has to go through the process of getting worse and then getting better. The v$sql_cs_histogram view records the amount of data processed by SQL, represented by three bucket. If the number of rows processed by SQL changes dramatically, that is, if the number of rows processed is scattered in at least 2 buckets, the next parse will peek into the value of the bound variable and re-hard parse to generate the execution plan.

4) once the SQL is flushed out of the shared pool, the SQL also needs to repeat the process from the ACS instability to the stabilizer.

5) bug exists in PL/SQL. If you do not adjust the session_cached_cursors parameter to 0, you will not be able to use the ACS feature.

In the production environment that I am responsible for managing, ACS is closed, and although I have done some research and testing on ACS myself, I have always stayed at a distance. Is there any way to use the features of ACS while avoiding these shortcomings to some extent? First we need to introduce a hint-bind_aware.

The usage and function of bind_aware

After using the function of ACS, a cursor goes from bind sensitive to bind aware, with an unstable period in the middle. If you use bind_aware hint in the cursor, it will make the state of the cursor directly enter the bind aware without experiencing the bind sensitive state. Let's take a look at this through an example:

SQL > CREATE TABLE test

2 AS

3 SELECT ROWNUM id

4 DBMS_RANDOM.STRING ('Aguilar, 12) name

5 DECODE (MOD (ROWNUM, 500), 0, 'Inactive',' Active') status

6 FROM all_objects

7 WHERE ROWNUM CREATE INDEX test_id_ind ON test (status)

Index created.

SQL > begin

2 dbms_stats.gather_table_stats (user

3 'test'

4 method_opt = > 'for columns status size 254'

5 cascade = > true)

6 end

7 /

PL/SQL procedure successfully completed.

SQL > SELECT COUNT (*) cnt, status

2 FROM test

3 GROUP BY status

4 /

CNT STATUS

--

49900 Active

100 Inactive

The above code has appeared several times in this chapter, and the main purpose is to create a table with a column of STATUS with data skew, an index on the column, and a histogram collection on that column. Let's take a look at what ACS will do when we add bind aware's hint to SQL.

We first query the case where STATUS is Inactive, which is a minority in the table.

SQL > alter system flush shared_pool

System altered.

SQL > var a varchar2

SQL > exec: a: = 'Inactive'

PL/SQL procedure successfully completed.

SQL >

SQL > select / * + bind_aware * / count (name) from test where status=:a

COUNT (NAME)

-

one hundred

SQL > select * from table (dbms_xplan.display_cursor (null,null))

PLAN_TABLE_OUTPUT

-

SQL_ID a5fy4g63j8vzr, child number 0

-

Select / * + bind_aware * / count (name) from test where status=:a

Plan hash value: 2948918962

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

-

| | 0 | SELECT STATEMENT | 2 (100) | |

| | 1 | SORT AGGREGATE | | 1 | 25 |

| | 2 | TABLE ACCESS BY INDEX ROWID | TEST | 133 | 3325 | 2 (0) | 00:00:01 |

| | * 3 | INDEX RANGE SCAN | TEST_ID_IND | 133 | | 1 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

3-access ("STATUS" =: a)

SQL > SELECT child_number, executions, buffer_gets, is_bind_sensitive

2 is_bind_aware

3 FROM v$sql

4 WHERE sql_id='a5fy4g63j8vzr'

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS

-

0 1 254 Y Y

As you can see from the is_bind_aware output of v$sql as Y, SQL has been marked as bind aware after only one execution and has not experienced instability. Let's take a look at the performance when STATUS is Active:

SQL > exec: a: = 'Active'

PL/SQL procedure successfully completed.

SQL > select / * + bind_aware * / count (name) from test where status=:a

COUNT (NAME)

-

49900

SQL > select * from table (dbms_xplan.display_cursor (null,null))

PLAN_TABLE_OUTPUT

SQL_ID a5fy4g63j8vzr, child number 1

-

Select / * + bind_aware * / count (name) from test where status=:a

Plan hash value: 1950795681

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | 51 (100) | | |

| | 1 | SORT AGGREGATE | | 1 | 25 |

| | * 2 | TABLE ACCESS FULL | TEST | 49862 | 1217K | 51 (2) | 00:00:01 |

Predicate Information (identified by operation id):

2-filter ("STATUS" =: a)

19 rows selected.

SQL > SELECT child_number, executions, buffer_gets, is_bind_sensitive

2 is_bind_aware

3 FROM v$sql

4 WHERE sql_id='a5fy4g63j8vzr'

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS

-

0 1 1486 Y Y

1 1 210 Y Y

Very good! After we changed the value of the binding variable to Active, we got the correct execution plan on the first execution, and a new entry has been added to v$sql to record the execution plan of the newly generated cursor. Therefore, after using the hint of bind aware, the cursor will not experience an unstable period. Every time SQL parses, it will snoop on the value of the bound variable, and then calculate the selection rate. If the calculated selection rate does not match the selection rate of the existing cursor, a new cursor will be generated based on the hard parsing of the value of the snooped bound variable. If you confirm that a SQL needs to use the ACS feature, but do not want it to go through an unstable period, then you can do this through the bind aware hint. There are also some ways to try, for example, we can turn off the function of ACS and turn on the function of ACS separately for SQL if necessary. It may be a habit inherited from Ali to do DBA, like to turn off binding variable snooping directly, after binding variable snooping is turned off, ACS is automatically closed. Then for SQL that needs to use ACS, add hint,OPT_PARAM ('_ optim_peek_user_binds' 'true') bind_aware to use the ACS feature, and OPT_PARAM (' _ optim_peek_user_binds' 'true') is used to turn on binding variable snooping at the SQL statement level. When binding variable snooping and ACS are turned off, the shortcomings of ACS mentioned above are circumvented. DBA can selectively use ACS for some SQL. Of course, this requires a higher DBA, need to understand the application, understand the data distribution characteristics of the table, understand the query characteristics of the SQL on the table. Some large companies have been equipped with the role of application DBA, responsible for the development of SQL REVIEW and other work, DBA can be in the SQL REVIEW phase by understanding the application of SQL, the need for SQL to add ACS function. If you can't add hint in the first place, you can also use sql profile,sql patch to bind these hint to SQL statements without modifying the SQL statement. The SPM baseline is invalid here.

For example, we can add the ACS function to a SQL through sql profile:

SQL > show parameter binds

NAME TYPE VALUE

-

_ optim_peek_user_binds boolean FALSE

SQL > var a varchar2

SQL > exec: a: = 'Active'

PL/SQL procedure successfully completed.

SQL > select count (name) from test where status=:a

COUNT (NAME)

-

49900

SQL > select * from table (dbms_xplan.display_cursor (null,null))

PLAN_TABLE_OUTPUT

SQL_ID 7yjf9wt1rt8a6, child number 0

-

Select count (name) from test where status=:a

Plan hash value: 1950795681

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | 51 (100) | | |

| | 1 | SORT AGGREGATE | | 1 | 25 |

| | * 2 | TABLE ACCESS FULL | TEST | 25000 | 610K | 51 (2) | 00:00:01 |

Predicate Information (identified by operation id):

2-filter ("STATUS" =: a)

SQL > SELECT child_number, executions, buffer_gets, is_bind_sensitive

2 is_bind_aware

3 FROM v$sql

4 WHERE sql_id='7yjf9wt1rt8a6'

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS

-

0 2 540 N N

When binding variable snooping is turned off, the function of ACS is also turned off, and the bind sensitive statement of SQL is marked N. Let's add hint through sql profile.

SQL > @ profile

Enter value for sql_id: 7yjf9wt1rt8a6

PLAN_TABLE_OUTPUT

SQL_ID 7yjf9wt1rt8a6, child number 0

-

Select count (name) from test where status=:a

Plan hash value: 1950795681

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | 51 (100) | | |

| | 1 | SORT AGGREGATE | | 1 | 25 |

| | * 2 | TABLE ACCESS FULL | TEST | 25000 | 610K | 51 (2) | 00:00:01 |

Outline Data

-

/ * +

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE ('11.2.0.3')

DB_VERSION ('11.2.0.3')

OPT_PARAM ('_ optim_peek_user_binds' 'false')

OPT_PARAM ('_ optimizer_skip_scan_enabled' 'false')

ALL_ROWS

OUTLINE_LEAF (@ "SEL$1")

FULL (@ "SEL$1"TEST" @ "SEL$1")

END_OUTLINE_DATA

, /

Predicate Information (identified by operation id):

2-filter ("STATUS" =: a)

35 rows selected.

Enter value for hint_text: OPT_PARAM ('_ optim_peek_user_binds' 'true') bind_aware

Profile profile_7yjf9wt1rt8a6_dwrose created.

SQL > select count (name) from test where status=:a

COUNT (NAME)

-

49900

1 row selected.

SQL > SELECT child_number, executions, buffer_gets, is_bind_sensitive

2 is_bind_aware

3 FROM v$sql

4 WHERE sql_id='7yjf9wt1rt8a6'

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS

-

0 1 270 Y Y

1 row selected.

SQL > select * from table (dbms_xplan.display_cursor ('7yjf9wt1rt8a6))

PLAN_TABLE_OUTPUT

SQL_ID 7yjf9wt1rt8a6, child number 0

-

Select count (name) from test where status=:a

Plan hash value: 1950795681

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | 51 (100) | | |

| | 1 | SORT AGGREGATE | | 1 | 25 |

| | * 2 | TABLE ACCESS FULL | TEST | 49862 | 1217K | 51 (2) | 00:00:01 |

Predicate Information (identified by operation id):

2-filter ("STATUS" =: a)

Note

-

-SQL profile profile_7yjf9wt1rt8a6_dwrose used for this statement

23 rows selected.

SQL > exec: a: = 'Inactive'

PL/SQL procedure successfully completed.

SQL > select count (name) from test where status=:a

COUNT (NAME)

-

one hundred

1 row selected.

SQL > select * from table (dbms_xplan.display_cursor (null,null))

PLAN_TABLE_OUTPUT

SQL_ID 7yjf9wt1rt8a6, child number 5

-

Select count (name) from test where status=:a

Plan hash value: 2948918962

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

-

| | 0 | SELECT STATEMENT | 2 (100) | |

| | 1 | SORT AGGREGATE | | 1 | 25 |

| | 2 | TABLE ACCESS BY INDEX ROWID | TEST | 133 | 3325 | 2 (0) | 00:00:01 |

| | * 3 | INDEX RANGE SCAN | TEST_ID_IND | 133 | | 1 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

3-access ("STATUS" =: a)

Note

-

-SQL profile profile_7yjf9wt1rt8a6_dwrose used for this statement

24 rows selected.

However, it is a pity that BUG exists in version 11.2.0.3 (other versions have not been tested). When binding variable snooping is turned off at session or system level, if hint OPT_PARAM ('_ optim_peek_user_binds' 'true') bind_aware is used to use ACS function, every time SQL is executed, a new execution plan is generated in the shared pool, and the previously generated plan is marked as not shared. The reason why it cannot be shared is: user_bind_peek_mismatch. This BUG has been fixed in version 12.0.1. Therefore, if you use the way mentioned by the author, you must do a good test before use to prevent the generation of too many sub-cursors. If there are very few unique values for columns with data skew, consider using text variables directly and abandoning the use of bound variables.

SQL > select child_number,user_bind_peek_mismatch from v$sql_shared_cursor where sql_id='7yjf9wt1rt8a6'

CHILD_NUMBER US

0 N

1 Y

2 Y

3 Y

4 Y

5 Y

How to turn off the features of ACS:

Alter system set "_ optimizer_extended_cursor_sharing_rel" = none scope=both

If your system turns off binding variable snooping, it will also turn off ACS automatically.

Alter system set "_ optim_peek_user_binds" = false scope=both

Thank you for your reading, the above is the content of "Adaptive Cursor Sharing Analysis", after the study of this article, I believe you have a deeper understanding of the problem of Adaptive Cursor Sharing analysis, and the specific use needs to be verified by practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.