When most of the time is a wait for the application, the database was idle. The way forward is to reduce the round trips or to find the delay on the application side, not to tune SQL.
Symptoms
In the Diagnosis, the finding The session spent most of its time waiting for the application (TEMPO_NA_APLICACAO) appears, with the percentage of total time next to it. The text says that the database sat idle waiting for the next request from the client, that SQL tuning does not reduce this time, and ends with the View in profile link.
The finding appears when the SQL*Net message from client wait takes up a large part of the total time. The severity is always medium. Even when this wait is the largest component of the profile, it does not also raise the dominant component finding: this finding already covers that time.
Cause
SQL*Net message from client is the time between the end of one call and the arrival of the next request from the application. In that interval the database does no work: the time belongs to the application, the network or the user. Two patterns give the same finding and call for different solutions:
- Many short waits: the application talks to the database too much. One call per row, SQL inside a loop in the application, one query per item of a list. Each round trip pays the network latency, and the total grows with the number of calls.
- Few long waits: the application was busy with something else between calls (its own processing, a call to another service), or the user paused. It also happens when tracing was turned on well before the operation started or turned off well after it ended.
Resolution
- Tell the two patterns apart. In the
SQL*Net message from clientrow of the profile, compare Occurrences, Avg (s) and Max (s); in the latency distribution of the event, see whether the time is in the short buckets or in the long ones. - If there are many short waits, reduce the round trips: fetch rows in larger batches (many FETCH calls with few rows), send DML in batches (array DML,
addBatchandexecuteBatchin JDBC) and replace the loop of queries with a single query, with a join or with the list of values. - If the logic makes many calls in sequence, move the work into the database: a PL/SQL block or a single SQL statement does in one call what the application loop does in hundreds.
- With many short waits, also check the network distance: an application in another data center or in another cloud region pays more per round trip.
- If there are few long waits, the delay is in the application. Measure its time between calls with the application's own monitoring: the database trace does not show what it was doing.
- If the Period in the Summary starts well before or ends well after the operation, part of the wait is time from before or after it. Generate a new trace, turned on right before the start and turned off right after the end.
How to confirm
After the change, generate a new trace of the same operation, under the same conditions, and upload the new file. Compare it with the previous report:
- the Total time in the Summary dropped;
- in the
SQL*Net message from clientrow, the Occurrences dropped (fewer round trips) or the Duration (s) dropped (less time in the application); - the finding disappears when the wait no longer takes up a large part of the total time.
The percentage can stay high, and the finding can stay in the Diagnosis, if the rest of the time also dropped: what counts is the Total time. If the wait dropped and the total time did not, the time moved to another component; go back to the profile and attack the largest one.
In the report
The sample report has this finding, with 71.3% of the total time. The links open the matching part of it:
- Diagnosis: the finding and the View in profile link.
- Profile, SQL*Net message from client row: Occurrences, Avg (s) and Max (s). Click the event name to open the card with Normal when, A concern when and Where to look.
- Latency distribution of the event: many short waits or few long ones.
- Top statements by component, the event's group: the statements that waited the most for the application.
- Call types: the FETCH row, with Count and Rows.
- Summary: Period, and Database calls with the level-0 ones, which are the calls the application made.
Each section has its own page: Profile, Latency, Statements by component and Call types.