每逢与遇到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
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
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);
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 ;
【oracle数据库安装】oracle数据库10046的各种Case,方便trace信息的收集
http://m.bbyears.com/jiaocheng/114160.html
推荐访问:oracle数据库入门教程 oracle数据库面试题