you're reading...
Oracle Database Performance Tuning

Oracle performance Analysis Section1: Open SQL trace file tracking and acquisition

When Oracle query efficiency problems arise, we often need to understand the problem, so as to give solutions for the problem. Oracle provides SQL execution trace information, which includes the text of the SQL statement, the implementation of some statistical information, the process of waiting, and an analysis phase (such as the execution plan) generated. This information can help you break down the service time and the waiting time sql statement, and resources for more information and synchronization points with to help you diagnose performance problems.
This article describes how to open SQL trace file tracking and acquisition, as detailed below.

Open SQL Trace

In detail, SQL trace is based on 10046 debug event is supported by the following levels:


Prohibition debug event


Debug event is active. For each database call is processed, given the following information: SQL statement, response time, service time
Number of rows processed, the number of rows processed, the number of logical reads, physical reads and writes, as well as some additional information on the implementation plan


If Level 1, including bind variables additional information. The main data type, precision, and the values used for each execution


With Level 1, plus details about the waiting time. In order to process each wait, provide the following information: the name of the waiting time, duration, and some additional parameters, can be marked waits resources


Meanwhile Start Level 4 and Level 8
Prior to Oracle 9i or, where the following SQL statements against SQL trace session activation:

alter session set sql_trace = true;

dbms_session package can also use set_sql_trace stored procedure, or stored procedure approach by set_sql_tarce_in_session of the dbms_system package, but these are only activated at Level 1 SQL tracing, of little use in practice, it would not elaborate.

More useful is the way specified level, where the start of the session following is a SQL Trace Level 12:

alter session set events '10046 trace name context forever, level 12';

Corresponding to a session where prohibited SQL trace statement is as follows:

alter session set events '10046 trace name context off';

It can also dbms_system package set_ev stored procedure is not described in detail here, the way I talk about the following highlights after Oracle 10g provides.

Oracle provides dbms_monitor package to enable or disable SQL tracing, provides the session, the client, components, and open SQL database-level tracking method, note that only the dba role users are allowed to use after 10g.

Session Level

The following PL/SQL as the ID 122, serial number 6734 session open track section 8 of SQL:

  dbms_monitor.session_trace_enable(session_id => 122,
                                    serial_num => 6734,
                                    waits      => true,
                                    binds      => false);


session ID, corresponding to the v$session view SID column, the following is a method to get the current session id:

select userenv('sid') from dual;


Corresponding to v$session view SERIAL# columns, as SID will reuse reused when SID, SERIAL# increased acquisition method as follows:

select serial# from v$session where sid = 122;


Corresponding to v$session view SQL_TRACE_WAITS, waiting for the event to track whether express is activated, the default is true.


Corresponding to v$session view SQL_TRACE_BINDS, it indicates whether the binding tracking is activated by default false.

When executed successfully, v$session view SQL_TRACE been modified to ENABLED, SQL_TRACE_WAITS and SQL_TRACE_BINDS corresponding value you set.
The following PL / SQL is used to close SQL Trace:

  dbms_monitor.session_trace_disable(session_id => 122, serial_num => 6734);

Client level

The following PL/SQL calls for all sessions with the specified client mark the opening paragraph 8 of SQL trace:

  dbms_monitor.client_id_trace_enable(client_id => 'test',
                                      waits     => true,
                                      binds     => false);

Note that client tags are case sensitive, you can see whether successfully set through the following methods:

select primary_id as client_id, waits, binds
from dba_enabled_traces
where trace_type = 'CLIENT_ID';

Once set up, before each query specifies mark the corresponding client can open SQL trace method specifies the client marked as follows:

//SQL trace of the session has been opened

When you set up labeled as a session, you can see the mark in the column v$session of client_identifier. The following PL / SQL is used to close SQL Trace:

  dbms_monitor.client_id_trace_disable(client_id => 'test');

Component level

The following PL/SQL calls for all sessions with the specified client mark the opening paragraph 8 of SQL trace:

  dbms_monitor.serv_mod_act_trace_enable(service_name  => 'service',
                                         module_name   => 'PL/SQL Developer',
                                         action_name   => 'SQL Window - New',
                                         waits         => true,
                                         binds         => false,
                                         instance_name => null);

Service_name parameter corresponding to v$session view service_name, module_name v$session view corresponding to the module, action_name v$session view the corresponding action, the following query:

SELECT sid, serial#, client_identifier, service_name, action, module

After setting can view the settings by the following method:

select primary_id    as service_name,
       qualifier_id1 as module_name,
       qualifier_id2  as action_name,
from dba_enabled_traces
where trace_type = 'SERVICE_MODULE_ACTION';

The following PL/SQL is used to close SQL Trace:

  dbms_monitor.serv_mod_act_trace_disable(service_name  => 'ly',
                                          module_name   => 'PL/SQL Developer',
                                          action_name   => 'SQL Window - New',
                                          instance_name => null);

Database Level

The following PL/SQL call to open the 12 SQL database tracking:

  dbms_monitor.database_trace_enable(waits         => true,
                                     binds         => true,
                                     instance_name => null);

The following methods to view the setup is successful:

select instance_name,
from dba_enabled_traces
where trace_type = 'DATABASE';

The following PL / SQL is used to close SQL Trace:

  dbms_monitor.database_trace_disable(instance_name => null);

Timing trace file information

The following statement is used to provide timing information for the trace file:

alter session set timed_statistics = true;

The following statement generally defaults are true, if you do not provide time information, trace file is no use, so before you turn SQL trace, confirm the best parameter is set to true. It is used to provide timing information for the trace file:

Get generated trace file

SQL tracing is turned on, it generates a trace file, initialization parameter user_dump_dest configure it in a directory, the value of this parameter can be obtained through the following methods:

select name, value from <b>v$parameter</b> where name = 'user_dump_dest';

But if we need to locate a specific file, you need to understand the trace file name. trace file name is independent of the version and platform, in most popular platforms, naming structure is as follows:

{instance name} _ {process name} _ {process id} .trc
  • instance name
  • Instance_name initialization parameter lowercase value. Through v$instance instance_name view columns can get this value.

  • process name
  • The process of generating a tracking file name lowercase value. For proprietary server process, use ora, shared server process, you can v$diapatcher or view v$name column shared_server obtained. For parallel slave process, you can view v$px_process get server_name column, for most other background processes, it can be obtained through the column name v$bgprocess view.

  • process id
  • OS-level process tags. This value can be v$process view spid column to obtain.

    Based on this information, you can obtain the trace file name in the following way:

    		select s.SID,
                   when s.SERVER in ('DEDICATED', 'SHARED') then
                    i.INSTANCE_NAME || '_' || nvl(pp.SERVER_NAME, nvl(ss.NAME, 'ora')) || '_' ||
                    p.SPID || '.trc'
                 end) as trace_file_name
    		from v$instance      i,
    			 v$session       s,
                 v$process       p,
                 v$px_process    pp,
                 v$shared_server ss
    		where s.PADDR = p.ADDR
    			  and s.SID = pp.SID(+)
    			  and s.PADDR = ss.PADDR(+)
    			  and s.TYPE = 'USER'
    			  and s.SID = 'your sid'
    			  order by s.SID

    The above ‘your sid’ replaces the sid your session you can find out the name of the specified session trace file is generated, session of sid obtained in v$session view, or direct access to the current session of sid:

    		select userenv('sid') from dual;

    The path (user_dump_dest) and the file name together, we get the full path of the trace file.

    In Oracel 11g, and query the current session trace file generated is very simple:

    		select value from v$diag_info where name = 'Default Trace File';

About daviewning

I am an Oracle DBA


No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: