Oracle workload analysis

Right-size your Oracle estate.

Capture AWR reports, sizing data, and compression detail across every instance. We turn it into a normalized view of performance, capacity, and migration readiness — so you can plan refreshes, rightsizing, and platform moves with real numbers.

DBA support

Required — the scripts read AWR and database metadata views

30 days minimum

A full business cycle of hourly snapshots, per node

All Oracle estates

RAC, single-instance, Exadata. AWR licensing required.

What to gather

Six steps. The first four apply to every Oracle estate. Step five (encryption inventory) is recommended whenever TDE is in scope, and step six is for Exadata customers using Hybrid Columnar Compression. Toolkit v1.7.

AWR snapshots, hourly, every node

Have your DBA enable AWR snapshots at 1-hour intervals or less on each node of every instance. For diagnostic-quality data, 15-minute snaps are best.

Run on every node of a RAC cluster — not just one.
Cover at least one full business cycle — 30 days of hourly snaps yields ~720 reports per node.
Don't merge snapshots into one giant report. Separate hourly reports give us the resolution we need.

Generate AWR reports in bulk with `AWR-Generator.sql`

This script (a slightly modified version of flashdba's open-source generator) creates a second SQL file that, when run, produces an AWR HTML report for every snapshot in your chosen window.

From sqlplus on each node:

@AWR-Generator.sql
-- Then enter the number of days (≥ 30) and the begin/end snap IDs.
-- It writes awr-generate.sql.

@awr-generate.sql
-- Produces awr-1-NN_NN.html files, one per snapshot.

Save the resulting HTML files into a folder named after the database instance.

AWR-Generator.sql View source on GitHub

Database sizing & FRA usage

AWR reports don't capture capacity, so we need a separate sizing pass per database. oracle-sizing.sql totals data + temp + redo + controlfile, then dumps Fast Recovery Area usage. Output goes to screen and to db_capacity_fra_report.csv.

SELECT
      (SELECT SUM(bytes)              /1024/1024/1024 FROM dba_data_files)
    + (SELECT NVL(SUM(bytes),0)       /1024/1024/1024 FROM dba_temp_files)
    + (SELECT SUM(bytes)              /1024/1024/1024 FROM v$log)
    + (SELECT SUM(block_size*file_size_blks)/1024/1024/1024 FROM v$controlfile)
    AS size_gb
FROM dual;

SELECT name,
       space_limit       /1024/1024/1024 AS quota_gb,
       space_used        /1024/1024/1024 AS used_gb,
       space_reclaimable /1024/1024/1024 AS reclaimable_gb
  FROM v$recovery_file_dest;

If you use Flashback, also capture its footprint with the small companion script:

SELECT estimated_flashback_size FROM v$flashback_database_log;

oracle-sizing.sql flashback_estimate.sql

ASM disk group capacity

If your databases sit on ASM, capture each disk group's total / free / used MB plus its redundancy type (NORMAL, HIGH, EXTERN). Connect to the ASM instance and run:

sqlplus / as sysasm
SQL> @asm_diskgroup_capacity.sql

Writes asm_diskgroup_capacity.csv. Run once per ASM instance — in a RAC cluster the disk groups are shared, so you don't need to repeat per node.

asm_diskgroup_capacity.sql

Encryption inventory Recommended for TDE

Inventories every user segment by encryption status, algorithm, and segment type. Helpful for sizing TDE migrations and validating that sensitive tablespaces stay encrypted on the new platform. Writes encryption_capacity_report.csv.

SELECT NVL(et.encryptedts, 'NO')   AS is_encrypted,
       NVL(et.encryptionalg, 'NONE') AS algorithm,
       s.segment_type,
       COUNT(*)                      AS object_count,
       ROUND(SUM(s.bytes)/1024/1024, 2) AS total_mb
  FROM dba_segments s
  JOIN v$tablespace vt
    ON s.tablespace_name = vt.name
  LEFT JOIN v$encrypted_tablespaces et
    ON vt.ts# = et.ts#
 WHERE s.owner NOT IN ('SYS','SYSTEM','MDSYS','WMSYS','XDB','DBSNMP','OUTLN')
 GROUP BY et.encryptedts, et.encryptionalg, s.segment_type
 ORDER BY NVL(et.encryptedts,'NO') DESC, SUM(s.bytes) DESC;

encryption_detail.sql

Compression details Exadata HCC only

If you're running Exadata with Hybrid Columnar Compression, this script dumps a CSV of every compressed table and partition with its compression state and size — helpful for projecting de-compressed footprint when moving to a non-Exadata platform. Writes compressed_data_capacity.csv.

SELECT t.owner, t.table_name, 'TABLE' AS type,
       t.compress_for AS compression_type,
       ROUND(SUM(s.bytes)/1024/1024, 2) AS size_mb
  FROM dba_tables t
  JOIN dba_segments s
    ON t.owner = s.owner
   AND t.table_name = s.segment_name
 WHERE t.compression = 'ENABLED'
   AND t.partitioned = 'NO'
   AND t.owner NOT IN ('SYS','SYSTEM','MDSYS','WMSYS')
 GROUP BY t.owner, t.table_name, t.compress_for;
-- Plus the same shape for partitioned tables (dba_tab_partitions).

Run as a user with read access to dba_tables, dba_tab_partitions, and dba_segments.

compression_details.sql

Bundling for submission

Once each node is captured:

Place each instance's AWR HTML reports in a folder named after the instance.
Add the sizing CSV (db_capacity_fra_report.csv), the flashback estimate, and (if you ran them) encryption_capacity_report.csv and compressed_data_capacity.csv.
Add asm_diskgroup_capacity.csv at the cluster level (one per ASM instance, not per database).
If you're on Exadata, note your Exadata version (X5/X6/X7/X8…), storage cell version, and HC vs EF.
Zip each instance directory separately, then bundle them into one upload.

Submit through the secure upload portal

The upload portal accepts .zip bundles up to 100 GB and verifies your license key before generating a secure upload URL. Resumable, integrity-checked, and routed straight to our analysis queue.

Open the upload portal

Questions about the analysis or the scripts? support@wooldata.com