Shulou(Shulou.com)06/01 Report--

1. Preface:

SQL_TRACE is a powerful assistant diagnostic tool for SQL tracking provided by Oracle. SQL_TRACE is a very common method in daily database problem diagnosis and resolution.

This paper briefly discusses the use of SQL_TRACE and illustrates the use of sql_trace through specific cases.

II. Basic introduction

(a) SQL_TRACE description

SQL_TRACE can be enabled globally as an initialization parameter or on a specific session from the command line.

1. Enable globally

Specify in the parameters file (pfile/spfile):

Sql_trace = true

Enabling SQL_TRACE globally will cause the activities of all processes to be tracked, including background processes and all user processes, which will usually lead to serious performance problems, so be cautious in the production environment. This parameter is a dynamic parameter after 10g and can be adjusted at any time, which is very effective in some diagnostics.

Tip: by enabling sql_trace globally, we can track the activities of all background processes, and many abstract instructions in the document. By tracking the real-time changes of the file, we can clearly see the close coordination between the various 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, you can find the background database recursive activity of the current operation (this is especially effective when studying new database features)

Study the implementation of SQL, find background errors, etc.

Enabling and stopping sql_trace at the session level is as follows:

Enable tracing for the current session:

SQL > alter session set sql_trace=true

Session altered.

End tracking:

SQL > alter session set sql_trace=false

Session altered.

3. Track other user processes

In many cases, we need to track the progress of other users rather than the current user, which can be done through the system package DBMS_SYSTEM. SET_SQL_TRACE_IN_SESSION

To finish it.

After the SET_SQL_TRACE_IN_SESSION program, three parameters are 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 get sid, serial# and other information:

Get the process information and select the process to be tracked:

SQL > select sid,serial#,username from v$session where username is not null

SID SERIAL# USERNAME

210 11 SAPSR3

Set up tracking:

SQL > exec dbms_system.set_sql_trace_in_session (210pm 11je true)

PL/SQL procedure successfully completed.

....

You can wait for a moment, track session execution tasks, and capture sql operations.

....

Stop tracking:

SQL > exec dbms_system.set_sql_trace_in_session (210pm 11pm false)

PL/SQL procedure successfully completed.

(B) 10046 event description

The 10046 event is an internal event provided by Oracle and an enhancement to SQL_TRACE.

The 10046 event can be set at the following four levels:

1-enables the standard SQL_TRACE function, equivalent to sql_trace

4-Level 1 plus binding value (bind values)

8-Level 1 + wait event tracking

12-Level 1 + Level 4 + Level 8

Similar sql_trace,10046 events can be set globally or at the session level.

1. Set globally

Add to the parameter file:

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

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

2. Set the current session

To modify through 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 session for other users

Through the DBMS_SYSTEM.SET_EV system package to achieve:

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 parameters SI and SE come from the v$session view:

Query to get the session information to be tracked:

SQL > select sid,serial#,username from v$session where username is not null

SID SERIAL# USERNAME

146 75 SYS

210 11 SAPSR3

Perform tracking:

SQL > exec dbms_system.set_ev (210, 11, 100, 46, 8, and SAPSR3')

PL/SQL procedure successfully completed.

End tracking:

SQL > exec dbms_system.set_ev (210, 11, 100, 46, 8, and SAPSR3')

PL/SQL procedure successfully completed.

(C) obtain tracking files

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

SQL > select d.value | |'/'| | lower (rtrim (i.instance, chr (0) | |'_ ora_' | | p.spid | | '.trc' trace_file_name from (select p.spid from sys.v$mystat mjournal sys.vencoding session sdirection sys.vencoding process p where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p

(select t.instance from sys.v$thread tjorie sys. Vault parameter v

Where v.name = 'thread' and (v.value = 0 or t.thread# = to_number (v.value) I, (select value from sys.v$parameter where name =' user_dump_dest') d

TRACE_FILE_NAME

/ oracle/ECD/saptrace/diag/rdbms/ecd/PIP/trace/pip_ora_11403414.trc

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.