准备使用SQL TRACE
)InitORA参数
) 确定是以dedicated方式连接到数据库
在系统中打开SQL_TRACE
在session中打开SQL_TRACE
) 在SQLPLUS中 alter session set sql_trace=true
) 在PL/SQL中由于不能执行alter session可以使用dbms_sessionset_sql_trace(TRUE);
)打开其它session的SQL_TRACEdbms_systemset_sql_trace_in_session(sidserial#TRUE)
) 用event来打开
alter session set events trace name context foreverlevel <N>;alter session set events trace name context off;
找到trace文件
用tkprof格式化trace文件
准备使用SQL TRACE
)InitORA参数
timed_statistics 设置为true(也可以在session上设置)否则不会有CPU时间信息
user_dump_dest 指定trace文件生成的目录
max_dump_file_size trace文件的最大尺寸(单位为操作系统块)UMLIMITED表示没有限制
Oracle以后可以在后面加上 K 或 M 来表示文件大小
optimizer_mode 定义缺省的查询优化器虽然可以用alter session来设置但在格式化trace文件里optimizer_mode会回复到原来的设置(一个新的session来分析SQL的执行计划)这样会产生不准确的执行计划所以建议不要通过session来修改这个参数
注在运行tkprof时不要加explain参数就不存在这个问题执行计划是Oracle在运行时所用的计划
) 确定是以dedicated方式连接到数据库
在系统中打开SQL_TRACE
在InitORA中加入
SQL_TRACE = TRUE
这样会对系统性能造成明显的影响建议不要使用
在session中打开SQL_TRACE
) 在SQLPLUS中
alter session set sql_trace=true
) 在PL/SQL中由于不能执行alter session可以使用
dbms_sessionset_sql_trace(TRUE);
必须安装DBMS_SESSION包并直接赋给用户alter session的权限
)打开其它session的SQL_TRACE
dbms_systemset_sql_trace_in_session(sidserial#TRUE)
) 用event来打开
alter session set events trace name context foreverlevel ;
alter session set events trace name context off;
N为以下值之一
N= 同alter session set sql_trace = true
N= 可以捕获绑定变量
N= 可以捕获查询时的等待事件
N= 可以捕获绑定变量与等待事件
找到trace文件
trace文件名是ora_xxxx_SIDtrc其中xxxx是与Oracle连接的shadow进程的PIDSID是Oracle实例的SID文件生成在InitORA参数user_dump_dest指定的目录下
用tkprof格式化trace文件
tkprof是用来解释trace文件内容把原始的trace文件转化为容易理解的文件使用方法为
tkprof trace文件名 报告文件名 [sort=option]
例:
tkprof ora__testtrc reporttxt
sort参数是用来指定输出的SQL是按什么数据来排序(如cpu时间或elapsed时间详见tkprof的使用参数说明)
在reporttxt中有关于每个SQL的parse/execute/fetch/disk read/buffer get/cpu time/执行计划(包括每一步运行时的行数)样例如下
select *
from
reverse_test where id=
call count cpuelapsed diskquerycurrentrows
Parse
Execute
Fetch
total
Misses in library cache during parse:
Optimizer goal: CHOOSE
Parsing user id:
Rows Row Source Operation
TABLE ACCESS BY INDEX ROWID REVERSE_TEST
INDEX RANGE SCAN (object id )
在reporttxt文件头有各个数据的解释根据以下一些指标可以分析一下SQL的执行性能:
query+current/rows 平均每行所需的block数太大的话（超过）SQL语句效率太低
Parse count/Execute count parse count应尽量接近如果太高的话SQL会进行不必要的reparse要检查Pro*C程序的MAXOPENCURSORS是不是太低了或不适当的使用的RELEASE_CURSOR选项
rows Fetch/Fetch Fetch Array的大小太小的话就没有充分利用批量Fetch的功能增加了数据在客户端和服务器之间的往返次数在Pro*C中可以用prefetch=NNJava/JDBC中可调用SETROWPREFETCH在PL/SQL中可以用BULK COLLECTSQLPLUS中的arraysize(缺省是)
disk/query+current 磁盘IO所占逻辑IO的比例太大的话有可能是db_buffer_size过小(也跟SQL的具体特性有关)
elapsed/cpu 太大表示执行过程中花费了大量的时间等待某种资源
cpuORelapsed 太大表示执行时间过长或消耗了大量的CPU时间应该考虑优化
执行计划中的Rows 表示在该处理阶段所访问的行数要尽量减少
tkprof的参数有下面几个
/*********************************/
explain=username/password
connect to oracle and issue explain plain
/*********************************/
/*********************************/
talbe=schematablename
useschematable 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 nonrecursive 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
/********************************/
##########################################
##### 详细参数 ######
##########################################
/*********************************/
( ::) 东茳荨
例如
/**************************/
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
/********************************/
/******************************/
