Oracle如何分析执行计划Word格式.docx

1、3. 各个表之间是如何关联的 在执行计划中，需要知道哪个操作是先执行的，哪个操作是后执行的，这对于判断哪个表为驱动表有用处。判断之前，如果对表的访问是通过rowid，且该rowid的值是从索引扫描中得来得，则将该索引扫描先从执行计划中暂时去掉。然后在执行计划剩下的部分中，判断执行顺序的指导原则就是：最右、最上的操作先执行。4. 在RBO中，以from 子句中从右到左的顺序选择驱动表，即最右边的表为第一个驱动表但是，在RBO中，也是有一套规则来决定使用哪种连接类型和哪个表作为驱动表，在选择时肯定会考虑当前索引的情况，还可能会考虑where 中的限制条件，但是肯定是与where中限制条件的位置无关

2、。5. 在CBO中，如果没有统计信息，则在from子句中从左到右的顺序选择驱动表。如果用ordered它会按从左到右的顺序选择驱动表。但是如果对表或索引进行分析，则优化器会自动根据cost值决定采用哪种连接类型，这与where子句中各个限制条件的位置没有任何关系，如果想改变优化器选择的连接类型或驱动表，则要使用hints。CBO与RBO总结：在RBO中，以从右到左的顺序选择驱动表，即最右边的表为第一个驱动表，但是在RBO中也有一套规则来决定使用哪种连接类型和哪个表作为驱动表，在选择时肯定会考虑到当前索引的情况，还可能会考虑到where中的限制条件，但是肯定是与where中限制条件的位置无关。在

3、CBO中，如果没有统计信息，则以从右到左的顺序选择驱动表，但是如果对表或索引进行分析，则优化器会自动根据cost值决定采用哪种连接类型，与where子句中各个限制的条件位置没有任何关系，如果想改变优化器选择类型或驱动表，刚要使用hints.如果使用ordered它也会按从左到右的顺序选择驱动表。6. 下面我们来干预执行计划：使用hints提示我们可以用hints来实现：1） 使用优化器的类型2） 基于代价的优化器的优化目标，是all_rows还是first_rows3） 表的访问路径，是全表扫描还是索引扫描，还是直接利用rowid4） 表之间的连接类型5） 表之间的连接顺序6） 语句的并行程序

4、如何使用hints:Hints只应用在它们所在sql语句块（statement block，由select、update、delete关键字标识）上，对其它SQL语句或语句的其它部分没有影响。如：对于使用union操作的2个sql语句，如果只在一个sql语句上有hints，则该hints不会影响另一个sql语句。DELETE | INSERT | SELECT | UPDATE /*+ hint text hinttext.*/orDELETE | INSERT | SELECT | UPDATE -+ hint text hinttext.注解：1） DELETE、INSERT、SELECT

5、和UPDATE是标识一个语句块开始的关键字，包含提示的注释只能出现在这些关键字的后面，否则提示无效。2） “+”号表示该注释是一个hints，该加号必须立即跟在”/*”的后面，中间不能有空格。3） hint是下面介绍的具体提示之一，如果包含多个提示，则每个提示之间需要用一个或多个空格隔开。4） text 是其它说明hint的注释性文本如果你没有正确的指定hints，Oracle将忽略该hints，并且不会给出任何错误。下面是使用hints的例子：ORDERED提示指出了连接的顺序，也为不同的表指定了连接方法SELECT /*+ ORDERED INDEX （b, jl_br_balances_

6、n1） USE_NL （j b）USE_NL （glcc glf） USE_MERGE （gp gsb） */b.application_id, b.set_of_books_id ,b.personnel_id, p.vendor_id Personnel,p.segment1 PersonnelNumber, p.vendor_name NameFROM jl_br_journals j, jl_br_balances b,gl_code_combinations glcc, fnd_flex_values_vl glf,gl_periods gp, gl_sets_of_books gs

7、b, po_vendors pWHERE .6.1 指示优化器的方法与目标的hints：ALL_ROWS - 基于代价的优化器，以吞吐量为目标FIRST_ROWS（n） - 基于代价的优化器，以响应时间为目标CHOOSE - 根据是否有统计信息，选择不同的优化器RULE - 使用基于规则的优化器SELECT /*+ FIRST_ROWS（19） */ employ_id,empnameFROM employeesWHERE department_id = 20;6.2 指示存储路径的hints：FULL /*+ FULL （ table ） */指定该表使用全表扫描ROWID /*+ ROWI

8、D （ table ） */指定对该表使用rowid存取方法，该提示用的较少INDEX /*+ INDEX （ table index） */使用该表上指定的索引对表进行索引扫描INDEX_FFS /*+ INDEX_FFS （ table index） */使用快速全表扫描NO_INDEX /*+ NO_INDEX （ table index） */不使用该表上指定的索引进行存取，仍然可以使用其它的索引进行索引扫描SELECT /*+ FULL（e） */ emp_id,empnameFROM employees e;SELECT /*+ INDEX（A sex_index） use sex

9、_index because there are fewmale patients */ A.name, A.height, A.weightFROM patients AWHERE A.sex = m;6.3 指示连接顺序的hints:ORDERED /*+ ORDERED */按from 字句中表的顺序从左到右的连接STAR /*+ STAR */指示优化器使用星型查询SELECT /*+ORDERED */ o.order_id, c.customer_id, l.unit_price * l.quantityFROM customers c, order_items l, orders

