Oracle 深入解析10053事件

Oracle深入解析10053事件

本次我们主要讲解oracle 10053事件和实验,好多朋友可能对这个事件不是很熟悉,因为在日常运维中用到的不是很多。Oracle 10046和10053都是非官方trace sql的方法,在官方文档上是找不到相关资料的,但在MOS上可以找到。sql_trace是官方推荐的trace sql的方法,在官方文档上是可以查询出来的。

10053事件:用来描述oracle如何选择执行计划的过程,然后输出到trace文件里,共我们参考,因为我们经常看执行计划怎么执行的消耗了哪些资源,而不是常看执行计划怎么选择出来了的。

10053场景:当SQL语句执行时走的是错误的执行计划,而又找不到原因时,这时请用10053来分析一下原因。

10053特点:

(1)只可以了解oracle执行计划的选择过程

(2)无法获知代价的计算公式,因为这是oracle内部的商业机密,而且每个oracle版本的优化器计算公式都不相同差距还是蛮大的,不同版本的同一个语句的代价也不一样,优化器现在还不是很成熟,还有待完善。

(3)在这个里面我们重点要了解的是“代价”是如何计算出来的,然后我们才能了解执行计划是如何选择的。

(4)在10053中可以了解哪些因素影响sql的执行代价

(5)oracle 8i cost等价IO资源消耗  9i以后cost等价IO+CPU+网络+等待事件+其他代价

一般IO资源的权重比较大CPU权重较小

10053内容:

参数区:初始化参数,隐含参数,这些参数可以左右oracle工作方式

SQL区:执行的SQL语句,是否使用绑定变量,是否进行了转换操作

系统信息区:操作系统统计信息  cpu主频CPU执行时间IO寻址时间单块读时间多块读时间

对象统计信息区:

数据访问方式:访问方式不一样计算代价的方法也不一样,全表扫描走索引多表关联代价都不同

关联查询:把每张表都作为驱动表去组合,择优选择“代价”最小的关联方式,与哪个表在前无关系

代价的最后修正:oracle会对选择出来的代价再进行最后的修正,使其更准确一些,更合理一些

选择出最终执行计划:这个过程是非常快速的,毫秒级就搞定啦

实验环境

LEO1@LEO1> select * from v$version;                    这是我的oracle edition

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

 

1.验证全表扫描的成本计算公式,贴出执行计划和计算公式。

LEO1@LEO1> col sname for a20

LEO1@LEO1> col pname for a20

LEO1@LEO1> col pual1 for a30

LEO1@LEO1> col pual2 for a30

LEO1@LEO1> select * from sys.aux_stats$;  查看操作系统统计信息

SNAME              PNAME                  PVAL1    PVAL2

-------------------- -------------------- ---------- ---------------------------------------------------

SYSSTATS_INFO        STATUS                            COMPLETED

SYSSTATS_INFO        DSTART                            08-15-2009 00:49

SYSSTATS_INFO        DSTOP                              08-15-2009 00:49

SYSSTATS_INFO        FLAGS                        1

SYSSTATS_MAIN        CPUSPEEDNW            2657.0122 

SYSSTATS_MAIN        IOSEEKTIM                    10 

SYSSTATS_MAIN        IOTFRSPEED                4096

SYSSTATS_MAIN        SREADTIM     

SYSSTATS_MAIN        MREADTIM     

SYSSTATS_MAIN        CPUSPEED     

SYSSTATS_MAIN        MBRC     

SYSSTATS_MAIN        MAXTHR

SYSSTATS_MAIN        SLAVETHR

说明

aux_stats$是sys管理员用户下的一个基表后缀为$,必须写schema才能查询到,所谓的基表就是给动态性能视图提供数据的原始表,由于基表非常重要,oracle规定不允许直接访问和修改基表,如果你比较了解这些那么另说了。这个表中记录了“操作系统统计信息”。Oracle会利用操作系统统计信息来修正执行计划的代价,也就是说这些信息是影响代价计算的因素之一。

注意:如果oracle收集了操作系统统计信息,那么CBO采用工作量统计模式计算代价

      如果oracle没有收集操作系统统计信息,那么CBO采用非工作量统计模式计算代价,看上面MBRC没有参数值就说明还没有收集操作系统统计信息

这两个模式计算代价的公式是不同的。

SNAME:是指操作系统统计信息

PNAME:parameter name参数名

PVAL1:参数值

PVAL2:参数值

参数解释

FLAGS:标志

CPUSPEEDNW:非工作量统计模式下CPU主频,直接来自硬件

IOSEEKTIM:IO寻址时间(毫秒),直接来自硬件

IOTFRSPEED:IO传输速率(字节/毫秒)

SREADTIM:读取单个数据块的平均时间

MREADTIM:读取多个数据块的平均时间

CPUSPEED:工作量统计模式下CPU主频,根据当前工作量评估出一个合理值

MBRC:oracle收集完统计信息后评估出的一次多块读可以读几个数据块db_file_multiblock_read_count

MAXTHR:最大IO吞吐量(字节/秒)

SLAVETHR:平均IO吞吐量(字节/秒)

后面这6个参数是在oracle收集完统计信息后才能得出的参数值,有什么用呢?我来解释一下下

CBO在计算SQL语句的代价时,需要使用数据库对象例如表索引等对象统计数据,还要使用操作系统统计数据例如CPU周期IO速度数据块读时间等,选择花费时间最少的执行计划为最佳执行计划。

Oracle使用dbms_stats.gather_system_stats存储过程来收集操作系统统计信息,收集来的数据存放在sys.aux_stats$表中,如果我们做了收集操作那么会有统计数据,如果没有做就没有统计数据,这两种计算代价的方法是不同的,后续会讲。

dbms_stats.gather_system_stats语法

execute  dbms_stats.gather_system_stats(

gathering_mode varchar2 default ‘noworkload’

interval integer default null,

stattab varchar2 default null,

statid varchar2 default null,

statown varchar2 default null);

解释

gathering_mode参数,默认值“noworkload”,还可以设置为“workload”含义

noworkload:非工作量统计模式,收集上来的数据都是来自硬件

workload:工作量统计模式,收集上来的数据需要在特定的数据库负载间隔内统计出来的,这样的数据才能真实反映出数据库的操作系统参数(需要执行sql测评出来)

interval:可以指定收集统计信息的时间间隔,例如5收集5分钟的统计信息

命令:execute dbms_stats.gather_system_stats(‘noworkload’,5); 

START和STOP关键字自己决定何时开始何时结束收集统计信息

相关推荐