Oracle Performance Diagnostics Without the Tuning Pack License

The License Boundary: What You Can and Cannot Use

Before the diagnostic queries, the license landscape needs to be clear. Oracle's Diagnostic Pack license gates access to AWR (Automatic Workload Repository), ADDM (Automatic Database Diagnostic Monitor), and ASH (Active Session History) via the DBA_HIST_* views. The Tuning Pack licenses SQL Tuning Advisor and SQL Access Advisor. Both packs are included with Oracle Enterprise Edition but are separate licensed options for Standard Edition 2 and Oracle Database SE2 on EC2/RDS.

Querying DBA_HIST_* views without a Diagnostic Pack license is a license compliance violation, even if Oracle doesn't technically prevent it. This matters in audits.

What you can always use, regardless of pack license:

• All V$ dynamic performance views
• Statspack (manually installed, stores snapshots in PERFSTAT schema)
• GV$ views in RAC environments
• Direct SGA memory reads via DBMS_SHARED_POOL and related packages
• EXPLAIN PLAN (does not require any pack)
• SQL*Trace / TKPROF at the session level

Installing and Configuring Statspack

Statspack is Oracle's pre-AWR performance snapshot tool. It's been shipping with Oracle since 8i, it's included in the database installation, and it requires no additional license. It captures a point-in-time snapshot of V$ view data and stores it in the PERFSTAT schema. Running a report between two snapshots gives you an output structurally similar to an AWR report.

-- Install Statspack (run as SYS or SYSDBA)
-- Script is in $ORACLE_HOME/rdbms/admin/
@$ORACLE_HOME/rdbms/admin/spcreate.sql
-- Prompted for: PERFSTAT password, default tablespace, temp tablespace

-- Verify installation
SELECT * FROM perfstat.stats$snapshot ORDER BY snap_id DESC FETCH FIRST 5 ROWS ONLY;

-- Take a manual snapshot
EXECUTE statspack.snap;

-- Set automatic snapshot collection every 30 minutes
-- Uses DBMS_JOB (not DBMS_SCHEDULER -- Statspack predates it)
VARIABLE jobno NUMBER;
EXECUTE dbms_job.submit(:jobno,
  'statspack.snap;',
  TRUNC(SYSDATE + 1/48, 'MI'),
  'TRUNC(SYSDATE + 1/48, ''MI'')');
COMMIT;

-- Verify the job is scheduled
SELECT job, what, next_date, next_sec, interval
FROM user_jobs
WHERE what LIKE '%statspack%';

-- Take snapshots before and after a problem window, then generate report
-- Note the snap_ids
SELECT snap_id, snap_time FROM perfstat.stats$snapshot
ORDER BY snap_id DESC FETCH FIRST 10 ROWS ONLY;

-- Generate Statspack report between two snapshots
@$ORACLE_HOME/rdbms/admin/spreport.sql
-- Prompted for: begin snap_id, end snap_id, report name

V$ Views That Replace AWR for Real-Time Diagnostics

AWR's primary value is historical — it lets you analyze what happened two hours ago during a performance incident. For real-time and recent-past diagnostics, V$ views are both sufficient and always licensed. Here are the queries that replace the most commonly used AWR report sections.

Top Wait Events (replaces AWR Top 5 Timed Events)

-- Current top wait events since instance startup
-- Divide by STARTUP_TIME diff for rate if needed
SELECT
  event,
  total_waits,
  total_timeouts,
  ROUND(time_waited_micro / 1000000, 1) AS time_waited_sec,
  ROUND(time_waited_micro / NULLIF(total_waits, 0) / 1000, 3) AS avg_wait_ms,
  ROUND(100 * time_waited_micro /
    NULLIF(SUM(time_waited_micro) OVER (), 0), 1) AS pct_total_wait
FROM v$system_event
WHERE wait_class != 'Idle'
ORDER BY time_waited_micro DESC
FETCH FIRST 15 ROWS ONLY;

-- For a time-bounded view, take two snapshots manually
-- and calculate deltas, or use Statspack report sections

Top SQL by Elapsed Time (replaces AWR Top SQL section)

-- Top SQL in shared pool by elapsed time (current cursor cache)
-- This is the most useful single query for identifying problem SQL
SELECT
  sql_id,
  ROUND(elapsed_time / 1000000, 1) AS elapsed_sec,
  executions,
  ROUND(elapsed_time / NULLIF(executions, 0) / 1000000, 3) AS elapsed_per_exec_sec,
  ROUND(cpu_time / 1000000, 1) AS cpu_sec,
  buffer_gets,
  ROUND(buffer_gets / NULLIF(executions, 0), 0) AS gets_per_exec,
  disk_reads,
  rows_processed,
  ROUND(rows_processed / NULLIF(executions, 0), 0) AS rows_per_exec,
  SUBSTR(sql_text, 1, 80) AS sql_preview
FROM v$sqlstats
WHERE executions > 0
ORDER BY elapsed_time DESC
FETCH FIRST 20 ROWS ONLY;

-- Get full SQL text for a specific sql_id
SELECT sql_fulltext
FROM v$sql
WHERE sql_id = '&sql_id'
FETCH FIRST 1 ROWS ONLY;

Session Wait Activity (partial ASH replacement)

-- Currently waiting sessions -- real-time ASH equivalent
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.status,
  s.event,
  s.wait_class,
  s.seconds_in_wait,
  s.state,
  s.sql_id,
  s.blocking_session,
  s.blocking_session_status,
  SUBSTR(q.sql_text, 1, 80) AS current_sql
FROM v$session s
LEFT JOIN v$sql q ON s.sql_id = q.sql_id AND s.sql_child_number = q.child_number
WHERE s.status = 'ACTIVE'
  AND s.username IS NOT NULL
