How to use Sql_trace for database diagnostics in Oracle

Sql_trace is an Oracle-provided tool for SQL tracing and is a powerful aid diagnostic tool. Sql_trace is a very common method in the daily diagnosis and resolution of database problems. In this paper, the use of Sql_trace is discussed briefly, and the use of Sql_trace is explained through specific cases.

First, the basic introduction

(a) Sql_trace description

Sql_trace can be enabled globally as an initialization parameter, or it can be enabled in a specific session through a command-line approach. 1. specified in the parameter file (pfile/spfile) in global enabled:

Sql_trace =true

Enabling Sql_trace globally causes the activities of all processes to be tracked, including background processes and all user processes, which often result in more serious performance problems, so use caution in a production environment, which is a dynamic parameter after 10g and can be adjusted at any time, and is very effective in some diagnostics. Tip: By enabling sql_trace globally, we can track the activities of all background processes, many abstract descriptions in the documentation, and we can clearly see the close coordination between the processes by tracking the real-time changes in the file.

2. At the current session-level setting most of the time we use Sql_trace to track the current process. By tracking the current process, you can discover the backend database recursive activity of the current operation (especially effective when studying the new features of the database), study SQL execution, Discover background errors, and so on. The Sql_trace mode is enabled and stopped at the session level as follows:

Enable tracing for current session:
sql> alter session set Sql_trace=true;

Session altered.

The SQL operation at this point will be tracked:
sql> Select COUNT (*) from dba_users;

  COUNT (*)
----------
End trace:
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, not the current user, which can be dbms_system through the system package provided by Oracle. Set_sql_trace_in_session to complete

This column more highlights: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

Set_sql_trace_in_session program to provide three parameters:

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 information such as SID, Serial#, etc.:

To obtain process information, select the process that needs to be tracked:

sql> select Sid,serial#,username from V$session
  2  where username are not null;

       SID    serial#  USERNAME
--------------------------------------------------
         8       2041  SYS
         9        437  eygle

settings tracking:
sql> exec dbms_system.set_sql_trace_in_session (9,437,true)

Pl/sql procedure successfully completed.

....
Can wait for a moment, track session execution task, capture SQL Operation
... ....

Stop tracing:
sql> exec dbms_system.set_sql_trace_in_session (9,437,false)

Pl/sql procedure successfully Completed.

(b) 10046 Event Description 10046 event is an internal event provided by Oracle and is an enhancement to sql_trace. The 10046 event can be set to the following four levels: 1-enable the standard Sql_trace feature, equivalent to Sql_trace 4-level 1 plus bound value (bind values) 8-level 1 + Wait event tracking 12-level 1 + Le Vel 4 + level 8 similar to sql_trace,10046 events can be set globally or at the session stage. 1. Increase in the global setting in the parameter file:

event= "10046 Trace name context forever,level 12"

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

2. The current session setting is modified by alter session and requires the system permission of ALTER session:

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. The other user session settings pass Dbms_system. Set_ev System Package to implement:

Sql> desc dbms_system ...
PROCEDURE Set_ev
 Argument Name                  Type                    in/out Default?
 -------------------------------------------------------------------
 SI                             binary_integer in
 SE                             Binary_integer          in
 EV                             binary_integer          into
 LE                             binary_integer          in
 NM                             VARCHAR2                In

...

.. Where the parameters si, se from the v$session view: The query gets the session information that needs 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,10046,8, ' eygle ');

Pl/sql procedure successfully completed.

End tracking: sql> exec Dbms_system.set_ev (9,437,10046,0, ' eygle ');

Pl/sql procedure successfully completed.

(c) Get trace files The trace files generated above are located in the User_dump_dest directory, and the location and file name can be obtained from 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  /

Trace_file_name--------------------------------------------------------------------------------/opt/oracle/ Admin/hsjf/udump/hsjf_ora_1026.trc

(d) to read the parameters of the current session settings when we set the Sql_trace through ALTER session, this setting is not available through show parameter, and we need to get by Dbms_system.read_ev:

Sql> Set Feedback off

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) | | || One to_char (event_level) 12); The End If; End Loop; End; 16/event 10046 is set at level 1

This article is from the "Technical Knowledge exchange" blog, please be sure to keep this source http://xiahouyao123.blog.51cto.com/646312/825989