These discussions revolved around the following issues with Dynamic Sampling and indexes:
1. CREATE INDEX On Empty Table
There is an interesting change in behaviour that has been introduced with release 11.2. Whereas pre-11.2 versions do not automatically gather index statistics on empty tables, 11.2 does so. So a simple CREATE TABLE followed by a CREATE INDEX command (or an ALTER TABLE add constraint that implicitly/explictly creates indexes) will now lead to a table without statistics, but index statistics with every value set to 0 (rows, distinct keys, clustering factor, leaf blocks etc.)
Since Oracle 10g a CREATE INDEX command automatically computes statistics for an index (since Oracle 9i you could add optionally a COMPUTE STATISTICS clause to the CREATE INDEX command). The interesting point is that there is no "NOCOMPUTE STATISTICS" clause, so you cannot prevent this from happening via the syntax.
There is a way to prevent it, but only indirectly by locking the table statistics. Funny enough, if the table statistics are locked, there is again no way to overwrite the lock by using a FORCE keyword or similar as part of the CREATE INDEX command, whereas DBMS_STATS offers such a FORCE option.
Note that creating constraints requiring indexes as part of the CREATE TABLE command shows a different behaviour: The indexes do not have statistics in this case.
This shouldn't be too relevant for real-life cases, but is probably more important to setups of test cases / demonstrations. Of course it can become relevant to real-life deployment tasks that add new objects to a database. If statistics are not handled properly afterwards you now end up with tables missing statistics but indexes with 0 statistics.
Of course when deliberately leaving a table without statistics you need to do something about it anyway, because otherwise the default statistics job (since 10g) will turn this table into a potential threat by gathering statistics during the maintenance window where such tables might be in some state that is not representative for the actual workload during processing.
Either the default statistics job has to be disabled or re-configured via the AUTOSTATS_TARGET option of the SET_GLOBAL_PREFS / SET_PARAM procedures of DBMS_STATS, or the table statistics need to be locked individually via LOCK_TABLE_STATS.
So a reasonable order of commands for such a table at deployment time could look like this:
CREATE TABLE...
EXEC DBMS_STATS.LOCK_TABLE_STATS(...)
CREATE INDEX...
This way the statistics will be handled consistently for both table and indexes.
Note that all this doesn't apply to Global Temporary Tables (GTTs) as they are not analyzed by the default statistics job and creating an index on them doesn't automatically gather statistics for that index either, even in 11.2.
The change introduced in 11.2 can be seen by simply trying the following in 11.2 and pre-11.2 versions to see the difference:
drop table t;
purge table t;
create table t (id number(*, 0) not null, id2 number(*, 0), filler varchar2(100));
create index t_idx on t (id);
select num_rows, blocks from user_tab_statistics where table_name = 'T';
select blevel, num_rows, leaf_blocks, distinct_keys, clustering_factor from user_ind_statistics where table_name = 'T';
2. Inconsistent Costing
The reason why this change in behaviour is particularly relevant is the fact that the optimizer behaves inconsistently if a table doesn't have statistics but a suitable index does.
If Dynamic Sampling gets used (very likely from 10g on because the table is missing statistics and the default level of 2 will trigger Dynamic Sampling for such a table in this case) the optimizer will not only obtain basic statistics information about the table but also overwrite the existing index statistics, but only partially.
The problem is that the existing index leaf blocks statistics get overwritten by the Dynamic Sampling code, but the clustering factor is not.
You can see this from the following optimizer trace snippet:
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: T6 Alias: T6 (NOT ANALYZED)
#Rows: 82 #Blks: 1 AvgRowLen: 100.00
Index Stats::
Index: IND_T6_C2 Col#: 2
LVLS: 0 #LB: 0 #DK: 0 LB/K: 0.00 DB/K: 0.00 CLUF: 0.00
Access path analysis for T6
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T6[T6]
*** 2012-02-17 16:28:49.182
** Performing dynamic sampling initial checks. **
** Dynamic sampling initial checks returning TRUE (level = 2).
** Dynamic sampling updated index stats.: IND_T6_C2, blocks=227
** Dynamic sampling index access candidate : IND_T6_C2
** Dynamic sampling updated table stats.: blocks=4529
So although the index has statistics gathered (there is no (NOT ANALYZED) mentioned for the index) the Dynamic Sampling updates the index stats "blocks".
This is not consistent with the expected behaviour of Dynamic Sampling, because by default it is not supposed to overwrite existing statistics. If you want to force Dynamic Sampling to do so you need to specify the (not really) documented table level hint DYNAMIC_SAMPLING_EST_CDN.
However, another pretty important number, the clustering factor of the index, doesn't get updated. Since the clustering factor is important for accessing the table via an index any execution plan that needs to visit the table in such a way will now be costed inconsistently: The index access part will be based on the index block statistics updated by Dynamic Sampling whereas the table access part will be costed using the clustering factor (and potentially other existing index statistics left unmodified by Dynamic Sampling).
This will potentially lead to rather unreasonable plans, as the following test case demonstrates:
set echo on linesize 200
drop table t;
purge table t;
create table t (
c1 number not null,
c2 number not null,
c3 varchar2(300) not null);
create index t_idx on t(c2);
create index t_pk on t(c1);
insert into
t
select
rownum as c1,
1 as c2,
rpad('A',300,'A') as c3
from
dual
connect by
level<=100000;
commit;
alter session set optimizer_dynamic_sampling = 8;
explain plan for
select * from t where c2 = 1;
select * from table(dbms_xplan.display);
select index_name, blevel, num_rows, leaf_blocks, distinct_keys, clustering_factor from user_ind_statistics where table_name = 'T';
exec dbms_stats.delete_index_stats(null, 't_idx', no_invalidate=>false)
explain plan for
select * from t where c2 = 1;
select * from table(dbms_xplan.display);
exec dbms_stats.gather_index_stats(null, 't_idx', no_invalidate=>false)
explain plan for
select /*+ index(t(c2)) */ * from t where c2 = 1;
select * from table(dbms_xplan.display);
I get the following execution plans from 11.2.0.1:
SQL>
SQL> alter session set optimizer_dynamic_sampling = 8;
Session altered.
SQL>
SQL> explain plan for
2 select * from t where c2 = 1;
Explained.
SQL>
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 470836197
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 16M| 177 (0)| 00:00:03 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 100K| 16M| 177 (0)| 00:00:03 |
|* 2 | INDEX RANGE SCAN | T_IDX | 100K| | 177 (0)| 00:00:03 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C2"=1)
Note
-----
- dynamic sampling used for this statement (level=8)
18 rows selected.
SQL>
SQL> select index_name, blevel, num_rows, leaf_blocks, distinct_keys, clustering_factor from user_ind_statistics where table_name = 'T';
INDEX_NAME BLEVEL NUM_ROWS LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR
------------------------------ ---------- ---------- ----------- ------------- -----------------
T_IDX 0 0 0 0 0
T_PK 0 0 0 0 0
SQL>
SQL> exec dbms_stats.delete_index_stats(null, 't_idx', no_invalidate=>false)
PL/SQL procedure successfully completed.
SQL>
SQL> explain plan for
2 select * from t where c2 = 1;
Explained.
SQL>
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 470836197
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 16M| 978 (1)| 00:00:12 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 100K| 16M| 978 (1)| 00:00:12 |
|* 2 | INDEX RANGE SCAN | T_IDX | 100K| | 177 (0)| 00:00:03 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C2"=1)
Note
-----
- dynamic sampling used for this statement (level=8)
18 rows selected.
SQL>
SQL> exec dbms_stats.gather_index_stats(null, 't_idx', no_invalidate=>false)
PL/SQL procedure successfully completed.
SQL>
SQL> explain plan for
2 select /*+ index(t(c2)) */ * from t where c2 = 1;
Explained.
SQL>
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 470836197
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 16M| 4533 (1)| 00:00:55 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 100K| 16M| 4533 (1)| 00:00:55 |
|* 2 | INDEX RANGE SCAN | T_IDX | 100K| | 179 (2)| 00:00:03 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C2"=1)
Note
-----
- dynamic sampling used for this statement (level=8)
18 rows selected.
Notice how the optimizer for the first execution plan gets a reasonable cardinality estimate from the Dynamic Sampling (due to the increased level it is spot on in this case) but still thinks that reading 100,000 rows from a 100,000 table via an index is a good idea.
While the index access is costed reasonably based on the updated index blocks statistics, the table access is "cost-free" due to the clustering factor left unmodified at 0.
When deleting the index statistics we can see that a default clustering factor of 800 gets assumed (you can see this in the optimizer trace file), which still makes the index access more favourable.
Only with index statistics gathered a more reasonable plan gets selected.
There is a bug filed against 11.2 (bug no 12942119 "Suboptimal plan if index has statistics but table has no statistics and dynamic sampling occurs") that seems to address this issue, but it is not clear from the bug description what exactly the bug fix does. It hasn't made it into the 11.2.0.3 patch set but it is available as one-off patch and part of some Windows platform intermediate patch sets.
3. 11.2.0.3 Change In Behaviour
The 11.2.0.3 patch set introduces another interesting change in behaviour: As I've just demonstrated even with index statistics missing and hence consistent optimizer behaviour the default clustering factor assumed might still favour unreasonable execution plans.
There is a bug fix 12399886:"OPTIMIZER STILL USES DEFAULT INDEX STATISTICS AFTER DYNAMIC SAMPLING WAS USED" that is by default enabled in 11.2.0.3 that addresses this issue and uses a different clustering factor derived from the Dynamic Sampling results (it turns out to be based on the blocks of the table, not the rows, see below for more details).
This is a good thing in principle because the cost estimates now might be closer to reality, but as always this might cause a lot of headaches after applying the patch sets because execution plans might change. This applies of course only to those scenarios that rely on Dynamic Sampling and can make use indexes.
Note that the inconsistent behaviour described under 2. is still there in 11.2.0.3, so tables without statistics having index statistics gathered still don't mix very well.
Here are the execution plans that I got from 11.2.0.3 for above test case:
SQL> alter session set optimizer_dynamic_sampling = 8;
Session altered.
SQL>
SQL> explain plan for
2 select * from t where c2 = 1;
Explained.
SQL>
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 470836197
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 98120 | 16M| 370 (0)| 00:00:05 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 98120 | 16M| 370 (0)| 00:00:05 |
|* 2 | INDEX RANGE SCAN | T_IDX | 98120 | | 370 (0)| 00:00:05 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C2"=1)
Note
-----
- dynamic sampling used for this statement (level=8)
18 rows selected.
SQL>
SQL> select index_name, blevel, num_rows, leaf_blocks, distinct_keys, clustering_factor from user_ind_statistics where table_name = 'T';
INDEX_NAME BLEVEL NUM_ROWS LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR
------------------------------ ---------- ---------- ----------- ------------- -----------------
T_IDX 0 0 0 0 0
T_PK 0 0 0 0 0
SQL>
SQL> exec dbms_stats.delete_index_stats(null, 't_idx', no_invalidate=>false)
PL/SQL procedure successfully completed.
SQL>
SQL> explain plan for
2 select * from t where c2 = 1;
Explained.
SQL>
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 98120 | 16M| 1230 (1)| 00:00:15 |
|* 1 | TABLE ACCESS FULL| T | 98120 | 16M| 1230 (1)| 00:00:15 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C2"=1)
Note
-----
- dynamic sampling used for this statement (level=8)
17 rows selected.
SQL>
SQL> explain plan for
2 select /*+ index(t(c2)) */ * from t where c2 = 1;
Explained.
SQL>
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 470836197
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 98120 | 16M| 36602 (1)| 00:07:20 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 98120 | 16M| 36602 (1)| 00:07:20 |
|* 2 | INDEX RANGE SCAN | T_IDX | 98120 | | 370 (0)| 00:00:05 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C2"=1)
Note
-----
- dynamic sampling used for this statement (level=8)
18 rows selected.
SQL>
SQL> exec dbms_stats.gather_index_stats(null, 't_idx', no_invalidate=>false)
PL/SQL procedure successfully completed.
SQL>
SQL> explain plan for
2 select /*+ index(t(c2)) */ * from t where c2 = 1;
Explained.
SQL>
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 470836197
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 98120 | 16M| 4898 (1)| 00:00:59 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 98120 | 16M| 4898 (1)| 00:00:59 |
|* 2 | INDEX RANGE SCAN | T_IDX | 98120 | | 371 (1)| 00:00:05 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C2"=1)
Note
-----
- dynamic sampling used for this statement (level=8)
18 rows selected.
Notice 11.2.0.3 suffers from the same cost-free table access with the zero index statistics in place, but after deleting the index statistics a full table scan will be chosen. It looks like that the new clustering factor is based on table blocks * 8 rather than a hard coded value of 800. Both constants (8, 800) might depend on the default block size - these tests were performed on 8KB.
