Oracle events
Oracle SQL Trace and TKPROF
| Event # Level Description 8186 – ALTER SESSION SET EVENTS ‘8186 trace name context OFF’ – VERSIONS BETWEEN query 10005 – Trace latch operations for debugging 10013 1 Monitor Transaction Recovery 10015 1 Dump Undo Segment Headers 10015 3 Identify corrupt undo segments 10021 – KST event to trace control file header writes and reads 10022 – Trace ktsgsp 10027 – Specify Deadlock Trace Information to be Dumped 10028 – Dump trace information during lock / resource latch cleanup 10032 4 Dump Sort Statistics 10031 – Sort Debug Events 10032 10 Dump Sort Statistics 10033 4 Dump Sort Large Run Statistics (blocks dumped to disk and read back) – poss. no levels. 10033 10 Dump Sort Intermediate Run Statistics 10038 – Dump row source tree (QBADRV) 10041 – Dump undo records skipped 10045 1 Trace Free List Management Operations 10046 0 No statistics generated 10046 1 Standard trace output including parsing, executes and fetches plus more 10046 2 Same as Level 1 10046 4 Level 1 + Bind Variables 10046 8 Level 1 + Waits 10046 12 Level 1 + Bind Variables & Waits 10047 – Trace Session Switching 10050 – Sniper Trace 10053 1 CBO ENABLE Optimizer Trace: Dump Optimizer Statistics and Computations 10053 2 CBO ENABLE Optimizer Trace: Dump Optimizer Computations only 10054 – CBO Enable optimizer trace for recursive statement (RPI) 10056 – Dump analyze stats (kdg) 10060 1 Traces a SQL statement ensuring rows column will be populated. See Metalink Note 246821.1 10065 1 Restrict Library Cache Output for State Object Dumps – Library Objects Only 10065 2 Restrict Library Cache Output for State Object Dumps + library object lock details 10065 3 Restrict Library Cache Output for State Object Dumps + library object handle and library object 10072 – Local tempspace tracing – recovery layer 10073 – Latch Cleanup Tracing 10074 – Default trace function mask for kst 10079 2 Trace data sent/received via SQLNet: Dump SQLNet Statistics 10080 – Dump a block on a segment list which cannot be exchanged 10081 1 Dump High Water Mark Changes 10083 – Trace IMCDT in LIMITED mode 10095 – Dump debugger commands to the trace file 10104 10 Dump Hash Join Statistics to the trace file 10128 1 Dump Partition Pruning Information 10128 2 Dump partition iterators 10128 4 Dump optimizer decisions about partition-wise joins 10128 8 Dump ROWID range scan pruning information 10132 12 Dumps SQL statements along with the actual, after compilation, execution plan 10134 – Trace SQL Memory Management for session 10146 – Enable Oracle TRACE collection 10148 – Quarantine trace event 10166 – Trace long operation statistics updates 10200 1 Dump Consistent Read Buffer Status 10201 1 Dump Consistent Read Undo Application 10210 10 Table Integrity Check 10211 10 Index Integrity Check 10216 – Dump the control file header 10218 – Dump the UBA of applied undo 10220 1 Dump Changes to Undo Header 10221 7 Dump Undo Changes 10224 1 Dump Index Block Splits / Deletes Trace 10225 1 Dump Changes to Dictionary Managed Extents 10226 – Trace CR applications of undo for data operations 10228 – Trace application of redo by kcocbk 10231 10 Causes full table scans to skip corrupt blocks, rather than fail with an ORA-01578 error zzyzx ALTER SESSION SET EVENTS=’10231 trace name context forever’; 10232 10 Dump corrupted blocks symbolically when kcbgotten to the process trace file 10233 10 Traces memory usage statistics 10235 1 Check memory manager internal structures (use with ORA-600/ORA-7445 errors) 10236 – Dump redo on object no. or block type mismatch errors 1410/8103 10241 1 Dump Remote SQL Execution Tracing/Validation 10246 1 Trace PMON Process actions to the trace file 10247 – Turn on scgcmn tracing (VMS ONLY) 10248 10 Trace Dispatcher Processes 10249 10 Trace Shared Server (MTS) Processes (multi-stated servers) 10250 – Trace all allocate and free calls to the topmost SGA heap 10254 – Trace cross-instance calls 10257 – Trace shared server load balancing 10266 – Trace OSD stack usage 10270 10 Debug Shared Cursors 10289 1 Dump a database block in hexadecimal rather than formatted 10290 – Internal sequence tracing event 10292 – Hang analysis trace event 10293 – Trace log switch media recovery checkpoints 10294 – Trace ksrpc 10297 – Trace shared I/O pool 10298 – Trace ksfd I/O 10299 1 Trace prefetch tracking decisions made by CKPT 10300 – Distributed transaction tracing 10302 – Trace create or drop internal trigger 10303 – Trace loading of library cache for internal triggers 10304 – Trace replication trigger 10305 – Trace updatable materialized view trigger 10306 – Trace materialized view log trigger 10307 – Trace RepCat execution 10310 – Trace synchronous change table trigger 10318 – Trace extensible transaction header movements 10319 – Trace PGA statistics maintenance 10320 – Enable data layer (kdtgrs) tracing of space management calls 10325 – Trace control file record section expand and shrink operations 10333 – Dump MTTR statistics each time it is updated 10335 – Trace database open status 10347 – Dump active checkpoint entries and checkpoint buffers 10357 1 Debug Direct Path 10366 – kgnfs tracing 10367 – kgodm tracing 10384 – parallel dataflow scheduler tracing 10390 0x0001 Dump Parallel Execution Slave Statistics – Slave-side execution messages 10390 0x0002 Dump Parallel Execution Slave Statistics – Coordinator-side execution messages 10390 0x0004 Dump Parallel Execution Slave Statistics – Slave context state changes 10390 0x0008 Dump Parallel Execution Slave Statistics – Slave ROWID range bind variables and xty 10390 0x0010 Dump Parallel Execution Slave Statistics – Slave fetched rows as enqueued to TQ 10390 0x0020 Dump Parallel Execution Slave Statistics – Coordinator wait reply handling 10390 0x0040 Dump Parallel Execution Slave Statistics – Coordinator wait message buffering 10390 0x0080 Dump Parallel Execution Slave Statistics – Slave dump timing 10390 0x0100 Dump Parallel Execution Slave Statistics – Coordinator dump timing 10390 0x0200 Dump Parallel Execution Slave Statistics – Slave dump allocation file number 10390 0x0400 Dump Parallel Execution Slave Statistics – Terse format for debug dumps 10390 0x0800 Dump Parallel Execution Slave Statistics – Trace CRI random sampling 10390 0x01000 Dump Parallel Execution Slave Statistics – Trace signals 10390 0x02000 Dump Parallel Execution Slave Statistics – Trace parallel execution granule operations 10390 0x04000 Dump Parallel Execution Slave Statistics – Force compilation by slave 0 10391 0x0001 Dump Parallel Execution Granule Allocation – Summary of each object scanned in parallel 10391 0x0002 Dump Parallel Execution Granule Allocation – Full dump of each object except extent map 10391 0x0004 Dump Parallel Execution Granule Allocation – Full dump of each object including extent map 10391 0x0010 Dump Parallel Execution Granule Allocation – Summary of each granule generators 10391 0x0020 Dump Parallel Execution Granule Allocation – Granule generators except granule instances 10391 0x0040 Dump Parallel Execution Granule Allocation – Granule generators including granule instances 10391 0x0080 Dump Parallel Execution Granule Allocation – System information 10391 0x0100 Dump Parallel Execution Granule Allocation – Reference object for the query 10391 0x0200 Dump Parallel Execution Granule Allocation – kxfralo timing 10391 0x0400 Dump Parallel Execution Granule Allocation – Affinity module 10391 0x0800 Dump Parallel Execution Granule Allocation – Granule allocation during query execution 10391 0x1000 Dump Parallel Execution Granule Allocation – Object flush 10393 1 Dump Parallel Execution Statistics 10399 – Trace buffer allocation 10401 1 KSXP tracing: for RAC kernel 10402 1 SKGXP tracing: for RAC kernel minimal tracing 10402 127 SKGXP tracing: for RAC kernel maximum tracing 10412 – Dump the call stack if an error is signaled 10420 – Trace KSO OS-process operations 10421 – Enable dump from ksbwco if there is no reply 10423 – dump the call stack if the specified error is cleared 10425 – Enable global enqueue operations event trace 10426 – Enable ges/gcs reconfiguration event trace 10427 – Enable global enqueue service traffic controller event trace 10428 – Enable tracing of global enqueue service cached resource 10429 – Enable tracing of global enqueue service IPC calls 10430 – Enable ges/gcs dynamic remastering event trace 10432 – Enable tracing of global cache service fusion calls 10434 – Enable tracing of global enqueue service multiple LMS 10435 – Enable tracing of global enqueue service deadlock detetction 10437 – Enable trace of global enqueue service S optimized resources 10439 – Enable tracing of global cache service fusion calls – part 2 10440 – Enable global enqueue service inquire resource modes trace 10441 – Enable diagnosibility daemon (DIAG) trace 10442 – Enable trace of kst for ORA-01555 diagnostics 10445 – Enable tracing of LMS priority management 10448 – Dump trace for OLTP partial compression features diagnostics 10449 – Enable trace of kst for undo manageability features diagnostics 10453 – Dump compression statistics to trace file 10472 – Dump reading log buffer hit ratio histogram to LGWR trace file 10473 – Enable BRR tracing 10474 – Controlfile time tracing 10478 – DBW0 file identification trace event 10484 – Enable tracing for online data file move operation 10487 – Dump redo memory protection information 10488 – Dump block headers read for media recovery 10490 – Trace OSM misc. events 10491 – Trace OSM messaging (KFN) events 10492 – Trace OSM metadata events 10494 – Trace OSM metadata events 10495 – Trace OSM metadata events 10497 – Trace OSM metadata events 10499 – Trace OSM metadata events 10500 1 Trace SMON Process 10505 – CBO enable dynamic sampling dump to table 10507 – Trace bind equivalence logic 10254 – CMON connection pool trace event 10526 – Enable lightweight thread tracing 10527 – Enable SGA allocation tracing 10528 – Enable ksmg tracing 10529 – Enables SGA range protection tracing 10542 – Enable tracing for block compare 10556 – Enable tracing for multi instance Redo Apply 10569 – Trace datafile header writes 10601 32 Forces the database to consider SQL*Plus a foreign SQL application 10604 – Trace parallel create index 10606 – Trace parallel create index 10607 – Trace index rowid partition scan 10608 10 Trace Bitmap Index Creation 10609 – Trace for array index insertion 10610 – Trace create index pseudo optimizer 10622 – Test or trace online index build or rebuild 10625 – Turn off redo log dump for the index when OERI 12700 10627 – Dump the content of the index leaf block 10639 – Dump library cache during reparse loops 10690 – Set shadow process core file dump type (Unix only) 10692 – Trace preserving buffer across standby role transition 10701 – Dump direct loader index keys 10704 1 Trace Enqueues 10706 1 Trace Global Enqueue Manipulation 10708 10 Print out trace information from the RAC buffer cache 10710 1 Trace Bitmap Index Access 10711 1 Trace Bitmap Index Merge Operation 10712 1 Trace Bitmap Index OR Operation 10713 1 Trace Bitmap Index AND Operation 10714 1 Trace Bitmap Index MINUS Operation 10715 1 Trace Bitmap Index Conversion to ROWIDs 10716 1 Trace Bitmap Index Compress / Decompress 10717 1 Trace Bitmap Index Compaction for Index Creation 10719 1 Trace Bitmap Index DML 10720 – Trace database scheduling 10722 – Trace server level database scheduling 10724 – Trace cross-instance broadcast 10730 1 Trace Fine Grained Access Policy Predicates 10731 1 Dump SQL for CURSOR expressions 10731 2 Dump SQL for CURSOR expressions 10741 – Trace missing BRR generation 10742 – Dump process state on flush buffer_cache 10743 – Define the misbehaved vt dump thresholds 10744 – Disable LMD stale cvak traces and dumps 10756 – Event to enable svpt/autotxn tracing 10757 – Event to control tracing row source: rows to dump per rowset 10758 – Event to control tracing row source: start row number 10759 – Event to control tracing row source: end row number 10760 – Event to control tracing row source: maximum encoded operands to check 10761 – Event to control tracing row source: size of dictionary 10780 – LogMiner API trace event 10781 – LogMiner reader trace event 10782 – LogMiner preparer trace event 10783 – LogMiner builder trace event 10784 – LogMiner dictionary trace event 10785 – LogMiner trace event 10787 – Trace intra-instance broadcast 10788 – Trace call stacks 10790 – LogMiner trace event 10791 – Logical Standby swithover/failover trace event 10792 – Logical Standby XDAT trace event 10793 – Logical Standby trace event 10794 – Logical Standby trace event 10795 – Time Drift (VKTM) Detection Process Trace Event 10796 – Elevate Scheduler Priority trace event 10800 – Disable Smart Disk Scans 10801 – Enable Smart Disk Trace 10805 – Trace state object operations 10809 – Trace state object allocate / free history 10810 – Trace snapshot too old 10811 – Trace block cleanouts 10812 – Trace Consistent Reads 10826 – Enable upgrade/downgrade error message trace 10830 – Trace group by sort row source 10831 – Trace group by rollup row source 10832 – Trace approximate NDV row source 10839 – Trace / debug caching module (qesca.c) 10840 – Trace / debug pl/sql caching module (kkxmInitCache) 10842 – Event for OCI Tracing and Statistics Info 10843 – Event for client result cache tracing 10845 – Enable Director tracing 10847 – OCI dump action is being invoked for internal error ORA- 10850 – Enable time manager tracing 10852 – Enable dumping of the AQ statistics hash table, Enable tracing for Enqueue Dequeue Operations 10853 – Enable tracing for Replicating AQ Operations 10865 – Control tracing of notification operations 10871 – Dump file open/close timestamp during media recovery 10880 – Trace Java VM execution 10883 – Event for Fast Application Notification tracing 10893 – TTC trace event 10907 – Trace extent management events 10908 – Trace temp tablespace events 10909 – Trace free list events 10924 – Import Storage Parse Error Ignore Event 10925 – Trace PL/SQL Execution 10926 – Trace name context forever 10927 – Trace name context forever 10928 1 Trace PL/SQL Execution 10929 – Trace name context forever 10930 – Trace name context forever 10931 – Trace name context forever 10932 – Trace name context forever 10933 – Trace name context forever 10934 – Trace name context forever 10936 – Trace name context forever 10937 – Trace name context forever 10938 1 Trace PL/SQL Execution Statistics 10939 – Trace name context forever 10940 – Trace name context forever 10941 – Trace name context forever 10943 – Trace name context forever 10944 – Trace name context forever 10945 – Trace name context forever 10946 – Trace name context forever 10947 – Trace name context forever 10948 – Trace name context forever 10960 – AQ tracing event 10961 – Enable tracing for Scheduler subscriber cleanup 10975 – Trace execution of parallel propagation 10976 – Internal package related tracing 10977 – Trace event for RepAPI 10979 – Trace flags for join index implementation 10990 – Dump spreadsheet info 12500 10 The SMON time mapping is mainly for flashback type queries to map a time to an SCN so it is probably simplest to copy the content to a holding table then delete ALL rows, then recycle the instance.SMON should start to populate the table with new time / SCN pairs from the time that the instance is started. Setting the 12500 event at system level should stop SMON from updating the SMON_SCN_TIME table. 12760 – ACMS tracing event 16300 – LSBY architected tracing event 16499 – Dump redo transport information 16605 – Event to control fast-start failover diagnostic tracing 16634 – Event for Data Guard NetSlave tracing 16752 – Dump System State 16756 – Dump Hang Analysis 22829 – Enables lateral queries using the undocumented LATERAL() operator ALTER SESSION SET EVENTS ‘22829 TRACE NAME CONTEXT FOREVER’; 28400 Enable TDE Tracing 28401 1 Disables the sleep time for logon failures caused by row cache locks leaving the system more vulnerable to brute force password attack 28402 Disable TDE Tracing 44410 0 xmlagg() with a GROUP BY can fail with ORA-22813 if the result is too large. No statistics 44410 1 Basic statistics CURSOR, PARSE, EXEC, FETCH ERROR, SORT UMAP, ERROR, UMAP, STATS and XCTEND. 44410 2 Same as level 1 44410 4 Level 1 plus binds 44410 8 Level 1 plus wait events 44410 12 Combine levels 4 and 8 |