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

This article introduces the relevant knowledge of "what are the knowledge points of SQL ACS". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

A preliminary study of ACS

ACS is enabled by default, and of course you can turn ACS,ACS on or off with some implicit parameters and introduce some new views and columns.

Two new items are listed in the V$SQL view: IS_BIND_SENSITIVE and IS_BIND_AWARE. The values of IS_BIND_SENSITIVE can be Y and N, and the cursor of Y means that the cursor is monitored by ACS, and the cursor of N means it is not monitored or the ACS function is not enabled. If IS_BIND_AWARE is Y, it means that the cursor will pry into the value of the binding variable every time it parses, calculate the selection rate of the predicate, and then check whether there is an execution plan that meets the requirements in the current shared pool according to the selection rate. If so, reuse it. If not, hard parse and generate a new one.

The V$SQL_CS_HISTOGRAM view, which is the key view of ACS, mainly records the histogram of the number of rows processed by SQL, which drives ACS to play a role. Each child cursor has three bucket available in this view, numbered from 0 to 2, the field bucket_id represents the bucket number, and bucket_id is from 0 to 2 after each SQL execution ends. According to the number of SQL processing rows, the count of the corresponding record in the V$SQL_CS_HISTOGRAM will change, count represents the number of executions, and each bucket represents the data volume range of the cursor operation. In the 11GR2 version, when the number of rows returned is 0-1000 After the SQL execution is completed, the count field of the bucket with bucket_id 0 will be updated. Each time, the value of the count field will be increased by 1. When the number of rows returned in 1000-1000000 SQL execution is completed, the count field of the bucket with bucket_id 1 will be updated, and the number of rows returned will exceed 1000000. The count field of the bucket with bucket_id 2 will be updated. Readers are requested not to memorize the numbers. Future versions may be different. If the number of rows processed by SQL changes dramatically, that is, the number of rows processed is scattered in at least 2 buckets, the next parse is to snoop on the value of the bound variable and re-hard parse to generate the execution plan.

L V$SQL_CS_SELECTIVITY, which records the range of selections of cursor predicates, and only cursor marked as bind aware is recorded in this view. Once a cursor is marked as bind aware, each parsing will pry into the value of the binding variable, calculate the predicate selection rate, and then compare it with the relevant records in this view according to the calculated results. If the calculated selection rate falls within the range of the selection rate of the corresponding cursor in this view, soft parsing reuses the cursor, and if it is not there, hard parsing will generate a new subcursor. There is a section of the article that will focus on this issue.

L V$SQL_CS_STATISTICS records the number of rows processed by cursors, buffer gets and other information, but this view is misleading. The data in the view will only change when new cursors are generated in hard parsing, and these metric values will not change in soft parsing. Once a new row is generated in this view, the SQL generates a new cursor, and the new cursor is the result of the effect of ACS.

The theoretical stuff is too boring, let's first look at a test example. Looking back at these theories, it will be easy to understand.

Test@DLSP > 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 a,dba_objects b

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 2'

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 creates a test table, test, with a column of status data skewed, creates an index on this column, collects the table's statistics, and collects the histogram of the column status. Most of the data status in the table is Active, and very little data status is Inactive. We can start our test by testing the changes in the values in several related views when querying for different values of column status.

SQL > var a varchar2

SQL > exec: a: = 'Inactive'

PL/SQL procedure successfully completed.

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

COUNT (NAME)

-

one hundred

1 row selected.

SQL > select * from table (dbms_xplan.display_cursor)

PLAN_TABLE_OUTPUT

SQL_ID a9cf9a1ky3bda, child number 0

-

Select / * + find_me * / 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 | 87 | 2175 | 2 (0) | 00:00:01 |

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

-

Predicate Information (identified by operation id):

3-access ("STATUS" =: a)

SQL >-- check ACS status

SQL > SELECT child_number, executions, buffer_gets, is_bind_sensitive

2 is_bind_aware

3 FROM v$sql

4 WHERE sql_id='a9cf9a1ky3bda'

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS

-

0 1 148 Y N

SQL >-- histogram

SQL > SELECT hash_value, sql_id, child_number, bucket_id, COUNT

2 FROM v$sql_cs_histogram

3 WHERE sql_id='a9cf9a1ky3bda'

4 ORDER BY sql_id, child_number

HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT

--

1709288874 a9cf9a1ky3bda 0 0 1

1709288874 a9cf9a1ky3bda 0 2 0

1709288874 a9cf9a1ky3bda 0 1 0

SQL >-Statistics

SQL > SELECT hash_value, sql_id, child_number, executions

2 rows_processed

3 FROM v$sql_cs_statistics

4 WHERE sql_id='a9cf9a1ky3bda'

5 ORDER BY sql_id, child_number

HASH_VALUE SQL_ID CHILD_NUMBER EXECUTIONS ROWS_PROCESSED

--

1709288874 a9cf9a1ky3bda 01 201

SQL >

SQL >-selection rate

SQL > SELECT hash_value, sql_id, child_number, predicate, range_id, low, high

2 FROM v$sql_cs_selectivity

3 WHERE sql_id='a9cf9a1ky3bda'

