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 editionBANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionPL/SQL Release 11.2.0.1.0 - ProductionCORE 11.2.0.1.0 ProductionTNS for Linux: Version 11.2.0.1.0 - ProductionNLSRTL Version 11.2.0.1.0 - Production 1.验证全表扫描的成本计算公式，贴出执行计划和计算公式。LEO1@LEO1> col sname for a20LEO1@LEO1> col pname for a20LEO1@LEO1> col pual1 for a30LEO1@LEO1> col pual2 for a30LEO1@LEO1> select * from sys.aux_stats$; 查看操作系统统计信息SNAME PNAME PVAL1 PVAL2-------------------- -------------------- ---------- ---------------------------------------------------SYSSTATS_INFO STATUS COMPLETEDSYSSTATS_INFO DSTART 08-15-2009 00:49SYSSTATS_INFO DSTOP 08-15-2009 00:49SYSSTATS_INFO FLAGS 1SYSSTATS_MAIN CPUSPEEDNW 2657.0122 SYSSTATS_MAIN IOSEEKTIM 10 SYSSTATS_MAIN IOTFRSPEED 4096SYSSTATS_MAIN SREADTIM SYSSTATS_MAIN MREADTIM SYSSTATS_MAIN CPUSPEED SYSSTATS_MAIN MBRC SYSSTATS_MAIN MAXTHRSYSSTATS_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_countMAXTHR：最大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关键字自己决定何时开始何时结束收集统计信息

ASM启动和关闭操作选项实战Oracle 10g新特性之RMAN TSPITR特性相关资讯 Oracle 10053 10053事件本文评论查看全部评论（0）