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

In this issue, the editor will bring you what are several methods of tracking conversational execution sentences in oracle. The article is rich in content and analyzes and describes for you from a professional point of view. I hope you can get something after reading this article.

You can generate sql trace in the following ways:

1. Parameter setting: a very traditional method.

System level:

Specified in the parameter file: sql_trace=true

Or

SQL > alter system set sql_trace=true

Note: enabling sql_trace at the system level produces a large number of trace files, which can easily run out of disk space, so the session level is generally set and closed in a timely manner.

Session level:

SQL > alter session set sql_trace=true

SQL > execute sql

SQL > alter session set sql_trace=false

When tracing is enabled, the trace file is saved under user_dump_dest

You can use the following query to find the generated trace file

SQL > select

2 d.value | |'/'| | lower (rtrim (i.instance)

3 chr (0)) | |'_ ora_' | | p.spid | | '.trc' trace_file_name

4 from (select p.spid

5 from v$mystat m

6 v$session spark vested process p

7 where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p

8 (select t.instance from v$thread trecast vain parameter v

9 where v.name = 'thread' and

10 (v.value = 0 or t.thread# = to_number (v.value) I

11 (select value from v$parameter

12 where name = 'user_dump_dest') d

13 /

TRACE_FILE_NAME

/ oracle/admin/RLZY/udump/rlzy_ora_721532.trc

You can also specify an identifier for the trace file to be generated to make it easier for you to find the trace file

SQL > alter session set tracefile_identifier='jingyong'

2. Use 10046 events:

10046 event level:

Lv0-disable sql_trace, equivalent to sql_trace=false

Lv1-enables standard sql_trace functionality, equivalent to sql_trace=true

Lv4-Level 1 + binding variable value (bind values)

Lv8-Level 1 + wait event tracking (waits)

Lv12-Level 1 + Level 4 + Level 8

Global settings:

Specified in the parameter file: event= "10046 trace name context forever,level 12"

Or

SQL > alter system set events' 10046 trace name context forever, level 12'

SQL > alter system set events' 10046 trace name context off'

Note: enabling sql_trace at the system level produces a large number of trace files, which can easily run out of disk space, so the session level is generally set and closed in a timely manner.

Current session settings:

SQL > alter session set events' 10046 trace name context forever, level 12'

SQL > execute sql

SQL > alter session set events' 10046 trace name context off'

3. Dbms_session package: only the current session can be tracked, no session can be specified.

Track the current session:

SQL > exec dbms_session.set_sql_trace (true)

SQL > execute sql

SQL > exec dbms_session.set_sql_trace (false)

Dbms_session.set_sql_trace is equivalent to alter session set sql_trace, and you can clearly see from the generated trace file

Alter session set sql_trace statement.

Using the dbms_session.session_trace_enable procedure, you can see not only the waiting event information but also the binding variable information

Equivalent to alter session set events' 10046 trace name context forever, level 12 statements; statements can be confirmed from the generated trace file.

SQL > exec dbms_session.session_trace_enable (waits= > true,binds= > true)

SQL > execute sql

SQL > exec dbms_session.session_trace_enable ()

4. Dbms_support package: this method should not be used, unofficially supported.

This package is not installed by default. You can manually execute the $ORACLE_HOME/rdbms/admin/bmssupp.sql script to create the package.

Track the current session:

SQL > exec dbms_support.start_trace

SQL > execute sql

SQL > exec dbms_support.stop_trace

Track other sessions: wait for events + bind variables, which is equivalent to the 10046 event of level 12.

SQL > select sid,serial#,username from v$session where...

SQL > exec dbms_support.start_trace_in_session (sid= > sid,serial= > serial#,waits= > true,binds= > true)

SQL > exec dbms_support.stop_trace_in_session (sid= > sid,serial= > serial#)

5. Dbms_ system package:

Track other sessions:

Set the 10046 event using dbms_system.set_ev

SQL > select sid,serial#,username from v$session where...

SQL > exec dbms_system.set_ev (sid,serial#,10046,12,'')

SQL > exec dbms_system.set_ev (sid,serial#,10046,0,'')

However, it has been tested that the trace with a level of 8 in 10g does not produce wait event information in the trace file.

6. Dbms_monitor package: provided by 10g, it is very powerful. It can be tracked at module level, action level, client level, database level, and session level. Official support of oracle.

Track the current session:

SQL > exec dbms_monitor.session_trace_enable

SQL > execute sql

SQL > exec dbms_monitor.session_trace_disable

Track other sessions:

SQL > exec dbms_monitor.session_trace_enable (session_id= > sid,serial_num= > serial#,waits= > true,binds= > true)

SQL > exec dbms_monitor.session_trace_disable (session_id= > sid,serial_num= > serial#)

7 、 oradebug

This is a tool for sqlplus and needs to provide OSPID or oracle PID.

Track the current session:

SQL > oradebug setmypid

Statement processed.

SQL > oradebug unlimit

Statement processed.

SQL > oradebug event 10046 trace name context forever,level 12

Statement processed.

SQL > execute sql

SQL > oradebug tracefile_name

SQL > oradebug event 10046 trace name context off

Statement processed.

Track other sessions:

SQL > select spid,pid2 from v$process

2 where addr in (select paddr from v$session where sid= (select distinct sid from v$mystat))

SPID PID

--

1457 313

SQL > oradebug setospid 1457

Statement processed.

Or

SQL > oradebug setorapid 313

Statement processed.

SQL > oradebug unlimit

Statement processed.

SQL > oradebug event 10046 trace name context forever,level 12

Statement processed.

SQL > oradebug tracefile_name

SQL > oradebug event 10046 trace name context off

Statement processed.

These are the ways to track conversation execution statements in oracle shared by Xiaobian. If you happen to have similar doubts, please refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.