oracle执行计划怎么看:核心判读方法

oracle执行计划怎么看,核心是读懂Oracle官方SQL调优参考文档《OracleDatabaseSQLTuningGuide》中定义的执行计划层级、访问路径、连接方式与预估统计信息,你可以通过EXPLAINPLAN、DBMS_XPLAN.DISPLAY、AUTOTRACE三种方式生成计划,判读时优先确认ID层级缩进、Operation操作类型、Rows预估行数、Cost成本值,重点识别全表扫描、嵌套循环、哈希连接等关键操作,该方法适用于Oracle11g、12c、19c版本,不适合已经绑定SQLProfile、Outline且计划被强制锁定的SQL,这类场景直接查看生成计划会出现预估和真实运行偏差。

oracle执行计划怎么看:生成执行计划的常用方式

你在SQL窗口执行EXPLAINPLANFOR你的目标SQL,之后调用SELECT*FROMTABLE(DBMS_XPLAN.DISPLAY()),就能拿到静态执行计划,这种方式只做优化器预估,不会真实执行SQL,适合还未上线、不能在生产环境跑重查询的语句。开启SETAUTOTRACEON之后直接运行SQL,会同时输出执行计划与真实统计信息,包含逻辑读、物理读、递归调用数据,适合已经确认可以执行的SQL调优。

两种生成方式存在明显差异,你可以对照下表区分适用场景,避免选错方式造成误判。

生成方式是否真实执行SQL是否输出真实统计量适用场景
EXPLAINPLAN+DBMS_XPLAN否否,仅优化器预估生产环境大表SQL预评估
AUTOTRACE是是,包含buffergets等指标测试环境SQL性能验证

DBMS_XPLAN还支持从V$SQL、DBA_HIST_SQL_PLAN读取已经缓存在共享池或者AWR历史中的执行计划,写法为SELECT*FROMTABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id,child_number)),适合线上已经执行完成、无法重新跑SQL的语句。

oracle执行计划怎么看:核心字段与操作判读

执行计划是树形结构,缩进代表父子层级,上层ID依赖下层ID输出结果,阅读顺序是从上到下、同层级从左到右,先看最底层表访问路径,再向上看表之间的连接运算。Operation列记录操作类型,TABLEACCESSFULL代表全表扫描,INDEXRANGESCAN代表索引范围扫描,INDEXUNIQUESCAN代表唯一索引等值查询。

Cost是优化器估算的累计代价,数值由IO、CPU开销加权计算,不是绝对耗时,不能单独拿Cost高低判定SQL快慢,Rows是优化器预估返回行数,一旦统计信息陈旧,Rows预估会和真实返回行数差距很大,这也是很多计划看着合理但实际性能差的主要原因。

连接方式分为三类,NESTEDLOOPS嵌套循环适合外表结果集很小、内表存在高效索引的场景,HASHJOIN哈希连接适合大表关联、两边数据量都比较大的场景,MERGESORTJOIN合并排序连接要求两边数据已经按照关联键排序。错误示范:只看到Cost数值高就强行加索引,忽略统计信息过期,最终优化器依然选择原有计划,调优没有效果。

重点识别异常信号。

  • TABLEACCESSFULL出现在大表,且过滤条件本可以命中索引
  • 预估Rows和真实返回行数偏差超过一个数量级
  • 连接顺序不符合业务过滤逻辑,大表先做关联再过滤

该判读规则的适用边界是基于CBO基于成本优化器,OracleRBO规则优化器版本已经淘汰,旧版本RBO计划字段含义和CBO存在区别,不能直接套用这套判读逻辑。

oracle执行计划怎么看:预估与真实执行差异排查

如果你发现DBMS_XPLAN预估计划和实际运行计划不一致,优先检查表统计信息是否收集,执行DBMS_STATS.GATHER_TABLE_STATS重新收集表、索引统计信息,同时检查是否存在绑定变量窥探、SQLProfile、SQLPlanBaseline干预优化器选择。DISPLAY_CURSOR输出中A-Rows列是实际返回行数,E-Rows是预估行数,两列对比就是判断统计信息是否失真最直接的依据。

查看执行计划时还要关注PredicateInformation,区分AccessPredicates和FilterPredicates,AccessPredicates是可以利用索引定位数据的过滤条件,FilterPredicates是拿到数据之后再做筛选,FilterPredicates过多通常意味着索引选择不理想。

敬慕百科汇集百科知识与游戏文化,带你发现世界的每一个精彩角落。

想要了解更多关于oracle执行计划怎么看的文章欢迎访问:百科