oradebug

Can not purge obsolete cursor in 11.2.0.3

November 28, 2012 11g, maintain, oracle No comments

11.2.0.3中Oracle引入了_cursor_obsolete_threshold隐藏参数,默认为100,表示一个parent cursor最多可以有100个child cursor;如果SQL需要第101个child cursor,则将该parent cursor and its 100 child cursor设置为obsoleted,并且重新生成新的parent cursor. 但是那些过期的child cursor不会从v$sql中消失,dbms_shared_pool.purge也无法将这些过期的child cursor flush出去,Oracle正在fix这个bug.

eg:

-bash-3.2$ ./opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.0
Copyright (c) 2012, Oracle Corporation. All rights reserved.

Oracle Home : /u01/app/oracle/product/11.2.0/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0/db_1/oraInst.loc
OPatch version : 11.2.0.3.0
OUI version : 11.2.0.3.0
Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2012-11-28_12-40-02PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2012-11-28_12-40-02PM.txt

——————————————————————————–
Installed Top-level Products (1):

Oracle Database 11g 11.2.0.3.0
There are 1 products installed in this Oracle Home.

Interim patches (1) :

Patch 13923374 : applied on Thu Sep 20 14:10:21 CST 2012
Unique Patch ID: 14893407
Patch description: “Database Patch Set Update : 11.2.0.3.3 (13923374)”
Created on 14 Jun 2012, 23:16:11 hrs PST8PDT
Sub-patch 13696216; “Database Patch Set Update : 11.2.0.3.2 (13696216)”
Sub-patch 13343438; “Database Patch Set Update : 11.2.0.3.1 (13343438)”

From v$sqlarea only return one row:

SQL> select address,hash_value from v$sqlarea where sql_id=’27svyp3s52cu0′

ADDRESS HASH_VALUE
—————- ———-
0700001DBB270900 4031853376

you can only purge this cursor:

SQL> exec dbms_shared_pool.purge(‘0700001DBB270900,4031853376′,’C’);

PL/SQL procedure successfully completed.

but if you want to purge like this:

SQL> select distinct address,hash_value from v$sql where sql_id=’27svyp3s52cu0′;

ADDRESS HASH_VALUE
—————- ———-
0700001DBE384CD8 4031853376
0700001DBD3119A0 4031853376
0700001DBC063368 4031853376
0700001DBDB39548 4031853376
0700001DBC993898 4031853376
0700001DBC75A2E8 4031853376
0700001DBCC34B20 4031853376
0700001DBB270900 4031853376
0700001DBCFFE730 4031853376
0700001DBCB15A70 4031853376
0700001DBFA741A8 4031853376
0700001DBC4FEA60 4031853376
0700001DBE3F49F8 4031853376
0700001DBB4D1938 4031853376

14 rows selected

SQL> exec dbms_shared_pool.purge(‘0700001DBE384CD8,4031853376′,’C’);

begin dbms_shared_pool.purge(‘0700001DBE384CD8,4031853376′,’C’); end;

ERROR at line 1:
ORA-06570: shared pool object does not exist, cannot be pinned/purged
ORA-06512: at “SYS.DBMS_SHARED_POOL”, line 48
ORA-06512: at “SYS.DBMS_SHARED_POOL”, line 115
ORA-06512: at line 1

SQL> exec dbms_shared_pool.purge(‘0700001DBB4D1938,4031853376′,’C’);
BEGIN dbms_shared_pool.purge(‘0700001DBB4D1938,4031853376′,’C’); END;

*
ERROR at line 1:
ORA-06570: shared pool object does not exist, cannot be pinned/purged
ORA-06512: at “SYS.DBMS_SHARED_POOL”, line 48
ORA-06512: at “SYS.DBMS_SHARED_POOL”, line 115
ORA-06512: at line 1

extended statistics for this cursor:

Shared Cursors Statistics
Total Parses		29,918
Hard Parses		1,665
Child Cursors		1,478
Loaded Plans		1,478
Invalidations		1,473
Largest Cursor Size (KB)		292.43
All Cursor Size (KB)		399,376.98
First Load Time		Nov 23, 2012 4:26:13 AM GMT+08:00
Last Load Time		Nov 28, 2012 1:10:10 PM GMT+08:00
Execution Statistics

Total	Per Execution	Per Row
Executions	1,264,476	1	1.00
Elapsed Time (sec)	18,717.60	0.01	0.01
CPU Time (sec)	1,087.67	<0.01	<0.01
Buffer Gets	114,482,137	90.54	90.54
Disk Reads	2,241,885	1.77	1.77
Direct Writes	0	0.00	0.00
Rows	1,264,476	1.00	1
Fetches	0	0.00	0.00



