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

This article focuses on "histogram and ACS case analysis", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "histogram and ACS case Analysis".

In general, ACS must be used in conjunction with a histogram to work. Let's see how ACS performs when the data on the column is skewed, but the histogram is not collected, followed by a special case. The following code removes the histogram from the column status.

SQL > begin

2 dbms_stats.delete_column_stats (ownname = > 'test'

3 tabname = > 'test'

4 colname = > 'status'

5 col_stat_type = > 'HISTOGRAM')

6 end

7 /

PL/SQL procedure successfully completed.

SQL > alter system flush shared_pool

System altered.

Delete histogram is a function provided by 11G, if your version is less than 11G, you can re-collect the statistics of the table without collecting histogram.

SQL > exec: a: = 'Active'

PL/SQL procedure successfully completed.

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

COUNT (NAME)

-

49900

SQL > exec: a virtual inactive.'

PL/SQL procedure successfully completed.

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

COUNT (NAME)

-

one hundred

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

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

PLAN_TABLE_OUTPUT

SQL_ID a9cf9a1ky3bda, child number 0

-

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 | 25000 | 610K | 51 (2) | 00:00:01 |

Predicate Information (identified by operation id):

2-filter ("STATUS" =: a)

We see that there are already 2 barrels of count non-zero in the three barrels of the cursor in v$sql_cs_histogram, indicating that the optimizer has realized that the number of records returned by the second execution is very different from that of the first time. According to our previous tests, if there is a histogram on the column, execute the SQL again, and a new cursor should be generated. Let's see what happens without a histogram:

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 463 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 0

1709288874 a9cf9a1ky3bda 1 0 1

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 101

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.450000 0.550000

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

PLAN_TABLE_OUTPUT

SQL_ID a9cf9a1ky3bda, child number 0

-

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 | 25000 | 610K | 51 (2) | 00:00:01 |

Predicate Information (identified by operation id):

2-filter ("STATUS" =: a)

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 | 25000 | 610K | 51 (2) | 00:00:01 |

Predicate Information (identified by operation id):

2-filter ("STATUS" =: a)

We see that although a new child cursor is generated and the bind aware of this cursor is Y, we look at the execution plan and find that the newly generated execution plan with a child_number of 1 is also a full table scan instead of an index scan. In fact, after the optimizer finds that the number of rows processed by this cursor has changed greatly, the next time it executes it, it will snoop on the value of the variable and then perform hard parsing according to the snooped value, but because there is no histogram, the optimizer thinks that the cost of index scanning is too high, so the execution plan of the full table scan is still generated after hard parsing. Let's see what the COST value of the index scan is:

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

COUNT (NAME)

-

one hundred

SQL > select * from table (dbms_xplan.display_cursor)

PLAN_TABLE_OUTPUT

SQL_ID 88jwg2t11b237, child number 0

-

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

Plan hash value: 2948918962

-

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

-

| | 0 | SELECT STATEMENT | 218,100 | | |

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

| | 2 | TABLE ACCESS BY INDEX ROWID | TEST | 25000 | 610K | 218 (1) | 00:00:03 |

| | * 3 | INDEX RANGE SCAN | TEST_ID_IND | 25000 | | 63 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

3-access ("STATUS" =: a)

The cost of index scan is 218, which exceeds the cost 51 of full table scan, so the lack of histogram can only produce the execution plan of full table scan even if the histogram is reparsed hard. The optimizer is trying to correct the error, but the impatient information is not enough to correct the error effectively.

However, there is a special case. If you do a non-equivalent query on a column, you may still use ACS even if there is no histogram. Let's look at a case:

L create a table with 5 million records, and the id field is generated according to rownum

L create an index on the id field

L collect statistics, not histograms

L clear shared_pool

SQL > create table t as select rownum id,a.* from dba_objects a, dba_objects b where rownumcreate index i on t (id)

Index created.

SQL > begin

2 dbms_stats.gather_table_stats (ownname = > 'test'

3 tabname = >'t'

4 no_invalidate = > FALSE

5 estimate_percent = > 100

6 force = > true

7 degree = > 5

8 method_opt = > 'for all columns size 1'

9 cascade = > true)

10 end

11 /

PL/SQL procedure successfully completed.

SQL > alter system flush shared_pool

System altered.

SQL > var a number

SQL > exec: a: = 4999999

PL/SQL procedure successfully completed.

SQL > select count (object_id) from t where id >: a

COUNT (OBJECT_ID)

-

one

SQL > select * from table (dbms_xplan.display_cursor)

PLAN_TABLE_OUTPUT

SQL_ID 1vmttxn3jrww3, child number 0

-

Select count (object_id) from t where id >: a

Plan hash value: 3694077449

