Enable Oracle 10046 debugging events

Oracle 10046 is an Oracle internal event. The most common method is to set SQL _trace (alter Session set SQL _trace = true) at the session level, that is, to enable the 10046 debugging event of level 1. After a 10046 event is set, Oracle generates a dump file. Through further analysis of the dump file, detailed trace information such as parsing, calling, waiting, and binding variables of the Oracle internal execution system can be obtained, which plays an important role in analyzing the system performance.

I. 10046 event-related parameters
Some parameters need to be set for this event to control the output of the dump file:
TIMED_STATISTICS
Used to control timing information. It can be set to true or false. When set to true, the timing information will be added to the trace file.

MAX_DUMP_FILE_SIZE
Used to control the maximum size of a trace file. When using the 10046 event, we recommend that you set this parameter to unlimited.

USER_DUMP_DEST
Used to set the directory to which the trace file is written.

STATISTICS_LEVEL
Used to control the collection of statistical information. This parameter has three options: baisc, typical, and all.
Basic: only collect the most basic information that meets the trace requirements, such as Timed statistics, Object level statistics, and some advisory will be ignored.
Typical: This is the default value. This configuration will add some additional statistics on the basis of basic, such as the Statistical Information of the time consumed by the operating system, the statistical information of the Execution Plan will be collected
All: when it is set to all, all information related to this session will be collected.

TRACEFILE_IDENTIFIER
It is used to set the string used to identify the Trace file, so that the generated Trace file can be found more quickly.

The preceding parameters can be modified based on the system level and session level.
Alter session/system set timed_statistics = true
Alter session/system set max_dump_file_size = unlimited
Alter session set tracefile_identifier = 'trace _ SQL _example '--> only session level

Dynamically set trace parameters for a specific session, with the help of the DBMS_SYSTEM package
Sys. DBMS_SYSTEM.set_bool_param_in_session (& sid
, & Serial
, 'Timed _ statistics'
, TRUE );
Sys. DBMS_SYSTEM.set_int_param_in_session (& sid
, & Serial
, 'Max _ dump_file_size'
, 2147483647 );

Ii. 10046 levels of debugging events
10046 debugging events can be divided into multiple levels, and different levels output different trace information.
Level Function
0. Disable debugging events.
1. the debugging event is active. For each processed database call, the output SQL statement, APPNAME (Application name), parsing in cursor, and parse error (SQL PARSING)
, EXEC (execution), FETCH (obtain data), UNMAP, sort unmap (sorting, temporary segment), ERROR, STAT (Execution Plan), XCTEND (transaction) and other rows.
4 include level 1 Output and BIND line (BIND Variable information)
8 includes Level 1 Output and WAIT line (WAIT for event information ). For each wait in the processing process, provide the following information: The name of the wait time, duration, and some additional
Parameter, indicating the waiting resources.
12 output all information of level 4 and Level 8 

1 2 3 Next Page