执行计划：
首先在分析的用户下执行rdbms\admin\utlxplan.sql
用sys用户登录：sqlplus\admin\plustrace.sql
grant sqlplus to user_name;
1. 找出耗费资源比较多的语句
SELECT ADDRESS,
substr(SQL_TEXT,1,20) Text,
buffer_gets,
executions,
buffer_gets/executions AVG
FROM v$sqlarea
WHERE executions>0
AND buffer_gets > 100000
ORDER BY 5;
2. 如何分析执行计划：
SQL> SELECT * FROM LARGE_TABLE where USERNAME = ‘TEST’;
Query Plan
-----------------------------------------
SELECT STATEMENT Optimizer=CHOOSE (Cost=1234 Card=1 Bytes=14)
TABLE ACCESS FULL LARGE_TABLE [:Q65001] [ANALYZED]
TABLE ACCESS FULL large_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语句块(statement block，由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 /*+ ORDERED INDEX (b, jl_br_balances_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 Name
FROM jl_br_journals j, jl_br_balances b,
gl_code_combinations glcc, fnd_flex_values_vl glf,
gl_periods gp, gl_sets_of_books gsb, po_vendors p
WHERE ...
6.1 指示优化器的方法与目标的hints：
ALL_ROWS -- 基于代价的优化器，以吞吐量为目标
FIRST_ROWS(n) -- 基于代价的优化器，以响应时间为目标
CHOOSE -- 根据是否有统计信息，选择不同的优化器
RULE -- 使用基于规则的优化器
SELECT /*+ FIRST_ROWS(19) */ employ_id,empname
FROM employees
WHERE department_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
FROM employees e;
SELECT /*+ INDEX(A sex_index) use sex_index because there are few male patients */ A.name, A.height, A.weight
FROM patients A
WHERE A.sex = 'm';
6.3 指示连接顺序的hints:
ORDERED /*+ ORDERED */
按from 字句中表的顺序从左到右的连接
STAR /*+ STAR */
指示优化器使用星型查询
SELECT /*+ORDERED */ o.order_id, c.customer_id, l.unit_price * l.quantity
FROM customers c, order_items l, orders o
WHERE c.cust_last_name = :b1
AND o.customer_id = c.customer_id
AND 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指的是表的别名，而不是真实的表名
对对象进行分析： 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信息，然后用tkprof格式化trace信息，这样可以得到全面的供优化的信息。
5) 如果一个row source 超过10000行数据，则可以被认为大row source
6) 有(+)的表不是driving table，注意：如果有外联接，而且order hint指定的顺序与外联结决定的顺序冲突，则忽略order hint
7. 如何通过跟踪一个客户端程序发出的sql的方法来优化SQL
1) 识别要跟踪的客户端程序到数据库的连接(后面都用session代替)，主要找出能唯一识别一个session的sid与serial#.
2) 设定相应的参数，如打开时间开关(可以知道一个sql执行了多长时间)，存放跟踪数据的文件的位置、最大值。
3) 启动跟踪功能
4) 让系统运行一段时间，以便可以收集到跟踪数据
5) 关闭跟踪功能
6) 格式化跟踪数据，得到我们易于理解的跟踪结果。
1) 识别要跟踪的客户端程序到数据库的数据库连接
查询session信息(在sql*plus中运行)：
set linesize 190
col machine format a30 wrap
col program for a40
col username format a15 wrap
set pagesize 500
select s.sid sid, s.SERIAL# "serial#", s.username, s.machine, s.program,
p.spid ServPID, s.server
from v$session s, v$process p
where p.addr = s.paddr ;
上面的结果中比较有用的列为：
sid, serial# : 这两个值联合起来唯一标识一个session
username : 程序连接数据库的用户名
machine : 连接数据库的程序所在的机器的机器名，可以hostname得到
program : 连接数据库的程序名，所有用java jdbc thin的程序的名字都一样，
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> exec sys.dbms_system.set_bool_param_in_session( -
sid => 8, -
serial# => 3, -
parnam => 'timed_statistics', -
bval => true);
SQL> alter system set user_dump_dest='c:\temp';
-- 注意这个语句会改变整个系统的跟踪文件存放的位置，所以我一般不改这个参数，而用系统的缺省值，要查看当前系统的该参数的值，可以用system用户登陆后：
SQL> show parameter user_dump_dest
SQL> exec sys.dbms_system.set_int_param_in_session( -
sid => 8, -
serial# => 3, -
parnam => 'max_dump_file_size', -
intval => 2147483647)
3) 启动跟踪功能
SQL> exec sys.dbms_system.set_sql_trace_in_session(8, 3, true);
注意，只有跟踪的session再次发出sql语句后，才会产生trc文件
4) 让系统运行一段时间，以便可以收集到跟踪数据
5) 关闭跟踪功能
SQL> exec sys.dbms_system.set_sql_trace_in_session(8,3,false);
6) 格式化跟踪数据，得到我们易于理解的跟踪结果。
对产生的trace文件进行格式化：
在命令提示符下，运行下面的命令
tkprof dsdb2_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)' (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的统计数据是包含在recursive calls上的，并不包含在引起该recursive calls语句的sql语句上面。所以计算一个sql语句耗费的资源时，也要考虑该sql语句引起recursive calls语句花费的资源。通过将sys参数设为no时，我们变可以在格式化的输出文件中屏蔽掉这些recursive calls信息。
如何得到tkprof的帮助信息：
运行tkprof时，不带任何参数，就可以得到该工具的帮助信息。
TKPROF的使用语法：
TKPROF command ::=
>>-- TKPROF traced_file formatted_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语句的执行计划，这也说明在trace file中并没有各个sql语句的执行计划，只是在运行tkprof程序时才将trace file文件中的sql语句用explian参数指定的用户连接到数据库，然后运用EXPLAIN PLAN命令生成sql的执行计划。这个用户一般是你的程序中连接数据库的用户
'TABLE' 在对sql语句进行分析时，将产生的执行计划暂时存放到该表中。一般不需要该参数，这样当表不存在时，tkprof会自动创建相应的表，并在分析完之后，将创建的表自动删除。如果要指定自定义的表，该表的结构必须与utlxplan.sql文件中指定的表的结构一样。我一般不设置这个参数，让其采用默认的表名，并自动创建、删除
'SYS' 是否对sys用户运行的sql语句或被跟踪session产生的recursive SQL也进行分析，并将分析结果放到输出文件中。缺省值为YES。我一般设为NO，这样输出文件中只包含我发
出的sql语句，而不包含系统产生的sql。
SORT： 按照指定的排序选项(条件)对格式化好的sql语句进行降序排列，然后存放到输出文件中。可以将多个排序选项组合起来，如果没有指定排序选项，则按照使用sql的先后顺序。
PRINT只列出指定数量的已排序的sql语句，排序的条件参见SORT参数。如果忽略此参数，tkprof将跟踪文件中的所有的sql语句及其相关的分析数据存放到输出文件中。Print与sort参数组合在一起，可以实现:找出某一阶段耗费cpu最多的前n个sql找出某一阶段读硬盘最多的前n个sql等等。
INSERT： 创建一个sql脚本文件，里面包含create table 与insert语句。利用这个脚本文件创建一个表及插入数据后，可以得到跟踪文件中所有sql语句(包含recursive SQL)的统计信息
RECORD： 创建一个包含客户端程序发出的所有的sql语句的脚本文件。注意，并不包含recursive SQL 。想知道它的用处吗？对了可以窥探别人程序是如何访问数据库的，从而对了解程序的访问流程。此时，最好不用sort参数，这样就可以按先后发出的顺序的到sql.
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 19 (SCOTT)
Rows Row Source Operation
------- ---------------------------------------------------
12 TABLE ACCESS FULL EMP
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
12 TABLE ACCESS (FULL) OF 'EMP'
DELETE FROM RM$HASH_ELMS
call count cpu elapsed disk query current rows
------- ------ ------ -------- ----- ------ -------- ----
Parse 2 0.00 0.00 0 0 0 0
Execute 29 12.04 12.61 6786 6853 108 19
Fetch 0 0.00 0.00 0 0 0 0
------- ------ ------ -------- ----- ------ -------- ----
total 31 12.04 12.61 6786 6853 108 19
Misses in library cache during parse: 0
Optimizer hint: CHOOSE
Parsing user id: 9 (DES12A) (recursive depth: 3)
Rows Execution Plan
------- ---------------------------------------------------
0 DELETE STATEMENT HINT: CHOOSE
16 TABLE ACCESS (FULL) OF 'RM$HASH_ELMS'
下面对每个列进行说明：
call : 表示sql语句执行的每个阶段，每个sql语句的活动被分成以下3部分
Parse: 语句被解析的次数，主要是生成执行计划。包含hard parse与soft parse。需要做的工作：权限检查，表、列、引用的表的存在性检查；比较执行计划，选出最好的一个等等。
Execute: 真正执行语句的时间，对于DML语句，在此阶段中修改数据；对于select语句，这步只是标识出查询出的行。
Fetch : 只对select语句有效，DML语句的执行并没有该阶段其它列的值都是与sql执行三个阶段中所耗费的资源的统计值
COUNT: 一个语句被parsed、executed、fetched的次数
CPU: 执行这个语句的每个阶段耗费的cpu时间
ELAPSED: 执行这个语句的每个阶段耗费的总时间(包括上面的cpu时间与其它时间，如读数据)
DISK: 每个阶段读硬盘的次数(有可能从file system buffer中取得数据)对于该参数，我们希望该值越小越好，如果该值很大，该sql需要调整，建立相关索引或看是否正确的使用了索引
QUERY: 每个阶段以consistent mode 方式从数据库buffer中查询的buffers数。对于查询，其buffer一般都是以consistent mode模式被读取
CURRENT: 每个阶段以current mode方式从数据库buffer中查询的buffers数。Buffers are often对于DML语句，需要的buffer是以current mode模式被读取的。QUERY + CURRENT 的和是该sql语句总的存取的buffer数目
ROWS:这个sql语句最后处理的行数，不包括子查询中查询出来的行数。对于select语句，该值产生于fetch阶段；对于dml该值产生于execute阶段。
1. 是否发生过量的parsing
2. 高速缓存的命中率
3. fetch的次数与rows小，可以高效地取得查询数据
4. 读数据字典告诉缓存的次数
第二步 – 检查耗费大量资源的语句
upadte ...
where ...
1. 需要访问多少个数据块才能找到我们修改的数据
2. 修改了多少个数据块
3. 修改了几行数据
第三步 - 查看是否有过量的parse现象
select ...
1.
格式化sql语句tkprof
【格式化输出文件最后部分，即汇总部分】
检查是否有过量的parsing现象：SQL statements in session(54)与parsing(7)比较，执行54个语句分析7次
命中率：1 - ([execute与fetch中]disk的和 / [execute与fetch中]query的和 + [execute与fetch中]current的和))
在取结果的时候fetch的次数要比rows的次数少最好
parase中的disk：为了对语句进行分析，读数据字典告诉缓存的次数，这个值影响不大，不用关心，它的值不是我们能控制的
【检查耗费大量资源的语句】
update ... where
在execute 中的query中的数据，表明需要访问多少个数据块才能找到我们需要修改的数据 在execute 中的current中的数据，表明我们的修改操作才修改多少个数据块中的数据
在execute 中的rows中的数据，表明我们只修改了一行数据（其它数据块的修改应为undo,redo信息）
【检查是否有过量的parse现象】
select ...
在 fetch 中的rows中的数据，表明这个查询只反回一行数据
在 fetch 中的query中的数据，表明我们需要fetch多少次才能得到我们
在 parse 中的count中的数据，表明我们进行了几次parse（包含hard parse 与soft parse）--这是我们不想看到的，特别是当parse阶段操作耗费cpu资源比execute阶段耗费的cpu资源 Misses in library cache during parse:2 在这个后面的值（2），如果它为1这个语句有一个hard parse然后跟着一个soft parse（仅仅从库缓存中得到上次分析的信息，比hard parse 要高效的多）
本文来自CSDN博客，转载请标明出处：/ailandle/archive/2009/03/23/4016025.aspx