Oracle tracing and events ORADEBUG
Oracle made big improvements to its diagnosability infrastructure in database version 11g.The new 11g kernel debug and diagnostics infrastructure allows you to be much more precise when tracing & dumping diagnostic info. Before Oracle 11g versions, the diagnostic events was more limited syntax en usable.Now you can enable tracing/actions only when the current function call stack contains a specific function name (or prefix)or tracing is only done when the SQL statement executed has specified SQLID.
Trace Example - Trace events activation before 11g.
alter session set event 'sql_trace level 12';
alter session set event 'sql_trace off';Trace example - Trace events activation after 11g. Not the whole session only the SQLID will be traced.
alter session set 'sql_trace [sql:fb2yuOp1kgvhr] level 12'';
alter session set 'sql_trace [sql:fb2yuOp1kgvhr] off';
ORADEBUG DOC
Type insite sqlplus session the following text command ORADEBUG DOC. This will give you the full syntax of ORADEBUG options of what you can use starting from 11g onwards.SQL> ORADEBUG DOC Internal Documentation ********************** EVENT Help on events (syntax, event list, ...) COMPONENT [<comp_name>] List all components or describe <comp_name>
ORADEBUG DOC EVENT
The syntax immediately below shows that it's possible to assign actions (action_list) to events (functions, code locations in Oracle) and make these actions fire only in specific conditions (when event scope (SQL_ID) and filter conditions are matched).SQL> ORADEBUG DOC EVENT
Event Help:
***********
Formal Event Syntax
--------------------
<event_spec> ::= '<event_id> [<event_scope>]
[<event_filter_list>]
[<event_parameters>]
[<action_list>]
[off]'
<event_id> ::= <event_name number="">[<target_parameters>]
<event_scope> ::= [<scope_name>: scope_parameters]
<event_filter> ::= {<filter_name>: filter_parameters}
<action> ::= <action_name>(action_parameters)
<*_parameters> ::= <parameter_name> = <value>[, ]
Some Examples
-------------
* Set event 10235 level 1:
alter session set events '10235';
* Set events SQL_TRACE (a.k.a. 10046) level 1:
alter session set events 'sql_trace';
* Turn off event SQL_TRACE:
alter session set events 'sql_trace off';
* Set events SQL_TRACE with parameter <plan_stat> set to 'never'
and parameter <wait> set to 'true':
alter session set events 'sql_trace wait=true, plan_stat=never';
* Trace in-memory the SQL_MONITOR component (the target) and all its
sub-components at level high. Get high resolution time for each
trace:
alter session set events 'trace[sql_mon.*] memory=high,
get_time=highres';
* On-disk trace PX servers p000 and p005 for components 'sql_mon'
and 'sql_optimizer' (including sub-components) at level highest:
alter system set events 'trace[sql_mon | sql_optimizer.*]
{process: pname = p000 | p005}';
* Same as above but only when SQL id '7ujay4u33g337' is executed:
alter system set events 'trace[sql_mon | sql_optimizer.*]
[sql: 7ujay4u33g337]
{process: pname = p000 | p005}';
* Execute an action immediatly by using 'immediate' for the event
name:
alter session set events 'immediate eventdump(system)'
* Create an incident labeled 'table_missing' when external error
942 is signaled by process id 14534:
alter session set events '942 {process: 14534}
incident(table_missing)';
Notes
-----
* Implicit parameter level is 1 by default
e.g. '10053' is same as '10053 level 1'
* Event target (see [<target_parameters>] construct) is only
supported by specific events like the TRACE[] event
* <event_scope> and/or <event_filter> are constructs
that can be used for any event
* Same event can be set simultaneously for a different scope or
target but not for different filters.
* '|' character can be used to select multiple targets, scope or
filters.
E.g. 'sql_trace [sql: sql_id=g3yc1js3g2689 | sql_id=7ujay4u33g337]'
* '=' sign is optional in <*_parameters>
E.g. 'sql_trace level 12';
* Like PL/SQL, no need to specify the parameter name for target,
scope, filters and action. Resolution is done by position in
that case:
E.g. 'sql_trace [sql: g3yc1js3g2689 | 7ujay4u33g337]'
Help sub-topics
---------------
NAME [<event_name>] List all events or describe <event_name>
SCOPE [<scope_name>] List all scopes or describe <scope_name>
FILTER [<filter_name>] List all filters or describe <filter_name>
ACTION [<action_name>] List all actions or describe <action_name>
ORADEBUG DOC EVENT NAME
SQL> ORADEBUG DOC EVENT NAME Events in library DIAG: ------------------------------ trace[] Main event to control UTS tracing disable_dde_action[] Event used by DDE to disable actions ams_trace[] Event to dump ams performance trace records ams_rowsrc_trace[] Event to dump ams row source tracing sweep_verification Event to enable sweep file verification enable_xml_inc_staging Event to enable xml incident staging format Events in library RDBMS: ------------------------------ alert_text event for textual alerts trace_recursive event to force tracing recursive SQL statements clientid_overwrite event to overwrite client_identifier when client_info is set sql_monitor event to force monitoring SQL statements eventsync_tac Event posted from events syncing tac sql_trace event for sql trace pmon_startup startup of pmon process background_startup startup of background processes db_open_begin start of db open operation Events in library GENERIC: ------------------------------ kg_event[] Support old error number events (use err# for short) Events in library CLIENT: ------------------------------ oci_trace event for oci trace Events in library LIBCELL: ------------------------------ libcell_stat libcell statistics level specification cellclnt_skgxp_trc_ops Controls to trace SKGXP operations cellclnt_high_lat_ops Control to trace High-latency I/O operations Events in library ADVCMP: ------------------------------ arch_comp_level[] arch_comp_level[<ulevel 1-7="">] ccmp_debug columnar compression debug event ccmp_align columnar compression enable alignment ccmp_countstar columnar compression enable count(*) optimization ccmp_dumpunaligned columnar compression dump dbas of unaligned CUs
ORADEBUG DOC EVENT NAME <event_name>
This syntax allows you to get more information about parameters for some events (especially these ones suffixed with [ ] in above output):SQL> ORADEBUG DOC EVENT NAME sql_trace sql_trace: event for sql trace Usage ------- sql_trace wait < false | true >, bind < false | true >, plan_stat < never | first_execution | all_executions >, level <ub4>
SQL> ORADEBUG DOC EVENT NAME trace trace: Main event to control UTS tracing Usage ------- trace [ component <string> ] disk < default | lowest | low | medium | high | highest | disable >, memory < default | lowest | low | medium | high | highest | disable >, get_time < disable | default | seq | highres | seq_highres >, get_stack < disable | default | force >, operation <string>, function <string>, file <string>, line <ub4>
SQL> ORADEBUG DOC EVENT NAME kg_event kg_event: Support old error number events (use err# for short) Usage ------- kg_event [ ] level <ub4>, lifetime <ub4>, armcount <ub4>, traceinc <ub4>, forever <ub4>
ORADEBUG DOC EVENT SCOPE
SQL> ORADEBUG DOC EVENT SCOPE Event scopes in library RDBMS: ------------------------------ SQL[] sql scope for RDBMS
ORADEBUG DOC EVENT SCOPE SQL
The scope specifier allows you to fire events and trace only when Oracle is executing a specific SQL_ID:SQL> ORADEBUG DOC EVENT SCOPE SQL SQL: sql scope for RDBMS Usage ------- [SQL: sql_id <string> ]
ORADEBUG DOC EVENT FILTER
The event filter allows you to fire event actions only when certain event filter conditions are met, like only when a specific (background) process hits the event, when a specific error condition is raised or when a specific function is in the call stack:SQL> ORADEBUG DOC EVENT FILTER Event filters in library DIAG: ------------------------------ occurence filter to implement counting for event checks callstack filter to only fire an event when a function is on the stack tag filter to only fire an event when a tag is set Event filters in library RDBMS: ------------------------------ process filter to set events only for a specific process pgadep filter to only fire an event when the pgadep matches a given value or falls within a range Event filters in library GENERIC: ------------------------------ errarg filter to set error events only for a specific error argumentYou can get more documentation for each item listed in the above output:
SQL> ORADEBUG DOC EVENT FILTER occurence
occurence: filter to implement counting for event checks
Usage
-------
{occurence: start_after <ub4>,
end_after <ub4> }
SQL> ORADEBUG DOC EVENT FILTER callstack
callstack: filter to only fire an event when a function is on the stack
Usage
-------
{callstack: fname <string>,
fprefix <string>,
maxdepth <ub4> }
SQL> ORADEBUG DOC EVENT FILTER tag
tag: filter to only fire an event when a tag is set
Usage
-------
{tag: tname <string> }
SQL> ORADEBUG DOC EVENT FILTER process
process: filter to set events only for a specific process
Usage
-------
{process: ospid <string>,
orapid <ub4>,
pname <string> }
SQL> ORADEBUG DOC EVENT FILTER pgadep
pgadep: filter to only fire an event when the pgadep matches a given value or falls
within a range
Usage
-------
{pgadep: exactDepth <ub4>,
lessThan <ub4>,
greaterThan <ub4> }
SQL> ORADEBUG DOC EVENT FILTER errarg
errarg: filter to set error events only for a specific error argument
Usage
-------
{errarg: arg1 <string>,
arg2 <string>,
arg3 <string>,
arg4 <string>,
arg5 <string>,
arg6 <string>,
arg7 <string>,
arg8 <string> }
ORADEBUG DOC EVENT ACTION
Finally, the action keyword defines what to do when the event has been hit:SQL> ORADEBUG DOC EVENT ACTION Actions in library DIAG: --------------------------- dumpFrameContext - Dump Frame Context contents dumpBuckets kgsfdmp dumpDiagCtx dumpDbgecPopLoc dumpDbgecMarks dumpGenralConfiguration dumpADRLockTable act1 action1 action2 UTDumpGC dbgvci_action_signal_crash Actions in library RDBMS: --------------------------- incident - Create an Incident sqlmon_dump - SQL Monitor Dump SGA Action flashfreeze oradebug - debug process using ORADEBUG debugger - debug process using System Debugger debug - alias for 'debugger' - debug process using System Debugger crash - crash the instance eventdump - list events that are set in the group kdlut_bucketdump_action kzxt_dump_action dumpKernelDiagState HMCHECK (async) DATA_BLOCK_INTEGRITY_CHECK (async) CF_BLOCK_INTEGRITY_CHECK (async) DB_STRUCTURE_INTEGRITY_CHECK (async) REDO_INTEGRITY_CHECK (async) TRANSACTION_INTEGRITY_CHECK (async) SQL_TESTCASE_REC (async) SQL_TESTCASE_REC_DATA (async) ORA_12751_DUMP sqladv_dump_dumpctx ORA_4030_DUMP - dump summary of PGA memory usage, largest allocations kcfis_action - kcfis actions exadata_dump_modvers - Exadata dump module versions QUERY_BLOCK_DUMP - Debug action for dumping a qbcdef tree ASM_MOUNT_FAIL_CHECK (async) ASM_ALLOC_FAIL_CHECK (async) ASM_ADD_DISK_CHECK (async) ASM_FILE_BUSY_CHECK (async) TRACE_BUFFER_ON - Allocate trace output buffer for ksdwrf() TRACE_BUFFER_OFF - Flush and deallocate trace output buffer for ksdwrf() LATCHES - Dump Latches XS_SESSION_STATE - Dump XS session state PROCESSSTATE - Dump process state SYSTEMSTATE - Dump system state INSTANTIATIONSTATE - Dump instantiation state CONTEXTAREA - Dump cursor context area HEAPDUMP - Dump memory heap (1-PGA, 2-SGA, 4-UGA, +1024-Content) POKE_LENGTH - Set length before poking value POKE_VALUE - Poke a value into memory POKE_VALUE0 - Poke 0 value into memory GLOBAL_AREA - Dump fixed global area(s) (1=PGA/2=SGA/3=UGA, add +8 for pointer content) REALFREEDUMP - Dump PGA real free memory allocator state FLUSH_JAVA_POOL - Flush Java pool PGA_DETAIL_GET - Ask process to publish PGA detail info (level is pid) PGA_DETAIL_DUMP - Dump PGA detail information for process (level is pid) PGA_DETAIL_CANCEL - Free PGA detail request (level is pid) PGA_SUMMARY - Summary of PGA memory usage, largest allocations MODIFIED_PARAMETERS - Dump parameters modifed by session (level unused) ERRORSTACK - Dump state (ksedmp). Use INCIDENT action to create incident CALLSTACK - Dump call stack (level > 1 to dump args) RECORD_CALLSTACK - Record or dump call stack, level = #frames (level += 1000000 go to trc) BG_MESSAGES - Dump routine for background messages ENQUEUES - Dump enqueues (level >=2 adds resources, >= 3 adds locks) KSTDUMPCURPROC - Dump current process trace buffer (1 for all events) KSTDUMPALLPROCS - Dump all processes trace buffers (1 for all events) KSTDUMPALLPROCS_CLUSTER - Dump all processes (cluster wide) trace buffers (1 for all events) KSKDUMPTRACE - Dumping KSK KST tracing (no level) DBSCHEDULER - Dump ressource manager state LDAP_USER_DUMP - Dump LDAP user mode LDAP_KERNEL_DUMP - Dump LDAP kernel mode DUMP_ALL_OBJSTATS - Dump database objects statistics DUMPGLOBALDATA - Rolling migration DUMP GLOBAL DATA HANGANALYZE - Hang analyze HANGANALYZE_PROC - Hang analyze current process HANGANALYZE_GLOBAL - Hang analyze system GES_STATE - Dump DML state OCR - OCR client side tracing CSS - CSS client side tracing CRS - CRS client side tracing SYSTEMSTATE_GLOBAL - Perform cluster wide system state dump (via DIAG) DUMP_ALL_COMP_GRANULE_ADDRS - MMAN dump all granule addresses of all components (no level) DUMP_ALL_COMP_GRANULES - MMAN dump all granules of all components (1 for partial list) DUMP_ALL_REQS - MMAN dump all pending memory requests to alert log DUMP_TRANSFER_OPS - MMAN dump transfer and resize operations history DUMP_ADV_SNAPSHOTS - MMAN dump all snapshots of advisories (level unused) CONTROLF - DuMP control file info FLUSH_CACHE - Flush buffer cache without shuting down the instance BUFFERS - Dump all buffers in the buffer cache at level l SET_TSN_P1 - Set tablespace # for buffer dump (level = ts# + 1) BUFFER - Dump all buffers for full relative dba <level> at lvl 10 BC_SANITY_CHECK - Run buffer cache sanity check (level = 0xFF for full) SET_NBLOCKS - Set number of blocks for range reuse checks CHECK_ROREUSE_SANITY - Check range/object reuse sanity (level = ts#) DUMP_PINNED_BUFFER_HISTORY - kcb Dump pinned buffers history (level = # buffers) REDOLOGS - Dump all online logs according to the level LOGHIST - Dump the log history (1: dump earliest/latest entries, >1: dump most recent 2**level entries) REDOHDR - Dump redo log headers LOCKS - Dump every lock element to the trace file GC_ELEMENTS - Dump every lock element to the trace file FILE_HDRS - Dump database file headers KRB_TRACE - Set krb trace options FBINC - Dump flashback logs of the current incarnation and all its ancestors. FBHDR - Dump all the flashback logfile headers FLASHBACK_GEN - Dump flashback generation state KTPR_DEBUG - Parallel txn recovery (1: cleanup check, 2: dump ptr reco ctx, 3: dump recent smon runs) DUMP_TEMP - Dump temp space management state (no level) DROP_SEGMENTS - Drop unused temporary segments TREEDUMP - Dump an index tree rooted at dba BLOCKDBA (<level>) KDLIDMP - Dump 11glob inodes states (level = what to dump) ROW_CACHE - Dump all cache objects LIBRARY_CACHE - Dump the library cache (level > 65535 => level = obj @) CURSORDUMP - Dump session cursors CURSOR_STATS - Dump all statistics information for cursors SHARED_SERVER_STATE - Dump shared server state LISTENER_REGISTRATION - Dump listener registration state JAVAINFO - Dump Oracle Java VM KXFPCLEARSTATS - Clear all Parallel Query messaging statistics KXFPDUMPTRACE - Dump Parallel Query in-memory traces KXFXSLAVESTATE - Dump PX slave state (1: uga; 2: current cursor state; 3: all cursors) KXFXCURSORSTATE - Dump PX slave cursor state WORKAREATAB_DUMP - Dump SQL Memory Manager workarea table OBJECT_CACHE - Dump the object cache SAVEPOINTS - Dump savepoints RULESETDUMP - Dump rule set FAILOVER - Set condition failover immediate OLAP_DUMP - Dump OLAP state AWR_FLUSH_TABLE_ON - Enable flush of table id <level> (ids in X$KEWRTB) AWR_FLUSH_TABLE_OFF - Disable flush of table id <level> (ids in X$KEWRTB) ASHDUMP - Dump ASH data (level = # of minutes) HM_FW_TRACE - DIAG health monitor set tracing level IR_FW_TRACE - DIAG intelligent repair set/clear trace HEAPDUMP_ADDR - Heap dump by address routine (level > 1 dump content) POKE_ADDRESS - Poke specified address (level = value) CURSORTRACE - Trace cursor by hash value (hash value is address) RULESETDUMP_ADDR - Dump rule set by address Actions in library GENERIC: --------------------------- xdb_dump_buckets dumpKGERing - Dump contents of KGE ring buffer Actions in library CLIENT: --------------------------- kpuActionDefault - dump OCI data kpuActionSignalCrash - crash and produce a core dump (if supported and possible) kpudpaActionDpapi - DataPump dump actionYou can get more details about some actions by running the doc command for the library.action:
SQL> ORADEBUG DOC EVENT ACTION RDBMS.query_block_dump QUERY_BLOCK_DUMP - Debug action for dumping a qbcdef tree Usage ------- QUERY_BLOCK_DUMP( level <ub4>)
ORADEBUG DOC COMPONENT
You can trace and set actions for Oracle kernel components (if you don't know exact function name which you want to trace). The following command would apply the actions query_block_dump, processstate dump and call stack dump only when executing code in RDBMS.SQL_Transform module (the query transformation engine) and only when the SQL ID executed is 32cqz71gd8wy3. Disk=high allows tracing to disk (if that component is able to trace anything).SQL> alter session set events 'trace[RDBMS.SQL_Transform] [SQL: 32cqz71gd8wy3] disk=high RDBMS.query_block_dump(1) processstate(1) callstack(1)'; Session altered.You can check what sub-components are there under the SQL_Transform component (and you can trace each of these individually if you like):
SQL> ORADEBUG DOC COMPONENT RDBMS.SQL_Transform
SQL_Transform SQL Transformation (kkq, vop, nso)
SQL_MVRW SQL Materialized View Rewrite
SQL_VMerge SQL View Merging (kkqvm)
SQL_Virtual SQL Virtual Column (qksvc, kkfi)
All (registered) components in Oracle kernel can be listed like this:
SQL> ORADEBUG DOC COMPONENT
Components in library DIAG:
--------------------------
diag_uts Unified Tracing Service (dbgt, dbga)
uts_vw UTS viewer toolkit (dbgtp, dbgtn)
diag_adr Automatic Diagnostic Repository (dbgr)
ams_comp ADR Meta-data Repository (dbgrm)
ame_comp ADR Export/Import Services (dbgre)
ami_comp ADR Incident Meta-data Services (dbgri)
diag_ads Diagnostic Directory and File Services (dbgrf,
sdbgrf, sdbgrfu, sdbgrfb)
diag_hm Diagnostic Health Monitor
diag_ips Diagnostic Incident Packaging System
diag_dde Diagnostic Data Extractor (dbge)
diag_fmwk Diagnostic Framework (dbgc)
diag_ilcts Diagnostic Inter-Library Compile-time Service (dbgf)
diag_attr Diagnostic Attributes Management
diag_comp Diagnostic Components Management
diag_testp Diagnostic component test parent (dbgt)
diag_testc1 Diagnostic component test child 1
diag_testc2 Diagnostic component test child 2
KGSD Kernel Generic Service Debugging (kgsd)
diag_events Diagnostic Events (dbgd)
diag_adl Diagnostic ARB Alert Log (dbgrl, dbgrlr)
diag_vwk Diagnostic viewer toolkit (dbgv)
diag_vwk_parser Diagnostic viewer parser (dbgvp, dbgvl)
diag_vwk_uts Diagnostic viewer for UTS traces and files (dbgvf)
diag_vwk_ams Diagnostic viewer for AMS metadata (dbgvm)
diag_vwk_ci Diagnostic viewer for command line (dbgvci)
kghsc KGHSC Compact Stream (kghsc)
Components in library RDBMS:
--------------------------
SQL_Compiler SQL Compiler
SQL_Parser SQL Parser (qcs)
SQL_Semantic SQL Semantic Analysis (kkm)
SQL_Optimizer SQL Optimizer
SQL_Transform SQL Transformation (kkq, vop, nso)
SQL_MVRW SQL Materialized View Rewrite
SQL_VMerge SQL View Merging (kkqvm)
SQL_Virtual SQL Virtual Column (qksvc, kkfi)
SQL_APA SQL Access Path Analysis (apa)
SQL_Costing SQL Cost-based Analysis (kko, kke)
SQL_Parallel_Optimization SQL Parallel Optimization (kkopq)
SQL_Code_Generator SQL Code Generator (qka, qkn, qke, kkfd, qkx)
SQL_Parallel_Compilation SQL Parallel Compilation (kkfd)
SQL_Expression_Analysis SQL Expression Analysis (qke)
SQL_Plan_Management SQL Plan Managment (kkopm)
SQL_Execution SQL Execution (qer, qes, kx, qee)
Parallel_Execution Parallel Execution (qerpx, qertq, kxfr, kxfx,
kxfq, kxfp)
PX_Messaging Parallel Execution Messaging (kxfp)
PX_Group Parallel Execution Slave Group (kxfp)
PX_Affinity Parallel Affinity (ksxa)
PX_Buffer Parallel Execution Buffers (kxfpb)
PX_Granule Parallel Execution Granules (kxfr)
PX_Control Parallel Execution Control (kxfx)
PX_Table_Queue Parallel Execution Table Queues (kxfq)
PX_Scheduler Parallel Execution Scheduler (qerpx)
PX_Queuing Parallel Execution Queuing (kxfxq)
Bloom_Filter Bloom Filter (qerbl, qesbl)
PGA_Manage PGA Memory Management
PGA_Compile PGA Memory Compilation
PGA_IMM PGA Memory Instance Manage
PGA_CMM PGA Memory Cursor Manage
PGA_ADV PGA Memory Advisor
rdbms_dde RDBMS Diagnostic Data Extractor (dbke)
VOS VOS (ks)
hang_analysis Hang Analysis (ksdhng)
background_proc Background Processes (ksb, ksbt)
system_param System Parameters (ksp, kspt)
ksu Kernel Service User (ksu)
ksutac KSU Timeout Actions
ksv_trace Kernel Services Slave Management (ksv)
sql_mon SQL Monitor (keswx)
sql_mon_deamon SQL Monitor Deamon
sql_mon_query SQL Monitor Query
CACHE_RCV Cache Recovery (kcv, kct, kcra, kcrp, kcb)
DIRPATH_LOAD Direct Path Load (kl, kdbl, kpodp)
DIRPATH_LOAD_BIS Direct Path Kpodpbis Routine (kpodp)
RAC Real Application Clusters
GES Global Enqueue Service
GCS Global Cache Service (kjb)
GSIPC Global Enqueue/Cache Service IPC
KSI Kernel Service Instance locking (ksi)
RAC_ENQ Enqueue Operations
RAC_RCFG Reconfiguration
RAC_DRM Dynamic Remastering
RAC_MRDOM Multiple Recovery Domains
CGS Cluster Group Services (kjxg)
CGSIMR Instance Membership Recovery (kjxgr)
DD GES Deadlock Detection
GCS_BSCN Broadcast SCN (kjb)
RAC_WLM Work Load Management (wlm)
RAC_MLMDS RAC Multiple LMS (kjm)
GCS_READMOSTLY GCS Read-mostly (kjb)
GCS_READER_BYPASS GCS Reader Bypass (kjb)
GCS_DELTAPUSH GCS Delta Push (kjb)
db_trace RDBMS server only tracing
kst server trace layer tracing (kst)
ddedmp RDBMS Diagnostic Data Extractor Dumper (dbked)
cursor Shared Cursor (kxs, kks)
Bind_Capture Bind Capture Tracing
KSM Kernel Service Memory (ksm)
KSE Kernel Service Error Manager (kse)
explain SQL Explain Plan (xpl)
rdbms_event RDBMS Events (dbkd)
LOB_INODE Lob Inode (kdli)
rdbms_adr RDBMS ADR (dbkr)
ASM Automatic Storage Management (kf)
KFK KFK (kfk)
KFKIO KFK IO (kfkio)
KFKSB KFK subs (kfksubs)
KFN ASM Networking subsystem (kfn)
KFNU ASM Umbillicus (kfnm, kfns, kfnb)
KFNS ASM Server networking (kfns)
KFNC ASM Client networking (kfnc)
KFIS ASM Intelligent Storage interfaces (kfis)
KFM ASM Node Monitor Interface Implementation (kfm)
KFMD ASM Node Monitor Layer for Diskgroup Registration (
kfmd)
KFMS ASM Node Monitor Layers Support Function Interface
(kfms)
KFFB ASM Metadata Block (kffb)
KFFD ASM Metadata Directory (kffd)
KFZ ASM Zecurity subsystem (kfz)
DML DML Drivers (ins, del, upd)
Health_Monitor Health Monitor
DRA Data Repair Advisor
DIRACC Direct access to fixed tables (kqfd)
ASH Active Session History (kewa)
PART Partitioning (kkpo, qespc, qesma, kkpa, qergi)
PART_IntPart Interval Partitioning
LOB_KDLW Lob kdlw (kdlw)
LOB_KDLX Lob xfm (kdlx)
LOB_KDLXDUP Lob dedup (kdlxdup)
LOB_KDLRCI Lob rci (kdlrci)
LOB_KDLA SecureFile Archive (kdla)
SQL_Manage SQL Manageability (kes)
SQL_Manage_Infra Other SQL Manageability Infrastructure (kesai, kesqs,
kesatm, kesutl, kessi, keswat, keswts, keswsq)
SQL_Tune SQL Tuning Advisor (kest)
SQL_Tune_Auto SQL Tuning Advisor (auto-tune) (kestsa)
SQL_Tune_Index SQL Tuning Advisor (index-tune) (kestsi)
SQL_Tune_Plan SQL Tuning Advisor (plan node analysis) (kestsp)
SQL_Tune_Px SQL Tuning Advisor (parallel execution) (kestsa)
SQL_Tune_Fr SQL Tuning Advisor (fix regression) (kestsa)
SQL_Test_Exec SQL Test-Execute Service (kestse)
SQL_Perf SQL Performance Analyzer (kesp, keswpi)
SQL_Repair SQL Repair Advisor (kesds)
Auto_Tune_Opt Auto Tuning Optimizer (kkoat)
SQL_Analyze SQL Analyze (qksan)
SQL_DS SQL Dynamic Sampling Services (qksds)
SQL_DDL SQL DDL (atb, ctc, dtb)
RAT_WCR Real Application Test: Workload Capture and Replay
(kec)
Spatial Spatial (md)
Spatial_IND Spatial Indexing (mdr)
Spatial_GR Spatial GeoRaster (mdgr)
Text Text (dr)
rdbms_gc RDBMS Diagnostic Generic Configuration (dbkgc)
XS XS Fusion Security (kzx)
XSSESSION XS Session (kzxs)
XSPRINCIPAL XS Principal (kzxu)
XSSECCLASS XS Security Class (kzxc)
XSXDS XS Data Security (kzxd)
XSVPD XS VPD
XSXDB_DEFAULT XS XDB
XS_MIDTIER XS Midtier (kpuzxs)
AQ Streams Advanced Queuing (kwq, kkcn, kpon, kpoaq,
kpce, kpcm, kpun, kpuaq)
AQ_DEQ Streams Advanced Queuing Dequeue (kwqid, kwqdl)
AQ_TM Streams Advanced Queuing Time Manager (kwqit, kwqmn)
KSFM Kernel Service File Mapping (ksfm)
KXD Exadata specific Kernel modules (kxd, kcfis)
KXDAM Exadata Disk Auto Manage (kxdam)
Components in library GENERIC:
--------------------------
Generic_VOS Generic VOS
VOS_Heap_Manager VOS Heap Manager
VOS_Latches VOS Latches
VOS_GST VOS Generic Stack Trace (kgds)
XML XML (qmxt, qmxq)
Generic_XDB Generic XDB
XDB_Repository XDB Repository (qme)
XDB_Protocols XDB Protocols (qmh, qmf, qmn)
XDB_Query XDB Query (qmxtra, qerxp)
XDB_XMLIndex XDB XMLIndex (qmxtri, qmixq)
XDB_Schema XDB Schema (qmxtri, qmixq)
XDB_XOB XDB XOB (qmx)
XDB_CSX XDB CSX (qmcxe, qmcxd)
XDB_Default XDB Default
LOB LOB (koll, kola)
LOB_Refcount LOB Refcount (kolr)
LOB_Default LOB Default (kole, kokl, koxs, kokla, koklm, koklv)
KGH KGH Memory Allocator (kgh)
KGF ASM Automatic Storage Management (kgf)
LIBCACHE LIBCACHE (kgl, kql)
Components in library CLIENT:
--------------------------
Client_KPU Client KPU
KPU_Memory KPU Memory
KPU_TTC KPU TTC
KPU_Relational KPU Relational
KPU_Objects KPU Objects
KPU_LOBS KPU LOBS
SQLLDR_Load SQLLDR Load (ul)
DPAPI_Load DPAPI Load (kpudp)
Components in library LIBCELL:
--------------------------
Client_Library Client Library
Disk_Layer Disk Layer
Network_Layer Network Layer
Components in library ORANET:
--------------------------
TNSLSNR OraNet Listener
NET_NSGR Network Service Generic Registration
NET_NSGI TNI Network Service Generic Listener User-defined
class
CMAN OraNet Connection Manager
NET OraNet Services
NET_NI Network Interface Layer
NET_NS Network Session Layer
NET_NT Network Transport Layer
NET_NTM Network Transport Mailbox Layer
NET_NTP Network Transport IPC Layer
NET_NTT Network Transport TCP/IP Layer
NET_NTUS Network Transport Unix Domain Sockets Layer
NET_NL Network Library
NET_NA Network Authentication
NET_NZ Network Zecurity
NET_NTZ Network SSL
NET_NU Network Trace Route Utility
NET_NN Network Names
Components in library ADVCMP:
--------------------------
ADVCMP_MAIN Archive Compression (kdz)
ADVCMP_COMP Archive Compression: Compression (kdzc, kdzh, kdza)
ADVCMP_DECOMP Archive Compression: Decompression (kdzd, kdzs)
You can do more with this command:
Only trace when the SQL is executed from top level (directly by application) as opposed to from recursive context (like SQL fired from PL/SQL or data dictionary queries where dep>0 in SQL trace
Use in the trace command the pgadep 0
SQL> alter session set events 'sql_trace[SQL: 32cqz71gd8wy3] {pgadep: exactdepth 0} plan_stat=all_executions,wait=true,bind=true';
Other commands options in the trace commando:
plan_stat=all => dumps the STAT# lines (execution plan & execution statistics from last execution) at every execution of the cursor as opposed to only when the cursor is closed
SQL> alter session set events 'trace[RDBMS.SQL_Transform] [SQL: 32cqz71gd8wy3] disk=high RDBMS.query_block_dump(1) processstate(1) callstack(1)';This statement cause the optimizer SQL Transformation engine tracing would happen only when executing the SQLID mentioned above and additionally three dumps (query_block_dump,processstate and current call stack) are performed every time this SQLID execution hits events in the SQL_Transform module.
References
- How to set EVENTS in the SPFILE [MOS 160178.1]
- How to determine which system events are currently being set? [MOS 845043.1]
- Important Customer information about using Numeric Events [MOS 75713.1]
- Getting Disassembly output on Unix platforms [MOS 300892.1]
- How To Use The New 11g Events++ Syntax For Easier SQL Tracing Of Datapump Operations? [MOS 813737.1]
