Oracle中对于SQL的执行计划和跟踪功能是很强大的,其中包括EXPLAIN PLAN,TKPROF和SQL Trace, Auto Trace.

EXPLAIN PLAN

Explain Plan语句能够显示优化器对SELECT,UPDATE,INSERT,DELETE等语句分析的执行计划.一条语句的执行计划是Oracle运行SQL的顺序.行源树是执行计划的核心,主要包含下列信息:

Ø 参考表的顺序

Ø 每个表的访问方式

Ø 表的连接方式

Ø 最佳化:成本和基数

Ø 分区

Ø 并行化

创建PLAN_TABLE表

@C:UserDefineoracleora92rdbmsadminutlxplan.sql;

运行PLAN_TABLE

SQL> explain plan

2 set statement_id='test' for

3 select * from testindex where object_type='JAVA CLASS';

Explained

显示PLAN_TABLE表输出

SQL> @C:UserDefineoracleora92rdbmsadminutlxpls.sql;

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 895 | 76075 | 27

| 1 | TABLE ACCESS BY INDEX ROWID| TESTINDEX | 895 | 76075 | 27

|* 2 | INDEX RANGE SCAN | OBJECTTYPEINDEX | 895 | | 2

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("TESTINDEX"."OBJECT_TYPE"='JAVA CLASS')

13 rows selected

自定义PLAN_TABLE表输出

SELECT lpad(' ',level-1)||operation||' '||options||' '||object_name "Plan"
FROM plan_table
CONNECT BY prior id = parent_id AND prior statement_id = statement_id
START WITH id = 0 AND statement_id = 'test'
ORDER BY id;

PLAN_TABLE表输出

Plan

--------------------------------------------------------------------------------

SELECT STATEMENT

TABLE ACCESS BY INDEX ROWID TESTINDEX

INDEX RANGE SCAN OBJECTTYPEINDEX

SQL Trace

SQL Trace提供了每一条SQL语句的性能信息,产生下列统计信息:

Ø 分析,执行和fetch数量

Ø CPU和消耗时间

Ø 物理读和逻辑读

Ø 处理的记录数

Ø 库缓存的缺失数

你可以在会话或者实例级别上增强SQL Trace跟踪.

TKPROF

你能够使用TKPROF程序格式化跟踪文件的内容,把输出写入一个易于理解的文件中,此外,TKPROF也能够完成以下:

Ø 决定SQL语句的执行计划

Ø 在数据库中创建一个存储统计的SQL脚本

SQL Trace和TKPROF的步骤

1. 设置跟踪文件管理的初始化参数

TIMED_STATISTICS 启用或屏蔽时间统计的收集,例如CPU和消耗时间

MAX_DUMP_FILE_SIZE 文件的最大行数,缺省为500

USER_DUMP_DEST 跟踪文件的目录

2. 增强SQL Trace跟踪,运行应用程序

使用DBMS_SESSION.SET_SQL_TRACE 存储过程

ALTER SESSION SET SQL_TRACE=TRUE;

3. 运行TKPROF转换SQL Trace文件到一个易于阅读的输出文件中.

TKPROF filename1 filename2

4. 解译输出文件

5. 运行SQL script把统计信息存储到数据库中.

SQL Trace和TKPROF全过程

显示系统设置参数并产生Trace文件

SQL> show parameters TIMED_STATISTICS;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

timed_statistics boolean TRUE

SQL> show parameters MAX_DUMP_FILE_SIZE;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

max_dump_file_size string UNLIMITED

SQL> show parameters USER_DUMP_DEST;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

user_dump_dest string C:UserDefineoracleadminwbqudump

SQL> ALTER SESSION SET SQL_TRACE=TRUE;

Session altered

SQL> select owner,object_name from testindex where object_type='CONTEXT';

OWNER OBJECT_NAME

------------------------------ --------------------------------------------------------------------------------

SYS LT_CTX

