ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Recommended Method for Obtaining 10046 trace for Tuning

Recommended Method for Obtaining 10046 trace for Tuning

原创 Linux操作系统 作者:xhailiang 时间:2006-11-02 00:00:00 0 删除 编辑
For Tuning issues the typical need is to record wait and bind variable infrormation which is achieved using level 12.
The following examples outline how to set the event in various scenarios:

Event 10046 tracing will produce a trace file in the for user processes and for background processes.
To show the loaction of the user_dump_dest, the following command can be used:


  show parameter user_dump_dest

Note that some examples include setting a 'tracefile_identifier' to assist with finding the resultant trace output.
  • Session Tracing

    This tracing can be used where the session is accessible to the user prior to the start of the statement(s) to be traced.
    To gather 10046 trace at the session level:
    
      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;
    
      alter session set events '10046 trace name context forever,level 12';
    
      -- Execute the queries or operations to be traced here --
    
      select * from dual;
      exit;
    
    
    If the session is not exited then the trace can be disabled using:
    
      alter session set events '10046 trace name context off';
    
    
    Note that if the session is not closed cleanly and tracing is disabled, then important trace information may be missing from the trace file.
  • Tracing a process after it has started

    If trace from an existing session is required then oradebug can be used to attach to the session and initiate 10046 tracing.

    1. The first step is to identify the session to be traced by some means:

      For example, in SQL*Plus, start a session to find the OS process id (spid) for the target session:
      
        select p.PID,p.SPID,s.SID
        from v$process p,v$session s
        where s.paddr = p.addr
        and s.sid = &SESSION_ID
        /
      
      
      SPID is the operating system Process identifier (os pid)
      PID is the Oracle Process identifier (ora pid)
    2. Once the OS process id for the process has been determined then the trace can be initialised as follows:

      Lets assume that the process to be traced has an os pid of 9834.
      Login to SQL*Plus as a dba and execute the following:
      
        connect / as sysdba
        oradebug setospid 9834
        oradebug unlimit
        oradebug event 10046 trace name context forever,level 12
      
      
      Remember to replace the example '9834' value with the actual os pid.
    Note that it is also possible to attach to a session via oradebug using the 'setorapid'.
    In this case the PID (Oracle Process identifier ) would be used (rather than the 'SPID') and the oradebug text would change to:
    
      connect / as sysdba
      oradebug setorapid 9834
      oradebug unlimit
      oradebug event 10046 trace name context forever,level 12
    
    
    Remember to replace the example '9834' value with the actual ora pid.

    To disable oradebug tracing once tracing is finished:
    
      oradebug event 10046 trace name context off 
    
    
    Alternatively you can use the DBMS_SUPPORT package to trace sessions.

    To set session tracing using the DBMS_SUPPORT package:
    
      exec DBMS_SUPPORT.START_TRACE_IN_SESSION( &SID, waits=>true, binds=>true )
    
    
    SID is the Oracle Session ID for the session that is to be traced.
    To disable the tracing use:
    
      exec DBMS_SUPPORT.STOP_TRACE_IN_SESSION( &SID , null )
    
    
  • Instance wide tracing

    This setting will trace every session that is created after the parameter is set. Existing sessions will not be traced.

    Setting system-wide 10046 tracing can be useful for scenarios where a problem session is known to occur but cannot be identified in advance.
    In this situation, tracing can be enabled for a short period of time, the problem can then be reproduced and tracing disabled and the resultant traces searched for evidence of the problem.

    System-wide tracing can be enabled as follows:
    
      alter system set events '10046 trace name context forever,level 12';
    
    
    The setting can be disabled in all sessions by using the following command:
    
      alter system set events '10046 trace name context off';
    
    
  • Initialisation parameter setting

    This setting will trace every session in the instance when it is restarted.
    
      event="10046 trace name context forever,level 12"
    
    
    The setting can be disabled by removing the parameter and restarting the instance or by using an alter system command as follows:
    
      alter system set events '10046 trace name context off';
    
    
  • Via a Logon Trigger

    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;
      /
    
    

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/308563/viewspace-171918/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-02-17

  • 博文量
    270
  • 访问量
    414554