SQL_TRACE是Oracle提供的用于进行SQL跟踪的手段，是强有力的辅助诊断工具.后期升级版就是10046


打开方式
alter session set sql_trace=true;
alter database set sql_trace=true;


1.跟踪当前会话


定位当前会话

spid为14911

SQL>  alter session set sql_trace=true;


Session altered.


SQL> select /*+index_ffs(t ll)*/ distinct num1 from wh t where num1<10 order by num1;


      NUM1
----------
         0
         1
         4
         5
         7
         9


6 rows selected.


SQL>  alter session set sql_trace=false;


Session altered.


查看trace文件内容


SQL ID: g9m4jcpw16wuq Plan Hash: 451598605


select /*+index_ffs(t ll)*/ distinct num1 
from
 wh t where num1<10 order by num1




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.67       0.69          0      42887          0           6
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.67       0.69          0      42887          0           6


Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5  
Number of plan statistics captured: 1


Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         6          6          6  SORT UNIQUE (cr=42887 pr=0 pw=0 time=694613 us cost=12267 size=24 card=6)
     20480      20480      20480   INDEX FAST FULL SCAN LL (cr=42887 pr=0 pw=0 time=677329 us cost=12265 size=81936 card=20484)(object id 105309)


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


观察hash value为451598605的sql
Misses in library cache during parse: 1  产生了一次硬解析








2.跟踪其它用户会话

定位sid,serial#  71      50095 

另开一个session执行


SQL>  exec dbms_system.set_sql_trace_in_session(71,50095,true)


PL/SQL procedure successfully completed.


执行查询SQL


SQL>  exec dbms_system.set_sql_trace_in_session(71,50095,false)


PL/SQL procedure successfully completed.


查看内容


SQL ID: g9m4jcpw16wuq Plan Hash: 451598605


select /*+index_ffs(t ll)*/ distinct num1 
from
 wh t where num1<10 order by num1




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.79       0.79          0      42887          0           6
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.79       0.79          0      42887          0           6


Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 5  
Number of plan statistics captured: 1


Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         6          6          6  SORT UNIQUE (cr=42887 pr=0 pw=0 time=795220 us cost=12267 size=24 card=6)
     20480      20480      20480   INDEX FAST FULL SCAN LL (cr=42887 pr=0 pw=0 time=794319 us cost=12265 size=81936 card=20484)(object id 105309)




看到这个SQL产生了一个软解析






3.10046 跟踪当前会话
用sys用户登陆


ALTER SESSION SET EVENTS '10046 trace name context off'

SQL> select /*+index_ffs(t ll)*/ distinct num1 from system.wh t where num1<10 order by num1;


      NUM1
----------
         0
         1
         4
         5
         7
         9


6 rows selected.



查看内容


SQL ID: 84ux517u8xcy0 Plan Hash: 451598605


select /*+index_ffs(t ll)*/ distinct num1 
from
 system.wh t where num1<10 order by num1




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.78       0.79          0      42887          0           6
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.78       0.79          0      42887          0           6


Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1


Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         6          6          6  SORT UNIQUE (cr=42887 pr=0 pw=0 time=790210 us cost=12267 size=24 card=6)
     20480      20480      20480   INDEX FAST FULL SCAN LL (cr=42887 pr=0 pw=0 time=788814 us cost=12265 size=81936 card=20484)(object id 105309)




Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        6.06          6.06




4.10046跟踪其它会话

其它SESSION执行SQL



查看内容




SQL ID: 84ux517u8xcy0 Plan Hash: 451598605


select /*+index_ffs(t ll)*/ distinct num1 
from
 system.wh t where num1<10 order by num1




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.77       0.77          0      42887          0           6
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.77       0.77          0      42887          0           6


Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 5  
Number of plan statistics captured: 1


Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         6          6          6  SORT UNIQUE (cr=42887 pr=0 pw=0 time=778139 us cost=12267 size=24 card=6)
     20480      20480      20480   INDEX FAST FULL SCAN LL (cr=42887 pr=0 pw=0 time=776907 us cost=12265 size=81936 card=20484)(object id 105309)




Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message from client                     2       48.11         48.11
  SQL*Net message to client                       2        0.00          0.00