Oracle执行计划详细解读

Click to edit Master title style,Click to edit Master text styles,Second level,Third level,Fourth level,Fifth level,*,#,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),),);,AUTO TRACE,SQL,setautotraceon,;,;,SQL,select,*,*fromdual;,D,-,X,ExecutionPlan,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,0SELECT STATEMENTOptimizer,=,=CHOOSE,10TABLEACCESS,(,(FULL,),)OF DUAL,Statistics,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,0recursivecalls,0dbblockgets,3consistent gets,0physicalreads,0redo size,372bytes sentvia SQL*Nettoclient,511bytes received viaSQL,*,*Net fromclient,2SQL,*,*Net roundtripsto,/,/fromclient,0sorts,(,(memory),0sorts,(,(disk),1rows processed,SQL,统计信,息,息,执行计,划,划,结果数,据,据,使用Explain,1,删,除,除上次,解,解析数,据,据,2,执,行,行解析,deletefromplan_tablewhereSTATEMENT,_,_ID,=,=testplan,;,explain planset STATEMENT_ID=,testplan,forselect*fromdual;,3,显,示,示执行,计,计划,selectlpad(,5*,(,(level,-,-1),),)|operationoperation,options,object,_,_name,cost,position,from plan,_,_table,startwith id=0and STATEMENT_ID=,testplan,connect by prior id=parent_id,;,;,第一个,执,执行,计,计划,对应SQL语,句,句：,select,*,*from dual,;,;,执行计,划,划:,怎样看,执,执行计,划,划,执行计,划,划其实,是,是一棵,树,树，层,次,次最深,的,的最先,执,执行，,层,层次相,同,同，上,面,面的先,执,执行。,显,显示时,已,已经按,照,照层次,缩,缩进，,因,因此从,最,最里面,的,的看起,。,。最后,一,一组就,是,是驱动,表,表。例,：,：,驱动表,selectcount(*)fromprm,_,_adjustpricemain,prm,_,_adjustprice,_,_bdetail,wheremain.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:,复,复合索,引,引列顺,序,序,对复合,索,索引，,应,应将重,复,复值少,的,的列作,为,为先导,列,列,例如订,