Sql-trace 跟踪与诊断
我的联系方式:qq 149196034
使用sql_trace工具我遇到的一个问题是:在bs结构中,终端用户在select查询的时候,我看到的都是一个用户名,虽然SID,SERIAL#不同,但是我不知道如何确定是哪个用户产生的问题。希望有人能帮我!
1 :选择需要跟踪的用户
2:模式分为:跟踪当前用户,跟踪别的用户
3:停止trace跟踪
3:产生trace文件,路径在udump/
4:tkprof格式化trace文件
5:分析trace文件,如何解析trace文件介绍
6:解决sql问题
1select,开启当前用户跟踪方式
1select sid,serial#,username from v$session where username is not null;
SQL> alter session set sql_trace=true;
Session altered
Alter session set sql_trace=false;
2:跟踪多用户方式,先查出SID,SERIAL#,
Sql> exec dbms_system.set_sql_trace_in_session(45,62745,true)
exec dbms_system.set_sql_trace_in_session(60,9287,true)
exec dbms_system.set_sql_trace_in_session(136,62432,true)
Sql> exec dbms_system.set_sql_trace_in_session(45,62745,false)
exec dbms_system.set_sql_trace_in_session(60,9287,false)
exec dbms_system.set_sql_trace_in_session(136,62432,false)
3:show parameter dump_dest,可以看到trace文件存放路径
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest string /oracle/admin/mis/bdump
core_dump_dest string /oracle/admin/mis/cdump
user_dump_dest string /oracle/admin/mis/udump
trace存放在bdump
4:采用ls –lt|more找到trace文件
pwd
/oracle/admin/mis/udump
ls -lt|more
5:命令tkprof格式化trc文件,产生易读
[oracle@DELL udump]$ tkprof mis_ora_25236.trc mis_ora_2236.prf
TKPROF: Release 9.2.0.6.0 - Production on Tue Oct 26 18:37:33 2010
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
[oracle@DELL udump]$ cat mis_ora_2236.prf|more
TKPROF: Release 9.2.0.6.0 - Production on Tue Oct 26 18:37:33 2010
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Trace file: mis_ora_25236.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
********************************************************************************
ALTER SESSION SET REMOTE_DEPENDENCIES_MODE=SIGNATURE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.01 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.00 0 0 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 29
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.01 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.00 0 0 0 0
Misses in library cache during parse: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
2 user SQL statements in session.
0 internal SQL statements in session.
2 SQL statements in session.
********************************************************************************
Trace file: mis_ora_25236.trc
Trace file compatibility: 9.02.00
Sort options: default
3 sessions in tracefile.
3 user SQL statements in trace file.
0 internal SQL statements in trace file.
2 SQL statements in trace file.
1 unique SQL statements in trace file.
233 lines in trace file.
6:prf文件参数说明
关于统计表格的标题信息中count、cpu、elapsed、disk、query、current和rows的说明在该trace文件的最前端有一个简要的说明,这里再分别赘述一下。
count :查询在此阶段执行的次数;
cpu :该查询在此阶段的CPU时间量,以毫秒为单位;
elapsed :花费在此阶段上的挂钟时间,该值比cpu值大的时候,表明存在等待事件;
disk :执行物理I/O次数;
query :在意一致性检索方式获得块时,执行逻辑I/O次数;
current :逻辑I/O次数;
rows :此阶段,被处理或受影响的行数。
关于第一列的赘述:
Parse :软编译和硬编译次数;
Execute :在open和execute语句中完成的内容;
Fetch :select中会有数据显示,在update语句中不会有数据显示。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22934571/viewspace-1040481/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22934571/viewspace-1040481/