[20151212]審計統計分析.txt

--昨天看了一個帖子,連線如下：
http://www.itpub.net/thread-2048266-1-1.html

--對方在指令碼有如下語句：
   EXECUTE IMMEDIATE
         'begin dbms_stats.set_index_stats(ownname => '''
      || SOWNER
      || ''',indname => '''
      || SIDXNAME
      || ''',numrows => 100000000000,numlblks => 100000,numdist => 100000,avglblk => 100000,avgdblk => 100000,clstfct => 100000000000); end;';

--開始以為目的是趨向使用索引，仔細檢查不是。
--avglblk => 100000,avgdblk => 100000,clstfct => 100000000000,都設定的太大。

clstfct是設定索引的聚簇因子的。這個值越大，越不會使用索引。
numrows:索引中的記錄數。
numlblks:索引中葉子塊的數量。
numdist:索引中唯一值的數量。
avglblk: 索引中一個鍵值平均分佈在多少個葉子塊中。
avgdblk:索引中一個鍵值平均分佈在多少個表中的資料塊中。這個值也可以用clstfct/dumdist來獲取。

--有網友發現靈感來源這裡：http://blog.csdn.net/robinson1988/article/details/25126125

--很明顯維護人員沒有很好理解原作者的意思。

--從這裡看出給第3方維護一定要有文件，知道人家改了什麼，不然別人接手會遇到一些莫名其妙的問題。
--由此聯想到對於統計分析的審計也很重要。自己做一些測試：

1.環境：

SCOTT@book> @ &r/ver1

PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

BEGIN
   DBMS_STATS.set_index_stats
   (
      ownname    => USER
     ,indname    => 'PK_DEPT'
     ,numrows    => 100000000000
     ,numlblks   => 100000
     ,numdist    => 100000
     ,avglblk    => 100000
     ,avgdblk    => 100000
     ,clstfct    => 100000000000
   );
END;
/

select * from dba_indexes where owner=user and index_name='PK_DEPT';

OWNER:                    SCOTT
INDEX_NAME:               PK_DEPT
INDEX_TYPE:               NORMAL
TABLE_OWNER:              SCOTT
TABLE_NAME:               DEPT
TABLE_TYPE:               TABLE
UNIQUENESS:               UNIQUE
COMPRESSION:              DISABLED
PREFIX_LENGTH:
TABLESPACE_NAME:          USERS
INI_TRANS:                2
MAX_TRANS:                255
INITIAL_EXTENT:           65536
NEXT_EXTENT:              1048576
MIN_EXTENTS:              1
MAX_EXTENTS:              2147483645
PCT_INCREASE:
PCT_THRESHOLD:
INCLUDE_COLUMN:
FREELISTS:
FREELIST_GROUPS:
PCT_FREE:                 10
LOGGING:                  YES
BLEVEL:                   0
LEAF_BLOCKS:              100000
DISTINCT_KEYS:            100000
AVG_LEAF_BLOCKS_PER_KEY: 100000
AVG_DATA_BLOCKS_PER_KEY: 100000
CLUSTERING_FACTOR:        100000000000
STATUS:                   VALID
NUM_ROWS:                 100000000000
SAMPLE_SIZE:              4
LAST_ANALYZED:            2015/12/12 09:36:23
DEGREE:                   1
INSTANCES:                1
PARTITIONED:              NO
TEMPORARY:                N
GENERATED:                N
SECONDARY:                N
BUFFER_POOL:              DEFAULT
FLASH_CACHE:              DEFAULT
CELL_FLASH_CACHE:         DEFAULT
USER_STATS:               YES
DURATION:
PCT_DIRECT_ACCESS:
ITYP_OWNER:
ITYP_NAME:
PARAMETERS:
GLOBAL_STATS:             YES
DOMIDX_STATUS:
DOMIDX_OPSTATUS:
FUNCIDX_STATUS:
JOIN_INDEX:               NO
IOT_REDUNDANT_PKEY_ELIM: NO
DROPPED:                  NO
VISIBILITY:               VISIBLE
DOMIDX_MANAGEMENT:
SEGMENT_CREATED:          YES

--只要注意USER_STATS=YES,就是人為修改了統計資訊。
--如果人為的lock表：

BEGIN
SYS.DBMS_STATS.LOCK_TABLE_STATS (
OwnName => 'SCOTT'
,TabName => 'DEPT');
END;
/

select * from DBA_TAB_STATISTICS where owner=user and table_name='DEPT';

OWNER:                      SCOTT
TABLE_NAME:                 DEPT
PARTITION_NAME:
PARTITION_POSITION:
SUBPARTITION_NAME:
SUBPARTITION_POSITION:
OBJECT_TYPE:                TABLE
NUM_ROWS:                   4
BLOCKS:                     5
EMPTY_BLOCKS:               0
AVG_SPACE:                  0
CHAIN_CNT:                  0
AVG_ROW_LEN:                20
AVG_SPACE_FREELIST_BLOCKS: 0
NUM_FREELIST_BLOCKS:        0
AVG_CACHED_BLOCKS:
AVG_CACHE_HIT_RATIO:
SAMPLE_SIZE:                4
LAST_ANALYZED:              2015/12/12 09:35:50
GLOBAL_STATS:               YES
USER_STATS:                 NO
STATTYPE_LOCKED:            ALL
STALE_STATS:                NO

--STATTYPE_LOCKED= ALL表示人為lock鎖定。

2.有時候自己也會做一些統計資訊修改的測試，不過現在很少，特別11g以後，因為11g提供了更多的方法控制執行計劃。
但是審計有誰執行了統計分析，或者修改了什麼東西還是顯得很重要，因為並非每個dba在檢查sql語句時發現統計資訊
被人為篡改。

SCOTT@book> show parameter audit_trail
NAME TYPE VALUE
------------ -------- -------------
audit_trail string DB

--11g已經預設開啟審計，但是還不夠，加入
SCOTT@book> alter system set audit_trail=db,extended scope=spfile;
System altered.

--重啟資料庫，執行如下：1
SYS@book> audit execute on sys.dbms_stats;
Audit succeeded.

BEGIN sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 'dept',Estimate_Percent => NULL,Method_Opt => 'FOR
ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 24281
ORA-06512: at "SYS.DBMS_STATS", line 24332
ORA-06512: at line 1

--不行，因為lock統計資訊。加入force=>true.

BEGIN
   sys.DBMS_STATS.gather_table_stats
   (
      OwnName            => USER
     ,TabName            => 'dept'
     ,Estimate_Percent   => NULL
     ,Method_Opt         => 'FOR ALL COLUMNS SIZE 1 '
     ,Cascade            => TRUE
     ,No_Invalidate      => FALSE
     ,force              => TRUE
   );
END;
/

SELECT timestamp
        ,username
        ,userhost
        ,sql_text
    FROM dba_audit_object
   WHERE obj_name = 'DBMS_STATS' AND timestamp > TRUNC (SYSDATE)
ORDER BY timestamp;

TIMESTAMP           USERNAME             USERHOST             SQL_TEXT
------------------- -------------------- -------------------- ------------------------------------------------------------------------------------------------------------------------
2015-12-12 09:53:34 SCOTT                xxxxxxxx             BEGIN sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 'dept',Estimate_Percent => NULL,Method_Opt => 'FOR
                                                              ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false); END;

2015-12-12 09:55:45 SCOTT                xxxxxxxx             BEGIN
                                                                 sys.DBMS_STATS.gather_table_stats
                                                                 (
                                                                    OwnName            => USER
                                                                   ,TabName            => 'dept'
                                                                   ,Estimate_Percent   => NULL
                                                                   ,Method_Opt         => 'FOR ALL COLUMNS SIZE 1 '
                                                                   ,Cascade            => TRUE
                                                                   ,No_Invalidate      => FALSE
                                                                   ,force              => TRUE
                                                                 );
                                                              END;

BEGIN
SYS.DBMS_STATS.UNLOCK_TABLE_STATS (
OwnName => 'SCOTT'
,TabName => 'DEPT');
END;
/

TIMESTAMP           USERNAME             USERHOST             SQL_TEXT
------------------- -------------------- -------------------- ------------------------------------------------------------------------------------------------------------------------
2015-12-12 09:53:34 SCOTT                xxxxxxxx             BEGIN sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 'dept',Estimate_Percent => NULL,Method_Opt => 'FOR
                                                              ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false); END;

2015-12-12 09:55:45 SCOTT                xxxxxxxx             BEGIN
                                                                 sys.DBMS_STATS.gather_table_stats
                                                                 (
                                                                    OwnName            => USER
                                                                   ,TabName            => 'dept'
                                                                   ,Estimate_Percent   => NULL
                                                                   ,Method_Opt         => 'FOR ALL COLUMNS SIZE 1 '
                                                                   ,Cascade            => TRUE
                                                                   ,No_Invalidate      => FALSE
                                                                   ,force              => TRUE
                                                                 );
                                                              END;

2015-12-12 09:58:33 SCOTT                xxxxxxxx             BEGIN
                                                                SYS.DBMS_STATS.UNLOCK_TABLE_STATS (
                                                                    OwnName        => 'SCOTT'
                                                                   ,TabName        => 'DEPT');
                                                              END;

--但是如果使用:

SCOTT@book> Analyze Table DEPT Compute Statistics;
Table analyzed.

--這樣就沒有記錄。

SCOTT@book> SCOTT@book> select table_name,global_stats,user_stats,stattype_locked from DBA_TAB_STATISTICS where owner=user and table_name='DEPT';
TABLE_NAME GLO USE STATT
---------- --- --- -----
DEPT NO NO

--這樣分析的表global_stats='NO'.

BEGIN
SYS.DBMS_STATS.LOCK_TABLE_STATS (
OwnName => 'SCOTT'
,TabName => 'DEPT');
END;
/

SCOTT@book> select table_name,global_stats,user_stats,stattype_locked from DBA_TAB_STATISTICS where owner=user and table_name='DEPT';
TABLE_NAME GLO USE STATT
---------- --- --- -----
DEPT NO NO ALL

SCOTT@book> Analyze Table DEPT Compute Statistics;
Analyze Table DEPT Compute Statistics
*
ERROR at line 1:
ORA-38029: object statistics are locked

--lock以後analyze 也無法分析。

3.收尾取消審計：

SYS@book> noaudit execute on sys.dbms_stats;
Noaudit succeeded.

--另外這裡還記錄了什麼時候分析了表。好像儲存僅僅1個月。

select * from DBA_TAB_STATS_HISTORY;

[20151212]審計統計分析.txt

相關文章