Dynamic Oracle 11g sampling

Recently, I encountered a performance problem caused by dynamic sampling, which is a bit difficult for me to understand. by reading the document and doing experiments, I will summarize it as follows:
Let's take a look at the Online Document's explanation of dynamic adoption.
Oracle 10GR2 documentation:
This dynamic sampling feature is controlled by the OPTIMIZER_DYNAMIC_SAMPLING parameter.
For dynamic sampling to automatically gather the necessary statistics, this parameter shocould be set to a value of 2 or higher.
The default value is 2. See "Dynamic Sampling Levels" for information about the sampling levels that can be set.

Oracle 11GR2 documentation:
When the Optimizer Uses Dynamic Sampling
During compilation, the optimizer decides whether to use dynamic sampling based on a number of factors,
Including whether the statements use parallel processing or serial processing.
For parallel statements, the optimizer automatically decides whether to use dynamic sampling and which level to use.
The demo-depends on the size of the tables and the complexity of the predicates. The optimizer expects parallel statements to be
Resource-intensive, so the additional overhead at compile time is worth it to ensure the best plan. The database ignores the OPTIMIZER_DYNAMIC_SAMPLING setting
Unless set to a nondefault value, in which case the value is honored.
For serially processed SQL statements, the dynamic sampling level depends on the value of the OPTIMIZER_DYNAMIC_SAMPLING parameter and
Is not triggered automatically by the optimizer. Serial statements are typically short-running, so that any overhead at compile time
Cocould have a huge impact on their performance.
Note: we have noticed that 11.2.0 is unavailable. * If the statement is executed in parallel, whether to use dynamic sampling and level is determined by the table size and the complexity of table join in SQL, that is, CBO determines the sampling level value.
CBO ignores the default value of OPTIMIZER_DYNAMIC_SAMPLING. For Oracle's various features, if there are automatic functions, more or less unstable, there will always be problems. Of course, the level of the serial execution statement that is still dynamically sampled will still be determined by OPTIMIZER_DYNAMIC_SAMPLING. In fact, level 2 is sufficient in most cases.

Let's take a look at the dynamic usage in 11.2.0.3.

1. SQL>Create table leo (id number,
2. 2 leo_date date,
3. 3 value varchar2 (20 ),
4. 4 name varchar2 (30) tablespace users;
6. Table created.
8. <SPAN Style="FONT-FAMILY: 'comic Sans Ms '">Generate million data with a random number and insert it into the table.</SPAN>
9. SQL>Insert into leo
10. 2 select rownum id, sysdate-dbms_random.value (1,500) leo_create,
11. 3 dbms_random.string ('1', 15) value,
12. 4 dbms_random.string ('1', 20) name
13. 5 from dual
14. 6 * connect by level<= 1e6;
15. 1000000 rows created.
16. SQL>Commit;
17. Commit complete

Note: Do not collect statistics

1. SQL>Show parameter optimizer
2. NAME TYPE VALUE
3. -------------------------------------------------------------
4. Optimizer_dynamic_sampling integer 2
5. Optimizer_features_enable string 11.2.0.3
7. SQL>Explain plan for select * from leo;
8. Explained.
10. SQL>@ Explain
11. PLAN_TABLE_OUTPUT
12. ----------------------------------------------------------------------------
13. Plan hash value: 1174476904
14. --------------------------------------------------------------------------
15. | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
16. --------------------------------------------------------------------------
17. | 0 | select statement | 897K | 43M | 2105 (2) | 00:00:26 |
18. | 1 | table access full | LEO | 897K | 43M | 2105 (2) | 00:00:26 |
19. --------------------------------------------------------------------------
20. Note
21. -----
22. -Dynamic sampling used for this statement (Level=2)
23. 12 rows selected.
24. SQL>Explain plan for select/* + parallel */* from leo;
25. Explained.
27. SQL>@ Explain
28. PLAN_TABLE_OUTPUT
29. Bytes -----------------------------------------------------------------------------------------------------------------
30. Plan hash value: 3240177498
31. Bytes -------------------------------------------------------------------------------------------------------------
32. | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | TQ | IN-OUT | PQ Distrib |
33. Bytes --------------------------------------------------------------------------------------------------------------
34. | 0 | select statement | 897K | 43M | 1166 (2) | 00:00:14 |
35. | 1 | px coordinator |
36. | 2 | px send qc (RANDOM) |: TQ10000 | 897K | 43M | 1166 (2) | 00:00:14 | Q1, 00 | P->S | QC (RAND) |
37. | 3 | px block iterator | 897K | 43M | 1166 (2) | 00:00:14 | Q1, 00 | PCWC |
38. | 4 | table access full | LEO | 897K | 43M | 1166 (2) | 00:00:14 | Q1, 00 | PCWP |
39. Bytes --------------------------------------------------------------------------------------------------------------
40. Note
41. -----
42. -Dynamic sampling used for this statement (Level=6)
43. --Automatic DOP: skipped because of IO calibrate statistics are missing
44. 16 rows selected.

1 2 Next Page