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_filter_list>]
[<event_parameters>]
[<action_list>]
[off]‘
<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..
