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: AWR is not available there at all, 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".

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:

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:

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:

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.