監視stale statistics(失真的統計資訊)的物件！

透過dbms_stats提供的幾個procedure可以監視stale statistics的物件以便之後對這些物件蒐集statistics。[@more@]

SQL> exec dbms_stats.alter_schema_tab_monitoring('xys');

PL/SQL 過程已成功完成。

SQL> select * from tt;

ID N
---------- -
1 a
2 A
3 B
4 b
5 c

SQL> update tt set name='m';

已更新5行。

SQL> commit;

提交完成。
--user_tab_modifications中沒有記錄，oracle flush sga
到user_tab_modifications可能需要一段時間，不過我們可以
透過DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO來手動flush
SQL> select * from user_tab_modifications;

未選定行

--手動flush sga的資訊到user_tab_modifications
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO ;

PL/SQL 過程已成功完成。

SQL> desc user_tab_modifications
名稱 是否為空? 型別
----------------------------------------- -------- ----------------------------

TABLE_NAME VARCHAR2(30)
PARTITION_NAME VARCHAR2(30)
SUBPARTITION_NAME VARCHAR2(30)
INSERTS NUMBER
UPDATES NUMBER
DELETES NUMBER
TIMESTAMP DATE
TRUNCATED VARCHAR2(3)
DROP_SEGMENTS NUMBER

SQL> select * from user_tab_modifications;

TABLE P SUBPA INSERTS UPDATES DELETES TIMESTAMP TRU DROP_SEGMENTS
----- - ----- ---------- ---------- ---------- -------------- --- -------------
TT 0 5 0 17-4月 -08 NO 0

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 STALE',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> /

PL/SQL 過程已成功完成。
Objects are considered stale when 10% of the total rows have been changed. When you issue

GATHER_TABLE_STATS with GATHER STALE, the procedure checks the USER_TAB_MODIFICATIONS view.

If a monitored table has been modified more than 10%, then statistics are gathered again.

The information about changes of tables, as shown in the USER_TAB_MODIFICATIONS view, can be

flushed from the SGA into the data dictionary with the

DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO procedure.
資料的10%被修改的時其statistics被認為就是失真的，那麼這個物件就被記錄在

user_tab_modifications中，不過我們來驗證一下oracle是否會自動蒐集具有stale statistics的物件
的statistics呢？

SQL> SET SERVEROUTPUT ON
SQL> /
XYS
TABLE
TT

PL/SQL 過程已成功完成。

--透過下面試驗驗證oracle不會對那些stale statistics的物件進行statistics自動蒐集，僅僅是監視而已。

SQL> select table_name , num_rows,blocks,avg_row_len from user_tables where tabl
e_name='TT';

TABLE NUM_ROWS BLOCKS AVG_ROW_LEN
----- ---------- ---------- -----------
TT 5 5 8

SQL> select * from tt;

ID N
---------- -
1 m
2 m
3 m
4 m
5 m

SQL> insert into tt select *from tt;

已建立5行。

SQL> commit;

提交完成。

--oracle到底什麼時候會把sga中的資訊flush到user_tab_modifications中也是我的疑問

SQL> select * from user_tab_modifications;

TABLE P SUBPA INSERTS UPDATES DELETES TIMESTAMP TRU DROP_SEGMENTS
----- - ----- ---------- ---------- ---------- -------------- --- -------------
TT 0 5 0 17-4月 -08 NO 0

SQL> select * from user_tab_modifications;

TABLE P SUBPA INSERTS UPDATES DELETES TIMESTAMP TRU DROP_SEGMENTS
----- - ----- ---------- ---------- ---------- -------------- --- -------------
TT 0 5 0 17-4月 -08 NO 0

SQL> select * from user_tab_modifications;

TABLE P SUBPA INSERTS UPDATES DELETES TIMESTAMP TRU DROP_SEGMENTS
----- - ----- ---------- ---------- ---------- -------------- --- -------------
TT 0 5 0 17-4月 -08 NO 0

--等不及了，還是手動flush一下吧

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO ;

PL/SQL 過程已成功完成。

SQL> select * from user_tab_modifications;

TABLE P SUBPA INSERTS UPDATES DELETES TIMESTAMP TRU DROP_SEGMENTS
----- - ----- ---------- ---------- ---------- -------------- --- -------------
TT 5 5 0 17-4月 -08 NO 0

SQL> select table_name , num_rows,blocks,avg_row_len from user_tables where tabl
e_name='TT';

TABLE NUM_ROWS BLOCKS AVG_ROW_LEN
----- ---------- ---------- -----------
TT 5 5 8

SQL>

上面查詢結果顯示 NUM_ROWS＝5而不是現在的10行資料，也就說oracle不會對那些具有stale statistics的物件自動蒐集statistics。

--不過如果有了監視資訊，之後可以透過引數OPTIONS=>'GATHER STALE'對這些具有stale statistics
的物件蒐集新的statistics。
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=>'GATHER STALE',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
TT
-------------------------

PL/SQL 過程已成功完成。

SQL> select * from user_tab_modifications;

未選定行

SQL> select table_name , num_rows,blocks,avg_row_len from user_tables where tabl
e_name='TT';

TABLE NUM_ROWS BLOCKS AVG_ROW_LEN
----- ---------- ---------- -----------
TT 10 5 7

SQL>