Other Statistics
Executions that Fetched all Rows (%)	100.00
Average Persistent Mem (KB)	45.90
Average Runtime Mem (KB)	41.75
Serializable Aborts	0
Remote	No
Obsolete Yes
Child Latch Number 0

针对这个问题要说明一下,这是一条简单的insert语句 但是bind value达到了150+ 对于这种设计不可避免的出现了bind variable graduation。另外由于一条insert需要耗费14ms,此sql已经成为了这个系统的top1 sql,并且在11.2.0.3中遇到了这个bug(so sad ~)。由此可见表设计的重要性。目前在国内,为了满足业务的需求,无休止的为表添加column依然成为习惯。不过当你的系统已经接近瓶颈时候,你就会发现当初的决定是多么的愚蠢。

Bug 14127231 dbms_shared_pool.purge raised ora-6570 on obsoleted child cursors
This note gives a brief overview of bug 14127231.
The content was last updated on: 06-JUL-2012
Click here for details of each of the sections below.
Affects:
Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions >= 11.2 but BELOW 12.1
Versions confirmed as being affected => 11.2.0.3

Platforms affected Generic (all / most platforms affected)
Fixed:
This issue is fixed in ? 12.1 (Future Release)
11.2.0.4 (Future Patch Set)

Symptoms: Related To:
Error May Occur
ora-6570 PL/SQL (DBMS Packages)
dbms_shared_pool

Description
dbms_shared_pool.purge() returns ora-6570 when trying to purge an obsolete object.

Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. For questions about this bug please consult Oracle Support.
References
Bug:14127231 (This link will only work for PUBLISHED bugs)
Note:245840.1 Information on the sections in this article

————–

SQL> alter system set "_cursor_obsolete_threshold"=3 scope=spfile;

System altered.

SQL> 
SQL> startup force;



SQL> set linesize 300
SQL> select sql_text,sql_id,address,hash_value,is_obsolete  from v$sql where sql_text like '%INSERT INTO t (n, v) VALUES%';

SQL_TEXT                                                               SQL_ID        ADDRESS          HASH_VALUE I
---------------------------------------------------------------------- ------------- ---------------- ---------- -
INSERT INTO t (n, v) VALUES (:n, :v)                                   6cvmu7dwnvxwj 000000011ECD0220 2035152785 Y
INSERT INTO t (n, v) VALUES (:n, :v)                                   6cvmu7dwnvxwj 000000011ECD0220 2035152785 Y
INSERT INTO t (n, v) VALUES (:n, :v)                                   6cvmu7dwnvxwj 000000011ECD0220 2035152785 Y
INSERT INTO t (n, v) VALUES (:n, :v)                                   6cvmu7dwnvxwj 000000011ECCE110 2035152785 N

SQL> 
SQL> 

Sys:

SQL> exec dbms_shared_pool.purge('000000011ECD0220,2035152785','C');

PL/SQL procedure successfully completed.



SQL> 
SQL> select sql_text,sql_id,address,hash_value,is_obsolete  from v$sql where sql_text like '%INSERT INTO t (n, v) VALUES%';

SQL_TEXT                                                               SQL_ID        ADDRESS          HASH_VALUE I
---------------------------------------------------------------------- ------------- ---------------- ---------- -
INSERT INTO t (n, v) VALUES (:n, :v)                                   6cvmu7dwnvxwj 000000011ECCE110 2035152785 N

SQL> 
SQL> 
SQL> exit

更新:在Patch 14127231中 已经修复这一bug 这个CPU针对solaris 和 linux 11.2.0.3.x.最经也release了 AIX 64 bit的patch.

11g Oradebug Infrastructure (Internal only)

January 2, 2012 11g, Internals, oracle No comments

11g Oracle 加强了oradebug 可以使用oradebug doc命令查看internal trace command,下面简述各种命令的输出,由于是undocumented,所以很多东西有待研究


Contents
ORADEBUG DOC
ORADEBUG DOC EVENT
ORADEBUG DOC EVENT NAME
ORADEBUG DOC EVENT NAME
ORADEBUG DOC EVENT SCOPE
ORADEBUG DOC EVENT SCOPE SQL
ORADEBUG DOC EVENT FILTER
ORADEBUG DOC EVENT ACTION
ORADEBUG DOC COMPONENT