4 ORDER BY sql_id, child_number

No rows selected

From the output above, we can see that the IS_BIND_SENSITIVE of v$SQL is IS_BIND_SENSITIVE ='Y'. The optimizer has marked this SQL as bind sensitive. After referring to statistics such as histogram, the optimizer uses index scan INDEX RANGE SCAN. Because the inactivity value is very small, this is the right decision. Note the output of v$sql and other V$ view output, because the number of rows processed by this SQL is 100. So the count field of the row with the bucket number bucket_id 0 in the view v$sql_cs_histogram has changed from 0 to 1, indicating that it was executed once. Note here that the optimizer's binding-sensitive SQL needs to update the count field of the corresponding bucket in the v$sql_cs_histogram view after each SQL execution.

SQL > exec: a: = 'Active'

PL/SQL procedure successfully completed.

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

COUNT (NAME)

-

49900

1 row selected.

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

PLAN_TABLE_OUTPUT

SQL_ID a9cf9a1ky3bda, child number 0

-

Select / * + find_me * / 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 | 87 | 2175 | 2 (0) | 00:00:01 |

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

-

Predicate Information (identified by operation id):

3-access ("STATUS" =: a)

SQL >-- check ACS status

SQL > SELECT child_number, executions, buffer_gets, is_bind_sensitive

2 is_bind_aware

3 FROM v$sql

4 WHERE sql_id='a9cf9a1ky3bda'

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS

-

0 2 482 Y N

SQL >

SQL >-- histogram

SQL > SELECT hash_value, sql_id, child_number, bucket_id, COUNT

2 FROM v$sql_cs_histogram

3 WHERE sql_id='a9cf9a1ky3bda'

4 ORDER BY sql_id, child_number

HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT

--

1709288874 a9cf9a1ky3bda 0 0 1

1709288874 a9cf9a1ky3bda 0 2 0

1709288874 a9cf9a1ky3bda 0 1 1

SQL >

SQL >-Statistics

SQL > SELECT hash_value, sql_id, child_number, executions

2 rows_processed

3 FROM v$sql_cs_statistics

4 WHERE sql_id='a9cf9a1ky3bda'

5 ORDER BY sql_id, child_number

HASH_VALUE SQL_ID CHILD_NUMBER EXECUTIONS ROWS_PROCESSED

--

1709288874 a9cf9a1ky3bda 01 201

SQL >

SQL >-selection rate

SQL > SELECT hash_value, sql_id, child_number, predicate, range_id, low, high

2 FROM v$sql_cs_selectivity

3 WHERE sql_id='a9cf9a1ky3bda'

4 ORDER BY sql_id, child_number

No rows selected

We see that when we query status as Active, no new cursors are generated in v$sql. The optimizer reuses the same execution plan as status for Inactive. This execution plan is very bad, because there are 49900 status for Active, accounting for most of the data in the table, so it is better to scan the whole table. Taking a closer look at the output, we notice the difference in the output of the two v$sql_cs_histogram. The first output value, only bucket_id 0 record count is 1, the second output value, bucket_id 0 record value remains the same, bucket_id 1 count has changed from 0 to 1, indicating that the optimizer has realized that the number of rows processed by this SQL has been greatly different from the first time. As mentioned at the beginning of this chapter, the number of rows processed this time has exceeded 10000, so the value of count in the record with bucket_id 1 is updated after SQL execution.

Let's continue to use Active as the query value to see what happens:

SQL > exec: a: = 'Active'

PL/SQL procedure successfully completed.

SQL > select / * + find_me * / 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 a9cf9a1ky3bda, child number 1

-

Select / * + find_me * / 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 | 49909 | 1218K | 51 (2) | 00:00:01 |

Predicate Information (identified by operation id):

2-filter ("STATUS" =: a)

SQL >-- check ACS status

SQL > SELECT child_number, executions, buffer_gets, is_bind_sensitive

2 is_bind_aware

3 FROM v$sql

4 WHERE sql_id='a9cf9a1ky3bda'

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS

-

0 2 482 Y N

1 1 210 Y Y

SQL >

SQL >-- histogram

SQL > SELECT hash_value, sql_id, child_number, bucket_id, COUNT

2 FROM v$sql_cs_histogram

3 WHERE sql_id='a9cf9a1ky3bda'

4 ORDER BY sql_id, child_number

HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT

--

1709288874 a9cf9a1ky3bda 0 1 1

1709288874 a9cf9a1ky3bda 0 0 1

1709288874 a9cf9a1ky3bda 0 2 0

1709288874 a9cf9a1ky3bda 1 1 1

1709288874 a9cf9a1ky3bda 1 0 0

1709288874 a9cf9a1ky3bda 1 2 0

6 rows selected.

SQL >

SQL >-Statistics

SQL > SELECT hash_value, sql_id, child_number, executions

2 rows_processed

3 FROM v$sql_cs_statistics

4 WHERE sql_id='a9cf9a1ky3bda'

5 ORDER BY sql_id, child_number

HASH_VALUE SQL_ID CHILD_NUMBER EXECUTIONS ROWS_PROCESSED

--

1709288874 a9cf9a1ky3bda 01 201

