When an SQL statement is hard parsed and the relevant statistics are stale, missing or insufficient, Oracle scans a small portion («sample») of the underlying data to get calculate dynamic statistics that can be used to generate an SQL execution plan.
Dynamic statistics are especially useful to calculate better cardinality estimates for joins and group by clauses.
Dynamic statistics was previously referred to as dynamic sampling.
Controlling behavior of dynamic statistics (Degree of sampling)
With enabled dynamic statistics, Oracle will generate sampling SQL statements with which the real data is probed for better estimates.
The behavior of gathering dynamic statistics can be controlled with a number that is specified with
These names contain «dynamic sampling» because in earlier releases (11g?), dynamic optimizer statistics was named dynamic sampling.
This number is an integer between 0 and 11 with the following meaning:
Level
Description
0
Don't gather dynamic sample statistics
1
Sample all tables that have not been analyzed if some conditions are true
2
Apply dynamic sampling to all unanalyzed tables. The number of blocks sampled is two times the default number of dynamic sampling blocks.
3
Apply dynamic sampling to all tables that meet Level 2 criteria, plus all tables for which standard selectivity estimation used a guess for some predicate that is a potential dynamic sampling predicate. The number of blocks sampled is the default number of dynamic sampling blocks. For unanalyzed tables, the number of blocks sampled is two times the default number of dynamic sampling blocks.
4
Apply dynamic sampling to all tables that meet Level 3 criteria, plus all tables that have single-table predicates that reference 2 or more columns. The number of blocks sampled is the default number of dynamic sampling blocks. For unanalyzed tables, the number of blocks sampled is two times the default number of dynamic sampling blocks.
5 - 9
Apply dynamic sampling to all tables that meet the previous level criteria using 2, 4, 8, 32, or 128 times the default number of dynamic sampling blocks respectively.
10
Apply dynamic sampling to all tables that meet the level 9 criteria using all blocks in the table.
11
Oracle uses other criteria (which exactly?) to determine if dynamic statistics will be collected
The relevant conditions for level 1 are:
At least one table in the query is unanalyzed
The unanalyzed table is joined to another table or appears in a subquery or non-mergeable view
The unanalyzed table has no indexes
The unanalyzed table has more blocks than the number of blocks that would be used for dynamic sampling of this table. The number of blocks sampled is the default number of dynamic sampling blocks (32).
Simple example
First, we verify that the init parameter init parameters is set to the (default) value of 2:
select
value
from
v$parameter
where
name = 'optimizer_dynamic_sampling';
We also create a table and fill it with some generated test data. The value of id is unique (but not enforced with a constraint:
create table tq84_dyn_smp_tst (
id integer, -- primary key,
val number(7,2)
);
insert into tq84_dyn_smp_tst
select
level,
dbms_random.value(1000, 100000)
from
dual connect by level <= 10000;
commit;
Because there are no table statistics for the table and the optimizer_dynamic_sampling is set to two, Oracle will use dynamic statistics in the following SQL statement:
explain plan for
select
*
from
tq84_dyn_smp_tst
where
id = 1;
select * from dbms_xplan.display(format=>'basic rows note');
--
-- ------------------------------------------------------
-- | Id | Operation | Name | Rows |
-- ------------------------------------------------------
-- | 0 | SELECT STATEMENT | | 1 |
-- | 1 | TABLE ACCESS FULL| TQ84_DYN_SMP_TST | 1 |
-- ------------------------------------------------------
--
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
… and inspect the new execution plan. This time, because there are statistics, Oracle will not dynamically sample the table again:
explain plan for
select
*
from
tq84_dyn_smp_tst
where
id = 1;
select * from dbms_xplan.display(format=>'basic rows note');
--
-- ------------------------------------------------------
-- | Id | Operation | Name | Rows |
-- ------------------------------------------------------
-- | 0 | SELECT STATEMENT | | 1 |
-- | 1 | TABLE ACCESS FULL| TQ84_DYN_SMP_TST | 1 |
-- ------------------------------------------------------
We change the value of id to 1 in each record (thus the where id = 1 condition will essentially become meaningless):
update tq84_dyn_smp_tst set id=1;
Again, the execution plan. Note that the plan, notably, the number of estimated rows, did not change: Oracle still thinks the query will return 1 row (which is of course wrong).
explain plan for
select
*
from
tq84_dyn_smp_tst
where
id = 1;
select * from dbms_xplan.display(format=>'basic rows note');
--
-- ------------------------------------------------------
-- | Id | Operation | Name | Rows |
-- ------------------------------------------------------
-- | 0 | SELECT STATEMENT | | 1 |
-- | 1 | TABLE ACCESS FULL| TQ84_DYN_SMP_TST | 1 |
-- ------------------------------------------------------
In order to force Oracle to use dynamic samples, we use the dynamic_sampling hint.
Because dynamic sampling will be used, the number of returned rows is now correctly estimated to 10000:
explain plan for
select /*+ dynamic_sampling(tq84_dyn_smp_tst 1) */
*
from
tq84_dyn_smp_tst
where
id = 1;
select * from dbms_xplan.display(format=>'basic rows note');
--
-- ------------------------------------------------------
-- | Id | Operation | Name | Rows |
-- ------------------------------------------------------
-- | 0 | SELECT STATEMENT | | 10000 |
-- | 1 | TABLE ACCESS FULL| TQ84_DYN_SMP_TST | 10000 |
-- ------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Cleaning up
drop table tq84_dyn_smp_tst;
/* OPT_DYN_SAMP */ and /* DS_SVC */ comments
When an SQL statement is executed with dynamic optimizer statistics, the statement is stored in the shared pool with one of the two comments (not hints) /* OPT_DYN_SAMP */ or /* DS_SVC */.
These comments are used by Oracle's optimizer team to make it easier for them to identify such statements in the shared pool.
/* OPT_DYN_SAMP */ comments indicate that regular statistics were not sufficient to estimate a cardinality, or such statistics were even missing.
/* DS_SVC */ comments were used in 11gR2 and 12c.
In Oracle 19c, there seems to also be comments like /* SQL Analyze(181,0) */.
TODO
The following tests were conducted with the default value of optimizer_dynamic_sampling which is 2:
select * from v$parameter where name in ('optimizer_dynamic_sampling');
A table is needed for the test:
create table tq84_t(id number, val varchar2(10));
The table needs some data::
insert into tq84_t
select
mod(level, 3) *
mod(level, 5) *
mod(level, 7) *
mod(level, 11) +
mod(level, 10101),
'xxx'
from
dual connect by level <= 100000;
The insert statement didn't generate any sort of auto statistics, num_rows and blocks is reported as null:
select
num_rows,
blocks
from
user_tables
where
table_name = 'TQ84_T';
There are 11 rows in the table whose id is 111:
select count(*) from tq84_t where id = 111;
Because of the missing statistics, the optimizer uses dynamic statistics (referred to as «dynamic sampling» in the note of the following output) when a simple select statement is explained. With dynamic sampling, the optimizer estimates the number of records with id = 111 to be 4:
explain plan for
select * from tq84_t where id = 111;
select * from table(dbms_xplan.display);
--
-- ----------------------------------------------------------------------------
-- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-- ----------------------------------------------------------------------------
-- | 0 | SELECT STATEMENT | | 4 | 80 | 68 (0)| 00:00:01 |
-- |* 1 | TABLE ACCESS FULL| TQ84_T | 4 | 80 | 68 (0)| 00:00:01 |
-- ----------------------------------------------------------------------------
--
- …
--
-- Note
-- -----
-- - dynamic statistics used: dynamic sampling (level=2)
This time, explaining the plan for the same select statement shows that with gathered statistics, the estimation of returned rows is 10, much better than before, but not correct. Also: the note of the previous plan about dynamic statistics used is gone:
E-Rows shows the estimated rows (= 10, as estimated in the execution plan), while A-Rows (= actual rows) shows the value of the actual rows.
Note, I used set serveroutput off in order to prevent SQL Developer from executing the statement which reads the dbms_output buffer which would have interfered with the last dbms_output.display_cursor statement.