Refer to the SQL*Plus User’s Guide and Reference for more information.
[oracle@testdb ~]$ sqlplus ‘/as sysdba’

SQL*Plus: Release 11.2.0.2.0 Production on Mon Jan 2 15:04:43 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORADEBUG DOC:

SQL> oradebug doc

Internal Documentation
**********************

EVENT Help on events (syntax, event list, …)
COMPONENT [] List all components or describe

ORADEBUG DOC EVENT:

SQL> oradebug doc event

Event Help:
***********

Formal Event Syntax
——————–
::= ‘ []
[]
[]
[]
[off]’

::= []

::= [: scope_parameters]

::= {: filter_parameters}

::= (action_parameters)

<*_parameters> ::= = [, ]

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 set to ‘never’
and parameter 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 [] construct) is only
supported by specific events like the TRACE[] event

* and/or 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 [] List all events or describe
SCOPE [] List all scopes or describe
FILTER [] List all filters or describe
ACTION [] List all actions or describe

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
test_gvtf test GV$() Table Tunction

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_ossnet_trc Controls to trace IP affinity in ossnet
cellclnt_high_lat_ops Control to trace High-latency I/O operations

Events in library ADVCMP:
——————————
arch_comp_level[] arch_comp_level[]
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

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 | adaptive >,
level

SQL> ORADEBUG DOC EVENT NAME db_open_begin

db_open_begin: start of db open operation

Usage
——-
db_open_begin
delay

ORADEBUG DOC EVENT SCOPE:

SQL> oradebug doc event scope

Event scopes in library RDBMS:
——————————
SQL[] sql scope for RDBMS

ORADEBUG DOC EVENT SCOPE SQL:

SQL> oradebug doc event scope sql

SQL: sql scope for RDBMS

Usage
——-
[SQL: sql_id ]

ORADEBUG DOC EVENT FILTER

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

ORADEBUG DOC EVENT ACTION

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
HNGDET_MEM_USAGE_DUMP_NOARGS – dump hang detection memory usage
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)
KJZN_ASYNC_SYSTEM_STATE (async)
KSI_GET_TRACE – Get lmd0 traces for ksi issues
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
HNGDET_MEM_USAGE_DUMP – dump hang detection memory usage
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 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 ()
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 (ids in X$KEWRTB)
AWR_FLUSH_TABLE_OFF
– Disable flush of table id (ids in X$KEWRTB)
ASHDUMP – Dump ASH data (level = # of minutes)
ASHDUMPSECONDS – Dump ASH data (level = # of seconds)
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
dumpKGEState – Dump KGE state information for debugging

Actions in library CLIENT:
—————————
kpuActionDefault – dump OCI data
kpuActionSignalCrash
– crash and produce a core dump (if supported and possible)
kpudpaActionDpapi – DataPump dump action

ORADEBUG DOC COMPONENT

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)
dbgxtk DBGXTK xml toolkit (dbgxtk)

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_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)
MPGE MPGE (qksctx)
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)
PX_Blackbox Parallel Execution Blackbox (kxf)
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, kcrfw)
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)
KFC ASM Cache (kfc)
KFR ASM Recovery (kfr)
KFE ASM attributes (kfe)
KFDP ASM PST (kfdp)
KFG ASM diskgroups (kfg)
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
PART_Dictionary Partitioning Dictionary (kkpod)
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_trace_parser SQL trace parser (kesstp)
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)
KXDAM Exadata Disk Auto Manage (kxdam)
KCFIS Exadata Predicate Push (kcfis)

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)
OBJECTS OBJECTS
OBJECTS_DDL OBJECTS DDL (kokt)
OBJECTS_Types OBJECTS Types (kot, ko, ort)
OBJECTS_Images OBJECTS Images (koke, kot, kad)
OBJECTS_Anydata OBJECTS Anydata (kokla, kolo, kot, kad)
OBJECTS_Streams OBJECTS Streams (koxs)
OBJECTS_Dict OBJECTS Dictionary (kkdo, qcdo)
OBJECTS_Semanal OBJECTS Semantic Analysis (koks, qcso, qcto)
OBJECTS_Default OBJECTS Default

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
IPC_Layer IPC 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)

结合 oradebug doc event name trace:

SQL> oradebug doc event name trace

trace: Main event to control UTS tracing

Usage
——-
trace [ component ]
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 ,
function ,
file ,
line

example:

alter session set events ‘trace[RDBMS.SQL_Transform] [SQL: 548447mzsjars] disk=high’;