空间管理您的位置: ITPUB个人空间 » 不胜人生一场醉 » 日志

这是最好的时代，这是最坏的时代，这是智慧的时代，这是愚蠢的时代；这是信仰的时期，这是怀疑的时期；这是光明的季节，这是黑暗的季节；这是希望之春，这是失望之冬；人们面前有着各样事物，人们面前一无所有；人们正在直登天堂；人们正在直下地狱。我也要与时俱进了，被itpub2.0牵着尾巴，拼命的奔跑，不停的灌水...

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

上一篇 / 下一篇 2007-05-12 00:00:00 / 个人分类：数据库专区

查看( 84 ) / 评论( 0 ) / 评分( 0 / 0 )

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"ITPUB个人空间.w/~y{$tQ
FROM plan_table
8e4},n1[4VNx0CONNECT BY prior id = parent_id AND prior statement_id = statement_idITPUB个人空间m!u"\F^F0_
START WITH id = 0 AND statement_id = 'test'ITPUB个人空间X3_&?9i|6T
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语句统计信息,不输出结果