The full power of Oracle’s diagnostic events, part 2: ORADEBUG DOC and 11g improvements

I haven’t written any blog entries for a while, so here’s a very sweet treat for low-level Oracle troubleshooters and internals geeks out there :)

Over a year ago I wrote that Oracle 11g has a completely new low-level kernel diagnostics & tracing infrastructure built in to it. I wanted to write a longer article about it with comprehensive examples and use cases, but by now I realize I won’t ever have time for this, so I’ll just point you to the right direction :)

Basically, since 11g, you can use SQL_Trace, kernel undocumented traces, various dumps and other actions at much better granularity than before.

For example, you can enable SQL_Trace for a specific SQL_ID only:

SQL> alter session set events 'sql_trace[SQL: 32cqz71gd8wy3{pgadep: exactdepth 0} {callstack: fname opiexe}
plan_stat=all_executions,wait=true,bind=true';


Session altered.

Actually I have done more in above example, I have also said that trace only when the PGA depth (the dep= in tracefile) is zero. This means that trace only top-level calls, issued directly by the client application and not recursively by some PL/SQL or by dictionary cache layer. Additionally I have added a check whether we are currently servicing opiexe function (whether the current call stack contains opiexe as a (grand)parent function) – this allows to trace & dump only in specific cases of interest!

The syntax is actually more powerful than that, in this example I’m running kernel tracing for a kernel component plus instructing Oracle to dump various other things at level 1 (callstack,process state and query block debug info) whenever a tracepoint (event) in the SQL Transformation component family is hit:

SQL> alter session set events 'trace[RDBMS.SQL_Transform[SQL: 32cqz71gd8wy3]
disk=high RDBMS.query_block_dump(1) processstate(1) callstack(1)';


Session altered.

And by now you are probably asking that where is this syntax formally documented? Google and MOS searches don’t return anything useful. Well, as with many other things, a good reference is stored within Oracle kernel itself!

Just log on as sysdba and type ORADEBUG DOC:

ORADEBUG DOC

SQL> oradebug doc
Internal Documentation
**********************
EVENT                           Help on events (syntax, event list, ...)
COMPONENT       [<comp_name>]   List all components or describe <comp_name>

This gives you the index page, now you can navigate on by running ORADEBUG DOC EVENT and take it from there. There’s lots of documentation there!

I have put the output with some comments and examples into my website too:

http://tech.e2sn.com/oracle/troubleshooting/oradebug-doc

Note that this feature is quite fresh, almost not used at all in the real (production) world, so I consider this quite experimental. I have managed to crash my session with some tests, so take the usual advice about any undocumented stuff (and oradebug) – don’t use it in production without thinking first and if you do use it, then use it at your own risk!

This entry was posted in Cool stuff, Oracle and tagged , , , . Bookmark the permalink.

9 Responses to The full power of Oracle’s diagnostic events, part 2: ORADEBUG DOC and 11g improvements

  1. Kyle Hailey says:

    cool! thanks for posting this.

  2. Yes cool indeed, although expected to be told during certain masterclasses as well – LOL – Anyway will give me some more insight in undocumented XMLDB stuff which is always welcome ;-)

  3. Kudos and props to you Tanel=

    Another great gem of a find!

    Cheers,
    Ben

  4. Tanel Poder says:

    Yeah, Oracle database is full of surprises! :)

  5. Hi, Tanel

    I got error:

    SQL> ORADEBUG DOC EVENT
    Error, unknown doc topic “EVENT”

    I’ve get this error in two 11.2.0.1 and one 11.1.0.6 db’s.
    What’s the matter ?

    SQL> ORADEBUG DOC – works normal.

  6. Tanel Poder says:

    @Yuri Pudovchenko
    It’s a bug, which I’ve seen in some versions too…

    Try this, in this order:

    1) ORADEBUG DOC EVENTX
    2) ORADEBUG DOC EVENT

    This did work for me…

  7. Thanks, Tanel

    You are right!
    After mistake in syntax – the help works well:

    SQL> ORADEBUG DOC EVENT
    Error, unknown doc topic “EVENT”
    SQL> ORADEBUG DOC EVENTS
    Error, unknown doc topic “EVENTS”
    SQL> ORADEBUG DOC EVENT

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

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

  8. Tanel Poder says:

    @Yuri Pudovchenko
    Cool! Yep I’m wondering whether this was a deliberate decision by someone at Oracle to hide the undocumented help further or just an accidental bug…

  9. Hi, Tanel, :)))

    Another question …
    I need to trace Oracle Text component.
    As I understand
    SQL> ORADEBUG DOC COMPONENT
    Components in library RDBMS:
    ————————–
    Text Text (dr)

    So I did :

    SQL> oradebug setmypid
    Statement processed.
    SQL> oradebug event ‘trace[Text]‘
    ORA-49100: Failed to process event statement ['trace[Text]‘]
    ORA-49108: Event Name [trace[Text]] not found
    SQL> oradebug event ‘trace[Text.*]‘
    ORA-49100: Failed to process event statement ['trace[Text.*]‘]
    ORA-49108: Event Name [trace[Text.*]] not found

    Where is mistake in here ?

    Another syntax works well:

    SQL> alter session set events ‘trace[Text.*]‘;

    Session altered.

    SQL> alter session set events ‘trace[Text]‘;

    Session altered.

    … select using oracle text index …

    But there is no any trace file on the disk …

    What is the reason for problem ?

    Thanks !

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>