🌅 ⛪️ 🧚🏿 Oracle diagnostic events - Cheat sheet 🎾 👨🏼‍🚒 🐳

Oracle diagnostic events is a very powerful tool, but unfortunately poorly documented, so I decided to list and summarize several unknown or little-known uses of it.

Its only documentation is very concise inline documentation available by command oradebug doc

in SQL * Plus, and it is not found in the official documentation. You can only find excerpts from it in different blogs, which is not very convenient, so I compiled it in full: http://orasql.org/files/events/

For modern syntax and some simple examples, see the oradebug doc event . I will not give them here and will start right away with examples.

alter system set events 
   'kg_event[1476]
        {occurence: start_after 1, end_after 3}
            trace("stack is: %\n", shortstack())
            errorstack(2)
    ';

kg_event [errno] is a Kernel Generic event from the Generic library, instructing to trigger on error numbererrno;
{occurence: start_after X, end_after Y} - this is one of the filters instructing to skip X occurrences of this event and execute Y times;
trace (format, str1, str2, ..., str15) is a function from ACTIONS for outputting its messages to the trace file;
shortstack () is a function from ACTIONS that returns call stack in short form;
errorstack(level) - ACTIONS, - (level: 0 - errorstack, 1 - errorstack + call stack, 2 - level=1 + processtate, 3 - level=2 + context area). PROCESSSTATE SYSTEMSTATE. call stack CALLSTACK(level) - level>1 .

alter system set events 
    'trace[SQL_Compiler.* | SQL_Execution.*]
        [SQL: ...]
            {process: ospid = ...}
            {occurence:end_after 3}
                controlc_signal()';

trace[component] - event, , . , SQL_Compiler SQL_Execution. , RDBMS.SQL_Compiler.SQL_Optimizer.SQL_Transform.*

.
SQL[SQL: sqlid ] - SCOPE RDBMS, , , (, sql_id PL/SQL , , - , PL/SQL .);
{process: ...} - , event .
controlc_signal - ACTION, "ORA-01013: user requested cancel of current operation", .. , .

, , - hibernate, .

alter system set events 
    'sql_trace {process: ospid = ...} 
     trace("sqlid(%): %\n", sqlid(), evfunc())
     ';

sql_trace - event 10046, event 10046, , (evfunc) sqlid (ACTION sqlid).

first we execute the query with default settings and then with _rowsource_statistics_sampfreq = 1 — , _rowsource_statistics_sampfreq=1

The difference in the trace is noticeable

, "_rowsource_statistics_sampfreq" =1 : 26 12! .

alter system set events 
    'wait_event["enq: TM - contention"]
        {wait: minwait=1000} 
            errorstack(1)
            trace("event=[%] sqlid=%, ela=% p1=% p2=% p3=%\n", 
                   evargs(5), sqlid(), evargn(1), evargn(2), evargn(3), evargn(4))
    ';

wait_event[name] - event, (wait events), v$event_name:

select wait_class,name,parameter1,parameter2,parameter3 ,display_name from v$event_name
{wait: ... } - ( ), P1, P2, P3. , "TM-contention", P2 - object #, {wait: minwait=1000; p2=12345}, .. object_id=12345 1 .
evargX() - ACTION, event-check , 1- elapsed time(ms), 2-4 - p1-p3, 5- - . kg_event: errargX.

, . , - nls- to_number, on conversion error

, - ORA-01722: invalid number:

-- ,     :
SQL> alter session set nls_numeric_characters='.,';

Session altered.

--    
SQL> select to_number('1,200.3','999g999d999') + 10 from dual;

TO_NUMBER('1,200.3','999G999D999')+10
-------------------------------------
                               1210.3

--   ,   -    :
SQL> alter session set nls_numeric_characters=q'[.']';

'Session altered.

--   :
SQL> select to_number('1,200.3','999g999d999') + 10 from dual;
select to_number('1,200.3','999g999d999') + 10 from dual
                 *
ERROR at line 1:
ORA-01722: invalid number

, v$ses_optimizer_env, MODIFIED_PARAMETERS():

alter system set events 
    'kg_event[1722] 
        {process: ospid=27556}
        {occurence:end_after 1}
            MODIFIED_PARAMETERS()';

, v$diag_alert_ext - alert.log, v$diag_trace_file_contents - -, :

select c.payload
from v$diag_trace_file_contents c
where 1=1
  and c.session_id   = ... -- sid 
  and c.serial#      = ... -- serial#
  and c.section_name = 'Error Stack' --     Error Stack
--  and c.payload like '%nls_numeric%' --   
  and c.timestamp>systimestamp-interval'15'minute;

-- :
DYNAMICALLY MODIFIED PARAMETERS:
  nls_language             = "AMERICAN"
  nls_territory            = "AMERICA"
  nls_sort                 = "BINARY"
  nls_date_language        = "AMERICAN"
  nls_date_format          = "yyyy-mm-dd hh24:mi:ss"
  nls_currency             = "$"
  nls_numeric_characters   = ".'"
  nls_iso_currency         = "AMERICA"
  nls_calendar             = "GREGORIAN"
  nls_time_format          = "hh24:mi:ssxff"
  nls_timestamp_format     = "yyyy-mm-dd hh24:mi:ssxff"
  nls_time_tz_format       = "hh24:mi:ssxff TZR"
  nls_timestamp_tz_format  = "yyyy-mm-dd hh24:mi:ssxff TZR"
  nls_dual_currency        = "$"
  nls_comp                 = "BINARY"
  local_listener           = ""

PS. Tanel Poder:

1. The full power of Oracle’s diagnostic events, part 1: Syntax for KSD debug event handling

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

3. What Caused This Wait Event: Using Oracle's wait_event[] tracing

4. http://tech.e2sn.com/oracle/troubleshooting/oradebug-doc

About Frits Hoogland's internal functions: http://orafun.info/

Full internal documentation on oradebug / diagnostic events: http://orasql.org/files/events

Oracle diagnostic events - Cheat sheet

More articles: