Sitemap
Open in app

Sign in

Medium Logo
Write
Search

Sign in

Scops

Scops

The most capable Oracle monitoring platform. Agentless, no Oracle Pack required, 75+ Radar detectors and Scops AI with 40+ agentic tools.

Oracle Session History Without the Diagnostics Pack

Scops
7 min readJust now

--

Press enter or click to view image in full size

Someone says the database was slow at 14:10. With ASH you would query the past. Without a Diagnostics Pack license, or on Standard Edition 2, that view is off limits, and at 16:00 V$SESSION only shows what is happening at 16:00.

The answer is to keep your own history. This article covers where the license line sits, what Statspack does and does not give you, and a working sampler with its limits.

Where the license line is

The Database Licensing Information User Manual is the reference. For 19c it lists these under the Diagnostics Pack:

  • V$ACTIVE_SESSION_HISTORY and its underlying table X$ASH
  • All DBA_HIST_ views, with six exceptions: DBA_HIST_SNAPSHOT, DBA_HIST_DATABASE_INSTANCE, DBA_HIST_SNAP_ERROR, DBA_HIST_SEG_STAT, DBA_HIST_SEG_STAT_OBJ and DBA_HIST_UNDOSTAT
  • DBMS_WORKLOAD_REPOSITORY, DBMS_ADDM, and DBMS_ADVISOR when it runs ADDM
  • Reports such as awrrpt.sql, ashrpt.sql and addmrpt.sql

The Tuning Pack, which also requires the Diagnostics Pack, covers V$SQL_MONITOR, V$SQL_PLAN_MONITOR, DBMS_SQLTUNE (with some subprograms excepted for on-premises Enterprise Edition) and DBMS_AUTO_SQLTUNE, among others.

The manual applies this to any method of access. A hand-written query against V$ACTIVE_SESSION_HISTORY counts the same as opening a report.

V$SESSION, V$SESSION_WAIT_HISTORY, V$SESSION_EVENT, V$SYSTEM_EVENT and V$SQLSTATS are not on those lists. Your agreement with Oracle has the last word, so read the manual for your release.

On Standard Edition 2 the question is simpler. The manual’s feature table marks both packs as unavailable, so nothing here can be licensed at all.

Two traps worth checking today

The default on Enterprise Edition is permissive. CONTROL_MANAGEMENT_PACK_ACCESS defaults to DIAGNOSTIC+TUNING on Enterprise Edition and to NONE on all other editions. An EE database without a pack license is therefore open to pack features until someone changes it:

SHOW PARAMETER control_management_pack_access

ALTER SYSTEM SET control_management_pack_access = 'NONE' SCOPE = BOTH;

The parameter cannot be changed from inside a PDB.

The usage view is not a full record. Look at what the database has recorded:

SELECT name, detected_usages, currently_used,
       first_usage_date, last_usage_date, last_sample_date
FROM   dba_feature_usage_statistics
WHERE  name LIKE '%Active Session History%'
   OR  name LIKE '%AWR%'
   OR  name LIKE '%Workload Repository%'
   OR  name LIKE '%ADDM%'
ORDER  BY name;

On a 21c test database this returned ADDM, AWR Baseline, AWR Baseline Template, AWR Report and Automatic Workload Repository. No row was named Active Session History. So zero usages here does not prove that nobody queried ASH. The view is also filled by periodic samples, as LAST_SAMPLE_DATE shows, so a use today does not appear today.

What Statspack gives you

Statspack ships with the database and needs no Diagnostics Pack; Oracle’s own health check readme says so. As SYS, spcreate.sql creates the PERFSTAT schema, and spreport.sql builds a report between two snapshots. sppurge.sql removes old ones. Level 5 adds SQL detail and level 6 adds execution plans. On multitenant 19c, dbi services recommends deploying it per PDB and scheduling with DBMS_SCHEDULER.

Keep Statspack for what it is: instance-wide differences between two snapshots. It tells you that row lock waits jumped between 14:00 and 15:00. It does not tell you which session waited, for how long, or behind whom. That question needs samples.

Sample V$SESSION yourself

ASH takes snapshots of active sessions once a second. Oracle defines active as on CPU or waiting for an event outside the Idle wait class. You can apply the same definition to V$SESSION.

One detail matters. When a session is not waiting, EVENT holds the event it most recently waited for, not what it is doing now. Taking EVENT as it stands makes CPU time look like the last wait. Use STATE to tell them apart.

Create the account as SYS. The grant must be direct, because a definer’s rights procedure does not see privileges from roles:

GRANT SELECT ON v_$session TO dba_mon;

Then, as dba_mon, which also needs CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE JOB and quota:

CREATE OR REPLACE VIEW sess_sample_v AS
SELECT SYSDATE AS sample_time, sid, serial#, username,
       sql_id, sql_child_number, sql_exec_id, sql_exec_start,
       CASE WHEN state = 'WAITING' THEN event ELSE 'ON CPU' END AS activity,
       CASE WHEN state = 'WAITING' THEN wait_class ELSE 'CPU' END AS activity_class,
       blocking_session, final_blocking_instance, final_blocking_session,
       module, action, program, machine, service_name, con_id
FROM   v$session
WHERE  type = 'USER'
AND    status = 'ACTIVE'
AND    (state <> 'WAITING' OR wait_class <> 'Idle')
AND    sid <> TO_NUMBER(SYS_CONTEXT('USERENV', 'SID'));

