Oracle Database 19c AWR Report and Top SQL Generator

-- Run in SQL*Plus as a user with SELECT on DBA_HIST views. Requires the Diagnostics Pack licence.
SET LINESIZE 200 PAGESIZE 100 VERIFY OFF
COLUMN dbid NEW_VALUE dbid
COLUMN bid NEW_VALUE bid
COLUMN eid NEW_VALUE eid

SELECT dbid FROM v$database;

SELECT snap_id, begin_interval_time, end_interval_time, startup_time
FROM   dba_hist_snapshot
WHERE  dbid = &dbid
AND    instance_number = 1
AND    end_interval_time BETWEEN TO_TIMESTAMP('2026-10-20 08:00', 'YYYY-MM-DD HH24:MI')
                             AND TO_TIMESTAMP('2026-10-20 12:00', 'YYYY-MM-DD HH24:MI')
ORDER  BY snap_id;

SELECT MIN(snap_id) bid, MAX(snap_id) eid
FROM   dba_hist_snapshot
WHERE  dbid = &dbid
AND    instance_number = 1
AND    end_interval_time BETWEEN TO_TIMESTAMP('2026-10-20 08:00', 'YYYY-MM-DD HH24:MI')
                             AND TO_TIMESTAMP('2026-10-20 12:00', 'YYYY-MM-DD HH24:MI');

SET PAGESIZE 0 LINESIZE 8000 HEADING OFF FEEDBACK OFF TRIMSPOOL ON
SPOOL awr_1_&bid._&eid..html
SELECT output
FROM   TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(&dbid, 1, &bid, &eid));
SPOOL OFF
SET PAGESIZE 100 LINESIZE 200 HEADING ON FEEDBACK ON

-- Interactive alternative: @?/rdbms/admin/awrrpt.sql (RAC wide: @?/rdbms/admin/awrgrpt.sql)

WITH s AS (
  SELECT sql_id,
         SUM(executions_delta)   execs,
         SUM(elapsed_time_delta) ela_us,
         SUM(cpu_time_delta)     cpu_us,
         SUM(buffer_gets_delta)  gets,
         SUM(disk_reads_delta)   reads
  FROM   dba_hist_sqlstat
  WHERE  dbid = &dbid
  AND    instance_number = 1
  AND    snap_id > &bid
  AND    snap_id <= &eid
  GROUP  BY sql_id
  ORDER  BY SUM(elapsed_time_delta) DESC
  FETCH FIRST 10 ROWS ONLY
)
SELECT s.sql_id,
       s.execs,
       ROUND(s.ela_us / 1e6, 1)                           elapsed_s,
       ROUND(s.cpu_us / 1e6, 1)                           cpu_s,
       ROUND(s.ela_us / 1e6 / NULLIF(s.execs, 0), 3)      s_per_exec,
       s.gets,
       s.reads,
       DBMS_LOB.SUBSTR(t.sql_text, 120, 1)                sql_text
FROM   s
LEFT JOIN dba_hist_sqltext t
       ON t.dbid = &dbid AND t.sql_id = s.sql_id
ORDER  BY s.ela_us DESC;

SELECT segment_name,
       segment_type,
       tablespace_name,
       ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) size_gb
FROM   dba_segments
WHERE  owner = UPPER('CRPDTA')
GROUP  BY segment_name, segment_type, tablespace_name
ORDER  BY SUM(bytes) DESC
FETCH FIRST 10 ROWS ONLY;

-- tablespace_usedsize is in database blocks; convert with the tablespace block size.
WITH u AS (
  SELECT TRUNC(CAST(sn.end_interval_time AS DATE), 'MM') mon,
         ts.name                                           tablespace_name,
         MAX(us.tablespace_usedsize * dt.block_size)       used_bytes
  FROM   dba_hist_tbspc_space_usage us
  JOIN   dba_hist_snapshot sn
         ON sn.snap_id = us.snap_id
        AND sn.dbid = us.dbid
        AND sn.instance_number = 1
  JOIN   v$tablespace ts
         ON ts.ts# = us.tablespace_id
  JOIN   dba_tablespaces dt
         ON dt.tablespace_name = ts.name
  WHERE  us.dbid = &dbid
  GROUP  BY TRUNC(CAST(sn.end_interval_time AS DATE), 'MM'), ts.name
)
SELECT mon,
       ROUND(SUM(used_bytes) / 1024 / 1024 / 1024, 2) used_gb,
       ROUND((SUM(used_bytes) - LAG(SUM(used_bytes)) OVER (ORDER BY mon)) / 1024 / 1024 / 1024, 2) growth_gb
FROM   u
GROUP  BY mon
ORDER  BY mon;