AWR Last Snapshot Finder (wr_find_last_snap.sql)

What This Script Does

This script provides AWR snapshot identification for report generation by:

Finding the last two AWR snapshots for a specific database and instance
Calculating begin and end snapshot IDs
Determining the time duration between snapshots
Setting SQL*Plus variables for use in AWR report generation
Preparing formatted date strings for reports

Script

set echo off heading on underline on;
define num_days = 0;
define report_type='html';

variable dbid       number;
variable inst_num   number;

variable begin_date varchar2(20);
variable end_date   varchar2(20);

variable begin_time varchar2(20);
variable duration   number;

variable bid        number;
variable eid        number;
--

declare
cursor c1 is
  select * from
  (select s.snap_id  snap_id, to_date(to_char(s.end_interval_time,'DD-MON-YY HH24:MI:SS'),'DD-MON-YY HH24:MI:SS') sdate
    from dba_hist_snapshot s,
         dba_hist_database_instance di
   where s.dbid              = :dbid
     and di.dbid             = :dbid
     and s.instance_number   = :inst_num
     and di.instance_number  = :inst_num
     and di.dbid             = s.dbid
     and di.instance_number  = s.instance_number
     and di.startup_time     = s.startup_time
   order by snap_id desc)
  where rownum < 3;
  --
  snap number;
  sdate date;
  bdate date;
  edate date;
BEGIN
  :dbid      :=  &dbid;
  :inst_num  :=  &inst_num;
  --
  open c1;
  LOOP
    FETCH c1 INTO snap, sdate;
    EXIT WHEN C1%NOTFOUND;
    IF C1%ROWCOUNT = 2
    THEN
      :bid := snap;
      :begin_date := to_char(sdate,'YYYY.MM.DD.HH24.MI');
      :begin_time := to_char(sdate,'MM/DD/YY HH24:MI:SS');
      bdate := sdate;
    END IF;
    IF C1%ROWCOUNT = 1
    THEN
      :eid := snap;
      :end_date := to_char(sdate,'YYYY.MM.DD.HH24.MI');
      edate := sdate;
    END IF;
  END LOOP;
  CLOSE C1;
  :duration :=  to_number(edate - bdate)*1440;
END;
/
col begin_snap new_value begin_snap format 9999999999 noprint;
col begin_time new_value begin_time noprint;
col end_snap new_value end_snap format 9999999999 noprint;
col report_name new_value report_name noprint;
col duration new_value duration noprint ;

Usage

SQL> define dbid=1234567890
SQL> define inst_num=1
SQL> @wr_find_last_snap.sql

This script is typically used as a helper for AWR report generation scripts.

Parameters

dbid: Database identifier (from V$DATABASE.DBID)
inst_num: Instance number (1 for single instance, 1-N for RAC)

Required Privileges

SELECT on DBA_HIST_SNAPSHOT
SELECT on DBA_HIST_DATABASE_INSTANCE

Output Variables Set

After execution, the following SQL*Plus variables are available:

begin_snap: Beginning snapshot ID
end_snap: Ending snapshot ID
begin_time: Begin time formatted for display
duration: Duration in minutes between snapshots
begin_date: Begin date in YYYY.MM.DD.HH24.MI format
end_date: End date in YYYY.MM.DD.HH24.MI format

Common Use Cases

AWR Report Automation
- Automatically select latest snapshot range
- Generate reports without manual snapshot selection
- Integrate into monitoring scripts
Performance Analysis
- Analyze the most recent performance period
- Compare current vs previous intervals
- Automated performance reporting
Monitoring Integration
- Include in daily monitoring scripts
- Generate automated AWR reports
- Alert on performance changes

Integration Example

-- Use with AWR report generation
@wr_find_last_snap.sql

-- Generate AWR report with found snapshots
@?/rdbms/admin/awrrpt.sql

-- Or use variables directly
SELECT * FROM table(dbms_workload_repository.awr_report_html(
  :dbid, :inst_num, :bid, :eid));

Error Handling

No Snapshots Found

Verify AWR is enabled and taking snapshots
Check if database has been restarted recently
Ensure proper DBID and instance number

Insufficient Snapshots

Script requires at least 2 snapshots
Check AWR retention settings
Verify snapshot interval configuration

Finding Database Information

-- Get current database ID
SELECT dbid FROM v$database;

-- Get instance number
SELECT instance_number FROM v$instance;

-- Check available snapshots
SELECT snap_id, begin_interval_time, end_interval_time
FROM dba_hist_snapshot
WHERE rownum <= 10
ORDER BY snap_id DESC;

Customization

Modify Snapshot Selection

-- Find snapshots from specific time period
where s.end_interval_time >= sysdate - 1  -- Last 24 hours
and s.end_interval_time <= sysdate

-- Find specific number of snapshots
where rownum < 5  -- Last 4 snapshots instead of 2

Different Time Formats

-- Modify date formatting
:begin_time := to_char(sdate,'YYYY-MM-DD HH24:MI:SS');
:duration := round(to_number(edate - bdate)*24, 2);  -- Hours instead of minutes

AWR Configuration Check

-- Check AWR settings
SELECT snap_interval, retention, dbid
FROM dba_hist_wr_control;

-- Verify snapshots are being taken
SELECT MAX(snap_id), MAX(end_interval_time)
FROM dba_hist_snapshot;

Best Practices

Error Checking
- Verify variables are set after execution
- Check that snapshots exist for the timeframe
- Validate DBID and instance number
Integration
- Use in automated reporting scripts
- Include error handling in calling scripts
- Document variable usage
Monitoring
- Regular checks that AWR is functioning
- Monitor snapshot intervals
- Verify adequate retention