Procedure for tracking events in Oracle 10046

1. Enable event tracking 10046
Alter session set events '2017 trace name context forever, level 12 ';
 
2. Execute an SQL statement at Will (we will find the execution of this SQL statement in the trace file)
Select * from dba_users where username = 'hr ';
 
3. Stop 10046 event tracking
Alter session set events '10046 trace name context off ';

4. Locate the generated tracking File

Select distinct (m. sid), p. pid, p. tracefile from v $ mystat m, v $ session s, v $ process p where m. sid = s. sid and s. paddr = p. addr;
 

5. Use the tkprof tool to standardize the file format (for ease of viewing and analysis)

Tkprof.exe E: \ APP \ Oracle \ diag \ rdbms \ orcl \ trace \ orcl_ora_3304.trc
Output00001.txt
Nodepad 1.txt

SQL ID: 7bx241ats4942 Plan Hash: 4242951753

Select *
From
Dba_users where username = 'hr'

Call count cpu elapsed disk query current rows
-----------------------------------------------------------------------
Parse 1 0.03 0.01 0 0 1 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.01 1 16 0 1
-----------------------------------------------------------------------
Total 3 0.03 0.02 1 16 1 1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------------------------------------------------------------------------------
1 1 1 merge join cartesian (cr = 16 pr = 1 pw = 0 time = 13157 us cost = 12 size = 228 card = 1)
1 1 1 hash join outer (cr = 14 pr = 1 pw = 0 time = 13068 us cost = 11 size = 215 card = 1)
1 1 1 hash join (cr = 12 pr = 1 pw = 0 time = 12782 us cost = 9 size = 177 card = 1)
1 1 1 nested loops (cr = 10 pr = 1 pw = 0 time = 12407 us)
17 17 17 nested loops (cr = 9 pr = 1 pw = 0 time = 12355 us cost = 6 size = 167 card = 1)
1 1 1 nested loops (cr = 8 pr = 0 pw = 0 time = 153 us cost = 5 size = 154 card = 1)
1 1 1 nested loops (cr = 6 pr = 0 pw = 0 time = 135 us cost = 4 size = 142 card = 1)
1 1 1 nested loops (cr = 4 pr = 0 pw = 0 time = 115 us cost = 3 size = 130 card = 1)
1 1 1 table access by index rowid user $ (cr = 2 pr = 0 pw = 0 time = 39 us cost = 1 size = 111 card = 1)
1 1 1 index unique scan I _USER1 (cr = 1 pr = 0 pw = 0 time = 23 us cost = 0 size = 0 card = 1) (object id 46)
1 1 1 table access full USER_ASTATUS_MAP (cr = 2 pr = 0 pw = 0 time = 74 us cost = 2 size = 19 card = 1)
1 1 1 table access cluster ts $ (cr = 2 pr = 0 pw = 0 time = 17 us cost = 1 size = 12 card = 1)
1 1 1 index unique scan I _TS # (cr = 1 pr = 0 pw = 0 time = 4 us cost = 0 size = 0 card = 1) (object id 7)
1 1 1 table access cluster ts $ (cr = 2 pr = 0 pw = 0 time = 14 us cost = 1 size = 12 card = 1)
1 1 1 index unique scan I _TS # (cr = 1 pr = 0 pw = 0 time = 2 us cost = 0 size = 0 card = 1) (object id 7)
17 17 17 index range scan I _PROFILE (cr = 1 pr = 1 pw = 0 time = 12206 us cost = 0 size = 0 card = 17) (object id 285)
1 1 1 table access by index rowid profile $ (cr = 1 pr = 0 pw = 0 time = 43 us cost = 1 size = 13 card = 1)
2 2 2 table access full profname $ (cr = 2 pr = 0 pw = 0 time = 46 us cost = 2 size = 10 card = 1)
0 0 0 table access full RESOURCE_GROUP_MAPPING $ (cr = 2 pr = 0 pw = 0 time = 46 us cost = 2 size = 38 card = 1)
1 1 1 buffer sort (cr = 2 pr = 0 pw = 0 time = 80 us cost = 10 size = 13 card = 1)
1 1 1 table access by index rowid profile $ (cr = 2 pr = 0 pw = 0 time = 42 us cost = 1 size = 13 card = 1)
17 17 17 index range scan I _PROFILE (cr = 1 pr = 0 pw = 0 time = 35 us cost = 0 size = 0 card = 17) (object id 285)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ----------------------
SQL * Net message to client 1 0.00 0.00
Disk file operations I/O 1 0.00 0.00
Db file sequential read 1 0.01 0.01
SQL * Net message from client 1 22.50 22.50
 
The preceding information is the execution information of the standard SQL statement, so that DBA can diagnose the SQL statement.

Oracle 11g new SQL Trace 10046 Method

Brief Introduction to Oracle 10046 event tracking

Oracle SQL Trace and 10046 events

Enable Oracle 10046 debugging events

Oracle SQL Trace and 10046 event tracking

Oracle 10046 event details

1 2 3 Next Page