--------------------------------------------------------------------------------------------
- --Lerning Content :分区表增量统计信息收集
- --Author :600团队
- --TimeStamp :2018-07-07
- --Description :How to Delete Unwanted Incremental Partition Statistics Synopsis Information From WRI$_OPTSTAT_SYNOPSIS$
in the SYSAUX Tablespace (Doc ID 1953961.1)
- --Remark :incremental特性 、synopses特性
--------------------------------------------------------------------------------------------
1.增量统计信息收集简单概述
1.1 Oracle11g推出了增量统计信息收集新特性，该特性缺省是禁用的，可以设置incremental来启动增量统计信息收集
可以在表层面设置，数据库级别设置增量统计信息收集方式，具体的工作原理为：在处理分区表时候cbo使用全局统计信息和
单独的一个分区来统计信息，选择最优的执行计划路径，默认情况根据分区数据的变化，oracle使用两次传递扫描技术，这种
技术官方称谓tow-pass scanning technique来维护分区表统计信息，具体的执行方式如下：
1.1.1扫描整个分区表收集第一次传递期间的全局统计信息
1.1.2在第二次传递扫描修改过的分区，收集分区统计信息
1.1.3一般在夜间跑批程序执行处理批作业情况都会扫描整个表来收集表全局统计信息，一般为扫描发生变化的分区，
同时扫描其他所有的分区，这里有个比较严重的性能问题，即使是发生变化的分区完成一次全表扫描的代价也是
比较昂贵的，尤其单个分区几百万数据量级别且多个子分区情况。
1.2 oracle通过synopses机制可以提高大表统计信息收集的效率问题，基本的工作原理遵循
没有变化的分区不进行统计信息收集的原则，基于这种机制，11g推出了synopses功能来辅助完成分区表global主分区以及
各个子分区的统计信息计算。具体的工作原理如下：
1.2.1为进行数据加载的分区收集统计信息并为该分区创建synopses
1.2.2通过合并所有分区synopses创建global synopses
1.2.3通过分区的synopses和global synopses计算出全局统计信息
1.3 synopses信息的增长占用sysaux表空间，DBA日常运维过程中需要关注sysaux表空间使用情况，定期清理synopses
统计信息数据，以保证数据库稳定运行。
-------------------------------------------------------------------------------------------------------
2.以下针对增量统计信息，以及如何删除统计信息，收缩sysaux表空间做如下案例演示：
2.1创建测试表
DROP TABLE T_600_LIST CASCADE CONSTRAINTS PURGE;
/
2.2创建测试表
CREATE TABLE T_600_LIST
PARTITION BY LIST(COL)
(
PARTITION P_COL_1 VALUES(1) TABLESPACE TBS_ORACLE,
PARTITION P_COL_2 VALUES(2) TABLESPACE TBS_ORACLE
)
AS
SELECT T.*, MOD(ROWNUM, 2) + 1 "COL", (MOD(ROWNUM, 2) + 1) * 5 "DEMO_STATUS"
FROM DBA_OBJECTS T
/
2.3开始设置增量统计信息策略
BEGIN
DBMS_STATS.SET_TABLE_PREFS('SYS', 'T_600_LIST', 'INCREMENTAL', 'TRUE');
DBMS_STATS.SET_TABLE_PREFS('SYS', 'T_600_LIST', 'GRANULARITY', 'AUTO');
DBMS_STATS.SET_TABLE_PREFS('SYS', 'T_600_LIST', 'PUBLISH', 'TRUE');
DBMS_STATS.SET_TABLE_PREFS('SYS',
'T_600_LIST',
'ESTIMATE_PERCENT',
'DBMS_STATS.AUTO_SAMPLE_SIZE');
DBMS_STATS.GATHER_TABLE_STATS('SYS',
'T_600_LIST',
CASCADE => TRUE,<--非必要参数
NO_INVALIDATE => FALSE,<--非必要参数
GRANULARITY => 'ALL');<--非必要参数
END;
注：
1.GRANULARITY的具体含义：
这个参数确定了数据库如何处理分区表统计信息收集，主要有如下几种方式：
'ALL'->收集子分区、分区和全局统计信息，这个设置将会提供非常准确的统计信息，但也很耗时，比使用其他选项
收集作业花费的时间更长。
|
'GLOBAL'->只收集全局统计信息
|
'PARTITION'->只收集分区统计信息
|
'GLOBAL AND PARTITION'->收集全局统计信息和分区统计信息，但不包括子分区的统计信息
|
'SUBPARTITION'->只收集子分区统计信息
|
'AUTO'->这是GRANLARITY参数的缺省设置，根据分区类型来确定收集统计信息的粒度
|
'ALL'->顾名思义全部收集，无论全局 局部还是子分区，但ALL会花费很长时间，文档中我们只是说明案例演示，那么
综合考虑收集子分区统计信息有时候并不是十分必要，在大多数情况下这个参数设置为AUTO即可，如果一定需要ALL条
件那么我们需要确定子分区信息是否真的会影响cbo评估，比如谓词条件中声明了子分区的字段，否则是不会被cbo使用的
所以最佳实践方法就是incremental增量统计信息收集方式
2.PUBLISH的具体含义：
缺省情况下，oracle会在完成统计信息收集后立刻发布消息，那么这个publish就是消息推送的过程，我们也可以设置
为false，这就表示oracle将新的统计信息保留为待定模式
3.INCREMENTAL的具体含义：
incremental参数决定oracle是否能够不进行全表扫描来维护一张分区表统计信息，这个参数缺省是false，表示oracle
需要进行全表扫描来维护全局统计信息
4.NO_INVALIDATE的具体含义：
这个参数可以设置为三个不同的值，true表示对oracle收集统计信息之后的表不会让它从属的游标失效，那么false
则相反，表示立即让从属游标失效，此外，还可以将这个参数设置为dbms_stats.auto_invalidate，让oracle自己去
决定是否让从属游标失效，这个auto也是缺省的设置。
--------------------------------------------------------------------------------------------
3.查询分区情况
WITH XS AS
(SELECT X.*,
TO_DATE(SUBSTR(HIGH_VALUE, 11, 19), 'YYYY-MM-DD HH24:MI:SS') DATES,
REPLACE(HIGH_VALUE, 'TIMESTAMP''') DATESTR
FROM XMLTABLE('/ROWSET/ROW' PASSING (SELECT DBMS_XMLGEN.GETXMLTYPE('
select t.table_owner, t.table_name, t.partition_name, t.high_value
from dba_tab_partitions t')
FROM DUAL) COLUMNS TABLE_OWNER VARCHAR2(30) PATH
'TABLE_OWNER',
TABLE_NAME VARCHAR2(30) PATH 'TABLE_NAME',
PARTITION_NAME VARCHAR2(30) PATH 'PARTITION_NAME',
HIGH_VALUE VARCHAR2(30) PATH 'HIGH_VALUE') X)
SELECT XS.DATESTR, P.*
FROM XS, DBA_TAB_PARTITIONS P
WHERE XS.TABLE_OWNER = 'SYS'
AND XS.TABLE_NAME = 'T_600_LIST'
AND P.TABLE_OWNER = XS.TABLE_OWNER
AND P.TABLE_NAME = XS.TABLE_NAME
AND P.PARTITION_NAME = XS.PARTITION_NAME
ORDER BY P.TABLE_OWNER, P.TABLE_NAME, P.PARTITION_POSITION;
注：内容太多，自行试验
4.检查SYNOPSIS信息内容，通过sys数据字典基表
SELECT TP.BO#, DO.OBJECT_ID, DO.DATA_OBJECT_ID, OBJECT_TYPE, OBJECT_NAME
FROM SYS.TABPART$ TP, DBA_OBJECTS DO
WHERE TP.BO# IN (SELECT DISTINCT (BO#) FROM SYS.WRI$_OPTSTAT_SYNOPSIS$)
AND TP.OBJ# = DO.OBJECT_ID
AND TP.DATAOBJ# = DO.DATA_OBJECT_ID;
BO#OBJECT_IDDATA_OBJECT_IDOBJECT_TYPEOBJECT_NAME
1975759757797577TABLE PARTITIONT_600_LIST
2975759757697576TABLE PARTITIONT_600_LIST
5.查看对应sysaux表空间WRI$数据字典基表数据量
SELECT COUNT(*) FROM WRI$_OPTSTAT_SYNOPSIS_HEAD$ WHERE BO# = '97575'
COUNT(*)
40
6.查看对应sysaux表空间WRI$数据字典基表数据量
SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_SYNOPSIS$ WHERE BO# = '97575'
COUNT(*)
65717
7.尝试去删除统计信息：
BEGIN
DBMS_STATS.PURGE_STATS(SYSDATE);
END;
8.尝试删除以后发现数据字典基表并没有发生任何变化：
SELECT COUNT(*)
FROM WRI$_OPTSTAT_SYNOPSIS_HEAD$
WHERE BO# = '97575'
UNION ALL
SELECT COUNT(*)
FROM SYS.WRI$_OPTSTAT_SYNOPSIS$
WHERE BO# = '97575'
COUNT(*)
140
265717
注意：DBMS_STATS.PURGE_STATS是不会删除统计信息的，需要满足如下条件
当对应的分区表取消增量搜集的时候会被删除。
当对应的分区变为stale，这是该分区对应的数据会被删除而从新收集
------------------------------------------------------------------------------------------------------------
9.当分区表数据量很大时需要用如下方式删除，以保证sysaux表空间充足：
4.1truncate table wri$_optstat_synopsis$;
4.2truncate table wri$_optstat_synopsis_head$
4.3alter index i_wri$_optstat_synopsis rebuild
4.4alter index i_wri$_optstat_synophead rebuild
10.重跑utlrp.sql 脚本，编译失效对象，或者如下方式也可以：
BEGIN
DBMS_UTILITY.compile_schema('SCOTT'); --重新编译用户下所有的程序
END ;
BEGIN
SYS.UTL_RECOMP.recomp_parallel(threads =>8 ,schema =>'SCOTT' );--重新编译用户下所有的程序
END ;
--------------------------------------------------------------------------------------------
作者：600团队
QQ：497631424
