tkprof和sql trace
2024-07-21 02:39:51
供稿：网友
1.预备使用SQL TRACE
1) Init.ORA参数
2) 确定是以"dedicated"方式连接到数据库
2. 在系统中打开SQL_TRACE
3. 在session中打开SQL_TRACE
1) 在SQLPLUS中 alter session set sql_trace=true
2) 在PL/SQL中,由于不能执行alter session,可以使用 dbms_session.set_sql_trace(TRUE);
3) 打开其它session的SQL_TRACE dbms_system.set_sql_trace_in_session(sid,serial#,TRUE)
4) 用event来打开
alter session set events '10046 trace name context forever,level <N>'; alter session set events '10046 trace name context off';
4.找到trace文件
5.用tkPRof格式化trace文件
1.预备使用SQL TRACE
1) Init.ORA参数
timed_statistics 设置为true(也可以在session上设置),否则不会有CPU时间信息
user_dump_dest 指定trace文件生成的目录
max_dump_file_size trace文件的最大尺寸(单位为操作系统块),UMLIMITED表示没有限制
Oracle8以后可以在后面加上 K 或 M 来表示文件大小
optimizer_mode 定义缺省的查询优化器。虽然可以用alter session来设置，但在格式化trace文件里optimizer_mode会回复到原来的设置(一个新的session来分析SQL的执行计划)，这样会产生不准确的执行计划，所以建议不要通过session来修改这个参数
注：在运行tkprof时不要加eXPlain参数，就不存在这个问题，执行计划是Oracle在运行时所用的计划
2) 确定是以"dedicated"方式连接到数据库
2. 在系统中打开SQL_TRACE
在Init.ORA中加入
SQL_TRACE = TRUE
这样会对系统性能造成明显的影响，建议不要使用。
3.在session中打开SQL_TRACE
1) 在SQLPLUS中
alter session set sql_trace=true
2) 在PL/SQL中,由于不能执行alter session,可以使用
dbms_session.set_sql_trace(TRUE);
必须安装DBMS_SESSION包，并"直接"赋给用户alter session的权限。
3) 打开其它session的SQL_TRACE
dbms_system.set_sql_trace_in_session(sid,serial#,TRUE)
4) 用event来打开
alter session set events '10046 trace name context forever,level ';
alter session set events '10046 trace name context off';
N为以下值之一：
N=1 同alter session set sql_trace = true
N=4 可以捕捉绑定变量
N=8 可以捕捉查询时的等待事件
N=12 可以捕捉绑定变量与等待事件
4.找到trace文件
trace文件名是ora_xxxx_SID.trc，其中xxxx是与Oracle连接的shadow进程的PID，SID是Oracle实例的SID。文件生成在Init.ORA参数user_dump_dest指定的目录下。
5.用tkprof格式化trace文件
tkprof是用来解释trace文件内容，把原始的trace文件转化为轻易理解的文件。使用方法为
tkprof trace文件名 报告文件名 [sort=option]
例:
tkprof ora_12345_test.trc report.txt
sort参数是用来指定输出的SQL是按什么数据来排序(如cpu时间或elapsed时间,详见tkprof的使用参数说明)
在report.txt中有关于每个SQL的parse/execute/fetch/disk read/buffer get/cpu time/执行计划(包括每一步运行时的行数),样例如下：
select *
from
reverse_test where id=23456
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.01 0.06 2 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.02 0.06 2 4 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 27
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE access BY INDEX ROWID REVERSE_TEST
2 INDEX RANGE SCAN (object id 5833)
在report.txt文件头有各个数据的解释，根据以下一些指标可以分析一下SQL的执行性能:
query+current/rows 平均每行所需的block数，太大的话（超过20）SQL语句效率太低
Parse count/Execute count parse count应尽量接近1，假如太高的话，SQL会进行不必要的reparse。
要检查Pro*C程序的MAXOPENCURSORS是不是太低了，或不适当的使用的RELEASE_CURSOR选项
rows Fetch/Fetch Fetch Array的大小，太小的话就没有充分利用批量Fetch的功能，增加了数据在客户端和服务器之间的往返次数。在Pro*C中可以用prefetch=NN,java/JDBC中可调用SETROWPREFETCH,在PL/SQL中可以用BULK COLLECT,SQLPLUS中的arraysize(缺省是15)
disk/query+current 磁盘IO所占逻辑IO的比例，太大的话有可能是db_buffer_size过小(也跟SQL的具体特性有关)
elapsed/cpu 太大表示执行过程中花费了大量的时间等待某种资源
cpu OR elapsed 太大表示执行时间过长，或消耗了大量的CPU时间，应该考虑优化
执行计划中的Rows 表示在该处理阶段所访问的行数，要尽量减少
tkprof的参数有下面几个
/*********************************/
explain=username/passWord
connect to oracle and issue explain plain
/*********************************/
/*********************************/
talbe=schema.tablename
use'schema.table' with explain option
/*********************************/
/*********************************/
aggregate=yes/no
/*********************************/
/*********************************/
insert=filename
list sql statements and data inside insert statements
/*********************************/
/*********************************/
sys=no
tkprof does not list sql statements run as user sys.
/*********************************/
/*********************************/
record=filename
record non-recursive statements found in the trace file
/*********************************/
/*********************************/
print=integer
list only the first 'integer' sql statements
/*********************************/
/*********************************/
sort=option
set zero or more of the following sort options
##########################################
##### 具体参数 #####
##########################################
/*********************************/
sort=userid
userid of user that parsed the cursor
/*********************************/
/*********************************/
sort=fchrow
number of rows fetched
/********************************/
/********************************/
sort=fchcu
number of buffers for current read during fetch
/********************************/
/********************************/
sort=fchela
elapsed time fetching
/********************************/
/********************************/
sort=fchcnt
number of times fetch was called
/********************************/
##########################################
##### 具体参数 ######
##########################################
/*********************************/
(2005-01-25 19:52:46) 東茳荨客.net
例如
/**************************/
sort=userid
userid of user that parsed the cursor
/********************************/
/*********************************/
sort=fchrow
number of rows fetched
/*****************************/
/*********************************/
sort=fchcu
number of buffers for current read during fetch
/**************************/
/**************************/
sort=fchela
elapsed time fetching
/****************************************/
/*************************************/
sort=fchcnt
number of times fetch was called
/********************************/
/******************************/ right">（出处：清风软件下载学院）
