Reading Oracle wait activity from ASH, license included

What ASH is

Active Session History samples every active session once a second and keeps the samples in a circular buffer in memory, visible as V$ACTIVE_SESSION_HISTORY. One sample in ten is kept for longer in the AWR, as DBA_HIST_ACTIVE_SESS_HISTORY. Idle sessions, waiting for the client to send something, are not sampled.

That makes ASH a different kind of data from the cumulative counters in V$SYSTEM_EVENT. Counters tell you how much time went to each wait event since startup. ASH tells you, second by second, which session was doing what: the SQL ID, the wait event or CPU, the module and program, and the object it was waiting on.

The license question, first

ASH and AWR are part of the Oracle Diagnostics Pack, a management pack licensed separately for Enterprise Edition. Querying V$ACTIVE_SESSION_HISTORY or the DBA_HIST views counts as using the pack, even though the views are there without it.

The parameter CONTROL_MANAGEMENT_PACK_ACCESS switches the packs on or off. It says nothing about what you have paid for: on Enterprise Edition its default is DIAGNOSTIC+TUNING, switched on. If you have not licensed the Diagnostics Pack, set it to NONE.

So check your license before you query, and check what your monitoring tools query too. Logstag collects ASH only when that parameter includes DIAGNOSTIC, on Enterprise Edition or Oracle Database Free; otherwise it skips ASH and shows the Wait Activity tab as not licensed. One exception today: when you add or re-test a target, the agent's connection test checks once whether the ASH view is readable, whatever the setting.

Count samples, don't sum wait times

Each row is one active session at one second. Counting rows over a period gives you database time in seconds: 600 samples over 10 minutes means that, on average, one session was active the whole time. Divide samples by elapsed seconds and you have average active sessions, the single best number for how busy the database was.

The TIME_WAITED column is tempting and misleading. It is filled in only on the last sample of a wait, once the wait has ended; the earlier samples of the same long wait carry zero, and a wait still in progress has no total yet. Summing it overweights the waits that happened to finish and misses the ones that are still hurting. Count samples, and treat a sum of completed waits as a footnote. That is how Logstag's Wait Activity tab is laid out: activity from sample counts first, the completed-wait sum as a secondary figure.

Three queries that answer most questions

Where the last 15 minutes went, CPU or which wait class. Samples on CPU have no wait class:

SELECT NVL(wait_class, 'CPU') AS activity, COUNT(*) AS samples
FROM v$active_session_history
WHERE sample_time > SYSDATE - INTERVAL '15' MINUTE
GROUP BY NVL(wait_class, 'CPU')
ORDER BY samples DESC;

The SQL behind it, with each statement's share of the activity:

SELECT sql_id, COUNT(*) AS samples,
       ROUND(100 * RATIO_TO_REPORT(COUNT(*)) OVER (), 1) AS pct
FROM v$active_session_history
WHERE sample_time > SYSDATE - INTERVAL '15' MINUTE
  AND sql_id IS NOT NULL
GROUP BY sql_id
ORDER BY samples DESC
FETCH FIRST 10 ROWS ONLY;

What one statement was waiting on:

SELECT NVL(event, 'ON CPU') AS event, COUNT(*) AS samples
FROM v$active_session_history
WHERE sql_id = :sql_id
  AND sample_time > SYSDATE - INTERVAL '15' MINUTE
GROUP BY NVL(event, 'ON CPU')
ORDER BY samples DESC;

Without the pack

The cumulative views need no extra license. V$SYSTEM_EVENT gives waits by event (take deltas, as with any counter), V$SESSION shows what each session is doing right now, including its current wait, SQL ID and blocking session, and V$SQLAREA has statistics per statement. Statspack, the older and free alternative to AWR, still works. You lose the per-second history for each session, not the ability to see waits.

That is what Logstag collects where ASH is not licensed: sessions and their waits, non-idle wait events, locks with the object being locked, and SQL performance from V$SQLAREA.

Multitenant

Connected to a pluggable database, V$ACTIVE_SESSION_HISTORY shows the samples of that PDB. Logstag connects to each PDB as its own target, so each one's activity is shown on its own.