漫活部落

Oracle Events 10046 是用來搜集 SQL_TRACE 相關數據的標準方法
(Event 10046 is the standard method of gathering extended SQL_TRACE information for Oracle sessions.)

10046 Event levels

10046 EVENT levels:  (the new sql_trace values are included in [..])
  These are bit values so can be ORed together to get different mixes
        1  - Enable standard SQL_TRACE functionality (Default)
        4  - As Level 1 PLUS trace bind values           [ bind=true ]
        8  - As Level 1 PLUS trace waits                 [ wait=true ]
             This is especially useful for spotting latch wait etc. 
             but can also be used to spot full table scans and index scans.

  As of 11g these additional bit levels are available:
        16 - Generate STAT line dumps for each execution [ plan_stat=all_executions ]
        32 - Never dump execution statistics             [ plan_stat=never ]

  As of 11.2.0.2 this additional bit level is available:
        64 - Adaptive dump of STAT lines.                [ plan_stat=adaptive ]
             This dumps the STAT information if a SQL took more than about 1 minute thereby 
             giving information for the more expensive SQLs and for different executions of such
             SQLs.

  eg: A common event level is 12 which includes standard SQL_TRACE output, binds, waits and 
        default STAT line tracing.

 

記錄自己 session sql_trace

1. 開啟 events 10046 capture current session 
(enable events 10046)

SQL> alter session set events '10046 trace name context forever, level 12'; 

2. 關閉 events 10046
(close events 10046)

SQL> alter session set events '10046 trace name context off';

3. 查詢自己 session 的 tracefile 位置

SQL> select value from v$diag_info where name = 'Default Trace File';

 

oradebug 記錄其他 session

1. 查詢出 OSPID (spid)

select p.pid,p.spid,s.sid
from v$process p,v$session s
where s.paddr = p.addr
and s.sid = &SESSION_ID;

column line format a79
set heading off
select 'ospid: ' || p.spid || ' # ''' ||s.sid||','||s.serial#||''' '||
  s.osuser || ' ' ||s.machine ||' '||s.username ||' '||s.program line
from v$session s , v$process p
where p.addr = s.paddr
and s.username <> ' ';

2. 設定 setorapid 為前步驟查詢的 spid
(對於12c之後有multi threaded的架構，需要額外查詢 v$process.stid 欄位

SQL> connect / as sysdba
SQL> oradebug setorapid <spid>
SQL> oradebug unlimit
SQL> oradebug event 10046 trace name context forever,level 12

-- For 12c later
SQL> oradebug setospid <spid> <stid> 

 

Trace to report (Example)

$ tkprof $ROACLE_BASE/diag/rdbms/$ORACLE_UNQNAME/$ORACLE_SID/orcl1_ora_17077.trc
output = 123.txt

TKPROF: Release 19.0.0.0.0 - Development on Mon Feb 21 14:53:34 2022

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

[oracle@testdb trace]$ cat 123.txt 

TKPROF: Release 19.0.0.0.0 - Development on Mon Feb 21 14:53:34 2022

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Trace file: orcl1_ora_17077.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
********************************************************************************
Trace file: orcl1_ora_17077.trc
Trace file compatibility: 12.2.0.0
Sort options: default

       1  session in tracefile.
       0  user  SQL statements in trace file.
       0  internal SQL statements in trace file.
       0  SQL statements in trace file.
       0  unique SQL statements in trace file.
      38  lines in trace file.
       0  elapsed seconds in trace file.

 

 

參考：

How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues (Doc ID 376442.1)

EVENT: 10046 "enable SQL statement tracing (including binds/waits)" (Doc ID 21154.1)

 SQL_TRACE (10046), TKProf and Explain Plan - Overview Reference (Doc ID 199081.1)

已轉向 blogger 記錄

https://slowlife-notes.blogspot.com