Oracle执行计划

www.ufsoft.comOracle执行计划解读培训教师：谢高兴时间：2006.4.27BEGINwww.ufsoft.comSQL执行过程1,解析SQL2,执行3,显示结果集4,转换字段数据•检查安全性•检查SQL语法•查询重新书写•创建执行计划•捆绑执行计划•执行执行计划•读取结果集www.ufsoft.com执行计划执行计划:Oracle内部的机器级代码,决定如何访问存储器，得到需要的结果集。执行计划的主要内容：访问方式，访问顺序。www.ufsoft.com得到执行计划的方式1.Explain（解释）2.Autotrace(自动跟踪)3.其他工具基本格式：explainplansetSTATEMENT_ID='testplan'ForSelect…..(Select,insert,update等数据操作语句均可)基本使用方式：SQLsetautotraceon;(SQLPLUS中使用)www.ufsoft.com准备：创建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));www.ufsoft.comAUTOTRACESQLsetautotraceon;SQLselect*fromdual;D-XExecutionPlan----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE10TABLEACCESS(FULL)OF'DUAL'Statistics----------------------------------------------------------0recursivecalls0dbblockgets3consistentgets0physicalreads0redosize372bytessentviaSQL*Nettoclient511bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)1rowsprocessedSQL统计信息执行计划结果数据www.ufsoft.com使用Explain1,删除上次解析数据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;www.ufsoft.com第一个执行计划对应SQL语句：select*fromdual;执行计划:OPERATIONOPTIONSOBJECT_NAMECOSTPOSITIONSELECTSTATEMENTTABLEACCESSFULLDUAL1www.ufsoft.com怎样看执行计划执行计划其实是一棵树，层次最深的最先执行，层次相同，上面的先执行。显示时已经按照层次缩进，因此从最里面的看起。最后一组就是驱动表。例：OPERATIONOPTIONSOBJECT_NAMECOSTSORTAGGREGATENESTEDLOOPS18HASHJOIN5TABLEACCESSFULLPRM_ADJUSTPRICE2TABLEACCESSFULLPRM_ADJUSTPRICE_B2TABLEACCESSBYINDEXROWIDBD_INVMANDOC1INDEXUNIQUESCANPK_BD_INVMANDOC驱动表selectcount(*)fromprm_adjustpricemain,prm_adjustprice_bdetailwheremain.cadjpriceid=detail.CADJPRICEIDandmain.DADJPRICEDATE='2004-04-18'anddetail.CINVENTORYIDin(selectpk_invmandocfrombd_invmandocwherebd_invmandoc.SEALFLAG='N');www.ufsoft.com表访问方式散列获取：全表扫描：ROWID访问：读取表的每一条记录，顺序地从第一个数据块开始知道结尾标志。ROWID包含记录的数据块号和数据块中的偏移量。因此它是获取一条记录的最快的方法。使用散列算法得到符号关键值的来确定记录所在的数据块。它能减少数据读入量，但是存在重新定位记录的问题，只能在静态表中使用。www.ufsoft.com索引访问方式快速全索引扫描：索引扫描：从索引中读取一个或多个ROWID。索引值通常按照升序方式扫描。有些查询可以不扫描表只扫描索引就能得到期望的查询结果。所有查询字段都在索引中指定;查询返回大于索引所有记录数的10%;进行Count(*)操作。Oracle索引KEYROWIDwww.ufsoft.com表连接操作11,嵌套循环连接-NESTEDLOOP两个表，一个小的内部表和一个外部表一般情况下速度较快，特别是中间结果集非常小的情况下速度快。外部表索引KEYROWID内部表外部表www.ufsoft.com表连接操作22,散列连接-HASHJOIN两个表，一个较小的驱动表和一个大表中间结果集非常大的情况下速度较快。Hash_Area_SizeRAM驱动表大表散列访问ROWIDRAM溢出使用临时表空间www.ufsoft.com表连接操作33,排序合并连接-MERGEJOIN两个表都使用全表扫描，分别进行排序，然后再合并成查询的结果集。极少情况适合。只有包含两个表的决大多数记录的查询适合。A表全表扫描B表全表扫描A表排序B表排序输出结果集合并www.ufsoft.comStep1:全表扫描对查询影响最大的就是全表扫描-TableAccessFull.OPERATIONOPTIONSOBJECT_NAMECOSTSORTAGGREGATENESTEDLOOPS18HASHJOIN5TABLEACCESSFULLPRM_ADJUSTPRICE2TABLEACCESSFULLPRM_ADJUSTPRICE_B2TABLEACCESSBYINDEXROWIDBD_INVMANDOC1INDEXUNIQUESCANPK_BD_INVMANDOC•计执行划中所有的全表扫描都值得怀疑.除非是数据量非常小的表•可能引起全表扫描的原因：表上没有索引没有WHERE条件对索引字段使用了内置函数，如To(Dcredate)=‘2005-02-15’Like操作符而参数以“%”开始表记录非常少www.ufsoft.comStep2:驱动表－1驱动表•最内层的驱动表是不是能在执行过程中得到最小的中间数据集？例如：查询销售订单明细，涉及三个表，订单主表，订单附表,存货基本档案表。条件中包含：订单日期等于某天和存货编码等于某个值。方案一：如果以订单主表为驱动表，可以使用日期上的索引过滤出所有的当天的订单，然后关联附表，最后再关联到存货档案表，过滤出相应存货。订单附表订单主表存货档案表日期索引主表PK索引存货主键索引www.ufsoft.comStep2:驱动表－2方案二：如果以存货档案为驱动表，可以使用编码上的索引过滤出所有存货，然后关联订单附表，得到所有存货符合条件的订单附表记录，最后再用订单附表上的主表主键关联主表，判断订单主表上的日期是否符合条件，最后得到结果集。订单附表订单主表存货档案表编码索引存货ID索引主表主键索引结论：如果按照存货去过滤，得到订单附表上符合条件记录会较少，认为方案二更优。www.ufsoft.comStep3:不该建的索引错误索引：•对于只有少数一个可能值的列，不应该建索引。如单据状态，单据主表上的部门人员，附表上的仓库，库存组织等。•索引不是越多越好。数据的插入，删除和修改都需要维护索引表，也是有成本的。•建议建索引的列。所有可能用做查询条件的日期列,客户列；附表上的主表主键列，存货列，来源单据列；基本档案的编码列等。www.ufsoft.comStep4:复合索引列顺序•对复合索引，应将重复值少的列作为先导列例如订单附表上有来源单据类型(srctype)和来源单据ID(srcid),建立索引的顺序应该是srcid,srctype.如果将srctype列作为先导列，可能在查询时，没有srcid的条件也使用这个索引扫描，其结果是扫描了大半个表，比全表扫描还慢。www.ufsoft.comStep5:非最优索引•对一个表存在多个索引的列作为条件，解析中可能使用了非最优索引使用提示，指定使用某个索引(存在SQL兼容问题，不推荐)•将索引尽可能多的使用条件使用内置函数或运算使不想使用的条件列失效。整型数据列+0字符型加trim函数例如：在进行调拨的时候需要判断这个仓库中此存货不存在没有记帐的出库单Selectcount(*)fromXXXwherepk_inv=:1andstatus=0这个查询会用到存货索引，但是速度还是慢。但是仔细分析会发现，其实实际运行中没记帐的出库单比例很少，如果在存货索引上再加上状态列，可以过滤更少的数据，速度将大大提高。www.ufsoft.comStep6:数据非均匀分布•对于某些SQL可能存在时快时慢，这个即可能是数据的分布不均导致举例：做销售订单时，去判断此客户是否存在未结算订单。SQL语句：这个语句在做多数客户的时候没有感觉，但是在做某一两个客户时速度奇慢，查其执行计划，也使用了主表上的客户索引。Select..Fromso_order,so_order_b,..whereso_order.pk=so_order_b.pkandccustomerid=:1and……..最后发现情况是这样的：企业是做批发业务的，政策法规上不允许直接销售给个人，内部职工需要购买时都是按照统一个特定的客户开票，因此数据库中数据严重不均，此客户的订单数量大于总数的10%。一进行这样的查询就严重占用数据库资源，导致系统相应速度慢。解决的办法：这种行为都是现金交易根本不需要进行检查，查询的结果永远是空。程序绕过这个检查就行了。www.ufsoft.comStep7:EXIST•查询基本格式例：selectcount(*)fromprm_adjustpricemain,prm_adjustprice_bdetailwheremain.cadjpriceid=detail.CADJPRICEIDa