Monitoring Oracle Without the Diagnostics Pack

If your databases are not licensed for the Oracle Diagnostics Pack, most of what you read about Oracle performance tuning is off-limits — AWR, ASH, ADDM, and every screen in Enterprise Manager built on them. This is what you lose, what you keep, and how to build usable performance history from views that carry no licence at all.

The default that catches people out

On Enterprise Edition, control_management_pack_access defaults to DIAGNOSTIC+TUNING. Everything works. AWR snapshots are taken every hour, awrrpt.sql produces a report, ADDM has findings waiting for you.

None of that means the packs were purchased. The parameter controls whether the features are enabled, not whether you are entitled to them. Oracle sells the Diagnostics Pack and the Tuning Pack separately from the Enterprise Edition licence, and the database will happily let you use both without ever asking.

That gap is where audit findings come from. A DBA runs an AWR report because it is the obvious tool, the query is recorded, and two years later somebody is reconciling feature usage against purchase orders.

If you are not licensed, the correct setting is explicit:

-- Check what is currently permitted
SHOW PARAMETER control_management_pack_access

-- If you are NOT licensed for the packs, say so
ALTER SYSTEM SET control_management_pack_access = 'NONE' SCOPE=BOTH;

Setting it to NONE turns off the pack features rather than leaving them armed. It is the difference between "we did not use it" as an assertion and as a configuration. Do this before you need to explain yourself, not after.

Standard Edition 2 sidesteps the question entirely. The parameter's documented default is per-edition — "Enterprise Edition: DIAGNOSTIC+TUNING. All other editions: NONE" — so on SE2 the packs are off out of the box, which is why SE2 shops have been solving this problem for years and have the most practical answers.

What the Diagnostics Pack actually gates

The boundary is narrower than most people assume, and it is not "V$ views are free, DBA_ views are licensed".

• DBA_HIST_* views — with six named exceptions. The AWR repository: DBA_HIST_SYSSTAT, DBA_HIST_SYSTEM_EVENT, DBA_HIST_SQLSTAT and the rest. The Licensing Information manual carves out exactly six that can be used unlicensed: DBA_HIST_SNAPSHOT, DBA_HIST_DATABASE_INSTANCE, DBA_HIST_SNAP_ERROR, DBA_HIST_SEG_STAT, DBA_HIST_SEG_STAT_OBJ and DBA_HIST_UNDOSTAT. Worth knowing before you assume the whole prefix is off-limits — segment statistics and undo history in particular are more useful than their reputation suggests.
• V$ACTIVE_SESSION_HISTORY and DBA_HIST_ACTIVE_SESS_HISTORY. This is the one that surprises people. Oracle's wording is unambiguous: "The V$ACTIVE_SESSION_HISTORY dynamic performance view and its underlying table, X$ASH, are part of this pack." It is licensed even though it is a V$ view, and querying it is pack usage.
• DBMS_WORKLOAD_REPOSITORY. Creating snapshots, baselines, or AWR reports.
• ADDM — DBMS_ADDM, the DBA_ADVISOR_* views for ADDM tasks, and addmrpt.sql.
• The Performance pages in Enterprise Manager that render any of the above, including Top Activity and the ASH Analytics screens.

The Tuning Pack is separate again, and covers the SQL Tuning Advisor, SQL Access Advisor, and SQL Profiles.

What stays free

Almost all of the real-time instrumentation. These carry no pack requirement:

• V$SESSION — who is connected, what they are running, what they are waiting on right now
• V$SYSSTAT and V$SESSTAT — cumulative counters since instance startup
• V$SYSTEM_EVENT and V$SESSION_EVENT — cumulative wait totals by event
• V$SYS_TIME_MODEL — DB time and DB CPU, the two numbers that anchor any tuning conversation
• V$SQLSTATS, V$SQL, V$SQLAREA — cursor cache statistics, still cumulative
• V$SQL_PLAN — the execution plan of anything still in the cursor cache
• V$SEGMENT_STATISTICS — physical reads, buffer busy waits, and row lock waits per segment
• V$OSSTAT and V$FILESTAT — host CPU and per-datafile I/O
• V$UNDOSTAT — undo usage, with roughly four days of ten-minute buckets already retained
• V$LOCK, V$SESSION_BLOCKERS — blocking chains
• DBA_TABLESPACE_USAGE_METRICS — accurate space usage without touching AWR
• The entire static data dictionary — DBA_TABLES, DBA_INDEXES, DBA_TAB_STATISTICS, and so on

Read that list again and notice what it means: you lose history and analysis, not visibility. Everything you need to answer "what is happening right now" is free. What you lose is "what happened at 02:00 last Tuesday" and "which of these findings matters most", and those are the two things AWR and ADDM respectively provide.

Building your own history

The free views expose the same counters AWR samples — AWR's value is that it snapshots them on a schedule and stores the deltas. You can do the same thing yourself, and for many shops the result is good enough.

The mechanics matter more than the tooling:

1. Counters are cumulative since startup, so store raw and subtract

