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