oracle 11.2.0.4使用dbms_stats收集統計資訊statistics及刪除和還原相關測試之一

測試結論
1，oracle 11.2.0.4,手工建立表並插入資料， 不會馬上收集統計資訊


2, 收集統計資訊 exec dbms_stats.gather_table_stats(user,'t_stat',cascade=>true);


3，備份統計資訊 exec dbms_stats.create_stat_table(user,'stat_t_stat');


exec dbms_stats.export_table_stats('user_zxy','t_stat',null,'stat_t_stat',null,true,'user_zxy');


4，刪除統計資訊
    
exec dbms_stats.delete_table_stats(user,'t_stat');


5，還原統計資訊
exec dbms_stats.import_table_stats('user_zxy','t_stat',null,'stat_t_stat',null,true,'user_zxy',true,true);




測試明細
1，作業系統版本
[oracle@mygirl ~]$ more /etc/redhat-release 
Red Hat Enterprise Linux Server release 6.5 (Santiago)






2，資料庫版本
SQL> select * from v$version where rownum=1;


BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production




3,構建測試表及資料
SQL> conn user_zxy/system
Connected.
SQL> create table t_stat(a int,b int);


Table created.


SQL> insert into t_stat select level,level+3 from dual connect by level<=10;


10 rows created.


SQL> commit;


Commit complete.






4，新建測試表不會收集統計資訊
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';


SQL> select table_name,column_name,num_distinct,low_value,high_value,num_nulls,num_buckets,last_analyzed,sample_size,histogram from user_tab_col_statistics where lower(table_name)='t_stat';


no rows selected


SQL> select count(*) from user_tables where lower(table_name)='t_stat';


  COUNT(*)
----------
         1




SQL> select table_name,num_rows,blocks,empty_blocks,last_analyzed from user_tables where lower(table_name)='t_stat';


TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS LAST_ANAL
------------------------------ ---------- ---------- ------------ ---------
T_STAT






5,手工收集統計資訊
SQL> exec dbms_stats.gather_table_stats(user,'t_stat',cascade=>true);


PL/SQL procedure successfully completed.


6，手工收集統計資訊後則有了統計資訊


  1* select table_name,num_rows,blocks,empty_blocks,last_analyzed from user_tables where lower(table_name)='t_stat'


TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS LAST_ANALYZED
------------------------------ ---------- ---------- ------------ -------------------
T_STAT                                 10          5            0 2017-05-29 22:17:24




SQL> r
  1* select table_name,column_name,num_distinct,low_value,high_value,num_nulls,num_buckets,last_analyzed,sample_size,histogram from user_tab_col_statistics where lower(table_name)='t_stat'


TABLE_NAME      COLUMN_NAME     NUM_DISTINCT LOW_VALUE                      HIGH_VALUE            NUM_NULLS NUM_BUCKETS LAST_ANALYZED       SAMPLE_SIZE HISTOGRAM
--------------- --------------- ------------ ------------------------------ -------------------- ---------- ----------- ------------------- ----------- ---------------
T_STAT          A                         10 C102                           C10B                          0           1 2017-05-29 22:17:24          10 NONE
T_STAT          B                         10 C105                           C10E                          0           1 2017-05-29 22:17:24          10 NONE




7，手工刪除統計資訊
SQL> exec dbms_stats.delete_table_stats(user,'t_stat');


PL/SQL procedure successfully completed.


8,手工刪除統計資訊後則沒有統計資訊了
SQL> select table_name,num_rows,blocks,empty_blocks,last_analyzed from user_tables where lower(table_name)='t_stat';


TABLE_NAME        NUM_ROWS     BLOCKS EMPTY_BLOCKS LAST_ANALYZED
--------------- ---------- ---------- ------------ -------------------
T_STAT


SQL> select table_name,column_name,num_distinct,low_value,high_value,num_nulls,num_buckets,last_analyzed,sample_size,histogram from user_tab_col_statistics where lower(table_name)='t_stat';


no rows selected






9，手工刪除紡計資訊前備份統計資訊然後還原統計資訊


SQL> exec dbms_stats.create_stat_table(user,'stat_t_stat');


PL/SQL procedure successfully completed.




SQL> exec dbms_stats.gather_table_stats(user,'t_stat',cascade=>true);


PL/SQL procedure successfully completed.




SQL> select table_name,num_rows,blocks,empty_blocks,last_analyzed from user_tables where lower(table_name)='t_stat';


TABLE_NAME        NUM_ROWS     BLOCKS EMPTY_BLOCKS LAST_ANAL
--------------- ---------- ---------- ------------ ---------
T_STAT                  10          5            0 29-MAY-17






SQL> exec dbms_stats.export_table_stats('user_zxy','t_stat',null,'stat_t_stat',null,true,'user_zxy');


PL/SQL procedure successfully completed.






SQL> exec dbms_stats.delete_table_stats(user,'t_stat');


PL/SQL procedure successfully completed.


SQL> select table_name,num_rows,blocks,empty_blocks,last_analyzed from user_tables where lower(table_name)='t_stat';


TABLE_NAME        NUM_ROWS     BLOCKS EMPTY_BLOCKS LAST_ANAL
--------------- ---------- ---------- ------------ ---------
T_STAT




SQL> exec dbms_stats.import_table_stats('user_zxy','t_stat',null,'stat_t_stat',null,true,'user_zxy',true,true);


PL/SQL procedure successfully completed.








SQL> select table_name,num_rows,blocks,empty_blocks,last_analyzed from user_tables where lower(table_name)='t_stat';


TABLE_NAME        NUM_ROWS     BLOCKS EMPTY_BLOCKS LAST_ANAL
--------------- ---------- ---------- ------------ ---------
T_STAT                  10          5            0 29-MAY-17