分区表imdp 导入 收集统计信息 自动 采样导致执行计划card 不准

操作过程：

--采用 auto_sample_size 

exec dbms_stats.gather_table_stats(user,'T_IMAGE_P_INTERVL_HASH',method_opt=>'for all columns size repeat',estimate_percent =>dbms_stats.auto_sample_size,granularity=>'ALL',CASCADE=>TRUE,DEGREE=>8,no_invalidate=>true);

SQL*Plus: Release 11.2.0.4.0 Production on Tue Oct 31 22:01:20 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> set autot trace exp stat
SQL> set lin 1000
SQL> set timing on
SQL> select /*+ gather_plan_statistics  */ *  from t_image_p_intervl_hash where hospital_id='04720081';

no rows selected

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 350982048

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                        | 13833 |  3998K|    10   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ALL   |                        | 13833 |  3998K|    10   (0)| 00:00:01 |     1 |1048575|
|   2 |   PARTITION HASH SINGLE|                        | 13833 |  3998K|    10   (0)| 00:00:01 |    18 |    18 |
|*  3 |    TABLE ACCESS FULL   | T_IMAGE_P_INTERVL_HASH | 13833 |  3998K|    10   (0)| 00:00:01 |       |       |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("HOSPITAL_ID"='04720081')


Statistics
----------------------------------------------------------
          1  recursive calls
          1  db block gets
         30  consistent gets
          1  physical reads
        168  redo size
       3285  bytes sent via SQL*Net to client
        509  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed
--改为非自动采样
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'CENTER',tabname=>'T_IMAGE_P_INTERVL_HASH',estimate_percent=>30,no_invalidate=>false,granularity=>'ALL',cascade=>true,degree => 10);

PL/SQL procedure successfully completed.

Elapsed: 00:00:34.47
SQL> select /*+ gather_plan_statistics  */ *  from t_image_p_intervl_hash where hospital_id='04720081';

no rows selected

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2376421221

----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                             |     5 |  1480 |     4   (0)| 00:00:01 |   |        |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| T_IMAGE_P_INTERVL_HASH      |     5 |  1480 |     4   (0)| 00:00:01 | ROWID | ROWID |
|*  2 |   INDEX RANGE SCAN                 | INX_HOSID_PK_LIS_CODE_HASH5 |     5 |       |     3   (0)| 00:00:01 |   |        |
----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("HOSPITAL_ID"='04720081')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
       3285  bytes sent via SQL*Net to client
        509  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed