A level-0 call is a request from the application to the database: its duration is what the application waits for in one round trip. The finding counts the ones that took 0.1 s or more and leads to the list of the longest ones, from which you reach the statement that took the time.
Symptoms
In the Diagnosis, the finding n calls at level 0 above 0.100 s (CHAMADAS_LENTAS), or 1 call at level 0 above 0.100 s, appears. The text says that the list of the longest ones is in the report and ends with View longest calls.
The finding appears when the trace has at least one level-0 call (dep=0: PARSE, EXEC, FETCH and the others that the application requests) with a duration (e=) of 0.1 s or more, the limit the title shows. The title says "above", but the count includes a call of exactly 0.1 s. The severity is always info, and the finding does not show a percentage of the total time.
The count covers all level-0 calls in the trace. The Longest depth-0 calls section shows only the longest ones and may also include calls below the limit, when few exceed it.
Cause
The call took long because it did a lot of work (logical reads, disk reads), because it waited for something inside it (a lock, the disk, another database over a database link) or because it ran recursive SQL, as in a PL/SQL block. The finding does not say which: it is a record, and the cause is in the call's statement.
In batch processing, long calls are expected. On a screen, the user feels each one of them.
Resolution
- Click View longest calls.
Result: the table comes sorted by Duration (s), with the call, the statement and the trace line of each one.
- Compare Duration (s) with CPU (s). CPU close to the duration means work in the database; much lower CPU means a wait inside the call.
- Check LIO, PIO and Rows. Many logical reads for few rows point to the plan; high PIO, to disk reads.
- Click the statement (#n) to open its detail. The Statement profile shows whether the time was CPU, waits or recursive SQL; in a PL/SQL block, Recursive SQL executed by this statement lists the children.
- Use Start (+s) to see at what moment of the trace the call happened. Several slow calls at the same moment usually have an outside cause, such as a lock or a load peak.
- Use Line to read the trace at the point of the call: the WAIT lines right above it are the waits inside the call. Replace 1000 with the number in the column:
head -n 1000 arquivo.trc | tail -n 30 - Go to the page of the finding that explains the statement's time, such as operation that dominates the plan, slow reads, locking or row by row in PL/SQL.
Calls from variants of the same SQL, which differ only in literals, become a single row in the list, that of the longest call, with the N variants badge before the statement text (longest calls). The finding's count does not change: every slow call counts, including those of the shape that the list sums up in one row.
How to confirm
- Fix the cause of the statement that appears most in the list.
- Turn on tracing for the same operation, under the same conditions (turn on tracing with DBMS_MONITOR).
- Upload the new file and open the report.
- Compare the number in the finding title and the durations at the top of Longest depth-0 calls.
Result: the number of slow calls and the largest durations drop. If no call reaches 0.1 s, the finding disappears.
In the report
In the sample report, all calls stay below 0.1 s and the finding does not appear; the links open the same sections in it.
- Longest depth-0 calls: Call, Duration (s), CPU (s), Start (+s), Line, LIO, PIO, Rows and Statement (longest calls).
- Latency distribution: tables such as EXEC (call duration e) show which duration buckets the calls of each type fall into (latency).
- Summary: Database calls, with how many are at level 0 (summary).
- Statement detail: the profile and the calls of the statement that took the time (statement detail).