Use of SQL _trace

SQL _trace Overview:

SQL _trace is a means provided by the Oracle database for SQL tracking. In a sense, SQL _trace is the most powerful auxiliary diagnostic tool in Oracle.

SQL _trace can be globally enabled as an initialization parameter, or it can be enabled in a specific session through a command line.

SetTimed_statistics = trueAllows SQL trace and other dynamic performance views to collect time-related parameters

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. In this case, it usually causes serious performance problems, therefore, you must be careful when using it in a production environment.

Note: by enabling SQL _trace globally, we can track the activities of all background processes, abstract descriptions in many documents, and track real-time file changes, we can clearly see the close coordination between processes.

2. Set at the current session level

In most cases, we use SQL _trace to trace the current process. By tracking the current process, you can find the background database recursive activity of the current operation (this is especially effective when studying the new features of the database), study SQL Execution, and find 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 users. We can use the system package dbms_system. set_ SQL _trace_in_session provided by Oracle to complete the process.

Set_ SQL _trace_in_sessionProgramThree parameters are required:

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 the V $ session, we can obtain information such as Sid and serial.

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

Set Tracing:

SQL> exec dbms_system.set_ SQL _trace_in_session (9,437, true) PL/SQL procedure successfully completed .....

Wait for a moment, track the session to execute tasks, capture SQL operations ......

Stop a trail:

SQL> exec dbms_system.set_ SQL _trace_in_session (9,437, false) PL/SQL procedure successfully completed.

Get the tracking file address:

Select   Rtrim (C. Value, ' / ' ) | ' / ' | D. instance_name | ' _ Ora _ ' | Ltrim (To_char (A. spid )) | ' . TRC '
From V $ process a, V $ session B, V $ parameter C, V $ instance d
Where A. ADDR = B. paddr And B. audsid = Sys_context ( ' Userenv ' , ' Sessionid ' ) And C. Name =   ' User_dump_dest ' ;

Or:

Select   Rtrim (C. Value, ' / ' ) | ' / ' | D. instance_name | ' _ Ora _ ' | Ltrim (To_char (A. spid )) | ' . TRC '
From V $ process a, V $ session B, V $ parameter C, V $ instance d
Where A. ADDR = B. paddr And B. audsid = Sys_context ( ' Userenv ' , ' Sessionid ' ) And C. Name =   ' User_dump_dest ' ;

Generally, trace files are stored in the directory admin \ orcl \ udump.

Alter session set tracefile_identifier = 'partition'; // you can specify the trail file name.