[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;