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!

Note that this year’s only Advanced Oracle Troubleshooting class takes place in the end of April/May 2014, so sign up now if you plan to attend this year!

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

16 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. Pingback: Log Buffer #192, A Carnival of The Vanities for DBAs | The Pythian Blog

  5. Tanel Poder says:

    Yeah, Oracle database is full of surprises! :)

  6. 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.

  7. 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…

  8. 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]‘

  9. 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…

  10. 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 !

  11. Pingback: Oracle Closed World presentation links | Tanel Poder's blog: Core IT for Geeks and Pros

  12. Pingback: oradebug extended « IT World

  13. Pingback: SQL_TRACE event in 11g « Alexander Anokhin

  14. Pingback: oradebug extended | IT World

  15. Pingback: Timing: query execution statistics (rowsource statistics). Part 1: How it works « Alexander Anokhin

  16. Pingback: http://dboptimizer.com/tools-2/ | xfang

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>