tkprof和sql trace

　　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
　　/********************************/
　　
　　/******************************/

查看本文来源