Search notes:

Oracle: Dynamic Optimizer Statistics

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.

Controlling behavior of dynamic statistics (Degree of sampling)

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:

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)
We now gather table statistics … 
begin
   dbms_stats.gather_table_stats(user, 'tq84_dyn_smp_tst');
end;
/
… 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)
Gathering statistics for the table; 
begin
   dbms_stats.gather_table_stats(user, 'tq84_t');
end;
/
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: 
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |    10 |    80 |    69   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TQ84_T |    10 |    80 |    69   (2)| 00:00:01 |
----------------------------------------------------------------------------
When executing the query with the gather_plan_statistics hint… 
set serveroutput off
select /*+ gather_plan_statistics */
   *
from tq84_t where id = 111;
… we can use dbms_xplan.display_cursor to show the actual values of that the query execution processed: 
select * from table(dbms_xplan.display_cursor(format => 'allstats last'));
--
-- --------------------------------------------------------------------------------------
-- | Id  | Operation         | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-- --------------------------------------------------------------------------------------
-- |   0 | SELECT STATEMENT  |        |      1 |        |     11 |00:00:00.01 |     202 |
-- |*  1 |  TABLE ACCESS FULL| TQ84_T |      1 |     10 |     11 |00:00:00.01 |     202 |
-- --------------------------------------------------------------------------------------
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.

See also

The init parameter _optimizer_dyn_smp_blks

Index