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!
cool! thanks for posting this.
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 ;-)
Kudos and props to you Tanel=
Another great gem of a find!
Cheers,
Ben
Yeah, Oracle database is full of surprises! :)
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.
@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…
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]’
@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…
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 !