CREATE OR REPLACE PROCEDURE stats_summary (
   stats_day   IN   DATE DEFAULT SYSDATE - 1
)
IS
   bid NUMBER;
   eid NUMBER;
   db_id NUMBER;
   inst_num NUMBER;
   inst_name VARCHAR2 (10);
   lhtr NUMBER;
   bfwt NUMBER;
   tran NUMBER;   -- transactions
   chng NUMBER;   -- block changes
   ucal NUMBER;   -- user calls
   urol NUMBER;
   ucom NUMBER;
   rsiz NUMBER;   -- redo size
   phyr NUMBER;   -- physical reads
   phyw NUMBER;   -- physical writes
   prse NUMBER;   -- parses
   hprs NUMBER;   -- hard parses
   recr NUMBER;
   gets NUMBER;   -- logical reads
   rlsr NUMBER;
   rent NUMBER;
   srtm NUMBER;   -- sorts memory
   srtd NUMBER;   -- sorts disk
   srtr NUMBER;
   strn NUMBER;
   call NUMBER;
   lhr NUMBER;
   sp VARCHAR2 (512);
   bc VARCHAR2 (512);
   lb VARCHAR2 (512);
   bs VARCHAR2 (512);
   twt NUMBER;
   logc NUMBER;   -- logons
   prscpu NUMBER;
   prsela NUMBER;
   tcpu NUMBER;
   exe NUMBER;   -- executes
   bspm NUMBER;
   espm NUMBER;
   bfrm NUMBER;
   efrm NUMBER;
   blog NUMBER;
   elog NUMBER;
   error_msg VARCHAR2 (80);
   insert_stmt VARCHAR2 (100);


   CURSOR stats_collect
   IS
      SELECT statistics_id, statistics_code
        FROM stats_dictionary
       WHERE disabled IS NULL;
BEGIN
   BEGIN
      SELECT d.dbid, i.instance_number, instance_name
        INTO db_id, inst_num, inst_name
        FROM v$database d, v$instance i;

      SELECT MIN (snap_id)
        INTO bid
        FROM stats$snapshot
       WHERE snap_time - TRUNC (stats_day) BETWEEN 0 AND 1 / 24;

      SELECT MIN (snap_id)
        INTO eid
        FROM stats$snapshot
       WHERE snap_time - TRUNC (stats_day + 1) BETWEEN 0 AND 1 / 24;

      SELECT NULL
        INTO error_msg
        FROM stats$snapshot b, stats$snapshot e
       WHERE b.snap_id = bid
         AND e.snap_id = eid
         AND b.dbid = db_id
         AND e.dbid = db_id
         AND b.instance_number = inst_num
         AND e.instance_number = inst_num
         AND b.startup_time = e.startup_time;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         IF    bid IS NULL
            OR eid IS NULL
         THEN
            error_msg := 'Snapshots not found for this date';
         ELSE
            error_msg := 'Snapshots chosen span an instance shutdown';
         END IF;


         raise_application_error (-20101, error_msg);
   END;

   perfstat.statspack.stat_changes (
      bid,
      eid,
      db_id,
      inst_num,   -- End of IN arguments
      lhtr,
      bfwt,
      tran,
      chng,
      ucal,
      urol,
      rsiz,
      phyr,
      phyw,
      ucom,
      prse,
      hprs,
      recr,
      gets,
      rlsr,
      rent,
      srtm,
      srtd,
      srtr,
      strn,
      lhr,
      bc,
      sp,
      lb,
      bs,
      twt,
      logc,
      prscpu,
      tcpu,
      exe,
      prsela,
      bspm,
      espm,
      bfrm,
      efrm,
      blog,
      elog
   );
   call := ucal + recr;


   FOR sc_rec IN stats_collect
   LOOP
      insert_stmt :=
         'INSERT INTO daily_stats (instance, as_of, statistics_id, value) VALUES (:1, :2, :3, ' ||
         sc_rec.statistics_code ||
         ')';
      EXECUTE IMMEDIATE insert_stmt
         USING inst_name, TRUNC (stats_day), sc_rec.statistics_id;
   END LOOP;
END;
