Upload a deadlock trace (ORA-00060)

When two sessions wait for each other, Oracle rolls back the statement of one of them, returns ORA-00060 to it and writes the deadlock trace on its own, with no tracing turned on. WaitScope reads that file and shows who was waiting for whom, on which row and with which statement.

Before you start

• Access to the operating system of the database server, or someone who can copy the file for you.
• The approximate time of the error and the instance, to find the right line in the alert log.
• Nothing to turn on: the file was already written when the error happened. Copy it soon, because ADR purging deletes old traces.

Find the file through the alert log

1. Find the instance's trace folder:

   select value
   from v$diag_info
   where name = 'Diag Trace';

2. In that folder, search for ORA-00060 in the text alert log, the alert_<SID>.log file:

   grep -n 'ORA-00060' alert_ORCL1.log

3. Read the error line: it has the path of the trace file with the deadlock, in the same folder.
4. Copy that .trc. The .trm with the same name is not needed.

Result: a file with the DEADLOCK DETECTED ( ORA-00060 ) block, followed by the graph (Deadlock graph) and the information about the sessions involved.

Upload the file

Upload the .trc like any trace, from the browser or the Android app: WaitScope recognizes the type from the content. In History, the Total time column of the analysis shows deadlock.

A single file can have more than one deadlock, when the process received the error more than once: the report shows one card for each DEADLOCK DETECTED block.

What the report shows

A deadlock-only trace has no time profile. The report has three parts:

• Summary: how many deadlocks, Detected at with the time and the trace line, Sessions involved with the wait cycle, and the diagnosis by lock type.
• Diagnosis: one high-severity finding per deadlock, with the lock type and the sessions, and the View deadlock link (the deadlock finding).
• Deadlocks: the wait cycle, the Wait-for graph with the resource, the type and the modes of the waiting and holding sessions, the Sessions involved with the user, PDB, machine, program, module, action, row waited for and SQL of each one, and the PL/SQL stack of the session that received the error (Deadlocks section).

The sample report comes from a trace with no deadlock, so it does not have this section.

Deadlock inside a 10046 trace

If the session had 10046 tracing on when it received the error, the deadlock block goes to the same file. The report has the full profile and also:

• the Deadlocks section, the same as for a deadlock-only trace;
• the time waiting for the lock in the profile, in a wait such as enq: TX - row lock contention;
• ORA-00060 in the Errors section, with the statement that received the error (Errors section).

This shows how much of the total time the session lost waiting before the error (lock wait).

Common problems

• The alert log does not show ORA-00060. In RAC, the error may have happened in another instance: search the alert log of each one.
• The file it points to no longer exists. ADR purging deleted the trace. Next time, copy the file right after the error.
• Deadlock between RAC instances. WaitScope reads the deadlock from the DEADLOCK DETECTED block. A deadlock between instances is detected by the LMD process (Oracle background processes), and its trace, with _lmd in the name, has a different format that WaitScope does not read: the analysis of that file ends with an error, because it does not have the event 10046 lines. To measure the time lost on the lock, turn on 10046 in the sessions of the operation: the wait appears in the profile and ORA-00060 in the Errors section.

Next steps

• Read the Deadlocks section field by field.
• See causes and fixes in deadlock.
• To measure the time lost on the lock, turn on 10046 in the session (turn on tracing with DBMS_MONITOR).