10、 oWHERE c.cust_last_name = :b1AND o.customer_id = c.customer_idAND o.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指的是表的别名，而不是真实

11、的表名对对象进行分析： analyze table a compute statistices;analyze index inx_col12A compute statistics;2） 当CBO选择了一个次优化的执行计划时, 不要同CBO过意不去, 先采取如下措施:a） 检查是否在表与索引上又最新的统计数据b） 对所有的数据进行分析，而不是只分析一部分数据c） 检查是否引用的数据字典表，在oracle 10G之前，缺省情况下是不对数据字典表进行分析的。d） 试试RBO优化器，看语句执行的效率如何，有时RBO能比CBO产生的更好的执行计划e） 如果还不行，跟踪该语句的执行，生成trace信息

12、，然后用tkprof格式化trace信息，这样可以得到全面的供优化的信息。5） 如果一个row source 超过10000行数据，则可以被认为大row source6） 有（+）的表不是driving table，注意：如果有外联接，而且order hint指定的顺序与外联结决定的顺序冲突，则忽略order hint7. 如何通过跟踪一个客户端程序发出的sql的方法来优化SQL1） 识别要跟踪的客户端程序到数据库的连接（后面都用session代替），主要找出能唯一识别一个session的sid与serial#.2） 设定相应的参数，如打开时间开关（可以知道一个sql执行了多长时间），存放跟踪

13、数据的文件的位置、最大值。3） 启动跟踪功能4） 让系统运行一段时间，以便可以收集到跟踪数据5） 关闭跟踪功能6） 格式化跟踪数据，得到我们易于理解的跟踪结果。1） 识别要跟踪的客户端程序到数据库的数据库连接查询session信息（在sql*plus中运行）：set linesize 190col machine format a30 wrapcol program for a40col username format a15 wrapset pagesize 500select s.sid sid, s.SERIAL# serial#, s.username, s.machine, s.pr

14、ogram,p.spid ServPID, s.serverfrom v$session s, v$process pwhere p.addr = s.paddr ;上面的结果中比较有用的列为：sid, serial# : 这两个值联合起来唯一标识一个sessionusername : 程序连接数据库的用户名machine : 连接数据库的程序所在的机器的机器名，可以hostname得到program : 连接数据库的程序名，所有用java jdbc thin的程序的名字都一样，servpid : 与程序对应的服务器端的服务器进程的进程号，在unix下比较有用server : 程序连接数据库的

15、模式：专用模式（dedicaed）、共享模式（shared）。只有在专用模式下的数据库连接，对其进程跟踪才有效logon_time : 程序连接数据库的登陆时间根据machine, logon_time 可以方便的识别出一个数据库连接对应的session，从而得到该sesion的唯一标识sid, serial#, 为对该session进行跟踪做好准备2） 设定相应的参数参数说明：timed_statistics : 收集跟踪信息时，是否将收集时间信息，如果收集，则可以知道一个sql的各个执行阶段耗费的时间情况user_dump_dest : 存放跟踪数据的文件的位置max_dump_file_

16、size : 放跟踪数据的文件的最大值，防止由于无意的疏忽，使跟踪数据的文件占用整个硬盘，影响系统的正常运行设置的方法： exec sys.dbms_system.set_bool_param_in_session（ -sid = 8, -serial# = 3, -parnam = timed_statistics, -bval = true）; alter system set user_dump_dest=c:temp- 注意这个语句会改变整个系统的跟踪文件存放的位置，所以我一般不改这个参数，而用系统的缺省值，要查看当前系统的该参数的值，可以用system用户登陆后： show para

17、meter user_dump_dest exec sys.dbms_system.set_int_param_in_session（ -max_dump_file_sizeintval = 2147483647） exec sys.dbms_system.set_sql_trace_in_session（8, 3, true）;注意，只有跟踪的session再次发出sql语句后，才会产生trc文件 exec sys.dbms_system.set_sql_trace_in_session（8,3,false）;对产生的trace文件进行格式化：在命令提示符下，运行下面的命令tkprof ds

18、db2_ora_18468.trc dsdb2_trace.out SYS=NO EXPLAIN=SCOTT/TIGER其它使用tkprof的例子：（a） tkprof tracefile.trc sort_1.prf explain=apps/your_apps_password print=10 sort=（prsqry,exeqry,fchqry,prscu,execu,fchcu） （b） tkprof tracefile.trc sort_2.prf explain=apps/your_apps_password print=10 sort=（prsela,exeela,fchela