1709288874 a9cf9a1ky3bda 1 1 49901

SQL >

SQL >-selection rate

SQL > SELECT hash_value, sql_id, child_number, predicate, range_id, low, high

2 FROM v$sql_cs_selectivity

3 WHERE sql_id='a9cf9a1ky3bda'

4 ORDER BY sql_id, child_number

HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH

1709288874 a9cf9a1ky3bda 1 = A 0 0.898361 1.097996

The optimizer finally realized that it had made a mistake, reparsed it hard and produced a new execution plan, using full table scan. V$SQL has generated a new cursor, the value of IS_BIND_AWARE has become Y, and the view v$SQL_CS_HISTOGRAM has an extra 3 lines, in order to record the histogram information of the number of rows processed by the new cursor (child_numer is 1). Thanks to hard parsing, the view V$SQL_CS_STATISTICS also adds a new row to record the row processing information and buffer gets information for the new cursor. $SQL_CS_SELECTIVITY also has a record of the range of selections of new cursor predicates. So far, this SQL is already owned by bind aware. Bind aware means that every time you parse the SQL, you have to snoop on the binding variable value of the SQL to calculate the selection rate, and then compare the calculated results with the relevant records in the v$sql_cs_selectivity view. If the calculated selection rate falls within the range of the selection rate of the corresponding cursor in this view, then soft parsing and reusing the cursor, if not, hard parsing to generate a new sub-cursor.

SQL > var a varchar2

SQL > exec: a: = 'Inactive'

PL/SQL procedure successfully completed.

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

COUNT (NAME)

-

one hundred

SQL >-- check ACS status

SQL > SELECT child_number, executions, buffer_gets, is_bind_sensitive

2 is_bind_aware

3 FROM v$sql

4 WHERE sql_id='a9cf9a1ky3bda'

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS

-

0 2 482 Y N

1 1 210 Y Y

2 1 102 Y Y

SQL >-- histogram

SQL > SELECT hash_value, sql_id, child_number, bucket_id, COUNT

2 FROM v$sql_cs_histogram

3 WHERE sql_id='a9cf9a1ky3bda'

4 ORDER BY sql_id, child_number

HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT

--

1709288874 a9cf9a1ky3bda 0 1 1

1709288874 a9cf9a1ky3bda 0 0 1

1709288874 a9cf9a1ky3bda 0 2 0

1709288874 a9cf9a1ky3bda 1 0 0

1709288874 a9cf9a1ky3bda 1 1 1

1709288874 a9cf9a1ky3bda 1 2 0

1709288874 a9cf9a1ky3bda 2 1 0

1709288874 a9cf9a1ky3bda 2 0 1

1709288874 a9cf9a1ky3bda 2 2 0

9 rows selected.

SQL >-Statistics

SQL > SELECT hash_value, sql_id, child_number, executions

2 rows_processed

3 FROM v$sql_cs_statistics

4 WHERE sql_id='a9cf9a1ky3bda'

5 ORDER BY sql_id, child_number

HASH_VALUE SQL_ID CHILD_NUMBER EXECUTIONS ROWS_PROCESSED

--

1709288874 a9cf9a1ky3bda 01 201

1709288874 a9cf9a1ky3bda 1 1 49901

1709288874 a9cf9a1ky3bda 2 1 201

SQL >-selection rate

SQL > SELECT hash_value, sql_id, child_number, predicate, range_id, low, high

2 FROM v$sql_cs_selectivity

3 WHERE sql_id='a9cf9a1ky3bda'

4 ORDER BY sql_id, child_number

HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH

1709288874 a9cf9a1ky3bda 1 = A 0 0.898361 1.097996

1709288874 a9cf9a1ky3bda 2 = A 0 0.001557 0.001903

Since the SQL is already bind aware, when the status='Inactive' is executed again, the optimizer peeps into the incoming value and refers to statistical information such as histogram information, and calculates the selection rate that does not match the selection rate of the existing cursor, so the hard parsing produces a new cursor whose is_bind_aware field is'y', resulting in an index range scan execution plan, which is great! There are also corresponding changes in the view of histogramgramgramgramgramgramgramgramgramgramgramgramgramgramgramgramgramgramgramgramgramgramgramgramgramgramgramgramgramgramgramgramgramgramgramgramgramgramgramgramgramgramgramgramgramgramgramgramgramgramgramgramgramgramgramgrammativsqlfolcsstatisticsgrammativssqlfolcs.statisticsreachesreachmentsqlfolcsselectivityalso have corresponding changes.

From the above experiment, we can see that there is an unstable period in ACS, and only after the performance of a cursor gets worse can the optimizer realize that it has made a mistake and try to correct it the next time it is executed. The trigger for this behavior depends on the v$sql_cs_histogram view, where the count in two of the three buckets of the cursor has a non-zero value. Once triggered, in each parsing phase, the selection rate is calculated by snooping into the value of the bound variable. If the calculated selection rate does not match the selection rate of the existing cursor, a new cursor is generated based on the hard parsing of the value of the snooped bound variable. And this SQL will be marked as bind aware.

This is the end of the content of "what are the knowledge points of SQL ACS". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.