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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-2140042/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-2140042/