Oracle执行计划详细解读.ppt

Oracle执行计划解读 培训教师 谢高兴 时间 2006 4 27 BEGIN SQL执行过程 1 解析SQL 2 执行 3 显示结果集 4 转换字段数据 检查安全性 检查SQL语法 查询重新书写 创建执行计划 捆绑执行计划 执行执行计划 读取结果集 执行计划 执行计划 Oracle内部的机器级代码 决定如何访问存储器 得到需要的结果集 执行计划的主要内容 访问方式 访问顺序 得到执行计划的方式 1 Explain 解释 2 Autotrace 自动跟踪 3 其他工具 基本格式 explainplansetSTATEMENT ID testplan ForSelect Select insert update等数据操作语句均可 基本使用方式 SQL setautotraceon SQLPLUS中使用 准备 创建Plan table表 createtableplan table statement idvarchar2 30 timestampdate remarksvarchar2 80 operationvarchar2 30 optionsvarchar2 30 object nodevarchar2 128 object ownervarchar2 30 object namevarchar2 30 object instanceinteger object typevarchar2 30 optimizervarchar2 255 search columnsinteger idinteger parent idinteger positioninteger costinteger cardinalityinteger bytesinteger other tagvarchar2 255 partition startvarchar2 255 partition stopvarchar2 255 partition idinteger otherlong distributionvarchar2 30 AUTOTRACE SQL setautotraceon SQL select fromdual D XExecutionPlan 0SELECTSTATEMENTOptimizer CHOOSE10TABLEACCESS FULL OF DUAL Statistics 0recursivecalls0dbblockgets3consistentgets0physicalreads0redosize372bytessentviaSQL Nettoclient511bytesreceivedviaSQL Netfromclient2SQL Netroundtripsto fromclient0sorts memory 0sorts disk 1rowsprocessedSQL 统计信息 执行计划 结果数据 使用Explain 1 删除上次解析数据 2 执行解析 deletefromplan tablewhereSTATEMENT ID testplan explainplansetSTATEMENT ID testplan forselect fromdual 3 显示执行计划 selectlpad 5 level 1 operationoperation options object name cost positionfromplan tablestartwithid 0andSTATEMENT ID testplan connectbypriorid parent id 第一个执行计划 对应SQL语句 select fromdual 执行计划 怎样看执行计划 执行计划其实是一棵树 层次最深的最先执行 层次相同 上面的先执行 显示时已经按照层次缩进 因此从最里面的看起 最后一组就是驱动表 例 驱动表 selectcount fromprm adjustpricemain prm adjustprice bdetailwheremain cadjpriceid detail CADJPRICEIDandmain DADJPRICEDATE 2004 04 18 anddetail CINVENTORYIDin selectpk invmandocfrombd invmandocwherebd invmandoc SEALFLAG N 表访问方式 散列获取 全表扫描 ROWID访问 读取表的每一条记录 顺序地从第一个数据块开始知道结尾标志 ROWID包含记录的数据块号和数据块中的偏移量 因此它是获取一条记录的最快的方法 使用散列算法得到符号关键值的来确定记录所在的数据块 它能减少数据读入量 但是存在重新定位记录的问题 只能在静态表中使用 索引访问方式 快速全索引扫描 索引扫描 从索引中读取一个或多个ROWID 索引值通常按照升序方式扫描 有些查询可以不扫描表只扫描索引就能得到期望的查询结果 所有查询字段都在索引中指定 查询返回大于索引所有记录数的10 进行Count 操作 Oracle索引 表连接操作1 1 嵌套循环连接 NESTEDLOOP 两个表 一个小的内部表和一个外部表 一般情况下速度较快 特别是中间结果集非常小的情况下速度快 外部表索引 内部表 外部表 表连接操作2 2 散列连接 HASHJOIN 两个表 一个较小的驱动表和一个大表 中间结果集非常大的情况下速度较快 Hash Area SizeRAM 驱动表 大表 散列访问ROWID RAM溢出使用临时表空间 表连接操作3 3 排序合并连接 MERGEJOIN 两个表都使用全表扫描 分别进行排序 然后再合并成查询的结果集 极少情况适合 只有包含两个表的决大多数记录的查询适合 A表全表扫描 B表全表扫描 A表排序 B表排序 输出结果集 合并 Step1 全表扫描 对查询影响最大的就是全表扫描 TableAccessFull 计执行划中所有的全表扫描都值得怀疑 除非是数据量非常小的表 可能引起全表扫描的原因 表上没有索引 没有WHERE条件 对索引字段使用了内置函数 如To Dcredate 2005 02 15 Like操作符而参数以 开始 表记录非常少 Step2 驱动表 1 驱动表 最内层的驱动表是不是能在执行过程中得到最小的中间数据集 例如 查询销售订单明细 涉及三个表 订单主表 订单附表 存货基本档案表 条件中包含 订单日期等于某天和存货编码等于某个值 方案一 如果以订单主表为驱动表 可以使用日期上的索引过滤出所有的当天的订单 然后关联附表 最后再关联到存货档案表 过滤出相应存货 订单附表 订单主表 存货档案表 日期索引 主表PK索引 存货主键索引 Step2 驱动表 2 方案二 如果以存货档案为驱动表 可以使用编码上的索引过滤出所有存货 然后关联订单附表 得到所有存货符合条件的订单附表记录 最后再用订单附表上的主表主键关联主表 判断订单主表上的日期是否符合条件 最后得到结果集 订单附表 订单主表 存货档案表 编码索引 存货ID索引 主表主键索引 结论 如果按照存货去过滤 得到订单附表上符合条件记录会较少 认为方案二更优 Step3 不该建的索引 错误索引 对于只有少数一个可能值的列 不应该建索引 如单据状态 单据主表上的部门人员 附表上的仓库 库存组织等 索引不是越多越好 数据的插入 删除和修改都需要维护索引表 也是有成本的 建议建索引的列 所有可能用做查询条件的日期列 客户列 附表上的主表主键列 存货列 来源单据列 基本档案的编码列等 Step4 复合索引列顺序 对复合索引 应将重复值少的列作为先导列 例如订单附表上有来源单据类型 srctype 和来源单据ID srcid 建立索引的顺序应该是srcid srctype 如果将srctype列作为先导列 可能在查询时 没有srcid的条件也使用这个索引扫描 其结果是扫描了大半个表 比全表扫描还慢 Step5 非最优索引 对一个表存在多个索引的列作为条件 解析中可能使用了非最优索引 使用提示 指定使用某个索引 存在SQL兼容问题 不推荐 将索引尽可能多的使用条件 使用内置函数或运算使不想使用的条件列失效 整型数据列 0 字符型加trim函数 例如 在进行调拨的时候需要判断这个仓库中此存货不存在没有记帐的出库单 Selectcount fromXXXwherepk inv 1andstatus 0 这个查询会用到存货索引 但是速度还是慢 但是仔细分析会发现 其实实际运行中没记帐的出库单比例很少 如果在存货索引上再加上状态列 可以过滤更少的数据 速度将大大提高 Step6 数据非均匀分布 对于某些SQL可能存在时快时慢 这个即可能是数据的分布不均导致 举例 做销售订单时 去判断此客户是否存在未结算订单 SQL语句 这个语句在做多数客户的时候没有感觉 但是在做某一两个客户时速度奇慢 查其执行计划 也使用了主表上的客户索引 Select Fromso order so order b whereso order pk so order b pkandccustomerid 1and 最后发现情况是这样的 企业是做批发业务的 政策法规上不允许直接销售给个人 内部职工需要购买时都是按照统一个特定的客户开票 因此数据库中数据严重不均 此客户的订单数量大于总数的10 一进行这样的查询就严重占用数据库资源 导致系统相应速度慢 解决的办法 这种行为都是现金交易根本不需要进行检查 查询的结果永远是空 程序绕过这个检查就行了 Step7 EXIST 查询基本格式 例 selectcount fromprm adjustpricemain prm adjustprice bdetailwheremain cadjpriceid detail CADJPRICEIDandmain cadjpriceidin 1 2 andexists select frombd invmandocwherebd invmandoc SEALFLAG N anddetail CINVENTORYID bd invmandoc pk invmandoc 不要将主表的其他条件写在子查询中 这样可能使外层查询没有条件而进行全表扫描 如果想按照子查询中的表作为驱动表进行查询 需要改造成IN子句 Select From主表where主表条件andexists Select from子表where子表条件and主表关联字段 子表PK Step8 IN 查询基本格式 例 selectcount fromprm adjustpricemain prm adjustprice bdetailwheremain cadjpriceid detail CADJPRICEIDandmain cadjpriceidin 1 2 anddetail CINVENTORYIDin selectpk invmandocfrombd invmandocwherebd invmandoc SEALFLAG N 如果不是想按照子查询中的表作为驱动表进行查询 建议改造成EXIST子句 特别是自查询存在较多重复数据时 Select From主表where主表条件and主表关联字段in Select子表PKfrom子表where子表条件 Step9 NOTIN 尽量不要使用NOTIN NOTIN都可以改造成NOTEXISTS NOTIN中子查询如果对某个记录返回空 整个结果集都会为空 Step10 视图 视图 不要存在视图套视图情况 视图中不要使用Distinct 视图中希望作为条件的关联列和结果列 不要进行运算 如月份的加减 不要使用条件太复杂的视图 StepOver 当所有的调整都无效时 那就是算法的问题 实例 采购计划查询时 先查询出采购计划 然后查询执行数据 再追加到采购计划数据中 查询执行数据SQL如下 selectplanbill cplanbid sum po planexe npraysnum sum from SELECTpo plan b cplanbid po plan h pk corp po plan b cobj1id po plan b cobj2id po plan b cobj3id po plan h dstartdate po plan h denddate aa invclasscodeFROMpo plan h po plan b bd invclaawherepo plan h cplanhid po plan b cplanhidandpo plan h dr 0andpo plan b dr 0andaa pk invcl po plan b cobj2idandpo plan h dprocessdate planbill dstartdateandpo planexe denddate planbill denddategroupbyplanbill cplanbid 实例 续1 各表数据关系如下 采购计划主表 采购计划子表 执行情况表 cobjid1 cobjid2 cobjid3 部门ID 存货分类ID 存货ID 存货分类表 实例 续2 查看执行计划如下 成本优化器模式下的计划 全是全表扫描 实例 续3 第一反应 加规则提示select rule planbill cplanbid sum 好像有提高 只有一个FullScan了 但是这个计划执行起来更差 原因是 内部驱动表bd invcl到执行计划表没有用到任何条件 结果集是两个表的完全关联 实例 续4 看看内部的子查询情况SELECTpo plan b cplanbid aa invclasscodeFROMpo plan h po plan b bd invclaawherepo plan h cplanhid po plan b cplanhidandpo plan h dr 0andpo plan b dr 0andaa pk invcl po plan b cobj2idandpo plan h dprocessdate 2005 04 19 实例 续5 发现计划表上相应日期列没有索引 子表上的主表字段也没有索引 加上索引 CREATEINDEXPO PLAN H IDX1ONPO PLAN H DPROCESSDATE CREATEINDEXIDX PO PLAN B 1ONPO PLAN B CPLANHID 内部子查询已经没有太大问题 但是整个查询仍然没有起色 实例 续6 在内部也都用上了索引 但是效果仍不好 速度照样慢 看到执行表使用的索引是I MPP PLANEXEX 4索引列 DSTARTDATE DENDDATE 删除先关联了执行表 然后再关联最外层的存货分类表 对数据没有起到过滤作用 实例 续7 仔细分析一下外围关联条件 wherepo planexe pk corp planbill pk corpandpo planexe dr 0and po planexe cdeptid planbill cobj1idorplanbill cobj1idisnull and po planexe cinvmandocid planbill cobj3idorplanbill cobj3idisnull andinvclass pk invcl po planexe cinvclassidand invclass invclasscodelikeplanbill invclasscode andplanbill invclasscodeisnotnull orplanbill cobj2idisnull andpo planexe dstartdate planbill dstartdateandpo planexe denddate planbill denddate这里的po planexe cdeptid planbill cobj1idorplanbill cobj1idisnull和po planexe cinvmandocid planbill cobj3idorplanbill cobj3idisnull是无法使用执行表上的部门和存货索引 部门上单独加索引 采购计划表上有一个逻辑是 存货和存货分类两个列是互斥的 且必有一个不为空 我们可以使用这个逻辑将条件分开 而不是写在一起 and po planexe cinvmandocid planbill cobj3idandplanbill cobj2idisnull or po planexe cinvclassid invclass pk invcl 但是 对存货不需要存货分类表 而对存货分类因为可以不定义到最末级 因此必须关联两次存货分类表 最后可行的解决办法是将两个查询UNION达到解决问题 实例 续8 先按照计划表上有存货的进行查询select rule planbill cplanbid from SELECTpo plan b cplanbid po plan h pk corp FROMpo plan h po plan bwherepo plan h cplanhid po plan b cplanhidandpo plan h dr 0andpo plan b dr 0andpo plan h dprocessdate planbill dstartdateandpo planexe denddate planbill denddategroupbyplanbill cplanbid 实例 续9 执行计划如下 看到已经能得到比较优化的结果 查询时间1秒 3305行 实例 续10 按照存货分类查询的呢 按照编码进行like查询已证明比较慢 那么我们可以考虑建立一个存货分类上下级关系的完全对照表 使用对照表关联 CREATETABLEtemp inv2up pkidNUMBER 10 NOTNULL 主键ID用处不大pk invclCHAR 20 NOTNULL 子分类pk