Oracle 10046 event details

The 10046 event of Oracle can track the SQL statements executed by the application and obtain the number of resolutions, executions, CPU usage time, and other information. This is very useful for us to analyze and locate database performance issues.

10046event is the most important event in oracle system performance analysis. When this event is activated, it notifies the oracle kernel to trace the real-time information of the session and writes it to the corresponding trace file. These useful information mainly includes how SQL is parsed, how variables are bound, and the waiting events in sessions.

10046 events can be divided into different levels to track useful information of different levels. For these different levels, it should be noted that it is backward compatible, that is, the high-level trace information contains all information lower than this level.

10046event tracing levels include:

Level 1: tracks SQL statements, including parsing, execution, extraction, submission, and rollback.

Level 4: including detailed information about variables

Level 8: including waiting events

Level 12: Includes variable binding and wait events

Among them, level 1 is equivalent to opening SQL _trace

Prerequisites:
(Ensure that the session environment for the event meets the conditions)

1. Make sure that timed_statistics is TRUE. This parameter can be modified at the session level.

2. To ensure that the trace output can be complete, you need to adjust the trace file size limit for this session. Generally, this limit is removed, that is, max_dump_file_size is set to UNLIMITED, or set it to a large threshold value.

After the preceding conditions are met, You can enable 10046event to track the background of the session.

1. First obtain spid, sid, and serial #. machine is the name of the machine connected to oracle.
Select B. spid,. sid,. serial #,. machine from v $ session a, v $ process B where. paddr = B. addr and. sid = '000000'

Continue to see why SPID is found;

2. event initiation 10046
SQL> execute sys. dbms_system.set_ev (15,196,100 ,'');
PL/SQL procedure successfully completed.

Parameter description:
15: SID
196: SERIAL #

Note that you must log on with sysdba.

Or session-level tracking:

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

The session has been changed.

3. perform database operations in the Application
For example, query, insert, and delete operations with poor performance.

4. Close 10046 events
SQL> execute sys. dbms_system.set_ev (15,196,100 ,'');

PL/SQL procedure successfully completed.

Or close at the session level.

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

The session has been changed.

5. Obtain the directory of the generated trace file
SQL> select value from v $ parameter where name = 'user _ dump_dest ';

Go to the directory and you will see that a zftang_ora_596.trc file is generated. Here, 596 is the value of SPID.

1 2 Next Page