19、） （c） tkprof tracefile.trc sort_3.prf explain=apps/your_apps_password print=10 sort=（prscnt,execnt,fchcnt） （d） tkprof tracefile.trc normal.prf explain=apps/your_apps_password当在打开跟踪功能时发生了recursive calls，则tkprof也会产生这些recursive calls的统计信息，并清楚的在格式化输出文件中标名它们为recursive calls。recursive calls的统计数据是包含在recurs

20、ive calls上的，并不包含在引起该recursive calls语句的sql语句上面。所以计算一个sql语句耗费的资源时，也要考虑该sql语句引起recursive calls语句花费的资源。通过将sys参数设为no时，我们变可以在格式化的输出文件中屏蔽掉这些recursive calls信息。如何得到tkprof的帮助信息：运行tkprof时，不带任何参数，就可以得到该工具的帮助信息。TKPROF的使用语法：TKPROF command :=- TKPROF traced_file formatted_file -| |+- SORT = -+- OPTION -+| +- , -+

21、| V | |_（ OPTION ）_|-| | | | | |+- PRINT = integer -+ +- INSERT = filname3 -+ +- SYS = -+- YES -+- NO -+- EXPLAIN = user/password -+- TABLE = schema.table -+-+- RECORD = filname -+各个参数的含义： traced_file 指定输入文件，即oracle产生的trace文件，该文件中可以只包含一个session的跟踪信息，也可以包含系统中所有session的信息（此时需要在系统级进行跟踪）formatted_file指定

22、输出文件，即我们想得到的易于理解的格式化文件，我们利用该文件对会话运行的sql进行分析。EXPLAIN 利用哪个用户对trace文件中的sql进行分析，从而得到该sql语句的执行计划，这也说明在trace file中并没有各个sql语句的执行计划，只是在运行tkprof程序时才将trace file文件中的sql语句用explian参数指定的用户连接到数据库，然后运用EXPLAIN PLAN命令生成sql的执行计划。这个用户一般是你的程序中连接数据库的用户TABLE 在对sql语句进行分析时，将产生的执行计划暂时存放到该表中。一般不需要该参数，这样当表不存在时，tkprof会自动创建相应的表，

23、并在分析完之后，将创建的表自动删除。如果要指定自定义的表，该表的结构必须与utlxplan.sql文件中指定的表的结构一样。我一般不设置这个参数，让其采用默认的表名，并自动创建、删除SYS 是否对sys用户运行的sql语句或被跟踪session产生的recursive SQL也进行分析，并将分析结果放到输出文件中。缺省值为YES。我一般设为NO，这样输出文件中只包含我发出的sql语句，而不包含系统产生的sql。SORT： 按照指定的排序选项（条件）对格式化好的sql语句进行降序排列，然后存放到输出文件中。可以将多个排序选项组合起来，如果没有指定排序选项，则按照使用sql的先后顺序。PRINT只

24、列出指定数量的已排序的sql语句，排序的条件参见SORT参数。如果忽略此参数，tkprof将跟踪文件中的所有的sql语句及其相关的分析数据存放到输出文件中。Print与sort参数组合在一起，可以实现:找出某一阶段耗费cpu最多的前n个sql找出某一阶段读硬盘最多的前n个sql等等。INSERT： 创建一个sql脚本文件，里面包含create table 与insert语句。利用这个脚本文件创建一个表及插入数据后，可以得到跟踪文件中所有sql语句（包含recursive SQL）的统计信息RECORD： 创建一个包含客户端程序发出的所有的sql语句的脚本文件。注意，并不包含recursive

25、SQL 。想知道它的用处吗？对了可以窥探别人程序是如何访问数据库的，从而对了解程序的访问流程。此时，最好不用sort参数，这样就可以按先后发出的顺序的到sql.Misses in library cache during parse: 1Optimizer goal: CHOOSEParsing user id: 19 （SCOTT）Rows Row Source Operation- -12 TABLE ACCESS FULL EMPRows Execution Plan0 SELECT STATEMENT GOAL:12 TABLE ACCESS （FULL） OF EMPDELETE F

26、ROM RM$HASH_ELMScall count cpu elapsed disk query current rows- - - - - - - -Parse 2 0.00 0.00 0 0 0 0Execute 29 12.04 12.61 6786 6853 108 19Fetch 0 0.00 0.00 0 0 0 0total 31 12.04 12.61 6786 6853 108 19 0Optimizer hint: 9 （DES12A） （recursive depth: 3）0 DELETE STATEMENT HINT:16 TABLE ACCESS （FULL） OF RM$HASH_ELMS下面对每个列进行说明：call : 表示sql语句执行的每个阶段，每个sql语句的活动被分成以下3部分