How to enable SQL Trace in Oracle Tracing Tool

SQL TraceYesOracleProvides a powerful diagnostic tool for holding SQL search. SQL TRACE is a commonly used method in the diagnosis and handling of database problems.

SQL TRACE can be divided into the following steps:

1. Define the scope of goals that must be pursued and use the appropriate call to enable the desired search.

2. After a while, you can still pursue it. At this moment, there should be a file to pursue the harvest.

3. Find the document to be pursued, style it, and then read or analyze it.

This article makes it easy to explore the use of SQL trace and explains the use of SQL _TRACE through detailed cases.

Enabling SQL TRACE

SQL _TRACE: SQL _TRACE can be used as initialization parameters in the overall situation, and can also be used in informative sessions through call line measures.

1. Enable

Specify SQL _TRACE = true in the parameter file (pfile/spfile.

Enabling SQL _TRACE in the overall situation will lead to the pursuit of all actions, including the background process and all user processes. This idle time will lead to more serious functional problems, and therefore must be reused in the production environment.

Reminder:When SQL _TRACE is enabled in the overall situation, we can pursue all background operations, many general interpretations in the document, and track real-time changes in files, we can inquire about the Compact mediation between various processes.

2. Set at the current session level

Most of the time, we use SQL _TRACE to trace the current session history. Through the pursuit of the current process, we can perceive the current monopoly of the background database recursive action (this is especially useful when thinking about the new personality of the database), ponder over SQL statements, and perceive the background errors.

The following are the steps to enable and stop SQL _TRACE at the session level:

Enable the current session search:

 
 

  
  
SQL> alter session set SQL_TRACE=true;  
  
  
 
  
  
Session alteredbr.brandmats.cn. 
 
 

At this moment, the SQL monopoly will be pursued:

 
 

  
  
SQL> select count(*) from dba_users;  
  
  
 
  
  
COUNT(*)  
  
  
 
  
  
----------  
  
  
 
  
  
34 
 
 

Final pursuit:

 
 

  
  
SQL> alter session set SQL_TRACE=false;  
  
  
 
  
  
Session altered. 
 
 

3. Search for other users

In many cases, we have to pursue the history of other users, SC .scjszp.com, instead of the current users. This can end with the system package DBMS_SYSTEM.SET_ SQL _TRACE_IN_SESSION provided by Oracle.

The SET_ SQL _TRACE_IN_SESSION program must provide three parameters:

 
 

  
  
SQL> desc DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION;  
  
  
 
  
  
Parameter Type Mode Default?  
  
  
 
  
  
--------- ------- ---- --------  
  
  
 
  
  
SID NUMBER IN  
  
  
 
  
  
SERIAL# NUMBER IN  
  
  
 
  
  
SQL_TRACE BOOLEAN IN 
 
 

Through the v $ session, we can get messages such as sid and serial:

To get the course news, choose the course that must be pursued:

 
 

  
  
SQL> select sid,serial#,username from v$session  
  
  
 
  
  
2 where username is not null;  
  
  
 
  
  
SID SERIAL# USERNAME  
  
  
 
  
  
---------- ---------- ------------------------------  
  
  
 
  
  
8 2041 SYS  
  
  
 
  
  
9 437 EYGLE 
 
 

The settings follow:

 
 

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

We can look forward to the flash, pursue the task of executing sessions, and seize the SQL monopoly...

Static pursuit:

 
 

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

10046 event interpretation

10046 events are internal events provided by Oracle and deepen SQL _TRACE.

10046 the following four levels can be set 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 + Event tracking
• 12-Level 1 + Level 4 + Level 8

Similar to SQL _TRACE, 10046 events can be set at the overall situation or session level.

This article introduces SQL Trace, an Oracle database tracing tool. We hope this introduction will bring you some benefits!