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