ORDER BY s.seconds_in_wait DESC;

-- Session history via V$ACTIVE_SESSION_HISTORY (1-second sampling, in-memory only)
-- NOTE: V$ASH is included with the database without pack license
-- DBA_HIST_ACTIVE_SESS_HISTORY (persistent AWR ASH) DOES require Diagnostic Pack
SELECT
  sample_time,
  session_id,
  session_serial#,
  sql_id,
  event,
  wait_class,
  session_state
FROM v$active_session_history
WHERE sample_time > SYSDATE - 1/24  -- last hour
  AND session_state = 'WAITING'
  AND wait_class != 'Idle'
ORDER BY sample_time DESC
FETCH FIRST 100 ROWS ONLY;

I/O Statistics (replaces AWR I/O Profile section)

-- Database-level I/O summary
SELECT
  metric_name,
  value,
  metric_unit
FROM v$sysmetric
WHERE metric_name IN (
  'Physical Read Total IO Requests Per Sec',
  'Physical Write Total IO Requests Per Sec',
  'Physical Read Total Bytes Per Sec',
  'Physical Write Total Bytes Per Sec',
  'Redo Generated Per Sec',
  'DB Block Gets Per Sec',
  'Consistent Gets Per Sec'
)
AND intsize_csec = (SELECT MAX(intsize_csec) FROM v$sysmetric)
ORDER BY metric_name;

-- Per-file I/O breakdown
SELECT
  f.file# ,
  f.name,
  s.phyrds,
  s.phywrts,
  s.phyblkrd,
  s.phyblkwrt,
  s.readtim,
  s.writetim,
  ROUND(s.readtim / NULLIF(s.phyrds, 0), 2) AS avg_read_ms,
  ROUND(s.writetim / NULLIF(s.phywrts, 0), 2) AS avg_write_ms
FROM v$filestat s
JOIN v$datafile f ON s.file# = f.file#
ORDER BY s.phyrds + s.phywrts DESC
FETCH FIRST 20 ROWS ONLY;

Statspack Report: Reading the Key Sections

A Statspack report covers a time interval between two snapshots. The sections most useful for performance diagnosis:

Load Profile. DB Time, Redo Size per second, Logical Reads per second, Block Changes per second. These establish whether the database is busier than baseline during the problem window — or whether the problem is latency at a given load, not load itself.

Top 5 Timed Foreground Events. Equivalent to AWR's top wait events. If db file sequential read is at the top, the problem is indexed single-block reads being slow — usually an I/O subsystem issue. If CPU time is at the top with high elapsed time, the problem is full scans or inefficient SQL consuming CPU. If log file sync is at the top, LGWR write latency is the bottleneck.

SQL Ordered by Elapsed Time. The same content as V$SQLSTATS but scoped to the snapshot interval. The SQL IDs here are your primary tuning targets.

-- After reading a Statspack report and identifying a problem SQL_ID,
-- get the execution plan from the shared pool
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', NULL, 'ALLSTATS LAST'));

-- If the cursor has aged out of the shared pool,
-- use EXPLAIN PLAN (no historical plan, but shows current optimizer decision)
EXPLAIN PLAN FOR
  SELECT /* paste problem SQL here */ ...;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SQL*Trace for Session-Level Diagnosis

When V$ views and Statspack identify a problem SQL but don't show enough detail — specifically, which bind variable values are causing the bad plan — SQL*Trace with bind capture gives you the full picture. No pack license required.

-- Enable trace for the current session with bind variable capture and waits
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
-- Level 12 = SQL + bind variables + wait events
-- Level 4 = SQL + bind variables
-- Level 8 = SQL + wait events

-- Run the problem query or workload

-- Disable trace
ALTER SESSION SET EVENTS '10046 trace name context off';

-- Find the trace file location
SELECT value FROM v$diag_info WHERE name = 'Default Trace File';

-- For another session (requires ALTER SESSION privilege or DBA)
EXEC dbms_monitor.session_trace_enable(
  session_id   => 123,
  serial_num   => 456,
  waits        => TRUE,
  binds        => TRUE
);

-- After trace collection, format with TKPROF
-- Run on the database server or copy trace file locally
-- tkprof tracefile.trc output.txt sort=exeela

The 10% You're Missing Without Pack Licenses

Being honest about what Statspack and V$ can't replace:

• Historical ASH data. V$ASH holds ~1 hour in memory. For "what was happening at 3am during last night's batch," you need either a Diagnostic Pack license (DBA_HIST_ACTIVE_SESS_HISTORY) or Statspack snapshots taken at 3am that captured the right SQL and wait data. If your Statspack snapshot interval is 30 minutes and the incident was in a 10-minute window, you may not have enough granularity.
• ADDM automated recommendations. ADDM reads AWR data and generates a ranked list of performance findings with recommended fixes. There's no Statspack equivalent that produces the same structured output. You reproduce it manually by reading the Statspack report sections and applying your own diagnostic reasoning.
• SQL Tuning Advisor. The automated index and SQL profile recommendations from SQL Tuning Advisor require the Tuning Pack. Manual analysis with EXPLAIN PLAN and V$SQL_PLAN achieves the same end result but takes more time per query.

For most environments running Oracle Standard Edition or Oracle Enterprise Edition without pack options, Statspack plus disciplined V$ querying is sufficient for the performance diagnostic work that actually happens — finding slow SQL, identifying wait bottlenecks, and tracking down runaway sessions. The pack licenses are genuinely valuable for large enterprise environments where automated recommendations save DBA time at scale. For smaller environments, the alternative tools covered here close most of the gap at zero additional licensing cost.