Oracle如何分析执行计划Word格式.docx
《Oracle如何分析执行计划Word格式.docx》由会员分享，可在线阅读，更多相关《Oracle如何分析执行计划Word格式.docx（15页珍藏版）》请在冰点文库上搜索。
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_rows
3）表的访问路径，是全表扫描还是索引扫描，还是直接利用rowid
4）表之间的连接类型
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_nameName
FROMjl_br_journalsj,jl_br_balancesb,
gl_code_combinationsglcc,fnd_flex_values_vlglf,
gl_periodsgp,gl_sets_of_booksgsb,po_vendorsp
WHERE...
6.1指示优化器的方法与目标的hints：
ALL_ROWS--基于代价的优化器，以吞吐量为目标
FIRST_ROWS（n）--基于代价的优化器，以响应时间为目标
CHOOSE--根据是否有统计信息，选择不同的优化器
RULE--使用基于规则的优化器
SELECT/*+FIRST_ROWS（19）*/employ_id,empname
FROMemployees
WHEREdepartment_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,empname
FROMemployeese;
SELECT/*+INDEX（Asex_index）usesex_indexbecausetherearefew
malepatients*/A.name,A.height,A.weight
FROMpatientsA
WHEREA.sex='
m'
;
6.3指示连接顺序的hints:
ORDERED/*+ORDERED*/
按from字句中表的顺序从左到右的连接
STAR/*+STAR*/
指示优化器使用星型查询
SELECT/*+ORDERED*/o.order_id,c.customer_id,l.unit_price*l.quantity
FROMcustomersc,order_itemsl,orderso
WHEREc.cust_last_name=:
b1
ANDo.customer_id=c.customer_id
ANDo.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行数据，则可以被认为大rowsource
6）有（+）的表不是drivingtable，注意：
如果有外联接，而且orderhint指定的顺序与外联结决定的顺序冲突，则忽略orderhint
7.如何通过跟踪一个客户端程序发出的sql的方法来优化SQL
1）识别要跟踪的客户端程序到数据库的连接（后面都用session代替），主要找出能唯一识别一个session的sid与serial#.
2）设定相应的参数，如打开时间开关（可以知道一个sql执行了多长时间），存放跟踪数据的文件的位置、最大值。
3）启动跟踪功能
4）让系统运行一段时间，以便可以收集到跟踪数据
5）关闭跟踪功能
6）格式化跟踪数据，得到我们易于理解的跟踪结果。
1）识别要跟踪的客户端程序到数据库的数据库连接
查询session信息（在sql*plus中运行）：
setlinesize190
colmachineformata30wrap
colprogramfora40
colusernameformata15wrap
setpagesize500
selects.sidsid,s.SERIAL#"
serial#"
s.username,s.machine,s.program,
p.spidServPID,s.server
fromv$sessions,v$processp
wherep.addr=s.paddr;
上面的结果中比较有用的列为：
sid,serial#:
这两个值联合起来唯一标识一个session
username:
程序连接数据库的用户名
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:
放跟踪数据的文件的最大值，防止由于无意的疏忽，
使跟踪数据的文件占用整个硬盘，影响系统的正常运行
设置的方法：
execsys.dbms_system.set_bool_param_in_session（-
sid=>
8,-
serial#=>
3,-
parnam=>
'
timed_statistics'
-
bval=>
true）;
altersystemsetuser_dump_dest='
c:
\temp'
--注意这个语句会改变整个系统的跟踪文件存放的位置，所以我一般不改这个参数，而用系统的缺省值，要查看当前系统的该参数的值，可以用system用户登陆后：
showparameteruser_dump_dest
execsys.dbms_system.set_int_param_in_session（-
max_dump_file_size'
intval=>
2147483647）
execsys.dbms_system.set_sql_trace_in_session（8,3,true）;
注意，只有跟踪的session再次发出sql语句后，才会产生trc文件
execsys.dbms_system.set_sql_trace_in_session（8,3,false）;
对产生的trace文件进行格式化：
在命令提示符下，运行下面的命令
tkprofdsdb2_ora_18468.trcdsdb2_trace.outSYS=NOEXPLAIN=SCOTT/TIGER
其它使用tkprof的例子：
（a）tkproftracefile.trcsort_1.prfexplain=apps/your_apps_passwordprint=10sort='
（prsqry,exeqry,fchqry,prscu,execu,fchcu）'
（b）tkproftracefile.trcsort_2.prfexplain=apps/your_apps_passwordprint=10sort='
（prsela,exeela,fchela）'
（c）tkproftracefile.trcsort_3.prfexplain=apps/your_apps_passwordprint=10sort='
（prscnt,execnt,fchcnt）'
（d）tkproftracefile.trcnormal.prfexplain=apps/your_apps_password
当在打开跟踪功能时发生了recursivecalls，则tkprof也会产生这些recursivecalls的统计信息，并清楚的在格式化输出文件中标名它们为recursivecalls。
recursivecalls的统计数据是包含在recursivecalls上的，并不包含在引起该recursivecalls语句的sql语句上面。
所以计算一个sql语句耗费的资源时，也要考虑该sql语句引起recursivecalls语句花费的资源。
通过将sys参数设为no时，我们变可以在格式化的输出文件中屏蔽掉这些recursivecalls信息。
如何得到tkprof的帮助信息：
运行tkprof时，不带任何参数，就可以得到该工具的帮助信息。
TKPROF的使用语法：
TKPROFcommand:
:
=
>
--TKPROFtraced_fileformatted_file---------------------------------------------->
||
+-SORT=---------------------------------+
+--OPTION--+
|+----,----+|
|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'
指定输出文件，即我们想得到的易于理解的格式化文件，我们利用该文件对会话运行的sql进行分析。
EXPLAIN'
利用哪个用户对trace文件中的sql进行分析，从而得到该sql语句的执行计划，这也说明在tracefile中并没有各个sql语句的执行计划，只是在运行tkprof程序时才将tracefile文件中的sql语句用explian参数指定的用户连接到数据库，然后运用EXPLAINPLAN命令生成sql的执行计划。
这个用户一般是你的程序中连接数据库的用户
TABLE'
在对sql语句进行分析时，将产生的执行计划暂时存放到该表中。
一般不需要该参数，这样当表不存在时，tkprof会自动创建相应的表，并在分析完之后，将创建的表自动删除。
如果要指定自定义的表，该表的结构必须与utlxplan.sql文件中指定的表的结构一样。
我一般不设置这个参数，让其采用默认的表名，并自动创建、删除
SYS'
是否对sys用户运行的sql语句或被跟踪session产生的recursiveSQL也进行分析，并将分析结果放到输出文件中。
缺省值为YES。
我一般设为NO，这样输出文件中只包含我发出的sql语句，而不包含系统产生的sql。
SORT：
按照指定的排序选项（条件）对格式化好的sql语句进行降序排列，然后存放到输出文件中。
可以将多个排序选项组合起来，如果没有指定排序选项，则按照使用sql的先后顺序。
PRINT只列出指定数量的已排序的sql语句，排序的条件参见SORT参数。
如果忽略此参数，tkprof将跟踪文件中的所有的sql语句及其相关的分析数据存放到输出文件中。
Print与sort参数组合在一起，可以实现:
找出某一阶段耗费cpu最多的前n个sql找出某一阶段读硬盘最多的前n个sql等等。
INSERT：
创建一个sql脚本文件，里面包含createtable与insert语句。
利用这个脚本文件创建一个表及插入数据后，可以得到跟踪文件中所有sql语句（包含recursiveSQL）的统计信息
RECORD：
创建一个包含客户端程序发出的所有的sql语句的脚本文件。
注意，并不包含recursiveSQL。
想知道它的用处吗？
对了可以窥探别人程序是如何访问数据库的，从而对了解程序的访问流程。
此时，最好不用sort参数，这样就可以按先后发出的顺序的到sql.
Missesinlibrarycacheduringparse:
1
Optimizergoal:
CHOOSE
Parsinguserid:
19（SCOTT）
RowsRowSourceOperation
----------------------------------------------------------
12TABLEACCESSFULLEMP
RowsExecutionPlan
0SELECTSTATEMENTGOAL:
12TABLEACCESS（FULL）OF'
EMP'
DELETEFROMRM$HASH_ELMS
callcountcpuelapseddiskquerycurrentrows
--------------------------------------------------
Parse20.000.000000
Execute2912.0412.616786685310819
Fetch00.000.000000
total3112.0412.616786685310819
0
Optimizerhint:
9（DES12A）（recursivedepth:
3）
0DELETESTATEMENTHINT:
16TABLEACCESS（FULL）OF'
RM$HASH_ELMS'
下面对每个列进行说明：
call:
表示sql语句执行的每个阶段，每个sql语句的活动被分成以下3部分
升级会员 