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

Tanel Poder


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}

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:


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 full output with some comments and examples into my website too:

When using diagnostic events, 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!

  1. Check out my 2021 online training classes here!
    Linux Performance & Troubleshooting training, Advanced Oracle Troubleshooting training, Advanced Oracle SQL Tuning training. In addition to the live online classes, all attendees will receive personal downloadable video recordings too!
  2. Get weekly updates by email or follow Social/RSS