摘要:《dbms_system.set_sql_trace_in_session》
2009/7/26
很多情況下無法在得知sql的效能，有時候是因為綁在應用程式，程式一大起來連那邊出的問題
都不是很清楚，有幸在學習的過程中看到這一個指令，這比set autotrace on實際多了。
但是還是要說明白點，實際使用的過程中，感覺是很耗Database資源的；所以使用時要小心，
不過不可否認是很好用的。
以下實地演練，在trace 之後再透過oracle command "TKPROF"解析trace file。
SQL> show user
USER 為 "SYS"
SQL> desc dbms_system
PROCEDURE DIST_TXN_SYNC
引數名稱 類型 In/Out 預設值？
------------------------------ ----------------------- ------ --------
INST_NUM NUMBER IN
PROCEDURE GET_ENV
引數名稱 類型 In/Out 預設值？
------------------------------ ----------------------- ------ --------
VAR VARCHAR2 IN
VAL VARCHAR2 OUT
PROCEDURE KCFRMS
PROCEDURE KSDDDT
PROCEDURE KSDFLS
PROCEDURE KSDIND
引數名稱 類型 In/Out 預設值？
------------------------------ ----------------------- ------ --------
LVL BINARY_INTEGER IN
PROCEDURE KSDWRT
引數名稱 類型 In/Out 預設值？
------------------------------ ----------------------- ------ --------
DEST BINARY_INTEGER IN
TST VARCHAR2 IN
PROCEDURE READ_EV
引數名稱 類型 In/Out 預設值？
------------------------------ ----------------------- ------ --------
IEV BINARY_INTEGER IN
OEV BINARY_INTEGER OUT
PROCEDURE SET_BOOL_PARAM_IN_SESSION
引數名稱 類型 In/Out 預設值？
------------------------------ ----------------------- ------ --------
SID NUMBER IN
SERIAL# NUMBER IN
PARNAM VARCHAR2 IN
BVAL BOOLEAN IN
PROCEDURE SET_EV
引數名稱 類型 In/Out 預設值？
------------------------------ ----------------------- ------ --------
SI BINARY_INTEGER IN
SE BINARY_INTEGER IN
EV BINARY_INTEGER IN
LE BINARY_INTEGER IN
NM VARCHAR2 IN
PROCEDURE SET_INT_PARAM_IN_SESSION
引數名稱 類型 In/Out 預設值？
------------------------------ ----------------------- ------ --------
SID NUMBER IN
SERIAL# NUMBER IN
PARNAM VARCHAR2 IN
INTVAL BINARY_INTEGER IN
PROCEDURE SET_SQL_TRACE_IN_SESSION
引數名稱 類型 In/Out 預設值？
------------------------------ ----------------------- ------ --------
SID NUMBER IN
SERIAL# NUMBER IN
SQL_TRACE BOOLEAN IN
PROCEDURE WAIT_FOR_EVENT
引數名稱 類型 In/Out 預設值？
------------------------------ ----------------------- ------ --------
EVENT VARCHAR2 IN
EXTENDED_ID BINARY_INTEGER IN
TIMEOUT BINARY_INTEGER IN
SQL> select sid,serial# from v$session where username='TING';
SID SERIAL#
---------- ----------
134 29
SQL> exec dbms_system.set_sql_trace_in_session('134','29',true);
已順利完成 PL/SQL 程序.
SQL>
---------------------------------------------------
--切換使用者 Ting
---------------------------------------------------
SQL> show user
USER 為 "TING"
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
T
SQL> select count(*) from t;
COUNT(*)
----------
9
SQL> select *from t;
F1
----------
1
2
3
4
5
6
7
8
9
已選取 9 個資料列.
SQL> create index t_I on t (f1);
已建立索引.
SQL>
---------------------------------------------------
--切換使用者 SYS
---------------------------------------------------
SQL> show user
USER 為 "SYS"
SQL> exec dbms_system.set_sql_trace_in_session('134','29',false);
已順利完成 PL/SQL 程序.
SQL> show parameter user_dump
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string C:\ORACLE\ADMIN\ORACLEA\UDUMP
---------------------------------------------------------------------------------------------------------
-- SQL TRACE的檔案會在user_dump_dest的路徑下。
---------------------------------------------------------------------------------------------------------
SQL> host dir c:\oracle\admin\oraclea\udump\oraclea_ora_2820.trc
磁碟區 C 中的磁碟沒有標籤。
磁碟區序號: 741A-1F28
c:\oracle\admin\oraclea\udump 的目錄
2009/07/26 下午 07:29 117,436 oraclea_ora_2820.trc
1 個檔案 117,436 位元組
0 個目錄 19,409,534,976 位元組可用
SQL> exit
已切斷與 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options 的連線
C:\Documents and Settings\Administrator>tkprof c:\oracle\admin\oraclea\udump\or
aclea_ora_2820.trc
output = c:\session_sql_trace.txt
TKPROF: Release 10.2.0.1.0 - Production on 星期日 7月 26 19:31:50 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
---------------------------------------------------------------------------------------------------------
下面是 oraclea_ora_2820.trc 的一部份內容：
Dump file c:\oracle\admin\oraclea\udump\oraclea_ora_2820.trc
Sun Jul 26 19:27:29 2009
ORACLE V10.2.0.1.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows XP Version V5.1 Service Pack 2
CPU : 1 - type 586
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:1326M/2047M, Ph+PgF:2632M/3434M, VA:1441M/2047M
Instance name: oraclea
Redo thread mounted by this instance: 1
Oracle process number: 41
Windows thread id: 2820, image: ORACLE.EXE (SHAD)
*** 2009-07-26 19:27:29.026
*** ACTION NAME:() 2009-07-26 19:27:28.976
*** MODULE NAME:(SQL*Plus) 2009-07-26 19:27:28.976
*** SERVICE NAME:(SYS$USERS) 2009-07-26 19:27:28.976
*** SESSION ID:(134.29) 2009-07-26 19:27:28.976
=====================
PARSING IN CURSOR #2 len=198 dep=1 uid=0 oct=3 lid=0 tim=642137271 hv=4125641360 ad='2165a6e4'
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 is null and linkname is null and subname is null
END OF STMT
PARSE #2:c=0,e=3201,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=642137240
EXEC #2:c=10015,e=29116,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=642227257
FETCH #2:c=0,e=8089,p=1,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=642236351
STAT #2 id=1 cnt=0 pid=0 pos=1 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=2 pr=1 pw=0 time=8129 us)'
STAT #2 id=2 cnt=0 pid=1 pos=1 obj=37 op='INDEX RANGE SCAN I_OBJ2 (cr=2 pr=1 pw=0 time=8050 us)'
---------------------------------------------------------------------------------------------------------
以下是透過tkprof所解析出來的檔案內容：
TKPROF: Release 10.2.0.1.0 - Production on 星期日 7月 26 19:31:50 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Trace file: c:\oracle\admin\oraclea\udump\oraclea_ora_2820.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 is null
and linkname is null and subname is null
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.01 0.02 0 0 0 0
Fetch 2 0.00 0.00 1 4 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.01 0.04 1 4 0 0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=2 pr=1 pw=0 time=8129 us)
0 INDEX RANGE SCAN I_OBJ2 (cr=2 pr=1 pw=0 time=8050 us)(object id 37)
********************************************************************************
select text
from
view$ where rowid=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.02 0.01 0 0 0 0
Fetch 1 0.00 0.00 1 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.03 0.01 1 2 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY USER ROWID VIEW$ (cr=1 pr=1 pw=0 time=1203 us)2011/02/23 原標題為 《not current session sql trace》
改為 《dbms_system.set_sql_trace_in_session》 以免自己想找都找不太到