Syed Anwar Ahmed – Oracle DBA Blog

Category: Performance Tuning

Oracle Database performance tuning guides, case studies and best practices

  • Statspack in Oracle 19c: A Real-World Performance Tuning Case Study

    In a production Oracle environment running on 19c, a critical performance degradation impacted business operations during peak hours. Despite being on a modern database version, the absence of Diagnostic Pack licensing meant AWR was not available. This case study demonstrates how Statspack was implemented and leveraged to identify the root cause and deliver a ~40% performance improvement using fundamental performance analysis techniques.

    Environment Overview

    ComponentDetails
    Database VersionOracle 19c
    EditionStandard Edition (No AWR)
    ApplicationOracle E-Business Suite
    Workload TypeOLTP (High concurrent users)
    Peak Users~1200 concurrent sessions

    Problem Statement

    During peak business hours, users experienced intermittent slowness with transactions taking ~3x longer than baseline. There was no historical performance repository due to AWR unavailability. Key constraints included:

    • Diagnostic Pack not licensed — AWR disabled
    • No ADDM recommendations available
    • Production-critical issue requiring immediate resolution

    Strategy and Approach

    Even in Oracle Database 19c, we relied on core DBA principles: enable Statspack, capture snapshots at 30-minute intervals, and perform comparative analysis between peak vs non-peak workload and before vs during the degradation window.

    Implementation Steps

    Step 1: Install Statspack

    Connect as SYSDBA and run the Statspack creation script. This creates the PERFSTAT schema and all required objects:

    sqlplus / as sysdba
@?/rdbms/admin/spcreate.sql

    You will be prompted for the PERFSTAT password, default tablespace, and temporary tablespace. Choose an appropriate tablespace with sufficient space (minimum 100MB recommended for active environments).

    Step 2: Automate Snapshot Collection

    Schedule automatic snapshots every 30 minutes using the built-in Statspack job:

    -- Connect as PERFSTAT user
sqlplus perfstat/<password>

-- Take a manual snapshot
EXECUTE statspack.snap;

-- Schedule automatic snapshots (every 30 mins) using spauto.sql
@?/rdbms/admin/spauto.sql

-- Or manually schedule with DBMS_JOB
VARIABLE jobno NUMBER;
BEGIN
  DBMS_JOB.SUBMIT(:jobno,
    'statspack.snap;',
    SYSDATE,
    'SYSDATE + 30/1440');
  COMMIT;
END;
/

    Step 3: Verify Snapshots

    -- Check available snapshots
SELECT snap_id, snap_time, snap_level
FROM stats$snapshot
ORDER BY snap_time DESC;

-- Count total snapshots
SELECT COUNT(*) FROM stats$snapshot;

    Step 4: Generate Statspack Report

    Generate a report between two snapshot IDs covering the peak period:

    sqlplus perfstat/<password>
@?/rdbms/admin/spreport.sql
-- Enter begin and end snapshot IDs when prompted

    Step 5: Query Top Wait Events Directly

    SELECT event,
       total_waits,
       time_waited_micro/1000000 time_waited_secs,
       average_wait
FROM stats$system_event se,
     stats$snapshot s
WHERE s.snap_id BETWEEN &begin_snap AND &end_snap
AND se.snap_id = s.snap_id
AND event NOT LIKE 'SQL*Net%'
ORDER BY time_waited_micro DESC
FETCH FIRST 10 ROWS ONLY;

    Analysis Findings

    Key finding: log file sync dominated database time — a clear indication of commit-related contention. No high-cost SQL was identified, ruling out query inefficiency as the primary cause. The load profile showed elevated user commits per second and an increased redo generation rate.

    Root Cause

    Frequent commits inside application loops (row-by-row processing) caused high log file sync waits, redo log contention, increased I/O latency, and reduced transaction throughput.

    Resolution

    The application team introduced commit batching to significantly reduce commit frequency — modifying loop logic to commit every N rows rather than every single row. Minor redo log tuning was also performed:

    -- Check redo log switch frequency (should not exceed 4-5 per hour ideally)
SELECT TO_CHAR(first_time,'YYYY-MM-DD HH24') hour,
       COUNT(*) switches
FROM v$log_history
WHERE first_time > SYSDATE - 1
GROUP BY TO_CHAR(first_time,'YYYY-MM-DD HH24')
ORDER BY 1;

-- Check redo log sizes
SELECT group#, members, bytes/1024/1024 size_mb, status
FROM v$log
ORDER BY group#;

    Results

    MetricBeforeAfter
    Transaction TimeHigh (3x baseline)Down ~40%
    log file sync waitsVery HighMinimal
    User ExperiencePoorStable

    Purging Old Statspack Data

    Statspack data grows quickly in busy environments. Purge old snapshots regularly:

    -- Purge a range of snapshots
EXECUTE statspack.purge(
  i_begin_snap  => &oldest_snap_id,
  i_end_snap    => &newest_snap_id,
  i_snap_range  => TRUE
);

-- Or use the provided script
@?/rdbms/admin/sppurge.sql

    Key Takeaways

    • Modern DB does not mean automatic performance — even in Oracle 19c, performance issues require structured analysis
    • Wait events reveal the truth — hit ratios appeared normal, but wait events exposed the actual bottleneck
    • Application design matters — commit strategy directly impacts performance; database tuning alone cannot fix design inefficiencies
    • Statspack still matters — highly effective in modern 19c environments, especially without AWR licensing
    • Always capture snapshots during peak workload with intervals of 30 minutes or less

    Why Not AWR?

    ReasonExplanation
    LicensingDiagnostic Pack not enabled
    Cost ConstraintsAvoid additional licensing overhead
    PracticalityStatspack provided sufficient insight for this issue

    This case reflects real-world experience in Oracle E-Business Suite environments, high-concurrency OLTP systems, and performance tuning under licensing constraints. Have questions? Reach out at sdanwarahmed@gmail.com.