统计信息类似战争中的侦察兵，如果情报工作没有做好，打仗就会输掉。
同样的，如果没有正确地收集表的统计信息或者没有及时更新表的统计信息，SQL执行计划就会跑偏，SQL也就会出现性能问题。
收集统计信息是为了让优化器选择最佳的执行计划，以最少的代价查询出表的的数据。本章重点讨论表的统计信息，列的统计信息以及索引的统计信息。
查看表的统计信息
--查看上次表的收集时间
select t.TABLE_NAME,t.NUM_ROWS,t.BLOCKS,t.LAST_ANALYZED from user_tables t where table_name ='TEST';
--查看表的统计信息
select a.OWNER,a.TABLE_NAME,a.NUM_ROWS,a.BLOCKS,a.AVG_ROW_LEN from dba_tables a where a.OWNER='C##ZXC' and a.TABLE_NAME='T3';
--查看索引的统计信息
select a.blevel,a.LEAF_BLOCKS,a.CLUSTERING_FACTOR,a.status from dba_indexes a where a.OWNER='C##ZXC' and a.INDEX_NAME='';收集统计信息
我们通常使用以下脚本收集表和索引的统计信息。
--收集表的统计信息
BEGIN
DBMS_STATS.gather_table_stats(
ownname => 'TAB_OWNER',
tabname => 'TAB_NAME',
estimate_percent => '根据表大小设置',
method_opt => 'for all columns size repeat',
no_invalidate => FALSE,
degree => '根据表的大小，CPU资源和负载设置',
granularity => 'AUTO',
cascade => TRUE);
END
--单独收集索引信息
BEGIN
DBMS_STATS.gather_index_stats(
ownname => '';
indname => '');
END查看统计信息是否过期
收集完表的统计信息之后，如果表中有大量数据发生变化，这时表的统计信息就过期了，我们需要重新统计表的统计信息，如果不重新收集，可能会导致执行计划走偏。
当表中有超过百分之10的数据发生变化（INSERT,DELETE,UPDATE）就会引起统计信息过期。
--STALE_STATS为YES表示表的统计信息过期
select a.OWNER,a.TABLE_NAME,a.OBJECT_TYPE,a.STALE_STATS,a.LAST_ANALYZED from dba_tab_statistics a where a.OWNER='C##ZXC' and a.TABLE_NAME='TEST';
--查询统计信息过期的原因
select a.TABLE_OWNER,a.TABLE_NAME,a.inserts,a.updates,a.deletes,a.timestamp from all_tab_modifications a where a.TABLE_NAME='TEST' and a.TABLE_OWNER='C##ZXC';动态采样
如果一个表从来没有收集过统计信息，默认情况下Oracle会对表进行动态采样（level2）。以便优化器估算出较为准确的Rows，动态采样的最终目的就是为了让优化器能够评估出较为准确的Rows。
--第一种方法
alter session set optimizer_dynamic_sampling = 3;
--第二种方法
select /*+ dynamic_sampling(3) */ * from table_name;当执行计划中表的Rows有严重偏差的时候，如两表关联，多个过滤条件，或者group by之后导致Rows估算失误。
此时我们可以使用动态采样，同时level至少设置为level4。不要在系统级更改采用级别，默认为2就可以了，如果某个表需要启用动态采样，直接再SQL语句中添加HINT即可。
查看与修改收集统计信息的策略
在Oracle 11g中，默认有3个自动任务，分别是：自动统计信息收集、SQL调优顾问、段空间调整顾问，查看方法如下：
SQL> SELECT CLIENT_NAME,TASK_NAME,OPERATION_NAME,STATUS FROM dba_autotask_task;
CLIENT_NAME TASK_NAME OPERATION_NAME STATUS
-------------------------------- -------------------------- -------------------------- --------
sql tuning advisor AUTO_SQL_TUNING_PROG automatic sql tuning task ENABLED
auto optimizer stats collection gather_stats_prog auto optimizer stats job ENABLED
auto space advisor auto_space_advisor_prog auto space advisor job ENABLED
--禁用自动统计信息收集
SQL> EXEC dbms_auto_task_admin.disable(client_name=> 'auto optimizer stats collection',operation=> NULL,window_name=> NULL);
--启用自动统计信息收集
SQL> EXEC dbms_auto_task_admin.enable(client_name=> 'auto optimizer stats collection',operation=> NULL,window_name=> NULL);
SQL> SELECT PROGRAM_NAME,PROGRAM_TYPE,PROGRAM_ACTION FROM dba_scheduler_programs WHERE PROGRAM_NAME = 'GATHER_STATS_PROG';
PROGRAM_NAME PROGRAM_TYPE PROGRAM_ACTION
------------------------------ ---------------- --------------------------------------------------------------------------------
GATHER_STATS_PROG STORED_PROCEDURE dbms_stats.gather_database_stats_job_procauto optimizer stats collection表示自动统计信息收集，使用的任务为gather_stats_prog。gather_stats_prog调用了DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC存储过程。
它会检测哪些对象没有统计信息或者统计信息比较陈旧，将优先对这些对象进行分析。而我们平时手工用的dbms_stats.gather_database_stats，是没有分优先级的，也就是它将对数据里的所有对象进行分析，不管该对象有没有被分析过。GATHER_STATS_JOB任务一般是安排在晚上10pm-6am,周未全天启动。
--查看每天的收集统计信息策略
select t1.WINDOW_NAME '任务名字',t1.REPEAT_INTERVAL '任务重复间隔时间',t1.duration '持续时间'
from dba_scheduler_windows t1, dba_scheduler_wingroup_members t2
where t1.window_name = t2.window_name
and t2.window_group_name in
('MAINTENANCE_WINDOW_GROUP', 'BSLN_MAINTAIN_STATS_SCHED');
WINDOW_NAME REPEAT_INTERVAL DURATION
------------------ ------------------------------------------------------- -----------------
MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 04:00:00
TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00
WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00
THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00
FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00
SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00
SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00
可以看到，从周一到周五，窗口运行时间为晚上22点开始，最多运行4个小时，周六周日从早上6点开始，最多运行20个小时。
--如下示例，将周五时间窗口时间到晚间23点30分
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE (
name =>'"SYS"."FRIDAY_WINDOW"',
attribute => 'REPEAT_INTERVAL',
VALUE =>'FREQ=WEEKLY;BYDAY=FRI;BYHOUR=23;BYMINUTE=30;BYSECOND=0');
END;
--修改任务的持续时间，单位是分钟
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."FRIDAY_WINDOW"',
attribute=>'DURATION',
value=>numtodsinterval(480, 'minute'));
END;
--关闭统计信息的任务
BEGIN
DBMS_SCHEDULER.DISABLE(
name=>'"SYS"."FRIDAY_WINDOW"',
force=>TRUE);
END;
--开启统计信息的任务
BEGIN
DBMS_SCHEDULER.ENABLE(
name=>'"SYS"."FRIDAY_WINDOW"');
END;统计信息阈值修改
在Oracle 11g中，默认统计信息的收集阈值为10%，即10%的行数据发生变化或者执行了truncate，才会再次收集统计信息。我们可以使用下面的方法针对单个表修改阈值。
例子1：修改test01表的统计信息收集阈值为5%。
查看初始的阈值:
SQL> SELECT dbms_stats.get_prefs(pname => 'STALE_PERCENT',ownname => 'LIJIAMAN',tabname => 'TEST01') FROM dual;
DBMS_STATS.GET_PREFS(PNAME=>'S
--------------------------------------------------------------------------------
10修改阈值为5：
SQL> EXEC dbms_stats.set_table_prefs(ownname => 'LIJIAMAN',tabname => 'TEST01',pname => 'STALE_PERCENT',pvalue => 5); 确认修改后的阈值：
SQL> SELECT dbms_stats.get_prefs(pname => 'STALE_PERCENT',ownname => 'LIJIAMAN',tabname => 'TEST01') FROM dual;
DBMS_STATS.GET_PREFS(PNAME=>'S
--------------------------------------------------------------------------------
5需要注意的是：当阈值为0时，不管数据如何变化，每天都会自动收集统计信息。
统计信息自动收集流程
（小tips：了解有这么一回事即可）
1. Automatic Statistics Gathering是由Scheduler调度GATHER_STATS_JOB作业来完成的，在GATHER_STATS_JOB作业中则调用DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC存储过程。
2. 该作业在创建数据库的自动创建，并且设置为每天晚上10点到第二天早上6点和周六周日的全天为运行窗口期。在运行窗口期内，该作业都会运行，根据stop_on_window_close属性来决定，如在窗口期结束以后，该作业如果还没有运行完毕，下次收集继续运行。
3. GATHER_DATABASE_STATS_JOB_PROC是内部的存储过程，基本上跟DBMS_STATS.GATHER_DATABASE_STATS的功能一样，但是有内部的优先顺序考虑，更新越多的表将会越优先收集统计信息。
4. 收集统计信息的表对象是，之前从来没有收集过的或者是更新的（包括insert,update,delete,truncate）记录数超过当前总记录数10%的表。记录数的更改量由Oracle数据库自动监控，在初始化参数statistics_level设置为TYPICAL或者ALL时，自动监控即会生效。
5. 在USER_TAB_MODIFICATIONS表中记录了所有被监控的表的数据量更改信息。该信息的更新将会稍微滞后于真实的修改，默认情况是15分钟更新，可以通过DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO存储过程来立刻将更改的信息更新到USER_TAB_MODIFICATIONS表中。对于更新之后再rollback的记录，仍然算为已经受影响的记录，Oracle不会在rollback之后再去更新USER_TAB_MODIFICATIONS表。
评论