oracle为何收集统计信息不准,收集统计信息后为什么执行计划更不准

SQL> create table t as select 1 a,object_name from dba_objects;

Table created.

SQL> update t set a=2 where rownum=1;

1 row updated.

SQL> create index idx_t on t(a);

Index created.

SQL> select a,count(*) from t group by a;

A   COUNT(*)

---------- ----------

1      86275

2          1

SQL> set autot on;

SQL> select * from t where a=2;

A OBJECT_NAME

---------- --------------------------------------------------------------------------------------------------------------------------------

2 ICOL$

Execution Plan

----------------------------------------------------------

Plan hash value: 1594971208

-------------------------------------------------------------------------------------

| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |       |     1 |    79 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |    79 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_T |     1 |       |     1   (0)| 00:00:01 |

-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("A"=2)

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

10  recursive calls

0  db block gets

78  consistent gets

1  physical reads

0  redo size

602  bytes sent via SQL*Net to client

523  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true,method_opt => 'for all columns size auto');

PL/SQL procedure successfully completed.

SQL> select * from t where a=2;

A OBJECT_NAME

---------- --------------------------------------------------------------------------------------------------------------------------------

2 ICOL$

Execution Plan

----------------------------------------------------------

Plan hash value: 1594971208

-------------------------------------------------------------------------------------

| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |       |    16 |   448 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T     |    16 |   448 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_T |    16 |       |     1   (0)| 00:00:01 |

-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("A"=2)

Statistics

----------------------------------------------------------

1  recursive calls

0  db block gets

7  consistent gets

0  physical reads

0  redo size

602  bytes sent via SQL*Net to client

523  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

1  sorts (memory)

0  sorts (disk)

1  rows processed

SQL> select * from t where a=2;

A OBJECT_NAME

---------- --------------------------------------------------------------------------------------------------------------------------------

2 ICOL$

Execution Plan

----------------------------------------------------------

Plan hash value: 1594971208

-------------------------------------------------------------------------------------

| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |       |    16 |   448 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T     |    16 |   448 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_T |    16 |       |     1   (0)| 00:00:01 |

-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("A"=2)

Statistics

----------------------------------------------------------

0  recursive calls

0  db block gets

3  consistent gets

0  physical reads

0  redo size

602  bytes sent via SQL*Net to client

523  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

同樣的方法，我的和你不一樣。