CREATE TABLE sess_hist AS SELECT * FROM sess_sample_v WHERE 1 = 0;
CREATE INDEX sess_hist_t ON sess_hist (sample_time);
CREATE TABLE sess_sample_log (sample_time DATE);

CREATE OR REPLACE PROCEDURE sample_sessions AS
  l_end DATE := SYSDATE + 59 / 1440;
BEGIN
  WHILE SYSDATE < l_end LOOP
    INSERT INTO sess_hist SELECT * FROM sess_sample_v;
    INSERT INTO sess_sample_log VALUES (SYSDATE);
    COMMIT;
    DBMS_SESSION.SLEEP(5);
  END LOOP;
END;
/

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'SESS_SAMPLER',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'SAMPLE_SESSIONS',
    repeat_interval => 'FREQ=HOURLY',
    enabled         => TRUE);
END;
/

The procedure ends every 59 minutes and the job starts it again, so a failed run costs you an hour at most. DBMS_SESSION.SLEEP arrived in 18c. On 12.2 use DBMS_LOCK.SLEEP, which needs its own grant.

Get Scops’s stories in your inbox

Join Medium for free to get updates from this writer.

Five seconds is a trade. Expect about 17,280 samples a day, so a database averaging 10 active sessions adds about 172,800 rows a day. Delete old rows with a daily job. Partitioning would make purges cheap, but it is an extra-cost option on Enterprise Edition and is unavailable on SE2.

Query it

Load per minute, at 12 samples a minute:

SELECT TRUNC(sample_time, 'MI') AS minute,
       ROUND(COUNT(*) / 12, 1)  AS avg_active
FROM   sess_hist
WHERE  sample_time >= SYSDATE - 1 / 24
GROUP  BY TRUNC(sample_time, 'MI')
ORDER  BY 1;

Top SQL and its waits in a window, with estimated seconds:

SELECT sql_id, activity, COUNT(*) * 5 AS est_seconds
FROM   sess_hist
WHERE  sample_time BETWEEN TIMESTAMP '2026-11-01 14:05:00'
                       AND TIMESTAMP '2026-11-01 14:20:00'
GROUP  BY sql_id, activity
ORDER  BY est_seconds DESC
FETCH  FIRST 20 ROWS ONLY;

Who held everybody up:

SELECT final_blocking_instance, final_blocking_session,
       COUNT(DISTINCT sid) AS blocked_sessions,
       COUNT(*) * 5 AS est_blocked_seconds
FROM   sess_hist
WHERE  sample_time >= SYSDATE - 1 / 24
AND    final_blocking_session IS NOT NULL
GROUP  BY final_blocking_instance, final_blocking_session
ORDER  BY est_blocked_seconds DESC;

What this will not do

The blocker is often missing. The most common root blocker is a session that took a lock and went idle, waiting for its client. The filter drops idle sessions, so you get its SID with no username, program or SQL. We ran this script on 21c with one idle session holding a row lock: the blocked session filled the history with enq: TX - row lock contention, and the holder had no rows at all. To keep it, also sample rows whose SID appears as a FINAL_BLOCKING_SESSION, whatever their state.

Short SQL slips through. A statement that runs in 50 ms is rarely caught at 5 seconds. Sample counts work for what holds the database, and they are poor for many fast statements. For those, snapshot V$SQLSTATS every few minutes and take differences, keeping in mind that a statement aged out of the shared pool loses its figures.

No plan in the sample. V$SESSION has no plan hash. Joining V$SQL at each sample adds load, so collect plan hash values in the same V$SQLSTATS snapshot.

Columns may not agree. Dynamic performance views are not read consistent, so one row can mix moments. Odd single samples are normal, and patterns across many samples are what to trust.

It lives in the database it watches. When the archiver is stuck, or a hang blocks new work, inserts stop exactly when you most want them. The log table tells a gap from a quiet period: no log row means no data, not no load. Read-only standbys cannot store samples locally at all. Sessions on another RAC instance need their own job, pinned with the instance_id attribute.

Background processes are excluded. TYPE = 'USER' keeps the table small but hides LGWR and DBWR. Drop that filter if you chase log or write problems.

When to stop maintaining it

The script is fine for one or two databases and a specific problem. Across a fleet, the work is in the upkeep: grants, purges, job failures, and a reporting layer that nobody has written yet.

Scops, an Oracle monitoring platform, does this sampling from outside. It connects through the listener with one account, creates no objects on the monitored database, reads active sessions every 5 seconds by default and stores them in its own repository, 15 days by default. It works on Standard Edition 2 and does not read ASH or AWR for monitoring. It is the Diagnostics Pack alternative for monitoring: session history, top SQL and plan changes on every edition, with a license switch guarding the one feature that uses the packs, and Radar Engine’s 75+ detectors and Scops AI run on that same history. The free Community edition covers 2 databases.

Sources

Originally published at https://scops.dev.

Oracle
Database
Database Administration
Monitoring
DevOps

--

--

Scops

Written by Scops

0 followers
1 following

The most capable Oracle monitoring platform. Agentless, no Oracle Pack required, 75+ Radar detectors and Scops AI with 40+ agentic tools.

Help

Status

About

Careers

Press

Blog

Store

Privacy

Rules

Terms

Text to speech