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

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

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'

 

Oracle SQL _trace: How can I check what SQL has been executed by oracle just now?

Select t. LAST_LOAD_TIME, t .*
From v $ sqlarea t where upper (t. SQL _TEXT) like '% table name %'
And t. SQL _TEXT like '% SELECT %'
Order by t. LAST_LOAD_TIME desc

How to view the execution plan of oracle SQL

1. Use the PL/SQL Dev Tool
1. directly File-> New-> Explain Plan Window. Execute SQL statements in the Window to view the Plan results. Among them, Cost indicates the cpu consumption, unit is n %, Cardinality indicates the number of lines executed, equivalent to Rows.
2. Execute the explain plan for select * from tableA where paraA = 1, and then select * from table (DBMS_XPLAN.DISPLAY) to view the oracle execution PLAN, the result is the same as that in 1. Therefore, we recommend that you use the 1 method when using the tool.
Note: The Command window of PL/SQL Dev does not support the set autotrance on Command. You can also use tools to view the information you see in the plan. Sometimes we need sqlplus support.

Ii. Use sqlplus
1. The simplest way
SQL> set autotrace on
SQL> select * from dual;
After the statement is executed, the explain plan and statistics are displayed.
The advantage of this statement is its disadvantage. When you use this method to view SQL statements that have been executed for a long time, you must wait until the statement is successfully executed before returning the execution plan, this greatly increases the optimization cycle. If you do not want to execute the statement but want to execute the plan, you can use:
SQL> set autotrace traceonly
In this way, only the execution plan will be listed, rather than the actual execution statement, greatly reducing the optimization time. Although the statistics are also listed, the statistics are useless because no statement is executed. If an error occurs while executing the Statement, the solution is as follows:
(1) users to be analyzed:
Sqlplus> @?
Dbmsadminutlxplan. SQL
(2) log on with the sys user
Sqlplus> @? Sqlplusadminplustrce. SQL
Sqlplus> grant plustrace to user_name;
--User_name is the analysis user mentioned above.

2. Use the explain plan command
(1) sqlplus> explain plan for select * from testdb. myuser
(2) sqlplus> select * from table (dbms_xplan.display );
The above two methods can only generate execution plans for the statements that are running in this session, that is, we need to know which statements are running very poorly, we aim to optimize this SQL statement only. In fact, in many cases, we only hear one customer complain that the system is running slowly, but we do not know which SQL is causing it. At this time, there are many ready-made statements to find the statements that consume more resources, such:
Select address, substr (SQL _TEXT, 1, 20) Text, buffer_gets, executions,
Buffer_gets/executions avg from v $ sqlarea
WHERE executions> 0 AND buffer_gets> 100000 order by 5;
Address text BUFFER_GETS EXECUTIONS AVG
-------- ---------------------------------------- ----------- -----------...... The remaining full text>