Dynamic_sampling
In this case the improved cardinality estimate has not affected our SQL execution plan, but for more complicated queries a better cardinality estimate will often result in a better SQL execution plan, which will in turn result in a faster query execution time.
You may now be wondering why we had to set the parameter optimizer_dynamic_sampling to 4 . The dynamic statistics feature is controlled by the parameter optimizer_dynamic_sampling, which can be set to different levels (0-11). These levels control two different things; when dynamic sampling kicks in and how large a sample size will be used to gather the statistics. The greater the sample size the bigger impact DS has on the compilation time of a query.
| Level | When Dynamic Statistics will be used | Sample size (blocks) |
|---|---|---|
| 0 | Switches off dynamic statistics | N/A |
| 1 | At least one non-partitioned table in the statement has no statistics | 32 |
| 2 (default) | One or more tables in the statement have no statistics This setting is suitable for the majority of systems | 64 |
| 3 | Any statement that meets level 2 criteria and any statement that has one or more expressions used in the where clause predicates e.g. Where substr(CUSTLASTNAME,1,3) or Where a + b =5 | 64 |
| 4 | Any statement that meets level 3 criteria and any statement that has complex predicates. An OR or AND operator between multiple predicates on the same table | 64 |
| 5 | Any statement that meets level 4 criteria | 128 |
| 6 | Any statement that meets level 4 criteria | 256 |
| 7 | Any statement that meets level 4 criteria | 512 |
| 8 | Any statement that meets level 4 criteria | 1024 |
| 9 | Any statement that meets level 4 criteria | 4086 |
| 10 | All statements | All Blocks |
| 11 | The database determines automatically if dynamic statistics are required | Automatically determined |
There more information on dynamic sampling levels, refer to the Chapter 12 of the SQL Tuning Guide for Oracle Database 12c Release 2.
In our example we had an AND operator between two simple predicates on the CUSTOMERS table. From the above table you can now see why I chose to set optimizer_dynamic_sampling to level 4 in order to have dynamic sampling kick in for our example.
From Oracle Database 11g Release 2 onwards, the optimizer automatically decides if dynamic sampling will be useful and what dynamic sampling level will be used for SQL statements executed in parallel（并行更加准确）. This decision is based on size of the tables in the statement and the complexity of the predicates. You can tell if dynamic sampling kicks in by looks in the note section of the execution plan.
SELECT /*+ Dynamic_sampling(11) */ myTable.* FROM myTable
6-7 变化，7是精确地 73440
原文地址：https://blog.csdn.net/jnrjian/article/details/142730240
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！