Oracle 性能诊断艺术 第四章 笔记

第四章 系统和对象统计信息

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