Oracle:性能工具Explain plan、Autotrace、Tkprof(2008-06-25 16:52:26)

标签：oracle explain plan autotrace tkprof it

Oracle: 三个内置的性能工具包 Explain plan、Autotrace、Tkprof

欲先善其事，必先利其器。

最近同事总是问我如何调优SQL，有什么工具可以使用。在此介绍一下关于Oracle内置的三个性能工具包的安装、使用、以及一些基本技巧。

1、EXPLAIN PLAN：目的是给出一个特定SQL的查询计划。Oracle查询计划可以在逻辑上分析SQL语句的优劣。
安装：

相关文件

@$ORACLE_HOME/rdbms/admin/utlxplan.sql

@$ORACLE_HOME/rdbms/admin/utlxpls.sql

@$ORACLE_HOME/rdbms/admin/utlxplp.sql
1、sqlplus " / as sysdba"登录
2、@$ORACLE_HOME/rdbms/admin/utlxplan.sql
3、create public synonym plan_table for plan_table;
4、grant select ,insert,update ,delete on plan_table to public

注：这里没有考虑安全性，plan_table对所有人都开发了增删改查权限，如果需要有安全性，可以针对某一个USER创建一个plan_table;

使用：
@$ORACLE_HOME/rdbms/admin/utlxpls.sql

注：还有一个utlxplp.sql用于显示并行查询计划的内容；

分析报告说明：
以oracle的scott用户登录（这个用户是被锁定的，使用前使用alter user scott account unlock;解锁）。
SQL> explain plan for SELECT * FROM emp, dept WHERE emp.deptno = dept.deptno AND empno = 7782；
SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 117 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 117 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 87 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 409 | 12270 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
注意看Operation的“缩进”。这个语句被解析成一棵“树”。这课树大约是
1
/
2 4
/
3 5
上面的数字，和PLAN_TABLE_OUTPUT中的“Id”是对应的。简单来说，就是这个语句有2，4构成；2又由3构成；4由5构成。

详细的SQL查询计划可以参阅其他书籍，作为初学者可以仅仅关注一下报告中的“TABLE ACCESS FULL”。如果你有where子句还有这个TABLE ACCESS FULL出现，表明你缺少索引（这仅仅针对初学者，但是现实生活中却也能解决60－70％的问题）。

2、AUTOTRACE：目的是为了给出执行SQL时，实际进行了多少工作。
autotrace的特点在于简单。

SQL> set autotrace ?
用法: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

安装：
SQL> @?/sqlplus/admin/plustrce.sql
SQL> grant plustrace to public；
SQL> grant select_catalog_role to scott; （Oracle 的sqldeveloper需要执行此语句）

使用：
SQL> set autotrace on
SQL> 运行你的SQL.
能够看到如下内容。
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
32 consistent gets
0 physical reads
0 redo size
801 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
14 rows processed

统计（Statistics）内容说明：
recursive calls：本SQL语句所嵌套调用的SQL的数目。
db block gets：逻辑读；从高速缓存中读取的总块数。
consistent gets：逻辑读；一致性读。
physical reads：物理读；从数据文件中的读数据、或者从temp中的读数据。
redo size：重做数据的大小。
bytes sent via SQL*Net to client：
bytes received via SQL*Net from client：
SQL*Net roundtrips to/from client：
sorts (memory)：内存中的排序
sorts (disk)：交换到临时表空间中的排序
rows processed：受影响的行。
这些内容十分丰富，不是三言两语能够说明清楚的，有兴趣可以参见后面列出的参考书目。

3、TKPROF：将底层的跟踪文件转换成易懂的格式。
这东西无须安装，关键是如何启用它。
启用：
alter session set timed_statistics=true;
alter session set events '10046 trace name context forever, level 12';

关闭：

alter session set events '10046 trace name context off';

然后再执行你需要执行的SQL
获取跟踪文件名
select rtrim(c.value,'/')||'/'||d.instance_name||'_ora_'||ltrim(to_char(a.spid))||'.trc'
from v$process a, v$session b, v$parameter c, v$instance d
where a.addr=b.paddr
and b.audsid=sys_context('userenv','sessionid')
and c.name='user_dump_dest'
生成tkprof报告 tkprof /usr/...........................xxxx.trc tkprof.prf
查看分析报告
首先，“查找”找到你执行那个SQL。后面列出大致如下内容。
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.07 0.08 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 2.32 3.05 187 44994 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 2.40 3.14 187 44994 0 1

以及下面的内容，这是等待事件，非常有用。
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file sequential read 187 0.06 0.56
SQL*Net message from client 2 0.00 0.00
********************************************************************************

参见：《Oracle高效设计》 Thomas Kyte
－－我们不缺少书籍，缺少的是看书的人

[@more@]

ITPub博客

(转)oracle性能工具包Explain plan、Autotrace、Tkprof

最新文章