怎樣理解trace信息的收集

138次閱讀

共計 4914 個字符，預計需要花費 13 分鐘才能閱讀完成。

本篇文章為大家展示了怎樣理解 trace 信息的收集，內容簡明扼要并且容易理解，絕對能使你眼前一亮，通過這篇文章的詳細介紹希望你能有所收獲。

每逢與遇到 SQL 相關性能，我們總是需要收集 10046 的，來查看和診斷問題。
因為 10046 真實的反應的 SQL 語句執行的時候的真實信息，解析，執行，獲取的時間消耗，row source operation 的具體情況。
具體等待事件，每個時間具體的時間消耗等等。希望下面的 Case 有一種就能幫助到您。

EVENT: 10046 enable SQL statement tracing (including binds/waits) (Doc ID 21154.1)
Interpreting Raw SQL_TRACE output (Doc ID 39817.1)
General SQL_TRACE / 10046 trace Gathering Examples (Doc ID 1274511.1)

==================
SQL 性能常用：
所有版本
10046 on session/system
To start tracing:
Alter session/system(慎用) set events 10046 trace name context forever, level 12
/* execute your selects to be traced */

To stop tracing
Alter session/system(慎用) set events 10046 trace name context off

11g 以上
1. event++ 在 system 級別指定 sql_id, 對新起的會話和當前的會話有效，對其他已經存在的會話無效
SQL alter system set events sql_trace [sql: 5qcyrymp65fak] level=12

注釋：當前事件對當前的 session 和新創建的 session 有效，對已經存在的其他 session 無效。
關閉 event ++：
SQL alter system set events sql_trace [sql: 5qcyrymp65fak] off

2. event ++ 指定某個 process 的 sql_id
SQL oradebug setospid SPID 指定檢測的會話的 spid select spid from V$process, V$session where audsid=userenv(SESSIONID) and paddr=addr;
SQL oradebug unlimit
SQL oradebug tracefile_name
SQL oradebug event sql_trace [sql: 5qcyrymp65fak] level=12

關閉 event ++：
SQL oradebug event sql_trace [sql: 5qcyrymp65fak] off

3. 不知道 SQL_ID 手動執行 SQL 收集 10046
SQL connect username/password
SQL alter session set timed_statistics = true;
SQL alter session set statistics_level=all;
SQL alter session set max_dump_file_size = unlimited;
SQL select value from v$diag_info where name= Default Trace File 在 11g 以上工作
SQL variable a1 the type of ACCOUNT_TYPE_ID 請執行類型
SQL exec :a1 := 123123 或 abded 請設置數值或字符串
SQL alter session set events 10046 trace name context forever, level 12
SQL UPDATE /*+ RESTRICT_ALL_REF_CONS */ LBI_ODS . T_O_CUSTOMER_ACCOUNT SET
ACCOUNT_TYPE_ID = :a1
WHERE
ACCOUNT_NO = 1234565; 執行 sql 重現問題
SQL alter session set events 10046 trace name context off

==================
使用 Trigger 設置 10046
Use a Logon TriggerTo start tracing:
create or replace trigger user_logon_trg
after logon on database
begin
if USER = xxxx then
execute immediate
Alter session set events 10046 trace name context forever, level 8
end if;
end;
/

/* Login a new session as User xxxx and execute your selects to be traced */

To stop tracing: via LogOff Trigger (needs to be created before logging off)
create or replace trigger user_logoff_trg
before logoff on database
begin
if USER = xxxx then
execute immediate
Alter session set events 10046 trace name context off
end if;
end;
/

==================
MMON 的 10046
1. 請打開 auto purge 的 trace？

begin
dbms_monitor.serv_mod_act_trace_enable
(service_name= SYS$BACKGROUND ,
module_name= MMON_SLAVE ,
action_name= Auto-Purge Slave Action
end;
/

2. 請至少等待一天，請您明天查看時候 auto purge 被執行，并產生 m00x trace 文件包含 10046

3. 關閉 auto purge 的 trace
begin
dbms_monitor.serv_mod_act_trace_disable
(service_name= SYS$BACKGROUND ,
module_name= MMON_SLAVE ,
action_name= Auto-Purge Slave Action
end;
/

==================
Data pump 10046
1. enable 10046 trace for DM/DW process

alter system set events sql_trace{process: pname=dw | pname=dm} level=12

2. Please reproduce the issue, then add TRACE=480300 in data pump importing command

3. Please upload data pump importing log and the generated DM/DW process trace

To disable the tracing by issuing:

alter system set events sql_trace {process : pname = dw | pname = dm} off

==================
其他方式設置 10046
1. DBMS_SUPPORTTo start tracing:
exec sys.dbms_support.start_trace ;
/* execute your selects to be traced */

To stop tracing:
exec sys.dbms_support.stop_trace ;
Tracing from Another SessionThe examples below demonstrate how to trace session with SID=18 and Serial# =226 obtained from V$SESSION.

2. Using dbms_system.SET_BOOL_PARAM_IN_SESSION To start tracing:
exec sys.dbms_system.SET_BOOL_PARAM_IN_SESSION(18, 226, sql_trace , TRUE);
/* execute your selects to be traced */
To stop tracing:
exec sys.dbms_system.SET_BOOL_PARAM_IN_SESSION(18, 226, sql_trace , FALSE);

3. Using dbms_system.set_ev To start tracing:
exec dbms_system.set_ev(18, 226, 10046, 12,

To stop tracing:
exec dbms_system.set_ev(18, 226, 10046, 0,
4. Using dbms_system.set_sql_trace_in_session To start tracing:
exec dbms_system.set_sql_trace_in_session(18,226,TRUE);
/* execute your selects to be traced */
To stop tracing:
exec dbms_system.set_sql_trace_in_session(18,226,FALSE);
5. Using sys.dbms_monitor To start tracing:
exec sys.dbms_monitor.session_trace_enable(session_id= 18,serial_num= 226, waits= true, binds= true);
/* execute your selects to be traced */

To stop tracing:
exec sys.dbms_monitor.session_trace_disable(session_id= 18,serial_num= 226);

http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_monitor.htm#CFAHBEAB
CLIENT_ID_STAT_DISABLE Procedure
CLIENT_ID_STAT_ENABLE Procedure
CLIENT_ID_TRACE_DISABLE Procedure
CLIENT_ID_TRACE_ENABLE Procedure
DATABASE_TRACE_DISABLE Procedure
DATABASE_TRACE_ENABLE Procedure
SERV_MOD_ACT_STAT_DISABLE Procedure
SERV_MOD_ACT_STAT_ENABLE Procedure
SERV_MOD_ACT_TRACE_DISABLE Procedure
SERV_MOD_ACT_TRACE_ENABLE Procedure
SESSION_TRACE_DISABLE Procedure
SESSION_TRACE_ENABLE Procedure

6. Using Oradebug (as SYS)To start tracing:
oradebug setospid xxxx
oradebug event 10046 trace name context forever, level 12;
/* In the session being traced execute the selects */

To stop tracing:
oradebug event 10046 trace name context off ;

上述內容就是怎樣理解 trace 信息的收集，你們學到知識或技能了嗎？如果還想學到更多技能或者豐富自己的知識儲備，歡迎關注丸趣 TV 行業資訊頻道。

正文完