In-depth understanding of Oracle optimizer (3): Dynamic sampling

Statistics of dynamic sampling are not recorded in the view, and sometimes the tables that have been analyzed are also evaluated.
It is effective for CBO because RBO does not need statistics, so dynamic sampling is meaningless.

(I) Role of dynamic sampling

① At the beginning of 10 Gb, RBO completely exited the stage of history, and CBO relied on sufficient statistical analysis information.
However, not every user will analyze each table very seriously and timely.
To ensure that the execution plan is as accurate as possible, Oracle needs to use dynamic sampling technology to help CBO obtain as much information as possible.

② Global temporary table. Generally, data in a temporary table is not analyzed because the data stored in the temporary table is temporary and may be released soon.
However, when a query is associated with such a temporary table, the CBO can only rely on dynamic sampling to obtain the statistical information of the temporary table.

③ In addition to providing analysis data for CBO when the segment object is not analyzed, dynamic sampling also has a unique capability.
It can calculate the correlation between different columns.

(Ii) Disadvantages of dynamic sampling

● The sampled data blocks are limited. For tables with massive data volumes, deviations are inevitable.
● Sampling will consume system resources, especially OLTP. Dynamic sampling is not recommended in particular.

(Iii) level of dynamic sampling

① Level 0

No dynamic sampling

② Level 1

CBO dynamically samples tables that are not analyzed, but must meet the following four conditions:

● At least one SQL table is not analyzed.
● Tables that are not analyzed appear in association queries or subqueries.
● The table not analyzed does not have an index.
● The data blocks occupied by unanalyzed tables must be larger than the default data blocks for dynamic sampling (32)

③ Level 2

Analyze all the non-analysis tables. The dynamic sampling data blocks are twice the default data blocks.

④ Level 3

The sampled table contains all the tables defined in Level 2, and the predicates that may need dynamic sampling.
The data blocks for dynamic sampling of these tables are default data blocks. For tables without analysis, the data blocks for dynamic sampling are twice the default data blocks.

⑤ Level 4

The sampled table contains all tables defined in level 3 and some tables. The WHERE clause references tables with two or more fields.
The data blocks for dynamic sampling of these tables are default data blocks. For tables without analysis, the data blocks for dynamic sampling are twice the default data blocks.

6 level 5, 6, 7, 8, 9

The sampled table contains all the tables defined in level 4, and uses 2, 4, 8, 32, and 128 times of the default number of data blocks for dynamic sampling respectively.

7. Level 10

The sampled table contains all the tables defined in level 9, and all the data in the table is dynamically sampled.

The more data blocks are sampled, the closer the analytical data is to the truth, but the larger the resource consumption

hr@ORCL> select count(*) from t;  COUNT(*)----------     50385hr@ORCL> exec dbms_stats.delete_table_stats(user,'T');PL/SQL procedure successfully completed.hr@ORCL> select last_analyzed from user_tables where table_name='T';LAST_ANALYZED------------------hr@ORCL> set autot trace exphr@ORCL> select /*+ dynamic_sampling(t 1) */ * from t;Execution Plan----------------------------------------------------------Plan hash value: 1601196873--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      | 36932 |  6383K|   160   (2)| 00:00:02 ||   1 |  TABLE ACCESS FULL| T    | 36932 |  6383K|   160   (2)| 00:00:02 |--------------------------------------------------------------------------Note-----   - dynamic sampling used for this statementhr@ORCL> select /*+ dynamic_sampling(t 2) */ * from t;Execution Plan----------------------------------------------------------Plan hash value: 1601196873--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      | 43387 |  7499K|   160   (2)| 00:00:02 ||   1 |  TABLE ACCESS FULL| T    | 43387 |  7499K|   160   (2)| 00:00:02 |--------------------------------------------------------------------------Note-----   - dynamic sampling used for this statementhr@ORCL> select /*+ dynamic_sampling(t 5) */ * from t;Execution Plan----------------------------------------------------------Plan hash value: 1601196873--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      | 52196 |  9022K|   161   (3)| 00:00:02 ||   1 |  TABLE ACCESS FULL| T    | 52196 |  9022K|   161   (3)| 00:00:02 |--------------------------------------------------------------------------Note-----   - dynamic sampling used for this statement

(Iv) When to use dynamic sampling

When dynamic sampling occurs in hard resolution, if hard resolution rarely occurs, dynamic sampling is of little significance.
Set the dynamic sampling level to 3 or 4 in an OLAP or data warehouse environment.
On the contrary, dynamic sampling should not be used in the OLTP system.
However, if OLTP uses a temporary table, dynamic sampling can also be used.

There is a case:

When XX first entered the company, he wrote the first stored procedure. As a result, 8 GB temporary tablespace was killed in the production process. After 1 hour of operation, no feedback was given.
After seeing the development DBA at that time, some minor adjustments were made, and the program ran out immediately.
XX used a temporary table and inserted a large amount of intermediate data into it. Since the temporary table does not have statistical information, the SQL statement is also complex.
Finally, merge carestion is applied to the temporary table because the temporary table data is small, and the actual situation is the opposite.
The DBA was modified in this way. First, the query is placed in a subquery, And the no_merge prompt is used for the subquery, and then the temporary table is associated.
At the same time, the statistical information of Level 4 of the temporary table is collected, and dynamic sampling is used, which immediately becomes hash join, and the problem is solved.