使用SQL_TEACE的跟踪文件分析执行计划
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
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
[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
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
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跟踪所获得的。来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31392094/viewspace-2129891/ ，如需转载，请注明出处，否则将追究法律责任。
