Oracle 对象信息收集

mrbear

对象信息收集机制
　　一种通过ANALYZE命令，一种通过DBMS_STATS报中的GATHER_*方法获得，这两种是手动方式。从10G开始，这个工作是GATHER_STATS_JOB自动完成。
　　手动：
　　SQL>ANALYZE TABLE TABLE1 VALIDATE STRUCTURE CASCADE;
　　SQL>ANALYZE INDEX INDEX1 COMPUTE STATISTICS;
　　查看自动信息：
　　ORACLE 10G中：
　　SQL>EXEC DBMS_SCHEDULER.ENABLE('SYS.GATHER_STATS_JOB');--启动自动收集
　　SQL>EXEC DBMS_SCHEDULER.DISABLE('SYS.GATHER_STATS_JOB');--关闭自动收集
　　SQL>SELECT JOB_NAME, STATE, COMMENTS FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME LIKE 'GATHER%';
　　如果有GATHER_STATS_JOB，那就是已经开启。
　　ORACLE 11G中：
　　ORACLE 11G中GATHER_STATS_JOB被BSLN_MAINTAIN_STATS_JOB替换了。
　　查询状态：
　　SQL> SELECT CLIENT_NAME, STATUS FROM DBA_AUTOTASK_CLIENT;
　　如果auto optimizer stats collection，auto space advisor，sql tuning advisor都ENABLED了就说明开启了。
　　TABLE MONITORING机制：
　　ORACLE 10G对对象收集优化：只针对那些发生了重大变化的，原有统计信息已经陈旧的对象才重新收集统计数据。
　　监控原理：
　　ORACLE会监控对象上发生的更改，包括INSERT、UPDATE、DELETE、TRUNCATE的数据量。如果改变的数据量超过记录总数的10%，则重新收集统计信息。监控收到参数STATISTICS_LEVEL控制。可以用如下语句查看：
　　SQL>SELECT STATISTICS_NAME, SYSTEM_STATUS, ACTIVATION_LEVEL, STATISTICS_VIEW_NAME FROM V$STATISTICS_LEVEL;
　　其中STATISTICS_LEVEL参数值被设置成TYPICAL或者ALL，就会启动表监控。
　　监控信息从USER_TAB_MODIFICATIONS视图中查看。
　　SQL> SELECT TABLE_NAME, PARTITION_NAME, INSERTS, UPDATES, DELETES, TRUNCATED, DROP_SEGMENTS, TABLE_OWNER FROM DBA_TAB_MODIFICATIONS WHERE TABLE_OWNER='SCOTT';
　　收集到的这些信息不是立即更新到系统字典中去，会拖延一段。
　　也可以手动使用DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO方法直接手动同步。
　　除了可以跟踪DML，还会跟踪每个数据列在查询中的使用情况，从COL_USAGE$中查看。
　　SQL>SELECT * FROM COL_USAGE$;
　　其中LIKE_PREDS表示在LIKE条件中的使用次数。这些会被ADDM作为SQL Access Advisor的依据。
动态收集对象信息
　　要想使用动态数据收集，需要把对象的统计信息设置成NULL。当ORACLE遇到一个统计信息是NULL的对象会动态收集其统计信息。就是通过抽取几个数据块作为样本来推算整个对象的统计信息。
　　初始化参数OPTIMIZER_DYNAMIC_SAMPLING，这个参数是采样提供数据块数量，缺省是2。
　　0：不适用动态统计信息收集。
　　1：使用32块进行统计信息收集。
　　2~9：分别是2、4、8、16、32、64、128、256倍的32块进行收集。
　　10：所有数据块。
　　还可以用HINT强行让优化器动态收集统计信息。
　　SQL>SELECT * from table1;
统计信息历史
　　统计信息的变化历史可以从DBA_TAB_STATS_HISTORY视图中看到。它会记录过去31天来的统计信息被修改的历史。
　　SQL>SELECT * FROM DBA_TAB_STATS_HISTORY;
　　*注：ANALYZE命令产生的数据不会保存历史信息。
　　查看保留策略：
　　SQL>SELECT DBMS_STATS.GET_STATS_HISTORY_RETENTION FROM DUAL;
　　修改保留策略：
　　SQL>EXECUTE EBMS_STATS.ALTER_STATS_HISTORY_RETENTION(45);
　　统计的信息历史也受到STATISTICS_LEVEL的约束。如果是BASIC，则历史数据不会被自动清理。需要用DBMS_STATS.PURGE_STATS来手动清理。而且BASIC的时候也不会自动收集任何统计数据了。
　　查看实际可用最早日期：
　　SQL>SELECT DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY FROM DUAL;
　　生产中有可能使用恢复历史统计信息，使用
　　SQL>EXEC DBMS_STATS.RESTORE_TABLE_STATS(OWNERNAME=>’ZXXX’,TABNAME=>’TABLE111’,AS_OF_TIMESTAMP=>’12-MAY-12 10.00.00.000000 AM+12:00’);
　　来恢复，具体里面的参数需要参考：
　　SQL>SELECT * FROM DBA_TAB_STATS_HISTORY WHERE TABLE_NAME=’TABLE111’;
　　来获得
操作系统统计数据收集
　　CBO公式
　　Cost=IO+CPU÷1000+NetIO×1.5
　　IO代表物理IO操作量，
　　CPU代表逻辑IO操作量，
　　NetIO代表通过数据库连接的网络IO操作量。
　　Single_Block Read每次只会读取一个数据块，经过Buffer Cache，需要用OPTIMIZER_INDEX_CACHING来调整（默认为0）。Multi-Blocks Read会读取多个块，不经过Bufer Cache。
　　OPTIMZIER_INDEX_CACHING表示索引块被缓存在Buffer Cache中的比率，如果是0，就说明不缓存。如果是80，就表明80%的读取都是逻辑读，不需要物理读。
　　加入此因素，
　　COST=(总读取×(1-(OPTIMIZER_INDEX_CACHING÷100)))
　　×(OPTIMIZER_INDEX_COST_ADJ÷100)
　　OPTIMIZER_INDEX_COST_ADJ默认为100。
　　用下面这个SQL计算一下“连续读平均等待×100”与“分散读平均等待”的比值：
　　SQL>SELECT TRUNC(A.AVERAGE_WAIT*100/B.AVERAGE_WAIT) FROM
　　V$SYSTEM_EVENT A, V$SYSTEM_EVENT B
　　WHERE A.EVENT='db file sequential read' and
　　b.event='db file scattered read';
　　得到的结果，可以设置为OPTIMIZER_INDEX_COST_ADJ参数。
　　ORACLE把IO收集信息放在SYS.AUX_SATS$中，包含两类信息，SYSSTATS_INFO和SYSSTATS_MAIN。
　　SYSSTATS_INFO描述收集活动自身。
　　SYSSTATS_MAIN表述真正收集的数据。
　　操作系统统计数据分成两类：无负载模式NOWORKLOAD，和负载模式WORKLOAD。
　　无负载模式在数据库启动时就会收集。
　　负载模式需要手动收集。
　　两种界定根据自身业务需要，例如凌晨是无负载模式，工作时间是负载模式。