利用dbms_stats的procedure gather_schema_stats 和gather_database_stats通过使用参数options 和objlist可以列出那些没有统计信息或者统计信息stale的对象。
[@more@]其中options的值可以是:
Value | Meaning |
---|---|
| Gathers statistics on tables with stale statistics. |
| Gathers statistics on all tables. (default) |
| Gathers statistics only on tables without statistics. |
| Creates a list of tables with stale statistics. |
| Creates a list of tables that do not have statistics. |
| Gathers all the statistics for the objects of a specific schema (or database with |
The objlist
parameter identifies an output parameter for the LIST
STALE
and LIST
EMPTY
options. The objlist
parameter is of type DBMS_STATS
.OBJECTTAB
.
--===============================================
SQL> edit
已写入 file afiedt.buf
1 declare
2 v_obj dbms_stats.objecttab;
3 begin
4 dbms_stats.gather_schema_stats
5 (OWNNAME=>'XYS',OPTIONS=>'LIST EMPTY',OBJLIST=>v_obj );
6 for i in 1 .. v_obj .count
7 loop
8 dbms_output.put_line( v_obj (i).ownname );
9 dbms_output.put_line( v_obj (i).objType );
10 dbms_output.put_line( v_obj (i).objName );
11 dbms_output.put_line( v_obj (i).PartName );
12 dbms_output.put_line( v_obj (i).subPartName );
13 dbms_output.put_line
14 ( '-------------------------' );
15 end loop;
16* end;
SQL> /
XYS
TABLE
MY_STAT_TAB
-------------------------
XYS
TABLE
T1
-------------------------
XYS
TABLE
TEST
-------------------------
XYS
TABLE
TEST1
-------------------------
XYS
TABLE
TT1
-------------------------
XYS
INDEX
IDX_T1
-------------------------
XYS
INDEX
MY_STAT_TAB
-------------------------
PL/SQL 过程已成功完成。
SQL> select table_name,num_rows,blocks,avg_row_len from user_tables;
TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN
------------------------------ ---------- ---------- -----------
T1
TEST
TEST1
TT1
TT 5 5 8
MY_STAT_TAB
已选择6行。
SQL> select index_name,blevel,leaf_blocks,clustering_factor from user_indexes;
INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
------------------------------ ---------- ----------- -----------------
IDX_TT 0 1 1
SYS_IL0000016783C00026$$
MY_STAT_TAB
IDX_T1
SQL>
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/19602/viewspace-1002654/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/19602/viewspace-1002654/