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

10046 is an internal event (event) of Oracle. By setting this event, you can get detailed trace information such as system parsing, calling, waiting, binding variables and so on, which plays a very important role in analyzing the performance of the system.

Different levels of trace information can be obtained by setting different levels of the 10046 event. The corresponding functions of different levels are listed below:

Level binary production uses 0000 with no output 10001 output * *, APPNAME (application name), PARSING IN CURSOR,PARSE ERROR (SQL parsing), EXEC (execution), FETCH (getting data), UNMAP,SORT UNMAP (sorting, temporary period), ERROR,STAT (execution plan), XCTEND (transaction), etc. Line 20011 is exactly the same as level 1 40101 includes level 1 output, plus BIND line (binding variable information) 81001 includes level 1 output Plus the WAIT line (waiting for event information) 121101 outputs all the information for levels 1, 4, and 8

10046 trace of level 1 is considered a normal SQL Trace, while levels 4, 8, and 12 are called Extended SQL Trace,Extended SQL Trace, which contain the most useful WAIT information, so they are also used the most in practice.

Parameters related to SQL Trace

Before opening the 10046-time SQL Trace, set the following parameters.

The parameter timed_statistics determines whether time-related statistics are collected. If this parameter is FALSE, then the result of SQL Trace is of little use. By default, this parameter is set to TRUE. The size of the max_dump_file_sizedump file, that is, deciding whether to limit the size of the SQL Trace file, SQL Trace on a busy system may generate a lot of information, so it is best to set this parameter to unlimited at the session level. Tracefile_identifier sets the identification string to the Trace file, which is a very useful parameter. Setting an easy-to-read string makes it faster to find the Trace file.

To modify the above parameters in the current session, simply use the following command:

The 12 3ALTER SESSION SET timed_statistics= true ALTER SESSION SET max_dump_file_size=unlimited ALTER SESSION SET tracefile_identifier='my_trace_session10046 Trace startup method uses the sql_trace parameter

Sql_trace should be a simple and quick way to open Trace, but only Trace with level 1 can be enabled through sql_trace, but not other more advanced Trace.

Session level: alter session set events' 10046 trace name context forever,level X'

System level: alter system set events' 10046 trace name context forever,level X'

Set for a process other than this session, if you know its SPID operating system process number

Oradebug setospid SPID

Oradebug event 10046 trace name context forever, level X

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.