SYS WK$CONTEXT

使用TKPROF格式化输出文件

C:>CD C:UserDefineoracleadminwbqudump

C:UserDefineoracleadminwbqudump>TKPROF wbq_ora_4868.trc output.txt

TKPROF: Release 9.2.0.1.0 - Production on Sat May 12 01:50:43 2007

C:UserDefineoracleadminwbqudump>more |output.txt

阅读并分析格式化报表

TKPROF: Release 9.2.0.1.0 - Production on Sat May 12 01:50:43 2007

Trace file: wbq_ora_4868.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

********************************************************************************

select *

from

testindex where object_type='CONTEXT'

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 0

Fetch 2 0.00 0.00 0 8 0 4

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 6 0.00 0.00 0 8 0 4

Misses in library cache during parse: 1

Optimizer goal: CHOOSE

Parsing user id: 61

Rows Row Source Operation

------- ---------------------------------------------------

2 TABLE ACCESS BY INDEX ROWID TESTINDEX

2 INDEX RANGE SCAN OBJECTTYPEINDEX (object id 30527)

Autotrace Report

Autotrace Setting	结果
SET AUTOTRACE OFF	没有自动跟踪报告(系统缺省)
SET AUTOTRACE ON EXPLAIN	输出执行结果和优化器执行计划
SET AUTOTRACE ON STATISTICS	输出执行结果和SQL语句统计信息
SET AUTOTRACE ON	输出执行结果,优化器执行计划和SQL语句统计信息
SET AUTOTRACE TRACEONLY	仅仅输出优化器执行计划和SQL语句统计信息,不输出结果

样例

SQL*Plus: Release 9.2.0.1.0 - Production on Sat May 12 02:16:18 2007

SQL> connect wbq/wbq;

Connected.

SQL> SET AUTOTRACE OFF;

SQL> SELECT OWNER,SUBSTR(OBJECT_NAME,1,20) OBJECT_NAME

2 FROM TESTINDEX

3 WHERE OBJECT_TYPE='CLUSTER';

OWNER OBJECT_NAME

------------------------------ --------------------

SYS C_COBJ#

SYS C_FILE#_BLOCK#

…

10 rows selected.

SQL> SET AUTOTRACE ON;

SQL> SELECT OWNER,SUBSTR(OBJECT_NAME,1,20) OBJECT_NAME

2 FROM TESTINDEX

3 WHERE OBJECT_TYPE='CLUSTER';

OWNER OBJECT_NAME

------------------------------ --------------------

SYS C_COBJ#

SYS C_FILE#_BLOCK#

…

10 rows selected.

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=27 Card=895 Bytes=32220)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TESTINDEX' (Cost=27 Card=895 Bytes=32220)

2 1 INDEX (RANGE SCAN) OF 'OBJECTTYPEINDEX' (NON-UNIQUE) (Cost=2 Card=895)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

6 consistent gets

0 physical reads

0 redo size

614 bytes sent via SQL*Net to client

499 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

10 rows processed

SQL> SET AUTOTRACE TRACEONLY;

SQL> SELECT OWNER,SUBSTR(OBJECT_NAME,1,20) OBJECT_NAME

2 FROM TESTINDEX

3 WHERE OBJECT_TYPE='CLUSTER';

10 rows selected.

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=27 Card=895 Bytes=32220)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TESTINDEX' (Cost=27 Card=895 Bytes=32220)

2 1 INDEX (RANGE SCAN) OF 'OBJECTTYPEINDEX' (NON-UNIQUE) (Cost=2 Card=895)

Statistics

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/6517/viewspace-145534/ ，如需转载，请注明出处，否则将追究法律责任。

ITPub博客

Oracle学习系列—数据库优化—性能优化工具

Oracle中对于SQL的执行计划和跟踪功能是很强大的,其中包括EXPLAIN PLAN,TKPROF和SQL Trace, Auto Trace.

最新文章