Dynamic statistics used: dynamic sampling (level=7)
In Execution plan, if these line specified in the execution plan of note section, Its means dynamic sampling is enabled. As we set value 2 for dynamic sampling.
Optimizer choose LEVEL according to the Data sample size. During fetching execution plan we have see following lines:
Note
----
- dynamic statistics used: dynamic sampling (level=7) or
- dynamic statistics used for this statement (level=4)
Note: The level value is depend upon the data size if we are using parallel query with dynamic sampling 2 value.
Check dynamic sampling detail
SELECT
dspd.type,
dspd.reason,
dspdo.owner,
dspdo.object_name,
dspdo.subobject_name,
dspdo.object_type
FROM
dba_sql_plan_directives dspd,
dba_sql_plan_dir_objects dspdo
WHERE
dspd.directive_id = dspdo.directive_id and dspd.type like '%DYNAMIC%;
You can find following queries in V$SQL view when dynamic sampling is used.
Where your stats aren't good enough or are missing:
SELECT /* OPT_DYN_SAMP */ … SAMPLE BLOCK (0.51390, 8) SEED(1) "TABLE_X"
Where Dynamic Sampling/SQL Plan Directive has kicked in:
SELECT /* DS_SVC */ … SAMPLE BLOCK (0.51398, 8) SEED(1) "TABLE_X"
Sometime it help in OLTP environment and sometime not then choose according to your need.
you can disable dynamic sampling completed by set its value 0, by default its value is 2.
ALTER SYSTEM SET optimizer_dynamic_sampling = 0 scope=both;
Details chart of Dynamic sampling value
Level | Status/Used | |
0 | off | |
1 | No stats on an unpartitioned table, and no indexes, and table is bigger than 32 blocks (samples 32 blocks) | |
2 | (default) no stats on 1 table in the join or (and this is badly documented) if you use PARALLEL (samples 64 blocks) | |
3 | (as 2) + if you have a complex predicate expression e.g WHERE trim(column) | |
4 | (as 3) + an OR or AND between multiple predicates on the same table | |
5-10 | (as 4) but sample 128/256/512/1024/4096/ALL table blocks | |
11 | Automatically Determined by Oracle |