The trace records every error a call returns, including those that PL/SQL handled and the application never saw. The finding counts these errors and leads to the list of codes, with what each one means and where it appeared.
Symptoms
In the Diagnosis, the finding n Oracle errors in the trace (ERROS_ORACLE), or 1 Oracle error in the trace, appears. The text notes that ORA-01403 (no data found) is not counted and ends with View errors.
The finding appears when the trace has at least one ERROR or PARSE ERROR line with a code other than ORA-01403. The number is the sum of the occurrences of all codes, except 1403. The severity is always high, even with a single error, and the finding does not show a percentage of the total time: it does not measure time, it asks you to check whether the error is expected.
When the only code in the trace is ORA-01403, the finding is a different one: ORA-01403 (no data found) in the trace.
Cause
- The
ERRORline records the error returned by a call on a cursor, such as aninsertthat violated the key or afetchcanceled by the client. - The
PARSE ERRORline records SQL that did not compile (syntax, an object that does not exist, a missing privilege), with the SQL text right below it. - Errors in recursive SQL, inside PL/SQL or a trigger, and errors handled in an
exceptionblock also appear, even if the application never sees them.
Each error costs the work done up to it and the rollback of the statement. When the error reaches the application, the session also waits on SQL*Net break/reset to client, in the Application class (lock held by another session).
Resolution
- Click View errors.
Result: one card per code, with What it is, When it appears, What to do and the link to the Oracle documentation. A code outside the console catalog shows only the note Code not in the console's catalog. and the link.
- In Where it appeared, check Call, Statement, Depth, Occurrences, First seen and Line for each place of the error.
- In the Depth column, 0 means that the error went back to the application; above 0, that it came from recursive SQL. In that case, check whether the PL/SQL handles the error or hides it with
when others. - Click the statement (#n) to open its detail. For a
PARSE ERROR, the Statement column shows the text of the SQL that did not compile. - Fix it according to the cause of the code, starting with the most common ones in traces:
| Code | What it usually is |
|---|---|
| ORA-00001 | Unique key violated: a sequence behind the data, a load processed twice or two sessions inserting the same key. |
| ORA-00060 | Deadlock: see deadlock between sessions. |
| ORA-00942, ORA-00904 | A table, view or column that does not exist for the user, usually in a PARSE ERROR: a dropped object, a missing synonym or privilege, badly built SQL. |
| ORA-01013 | The client canceled the call, usually because of the driver timeout. Check the plan and the waits of the statement before raising the timeout. |
| ORA-01722 | Invalid number: implicit conversion from text to number in a filter or an insert. |
| ORA-04068 | Package state discarded: the package was recompiled while the session was using it. |
An error repeated many times in the same statement costs one execution per occurrence. The typical case is ORA-00001 in a loop that inserts and handles the exception: use merge instead of inserting and catching the error.
In Where it appeared, the place of an error in a SQL variant that differs only in literals points to the shape: the Statement column shows the number and the text of the shape, with the N variants badge, and the link opens its detail. A PARSE ERROR, which has no cursor, is still identified by the text of the SQL that failed.
How to confirm
- Fix the cause of the code that appears most.
- Turn on tracing for the same operation (turn on tracing with DBMS_MONITOR).
- Upload the new file and open the report.
- Compare the number in the finding title and the occurrences of each code in Errors.
Result: the fixed code disappears from the Errors section. The finding disappears when, at most, ORA-01403 is left in the trace.
In the report
The sample report has no errors, and the Errors section only appears when the trace has some.
- Errors: one card per code, with the meaning, the causes, the action and the places in Where it appeared (errors).
- Diagnosis: the finding and the View errors link (Diagnosis).
- Statement detail: the Errors panel, at the top, with how many errors the statement had (statement detail).
- Response time profile: the
SQL*Net break/reset to clientwait, when the errors reach the application (profile). - Deadlocks: on the ORA-00060 card, the View the deadlock link leads to the deadlock that caused the error (deadlocks).