{"id":346,"date":"2020-03-06T07:47:28","date_gmt":"2020-03-06T07:47:28","guid":{"rendered":"http:\/\/tomas.papp.me.uk\/?p=346"},"modified":"2021-11-11T08:49:44","modified_gmt":"2021-11-11T08:49:44","slug":"oracle-events","status":"publish","type":"post","link":"https:\/\/tomas.papp.me.uk\/?p=346","title":{"rendered":"Oracle events"},"content":{"rendered":"\n

Oracle SQL Trace and TKPROF \n\n\n\n <\/p>\n\n\n\n

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 SQL<\/em>Net 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 <\/td><\/tr><\/tbody><\/table><\/figure>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-346","post","type-post","status-publish","format-standard","hentry","category-oracle"],"_links":{"self":[{"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=\/wp\/v2\/posts\/346","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=346"}],"version-history":[{"count":1,"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=\/wp\/v2\/posts\/346\/revisions"}],"predecessor-version":[{"id":347,"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=\/wp\/v2\/posts\/346\/revisions\/347"}],"wp:attachment":[{"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=346"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=346"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=346"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}