文章目录
##概述
本文主要介绍数据库自动维护任务中的【自动统计信息收集（Automatic Optimizer Statistics Collection）】和【自动段指导Automatic Segment Advisor】维护任务的内容进行详细介绍。
##自动统计信息收集（Automatic Optimizer Statistics Collection）：
###10g
从Oracle 10g版本开始，在默认设定的情况下，Oracle会通过自动维护作业，自动地收集优化器统计信息（Optimizer Statistics）。
####自动统计信息收集相关设定
10g的自动统计信息收集主要和以下的3个设定相关：
1.调度作业 GATHER_STATS_JOB
（默认：有效'SCHEDULED'）
如前一章介绍，10g数据库做成时会自动地做成调度作业【GATHER_STATS_JOB】，
通过预定义的维护窗口（Maintenance Windows）进行执行。
WEEKNIGHT_WINDOW : 周一到周五 每天22:00开始 ～ 次日06:00结束。
WEEKEND_WINDOW :周六和周日的全天
2.数据库对象的MONITORING属性
（默认：有效 statistics_level=TYPICAL）
从Oracle 8i开始，Oracle针对数据库对象推出了MONITORING属性，
通过这个属性可以监视表（及其分区）的DML更新操作
（如insert,update,delete,direct load）并记录更新数。
10g以后MONITORING属性主要依赖于初始化参数statistics_level的设定，
当初始化参数statistics_level为默认值（TYPICAL）或ALL时，
会针对一时表以外的所有表启用MONITORING属性，监视DML更新操作并记录更新数。
3.统计信息的Lock状况
（默认：统计信息不锁定）
如果对象的统计信息上进行了锁定（lock Statistics），统计信息不会被更新。
所以，统计信息上进行了锁定的对象不会作为自动统计信息收集的对象。
关于统计信息的锁定可以参考DBMS_STATS包的LOCK_SCHEMA_STATS、LOCK_TABLE_STATS等程序。
####自动统计信息收集基本流程
自动统计信息收集基本流程如下：
1.当调度作业【GATHER_STATS_JOB】有效时，
会在在预定义的维护窗口执行DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC程序进行统计信息收集。
2.统计信息收集的对象为满足以下条件的表：
1）统计信息不锁定
2）没有统计信息 或者
上一次统计信息收集后，表中10%的行被更新。
3.统计信息收集的顺序如下：
1）没有统计信息的对象
2）上一次统计信息收集后变更量较多的对象
3）1）2）相同的，先收集大小比较小的对象
####自动统计信息收集的动作确认
自动统计信息收集的相关动作可以通过以下视图进行查看：
1.GATHER_STATS_JOB 的状态和设定内容
SQL> set linesize 200
SQL> col job_name format a20
SQL> col program_name format a20
SQL> col schedule_name format a25
SQL> col state format a20
SQL>
SQL> SELECT job_name,
program_name,
schedule_name,
state,
stop_on_window_close
FROM dba_scheduler_jobs
WHERE job_name = 'GATHER_STATS_JOB'; 2 3 4 5 6 7
JOB_NAME PROGRAM_NAME SCHEDULE_NAME STATE STOP_ON_WINDOW_
-------------------- -------------------- ------------------------- -------------------- ---------------
GATHER_STATS_JOB GATHER_STATS_PROG MAINTENANCE_WINDOW_GROUP SCHEDULED TRUE
从上面的输出我们可以看到，GATHER_STATS_JOB作业运行在MAINTENANCE_WINDOW_GROUP窗口组中，并且状态为‘SCHEDULED’即有效状态。
并且其中STOP_ON_WINDOW_CLOSE为True代表当窗口关闭时，作业会被终止。即，当超过窗口的持续时间时，即使统计信息收集作业没有完成，也会被取消。
2.运行窗口内容
--窗口组信息
SQL> SELECT *
FROM dba_scheduler_wingroup_members
WHERE window_group_name = 'MAINTENANCE_WINDOW_GROUP'; 2 3
WINDOW_GROUP_NAME WINDOW_NAME
------------------------- --------------------
MAINTENANCE_WINDOW_GROUP WEEKNIGHT_WINDOW
MAINTENANCE_WINDOW_GROUP WEEKEND_WINDOW
--窗口信息
SQL> col repeat_interval format a60
SQL> col duration format a20
SQL> SELECT window_name,
repeat_interval,
duration
FROM dba_scheduler_windows
WHERE window_name IN ( 'WEEKNIGHT_WINDOW', 'WEEKEND_WINDOW' ); 2 3 4 5
WINDOW_NAME REPEAT_INTERVAL DURATION
-------------------- ------------------------------------------------------------ --------------------
WEEKNIGHT_WINDOW freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; b +000 08:00:00
ysecond=0
WEEKEND_WINDOW freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0 +002 00:00:00
通过上面我们可以看到，窗口的运行时间为如下
WEEKNIGHT_WINDOW : 周一到周五 每天22:00开始 ～ 次日06:00结束。
WEEKEND_WINDOW :周六0点开始，并且持续2天（到周日的23:59）。
其中，由于周五的窗口持续到周六06:00结束；由于Oracle一次只能打开一个窗口，
并且WEEKNIGHT_WINDOW和WEEKEND_WINDOW的优先度相同，所以WEEKEND_WINDOW的实际开始时间为周六06:00开始。
3.执行的程序内容
可以通过dba_scheduler_programs视图查询到GATHER_STATS_PROG作业实际运行的程序。
SQL> col program_action format a50
SQL> SELECT program_action
FROM dba_scheduler_programs
WHERE program_name = 'GATHER_STATS_PROG'; 2 3
PROGRAM_ACTION
--------------------------------------------------
dbms_stats.gather_database_stats_job_proc
####自动统计信息收集的注意事项
1.通常情况下，gather_database_stats_job_proc程序执行时，系统字典表也作为统计信息收集对象。
2.对于数据变化很大的表，建议通过锁定统计信息等方法，来避免作为自动统计信息收集的对象。
另外，针对一时表统计信息收集后也不会产生有效的信息，所以建议针对一时表采取动态统计的方法。
例如：
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('SCOTT', 'EMP');
3.可能由于统计信息收集时，可能会消耗大量的I/O、CPU等资源，
所以可以根据业务内容调整自动统计信息收集的时间。
参考：
Database Performance Tuning Guide
14.2 Automatic Statistics Gathering
http://docs.oracle.com/cd/B19306_01/server.102/b14211/stats.htm#i41282
###11g & 12c
从11g开始，导入了周一到周日的日次窗口，并且定义了自动维护任务功能。
10g中的GATHER_STATS_JOB作业不再存在，而被变更为自动维护任务的一部分，所以JOB作业的状态不在通过 DBA_SCHEDULER_JOBS视图确认，而是通过DBA_AUTOTASK_CLIENT 视图来查看。
而且对于自动维护任务的维护等会通过DBMS_AUTO_TASK_ADMIN程序包进行维护。
其他的动作基本和10g相同。
例如：
--查看自动统计收集任务。
SQL> col client_name format a20
SQL> col window_group format a20
SQL> SELECT client_name,
status,
window_group
FROM dba_autotask_client
WHERE client_name = 'auto optimizer stats collection'; 2 3 4 5
CLIENT_NAME STATUS WINDOW_GROUP
-------------------- -------- --------------------
auto optimizer stats ENABLED ORA$AT_WGRP_OS
collection
--禁用自动统计收集任务。
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
参考：
Database SQL Tuning Guide
>Configuring Automatic Optimizer Statistics Collection
##自动段指导（Automatic Segment Advisor） ：
###10g
####段指导（Segment Advisor）
从Oracle 10g R2开始推出了段指导（Segment Advisor）功能，用于识别是否有可用回收空间的段和有很多行链接的对象，并提出如何消除这些段中的碎片及行链接的建议。
段指导（Segment Advisor）主要生成以下类型的建议：
・当段指导（Segment Advisor）发现某对象的高水位线 (High Water Mark 简称HWM)下的空余空间特别多时，
会推荐online segment shrink；
如果该对象并不适合Shrink（如表不是定义在ASSM的表领域等），段指导会推荐在线重定义对象（online table redefinition）.
例如：利用DBMS_REDEFINITION程序包。
・当段指导（Segment Advisor）发现某对象的行链接数大于某个临界值时，会记录下来作为通知内容。
####段指导建议的生成级别
通过手动执行段指导（Segment Advisor），可以生成以下3种级别的指导建议：
（利用DBMS_ADVISOR.CREATE_OBJECT时，指定的OBJECT_TYPE）
1.段级别:
针对非分区表、分区表的分区以及子分区、索引等个别段对象生成指导建议。
2.对象级别：
针对表或索引等整个对象生成指导建议。
例如对某分区表进行分析，会对分区表的所有分区生成知道建议。
另外通过EM运行还可以指定对象的所有的依赖对象（如索引等）。
3.表空间级别
针对某表空间所有的段对象生成指导建议。
####自动段指导（Automatic Segment Advisor）
自动段指导（Automatic Segment Advisor）功能是，在数据库做成时会自动地做成调度作业【GATHER_STATS_JOB】，通过预定义的维护窗口（Maintenance Windows）进行执行。
WEEKNIGHT_WINDOW : 周一到周五 每天22:00开始 ～ 次日06:00结束。
WEEKEND_WINDOW :周六和周日的全天
####自动段指导的分析对象
自动段指导并不是针对所有的数据库对象进行分析，而是分析数据库的统计信息、段数据的采样，对以下的对象进行分析：
1.超过空间的 critical 或warning阈值的表空间。
2.操作活动很多的段
3.很高增长率的段
####自动段指导的动作确认
和自动统计信息收集一样，自动段指导的相关动作可以通过视图进行查看。
这里不做详细介绍，可参考【自动统计信息收集的动作确认】一节。
####手动执行段指导
以下是针对段级别进行手动执行段指导的例子.
variable id number;
begin
declare
name varchar2(100);
descr varchar2(500);
obj_id number;
begin
name:='Manual_Employees';
descr:='Segment Advisor Example';
dbms_advisor.create_task (
advisor_name => 'Segment Advisor',
task_id => :id,
task_name => name,
task_desc => descr);
dbms_advisor.create_object (
task_name => name,
object_type => 'TABLE',
attr1 => 'HR',
attr2 => 'EMPLOYEES',
attr3 => NULL,
attr4 => NULL,
attr5 => NULL,
object_id => obj_id);
dbms_advisor.set_task_parameter(
task_name => name,
parameter => 'recommend_all',
value => 'TRUE');
dbms_advisor.execute_task(name);
end;
end;
/
####段指导的结果表示
可以通过以下方式查看段指导的结果：
Enterprise Manager
DBA_ADVISOR_* 视图
DBMS_SPACE.ASA_RECOMMENDATIONS 程序包
下面简单列举通过DBMS_SPACE.ASA_RECOMMENDATIONS 程序包查看段指导的结果的方法：
SQL> desc dbms_space
FUNCTION ASA_RECOMMENDATIONS RETURNS DBMS_SPACE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ALL_RUNS VARCHAR2 IN DEFAULT
SHOW_MANUAL VARCHAR2 IN DEFAULT
SHOW_FINDINGS VARCHAR2 IN DEFAULT
SQL> select tablespace_name, segment_name, segment_type,
recommendations, c1
from table(dbms_space.asa_recommendations('TRUE', 'TRUE', 'FALSE'));
参考：
Database Administrator’s Guide
>Automatic Segment Advisor Job
>Using the Segment Advisor
###11g & 12c
####自动段指导（Automatic Segment Advisor）建议的拓展
从Oracle 11g R2开始，作为自动段指导（Automatic Segment Advisor）的一部分，追加了段压缩指导(Compression advisor)功能。
因此，11gR2以后，自动段指导（Automatic Segment Advisor）功能主要可以生成以下类型的建议：
・当段指导（Segment Advisor）发现某对象的高水位线 (High Water Mark 简称HWM)下的空余空间特别多时，会推荐online segment shrink；
如果该对象并不适合Shrink（如表不是定义在ASSM的表领域等），段指导会推荐在线重定义对象（online table redefinition）.
例如：利用DBMS_REDEFINITION程序包。
・当段指导（Segment Advisor）发现某对象的行链接数大于某个临界值时，会记录下来作为通知内容。
・当自动段指导（Segment Advisor）
发现段可以从高级行压缩(Advanced row compression 11g版本也称为OLTP压缩)中受益时，
段指导会生成建议指导。（11gR2以后）
####段压缩指导(Compression advisor)
通过段压缩指导(Compression advisor)功能，可以评估通过高级行压缩(Advanced row compression以前的11g版本也称为OLTP压缩)方法对段进行压缩时，能够节省的空间大小。
段压缩指导评估对象：
段压缩指导评估对象一般为大小为10MB以上，并且拥有3个以上索引的表。
(除了上面的条件以外，还会根据其他一些内部算法来决定评估对象)
段压缩指导处理逻辑：
・段压缩指导主要使用DBMS_COMPRESSION程序包进行压缩的评估。
・评估过程中，会建立以下2个内部临时表。
DBMS_TABCOMP_TEMP_UNCMP ：默认包含99%的采样块。
DBMS_TABCOMP_TEMP_CMP ：包含通过压缩后的DBMS_TABCOMP_TEMP_UNCMP。
通过DBMS_TABCOMP_TEMP_UNCMP和 DBMS_TABCOMP_TEMP_CMP计算出压缩比。
・评估结束后，删除2个临时表。
手动运行段压缩指导
可以通过以下的方法手动地运行段压缩指导。
例：
sql> set serveroutput on
sql> declare
v_blkcnt_cmp pls_integer;
v_blkcnt_uncmp pls_integer;
v_row_cmp pls_integer;
v_row_uncmp pls_integer;
v_cmp_ratio number;
v_comptype_str varchar2(60);
begin
dbms_compression.get_compression_ratio(
scratchtbsname => upper('&ScratchTBS'),
ownname => user,
tabname => upper('&TableName'),
partname => NULL,
comptype => dbms_compression.comp_for_query_high,
blkcnt_cmp => v_blkcnt_cmp,
blkcnt_uncmp => v_blkcnt_uncmp,
row_cmp => v_row_cmp,
row_uncmp => v_row_uncmp,
cmp_ratio => v_cmp_ratio,
comptype_str => v_comptype_str, subset_numrows=>&num_rows );
dbms_output.put_line('Estimated Compression Ratio: '||to_char(v_cmp_ratio));
dbms_output.put_line('Blocks used by compressed sample: '||to_char(v_blkcnt_cmp));
dbms_output.put_line('Blocks used by uncompressed sample: '||to_char(v_blkcnt_uncmp));
end;
/
参考：
Database Administrator’s Guide
>Automatic Segment Advisor
>Using the Segment Advisor
##其他
对于【自动SQL优化指导（Automatic SQL Tuning Advisor）】和【SQL计划管理（SPM）进化指导（SPM Evolve Advisor）】的相关内容，将在以后的文章进行介绍。
版权声明：本文为博主原创文章，转载必须注明出处，本人保留一切相关权力！http://blog.csdn.net/lukeunique
欢迎关注微信订阅号：TeacherWhat
扫一扫
2947
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
