Oracle internals, debugging and undocumented features

Oracle diagnostic events Part 2

This is my second post on Oracle events only but with more details.Every Oracle release has new stuff so I decided best way would be to dig version 11g… So here is what I got :

Let’s start with event syntax  what is most important part of all:

    <event_spec>   ::= ‘<event_id> [<event_scope>]
    <event_id>     ::= <event_name | number>[<target_parameters>]
    <event_scope>  ::= [<scope_name>: scope_parameters]
    <event_filter> ::= {<filter_name>: filter_parameters}
    <action>       ::= <action_name>(action_parameters)
    <*_parameters> ::= <parameter_name> = <value>[, ]

As you can see  plenty of  different options to play with.

I got a chance to try  few of them .Just imagine how many combinations you can make..

Some examples:

 Most famous event 10046  (SQL_TRACE ) :

turn on: alter session set events ‘sql_trace’;  ( level 1)

turn off :    alter session set events ‘sql_trace off’;

and  ones that I have never used it before:

alter session set events ‘sql_trace wait=true, plan_stat=never’;

Next event will set SQL_MONITOR component  at level high to get high resolution time for each trace:

alter session set events ‘trace[sql_mon] memory=high,get_time=highres’;

and  with all components  (.*)

alter session set events ‘trace[sql_mon.*] memory=high,get_time=highres’;

sql_mon plus sql_optimizer and sql_id   ( use ‘|’ charachter to select multiple scopes, filter or targets ):

alter system set events ‘trace[sql_mon | sql_optimizer.*] [sql: ,<sql_id>]’;

Immediate events:

alter session set events ‘immediate eventdump(process)’;

alter session set events ‘immediate eventdump(system)’;

Labeled events :

( on incident with specific error in this case ):

alter session set events ‘<ora_error_number>  incident(myincident)’;

with process id:

alter session set events ‘942  {process: <process_id>}  incident(table_missing)’;

Events are big help  for troubleshooting and understanding how Oracle works..



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: