oracle dbms_stat與analyze 獲取有效的統計資訊(3)

#object統計資訊的時效性(所謂的過時的統計資訊)
關於*_tab_modifications
Columns
   ___________________________

   TABLE_OWNER
      Owner of modified table
   TABLE_NAME
      Modified table
   PARTITION_NAME
      Modified partition
   SUBPARTITION_NAME
      Modified subpartition
   INSERTS
      Approximate number of rows inserted since last analyze
   UPDATES
      Approximate number of rows updated since last analyze
   DELETES
      Approximate number of rows deleted since last analyze
   TIMESTAMP
      Timestamp of last time this row was modified*********************
   TRUNCATED
      Was this object truncated since the last analyze

Oracle uses these views to identify tables with stale statistics.
Whenever there is 10% change in data in a table, Oracle considers its
statistics to be stale.

oracle 用記錄在此表中的資訊來決定統計資訊的時效性，當sql執行時oracle檢視此表看修改表資料的行數，10G 超過10%就算過期了dbms_stats就用這個判斷(11g可以修改這個閥值)

In 10G, the MONITORING and NOMONITORING keywords have been deprecated.
If you do specify these keywords, they are ignored.

In 10g, table-monitoring feature is controlled by the STATISTICS_LEVEL
parameter.

When STATISTICS_LEVEL is set to BASIC, monitoring is disabled on the table.

When STATISTICS_LEVEL is set to TYPICAL, then monitoring is enabled.

10g statistics_level=typical時or all時啟動統計
所以10g,11g是自動啟用的

SQL> select MONITORING from user_tables where rownum<2;

MON
---
YES

9i比較麻煩需要明確指定起用create table ,alert table中monitoring 才行 *_tables中欄位MONITORING 或nomonitoring禁用
#dbms_stats中alert_schema_tab_monitoring，alter_database_tab_monitoring也可以控制schema級別和db級所有可用的表是否monitoring（實際就是執行了一個alert table xx monitoring語句）

Monitoring tracks the approximate number of INSERT, UPDATE, and DELETE
operations for the table since the last time statistics were gathered.
information about how many rows are affected is maintained in the SGA,
until periodically (about every 15 minutes) SMON flush the data into the data
dictionary.
可以看到oracle說是15分鐘smon 執行重新整理操作

SQL> create user tt identified by a123
2 ;

User created.

SQL> grant dba to tt;

Grant succeeded.

SQL> conn tt/a123
Connected.
SQL> create table t_stat ( a int);

Table created.

SQL> declare
2 begin
3 for i in 1..1000 loop
4 insert into t_stat values(i);
5 end loop;
6 commit;
7 end;
8 /

PL/SQL procedure successfully completed.

SQL> set linesize 1000
SQL> select * from user_tab_statistics;

TABLE_NAME PARTITION_NAME PARTITION_POSITION SUBPARTITION_NAME SUBPARTITION_POSITION OBJECT_TYPE NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS AVG_CACHED_BLOCKS AVG_CACHE_HIT_RATIO SAMPLE_SIZE LAST_ANAL GLO USE STATT STA
------------------------------ ------------------------------ ------------------ ------------------------------ --------------------- ------------ ---------- ---------- ------------ ---------- ---------- ----------- ------------------------- ------------------- ----------------- ------------------- ----------- --------- --- --- ----- ---
T_STAT TABLE NO NO

可以看到沒有統計資訊

SQL> execute dbms_stats.gather_table_stats('TT','T_STAT');

PL/SQL procedure successfully completed.

SQL> select * from user_tab_statistics;

TABLE_NAME PARTITION_NAME PARTITION_POSITION SUBPARTITION_NAME SUBPARTITION_POSITION OBJECT_TYPE NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS AVG_CACHED_BLOCKS AVG_CACHE_HIT_RATIO SAMPLE_SIZE LAST_ANAL GLO USE STATT STA
------------------------------ ------------------------------ ------------------ ------------------------------ --------------------- ------------ ---------- ---------- ------------ ---------- ---------- ----------- ------------------------- ------------------- ----------------- ------------------- ----------- --------- --- --- ----- ---
T_STAT TABLE 1000 0 0 0 3 0 0 1000 27-APR-10 YES NO NO

SQL> delete from t_stat;

1000 rows deleted.

SQL> exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO();

PL/SQL procedure successfully completed.

SQL> SQL> select * from user_tab_modifications;

TABLE_NAME PARTITION_NAME SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRU DROP_SEGMENTS
------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- --------- --- -------------
T_STAT 0 0 1000 27-APR-10 NO 0

set serveroutput on
DECLARE
     l_objlist dbms_stats.objecttab;
     l_index PLS_INTEGER;
     BEGIN
     dbms_stats.gather_schema_stats(ownname => 'XH',
objlist => l_objlist,options=>'list stale');
        l_index := l_objlist.FIRST;
     WHILE l_index IS NOT NULL
     LOOP
    dbms_output.put(l_objlist(l_index).ownname || '.');
    dbms_output.put_line(l_objlist(l_index).objname);
    l_index := l_objlist.next(l_index);
    END LOOP;
    END;
   /

#可以看到查到了統計資訊過期的資訊(但並不收集解決)
SQL> set serveroutput on
DECLARE
SQL>   2       l_objlist dbms_stats.objecttab;
3       l_index PLS_INTEGER;
4       BEGIN
5       dbms_stats.gather_schema_stats(ownname => 'TT',
6   bjlist => l_objlist,options=>'list stale');
7          l_index := l_objlist.FIRST;
8       WHILE l_index IS NOT NULL
9       LOOP
10      dbms_output.put(l_objlist(l_index).ownname || '.');
11      dbms_output.put_line(l_objlist(l_index).objname);
12      l_index := l_objlist.next(l_index);
13      END LOOP;
14      END;
15     /
TT.T_STAT

PL/SQL procedure successfully completed.

SQL> select * from user_tab_modifications;

TABLE_NAME PARTITION_NAME SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRU DROP_SEGMENTS
------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- --------- --- -------------
T_STAT 0 0 1000 27-APR-10 NO 0

execute DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'TT', PTIONS=>'gather stale')

SQL> execute DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'TT', PTIONS=>'gather stale')

PL/SQL procedure successfully completed.

SQL> select * from user_tab_modifications;

no rows selected

SQL> declare
2      begin
3      for i in 1..1000 loop
4      insert into t_stat values(i);
5      end loop;
6      commit;
7      end;
8
9
10 /

PL/SQL procedure successfully completed.

SQL> select * from user_tab_modifications;

no rows selected

SQL> SQL> delete t_stat where a>999;

1 row deleted.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO();

PL/SQL procedure successfully completed.

SQL> select * from user_tab_modifications;

TABLE_NAME PARTITION_NAME SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRU DROP_SEGMENTS
------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- --------- --- -------------
T_STAT 1 0 1 27-APR-10 NO 0

SQL> set serveroutput on
SQL>   DECLARE
2       l_objlist dbms_stats.objecttab;
3       l_index PLS_INTEGER;
4       BEGIN
5       dbms_stats.gather_schema_stats(ownname => 'TT',
6   bjlist => l_objlist,options=>'list stale');
7          l_index := l_objlist.FIRST;
8       WHILE l_index IS NOT NULL
9       LOOP
10      dbms_output.put(l_objlist(l_index).ownname || '.');
11      dbms_output.put_line(l_objlist(l_index).objname);
12      l_index := l_objlist.next(l_index);
13      END LOOP;
14      END;
15     /

PL/SQL procedure successfully completed.

SQL>
#只修改了1行不會失效的

SQL> select dbms_stats.get_param(pname=>'stale_percent') from dual;

DBMS_STATS.GET_PARAM(PNAME=>'STALE_PERCENT')
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10
預設是10%就過時效(INSERTS + UPDATES + DELETES) >= 10%

SQL> execute dbms_stats.set_param(pname=>'stale_percent',pval=>2);修改引數（2%就過時效,只有11g才可以修改這個時效閥值）

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_param(pname=>'stale_percent') from dual;

DBMS_STATS.GET_PARAM(PNAME=>'STALE_PERCENT')
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2

SQL> delete t_stat where a>=970;

30 rows deleted.

SQL> commit;

Commit complete.

SQL> select * from user_tab_modifications;

TABLE_NAME PARTITION_NAME SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRU DROP_SEGMENTS
------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- --------- --- -------------
T_STAT 1 0 1 27-APR-10 NO 0

SQL> SQL> SQL> exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO();(手動讓smon搞)

PL/SQL procedure successfully completed.

SQL> select * from user_tab_modifications;

TABLE_NAME PARTITION_NAME SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRU DROP_SEGMENTS
------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- --------- --- -------------
T_STAT 1 0 31 27-APR-10 NO 0

SQL> set serveroutput on
DECLARE
     l_objlist dbms_stats.objecttab;
     l_index PLS_INTEGER;
     BEGIN
     dbms_stats.gather_schema_stats(ownname => 'TT',
SQL> bjlist => l_objlist,options=>'list stale');
        l_index := l_objlist.FIRST;
2    3    4    5    6    7    8       WHILE l_index IS NOT NULL
9       LOOP
10      dbms_output.put(l_objlist(l_index).ownname || '.');
11      dbms_output.put_line(l_objlist(l_index).objname);
12      l_index := l_objlist.next(l_index);
13      END LOOP;
14      END;
15     /
TT.T_STAT

PL/SQL procedure successfully completed.

可以看到過時效了

SQL> execute dbms_stats.reset_param_defaults;(使其回到預設值，all)

PL/SQL procedure successfully completed.

SQL> execute dbms_output.put_line(dbms_stats.get_param(pname=>'stale_percent'));
10

PL/SQL procedure successfully completed.

select u.TIMESTAMP,
           t.last_analyzed,
           u.table_name,
           u.inserts,
           u.updates,
           u.deletes,
           d.num_rows,
           TO_CHAR(((U.inserts+u.deletes+u.updates)/d.num_rows) * 100,'999.99')
percent
    from user_tables t,USER_TAB_MODIFICATIONS u,dba_tables d
    where u.table_name = t.table_name
    and d.table_name = t.table_name
    and d.owner = 'TT'
    and (u.inserts > 10000 or u.updates > 10000 or u.deletes > 10000)
order by t.last_analyzed
/

Since in 10g CBO is used, having up to date statistics is so important to generate
good execution plans. Automatic statistics collection job using DBMS_STATS
package depends on the monitoring data to determine when to collect statistics
on objects with stale objects.

Stats are considered stale when #(INSERTS + UPDATES + DELETES) >= 10%
of NUM_ROWS from dba_tables:

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-664240/，如需轉載，請註明出處，否則將追究法律責任。

oracle dbms_stat與analyze 獲取有效的統計資訊(3)

相關文章