一、查看并修改参数
SQL> show parameter max_dump_file_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_dump_file_size string UNLIMITED
SQL> show parameter user_dump_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string /u01/app/oracle/admin/dbnms/udump
SQL> alter session set timed_statistics=true;
Session altered.
用dyx登录数据库
[oracle@ora ~]$ sqlplus dyx/dyx
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Mar 2 09:44:20 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL>
在另一sys用户所在的session下查看dyx的相关信息
SQL> select sid,SERIAL#,username from v$session;
SID SERIAL# USERNAME
---------- ---------- ------------------------------
5 31948 DAIL
6 10910 DAIL
7 4793 DAIL
8 53527 DAIL
10 14549 SYS
11 47706 DAIL
12 2435 DAIL
15 1422 DAIL
17 47689 DAIL
18 15617 DYX
20 35969 DAIL
SID SERIAL# USERNAME
---------- ---------- ------------------------------
21 15456 DAIL
23 2544 DAIL
........
SQL> execute DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(18,15617,TRUE);
PL/SQL procedure successfully completed.
切到dyx所在的session
SQL> insert into d values (120);
1 row created.
SQL> commit;
切到sys所在的session
SQL> execute DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(18,15617,FALSE);
PL/SQL procedure successfully completed.
查看/u01/app/oracle/admin/dbnms/udump下的文件
[oracle@ora udump]$ ll
total 4
-rw-r----- 1 oracle oinstall 2812 Mar 2 10:04 dbnms_ora_13576.trc
执行tkprof
[oracle@ora udump]$ tkprof dbnms_ora_13576.trc dyx.txt sys=no
TKPROF: Release 10.2.0.1.0 - Production on Mon Mar 2 10:05:51 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
使用 TKPROF 命令选项 SYS=NO 以取消涉及递归 SQL 的输出
[oracle@ora udump]$ ll
total 12
-rw-r----- 1 oracle oinstall 2812 Mar 2 10:04 dbnms_ora_13576.trc
-rw-r--r-- 1 oracle oinstall 5902 Mar 2 10:05 dyx.txt
查看dbnms_ora_13576.trc
[oracle@ora udump]$ more dbnms_ora_13576.trc
/u01/app/oracle/admin/dbnms/udump/dbnms_ora_13576.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
System name: Linux
Node name: ora
Release: 2.6.9-78.ELlargesmp
Version: #1 SMP Fri Jul 25 00:02:41 EDT 2008
Machine: x86_64
Instance name: dbnms
Redo thread mounted by this instance: 1
Oracle process number: 65
Unix process pid: 13576, image: (TNS V1-V3)
*** 2009-03-02 10:04:26.683
*** ACTION NAME:() 2009-03-02 10:04:26.682
*** MODULE NAME:(SQL*Plus) 2009-03-02 10:04:26.682
*** SERVICE NAME:(SYS$USERS) 2009-03-02 10:04:26.682
*** SESSION ID:(18.15617) 2009-03-02 10:04:26.682
=====================
PARSING IN CURSOR #3 len=227 dep=1 uid=0 oct=3 lid=0 tim=1206991666682160 hv=2190775527 ad='deb64ed0'
select u.name,o.name, t.update$, t.insert$, t.delete$, t.enabled from obj$ o,user$ u,trigger$ t where t.baseobject=:1 and t.obj#=o
.obj# and o.owner#=u.user# and bitand(property,16)=0 and bitand(property,8)=0 order by o.obj#
END OF STMT
PARSE #3:c=1999,e=1557,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1206991666682141
EXEC #3:c=5999,e=5081,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1206991666687965
FETCH #3:c=0,e=93,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=1206991666688106
STAT #3 id=1 cnt=0 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=1 pr=0 pw=0 time=138 us)'
STAT #3 id=2 cnt=0 pid=1 pos=1 obj=0 op='NESTED LOOPS (cr=1 pr=0 pw=0 time=96 us)'
STAT #3 id=3 cnt=0 pid=2 pos=1 obj=0 op='NESTED LOOPS (cr=1 pr=0 pw=0 time=92 us)'
STAT #3 id=4 cnt=0 pid=3 pos=1 obj=81 op='TABLE ACCESS BY INDEX ROWID TRIGGER$ (cr=1 pr=0 pw=0 time=89 us)'
STAT #3 id=5 cnt=0 pid=4 pos=1 obj=125 op='INDEX RANGE SCAN I_TRIGGER1 (cr=1 pr=0 pw=0 time=60 us)'
STAT #3 id=6 cnt=0 pid=3 pos=2 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=0 us)'
STAT #3 id=7 cnt=0 pid=6 pos=1 obj=36 op='INDEX UNIQUE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=0 us)'
STAT #3 id=8 cnt=0 pid=2 pos=2 obj=22 op='TABLE ACCESS CLUSTER USER$ (cr=0 pr=0 pw=0 time=0 us)'
STAT #3 id=9 cnt=0 pid=8 pos=1 obj=11 op='INDEX UNIQUE SCAN I_USER# (cr=0 pr=0 pw=0 time=0 us)'
=====================
PARSING IN CURSOR #5 len=26 dep=0 uid=58 oct=2 lid=58 tim=1206991666689109 hv=2638150261 ad='bb667268'
insert into d values (120)
END OF STMT
PARSE #5:c=10998,e=11531,p=0,cr=1,cu=0,mis=1,r=0,dep=0,og=1,tim=1206991666689106
EXEC #5:c=4000,e=5509,p=0,cr=1,cu=20,mis=0,r=1,dep=0,og=1,tim=1206991666694664
=====================
PARSING IN CURSOR #1 len=6 dep=0 uid=58 oct=44 lid=58 tim=1206991669747805 hv=3480936638 ad='0'
commit
END OF STMT
PARSE #1:c=1000,e=919,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1206991669747795
XCTEND rlbk=0, rd_only=0
EXEC #1:c=999,e=899,p=0,cr=0,cu=1,mis=0,r=0,dep=0,og=0,tim=1206991669748865
查看dyx.txt
[oracle@ora udump]$ more dyx.txt
TKPROF: Release 10.2.0.1.0 - Production on Mon Mar 2 10:34:49 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Trace file: dbnms_ora_13576.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
********************************************************************************
insert into d
values
(120)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 1 0 0
Execute 1 0.00 0.00 0 1 20 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.01 0 2 20 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 58
********************************************************************************
commit
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 0 1 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 1 0
Misses in library cache during parse: 0
Parsing user id: 58
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.01 0.01 0 1 0 0
Execute 2 0.00 0.00 0 1 21 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.01 0 2 21 1
Misses in library cache during parse: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
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 0 0 0
Fetch 1 0.00 0.00 0 1 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 1 0 0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
2 user SQL statements in session.
1 internal SQL statements in session.
3 SQL statements in session.
********************************************************************************
Trace file: dbnms_ora_13576.trc
Trace file compatibility: 10.01.00
Sort options: default
1 session in tracefile.
2 user SQL statements in trace file.
1 internal SQL statements in trace file.
3 SQL statements in trace file.
3 unique SQL statements in trace file.
48 lines in trace file.
3 elapsed seconds in trace file.
