Oracle执行计划详细解读11368.pptx

Oracle执行计划解读 培训教师培训教师：谢高兴谢高兴 时时 间间：2006.4.27 BEGINSQL执行过程1,解析SQL2,执行3,显示结果集4,转换字段数据检查安全性检查SQL语法 查询重新书写 创建执行计划创建执行计划捆绑执行计划执行执行计划读取结果集执行计划执行计划执行计划:Oracle Oracle内部的机器级代码内部的机器级代码,决定如何访问决定如何访问存储器，得到需要的结果集。存储器，得到需要的结果集。执行计划的主要内容：访问方式，访问执行计划的主要内容：访问方式，访问顺序。顺序。得到执行计划的方式1.Explain1.Explain（解释）（解释）2.Autotrace(2.Autotrace(自动跟踪自动跟踪)3.3.其他工具其他工具基本格式：基本格式：explain plan set STATEMENT_ID=testplanFor Select.(Select,insert,update 等数据操作语句均可等数据操作语句均可)基本使用方式：基本使用方式：SQL set autotrace on;(SQL PLUS中使中使用用)准备：创建准备：创建Plan_table表表create table plan_table(statement_id varchar2(30),timestamp date,remarks varchar2(80),operation varchar2(30),options varchar2(30),object_node varchar2(128),object_owner varchar2(30),object_name varchar2(30),object_instance integer,object_type varchar2(30),optimizer varchar2(255),search_columns integer,id integer,parent_id integer,position integer,cost integer,cardinality integer,bytes integer,other_tag varchar2(255),partition_start varchar2(255),partition_stop varchar2(255),partition_id integer,other long,distribution varchar2(30);AUTO TRACESQL set autotrace on;SQL select*from dual;D-XExecution Plan-0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS(FULL)OF DUALStatistics-0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 372 bytes sent via SQL*Net to client 511 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts(memory)0 sorts(disk)1 rows processedSQL统计信息统计信息执行计划执行计划结果数据结果数据使用使用Explain1,1,删除上次解析数据删除上次解析数据2,2,执行解析执行解析delete from plan_table where STATEMENT_ID=testplan;explain plan set STATEMENT_ID=testplanexplain plan set STATEMENT_ID=testplanfor select*from dual;for select*from dual;3,3,显示执行计划显示执行计划select lpad(,5*(level-1)|operation operation,options,object_name,cost,position from plan_table start with id=0 and STATEMENT_ID=testplan connect by prior id=parent_id;第一个执行第一个执行 计划计划对应对应SQLSQL语句：语句：select*from dual;select*from dual;执行计划执行计划:怎样看执行计划执行计划其实是一棵树，层次最深的最先执行，层次执行计划其实是一棵树，层次最深的最先执行，层次相同，上面的先执行。显示时已经按照层次缩进，因相同，上面的先执行。显示时已经按照层次缩进，因此从最里面的看起。最后一组就是驱动表。例：此从最里面的看起。最后一组就是驱动表。例：驱动表 select count(*)from prm_adjustprice main,prm_adjustprice_b detail where main.cadjpriceid=detail.CADJPRICEID and main.DADJPRICEDATE=2004-04-18 and detail.CINVENTORYID in(select pk_invmandoc from bd_invmandoc where bd_invmandoc.SEALFLAG=N);表访问方式表访问方式散列获取：散列获取：全表扫描：全表扫描：ROWIDROWID访问：访问：读取表的每一条记录，顺序地从第一个数据块开始知道结尾读取表的每一条记录，顺序地从第一个数据块开始知道结尾标志。标志。ROWID包含记录的数据块号和数据块中的偏移量。因此它包含记录的数据块号和数据块中的偏移量。因此它是获取一条记录的最快的方法。是获取一条记录的最快的方法。使用散列算法得到符号关键值的来确定记录所在的数据块。使用散列算法得到符号关键值的来确定记录所在的数据块。它能减少数据读入量，但是存在重新定位记录的问题，只能它能减少数据读入量，但是存在重新定位记录的问题，只能在静态表中使用。在静态表中使用。索引访问方式索引访问方式快速全索引扫描：快速全索引扫描：索引扫描：索引扫描：从索引中读取一个或多个从索引中读取一个或多个ROWID。索引值通常按照升序方式扫描。索引值通常按照升序方式扫描。有些查询可以不扫描表只扫描索引就能得到期望的查询结果。所有些查询可以不扫描表只扫描索引就能得到期望的查询结果。所有查询字段都在索引中指定有查询字段都在索引中指定;查询返回大于索引所有记录数的查询返回大于索引所有记录数的10%;进行进行Count(*)操作。操作。OracleOracle索索引引表连接操作表连接操作11,1,嵌套循环连接嵌套循环连接-NESTED LOOP-NESTED LOOP两个表，一个小的内部表和一个外部表两个表，一个小的内部表和一个外部表一般情况下速度较快，特别是中间结果集非常小的情况下一般情况下速度较快，特别是中间结果集非常小的情况下速度快。速度快。外部表索引外部表索引内部表内部表外部表外部表表连接操作表连接操作22,2,散列连接散列连接-HASH JOIN-HASH JOIN两个表，一个较小的驱动表和一个大表两个表，一个较小的驱动表和一个大表中间结果集非常大的情况下速度较快。中间结果集非常大的情况下速度较快。Hash_Area_Size RAMHash_Area_Size RAM驱动表驱动表大表大表散列访问散列访问 ROWID ROWIDRAM溢出使用临时表空间溢出使用临时表空间表连接操作表连接操作33,3,排序合并连接排序合并连接-MERGE JOIN-MERGE JOIN两个表都使用全表扫描，分别进行排序，然后再合并成查两个表都使用全表扫描，分别进行排序，然后再合并成查询的结果集。询的结果集。极少情况适合。只有包含两个表的决大多数记录的查询适极少情况适合。只有包含两个表的决大多数记录的查询适合。合。A A表全表扫描表全表扫描B B表全表扫描表全表扫描A A表排序表排序B B表排序表排序输出结果集输出结果集合并合并Step1:全表扫描全表扫描对查询影响最大的就是全表扫描对查询影响最大的就是全表扫描-Table Access Full.-Table Access Full.计执行划中所有的全表扫描都值得怀疑计执行划中所有的全表扫描都值得怀疑.除非是数据量非常小的表除非是数据量非常小的表可能引起全表扫描的原因：可能引起全表扫描的原因：表上没有索引表上没有索引没有没有WHEREWHERE条件条件对索引字段使用了内置函数，如对索引字段使用了内置函数，如 To(Dcredate)=2005-02-15 To(Dcredate)=2005-02-15LikeLike操作符而参数以操作符而参数以“%”“%”开始开始表记录非常少表记录非常少Step2:驱动表驱动表1驱动表驱动表最内层的驱动表是不是能在执行过程中得到最小的中间数据集？最内层的驱动表是不是能在执行过程中得到最小的中间数据集？例如：查询销售订单明细，涉及三个表，订单主表，订单附表例如：查询销售订单明细，涉及三个表，订单主表，订单附表,存货存货基本档案表。条件中包含：订单日期等于某天和存货编码等于某个值。基本档案表。条件中包含：订单日期等于某天和存货编码等于某个值。方案一：如果以订单主表为驱动表，可以使用日期上的索引过滤出所方案一：如果以订单主表为驱动表，可以使用日期上的索引过滤出所有的当天的订单，然后关联附表，最后再关联到存货档案表，过滤出有的当天的订单，然后关联附表，最后再关联到存货档案表，过滤出相应存货。相应存货。订单附表订单附表订单主表订单主表存货档案表存货档案表日期索引日期索引主表主表PK索引索引存货主存货主键索引键索引Step2:驱动表驱动表2方案二：如果以存货档案为驱动表，可以使用编码上的索引过滤出所方案二：如果以存货档案为驱动表，可以使用编码上的索引过滤出所有存货，然后关联订单附表，得到所有存货符合条件的订单附表记录，有存货，然后关联订单附表，得到所有存货符合条件的订单附表记录，最后再用订单附表上的主表主键关联主表，判断订单主表上的日期是最后再用订单附表上的主表主键关联主表，判断订单主表上的日期是否符合条件，最后得到结果集。否符合条件，最后得到结果集。订单附表订单附表订单主表订单主表存货档案表存货档案表编码索引编码索引存货存货ID索引索引主表主主表主键索引键索引结论：如果按照存货去过滤，得到订单附表上符合条件记录会较少，结论：如果按照存货去过滤，得到订单附表上符合条件记录会较少，认为方案二更优。认为方案二更优。Step3:不该建的索引不该建的索引错误索引：错误索引：对于只有少数一个可能值的列，不应该建索引。如单据状态，单据对于只有少数一个可能值的列，不应该建索引。如单据状态，单据主表上的部门人员，附表上的仓库，库存组织等。主表上的部门人员，附表上的仓库，库存组织等。索引不是越多越好。数据的插入，删除和修改都需要维护索引表，索引不是越多越好。数据的插入，删除和修改都需要维护索引表，也是有成本的。也是有成本的。建议建索引的列。所有可能用做查询条件的日期列建议建索引的列。所有可能用做查询条件的日期列,客户列；附表客户列；附表上的主表主键列，存货列，来源单据列；基本档案的编码列等。上的主表主键列，存货列，来源单据列；基本档案的编码列等。Step4:复合索引列顺序复合索引列顺序对复合索引，应将重复值少的列作为先导列对复合索引，应将重复值少的列作为先导列例如订单附表上有来源单据类型例如订单附表上有来源单据类型(srctype)(srctype)和来源单据和来源单据ID(srcid),ID(srcid),建建立索引的顺序应该是立索引的顺序应该是srcid,srctype.srcid,srctype.如果将如果将srctypesrctype列作为先导列，列作为先导列，可能在查询时，没有可能在查询时，没有srcidsrcid的条件也使用这个索引扫描，其结果是扫的条件也使用这个索引扫描，其结果是扫描了大半个表，比全表扫描还慢。描了大半个表，比全表扫描还慢。Step5:非最优索引非最优索引对一个表存在多个索引的列作为条件，解析中可能使用了非最优索引对一个表存在多个索引的列作为条件，解析中可能使用了非最优索引使用提示，指定使用某个索引使用提示，指定使用某个索引(存在存在SQLSQL兼容问题，不推荐兼容问题，不推荐)将索引尽可能多的使用条件将索引尽可能多的使用条件使用内置函数或运算使不想使用的条件列失效。使用内置函数或运算使不想使用的条件列失效。整型数据列整型数据列+0+0字符型加字符型加trimtrim函数函数例如：在进行调拨的时候需要判断这个仓库中此存货不存在没有记帐的出库单例如：在进行调拨的时候需要判断这个仓库中此存货不存在没有记帐的出库单 Select count(*)from XXX where pk_inv=:1 and status=0 这个查询会用到存货索引，但是速度还是慢。但是仔细分析会发现，其这个查询会用到存货索引，但是速度还是慢。但是仔细分析会发现，其实实际运行中没记帐的出库单比例很少，如果在存货索引上再加上状态实实际运行中没记帐的出库单比例很少，如果在存货索引上再加上状态列，可以过滤更少的数据，速度将大大提高。列，可以过滤更少的数据，速度将大大提高。Step6:数据非均匀分布数据非均匀分布对于某些对于某些SQLSQL可能存在时快时慢，这个即可能是数据的分布不均导致可能存在时快时慢，这个即可能是数据的分布不均导致举例：做销售订单时，去判断此客户是否存在未结算订单。举例：做销售订单时，去判断此客户是否存在未结算订单。SQLSQL语句：语句：这个语句在做多数客户的时候没有感觉，但是在做某一两个客户时速度奇这个语句在做多数客户的时候没有感觉，但是在做某一两个客户时速度奇慢，查其执行计划，也使用了主表上的客户索引。慢，查其执行计划，也使用了主表上的客户索引。Select.From so_order,so_order_b,.where so_order.pk=so_order_b.pk and ccustomerid=:1 and.最后发现情况是这样的：企业是做批发业务的，政策法规上不允许直接销最后发现情况是这样的：企业是做批发业务的，政策法规上不允许直接销售给个人，内部职工需要购买时都是按照统一个特定的客户开票，因此数售给个人，内部职工需要购买时都是按照统一个特定的客户开票，因此数据库中数据严重不均，此客户的订单数量大于总数的据库中数据严重不均，此客户的订单数量大于总数的10%10%。一进行这样的。一进行这样的查询就严重占用数据库资源，导致系统相应速度慢。查询就严重占用数据库资源，导致系统相应速度慢。解决的办法：这种行为都是现金交易根本不需要进行检查，查询的结果永解决的办法：这种行为都是现金交易根本不需要进行检查，查询的结果永远是空。程序绕过这个检查就行了。远是空。程序绕过这个检查就行了。Step7:EXIST查询基本格式查询基本格式例：例：select count(*)from prm_adjustprice main,prm_adjustprice_b detail where main.cadjpriceid=detail.CADJPRICEID and main.cadjpriceid in(1,2)and exists(select*from bd_invmandoc where bd_invmandoc.SEALFLAG=N and detail.CINVENTORYID=bd_invmandoc.pk_invmandoc);不要将主表的其他条件写在子查询中，这样可能不要将主表的其他条件写在子查询中，这样可能使外层查询没有条件而进行全表扫描。使外层查询没有条件而进行全表扫描。如果想按照子查询中的表作为驱动表进行查询，如果想按照子查询中的表作为驱动表进行查询，需要改造成需要改造成ININ子句。子句。Select.From Select.From 主表主表 where where 主表条件主表条件 and exists(Select*from and exists(Select*from 子表子表 where where 子表条件子表条件 and and 主表关联字段子表主表关联字段子表PK)PK)Step8:IN查询基本格式查询基本格式例：例：select count(*)from prm_adjustprice main,prm_adjustprice_b detail where main.cadjpriceid=detail.CADJPRICEID and main.cadjpriceid in(1,2)and detail.CINVENTORYID in(select pk_invmandoc from bd_invmandoc where bd_invmandoc.SEALFLAG=N);如果不是想按照子查询中的表作为驱动表进行查如果不是想按照子查询中的表作为驱动表进行查询，建议改造成询，建议改造成EXISTEXIST子句，特别是自查询存在子句，特别是自查询存在较多重复数据时。较多重复数据时。Select.From Select.From 主表主表 where where 主表条件主表条件 and and 主表关联字段主表关联字段 in(in(Select Select子表子表PK from PK from 子表子表 where where 子表条件子表条件)Step9:NOT IN尽量不要使用尽量不要使用NOT INNOT INNOT INNOT IN都可以改造成都可以改造成NOT EXISTSNOT EXISTSNOT INNOT IN中子查询如果对某个记录返回空，整个结果集中子查询如果对某个记录返回空，整个结果集都会为空都会为空Step10:视图视图视图视图不要存在视图套视图情况不要存在视图套视图情况视图中不要使用视图中不要使用DistinctDistinct视图中希望作为条件的关联列和结果列，不要视图中希望作为条件的关联列和结果列，不要进行运算。如月份的加减。进行运算。如月份的加减。不要使用条件太复杂的视图。不要使用条件太复杂的视图。Step Over当所有的调整都无效时：当所有的调整都无效时：那就是算法的问题！那就是算法的问题！实例实例采购计划查询时，先查询出采购计划，然后查询执行数据，再追加到采购计采购计划查询时，先查询出采购计划，然后查询执行数据，再追加到采购计划数据中。查询执行数据划数据中。查询执行数据SQLSQL如下：如下：select planbill.cplanbid,sum(po_planexe.npraysnum),sumfrom(SELECT po_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.invclasscode FROM po_plan_h,po_plan_b,bd_invcl aa where po_plan_h.cplanhid=po_plan_b.cplanhid and po_plan_h.dr=0 and po_plan_b.dr=0 and aa.pk_invcl(+)=po_plan_b.cobj2id and po_plan_h.dprocessdate=planbill.dstartdate and po_planexe.denddate=planbill.denddate group by planbill.cplanbid;实例实例-续续1各表数据关系如下：各表数据关系如下：采购计划主表采购计划主表采购计划子表采购计划子表执行情况表执行情况表cobjid1cobjid2cobjid3部门部门ID存货分类存货分类ID存货存货ID存货分类表存货分类表实例实例-续续2查看执行计划如下：成本优化器模式下的计划，全是全表扫描查看执行计划如下：成本优化器模式下的计划，全是全表扫描实例实例-续续3第一反应：加规则提示第一反应：加规则提示 select/*+rule*/planbill.cplanbid,sum.好像有提高，只有一个好像有提高，只有一个FullScan了。但是这个计划执行起来更差，原因是：内部了。但是这个计划执行起来更差，原因是：内部驱动表驱动表bd_invcl到执行计划表没有用到任何条件，结果集是两个表的完全关联到执行计划表没有用到任何条件，结果集是两个表的完全关联实例实例-续续4看看内部的子查询情况看看内部的子查询情况 SELECT po_plan_b.cplanbid,aa.invclasscode FROM po_plan_h,po_plan_b,bd_invcl aa where po_plan_h.cplanhid=po_plan_b.cplanhid and po_plan_h.dr=0 and po_plan_b.dr=0 and aa.pk_invcl(+)=po_plan_b.cobj2id and po_plan_h.dprocessdate=planbill.dstartdate and po_planexe.denddate=planbill.denddate 这里的这里的po_planexe.cdeptid=planbill.cobj1id or planbill.cobj1id is null和和 po_planexe.cinvmandocid=planbill.cobj3id or planbill.cobj3id is null是无法使用执行表上的部门和存货索引。是无法使用执行表上的部门和存货索引。（部门上单独加索引？）（部门上单独加索引？）采购计划表上有一个逻辑是：存货和存货分类两个列是互斥的，且必有一个不为空采购计划表上有一个逻辑是：存货和存货分类两个列是互斥的，且必有一个不为空.我们可以我们可以使用这个逻辑将条件分开，而不是写在一起。使用这个逻辑将条件分开，而不是写在一起。and(po_planexe.cinvmandocid=planbill.cobj3id and planbill.cobj2id is null)or(po_planexe.cinvclassid=invclass.pk_invcl.)但是，对存货不需要存货分类表，而对存货分类因为可以不定义到最末级，因此必须关联两但是，对存货不需要存货分类表，而对存货分类因为可以不定义到最末级，因此必须关联两次存货分类表。次存货分类表。最后可行的解决办法是将两个查询最后可行的解决办法是将两个查询UNION达到解决问题达到解决问题实例实例-续续8先按照计划表上有存货的进行查询先按照计划表上有存货的进行查询select/*+rule*/planbill.cplanbid,.from(SELECT po_plan_b.cplanbid,po_plan_h.pk_corp,FROM po_plan_h,po_plan_b where po_plan_h.cplanhid=po_plan_b.cplanhid and po_plan_h.dr=0 and po_plan_b.dr=0 and po_plan_h.dprocessdate=planbill.dstartdate and po_planexe.denddate=planbill.denddate group by planbill.cplanbid实例实例-续续9执行计划如下，看到已经能得到比较优化的结果。查询时间执行计划如下，看到已经能得到比较优化的结果。查询时间1 1秒（秒（33053305行）行）实例实例-续续10按照存货分类查询的呢？按照编码进行按照存货分类查询的呢？按照编码进行likelike查询已证明比较慢。那么我们可以考虑建立查询已证明比较慢。那么我们可以考虑建立一个存货分类上下级关系的完全对照表，使用对照表关联。一个存货分类上下级关系的完全对照表，使用对照表关联。CREATE TABLE temp_inv2up(pkidNUMBER(10)NOT NULL,/主键主键ID用处不大用处不大 pk_invclCHAR(20)NOT NULL,/子分类子分类 pk_upinvclCHAR(20)NOT NULL,/上级分类上级分类 CONSTRAINT PK_temp_inv2up PRIMARY KEY(pkid);CREATE INDEX temp_inv2up_idxinv ON UAP0318.TEMP_INV2UP(PK_INVCL);CREATE INDEX TEMP_INV_UPIDX ON TEMP_INV2UP(PK_UPINVCL);create sequence temp_invseq start with 1;/生成主键生成主键ID用用/按照分类编码关联出上下级对照表数据按照分类编码关联出上下级对照表数据insert into temp_inv2up(pkid,pk_invcl,pk_upinvcl)select temp_invseq.nextval,bas.pk_invcl,up.pk_invcl from bd_invcl bas,bd_invcl up where bas.invclasscode like up.invclasscode|%;实例实例-续续11按照存货分类查询按照存货分类查询:select/*+rule*/planbill.cplanbid,sum.from(SELECT po_plan_b.cplanbid,po_plan_h.pk_corp,FROM po_plan_h,po_plan_b where po_plan_h.cplanhid=po_plan_b.cplanhid and po_plan_h.dr=0 and po_plan_b.dr=0 and po_plan_h.dprocessdate=planbill.dstartdate and po_planexe.denddate=planbill.denddate group by planbill.cplanbid;实例实例-续续12按照存货分类查询的执行计划按照存货分类查询的执行计划:效果不错。效果不错。实例实例-成果成果按照这种方式优化后，分别按照这种方式优化后，分别union前分别加前分别加rule提示，查询时间小于提示，查询时间小于2秒。秒。再去掉规则提示，让再去掉规则提示，让Oracle按照成本优化器解析，执行时间按照成本优化器解析，执行时间0.297秒。秒。代码业务逻辑并没有改变，执行结果也相同。！代码业务逻辑并没有改变，执行结果也相同。！OVER!演讲完毕，谢谢观看！