透過sql_trace跟蹤分析執行計劃，便於效能最佳化。其中sql_trace有分三個不同方式的跟蹤。
1、全域性模式的SQL_TRACE:這種跟蹤需要在引數檔案設定SQL_TRACE引數；
sql_trace=true
2、會話模式的SQL_TRACE:這隻能個跟蹤DBA角色使用者開啟的會話；
開啟與關閉跟蹤：
Alter session set sql_trace=true;
Alter session set sql_trace=false;
3、針對某個使用者模式的SQL_TRACE:這個透過呼叫包dbms_system. SET_SQL_TRACE_IN_SESSION
來跟中，需要設定包的相關引數，就可以針對某個使用者開啟的會話進行收集會話詳細的操作過程。
啟用與關閉：
exec dbms_system.SET_SQL_TRACE_IN_SESSION(26,35,true);
exec dbms_system.SET_SQL_TRACE_IN_SESSION(26,35,false);

引數由以下方式獲得：
PROCEDURE SET_SQL_TRACE_IN_SESSION
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SID NUMBER IN
SERIAL# NUMBER IN
SQL_TRACE BOOLEAN IN

引數的值可由v$session檢視sid,serial#獲取。以下是測試過程：

----trace跟蹤檔案：

---檢視會話資訊：

sys@PROD>select sid,serial#,username from v$session

2 where username in ('SUXING','SUSU');

SID SERIAL# USERNAME

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

41 829 SUXING

44 417 SUSU

---開啟跟蹤包：

sys@PROD>exec dbms_system.SET_SQL_TRACE_IN_SESSION(44,417,true);

PL/SQL procedure successfully completed.

---使用者會話進行的系列操作：

susu@PROD>insert into sutab values(14,'sskk');

1 row created.

susu@PROD>commit;

Commit complete.

susu@PROD>select * from sutab;

ID NAME

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

12 susu

13 suxy

14 sskk

---關閉跟蹤包：

sys@PROD>exec dbms_system.SET_SQL_TRACE_IN_SESSION(44,417,false);

PL/SQL procedure successfully completed

---檢視檔案：

[oracle@enmo trace]$ ls -lrt

... ...

-rw-r----- 1 oracle oinstall 85 Dec 6 13:30 PROD_ckpt_4591.trm

-rw-r----- 1 oracle oinstall 1686 Dec 6 13:30 PROD_ckpt_4591.trc

-rw-r----- 1 oracle oinstall 128 Dec 6 16:54 PROD_ora_15427.trm

-rw-r----- 1 oracle oinstall 4050 Dec 6 16:54 PROD_ora_15427.trc

-rw-r----- 1 oracle oinstall 1160 Dec 6 16:54 PROD_mmon_4603.trm

-rw-r----- 1 oracle oinstall 11761 Dec 6 16:54 PROD_mmon_4603.trc

---格式化新生成的trace檔案：

[oracle@enmo trace]$ tkprof PROD_ora_15427.trc susutace.text

TKPROF: Release 11.2.0.4.0 - Development on Tue Dec 6 16:59:12 2016

[oracle@enmo trace]$ ls susutace.text

susutace.text

[oracle@enmo trace]$

---檢視trace檔案的內容：

[oracle@enmo trace]$ cat susutace.text

TKPROF: Release 11.2.0.4.0 - Development on Tue Dec 6 16:59:12 2016

Trace file: PROD_ora_15427.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

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

SQL ID: guuy2qrm1mj9n Plan Hash: 0

insert into sutab

values

(14,'sskk')

call count cpu elapsed disk query current rows

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

Parse 1 0.00 0.01 0 0 0 0

Execute 1 0.00 0.00 0 1 5 1

Fetch 0 0.00 0.00 0 0 0 0

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

total 2 0.00 0.01 0 1 5 1

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 95

Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation

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

0 0 0 LOAD TABLE CONVENTIONAL (cr=1 pr=0 pw=0 time=69 us)

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

SQL ID: 23wm3kz7rps5y Plan Hash: 0

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: 95

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

SQL ID: 96g93hntrzjtr Plan Hash: 2239883476

select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#,

sample_size, minimum, maximum, distcnt, lowval, hival, density, col#,

spare1, spare2, avgcln

from

hist_head$ where obj#=:1 and intcol#=:2

call count cpu elapsed disk query current rows

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

Parse 1 0.00 0.00 0 0 0 0

Execute 2 0.00 0.00 0 0 0 0

Fetch 2 0.00 0.00 0 6 0 2

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

total 5 0.00 0.00 0 6 0 2

Misses in library cache during parse: 1

Misses in library cache during execute: 1

Optimizer mode: RULE

Parsing user id: SYS (recursive depth: 1)

Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation

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

1 1 1 TABLE ACCESS BY INDEX ROWID HIST_HEAD$ (cr=3 pr=0 pw=0 time=94 us)

1 1 1 INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=2 pr=0 pw=0 time=16 us)(object id 450)

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

SQL ID: 2w7t5suhn4wp1 Plan Hash: 3627255998

select *

from

sutab

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

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

total 4 0.00 0.00 0 7 0 3

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 95

Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation

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

3 3 3 TABLE ACCESS FULL SUTAB (cr=7 pr=0 pw=0 time=26 us cost=2 size=17 card=1)

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

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows

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

Parse 3 0.00 0.01 0 0 0 0

Execute 3 0.00 0.00 0 1 6 1

Fetch 2 0.00 0.00 0 7 0 3

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

total 8 0.00 0.01 0 8 6 4

Misses in library cache during parse: 2

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

Fetch 2 0.00 0.00 0 6 0 2

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

total 5 0.00 0.00 0 6 0 2

Misses in library cache during parse: 1

Misses in library cache during execute: 1

3 user SQL statements in session.

1 internal SQL statements in session.

4 SQL statements in session.

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

Trace file: PROD_ora_15427.trc

Trace file compatibility: 11.1.0.7

Sort options: default

1 session in tracefile.

3 user SQL statements in trace file.

1 internal SQL statements in trace file.

4 SQL statements in trace file.

4 unique SQL statements in trace file.

66 lines in trace file.

12 elapsed seconds in trace file.

[oracle@enmo trace]$

#以上標顏色底的記錄就是由sql_trace跟蹤所獲得的。

使用SQL_TEACE的跟蹤檔案分析執行計劃

相關文章