OBJECT_NAME CREATED LAST_DDL_TIM
-------------------------------------------------------------------------------------------------------------------------------- ------------ ------------
CUSTOMERS 28-NOV-14 28-NOV-14
表sales,customers的统计信息收集时间是2014-11-28
SQL> select * from dba_tab_stats_history where owner='SH' and table_name='SALES';
OWNER TABLE_NAME PARTITION_NAME SUBPARTITION_NAME STATS_UPDATE_TIME
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------------------------------------------------------------------
SH SALES 28-NOV-14 01.07.30.055660 PM +08:00
SQL> select * from dba_tab_stats_history where owner='SH' and table_name='CUSTOMERS';
OWNER TABLE_NAME PARTITION_NAME SUBPARTITION_NAME STATS_UPDATE_TIME
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------------------------------------------------------------------
SH CUSTOMERS 28-NOV-14 01.07.37.664982 PM +08:00
在重新导入数据后对表sales,customers收集统计信息
SQL> exec dbms_stats.gather_table_stats(ownname => 'SH',tabname => 'SALES',method_opt => 'for all columns size auto');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(ownname => 'SH',tabname => 'SALES',estimate_percent => 100,method_opt => 'for all columns size auto');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(ownname => 'SH',tabname => 'CUSTOMERS',estimate_percent => 100,method_opt => 'for all columns size auto');
PL/SQL procedure successfully completed.
SQL> select a.table_name,a.num_rows,a.blocks,a.empty_blocks,a.avg_row_len,a.last_analyzed from dba_tables a where a.owner='SH' and a.table_name='CUSTOMERS';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN LAST_ANALYZE
------------------------------ ---------- ---------- ------------ ----------- ------------
CUSTOMERS 55500 1486 0 181 12-DEC-14
SQL> select a.table_name,a.num_rows,a.blocks,a.empty_blocks,a.avg_row_len,a.last_analyzed from dba_tables a where a.owner='SH' and a.table_name='SALES';