本篇文章为大家展示了怎样理解trace信息的收集,内容简明扼要并且容易理解,绝对能使你眼前一亮,通过这篇文章的详细介绍希望你能有所收获。
10年积累的网站设计制作 、做网站经验,可以快速应对客户对网站的新想法和需求。提供各种问题对应的解决方案。让选择我们的客户得到更好、更有力的网络服务。我虽然不认识你,你也不认识我。但先网站设计 后付款的网站建设流程,更有惠民免费网站建设 让你可以放心的选择与我们合作。
每逢与遇到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 <<<<<<<<<< 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 ; <<<<<请执行类型 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信息的收集,你们学到知识或技能了吗?如果还想学到更多技能或者丰富自己的知识储备,欢迎关注创新互联行业资讯频道。
名称栏目:怎样理解trace信息的收集
文章URL:
http://cdxtjz.cn/article/jgidji.html