ORACLE数据库查看执行计划的方法（2）

3: 在SQL*PLUS下(有些命令在PL/SQL下无效)执行如下命令：
代码如下:


SQL>SET TIMING ON 　　　　　　　　　　　　--控制显示执行时间统计数据
SQL>SET AUTOTRACE ON EXPLAIN 　　　 　　--这样设置包含执行计划、脚本数据输出，没有统计信息
SQL>执行需要查看执行计划的SQL语句
SQL>SET AUTOTRACE OFF 　　　　　　　　　 --不生成AUTOTRACE报告，这是缺省模式
SQL> SET AUTOTRACE ON 　　　　　　　　　 --这样设置包含执行计划、统计信息、以及脚本数据输出
SQL>执行需要查看执行计划的SQL语句
SQL>SET AUTOTRACE OFF
SQL> SET AUTOTRACE TRACEONLY 　　　　　--这样设置会有执行计划、统计信息，不会有脚本数据输出
SQL>执行需要查看执行计划的SQL语句
SQL>SET AUTOTRACE TRACEONLY STAT --这样设置只包含有统计信息
SQL>执行需要查看执行计划的SQL语句

SET AUTOT[RACE] {ON | OFF | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

参考文档：SQLPlus User's Guide and Reference Release 11.1

注意：PL/SQL Developer 工具并不完全支持所有的SQL*Plus命令，像SET AUTOTRACE ON 就如此，在PL/SQL Developer工具下执行此命令会报错

SQL> SET AUTOTRACE ON;

Cannot SET AUTOTRACE

4：SQL_TRACE可以作为参数在全局启用，也可以通过命令形式在具体SESSION启用

4.1 在全局启用，在参数文件（pfile/spfile)中指定SQL_TRACE =true，在全局启用SQL_TRACE时会导致所有进程活动被跟踪，包括后台进程以及用户进程，通常会导致比较严重的性能问题，所以在生产环境要谨慎使用。

提示：通过在全局启用SQL_TRACE， 我们可以跟踪到所有后台进程的活动，很多在文档中的抽象说明，通过跟踪文件的实时变化，我们可以清晰的看到各个进程间的紧密协调。

4.2在当前SESSION级别设置，通过跟踪当前进程可以发现当前操作的后台数据库递归活动（这在研究数据库新特性时尤其有效），研究SQL执行时，发现后台

错误等。

 代码如下:


SQL> ALTER SESSION SET SQL_TRACE=TRUE;
SQL> SELECT * FROM SCOTT.EMP;
SQL> ALTER SESSION SET SQL_TRACE =FALSE;


那么此时如何查看相关信息？不管你在SQL*PLUS抑或PL/SQL DEVELOPER工具里面执行上面脚本过后都看不到什么信息，你可以通过下面脚本查询到trace日志信息

代码如下:


