Yuan Yao, An Oracle ACE's Blog

When an end user experiences a slow response from an application, it can sometimes be difficult to determine whether the issue is caused by the application layer (middleware) or the database layer. This often leads to discussions (and sometimes arguments) between the application developer and the database administrator (DBA) as each tries to identify the source of the problem.

This is an dialogue in this situation:

Application Developer: Our Oracle database is slow in response!

DBA: OK, I’ll look into it.

The DBA checks the AWR (Automatic Workload Repository) report and ASH (Active Session History).

DBA: That’s odd… I don’t see anything unusual in the AWR report.

DBA: And I don’t see much activity in the ASH either.

DBA: It seems like the problem lies in the application.

Application Developer: The application is running well. I believe the database is faulty.

DBA: I can see that the application is sending requests to the database to do work.

Application Developer: Please show me concrete evidence of this.

DBA: There is no convincing record of it.

The DBA cannot provide concrete evidence of the application not sending requests to Oracle because idle sessions, marked by the “SQL*Net message from client” wait event, are not recorded in the Active Session History (ASH). Oracle excludes these idle sessions to avoid cluttering performance data, which means periods when the database is waiting for the application are not logged.

The following query ASH data to collect activities of session initiated by the application:

SELECT TO_CHAR(SAMPLE_TIME, 'HH24:MI:SS') AS SAMPLE_TIME,
       TO_CHAR(SYSDATE, 'HH24:MI:SS') AS CURRENT_TIME,
       NVL(EVENT, 'ON CPU') AS EVENT
FROM V$ACTIVE_SESSION_HISTORY
WHERE SAMPLE_TIME > SYSDATE - 10/24/60
  AND MACHINE = 'INSPUR'
  AND MODULE = 'JDBC Thin Client'
  ORDER BY SAMPLE_TIME;

The output is:

SAMPLE_TIME   CURRENT_TIME   EVENT
17:34:03      17:44:54       log file sync
17:40:04      17:44:54       ON CPU
17:43:05      17:44:54       log file sync

3 rows selected.

ASH samples active sessions every second. The gaps in the ASH samples can indicate that the application was busy with its own processing and not sending requests to the Oracle database. However, this is not concrete evidence that the application developer is asking for. To definitively determine whether application is not sending request to the database, we can enable a hidden parameter  _ash_sample_all to capture both active and idle sessions:

ALTER SYSTEM SET "_ash_sample_all" = TRUE;

After enabling this parameter, rerun the prior query to collect related session activities. An example output might look like this:

SAMPLE_TIME   CURRENT_TIME   EVENT
18:21:12      18:21:20       SQL*Net message from client
18:21:13      18:21:20       SQL*Net message from client
18:21:14      18:21:20       SQL*Net message from client
18:21:15      18:21:20       SQL*Net message from client
18:21:16      18:21:20       SQL*Net message from client
18:21:17      18:21:20       SQL*Net message from client
18:21:18      18:21:20       SQL*Net message from client
18:21:19      18:21:20       SQL*Net message from client
18:21:20      18:21:20       SQL*Net message from client

9 rows selected.

The repeated “SQL*Net message from client” events every second indicate that the database is waiting for a message from the client (i.e., the application). This confirms that during these periods, the application is not sending requests to the database and is likely busy with its own processing.

Don’t forget revert the change by resetting the hidden parameter once you collected enough data:

ALTER SYSTEM SET "_ash_sample_all" = FALSE;

Now we should investigate application performance to ensure that the application is not experiencing delays or performance bottlenecks that prevent it from sending requests to the database in a timely manner.