第四章 系统和对象统计信息
4.1包dbms_stats简介
从9i开始，dbms_stats代替analyze，后者仅用于对象统计信息之外的用途，例如，index structure validate，行迁移的统计。
4.2系统统计信息
I/O开销模型(I/O cost model)：执行SQL语句所需的数据块读的多少
该方法的主要缺点是认为单块读和多块读开销相当，结果，优化器更多倾向于使用多块读操作，如全表扫描，直到8i，初始化参数optimizer_index_caching和optimizer_index_cost_adj解决了这个问题。但缺省值0和100，仅适用于OLAP环境，而不是常用的OLTP环境。
到了9i，产生了一种新的CPU开销模型（CPU cost model），它除了考虑I/O的多少之外，还考虑I/O子系统的性能。必须提供系统统计信息才可以使用CPU开销模型，系统统计信息包括：
I/O子系统的性能；
CPU的性能；
9i缺省没有系统统计信息；10G缺省就有(但缺省的值不一定是合适的)，除非SQL提示中指定no_cpu_costing，否则优化器都是使用CPU开销模型。或者使用隐含初始化参数：_optimizer_cost_model值为io时，指定使用I/O开销模型。
系统统计信息包括非工作量统计信息和工作量统计信息两种，前者是人工基准测试（自动模拟工作负载），后者使用应用程序基准测试（以实际的工作负载为准）。系统统计信息存放在aux_stats$表中。通过执行dbms_stats.gather_system_stats来进行收集。
一个数据库只有一套该信息，RAC系统所有实例使用同一个系统统计。
系统统计信息的状态和时间：
select pname,pval2 from aux_stats$ where sname='SYSSTATS_INFO'
PNAME PVAL2
DSTART 10-24-2009 14:15
DSTOP 10-24-2009 14:35
FLAGS
STATUS COMPLETED
STATUS 值为Badstats表示收集过程有错，这种情况下优化器不会使用这样的统计信息。
系统统计信息的结果：
select pname,pval1 from aux_stats$ where sname='SYSSTATS_MAIN'
PNAME PVAL1
CPUSPEED 1265 MHZ
CPUSPEEDNW 484.974958263773 每个CPU每秒钟处理的操作数（百万次）
IOSEEKTIM 10 平均磁盘寻道时间，缺省为10毫秒，本机为12.06
IOTFRSPEED 4096 平均每毫秒磁盘传输的字节，缺省为4096，实际上远不止这个数，本机7200转的笔记本硬盘，值为41248
MAXTHR
MBRC
MREADTIM
SLAVETHR
SREADTIM 4.423
非工作量统计信息的收集（注意，可能有时需要执行多次才生效）：
exec dbms_stats.gather_system_stats(gathering_mode => 'noworkload');
由于使用人工基准测试产生负载来衡量系统性能，所以应在相对空闲的时间执行，约1分钟内完成。
10G开始，非工作量统计信息是不能删除的，即使删除，数据库下次启动时会自动收集。
9i上，即使收集了，也不在数据字典aux_stats$中存储，只是显示状态为noworkload;
工作量统计信息的收集，要利用正常业务的工作负载来评估I/O性能，必须显示的收集后才有统计数据，才可用。它分为三个步骤:
1. 执行快照，并存储初始值到aux_stats$中（sname为sysstats_temp）
exec dbms_stats.gather_system_stats(gathering_mode => 'start');
2. 等待有代表性的业务运行，建议至少30分钟
select count(产地) from yhis.药品收发记录 --多块读
select * from yhis.病人信息 where 病人id=110 --单块读
3. 第二次快照
手工停exec dbms_stats.gather_system_stats(gathering_mode => 'stop');
自动停exec dbms_stats.gather_system_stats(gathering_mode => 'interval',interval => 30);
4. 根据两次快照的差值，产生系统统计信息。
下面的笔记本上的统计信息
|
PNAME |
PVAL1 |
|
|
CPUSPEED |
1392 |
单位MHZ, 只是一个基准线操作的内部校准 |
|
CPUSPEEDNW |
781.577 |
每个CPU每秒钟处理的操作数（百万次） |
|
IOSEEKTIM |
12.06 |
|
|
IOTFRSPEED |
41248 |
|
|
MAXTHR |
|
系统最大IO吞吐量（字节/秒） |
|
MBRC |
14 |
一次多块读，平均读取块数 |
|
MREADTIM |
8.656 |
多块数据平均读取时间，毫秒 |
|
SLAVETHR |
|
并行处理从属线程的平均IO吞吐量（字节/秒） |
|
SREADTIM |
4.421 |
单块数据平均读取时间，毫秒 |
下面是某三甲医院的统计信息
|
CPUSPEEDNW |
1107.385 |
|
IOSEEKTIM |
3.457 |
|
IOTFRSPEED |
26413.414 |
|
SREADTIM |
1.082 |
|
MREADTIM |
0.557 |
|
CPUSPEED |
1119 |
|
MBRC |
15 |
|
MAXTHR |
444416 |
|
SLAVETHR |
|
为了收集到有代表性的统计信息，可连续多天收集后取平均值，使用手工设定，调用过程dbms_stats.set_system_stats来进行。
下面是通过模拟工作负载来收集工作负载统计信息的方法(执行需要3-5分钟)：
Create Or Replace Procedure Oltp_Style As
l_Rec Yhis.住院费用记录%Rowtype;
l_n Number;
Begin
For I In 1 .. 10000 Loop
l_n := Trunc(Dbms_Random.Value(2, 1000000));
Begin
Select * Into l_Rec From Yhis.住院费用记录 Where ID = l_n;
Exception
When Others Then
Null;
End;
End Loop;
For I In 1 .. 3 Loop
Select Count(年龄) Into l_n From Yhis.病人信息;
End Loop;
End;
/
exec dbms_stats.drop_stat_table( user, 'SYSTEM_STATS' );
exec dbms_stats.create_stat_table( user, 'SYSTEM_STATS' );
exec dbms_stats.delete_system_stats;
declare
n number;
begin
oltp_style;
dbms_job.submit( n, 'oltp_style;' );
dbms_job.submit( n, 'oltp_style;' );
dbms_job.submit( n, 'oltp_style;' );
commit;
dbms_stats.gather_system_stats( gathering_mode => 'START',
stattab => 'SYSTEM_STATS',
statid => 'OLTP' );
select count(*) into n from user_jobs where what = 'oltp_style;';
while ( n > 0 )
loop
dbms_lock.sleep(5);
select count(*) into n from user_jobs where what = 'oltp_style;';
end loop;
dbms_stats.gather_system_stats( gathering_mode => 'STOP',
stattab => 'SYSTEM_STATS',
statid => 'OLTP' );
end;
/
alter system flush shared_pool;
begin
dbms_stats.import_system_stats
( stattab => 'SYSTEM_STATS', statid => 'OLTP', statown => user );
end;
/
select * from sys.aux_stats$;
系统统计信息对优化器的影响
CPU开销的计算，10.2开始，计算访问一个列的开销：
Cpu_cost=column_position*20
即CPU开销和列的位置相关，每往后一列，增加20（所以，列的位置会影响SQL性能）
验证脚本：
SET ECHO ON
DROP TABLE t;
DELETE plan_table;
CREATE TABLE t (c1 NUMBER, c2 NUMBER, c3 NUMBER,
c4 NUMBER, c5 NUMBER, c6 NUMBER,
c7 NUMBER, c8 NUMBER, c9 NUMBER);
INSERT INTO t VALUES (1, 2, 3, 4, 5, 6, 7, 8, 9);
execute dbms_stats.gather_table_stats(user,'t')
EXPLAIN PLAN SET STATEMENT_ID 'c1' FOR SELECT c1 FROM t;
EXPLAIN PLAN SET STATEMENT_ID 'c2' FOR SELECT c2 FROM t;
EXPLAIN PLAN SET STATEMENT_ID 'c3' FOR SELECT c3 FROM t;
EXPLAIN PLAN SET STATEMENT_ID 'c4' FOR SELECT c4 FROM t;
EXPLAIN PLAN SET STATEMENT_ID 'c5' FOR SELECT c5 FROM t;
EXPLAIN PLAN SET STATEMENT_ID 'c6' FOR SELECT c6 FROM t;
EXPLAIN PLAN SET STATEMENT_ID 'c7' FOR SELECT c7 FROM t;
EXPLAIN PLAN SET STATEMENT_ID 'c8' FOR SELECT c8 FROM t;
EXPLAIN PLAN SET STATEMENT_ID 'c9' FOR SELECT c9 FROM t;
SELECT statement_id, cpu_cost AS total_cpu_cost,
cpu_cost-lag(cpu_cost) OVER (ORDER BY statement_id) AS cpu_cost_1_coll,
io_cost
FROM plan_table
WHERE id = 0
ORDER BY statement_id;
DROP TABLE t;
PURGE TABLE t;
优化器计算总开销的公式：
工作量统计信息
Cost=io_cost+cpu_cost/(cpuspeed*sreadtim*1000)
Sreadtim=单块数据平均读取时间
非工作量统计信息
Cost=io_cost+cpu_cost/( CPUSPEEDNW *sreadtim*1000)
其中sreadtim= IOSEEKTIM+db_block_size/ IOTFRSPEED
mreadtim= IOSEEKTIM+mbrc*db_block_size/ IOTFRSPEED
CPUSPEEDNW=每个CPU每秒钟处理的操作数
IOSEEKTIM=平均磁盘寻道时间
IOTFRSPEED=平均每毫秒磁盘传输的字节，缺省为4096，收集后是它的10倍以上
如果存在工作量统计信息，优化器会忽略非工作量统计信息。
4.3对象统计信息
直方图
频度直方图
桶数：唯一值的数量，最大254，每个桶在user_tab_histogram中存储为一行；
列endpoint_value是number型，非数字型的列进行了一个转换，只取前6个字节。如果前面几个字符相同，则直方图的分布会严重不均衡。
列endpoint_number是累计计数，前去前一行的数，则为当前值的计数。
等高直方图
桶数大于254时，会使用等高直方图，所有的值分为5个段。
等高直方图可能导致错误的估算，引起查询优化器估值不准。
扩展的统计信息
11G以上，考虑到查询中列的相关性，可以收集扩展的统计信息。
Dbms_stats.create_extended_stats
4.3.2收集对象统计信息
1. dbms_stats包
Gather_database_stats：收集整个数据库；
Gather_schema_stats：收集指定模式的所有对象；
Gather_table_stats：收集表(索引可选)；
Gather_index_stats：收集索引；
数据字典的对象统计信息(10G以上才提供)：dbms_stats.gather_dictionary_stats
数据字典的固定表的特定对象统计信息(10G以上才提供)：
dbms_stats.gather_fixed_objects_stats
查询该过程处理了哪些表select * from v$fixed_table where type='TABLE'；
锁定和解锁统计信息
Dbms_stats.lock_schema_stats，Dbms_stats.lock_table_stats
Dbms_stats.unlock_schema_stats，Dbms_stats.unlock_table_stats
几个重要的参数：
Cascade：9i缺省为False，10G缺省为auto（自动决定是否收集索引，规则是什么?，是数据变化量吗？没有找到资料），所以，如果要收集索引，最好指定为True；
Options：缺省为Gather，处理所有对象，指定为gather stale只收集失效的对象，指定为gather empty时，只收集没有统计信息的对象。
对象统计信息的时效性：all(dba/user)_tab_modifications
10G，修改超过10%的行认为失效，11G可修改stale_percent参数来配置；
9i通过表的monitoring参数来监控表数据变化；
10G由数据库参数statistics_level来决定是否启动变化计数。缺省为typical，启动计数；
Estimate_percent：是否采样收集，该参数指定的值仅指定采样的最小百分比。100等同于Null， 表示不采样，0表示auto_sample_size，9i缺省为Null，10G缺省为auto_sample_size，对于大表，使用0.5%或0.1%，都不错，数据库会自动校正较小的值。
Method_opt：是否收集直方图，以及收集的最大桶数。Null表示只收集列的统计信息（最大，最小值等），不收集直方图。
9i缺省是for all columns size 1；10G缺省是for all columns size auto；为了加快速度，建议使用for all indexed columns（只收集索引上的列）。
Size repeat 刷新可用的直方图；
Size 1…254指定最大桶数，1表示不创建直方图；
Size skewonly只收集非均匀分布的列的直方图，自动确定桶数；
Size auto 只收集非均匀分布的列，并且根据列使用历史统计表决定是否收集；
查询优化器生成执行计划时，会跟踪Where 子句中列的使用情况，存储在col_usage$表中。
没有使用过的列不会在该表中出现。
SELECT c.name, cu.timestamp,
cu.equality_preds AS equality, cu.equijoin_preds AS equijoin,
cu.nonequijoin_preds AS noneequijoin, cu.range_preds AS range,
cu.like_preds AS "LIKE", cu.null_preds AS "NULL"
FROM sys.col$ c, sys.col_usage$ cu, sys.obj$ o, sys.user$ u
WHERE c.obj# = cu.obj# (+) AND c.intcol# = cu.intcol# (+)
AND c.obj# = o.obj# AND o.owner# = u.user#
AND o.name = '病人信息' AND u.name = 'YHIS'
ORDER BY c.col#;
Degree：指明收集一个对象统计时所用的从属时程数量。指定为Null时，使用对象自身的并行度。注意：多个对象的处理是顺序执行的，这个并行只是基于一个对象范围内。要并行收集多个对象，必须手工并行（同时启动多个执行任务）。
No_invalidate：是否使收集的对象相关的游标失效。为false时，立即失效。9i缺省是false，10G缺省为auto_invalidate，即Null，表示过一段时间后失效，目的是为了避免集中重新解析所有相关游标。
2.配置dbms_stats
10G以上才可用，通过Set_param过程修改 dbms_stats的参数缺省值，包括cascade,estimate_percent,degree,method_opt,no_invalidate,granuarity。
Set_param过程的参数：autostats_target被作业gather_stats_job使用，缺省是auto表示由作业决定要收集的对象，all表示处理所有对象,oracle表示仅处理数据字典的对象。
11G，除了全局的默认值外，还可以在模式级，表级设置默认值。
使用optstat_hist_control$查看全局默认值，表级默认值可查看dba_tab_stat_prefs
3. 调度收集统计信息
10G缺省周一到周五每晚10点，持续8小时，周六到周日全天。
SELECT program_name, schedule_name, schedule_type, enabled, state
FROM dba_scheduler_jobs
WHERE owner = 'SYS'
AND job_name = 'GATHER_STATS_JOB';
SELECT program_action, number_of_arguments, enabled
FROM dba_scheduler_programs
WHERE owner = 'SYS'
AND program_name = 'GATHER_STATS_PROG';
禁用作业：dbms_scheduler.disable(name=>’sys.gather_stats_job’)；
调度结束后，会生成一个跟踪文件，记录了未处理的对象。
11G，该作业集成进了自动维护任务。缺省周一到周五每晚10点，持续4小时，周六到周日6点，持续20个小时。
SELECT task_name, status
FROM dba_autotask_task
WHERE client_name = 'auto optimizer stats collection';
4.其它
11G新增一个概念，待定的统计信息。测试时，可以在会话级使用未发布的统计信息。
Create index 和alter index 后面增加compute statistics子句，可在创建或修改索引同时收集统计信息。因为它使用的额外资源很少，几乎可以忽略，所以很有用。
9i，需要明确指定该子句才会收集。10G缺省是启用的，除非对象被锁定统计信息。
比较统计信息
dbms_stats.diff_table_stats_in_stattab
dbms_stats.diff_table_stats_in_history
dbms_stats.diff_table_stats_in_pending
统计信息历史
10G以后，收集统计信息后，旧的统计信息会自动备份，缺省保留30天
Dbms_stats. get_stats_history_retention，dbms_stats.alter_stats_history_retention
统计信息的变化日志
Dba(all/user)_tab_stats_history
恢复统计信息
dbms_stats.restore_table_stats，……
统计信息处理的日志(数据库、数据字典，模式级，未明细到对象级)
dba_optstat_operations