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/执行计划(包括每一步运行时的行数),样例如下:
在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 | 表示在该处理阶段所访问的行数,要尽量减少 |
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/266281/viewspace-495483/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/266281/viewspace-495483/