Dear Experts,
I am struggling a bit with "dynamic sampling". Showering your expertise would be of great help.
My understanding about "Dynamic Sampling":
When statistics are missing for a table, Oracle optimizer will sample 64 blocks (at optimizer_dynamic_sampling=2) during query parsing phase to come up best execution plan.
Based on this concept, I carried a small test on Oracle 10g Release 2 (10.2.0.4) (OS = Windows XP).
SQL> show parameter optimizer
NAME TYPE VALUE
------------------------------------ ----------- --------------
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 10.2.0.4
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
SQL>
SQL> conn test/test
SQL> create table t( id number, name varchar2(30));
Table created.
SQL> create UNIQUE index t_idx on t(id);
Index created.
SQL> insert into t select level, 'test'||level from dual connect by level <= 1000;
1000 rows created.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.delete_table_stats(user, 't'); --> I agree it's not required
PL/SQL procedure successfully completed.
SQL> set autotrace on
SQL> select * from t where id=50;
ID NAME
---------- ------------------------------
50 test50
Execution Plan
----------------------------------------------------------
Plan hash value: 2929955852
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 30 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | T_IDX | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=50)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
124 redo size
466 bytes sent via SQL*Net to client
395 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> set autotrace off
What makes Oracle Optimizer choose the INDEX Access path when:
- Statistics are missing.
- Also, Optimizer did not consider "Dynamic Sampling"
I traced the query to see Optimizer behaviour.
SQL> alter session set tracefile_identifier= 'dyn';
Session altered.
SQL> alter session set events '10053 trace name context forever, level 1';
Session altered.
SQL> select * from t where id = 70;
ID NAME
---------- ------------------------------
70 test70
SQL> alter session set events '10053 trace name context off';
Session altered.
SQL> disc
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
Below is an excerpt from 10053 trace and there is no sign of optimizer doing dynamic sampling.
Is RBO used to arrive to Index Access Path?????
SINGLE TABLE ACCESS PATH
-----------------------------------------
BEGIN Single Table Cardinality Estimation
-----------------------------------------
Column (#1): ID(NUMBER) NO STATISTICS (using defaults)
AvgLen: 13.00 NDV: 3 Nulls: 0 Density: 0.39024
Table: T Alias: T
Card: Original: 82 Rounded: 1 Computed: 0.82 Non Adjusted: 0.82
-----------------------------------------
END Single Table Cardinality Estimation
-----------------------------------------
Access Path: TableScan
Cost: 2.00 Resp: 2.00 Degree: 0
Cost_io: 2.00 Cost_cpu: 23541
Resp_io: 2.00 Resp_cpu: 23541
Access Path: index (UniqueScan)
Index: T_IDX
resc_io: 2.00 resc_cpu: 15463
ix_sel: 0.012195 ix_sel_with_filters: 0.012195
Cost: 1.00 Resp: 1.00 Degree: 1
Access Path: index (AllEqUnique)
Index: T_IDX
resc_io: 2.00 resc_cpu: 15463
ix_sel: 0.004 ix_sel_with_filters: 0.004
Cost: 1.00 Resp: 1.00 Degree: 1
One row Card: 1.00
Best:: AccessPath: IndexUnique Index: T_IDX
Cost: 1.00 Degree: 1 Resp: 1.00 Card: 1.00 Bytes: 0
When I drop the Index and re-run the same query, I notice that optimizer is now doing dynamic sampling:
SQL> conn test/test
Connected.
SQL> drop index t_idx;
Index dropped.
SQL> set autotrace on
SQL> select * from t where id = 100;
ID NAME
---------- ------------------------------
100 test100
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 30 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=100)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
168 recursive calls
0 db block gets
34 consistent gets
0 physical reads
0 redo size
467 bytes sent via SQL*Net to client
395 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set autotrace off
SQL>
Thanks for clarifying my doubt.
Regards