Oracle dynamic sampling

Overview of dynamic sampling

Dynamic Sampling was initially proposed in Oracle 9i R2, where segments (tables, indexes, partitions) are not analyzed, A technique invented to enable the CBO optimizer to obtain sufficient information to ensure a correct execution plan can be seen as a supplement to the analysis.
When the segment object does not have statistical information (that is, no analysis is performed), the dynamic sampling technology can directly collect data blocks (sampling) from the object to be analyzed to obtain the statistical information required by CBO.

A simple example:
Create a table:
SQL> create table t as select owner, object_type from dba_objects;
Table created.
View the number of records in the table:
SQL> select count (*) from t
COUNT (*)
----------
50419 -- number of records
A common table is created here without analysis. We use level 0 in hint to restrict dynamic sampling. At this time, the only information that CBO can use is some information stored in the data dictionary, if there are multiple extent and block, this information is not enough.
SQL> set autotrace trace exp
SQL> select/* + dynamic_sampling (t 0) */* from t;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
--------------------------------------------------------------------------
| 0 | select statement | 12007 | 328K | 34 (0) | 00:00:01 |
| 1 | table access full | T | 12007 | 328K | 34 (0) | 00:00:01 |
--------------------------------------------------------------------------
Without dynamic analysis, the number of CBO records is estimated to be 12007, which is far from the actual 50419.
After dynamic analysis:
SQL> select * from t;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
--------------------------------------------------------------------------
| 0 | select statement | 45596 | 1246K | 35 (3) | 00:00:01 |
| 1 | table access full | T | 45596 | 1246K | 35 (3) | 00:00:01 |
--------------------------------------------------------------------------
Note
-----
-Dynamic sampling used for this statement
By default, 10 Gb of Oracle performs dynamic sampling for segments without analysis. The above query results show that dynamic sampling is used. The CBO results are 45596 and 50419, which are very similar. Dynamic sampling only analyzes a limited number of data blocks to estimate the entire table. Therefore, it is normal that dynamic sampling cannot completely match the actual value.
Note: In the absence of dynamic sampling, for segments that have not been analyzed, CBO may mistakenly increase the degree of result judgment.
See the following
SQL> delete from t;
50419 rows deleted.
SQL> set autotrace trace exp
SQL> select/* + dynamic_sampling (t 0) */* from t;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
--------------------------------------------------------------------------
| 0 | select statement | 12007 | 328K | 34 (0) | 00:00:01 |
| 1 | table access full | T | 12007 | 328K | 34 (0) | 00:00:01 |
--------------------------------------------------------------------------
SQL> select * from t;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
--------------------------------------------------------------------------
| 0 | select statement | 1 | 28 | 34 (0) | 00:00:01 |
| 1 | table access full | T | 1 | 28 | 34 (0) | 00:00:01 |
--------------------------------------------------------------------------
Note
-----
-Dynamic sampling used for this statement
We can see that, without dynamic analysis, the CBO still estimates 12007 rows of records for the t table, but the dynamic analysis shows one record. The data in the table has been deleted before the query. The reason for this is the high water level. Because the table information when dynamic sampling is not used comes from the extent and block information in the data dictionary mentioned above, although the table data has been deleted, the extent and block allocated to the table has not been recycled, in this case, CBO still thinks that there are so many data.
From this point, we can see that the information that CBO can use is very limited, that is, this table has several extent and several blocks. However, after dynamic sampling, Oracle immediately found that the original data blocks were empty.
If you set SQL _trace = true to view the execution plan, dynamic sampling will reflect the following information:
**************************************** ****************************************
SELECT/* OPT_DYN_SAMP * // * + ALL_ROWS IGNORE_WHERE_CLAUSE
NO_PARALLEL (SAMPLESUB) opt_param ('parallel _ execution_enabled ', 'false ')
NO_PARALLEL_INDEX (SAMPLESUB) NO_ SQL _TUNE */NVL (SUM (C1),: "SYS_ B _0 "),
NVL (SUM (C2),: "SYS_ B _1 ")
FROM
(SELECT/* + IGNORE_WHERE_CLAUSE NO_PARALLEL ("T1") FULL ("T1 ")
NO_PARALLEL_INDEX ("T1") */: "SYS_ B _2" AS C1, case when "T1". "ID" =: "SYS_ B _3"
THEN: "SYS_ B _4" ELSE: "SYS_ B _5" END AS C2 FROM "T1" SAMPLE BLOCK
(: "SYS_ B _6",: "SYS_ B _7") SEED (: "SYS_ B _8") "T1") SAMPLESUB
Call count cpu elapsed disk query current rows
-----------------------------------------------------------------------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.03 0.09 171 70 0 1
-----------------------------------------------------------------------
Total 3 0.03 0.10 171 70 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55 (recursive depth: 1)
Rows Row Source Operation
----------------------------------------------------------
1 sort aggregate (cr = 70 pr = 171 pw = 0 time = 97308 us)
14049 table access sample T1 (cr = 70 pr = 171 pw = 0 time = 720915 us)
**************************************** ****************************************
SELECT/* OPT_DYN_SAMP * // * + ALL_ROWS opt_param ('parallel _ execution_enabled ',
'False') NO_PARALLEL (SAMPLESUB) NO_PARALLEL_INDEX (SAMPLESUB) NO_ SQL _TUNE
*/NVL (SUM (C1),: "SYS_ B _0"), NVL (SUM (C2),: "SYS_ B _1"), NVL (SUM (C3),: "SYS_ B _1 ")
FROM
(SELECT/* + NO_PARALLEL ("T1") INDEX ("T1" T1_INX) NO_PARALLEL_INDEX ("T1 ")*/
: "SYS_ B _3" AS C1,: "SYS_ B _4" AS C2,: "SYS_ B _5" AS C3 FROM "T1" T1 "WHERE
"T1". "ID" =: "SYS_ B _6" and rownum <=: "SYS_ B _7") SAMPLESUB
Call count cpu elapsed disk query current rows
-----------------------------------------------------------------------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 2 0 1
-----------------------------------------------------------------------
Total 3 0.00 0.00 0 2 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55 (recursive depth: 1)
Rows Row Source Operation
----------------------------------------------------------
1 sort aggregate (cr = 2 pr = 0 pw = 0 time = 660 us)
1 VIEW (cr = 2 pr = 0 pw = 0 time = 521 us)
1 count stopkey (cr = 2 pr = 0 pw = 0 time = 368 us)
1 index range scan T1_INX (cr = 2 pr = 0 pw = 0 time = 156 us) (object id 52550)