-

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

-

| | 0 | SELECT STATEMENT | 4 (100) | |

| | 1 | SORT AGGREGATE | | 1 | 10 |

| | 2 | TABLE ACCESS BY INDEX ROWID | T | 1 | 10 | 4 (0) | 00:00:01 |

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

-

Predicate Information (identified by operation id):

3-access ("ID" >: a)

First query the id greater than 4999999, because only one record is returned, the recordset is very small, ORACLE chose index scan. Let's take a look at the performance of ACS-related views:

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='1vmttxn3jrww3'

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS

-

0 1 48 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='1vmttxn3jrww3'

4 ORDER BY sql_id, child_number

HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT

--

119272323 1vmttxn3jrww3 0 0 1

119272323 1vmttxn3jrww3 0 2 0

119272323 1vmttxn3jrww3 0 1 0

Because the result set of the processing is small, the statistics performed are included in bucket 0. Let's move on to the case where the query id > 1 returns almost the data of the entire table:

SQL > exec: a: = 1

Select count (object_id) from t where id >: a

PL/SQL procedure successfully completed.

SQL >

COUNT (OBJECT_ID)

-

4999999

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='1vmttxn3jrww3'

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS

-

0 2 76425 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='1vmttxn3jrww3'

4 ORDER BY sql_id, child_number

HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT

--

119272323 1vmttxn3jrww3 0 0 1

119272323 1vmttxn3jrww3 0 2 1

119272323 1vmttxn3jrww3 0 1 0

V$sql_cs_histogram has captured the result set of the SQL processing for this execution is very different from the first execution, and the execution statistics have been included in the bucket with a bucket_id of 2. Execute again:

SQL > select count (object_id) from t where id >: a

COUNT (OBJECT_ID)

-

4999999

SQL > select * from table (dbms_xplan.display_cursor)

PLAN_TABLE_OUTPUT

SQL_ID 1vmttxn3jrww3, child number 1

-

Select count (object_id) from t where id >: a

Plan hash value: 2966233522

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

| | 0 | SELECT STATEMENT | 14373 | | |

| | 1 | SORT AGGREGATE | | 1 | 10 |

| | * 2 | TABLE ACCESS FULL | T | 4999K | 47m | 14373 (2) | 00:02:53 |

Predicate Information (identified by operation id):

2-filter ("ID" >: a)

After the execution again, the execution plan of the full table scan has been generated, because the optimizer will snoop on the values of the bound variables for hard parsing. After re-evaluating the cost of the index scan and the full table scan, the optimizer chooses the full table scan. The following code shows the changes of the ACS-related views and the cost of the index scan.

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='1vmttxn3jrww3'

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS

-

0 2 76425 Y N

1 1 64685 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='1vmttxn3jrww3'

4 ORDER BY sql_id, child_number

HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT

--

119272323 1vmttxn3jrww3 0 1 0

119272323 1vmttxn3jrww3 0 0 1

119272323 1vmttxn3jrww3 0 2 1

119272323 1vmttxn3jrww3 11 0

119272323 1vmttxn3jrww3 1 0 0

119272323 1vmttxn3jrww3 1 2 1

SQL > select / * + index (t) * / count (object_id) from t where id >: a

Select

COUNT (OBJECT_ID)

-

4999999

SQL > select * from table (dbms_xplan.display_cursor)

PLAN_TABLE_OUTPUT

-

SQL_ID 51qy01unwm5r0, child number 0

-

Select / * + index (t) * / count (object_id) from t where id >: a

Plan hash value: 3694077449

-

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

-

| | 0 | SELECT STATEMENT | 76652 | | |

| | 1 | SORT AGGREGATE | | 1 | 10 |

| | 2 | TABLE ACCESS BY INDEX ROWID | T | 4999K | 47m | 76652 (1) | 00:15:20 |

| | * 3 | INDEX RANGE SCAN | I | 4999K | | 11792 (1) | 00:02:22 |

-

Predicate Information (identified by operation id):

3-access ("ID" >: a)

Subcursors with child_number 1 have also appeared in v$sql. Both is_bind_sensitive and is_bind_aware are Y. Three new lines of records are also generated in v$sql_cs_histogram. It shows that ACS has played a role in producing new cursors, and the execution plan is excellent. There is no example of the first histogram to do an equivalent query, and although ACS also played a role, it did not produce a good execution plan due to the lack of a histogram.

As can be seen from the above two examples, the so-called ACS only gives the optimizer a chance to reparse hard according to the value of specific bound variables, but whether the hard parsed execution plan is excellent or not depends on the completeness and accuracy of the statistical information and what kind of query your query predicate is doing.

At this point, I believe you have a deeper understanding of "histogram and ACS case analysis". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.