Oracle Performance Analysis 1: Turn on SQL tracing and get trace files

When Oracle queries come with efficiency issues, we often need to understand the problem so that we can solve the problem. Oracle provides trace information for SQL execution, which contains textual information about SQL statements, some execution statistics, waiting during processing, and information generated by the parsing phase, such as generating execution plans. This information helps you to diagnose performance problems by breaking down the service time and wait times for SQL statements, and knowing the details of the resources and synchronization points that are being used.
This article describes how to turn on SQL tracing and get trace files, as detailed below.

Turn on SQL tracing

From the internal technical details, SQL Trace is based on 10046 debug events, and the following are the supported levels:

0
Prohibit Debug events
1
The debug event is active. For each database call that is processed, given the following information: SQL statement, Response time, service time
Number of rows processed, number of rows processed, number of logical reads, number of physical reads and writes, execution plan, and some additional information
4
If Level 1, includes additional information about the bound variable. Primarily data types, precision, and values used each time they are executed
8
Same level 1, plus details about the wait time. To handle each wait in the process, provide the following information: The name of the Wait time, the duration, and some additional parameters that indicate the resources you are waiting for
A
Simultaneous start level 4 and Level 8
In the Oracle 9i or before, the following SQL statement activates SQL tracing for the session in which it is located:

Alter session Set Sql_trace = True

You can also pass the Set_sql_trace stored procedure in the Dbms_session package, or the Set_sql_tarce_in_session stored procedure method of the Dbms_system package, but these are only activated in Level 1 SQL Trace, It is not very useful in practice, it is not detailed.
More useful is the way to specify the level, below is the SQL Trace for the session starting level 12:

Alter session SET Events ' 10046 Trace name Context forever, Level 12 '

The following statements prohibit SQL tracing for the session in which they are located:

Alter session SET Events ' 10046 Trace name context off '

You can also pass the Set_ev stored procedure in the Dbms_system package, which is not detailed here, and I'll focus on the methods provided after Oracle 10g.
Oracle 10g provides dbms_monitor packages to turn SQL tracing on or off, providing the option to turn on SQL tracing at the session, client, component, and database levels, noting that only users with DBA roles are allowed.

Session-level

The following PL/SQL is for a session with an ID of 122 and a serial number of 6734 to turn on level 8th:

Begin  Dbms_monitor.session_trace_enable (session_id = 122,                                    serial_num = 6734,                                    waits      = True,                                    binds      = false); end;

session_id
Session ID, corresponding to the SID column in the V$session view, here's how to get 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, and when the SID is reused, the serial# is incremented to get the following method:

Select serial# from v$session where sid = 122

Waits
Corresponds to Sql_trace_waits in the V$session view, which indicates whether the wait event trace is activated and is true by default.

Binds
Corresponds to Sql_trace_binds in the V$session view, indicating whether the binding trace is activated, false by default.

After successful execution, the sql_trace in the V$session view is modified to the corresponding value set for you by Enabled,sql_trace_waits and Sql_trace_binds.
The following PL/SQL is used to turn off the SQL Trace:

Begin  Dbms_monitor.session_trace_disable (session_id = 122, serial_num = 6734); end;

Client-level

The following PL/SQL call turns on 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 client tags are case-sensitive, and you can see if the settings are successful in the following ways:

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

When the settings are successful, you can turn on SQL tracing by specifying the corresponding client tag before each query, specifying the client tag as follows:

Begin  Dbms_session. Set_identifier (' test '); end;//SQL Trace for this session is already open

After you have set a tag for a session, you can view the tag in the Client_identifier column of V$session.
The following PL/SQL is used to turn off the SQL Trace:

Begin  Dbms_monitor.client_id_trace_disable (client_id = ' test '); end;

Component-level

The following PL/SQL call turns on 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;

The service_name in the parameter corresponds to the module,action_name of the V$session view for the service_name,module_name of the v$session view corresponding to the V$session view of the action, Here's how to query:

SELECT SID, Serial#,            Client_identifier, service_name, action, module from       v$session

After setting, you can view the settings in the following ways:

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 is used to turn off the SQL Trace:

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 call turns on the database's level 12-trace:

Begin  Dbms_monitor.database_trace_enable (Waits         = True,                                     binds         = True,                                     Instance_ name = null); end;

The following methods see if the settings are successful:

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

The following PL/SQL is used to turn off the SQL Trace:

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 a trace file:

Alter session Set Timed_statistics = True

In general, the default is true, and if you do not provide timing information, trace files are useless, so before you turn on SQL tracing, it is a good idea to verify that the parameters are set to true.

Get the generated trace file

When SQL tracing is turned on, a trace file is generated, and the value of the parameter can be obtained by using the initialization parameter user_dump_dest to configure its directory:

Select name, value from V$parameter where name = ' User_dump_dest '

But if we need to navigate to a specific file, we need to know the name of the trace file. The name of the trace file is independent of version and Platform, and under most common platforms, the naming structure is as follows:

{Instance name}_{process name}_{process id}.trc

 1) Instance name
  Initialize the lowercase value of the parameter instance_name. This value can be obtained through the instance_name column of the V$instance view.
 2) process name
  Lowercase value that produces the name of the trace file process. For proprietary server processes, using Ora, for shared server processes, can be obtained through the name column of the V$diapatcher or V$shared_server view. For a parallel dependent process, it can be obtained through the V$px_process View server_name column, which is available through the Name column of the v$bgprocess view for most other background processes.
 3) Process ID
  OS-level markup. This value can be obtained through the SPID column of the v$process view.
Based on this information, you can get the trace file name in the following way:

Select S.sid,       s.server,       lower (case               "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 ' your SID ' above with the SID of your session to find out the name of the trace file generated by the session, the SID of the session is found in the V$session view, or the SID of the current session is queried directly:

Select Userenv (' Sid ') from dual

By combining the path (user_dump_dest) with the file name, we get the full path to the trace file.

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

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