SELECT stat_name, value
  FROM v$sys_time_model
 WHERE stat_name IN ('DB time', 'DB CPU', 'sql execute elapsed time');

SELECT event, total_waits, time_waited_micro
  FROM v$system_event
 WHERE wait_class != 'Idle'
 ORDER BY time_waited_micro DESC
 FETCH FIRST 20 ROWS ONLY;

Sample these on a fixed interval and store the raw values. The delta between consecutive samples is your interval statistic.

2. Detect instance restarts, or your charts will lie

A bounce resets every counter to zero. Subtract naively and the first sample afterwards produces a large negative delta, which most charting libraries render as a cliff or silently drop.

SELECT instance_name, startup_time, status
  FROM v$instance;

Store startup_time alongside every sample. If it changed since the previous sample, start a new series rather than computing a delta. This is the single most common bug in home-grown collectors.

3. Sample V$SESSION for a poor man's ASH

Real ASH samples in-memory once per second. You will not match that from outside the database, but sampling active sessions on a short interval and aggregating by wait class recovers most of the practical value — which session, running what, waiting on what, and how often that combination appeared.

SELECT s.sid, s.username, s.sql_id, s.event, s.wait_class, s.state
  FROM v$session s
 WHERE s.status = 'ACTIVE'
   AND s.type   = 'USER'
   AND s.wait_class != 'Idle';

Sampling V$SESSION yourself is not the same as reading V$ACTIVE_SESSION_HISTORY, and the widely-held reading is that it is not pack usage. It is also a licensing position rather than a technical fact — if you are in an environment where that distinction will be tested, get it confirmed in writing rather than taking a blog's word for it, including this one.

4. Accept the gaps

An external collector misses whatever happens while it is down. Render those windows as gaps. An interpolated straight line across an outage is a lie about precisely the interval someone will later be investigating.

Statspack: still there, still free

Before AWR there was Statspack, and it never went away. It ships with the database, it carries no pack requirement, and it does the snapshot-and-delta job properly because it runs inside the instance.

-- As SYSDBA. Prompts for the PERFSTAT password and its tablespaces.
@?/rdbms/admin/spcreate.sql

-- Take a snapshot
EXEC statspack.snap;

-- Report between two snapshot ids
@?/rdbms/admin/spreport.sql

-- Purge old snapshots
@?/rdbms/admin/sppurge.sql

Check $ORACLE_HOME/rdbms/admin/ on your own release before planning around it.

The constraint people miss: Statspack has to be installed in the database being monitored. It samples that instance's own V$ views and writes to a PERFSTAT schema in that database. There is no remote mode, and no way to run one central Statspack against a fleet. One install per database, always.

What it costs you:

• A PERFSTAT schema and its tablespace, which grows with snapshot frequency and level
• A scheduled job to call statspack.snap. The bundled spauto.sql uses DBMS_JOB, deprecated since 19c — write a DBMS_SCHEDULER job instead
• A purge job. Statspack does not clean up after itself, and a forgotten install is a slow-motion space problem
• SYSDBA DDL in a production database, which in most shops means a change request

What it gives you: genuine snapshot-based history — top SQL, wait events, load profile, instance statistics — surviving restarts and needing no external agent. Weaker than AWR (no ASH, coarser SQL capture, no ADDM), far better than nothing.

One thing Statspack does not do is reduce your exposure on an unlicensed Enterprise Edition instance. AWR keeps collecting regardless of whether you read it; what changes your position is control_management_pack_access = NONE. Install Statspack for the capability, set the parameter for the licensing.

What you genuinely cannot replace

Be honest with yourself about the ceiling:

• ADDM. It is an analysis engine, not a data source. You can reproduce its inputs and not its output. Nothing free ranks findings by estimated DB time impact the way ADDM does.
• SQL Tuning Advisor. Tuning Pack. Its recommendations come from Oracle's optimizer running what-if analysis you cannot invoke another way.
• True ASH granularity. One-second in-memory sampling with no round trip. External sampling at 10-60 seconds will miss short spikes, and a five-minute interval will miss most of them.
• History before you started. Whatever you build begins collecting the day you turn it on. AWR has been quietly filling SYSAUX since the database was created. If a regression started last month, that data does not exist for you.

A practical decision path

1. Establish the licence position first, in writing. Not "the parameter is set", but whether the packs appear on a purchase order. Everything else follows from the answer.
2. If unlicensed, set control_management_pack_access = NONE and make sure every DBA and every monitoring tool knows why the AWR scripts now fail.
3. Install Statspack if you need history that survives restarts and can carry a schema, a snapshot job, and a purge job in production.
4. Collect the free views externally if you would rather not run DDL in production, or you need one view across a fleet rather than one report per database.
5. Do both where it is warranted. They answer different questions: Statspack for depth on one database, external collection for comparison across many.

The most expensive outcome is none of these — assuming the packs are available because the parameter says so, and finding out during an audit. The second most expensive is going dark because AWR was switched off and nothing replaced it. Neither is necessary. Most of the instrumentation was always free; it just needs somebody to sample it on a schedule and keep the numbers.