Functions of dynamic sampling
1. CBO relies on sufficient statistical analysis information, but not every user is very careful and can analyze each table in a timely manner. 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.
2. Global temporary table. Generally, the 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, to obtain statistical analysis data on a temporary table, CBO can only rely on dynamic sampling.
3. In addition to providing analysis data for CBO when the segment object is not analyzed, dynamic sampling also provides a unique capability for statistics on the correlations between different columns. This usually happens when the table design does not conform to 3NF, which is rare when the table design conforms to 3NF.

Dynamic sampling level
Level 0: No Dynamic Analysis
Level 1: Oracle dynamically samples tables that are not analyzed, but must meet the following four conditions at the same time.
1. At least one SQL table is not analyzed.
2. Tables not analyzed appear in the join query or subquery.
3. The table not analyzed has no index.
4. The data blocks occupied by tables not analyzed must be larger than the default data blocks for dynamic sampling (32)
Level 2: analyze all non-analysis tables. Dynamic sampling data blocks are twice the default data blocks, that is, 64 data blocks.
Level 3: The sampled table contains all the tables that meet the definition of Level 2, and also includes the tables whose predicates may need dynamic sampling, the default data blocks for dynamic sampling are default data blocks. For tables without analysis, the default data blocks for dynamic sampling are two times the default data blocks, that is, 64 data blocks.
Level 4: the sampled table contains tables that meet the Level 3 definition, and some tables. The predicates that contain a single table will reference the other two or more columns; the number of sampled data blocks is the default number of dynamic sampling data blocks. For tables without analysis, the number of dynamically sampled data blocks is twice that of the default data blocks.
Level 5, 6, 7, 8, and 9: The sampled table contains tables that meet the definition of Level 4. Dynamic analysis is performed by using the default values of 2, 4, 8, 32, and 128 times of the dynamic sampling data blocks.
Level 10: the sampled table contains all the tables that meet the definition of Level 9, and all the data in the table is dynamically sampled.
The more data blocks are sampled, the closer the analysis data is to be real, but the greater the resource consumption.
When to use dynamic sampling
Despite the advantages of dynamic sampling, its disadvantages are also obvious. Otherwise, Oracle will always use dynamic sampling to replace Data Analysis:
1. In OLAP or data warehouse environments, SQL Execution consumes much more resources than SQL parsing, so that parsing consumes more resources for dynamic sampling analysis, therefore, it is worthwhile to make an optimal execution plan. In fact, in such an environment, the resources consumed by hard analysis are almost negligible. However, the sampled data blocks are limited. For tables with massive data volumes, the results will inevitably be biased. Therefore, it is better to set the dynamic sampling level to 3 or 4 in OLAP or data warehouse environments.
2. Dynamic sampling requires additional database resources. Therefore, if SQL statements are executed repeatedly, variables are bound, and hard analysis is rare, dynamic sampling is not recommended in such an environment. When dynamic sampling occurs in hard analysis, if hard analysis rarely occurs, dynamic sampling is of little significance. Of course, if the BIND variable is not used, frequent hard parsing and dynamic sampling will consume too much resources, so the OLTP system is not suitable for dynamic sampling.