How to use 10046 events to track SQL in database

This article mainly introduces how to use 10046 event tracking SQL in the database, the article is very detailed, has a certain reference value, interested friends must read it!

10046 event

Overview

This is a special event that can be used to initiate activities of the sql_trace class

The advantage of using 10046 is that you can see the details of database activity in the trace file, which also depends on the level of the event.

Level

1: standard sql_trace

4:level 1 plus binding variable [bind=true]

8:levle 1 plus wait. Useful when locating waiting events [wait=true]

12: commonly used level, including level 1, bind variables, wait, default stat line. Equivalent to (4-8)

16: stat line is generated for each execution. 11g is added. [plan_stat=all_executions]

32: no execution statistics are generated. 11g is added. [plan_stat=never]

64:Adaptive dump of STAT lines. This dumps the STAT information if a SQL took more than about 1 minute thereby giving information for the more expensive SQLs and for different executions of such SQLs . 11gR2 has been added. [plan_stat=adaptive]

Use 10046 events

Session level tracking

-View the location of trace

SQL > show parameter user_dump_dest

NAME TYPE VALUE

-

User_dump_dest string / u01/app/oracle/diag/rdbms/zld

B/zldb/trace

-tracefile Flag

SQL > alter session set tracefile_identifier='liuzhilong'

Session altered.

-Open 10046

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

Session altered.

SQL > select * from scott.emp where rownum

Exit sqlplus (session level) or close the event using the following statement

Alter session set events' 10046 trace name context off'

[root@lzl Desktop] # cd / u01/app/oracle/diag/rdbms/zldb/zldb/trace/

[root@lzl trace] # ls * liu*

Zldb_ora_2990_liuzhilong.trc zldb_ora_2990_liuzhilong.trm

Use the oradebug tool

SQL > oradebug setmypid

Statement processed.

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

Statement processed.

SQL > select ename from scott.emp where rownum oradebug tracefile_name

/ u01/app/oracle/diag/rdbms/zldb/zldb/trace/zldb_ora_2990.trc

SQL >

The above is all the contents of the article "how to use 10046 events to track SQL in the database". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!