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

时间:2023-01-01 14:37:30

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

4.1dbms_stats简介

            9i开始,dbms_stats代替analyze,后者仅用于对象统计信息之外的用途,例如,index structure validate,行迁移的统计。

 

4.2系统统计信息

            I/O开销模型(I/O cost model):执行SQL语句所需的数据块读的多少

            该方法的主要缺点是认为单块读和多块读开销相当,结果,优化器更多倾向于使用多块读操作,如全表扫描,直到8i,初始化参数optimizer_index_cachingoptimizer_index_cost_adj解决了这个问题。但缺省值0100,仅适用于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$中(snamesysstats_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_valuenumber型,非数字型的列进行了一个转换,只取前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_statsDbms_stats.lock_table_stats

Dbms_stats.unlock_schema_statsDbms_stats.unlock_table_stats

 

 

几个重要的参数:

Cascade9i缺省为False10G缺省为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_size9i缺省为Null10G缺省为auto_sample_size,对于大表,使用0.5%0.1%,都不错,数据库会自动校正较小的值。

 

Method_opt是否收集直方图,以及收集的最大桶数。Null表示只收集列的统计信息(最大,最小值等),不收集直方图。

9i缺省是for all columns size 110G缺省是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缺省是false10G缺省为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_retentiondbms_stats.alter_stats_history_retention

 

统计信息的变化日志

Dba(all/user)_tab_stats_history

 

恢复统计信息

dbms_stats.restore_table_stats……

 

统计信息处理的日志(数据库、数据字典,模式级,未明细到对象级)

dba_optstat_operations