Oracle Performance Analysis 1: Enabling SQL tracking and obtaining trace files

Oracle Performance Analysis 1: Enabling SQL tracking and obtaining trace files

When there is an issue with Oracle query efficiency, we often need to understand the problem so that we can provide a solution to the problem. Oracle provides the trace information for SQL Execution, including the text information of SQL statements, execution statistics, waiting during processing, and information generated during the parsing phase (such as generating an execution plan. This information helps you break down the SQL statement service time and wait time, and learn more about the resources used and synchronization points, so as to help you diagnose existing performance problems.
This article describes how to enable SQL tracking and obtain trace files. The details are as follows.

Enable SQL tracking

From the internal technical details, SQL tracking is based on 10046 debugging events. The following levels are supported:

0
Disable debugging events
1
Debugging events are activated. For each processed database call, the following information is given: SQL statement, response time, and service time.
Number of rows processed, number of rows processed, number of logical reads, number of physical reads and writes, execution plan, and additional information
4
If Level 1 includes additional information about the variable to be bound. Mainly data types, precision, and values used for each execution
8
Level 1, plus detailed information about the wait time. For each wait in the processing process, the following information is provided: the name, duration, and some additional parameters of the waiting time, indicating the resources to be waited
12
Both startup level 4 and Level 8
Before Oracle 9i or, the following SQL statement activates an SQL trace for the session:

alter session set sql_trace = true

You can also use the set_ SQL _trace stored procedure in the dbms_session package or the set_ SQL _tarce_in_session Stored Procedure Method in the dbms_system package. However, these methods are only used to activate SQL tracing at level 1, which is of little use in practice, I will not go into detail.
It is more useful to specify the level. The following is an SQL trace of the session start Level 12:

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

The statement used to disable SQL tracing for the session is as follows:

alter session set events '10046 trace name context off'

You can also use the set_ev stored procedure in the dbms_system package, which is not described here. I will focus on the methods provided after Oracle 10 Gb.
After 10 Gbit/s, Oracle provides the dbms_monitor package to enable or disable SQL tracking, and enables SQL tracking at the session, client, component, and database level. Note that only dba users are allowed to use SQL tracking.

Session level

The following PL/SQL statements enable SQL tracking at level 122 for sessions with ID 6734 and serial number 8th:

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

Session_id
Session id, which corresponds to the SID column in the v $ session view. The following describes how to obtain the current session id:

select userenv('sid') from dual

Serial_num
Corresponds to the SERIAL # column in the v $ session view. Because the SID is reused, SERIAL # is added when the SID is reused. The Obtaining method is as follows:

select serial# from v$session where sid = 122

Waits
Corresponding to SQL _TRACE_WAITS in the v $ session view, which indicates waiting for event tracking to be activated. The default value is true.

Binds
Corresponding to SQL _TRACE_BINDS in the v $ session view, which indicates whether the binding trace is activated. The default value is false.

After the execution is successful, SQL _TRACE in the v $ session view is modified to ENABLED. The corresponding values of SQL _TRACE_WAITS and SQL _TRACE_BINDS are set for you.
The following PL/SQL statements are used to disable SQL tracking:

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

Client-level

The following PL/SQL calls enable SQL tracking at level 8th for all sessions with the specified client Tag:

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

Note that the client tag is case sensitive. You can use the following method to check whether the setting is successful:

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

After the configuration is successful, you can enable SQL tracking by specifying the corresponding client tag before each query. The method for specifying the client tag is as follows:

Begin DBMS_SESSION.SET_IDENTIFIER ('test'); end; // The SQL trace for this session is enabled

After you set a flag for a session, you can view the flag in the client_identifier column of the v $ session.
The following PL/SQL statements are used to disable SQL tracking:

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

Component level

The following PL/SQL calls enable SQL tracking at level 8th for all sessions with the specified client Tag:

Begin dbms_monitor.serv_mod_act_trace_enable (service_name => 'ly ', module_name => 'pl/SQL Developer', action_name => 'SQL window-new', waits => true, binds => false, instance_name => null); end;

In the parameter, service_name corresponds to service_name in the v $ session view, module_name corresponds to module in the v $ session view, and action_name corresponds to action in the v $ session view. The query method is as follows:

SELECT sid, serial#,            client_identifier, service_name, action, module       FROM V$SESSION

You can view the settings as follows:

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

The following PL/SQL statements are used to disable SQL tracking:

Begin dbms_monitor.serv_mod_act_trace_disable (service_name => 'ly ', module_name => 'pl/SQL Developer', action_name => 'SQL window-new', instance_name => null); end;

Database-level

The following PL/SQL calls enable database-Level 12 SQL tracking:

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

The following method is used to check whether the setting is successful:

select instance_name,       waits,       binds  from dba_enabled_traces where trace_type = 'DATABASE'

The following PL/SQL statements are used to disable SQL tracking:

begin  dbms_monitor.database_trace_disable(instance_name => null);end;

Timing information in the trace file

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

alter session set timed_statistics = true

In general, the default value is true. If no timing information is provided, the trace file is useless. Therefore, before enabling SQL tracking, it is best to confirm that the parameter is set to true.

Obtain the generated trace file

After SQL tracking is enabled, a trace file is generated, and its directory is configured by initializing the user_dump_dest parameter. The value of this parameter can be obtained through the following method:

select name, value from v$parameter where name = 'user_dump_dest'

However, if you need to locate a specific file, you need to know the name of the trace file. The trace file name is independent of the version and platform. In most common platforms, the naming structure is as follows:

{Instance name }_{ process id}. trc

1) instance name
Initialize the lower-case value of instance_name. You can obtain this value through the instance_name column in the v $ instance view.
2) process name
The lower-case value of the process name that generates the trace file. For VPC processes, use ora. For Shared Server Processes, you can obtain them through the name column in the v $ diapatcher or v $ shared_server view. For parallel subordinate processes, you can use the server_name column of the v $ px_process view. For most background processes, you can use the name column of the v $ bgprocess view.
3) process id
Process tag at the operating system level. This value can be obtained through the spid column in the v $ process view.
Based on this information, you can obtain the trace file name in the following way:

select s.SID,       s.SERVER,       lower(case               when s.SERVER in ('DEDICATED', 'SHARED') then                i.INSTANCE_NAME || '_' || nvl(pp.SERVER_NAME, nvl(ss.NAME, 'ora')) || '_' ||                p.SPID || '.trc'               else                null             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

Replace the above 'your Sid' with your session sid to find out the name of the trace file generated by the specified session. The session sid is obtained in the v $ session view, or directly query the sid of the current session:

select userenv('sid') from dual

Combine the path (user_dump_dest) with the file name to obtain the complete path of the trace file.

Query the trace file generated by the current session is very simple in objective El 11g:

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