Tuesday, May 28, 2013

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       [&ltcomp_name&gt]   List all components or describe &ltcomp_name&gt

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
  --------------------
    &ltevent_spec&gt   ::= '&ltevent_id&gt [&ltevent_scope&gt]
                                   [&ltevent_filter_list&gt]
                                   [&ltevent_parameters&gt]
                                   [&ltaction_list&gt]
                                   [off]'

    &ltevent_id&gt     ::= &ltevent_name number=""&gt[&lttarget_parameters&gt]

    &ltevent_scope&gt  ::= [&ltscope_name&gt: scope_parameters]

    &ltevent_filter&gt ::= {&ltfilter_name&gt: filter_parameters}

    &ltaction&gt       ::= &ltaction_name&gt(action_parameters)

    <*_parameters> ::= &ltparameter_name&gt = &ltvalue&gt[, ]


  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 &ltplan_stat&gt set to 'never'
      and parameter &ltwait&gt 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 [&lttarget_parameters&gt] construct) is only
      supported by specific events like the TRACE[] event

    * &ltevent_scope&gt and/or &ltevent_filter&gt 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    [&ltevent_name&gt]      List all events or describe &ltevent_name&gt
    SCOPE   [&ltscope_name&gt]      List all scopes or describe &ltscope_name&gt
    FILTER  [&ltfilter_name&gt]     List all filters or describe &ltfilter_name&gt
    ACTION  [&ltaction_name&gt]     List all actions or describe &ltaction_name&gt

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[&ltulevel 1-7=""&gt]
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           &ltub4&gt
SQL> ORADEBUG DOC EVENT NAME trace

trace: Main event to control UTS tracing

Usage
-------
trace [ component       &ltstring&gt ]
   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       &ltstring&gt,
   function        &ltstring&gt,
   file            &ltstring&gt,
   line            &ltub4&gt
SQL> ORADEBUG DOC EVENT NAME kg_event

kg_event: Support old error number events (use err# for short)

Usage
-------
kg_event [ ]
   level           &ltub4&gt,
   lifetime        &ltub4&gt,
   armcount        &ltub4&gt,
   traceinc        &ltub4&gt,
   forever         &ltub4&gt

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          &ltstring&gt ]

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 argument
You 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     &ltub4&gt,
             end_after       &ltub4&gt }
SQL> ORADEBUG DOC EVENT FILTER callstack

callstack: filter to only fire an event when a function is on the stack

Usage
-------
{callstack:  fname           &ltstring&gt,
             fprefix         &ltstring&gt,
             maxdepth        &ltub4&gt }
SQL> ORADEBUG DOC EVENT FILTER tag

tag: filter to only fire an event when a tag is set

Usage
-------
{tag:  tname           &ltstring&gt }
SQL> ORADEBUG DOC EVENT FILTER process

process: filter to set events only for a specific process

Usage
-------
{process:  ospid           &ltstring&gt,
           orapid          &ltub4&gt,
           pname           &ltstring&gt }
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      &ltub4&gt,
          lessThan        &ltub4&gt,
          greaterThan     &ltub4&gt }
SQL> ORADEBUG DOC EVENT FILTER errarg

errarg: filter to set error events only for a specific error argument

Usage
-------
{errarg:  arg1            &ltstring&gt,
          arg2            &ltstring&gt,
          arg3            &ltstring&gt,
          arg4            &ltstring&gt,
          arg5            &ltstring&gt,
          arg6            &ltstring&gt,
          arg7            &ltstring&gt,
          arg8            &ltstring&gt }

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 &ltlevel&gt 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 (&ltlevel&gt)
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 &ltlevel&gt (ids in X$KEWRTB)
AWR_FLUSH_TABLE_OFF  
  - Disable flush of table id &ltlevel&gt (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 action
You 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           &ltub4&gt)

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]

No comments:

Post a Comment