执行计划并没有真正执行，适合开发测试阶段。上线阶段用sql trace 真正的跟踪sql执行的各步情况。sql trace 8i就有了

SYS>alter session set sql_trace=true;
SYS>exec dbms_session.set_sql_trace(true);
...

SYS>alter session set sql_trace=false;

如果不设置成false， true状态多少sql多会跟踪。跟踪文件在udump中

20:55:12 SYS>show parameter dump

NAME TYPE VALUE

----------------- ----------- ---------------------------------------------

user_dump_dest string /u01/app/oracle/diag/rdbms/prod/prod/trace

[oracle@linux6 trace]$ ls -ltr

SELECT T.VALUE || '/' || LOWER(RTRIM(I.INSTANCE, CHR(0))) || '_ora_' ||
P.SPID || '.trc' TRACE_FILE_NAME
FROM
( SELECT P.SPID
FROM V$MYSTAT M, V$SESSION S, V$PROCESS P
WHERE M.STATISTIC# =1
AND S.SID = M.SID
AND P.ADDR = S.PADDR
) P,
( SELECT T.INSTANCE
FROM V$THREAD T, V$PARAMETER V
WHERE V.NAME ='thread'
AND (V.VALUE = 0 OR T.THREAD# = TO_NUMBER(V.VALUE))
) I,
(SELECT VALUE FROM V$PARAMETER WHERE NAME='user_dump_dest') T ;

[oracle@linux6 trace]$ more prod_dbrm_1954.trc

这种格式看不懂，用tkprof格式化一下

[oracle@linux6 trace]$ tkprof prod_dbrm_1954.trc sys.trc --------sys=no

[oracle@linux6 trace]$ more sys.trc

TKPROF: Release 11.2.0.1.0 - Development on Sat Aug 23 21:17:21 2014

Trace file: prod_ora_2528.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) dml操作

rows = number of rows processed by the fetch or execute call 返回行

********************************************************************************

SQL ID: 4tk6t8tfsfqbf

Plan Hash: 0

alter session set sql_trace=true

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 0 0.00 0.00 0 0 0 0

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 2 0.00 0.00 0 0 0 0

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: SYS

********************************************************************************

SQL ID: 6avfua5g1gkh2

Plan Hash: 456374238

select *

from

dba_objects

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 4836 0.78 0.83 1196 7699 0 72524

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 4838 0.78 0.83 1196 7699 0 72524

Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: SYS

Rows Row Source Operation

------- ---------------------------------------------------

72524 VIEW DBA_OBJECTS (cr=7699 pr=1196 pw=0 time=1380499 us cost=258 size=14211171 card=68653)

72524 UNION-ALL (cr=7699 pr=1196 pw=0 time=1155627 us)

0 TABLE ACCESS BY INDEX ROWID SUM$ (cr=4 pr=2 pw=0 time=0 us cost=1 size=9 card=1)

3 INDEX UNIQUE SCAN I_SUM$_1 (cr=2 pr=1 pw=0 time=0 us cost=0 size=0 card=1)(object id 956)

0 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=0 us cost=3 size=30 card=1)

0 INDEX RANGE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)(object id 36)

72524 FILTER (cr=7692 pr=1193 pw=0 time=501766 us)

73431 HASH JOIN (cr=5708 pr=905 pw=0 time=843549 us cost=255 size=8951872 card=73376)

98 TABLE ACCESS FULL USER$ (cr=7 pr=0 pw=0 time=97 us cost=3 size=1666 card=98)

73431 HASH JOIN (cr=5701 pr=905 pw=0 time=534642 us cost=251 size=7704480 card=73376)

98 INDEX FULL SCAN I_USER2 (cr=1 pr=0 pw=0 time=97 us cost=1 size=2156 card=98)(object id 47)

73431 TABLE ACCESS FULL OBJ$ (cr=5700 pr=905 pw=0 time=235539 us cost=249 size=6090208 card=73376)

3891 TABLE ACCESS BY INDEX ROWID IND$ (cr=1984 pr=288 pw=0 time=0 us cost=2 size=8 card=1)

4793 INDEX UNIQUE SCAN I_IND1 (cr=798 pr=4 pw=0 time=0 us cost=1 size=0 card=1)(object id 41)

0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=2 size=29 card=1)

0 INDEX FULL SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=20 card=1)(object id 47)

0 INDEX RANGE SCAN I_OBJ4 (cr=0 pr=0 pw=0 time=0 us cost=1 size=9 card=1)(object id 39)

0 NESTED LOOPS (cr=3 pr=1 pw=0 time=0 us cost=3 size=105 card=1)

0 TABLE ACCESS FULL LINK$ (cr=3 pr=1 pw=0 time=0 us cost=2 size=88 card=1)

0 TABLE ACCESS CLUSTER USER$ (cr=0 pr=0 pw=0 time=0 us cost=1 size=17 card=1)

0 INDEX UNIQUE SCAN I_USER# (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 11)

********************************************************************************

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.00 0.00 0 0 0 0

Fetch 4836 0.78 0.83 1196 7699 0 72524

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 4840 0.78 0.83 1196 7699 0 72524

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: prod_ora_2528.trc

Trace file compatibility: 11.1.0.7

Sort options: default

1 session in tracefile.

2 user SQL statements in trace file.

0 internal SQL statements in trace file.

2 SQL statements in trace file.

2 unique SQL statements in trace file.

4977 lines in trace file.

60 elapsed seconds in trace file.

案例：

每隔三个月数据库服务器自动停机

应用程序消耗资源太多 #解释的话用sql跟踪，看看资源到底占多少

oracle bug #

操作系统故障IBM #

oracle 8.1.7.0升级到8174问题就解决了

oracle有问题只会造成实例重启，不会造成服务器重启。

ITPub博客

sql_trace

最新文章