Oracle如何分析执行计划.docx
- 文档编号:20185812
- 上传时间:2023-04-25
- 格式:DOCX
- 页数:15
- 大小:23.24KB
Oracle如何分析执行计划.docx
《Oracle如何分析执行计划.docx》由会员分享,可在线阅读,更多相关《Oracle如何分析执行计划.docx(15页珍藏版)》请在冰豆网上搜索。
Oracle如何分析执行计划
执行计划:
首先在分析的用户下执行rdbms\admin\utlxplan.sql
用sys用户登录:
sqlplus\admin\plustrace.sql
grantsqlplustouser_name;
1.找出耗费资源比较多的语句
SELECTADDRESS,
substr(SQL_TEXT,1,20)Text,
buffer_gets,
executions,
buffer_gets/executionsAVG
FROMv$sqlarea
WHEREexecutions>0
ANDbuffer_gets>100000
ORDERBY5;
2.如何分析执行计划:
SQL>SELECT*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_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:
放跟踪数据的文件的最大值,防止由于无意的疏忽,
使跟踪数据的文件占用整个硬盘,影响系统的正常运行
设置的方法:
SQL>execsys.dbms_system.set_bool_param_in_session(-
sid=>8,-
serial#=>3,-
parnam=>'timed_statistics',-
bval=>true);
SQL>altersystemsetuser_dump_dest='c:
\temp';
--注意这个语句会改变整个系统的跟踪文件存放的位置,所以我一般不改这个参数,而用系统的缺省值,要查看当前系统的该参数的值,可以用system用户登陆后:
SQL>showparameteruser_dump_dest
SQL>execsys.dbms_system.set_int_param_in_session(-
sid=>8,-
serial#=>3,-
parnam=>'max_dump_file_size',-
intval=>2147483647)
3)启动跟踪功能
SQL>execsys.dbms_system.set_sql_trace_in_session(8,3,true);
注意,只有跟踪的session再次发出sql语句后,才会产生trc文件
4)让系统运行一段时间,以便可以收集到跟踪数据
5)关闭跟踪功能
SQL>execsys.dbms_system.set_sql_trace_in_session(8,3,false);
6)格式化跟踪数据,得到我们易于理解的跟踪结果。
对产生的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:
CHOOSE
12TABLEACCESS(FULL)OF'EMP'
DELETEFROMRM$HASH_ELMS
callcountcpuelapseddiskquerycurrentrows
--------------------------------------------------
Parse20.000.000000
Execute2912.0412.616786685310819
Fetch00.000.000000
--------------------------------------------------
total3112.0412.616786685310819
Missesinlibrarycacheduringparse:
0
Optimizerhint:
CHOOSE
Parsinguserid:
9(DES12A)(recursivedepth:
3)
RowsExecutionPlan
----------------------------------------------------------
0DELETESTATEMENTHINT:
CHOOSE
16TABLEACCESS(FULL)OF'RM$HASH_ELMS'
下面对每个列进行说明:
call:
表示sql语句执行的每个阶段,每个sql语句的活动被分成以下3部分
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 如何 分析 执行 计划