It's a similar principle to single table adjustments. If the optimizer thinks the estimates are insufficient/missing/stale then it can use generate dynamic stats.
SQL> create table t as
2 select rownum x, sysdate y from dual connect by level <= 1;
SQL>
SQL> create index i on t (x);
SQL>
SQL> insert into t
2 select rownum+1 x, sysdate y from dual connect by level <= 999;
SQL>
SQL> commit;
SQL>
SQL> select num_rows from user_ind_statistics
2 where index_name = 'I';
NUM_ROWS
----------
1
So we have a one thousand row table. But Oracle thinks there's only one row in the index. A query finding rows where x > 900 will return 100 rows. With dynamic stats disabled (dynamic_sampling = 0) it's estimates are way off:
SQL> select /*+ gather_plan_statistics dynamic_sampling (0) */count(*) from t
2 where x > 900;
COUNT(*)
----------
100
SQL>
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST +NOTE'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
SQL_ID 7saawtsj93tyf, child number 1
-------------------------------------
select /*+ gather_plan_statistics dynamic_sampling (0) */count(*) from
t where x > 900
Plan hash value: 2079104444
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 2 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 2 | INDEX RANGE SCAN| I | 1 | 16 | 100 |00:00:00.01 | 2 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("X">900)
But enable dynamic stats (dynamic_sampling = 11) and it gets the E-rows spot on:
SQL> select /*+ gather_plan_statistics dynamic_sampling (11) */count(*) from t
2 where x > 900;
COUNT(*)
----------
100
SQL>
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST +NOTE'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
SQL_ID 2gk8mp858dnsk, child number 0
-------------------------------------
select /*+ gather_plan_statistics dynamic_sampling (11) */count(*) from
t where x > 900
Plan hash value: 2079104444
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 2 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 2 | INDEX RANGE SCAN| I | 1 | 100 | 100 |00:00:00.01 | 2 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("X">900)
Note
-----
- dynamic statistics used: dynamic sampling (level=0)
The query is an index only scan (it doesn't access the table). So this must be dynamic index stats!
You can see similar improvements when you add a group by (query block adjustment):
SQL> select /*+ gather_plan_statistics dynamic_sampling (0) */mod(x, 10) m, count(*) from t
2 where x > 900
3 group by mod(x, 10);
M COUNT(*)
---------- ----------
1 10
6 10
2 10
4 10
5 10
8 10
3 10
7 10
9 10
0 10
SQL>
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST +NOTE'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
SQL_ID 4gz72qvvwjz12, child number 0
-------------------------------------
select /*+ gather_plan_statistics dynamic_sampling (0) */mod(x, 10) m,
count(*) from t where x > 900 group by mod(x, 10)
Plan hash value: 1380848886
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 2 |
| 1 | HASH GROUP BY | | 1 | 33 | 10 |00:00:00.01 | 2 |
|* 2 | INDEX RANGE SCAN| I | 1 | 33 | 100 |00:00:00.01 | 2 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("X">900)
SQL>
SQL> select /*+ gather_plan_statistics dynamic_sampling (11) */mod(x, 10) m, count(*) from t
2 where x > 900
3 group by mod(x, 10);
M COUNT(*)
---------- ----------
1 10
6 10
2 10
4 10
5 10
8 10
3 10
7 10
9 10
0 10
SQL>
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST +NOTE'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
SQL_ID 3p9f80dwhv2rj, child number 0
-------------------------------------
select /*+ gather_plan_statistics dynamic_sampling (11) */mod(x, 10) m,
count(*) from t where x > 900 group by mod(x, 10)
Plan hash value: 1380848886
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 2 |
| 1 | HASH GROUP BY | | 1 | 10 | 10 |00:00:00.01 | 2 |
|* 2 | INDEX RANGE SCAN| I | 1 | 100 | 100 |00:00:00.01 | 2 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("X">900)
Note
-----
- dynamic statistics used: dynamic sampling (level=0)
Without the dynamic stats Oracle guessed the group by would return 33 rows. But it actually gives 10. With dynamic stats the estimate is correct.
You can see similar with joining:
SQL> select /*+ gather_plan_statistics dynamic_sampling (0) */count(*)
2 from t t1
3 join t t2
4 on mod(t1.x, 100) = mod(t2.x, 10);
COUNT(*)
----------
10000
SQL>
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST +NOTE'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID 70txpy61g9byg, child number 0
-------------------------------------
select /*+ gather_plan_statistics dynamic_sampling (0) */count(*) from
t t1 join t t2 on mod(t1.x, 100) = mod(t2.x, 10)
Plan hash value: 791582492
--------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 14 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 14 |
|* 2 | HASH JOIN | | 1 | 4277 | 10000 |00:00:00.01 | 14 |
| 3 | TABLE ACCESS FULL| T | 1 | 654 | 1000 |00:00:00.01 | 7 |
| 4 | TABLE ACCESS FULL| T | 1 | 654 | 1000 |00:00:00.01 | 7 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(MOD("T1"."X",100)=MOD("T2"."X",10))
SQL>
SQL> select /*+ gather_plan_statistics dynamic_sampling (11) */count(*)
2 from t t1
3 join t t2
4 on mod(t1.x, 100) = mod(t2.x, 10);
COUNT(*)
----------
10000
SQL>
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST +NOTE'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID 8s93pjnzqxh3x, child number 0
-------------------------------------
select /*+ gather_plan_statistics dynamic_sampling (11) */count(*) from
t t1 join t t2 on mod(t1.x, 100) = mod(t2.x, 10)
Plan hash value: 791582492
--------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 14 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 14 |
|* 2 | HASH JOIN | | 1 | 10000 | 10000 |00:00:00.01 | 14 |
| 3 | TABLE ACCESS FULL| T | 1 | 654 | 1000 |00:00:00.01 | 7 |
| 4 | TABLE ACCESS FULL| T | 1 | 654 | 1000 |00:00:00.01 | 7 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(MOD("T1"."X",100)=MOD("T2"."X",10))
Note
-----
- dynamic statistics used: dynamic sampling (level=0)
Notice the E-rows for the join is now correct (vs. 5,723 out without this).