www.1q.co.kr(park1q.com)

ID :  Password : Auto  

   ȸ¿ø:0¸í / ¼Õ´Ô:0¸í
 

 

Technote

ÀÚ·á ¹× °ü¸®ÆÁ

  • DBA Notes
  • Q & A

    ºÏ¸¶Å©
  • Asktom
       (Oracle ÀÇ ´ëÇ¥ Forum)
  • Technical Bulltin(KR)
       (±â¼úÁö¿ø°Ô½ÃÆÇ)
  • Dbazine

  •  

     


     Trace
    park1q  2017-07-20 17:50:16, Á¶È¸ : 16,844, Ãßõ : 940

    1. alter session set events ¡®10046 trace name context forever,level 12¡Ç;

      alter session set tracefile_identifier=¡¯10046¡Ç;
      alter session set timed_statistics = true;
      alter session set statistics_level=all;
      alter session set max_dump_file_size = unlimited;

    Start trace:

      alter session set events ¡®10046 trace name context forever,level 12¡Ç;

    Stop trace:

      alter session set events ¡®10046 trace name context off¡¯;

    Note: level can be 4,8,12, only trace your own session
     2. Using package DBMS_MONITOR
    EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => 27, 
         serial_num => 60,waits => TRUE, binds => TRUE);
    EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id => 27, 
         serial_num => 60);
    Trace a module:
    EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(
         service_name=>'vasont.world', module_name=>'VasontU.exe',
         action_name=>DBMS_MONITOR.ALL_ACTIONS,waits=>TRUE,
         binds=>TRUE,instance_name=>NULL);
    EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE(
      service_name=>'vasont.world',module_name=>'VasontU.exe');
    Note: not available before 10g, can trace any session 

    3. Using package DBMS_SESSION

    EXECUTE DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE,
         binds => TRUE);
    EXECUTE DBMS_SESSION.SESSION_TRACE_DISABLE();
    Note: only trace your own session
     4. Using package DBMS_SYSTEM
    execute DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (sid=>507,
         serial#=>4957,sql_trace=>TRUE);
    execute DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (sid=>507,
         serial#=>4957,sql_trace=>FALSE);
     Note: available in 8i/9i/10g, can trace any session, package is wrapped.

     5. oradebug
    Trace session sid=58;select p.PID,p.SPID from v$process p,v$session s where s.paddr = p.addr and s.sid = 58;
           PID SPID
    ———- ———
            32 12943 
    connect / as sysdba
    oradebug setospid 12943 //or   oradebug setorapid 32
    oradebug unlimit
    oradebug event 10046 trace name context forever,level 12

    Stop trace:
    oradebug event 10046 trace name context off
    Note: available in 8i/9i/10g, can trace any session 
    6. Using DBMS_SUPPORT 
    exec DBMS_SUPPORT.START_TRACE_IN_SESSION(&SID, 
         waits=>true, binds=>true );
    exec DBMS_SUPPORT.STOP_TRACE_IN_SESSION( &SID , null );
    NOTE: need to install before use
    SQL> connect / AS SYSDBA
    SQL> @?rdbmsadmindbmssupp.sql
    SQL> GRANT execute ON dbms_support TO schema_owner;
    SQL> CREATE PUBLIC SYNONYM dbms_support FOR dbms_support; 
    7. Using trcsess
    trcsess  [output=output_file_name]
             [session=session_id]
             [clientid=client_id]
             [service=service_name]        
             [action=action_name]        
             [module=module_name]        
             [trace_files] 
    Trace a module:
    trcsess service=vasont.world module=VasontU.exe trc.log
    
    
    8. Tracing whole system 
    alter system set events '10046 trace name context forever,level 12';
    or
    event="10046 trace name context forever,level 12"
    Stop system wider trace:
    alter system set events '10046 trace name context off'; 
    9. Using trigger to start traces
    There may be some situations where it is necessary to trace
     the activity of a specific user. In this case a logon trigger
     could be used.
    An example is provided below:
      CREATE OR REPLACE TRIGGER SYS.set_trace
      AFTER LOGON ON DATABASE
      WHEN (USER like  '&USERNAME')
      DECLARE
          lcommand varchar(200);
      BEGIN
          EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
          EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';
          EXECUTE IMMEDIATE 'alter session set events ''10046 trace 
                                           name context forever, level 12''';
      END set_trace;
      /
     
    Last step, after trace processing, using tkprof
    tkprof vasont_ora_22103.trc vasont_ora_22103.trc.log sys=no
       waits=yes sort=(prscnt, execnt)


      ÃßõÇϱâ ÇÁ¸°Æ®   ¸ñ·Ïº¸±â

    Copyright 1999-2025 Zeroboard

     

     
     
    [Today:3 / Total:172120]    Design by p@rk1q