SELECT T.VALUE || '/' || LOWER(RTRIM(I.INSTANCE, CHR(0))) || '_ora_' ||
P.SPID || '.trc' TRACE_FILE_NAME
FROM
( SELECT P.SPID
FROM V$MYSTAT M, V$SESSION S, V$PROCESS P
WHERE M.STATISTIC# =1
AND S.SID = M.SID
AND P.ADDR = S.PADDR
) P,
( SELECT T.INSTANCE
FROM V$THREAD T, V$PARAMETER V
WHERE V.NAME ='thread'
AND (V.VALUE = 0 OR T.THREAD# = TO_NUMBER(V.VALUE))
) I,
(SELECT VALUE FROM V$PARAMETER WHERE NAME='user_dump_dest') T

TKPROF的帮助信息如下

代码如下:


TKPROF 选项
选项 说明
TRACEFILE 跟踪输出文件的名称
OUTPUTFILE 已设置格式的文件的名称
SORT=option 语句的排序顺序
PRINT=n 打印前 n 个语句
EXPLAIN=user/password 以指定的用户名运行 EXPLAIN PLAN
INSERT=filename 生成 INSERT 语句
SYS=NO 忽略作为用户 sys 运行的递归 SQL 语句
AGGREGATE=[Y|N] 如果指定 AGGREGATE = NO TKPROF 不聚集相同
SQL 文本的多个用户
RECORD=filename 记录在跟踪文件中发现的语句
TABLE=schema.tablename 将执行计划放入指定的表而不是缺省的PLAN_TABLE

可以在操作系统中键入 tkprof 以获得所有可用选项和输出的列表
注 排序选项有

排序 选项说明
prscnt execnt fchcnt 调用分析执行提取的次数
prscpu execpu fchcpu 分析执行提取所占用的 CPU 时间
prsela exela fchela 分析执行提取所占用的时间
prsdsk exedsk fchdsk 分析执行提取期间的磁盘读取次数
prsqry exeqry fchqry 分析执行提取期间用于持续读取的缓冲区数
prscu execu fchcu 分析执行提取期间用于当前读取的缓冲区数
prsmis exemis 分析执行期间库高速缓存未命中的次数
exerow fchrow 分析执行期间处理的行数
userid 分析游标的用户的用户 ID

TKPROF 统计数据
Count: 执行调用数
CPU: CPU 的使用秒数
Elapsed: 总共用去的时间
Disk: 物理读取次数
Query: 持续读取的逻辑读取数
Current: 当前模式下的逻辑读取数
Rows: 已处理行数
TKPROF 统计信息
统计 含义
Count 分析或执行语句的次数以及为语句发出的提取调用数
CPU 每个阶段的处理时间以秒为单位如果在共享池中找到该语句对于分析阶段为 0
Elapsed 占用时间以秒为单位通常不是非常有用因为其它进程影响占用时间
Disk 从数据库文件读取的物理数据块如果该数据被缓冲则该统计可能很低
Query 为持续读取检索的逻辑缓冲区通常用于 SELECT 语句
Current 在当前模式下检索的逻辑缓冲区通常用于 DML 语句
Rows 外部语句所处理的行对于 SELECT 语句在提取阶段显示它对于 DML 语句在执行阶段显示它

Query 和Current 的总和为所访问的逻辑缓冲区的总数


执行下面命令：tkprof D:\ORACLE\PRODUCT\10.2.0\DB_1\RDBMS\TRACE/wgods_ora_3940.trc h:\out.txtoutputfile explain=etl/etl

执行上面命令后，可以查看生成的文本文件

代码如下:


TKPROF: Release 10.2.0.1.0 - Production on 星期三 5月 23 16:56:41 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Trace file: D:\ORACLE\PRODUCT\10.2.0\DB_1\RDBMS\TRACE/wgods_ora_3940.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
ALTER SESSION SET SQL_TRACE = TRUE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: 89 (ETL)
********************************************************************************
begin :id := sys.dbms_transaction.local_transaction_id; end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 2
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 0 0 2
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: 89 (ETL)
********************************************************************************
SELECT *
FROM
SCOTT.EMP
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 7 0 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 7 0 14
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 89 (ETL)
Rows Execution Plan
------- ---------------------------------------------------
SELECT STATEMENT MODE: CHOOSE
TABLE ACCESS MODE: ANALYZED (FULL) OF 'EMP' (TABLE)
********************************************************************************
ALTER SESSION SET SQL_TRACE = FALSE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 89 (ETL)
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 0.00 0.00 0 0 0 0
Execute 5 0.00 0.00 0 0 0 2
Fetch 1 0.00 0.00 0 7 0 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.00 0.00 0 7 0 16
Misses in library cache during parse: 2
Misses in library cache during execute: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
user SQL statements in session.
internal SQL statements in session.
SQL statements in session.
statement EXPLAINed in this session.
********************************************************************************
Trace file: D:\ORACLE\PRODUCT\10.2.0\DB_1\RDBMS\TRACE/wgods_ora_3940.trc
Trace file compatibility: 10.01.00
Sort options: default
session in tracefile.
user SQL statements in trace file.
internal SQL statements in trace file.
SQL statements in trace file.
unique SQL statements in trace file.
SQL statements EXPLAINed using schema:
ETL.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
lines in trace file.
elapsed seconds in trace file.