Oracle如何分析执行计划

执行计划：首先在分析的用户下执行rdbms\admin\utlxplan.sql用sys用户登录：sqlplus\admin\plustrace.sqlgrantsqlplustouser_name;1.找出耗费资源比较多的语句SELECTADDRESS,substr(SQL_TEXT,1,20)Text,buffer_gets,executions,buffer_gets/executionsAVGFROMv$sqlareaWHEREexecutions0ANDbuffer_gets100000ORDERBY5;2.如何分析执行计划：SQLSELECT*FROMLARGE_TABLEwhereUSERNAME=‘TEST’;QueryPlan-----------------------------------------SELECTSTATEMENTOptimizer=CHOOSE(Cost=1234Card=1Bytes=14)TABLEACCESSFULLLARGE_TABLE[:Q65001][ANALYZED]TABLEACCESSFULLlarge_table:在large_table上做全表扫描[:Q65001]表明该部分查询是以并行方式运行的。[ANALYZED]表明操作中引用的对象被分析过了，在数据字典中有该对象的统计信息可以供CBO使用。3.各个表之间是如何关联的在执行计划中，需要知道哪个操作是先执行的，哪个操作是后执行的，这对于判断哪个表为驱动表有用处。判断之前，如果对表的访问是通过rowid，且该rowid的值是从索引扫描中得来得，则将该索引扫描先从执行计划中暂时去掉。然后在执行计划剩下的部分中，判断执行顺序的指导原则就是：最右、最上的操作先执行。4.在RBO中，以from子句中从右到左的顺序选择驱动表，即最右边的表为第一个驱动表但是，在RBO中，也是有一套规则来决定使用哪种连接类型和哪个表作为驱动表，在选择时肯定会考虑当前索引的情况，还可能会考虑where中的限制条件，但是肯定是与where中限制条件的位置无关。5.在CBO中，如果没有统计信息，则在from子句中从左到右的顺序选择驱动表。如果用ordered它会按从左到右的顺序选择驱动表。但是如果对表或索引进行分析，则优化器会自动根据cost值决定采用哪种连接类型，这与where子句中各个限制条件的位置没有任何关系，如果想改变优化器选择的连接类型或驱动表，则要使用hints。CBO与RBO总结：在RBO中，以从右到左的顺序选择驱动表，即最右边的表为第一个驱动表，但是在RBO中也有一套规则来决定使用哪种连接类型和哪个表作为驱动表，在选择时肯定会考虑到当前索引的情况，还可能会考虑到where中的限制条件，但是肯定是与where中限制条件的位置无关。在CBO中，如果没有统计信息，则以从右到左的顺序选择驱动表，但是如果对表或索引进行分析，则优化器会自动根据cost值决定采用哪种连接类型，与where子句中各个限制的条件位置没有任何关系，如果想改变优化器选择类型或驱动表，刚要使用hints.如果使用ordered它也会按从左到右的顺序选择驱动表。6.下面我们来干预执行计划：使用hints提示我们可以用hints来实现：1）使用优化器的类型2）基于代价的优化器的优化目标，是all_rows还是first_rows3）表的访问路径，是全表扫描还是索引扫描，还是直接利用rowid4）表之间的连接类型5）表之间的连接顺序6）语句的并行程序如何使用hints:Hints只应用在它们所在sql语句块(statementblock，由select、update、delete关键字标识)上，对其它SQL语句或语句的其它部分没有影响。如：对于使用union操作的2个sql语句，如果只在一个sql语句上有hints，则该hints不会影响另一个sql语句。{DELETE|INSERT|SELECT|UPDATE}/*+hint[text][hint[text]]..*/or{DELETE|INSERT|SELECT|UPDATE}--+hint[text][hint[text]]...注解：1)DELETE、INSERT、SELECT和UPDATE是标识一个语句块开始的关键字，包含提示的注释只能出现在这些关键字的后面，否则提示无效。2)“+”号表示该注释是一个hints，该加号必须立即跟在”/*”的后面，中间不能有空格。3)hint是下面介绍的具体提示之一，如果包含多个提示，则每个提示之间需要用一个或多个空格隔开。4)text是其它说明hint的注释性文本如果你没有正确的指定hints，Oracle将忽略该hints，并且不会给出任何错误。下面是使用hints的例子：ORDERED提示指出了连接的顺序，也为不同的表指定了连接方法SELECT/*+ORDEREDINDEX(b,jl_br_balances_n1)USE_NL(jb)USE_NL(glccglf)USE_MERGE(gpgsb)*/b.application_id,b.set_of_books_id,b.personnel_id,p.vendor_idPersonnel,p.segment1PersonnelNumber,p.vendor_nameNameFROMjl_br_journalsj,jl_br_balancesb,gl_code_combinationsglcc,fnd_flex_values_vlglf,gl_periodsgp,gl_sets_of_booksgsb,po_vendorspWHERE...6.1指示优化器的方法与目标的hints：ALL_ROWS--基于代价的优化器，以吞吐量为目标FIRST_ROWS(n)--基于代价的优化器，以响应时间为目标CHOOSE--根据是否有统计信息，选择不同的优化器RULE--使用基于规则的优化器SELECT/*+FIRST_ROWS(19)*/employ_id,empnameFROMemployeesWHEREdepartment_id=20;6.2指示存储路径的hints：FULL/*+FULL(table)*/指定该表使用全表扫描ROWID/*+ROWID(table)*/指定对该表使用rowid存取方法，该提示用的较少INDEX/*+INDEX(table[index])*/使用该表上指定的索引对表进行索引扫描INDEX_FFS/*+INDEX_FFS(table[index])*/使用快速全表扫描NO_INDEX/*+NO_INDEX(table[index])*/不使用该表上指定的索引进行存取，仍然可以使用其它的索引进行索引扫描SELECT/*+FULL(e)*/emp_id,empnameFROMemployeese;SELECT/*+INDEX(Asex_index)usesex_indexbecausetherearefewmalepatients*/A.name,A.height,A.weightFROMpatientsAWHEREA.sex='m';6.3指示连接顺序的hints:ORDERED/*+ORDERED*/按from字句中表的顺序从左到右的连接STAR/*+STAR*/指示优化器使用星型查询SELECT/*+ORDERED*/o.order_id,c.customer_id,l.unit_price*l.quantityFROMcustomersc,order_itemsl,ordersoWHEREc.cust_last_name=:b1ANDo.customer_id=c.customer_idANDo.order_id=l.order_id;6.4指示连接类型的hints：USE_NL/*+USE_NL(table[,table,...])*/使用嵌套连接USE_MERGE/*+USE_MERGE(table[,table,...])*/使用排序--合并连接USE_HASH/*+USE_HASH(table[,table,...])*/使用HASH连接注意：如果表有alias(别名)，则上面的table指的是表的别名，而不是真实的表名对对象进行分析：analyzetableacomputestatistices;analyzeindexinx_col12Acomputestatistics;2)当CBO选择了一个次优化的执行计划时,不要同CBO过意不去,先采取如下措施:a)检查是否在表与索引上又最新的统计数据b)对所有的数据进行分析，而不是只分析一部分数据c)检查是否引用的数据字典表，在oracle10G之前，缺省情况下是不对数据字典表进行分析的。d)试试RBO优化器，看语句执行的效率如何，有时RBO能比CBO产生的更好的执行计划e)如果还不行，跟踪该语句的执行，生成trace信息，然后用tkprof格式化trace信息，这样可以得到全面的供优化的信息。5)如果一个rowsource超过10000行数据，则可以被认为大rowsource6)有(+)的表不是drivingtable，注意：如果有外联接，而且orderhint指定的顺序与外联结决定的顺序冲突，则忽略orderhint7.如何通过跟踪一个客户端程序发出的sql的方法来优化SQL1)识别要跟踪的客户端程序到数据库的连接(后面都用session代替)，主要找出能唯一识别一个session的sid与serial#.2)设定相应的参数，如打开时间开关(可以知道一个sql执行了多长时间)，存放跟踪数据的文件的位置、最大值。3)启动跟踪功能4)让系统运行一段时间，以便可以收集到跟踪数据5)关闭跟踪功能6)格式化跟踪数据，得到我们易于理解的跟踪结果。1)识别要跟踪的客户端程序到数据库的数据库连接查询session信息(在sql*plus中运行)：setlinesize190colmachineformata30wrapcolprogramfora40colusernameformata15wrapsetpagesize500selects.sidsid,s.SERIAL#serial#,s.username,s.machine,s.program,p.spidServPID,s.serverfromv$sessions,v$processpwherep.addr=s.paddr;上面的结果中比较有用的列为：sid,serial#:这两个值联合起来唯一标识一个sessionusername:程序连接数据库的用户名machine:连接数据库的程序所在的机器的机器名，可以hostname得到program:连接数据库的程序名，所有用javajdbcthin的程序的名字都一样，servpid:与程序对应的服务器端的服务器进程的进程号，在unix下比较有用server:程序连接数据库的模式：专用模式(dedicaed)、共享模式(shared)。只有在专用模式下的数据库连接，对其进程跟踪才有效logon_time:程序连接数据库的登陆时间根据machine,logon_time可以方便的识别出一个数据库连接对应的session，从而得到该sesion的唯一标识sid,serial#,为对该session进行跟踪做好准备2)设定相应的参数参数说明：timed_statistics:收集跟踪信息时，是否将收集时间信息，如果收集，则可以知道一个sql的各个执行阶段耗费的时间情况user_dump_dest:存放跟踪数据的文件的位置max_dump_file_size:放跟踪数据的文件的最大值，防止由于无意的疏忽，使跟踪数据的文件占用整个硬盘，影响系统的正常运行设置的方法：SQLexecsys.dbms_system.set_bool_param_in_session(-sid=8,-serial#=3,-parnam='timed_statistics