Note – this post is still not finished, so be prepared for a little mess ..
Over time oradebug became an extremely powerful tool, yet there is no official documentation available apart of contextual help (sourced from oradebug doc).
Good news, there are several good blogs mitigating that deficiency (see the list at the end of this post).
This post is an attempt to present in the structured way of achieving the understanding of building the syntax based on the example which I have used on my way to solve a problem described here.
The example which is our reference looks like this :
alter system set events ‘trace[rdbms.nsmtio][sql:9ur4ytc0n99wr] {occurence:start_after 0, end_after 1000}’ ;
Treat that command above as an abstract. If you’re interested which technical rationale is behind and what inspired me to write this post, take a look here.
Admittedly, I’ve been always struggling before I managed to work out the correct syntax for the oradebug. Maybe it’s time to document the path along the way.
Technically, I was in the need to construct the syntax of a command which allowed me to intercept an execution of a specific sql_id, produce the corresponding trace (the traced component was supposed to be NSMTIO) and examine the content of that trace file in order to go through its beginning only, so I also had to limit its content.
If it helps, getting around oradebug resembles the way RMAN doc.text interface has been designed. You’ll see that you’ll be able to recursively dive deeper and deeper along the path of a certain topic, then possibly come back to the surface, further exploring another (sub)topic starting from that level.
oradebug’s resemblance to searching through RMAN documentation is purely my impression and I hope I haven’t introduced any mess by that.
Let’s start form the “root”. This is how you invoke the starting point :
SQL > oradebug doc
Internal Documentation
**********************
EVENT Help on events (syntax, event list, ...)
COMPONENT [<comp_name>] List all components or describe <comp_name>
Clearly, we have two paths to explore – EVENT and COMPONENT
For the start, let’s dive into the branch EVENT :
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)
<action_parameters> ::= <parameter_name> = [<value>|<action>][, ]
<*_parameters> ::= <parameter_name> = <value>[, ]
Examples and Notes section
(…)
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>
At the end of this output related to EVENT we have a good hint how to build our command using the contextual help:
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>
Now, how to read it? – Pay attention to the two grey windows, I will combine them.
1.
SQL> alter system/session set events ‘do something... set an EVENT, perhaps trace?‘
So, we can do something , e.g. trace, but don’t get limit just to “trace” – in general I tend to think that this section should be represented by a verb.
Technically, for now you’ll set an event with the intention to do something with a (corresponding) component or error number (errno), etc. with the predefined scope (if applicable), then possibly apply the filtering criteria, eventually associate an action which is supposed to fire.
For the start try :
SQL> oradebug doc EVENT NAME
(rather long output)
You will notice several libraries listed, e.g. DIAG or RDBMS containing events which belong to the given library. What comes at handy, there is a short description of each event.
To get a feeling that you’ll have the possibility to dive deeper recursively, try for example:
SQL> oradebug doc EVENT NAME kg_event
or
SQL> oradebug doc EVENT NAME trace
Both, kg_event and trace (arguably, the most popular) are the predefined names of the events in the GENERIC and DIAG libraries, respectively.
You can also “step up a level higher” and list all events belonging to a library (e.g. RDBMS library):
SQL> oradebug doc EVENT NAME RDBMS
So, for now these events are invoked in the first section of your command.
2.
So, once you decided which event to invoke
We can trace a component (this is the COMPONENT)
Good. Now we need to come back to NAME.
if you delve into any of these items, you’ll get the contextual help.
Broadly speaking
Note, the list for NAME and ACTION is rather long which gives the notion how versatile this interface is.
SQL> oradebug doc event NAME
SQL> oradebug doc event SCOPE
SQL> oradebug doc event FILTER
SQL> oradebug doc event ACTION…deeper into the section of “event” :
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
dbg[] Event to hook dbgtDbg logging statements
trace_suppress[] Event for suppressing Trace
ok, so as an event specification we can choose “trace”.
Now, what can we trace ?
Let’s see :
..so deeper into the “event” :
SQL> oradebug doc event NAME TRACE
trace: Main event to control UTS tracing
Usage
-------
trace [ component <string>[0] ]
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>[32],
function <string>[32],
file <string>[32],
line <ub4>,
conuid <ub4>Exhausted “EVENT” path for TRACE. We see that we can trace a “component”.
Let’s delve into the “component” :
SQL> oradebug doc component
Components in library RDBMS:
--------------------------
SQL_Compiler SQL Compiler ((null))
SQL_Parser SQL Parser (qcs)
SQL_Semantic SQL Semantic Analysis (kkm)
SQL_Optimizer SQL Optimizer ((null))(…)
(…)
Then somewhere underneath the RDBMS (you’ll need to skip many indentations) you’ll come across :
KXD Exadata specific Kernel modules (kxd)
KXDAM Exadata Disk Auto Manage (kxdam)
KCFIS Exadata Predicate Push (kcfis)
NSMTIO Trace Non Smart I/O (nsmtio)
KXDBIO Exadata Block level Intelligent Operations (kxdbio)
KXDRS Exadata Resilvering Layer (kxdrs)
KXDOFL Exadata Offload (kxdofl)
KXDMISC Exadata Misc (kxdmisc)
KXDCM Exadata Metrics Fixed Table Callbacks (kxdcm)
KXDBC Exadata Backup Compression for Backup Appliance (kxdbc)There are obviously plenty of components which can be traced.If you are not immediately sure what you might be looking for, I suggest, spool the output of “oradebug doc component” and “grep -i” after a pattern.Since I got the name of this component (NSMTIO) from other sources, I didn’t have to shoot blindly.Nonetheless, it’s worth to browse through the available components which can be traced. Needless to say, it’s a goldmine.
Ok, so for now we have:
alter system set events ‘trace[RDBMS.NSMTIO]
Now that we know what kind of component we are about to trace, let’s see our options.
If you remember, we can follow the path:
TRACE (a component) –
SCOPE (choose the scope of your tracing, e.g. produce a trace file for a given sql_id. You can combine the sql_ids into a bundle, e.g. [sql:g3yc1js3g2689|7ujay4u33g337]) –
FILTER (set the optional criteria for filtering) –
ACTION (optionally fire an action if the condition following filtering gets fulfilled).A word of caution, an ACTION can be disastrous, e.g.
SQL> oradebug doc event ACTION kill_instance
kill_instance
- killing RDBMS instance
Usage
-------
kill_instance()Now, coming back to the main syntax and let’s focus on our requirement.
Let’s explore the “SCOPE” branch :
SQL> oradebug doc event SCOPE
Event scopes in library RDBMS:
------------------------------
SQL[] sql scope for RDBMS
QUEUE[] queue scope AQ..and deeper :
SQL> oradebug doc event scope SQL
SQL: sql scope for RDBMS
Usage
-------
[SQL: sql_id <string>[20] ]So at this level we have the following syntax (we added the scope section) :
alter system set events ‘trace[RDBMS.NSMTIO][SQL:<sql_id_to_intercept>]. e.g.
alter system set events ‘trace[RDBMS.NSMTIO][SQL:25wbs5bshgkpw]
Now, we may possibly want to filter the trace.
Let’s explore the “filter” section :
SQL> oradebug doc event filter
There are several options – we will go for number of occurrences (note the error in the doc, a single “r”).
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.If the nofname option is used, then the event is fired only when the function is not on the stack
eq filter to only fire an event when a == b
ne filter to only fire an event when a != b
gt filter to only fire an event when a > b
lt filter to only fire an event when a < b
ge filter to only fire an event when a >= b
le filter to only fire an event when a <= b
anybit filter to only fire an event when (a & b) != 0
allbit filter to only fire an event when (a & b) == b
nobit filter to only fire an event when (a & b) == 0
bet filter to only fire an event when b <= a <= c
nbet filter to only fire an event when a < b or a > c
in filter to only fire an event when a is equal to any b .. p
nin filter to only fire an event when a is not equal to any b .. p
streq filter to only fire an event when string s1 = s2 (up to <len> characters)
strne filter to only fire an event when string s1 != s2 (up to <len> characters)
tag filter to only fire an event when a tag is set
Event filters in library RDBMS:
------------------------------
wait filter for specific wait parameters and wait duration
process filter to set events only for a specific process
px filter to check identity of the process for fault injection
Event filters in library GENERIC:
------------------------------
errarg filter to set error events only for a specific error argumentSo, yet again a level deeper :
SQL> oradebug doc event filter occurence
occurence: filter to implement counting for event checks
Usage
-------
{occurence: start_after <ub4>,
end_after <ub4> }alter system set events ‘trace[RDBMS.NSMTIO][SQL:<sql_id_to_intercept>]{occurence:start_after <start_number>, end_after <end_number>}’
Example is :
alter system set events ‘trace[rdbms.nsmtio][sql:25wbs5bshgkpw] {occurence:start_after 0, end_after 1000}’ ;
The command from above matches the following command pattern (it misses the “ACTION” part):
alter system set events ‘EVENT[COMPONENT][SCOPE] {FILTER}’ ;
RMAN ?
If this makes it easier, the hovering around the options of oradebug resembles the way RMAN text interface has been organized. I will
oradebug’s resemblance to travelling through RMAN documentation is purely my impression and I hope I haven’t introduced any mess by that ; -)
RMAN> list ;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found ";": expecting one of: "all, archivelog, backed, backuppiece, backupset, backup, completed, controlfilecopy, copy, datafilecopy, db_unique_name, device, expired, failure, foreign, global, guid, incarnation, like, proxy, preplugin, recoverable, restore, script, tag"
RMAN-01007: at line 1 column 6 file: standard input
RMAN> list archivelog
2>
3>
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "end-of-file": expecting one of: "all, from, high, like, low, scn, sequence, time, until, double-quoted-string, integer, single-quoted-string"
RMAN-01007: at line 3 column 1 file: standard input
RMAN>
RMAN>
RMAN> list archivelog all
2>
3>
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "end-of-file": expecting one of: "backed, completed, device, for, guid, like, recoverable, tag, ;"
RMAN> list archivelog all ;
using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_name EXDBNAME
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - -------------------
159671 1 43782 A 10.09.2024 09:05:50
Name: +REC=/EXDBNAME/ARCHIVELOG/2024_09_10/thread_1_seq_43782.5617.1179307251
159673 1 43783 A 10.09.2024 09:20:51
Name: +RECO/EXDBNAME/ARCHIVELOG/2024_09_10/thread_1_seq_43783.9875.1179308125
159679 1 43784 A 10.09.2024 09:35:25
Name: +RECO/EXDBNAME/ARCHIVELOG/2024_09_10/thread_1_seq_43784.4286.1179309027
Leave a Reply