小结(一)"/>
oradebug 用法小结(一)
oradebug是oracle内部提供的工具,并且在文档中没有记录。11g中oracle的oradebug功能加强,我们可以通过 oradebug doc 命令来查看internal trace command。简单记录下,留作研究。
首先看下当前的环境和连接的用户
- > select * from v$version;
-
- BANNER
- --------------------------------------------------------------------------------
- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
- PL/SQL Release 11.2.0.4.0 - Production
- CORE 11.2.0.4.0 Production
- TNS for Linux: Version 11.2.0.4.0 - Production
- NLSRTL Version 11.2.0.4.0 - Production
-
- > sho user
- USER is "SYS"
- > oradebug doc
-
- Internal Documentation
- **********************
-
- EVENT Help on events (syntax, event list, ...)
- COMPONENT [<comp_name>] List all components or describe <comp_name>
- > oradebug doc event
-
- Event Help:
- ***********
-
- Formal Event Syntax
- --------------------
- <event_spec> ::= ' []
- []
- []
- []
- [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
- > 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
- krbabrstat_fault event to control krbabrstat fault injection
-
- 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
- cellclnt_read_outlier_limit Control to trace read I/O outliers
- cellclnt_write_outlier_limit Control to trace write I/O outliers
- cellclnt_lgwrite_outlier_limit Control to trace log write I/O outliers
-
- 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 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 <ub4>
-
-
- > oradebug doc event name db_open_begin
-
- db_open_begin: start of db open operation
-
- Usage
- -------
- db_open_begin
- delay <ub4>
- > 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 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)
- MPGE MPGE (qksctx)
- ADS ADS (kkoads)
- 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)
- RAC_BCAST Enqueue Broadcast Operations
- RAC_LT RAC Latch Usage
- 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)
- KFDS ASM staleness registry and resync (kfds)
- KFDX ASM Exadata interface (kfdx)
- DML DML Drivers (ins, del, upd)
- Health_Monitor Health Monitor
- DRA Data Repair Advisor
- DIRACC Direct access to fixed tables (kqfd)
- 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)
- NSMTIO Trace Non Smart I/O (nsmtio)
- KXDRS Exadata Resilvering Layer (kxdrs)
- KXDOFL Exadata Offload (kxdofl)
- KXDMISC Exadata Misc (kxdmisc)
- DV Database Vault (kzv)
- ASO Advanced Security Option
- RADM Real-time Application-controlled Data Masking (kzradm)
- SVRMAN Server Manageability (ke)
- ASH Active Session History (kewa)
- AWR Automaitc Workload Repository (kew)
- METRICS AWR metrics (kewm)
- AUTOTASK Automated Maintenance Tasks (ket)
- MMON MMON/MMNL Infrastructure (keb)
- RAT Real Application Testing (kec)
- RAT_MASK Real Application Testing: Masking (kesm, kecprm)
-
- 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)
-
- Components in library PLSQL:
- --------------------------
- PLSQL_Apps PL/SQL Apps (di, pi, plitblm, scm, std, textio, wpiutil)
- PLSQL_Codegen PL/SQL Codegen
- PLSQL_COG_IDL_Gen PL/SQL Codegen IDL Gen (pdw)
- PLSQL_COG_Infrastructure PL/SQL Codegen Infrastructure (pdz)
- PLSQL_COG_Native PL/SQL Codegen Native (pdn)
- PLSQL_COG_Optimizer PL/SQL Codegen Optimizer (pdx)
- PLSQL_COG_MCode_Gen PL/SQL Codegen MCode Gen (pdy)
- PLSQL_Code_Execution PL/SQL Code Execution (pb, pd, pe, pf, plst, pri)
- PLSQL_External_Proc PL/SQL External Proc (pef, ociextp)
- PLSQL_IDL PL/SQL IDL (pdt, pt)
- PLSQL_ILMS PL/SQL ILMS (pgm)
- PLSQL_KNLDE PL/SQL KNLDE (pbbicd, pbp3g, pbs, pbt3g, peibp)
- PLSQL_KG_Interface PL/SQL KG Interface (bam, hep, hsh, lis, par, phdr, pk)
- PLSQL_Infrastructure PL/SQL Infrastructure (pci, pcm, ph, pl, pncutil, pp, ps, pu, tre)
- PLSQL_PSD PL/SQL PSD
- PLSQL_PSD_Generic PL/SQL PSD Generic (psd, pso, psu)
- PLSQL_PSD_Standalones PL/SQL PSD Standalones (p2c, pls, psx)
- PLSQL_Semantics PL/SQL Semantics (ph2, pha, phn)
- PLSQL_Syntax PL/SQL Syntax (ph1)
来自 “ ITPUB博客 ” ,链接:/,如需转载,请注明出处,否则将追究法律责任。
转载于:/
更多推荐
oradebug 用法小结(一)
发布评论