zzORacle中sql语句跟踪监视
测试环境
SQL> select * from v$version ;
BANNER
---------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
跟踪会话查询
SQL> select sid,serial#,program,user,osuser from v$session;
SID SERIAL# PROGRAM
---------- ---------- -------------------------------------------
1 1 ORACLE.EXE
2 1 ORACLE.EXE
3 1 ORACLE.EXE
4 1 ORACLE.EXE
5 1 ORACLE.EXE
6 1 ORACLE.EXE
7 1 ORACLE.EXE
8 1 ORACLE.EXE
9 1 ORACLE.EXE
10 1 ORACLE.EXE
11 3 sqlplus.exe
SID SERIAL# PROGRAM
---------- ---------- -------------------------------------------
12 186 Pb100.exe
已选择12行。
执行跟踪
SQL> exec dbms_system.set_sql_trace_in_session(12,186,true);
PL/SQL 过程已成功完成。
撤销跟踪
SQL> exec dbms_system.set_sql_trace_in_session(12,186,false);
PL/SQL 过程已成功完成。
跟踪文件格式化处理
Microsoft Windows [版本 5.2.3790]
(C) 版权所有 1985-2003 Microsoft Corp.
C:"Documents and Settings"Administrator>tkprof
D:"99.88.66.software"02.oracle"db92i_data"udump"db92i_s000_3784.trc
C:"Documents and Settings"Administrator"桌面"sql.txt
LRM-00108: 定位参数值 'and' 无效
error during command line parsing, cannot continue.
哈哈:中文不识别
C:"Documents and Settings"Administrator>tkprof D:"99.88.66.software"02.oracle"db92i_data"udump"db92i_s000_3784.trc C:"sql.txt
TKPROF: Release 9.2.0.1.0 - Production on 星期四 4月 24 06:56:07 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
----------------------------------------------------------------------------------------------------------------
C:"Documents and Settings"Administrator>sqlplus "ur/pw@ora as sysdba"
SQL*Plus: Release 9.2.0.1.0 - Production on 星期一 5月 12 01:10:20 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
连接到:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> alter system set timed_statistics=true;
系统已更改。
SQL> alter system set events '10046 trace name context forever,level 12';
系统已更改。
SQL> alter system set events '10046 trace name context off';
系统已更改。
SQL> alter system set timed_statistics=false;
系统已更改。
SQL> exit
从Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production中断开
C:"Documents and Settings"Administrator>tkprof c:"erpora_ora_5264.trc C:"after.t
TKPROF: Release 9.2.0.1.0 - Production on 星期一 5月 12 01:13:12 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
结果部分截取
TKPROF: Release 9.2.0.1.0 - Production on 星期四 4月 24 06:56:07 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Trace file: D:"99.88.66.software"02.oracle"db92i_data"udump"db92i_s000_3784.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
********************************************************************************
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1,
spare2
from
obj$ where owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or
remoteowner is null and :4 is null)and(linkname=:5 or linkname is null and
:5 is null)and(subname=:6 or subname is null and :6 is null)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 10 0.00 0.00 0 0 0 0
Execute 17 0.03 0.00 0 0 0 0
Fetch 17 0.00 0.04 3 49 0 15
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 44 0.03 0.04 3 49 0 15
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
INSERT INTO "BAS_CENTER" ( "COST_CENTER", "CENTER_DESC", "MODIFIER",
"MODIFY_DATE" )
VALUES
( :1, :2, :3, :4 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 53 0 0
Execute 2 0.01 0.02 3 2 11 2
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.02 3 55 11 2
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61
********************************************************************************
DELETE FROM "BAS_CENTER"
WHERE
"COST_CENTER" = :1 AND "CENTER_DESC" = :2 AND "MODIFIER" = :3 AND
"MODIFY_DATE" = :4
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 2 6 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 2 6 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61
Rows Row Source Operation
------- ---------------------------------------------------
1 DELETE
1 TABLE ACCESS BY INDEX ROWID BAS_CENTER
1 INDEX UNIQUE SCAN PK_BAS_CENTER (object id 30599)
********************************************************************************
select /*+ all_rows */ count(1)
from
"ERP"."BAS_DEPT" where "COST_CENTER" = :1
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 1 0.00 0.02 6 7 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.03 6 7 0 1
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
0 TABLE ACCESS FULL BAS_DEPT
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 45 0.26 0.35 0 349 0 0
Execute 35 0.01 0.02 3 4 17 3
Fetch 81 0.15 1.11 296 93911 0 2002
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 161 0.43 1.49 299 94264 17 2005
Misses in library cache during parse: 30
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 181 0.09 0.10 1 4 0 0
Execute 211 0.03 0.01 0 0 0 0
Fetch 698 0.01 0.40 49 897 0 569
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1090 0.14 0.52 50 901 0 569
Misses in library cache during parse: 26
45 user SQL statements in session.
181 internal SQL statements in session.
226 SQL statements in session.
********************************************************************************
Trace file: D:"99.88.66.software"02.oracle"db92i_data"udump"db92i_s000_3784.trc
Trace file compatibility: 9.00.01
Sort options: default
1 session in tracefile.
45 user SQL statements in trace file.
181 internal SQL statements in trace file.
226 SQL statements in trace file.
57 unique SQL statements in trace file.
2886 lines in trace file.
其他参考资料
Tkprof工具可用来格式化sql trace产生的文件,让你更容易看懂trace的内容 用法: tkprof tracefile outputfile [explain= ] [table= ] [print= ] [insert= ] [sys= ] [sort= ] 参数说明: tracefile:你要分析的trace文件 outputfile:格式化后的文件 explain=user/password@connectstring table=schema.tablename 这两个参数是一起使用的,通过连接数据库对在trace文件中出现的每条sql语句查看执行计划,并将之输出到outputfile中 注意,该table必须是数据库中不存在的,如果存在会报错 print=n:只列出最初N个sql执行语句 insert=filename:会产生一个sql文件,运行此文件可将收集到的数据insert到数据库表中 sys=no:过滤掉由sys执行的语句 record=filename:可将非嵌套执行的sql语句过滤到指定的文件中去 waits=yes|no:是否统计任何等待事件 aggregate=yes|no:是否将相同sql语句的执行信息合计起来,默认为yes sort=option:设置排序选项,选项如下: prscnt number of times parse was called 可根据自己的需要设置排序 举例:(trace文件可用sql trace去产生,在此略过) 1.列出前2条sql语句的执行情况: C:">tkprof c:"oc_ora_2892.trc c:"cc.txt print=2 2.将数据保存到数据库: C:">tkprof c:"oc_ora_2892.trc c:"cc.txt insert=c:"insert.sql 执行后会在c:"产生insert.sql文件,执行该文件即可将数据保存到数据库,以下为insert.sql部分内容: REM Edit and/or remove the following CREATE TABLE 3.提取sql执行语句: C:">tkprof c:"oc_ora_2892.trc c:"cc.txt record=sqlstr.sql sqlstr.sql中的内容: alter session set sql_trace=true ; 4.产生执行计划: C:">tkprof c:"oc_ora_2892.trc c:"ff.txt explain=ocuser/ocuser table=ocuser.test1 在产生的ff.txt文件中会体现其执行计划: Rows Execution Plan 小技巧: 1.如何查找你产生的trace文件: 可用eygle写的脚本去查找: SQL> select 2 d.value||’/'||lower(rtrim(i.instance, chr(0)))||’_ora_’||p.spid||’.trc’ trace_file_name 3 from 4 ( select p.spid 5 from sys.v$mystat m,sys.v$session s,sys.v$process p 6 where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p, 7 ( select t.instance from sys.v$thread t,sys.v$parameter v 8 where v.name = ‘thread’ and (v.value = 0 or t.thread# = to_number(v.value))) i, 9 ( select value from sys.v$parameter where name = ‘user_dump_dest’) d 10 / TRACE_FILE_NAME 2.sort选项可同时用多个,做法是用括号括起来,中间用逗号分割: C:">tkprof c:"oc_ora_2892.trc c:"cc.txt sort=(prsela, exeela, fchela) |