Dynamic statistics used: dynamic sampling (level=7)

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.