Use SQL _trace for database Diagnosis

SQL _trace is a powerful auxiliary diagnostic tool provided by Oracle for SQL tracking. SQL _trace is a very common method in daily database problem diagnosis and solution.
This article briefly discusses the use of SQL _trace and describes the use of SQL _trace through specific cases.

I. Basic Introduction

(A) SQL _trace description

SQL _trace can be globally enabled as an initialization parameter, or it can be enabled in a specific session through a command line.
1. Enable globally
In the parameter file (pfile/spfile), specify:

SQL _trace = true

When SQL _trace is enabled globally, activities of all processes will be tracked, including background processes and all user processes. This usually causes serious performance problems.
Exercise caution.
Tip: by enabling SQL _trace globally, we can track the activities of all background processes. Many abstract descriptions in this document can be clearly defined by tracking real-time file changes.
See the close coordination between processes.

2. Set at the current session level
Most of the time, we use SQL _trace to track the current process. By tracking the current process, we can find the background database recursive activity of the current operation (this is especially effective when studying new database features ),
Study SQL Execution and background errors.
To enable and stop SQL _trace at the session level, follow these steps:

Enable the tracking of the current session: SQL> alter session set SQL _trace = true; Session altered. at this time, the SQL operation will be tracked: SQL> select count (*) from dba_users; count (*) ---------- 34 end tracking: SQL> alter session set SQL _trace = false; Session altered.

3. Tracking other user processes
In many cases, we need to track the processes of other users, rather than the current user. This can be done through the system package dbms_system. set_ SQL _trace_in_session provided by Oracle.
To complete

Set_ SQL _trace_in_sessionProgramThree parameters must be provided:

SQL> DESC dbms_system... Procedure set_ SQL _trace_in_session argument name type in/out default? ------------------------------ --------------------- ------ -------- SID number in serial # number in SQL _trace Boolean in...

Through V $ session, we can obtain Sid, serial #, and other information:

Obtain process information and select the process to be tracked: SQL> select Sid, serial #, username from V $ Session 2 where username is not null; sid serial # username ---------- ------------------------------ 8 2041 sys 9 437 eygle settings follow: SQL> exec encrypted (9,437, true) PL/SQL procedure successfully completed. .... Wait for a moment, track the session to execute tasks, and capture SQL operations ....... Stop tracking: SQL> exec dbms_system.set_ SQL _trace_in_session (9,437, false) PL/SQL procedure successfully completed.

(B) 10046 Event Description
10046 events are internal events provided by Oracle and are enhancements to SQL _trace.
10046 you can set the following four levels for an event:
1-enable the standard SQL _trace function, which is equivalent to SQL _trace
4-Level 1 and bind value (BIND values)
8-level 1 + waiting for event tracking
12-level 1 + Level 4 + level 8
Similar to SQL _trace, 10046 events can be set globally or at the session level.
1. Global settings
Add the following to the parameter file:

Event = "10046 trace name context forever, Level 12"

This setting takes effect for all processes of all users, including background processes.

2. Set the current session
To modify the statement by using alter Session, the system permission of alter session is required:

SQL> alter session set events '10046 trace name context forever '; Session altered. SQL> alter session set events '10046 trace name context forever, level 8'; Session altered. SQL> alter session set events '10046 trace name context off'; Session altered.

3. Set sessions for other users
Dbms_system.set_ev system package:

 

SQL> DESC dbms_system... procedure set_ev argument name type in/out default? ------------------------------ --------------------- ------ -------- Si binary_integer in SE binary_integer in EV binary_integer in Le binary_integer in NM varchar2 in...

The Si and Se parameters are from the V $ session View:

Query the session information to be tracked: SQL> select Sid, serial #, username from V $ session where username is not null; Sid serial # Username -------------------------------------------------- 8 2041 sys 9 437 eygle Execution tracking: SQL> exec dbms_system.set_ev (9,437,100, 'eygl '); PL/SQL procedure successfully completed. End tracking: SQL> exec dbms_system.set_ev (9,437,100, 'eygl '); PL/SQL procedure successfully completed.

(C) obtain the trace file
The above generated trace file is located in the user_dump_dest directory, and the location and file name can be obtained through the following SQL query:

SQL> select 2 D. value | '/' | Lower (rtrim (I. instance, CHR (0) | '_ ora _' | P. spid | '. TRC 'trace_file_name 3 from 4 (select P. spid 5 from sys. V $ mystat M, sys. V $ session S, sys. V $ process P 6 where M. statistic # = 1 and S. SID = m. sid and P. ADDR = S. paddr) P, 7 (select T. instance from sys. V $ thread t, sys. V $ parameter V 8 where v. name = 'thread' and (v. value = 0 or T. thread # = to_number (v. value) I, 9 (select value from sys. V $ parameter where name = 'user _ dump_dest ') d 10/ trace_file_name partition /opt/ oracle/admin/hsjf/udump/hsjf_ora_1026.trc

(D) read the parameters set by the current session
When we set SQL _trace through alter session, this setting cannot be obtained through show parameter. We need to get it through dbms_system.read_ev:

SQL> set feedback off SQL> set serveroutput on SQL> declare 2 event_level number; 3 begin 4 For event_number in 10000 .. 10999 Loop 5 SYS. dbms_system.read_ev (event_number, event_level ); 6 if (event_level> 0) then 7 SYS. dbms_output.put_line ( 8 'event' | 9 to_char (event_number) | 10' is set at Level '| 11 to_char (event_level) 12 ); 13 end if; 14 end loop; 15 end; 16/ Event 10046 is set at level 1