ospopen

#!/bin/bash
# ============================================================================
# Oracle StatsPack Report 2 HTML (c) 2003-2011 by IzzySoft (devel@izzysoft.de)
# ----------------------------------------------------------------------------
# $Id$
# ----------------------------------------------------------------------------
# The introductional block still contains some shell variables, so we cannot
# cat it directly to SQL*Plus. Instead we append it to our opening SQL Set.
# ============================================================================

if [ $DBVER -lt 90 ]; then
  RLIM_CURSOR=`cat $PLUGINDIR/rlim_cur_8i.pls`
else
  RLIM_CURSOR=`cat $PLUGINDIR/rlim_cur_9.pls`
fi

cat>>$SQLSET<<ENDSQL
DECLARE
  invalid_snap_range EXCEPTION;
  EXCLUDE_OWNERS VARCHAR2(255);
  MK_INSTEFF NUMBER;
  MK_RLIMS BOOLEAN;
  MK_TOPWAITS NUMBER;
  MK_ALLWAITS NUMBER;
  MK_BGWAITS NUMBER;
  MK_WAITOBJ NUMBER;
  MK_INSTACT NUMBER;
  MK_USS NUMBER;
  MK_USSTAT NUMBER;
  MK_LACT NUMBER;
  MK_LMS NUMBER;
  MK_IORA NUMBER;
  MK_SGASUM NUMBER;
  MK_SGABREAK NUMBER;
  MK_CACHSIZ NUMBER;
  MK_DC NUMBER;
  MK_LC NUMBER;
  MK_RSSTAT NUMBER;
  MK_RSSTOR NUMBER;
  MK_ENQ NUMBER;
  HAVENQ BOOLEAN;
  MK_PGAA NUMBER;
  MK_PGAM NUMBER;
  MK_SPSTAT NUMBER;
  MK_BUFFP NUMBER;
  MK_BUFFW NUMBER;
  MK_RECO NUMBER;
  MK_DFSTAT NUMBER;
  MK_TSIO NUMBER;
  MK_FIO NUMBER;
  MK_DBWR NUMBER;
  MK_LGWR NUMBER;
  MK_TOPSQL NUMBER;
  SQL_MAXLEN NUMBER;
  MK_EP NUMBER;
  MK_SEG_LR NUMBER;
  MK_SEG_PR NUMBER;
  MK_SEG_BUSY NUMBER;
  MK_SEG_LOCK NUMBER;
  MK_SEG_ITL NUMBER;
  MK_SEGSTAT NUMBER;
  MK_TABSCAN BOOLEAN;
  WR_IE_BUFFNW NUMBER;
  AR_IE_BUFFNW NUMBER;
  WR_IE_REDONW NUMBER;
  AR_IE_REDONW NUMBER;
  WR_IE_BUFFHIT NUMBER;
  AR_IE_BUFFHIT NUMBER;
  WR_IE_IMSORT NUMBER;
  AR_IE_IMSORT NUMBER;
  WR_IE_LIBHIT NUMBER;
  AR_IE_LIBHIT NUMBER;
  WR_IE_SOFTPRS NUMBER;
  AR_IE_SOFTPRS NUMBER;
  WR_IE_PRSC2E NUMBER;
  AR_IE_PRSC2E NUMBER;
  WR_IE_LAHIT NUMBER;
  AR_IE_LAHIT NUMBER;
  WR_RLIM NUMBER;
  AR_RLIM NUMBER;
  WR_RWP NUMBER;
  AR_RWP NUMBER;
  AR_EP_FTS NUMBER;
  WR_DF_CHNG NUMBER;
  AR_DF_CHNG NUMBER;
  WR_TS_BLKRD NUMBER;
  AR_TS_BLKRD NUMBER;
  WR_TS_RD NUMBER;
  AR_TS_RD NUMBER;
  WR_LC_MISS NUMBER;
  AR_LC_MISS NUMBER;
  WR_LC_RLPRQ NUMBER;
  AR_LC_RLPRQ NUMBER;
  WR_LC_INVPRQ NUMBER;
  AR_LC_INVPRQ NUMBER;
  WR_ET NUMBER;
  AR_ET NUMBER;
  START_ID NUMBER;
  END_ID NUMBER;
  DB_UPTIME NUMBER; DB_BLOCKSIZE NUMBER;
  L_LINE VARCHAR2(4000);
  R_TITLE VARCHAR(200);
  TABLE_OPEN VARCHAR(100); -- Table Attributes
  TABLE_CLOSE VARCHAR(100);
  S1 VARCHAR(200); S2 VARCHAR(200); S3 VARCHAR(200); S4 VARCHAR(200);
  I1 NUMBER; I2 NUMBER; I3 NUMBER; TDATE DATE;
  DBVER NUMBER; DBSUBVER NUMBER; OSPVER VARCHAR2(10); TOP_N_SQL NUMBER;
  TOP_N_WAITS NUMBER; TOP_N_SEGSTAT NUMBER;
  BID NUMBER; EID NUMBER; ELA NUMBER; EBGT NUMBER; EDRT NUMBER; EET NUMBER;
  EPC NUMBER; BTIME VARCHAR2(20); ETIME VARCHAR2(20);
  DB_ID NUMBER; DB_NAME VARCHAR(9); INST_NUM NUMBER; INST_NAME VARCHAR(16);
  PARA VARCHAR2(3); VERSN VARCHAR(17); HOST_NAME VARCHAR(64);
  LHTR NUMBER; BFWT NUMBER; TRAN NUMBER; CHNG NUMBER; UCAL NUMBER; UROL NUMBER;
  UCOM NUMBER; RSIZ NUMBER; PHYR NUMBER; PHYRD NUMBER; PHYRDL NUMBER;
  PHYW NUMBER; PRSE NUMBER; HPRS NUMBER; RECR NUMBER; GETS NUMBER; RLSR NUMBER;
  RENT NUMBER; SRTM NUMBER; SRTD NUMBER; SRTR NUMBER; STRN NUMBER; CALL NUMBER;
  LHR NUMBER; SP VARCHAR2(512); BC VARCHAR2(512); LB VARCHAR2(512); BS VARCHAR2(512);
  TWT NUMBER; LOGC NUMBER; PRSCPU NUMBER; PRSELA NUMBER; TCPU NUMBER; EXE NUMBER;
  BSPM NUMBER; ESPM NUMBER; BFRM NUMBER; EFRM NUMBER; BLOG NUMBER; ELOG NUMBER;
  BOCUR NUMBER; EOCUR NUMBER; DMSD NUMBER; DMFC NUMBER; DMSI NUMBER; PMRV NUMBER;
  PMPT NUMBER; NPMRV NUMBER; NPMPT NUMBER; DPMS NUMBER; DNPMS NUMBER;
  GLSG NUMBER; GLAG NUMBER; GLGT NUMBER; GLSC NUMBER; GLAC NUMBER; GLCT NUMBER;
  GLRL NUMBER; GCGE NUMBER; GCGT NUMBER; GCCV NUMBER; GCCT NUMBER;
  GCCRRV NUMBER; GCCRRT NUMBER; GCCURV NUMBER; GCCURT NUMBER; GCCRSV NUMBER;
  GCCRBT NUMBER; GCCRFT NUMBER; GCCRST NUMBER; GCCUSV NUMBER; GCCUPT NUMBER;
  GCCUFT NUMBER; GCCUST NUMBER;
  -- StatsPack ab Oracle v9.2 Start
  DBFR NUMBER; GCDFR NUMBER; MSGSQ NUMBER; MSGSQT NUMBER; MSGSQK NUMBER;
  MSGSQTK NUMBER; MSGRQ NUMBER; MSGRQT NUMBER;
  -- StatsPack ab Oracle v9.2 END
  -- StatsPack vor Oracle v9.2 Start
  DFCMS NUMBER; DFCMR NUMBER; DMRV NUMBER; DYNAL NUMBER; SCMA NUMBER; SCML NUMBER;
  PINC NUMBER; PINCRNC NUMBER; PICC NUMBER; PICRRC NUMBER; PBC NUMBER;
  PBCRC NUMBER; PCRBPI NUMBER; DYNAPRES NUMBER; DYNAPSHL NUMBER; PRCMA NUMBER;
  PRCML NUMBER; PWRM NUMBER; PFPIM NUMBER; PWNM NUMBER; DYNARES NUMBER;
  PCBA NUMBER; PCCRBA NUMBER;
  -- StatsPack vor Oracle v9.2 END
  -- StatsPack ab Oracle v10g Start
  BBC VARCHAR2(512); EBC VARCHAR2(512); BSP VARCHAR2(512); ESP VARCHAR2(512);
  BPGAALLOC NUMBER; EPGAALLOC NUMBER; BSGAALLOC NUMBER; ESGAALLOC NUMBER;
  BNPROCS NUMBER; ENPROCS NUMBER; TIMSTAT VARCHAR2(40); STATLVL VARCHAR2(20);
  BNCPU NUMBER; ENCPU NUMBER; BPMEM NUMBER; EPMEM NUMBER; BLOD NUMBER; ELOD NUMBER;
  ITIC NUMBER; BTIC NUMBER; IOTIC NUMBER; RWTIC NUMBER; UTIC NUMBER; STIC NUMBER;
  VMIB NUMBER; VMOB NUMBER; OSCPUW NUMBER; DBTIM NUMBER; DBCPU NUMBER; BGELA NUMBER;
  BGCPU NUMBER; PRSTELA NUMBER; SQLEELA NUMBER; CONMELA NUMBER; GCCRFL NUMBER;
  GCCUFL NUMBER; PHYRC NUMBER; SLR NUMBER; 
  -- StatsPack ab Oracle v10g END

  CURSOR C_MaxSnap IS
    SELECT MAX(snap_id) maxid FROM stats\$snapshot
     WHERE dbid = DB_ID AND instance_number = INST_NUM;

  CURSOR C_MaxSnapRel(id IN NUMBER) IS
    SELECT MAX(snap_id) maxid FROM stats\$snapshot
     WHERE dbid = DB_ID AND instance_number = INST_NUM
       AND snap_id < id;

  CURSOR C_MaxSnapFor IS
    SELECT MAX(snap_id) maxid FROM stats\$snapshot
     WHERE dbid = DB_ID AND instance_number = INST_NUM
       AND startup_time = (SELECT startup_time FROM stats\$snapshot
                            WHERE dbid = DB_ID AND instance_number = INST_NUM
                              AND snap_id = BID);

  CURSOR C_MinSnap IS
    SELECT MIN(snap_id) minid, MIN(startup_time) startup_time, MIN(snap_time) snap_time
      FROM stats\$snapshot
     WHERE dbid = DB_ID AND instance_number = INST_NUM
       AND startup_time = (SELECT startup_time FROM stats\$snapshot
                            WHERE dbid = DB_ID AND instance_number = INST_NUM
                              AND snap_id = EID);

  CURSOR C_MinSnapRel(id IN NUMBER) IS
    SELECT MIN(snap_id) minid FROM stats\$snapshot
     WHERE dbid = DB_ID AND instance_number = INST_NUM
       AND snap_id > id
       AND startup_time = (SELECT startup_time FROM stats\$snapshot
                            WHERE dbid = DB_ID AND instance_number = INST_NUM
                              AND snap_id = EID);

  CURSOR C_SnapBind IS
    SELECT parallel,version,host_name
      FROM stats\$database_instance di,stats\$snapshot s
     WHERE s.snap_id=BID AND s.dbid=DB_ID AND s.instance_number=INST_NUM
       AND di.dbid=s.dbid AND di.instance_number=s.instance_number
       AND di.startup_time=s.startup_time;

  CURSOR C_SnapInfo IS
    SELECT b.snap_id begin_snap_id,to_char(b.snap_time,'dd.mm.yyyy hh24:mi') begin_snap_time,
           NVL(b.ucomment,'&nbsp;') begin_snap_comment,
           e.snap_id end_snap_id,to_char(e.snap_time,'dd.mm.yyyy hh24:mi') end_snap_time,
           NVL(e.ucomment,'&nbsp;') end_snap_comment,
           to_char(round(((e.snap_time - b.snap_time) * 1440 * 60),0)/60,'9,999,990.00') elapsed,
           (e.snap_time - b.snap_time)*1440*60 ela,
           e.buffer_gets_th ebgt,
           e.disk_reads_th edrt,
           e.executions_th eet,
           e.parse_calls_th epc
      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
       AND b.snap_time < e.snap_time;

$RLIM_CURSOR

  FUNCTION strpos (str IN VARCHAR2,needle IN VARCHAR2,startpos NUMBER) RETURN NUMBER IS
    pos NUMBER; strsub VARCHAR2(255);
    BEGIN
      strsub := SUBSTR(str,1,255);
      pos    := INSTR(strsub,needle,startpos);
      return pos;
    END;

  PROCEDURE print(line IN VARCHAR2) IS
    pos NUMBER;
    BEGIN
      -- <255 char || (sqlplus > v10 && db > v10): _SQLPLUS_RELEASE: '101020000' 10g, '902000800' 9.2
      IF (LENGTH(line) < 255) OR ((substr($SQLPLUSRELEASE,1,2) < 20) AND ($DBVER > 92)) THEN
        dbms_output.put_line(line);
      ELSE
        pos := strpos(line,' ',-1);
        print(SUBSTR(line,1,pos));
        pos := pos +1;
        print(SUBSTR(line,pos));
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLERRM LIKE '%ORU-10028%' THEN
          pos := strpos(line,' ',-1);
          print(SUBSTR(line,1,pos));
          pos := pos +1;
          print(SUBSTR(line,pos));
        ELSE
          dbms_output.put_line('*!* Problem in print() *!*');
        END IF;
    END;

  FUNCTION have_rlims RETURN BOOLEAN IS
    CI NUMBER; statement VARCHAR2(500);
    BEGIN
      IF DBVER < 9 THEN
        statement := 'SELECT 0 from dual WHERE 1=1 OR 1 IN (:EID,:DB_ID,:INST_NUM)';
      ELSE
        statement := 'SELECT COUNT(resource_name) FROM stats\$resource_limit '||
                     ' WHERE snap_id = :EID AND dbid = :DB_ID'||
                     '   AND instance_number = :INST_NUM';
      END IF;
      EXECUTE IMMEDIATE statement INTO CI USING EID,DB_ID,INST_NUM;
      IF CI > 0 THEN
        RETURN TRUE;
      ELSE
        RETURN FALSE;
      END IF;
    EXCEPTION
      WHEN OTHERS THEN RETURN FALSE;
    END;

  FUNCTION have_segstats RETURN BOOLEAN IS
    CI NUMBER; statement VARCHAR2(500);
    BEGIN
      statement := 'SELECT COUNT(logical_reads) FROM stats\$seg_stat '||
                   ' WHERE snap_id BETWEEN :BID AND :EID AND dbid = :DB_ID'||
                   '   AND instance_number = :INST_NUM';
      EXECUTE IMMEDIATE statement INTO CI USING BID,EID,DB_ID,INST_NUM;
      print(statement);
      IF CI > 0 THEN
        RETURN TRUE;
      ELSE
        RETURN FALSE;
      END IF;
    EXCEPTION
      WHEN OTHERS THEN RETURN FALSE;
    END;

  PROCEDURE tab_exists(tab_name IN VARCHAR2,content IN NUMBER,is_ok OUT NUMBER) IS
    temp_count NUMBER; statement VARCHAR2(255);
    BEGIN
      statement := 'SELECT COUNT(*) FROM '||tab_name;
      EXECUTE IMMEDIATE statement INTO temp_count;
      IF NVL(content,0) = 0 THEN
        is_ok := 1;
      ELSE
        IF temp_count = 0 THEN
          is_ok := 0;
        ELSE
          is_ok := 1;
        END IF;
      END IF;
    EXCEPTION
      WHEN OTHERS THEN is_ok := 0;
    END;

  FUNCTION num_cp(small IN NUMBER, big IN NUMBER, level IN STRING) RETURN VARCHAR2 IS
    level2 VARCHAR2(100);
    BEGIN
      IF small > big THEN
        RETURN ' CLASS="'||level||'"';
      ELSE
        RETURN '';
      END IF;
    EXCEPTION
      WHEN OTHERS THEN RETURN '';
    END;

  FUNCTION alert_gt_warn(val IN NUMBER, alert IN NUMBER,warn IN NUMBER) RETURN VARCHAR2 IS
    htmlcode VARCHAR2(50);
    BEGIN
      htmlcode := num_cp(val,alert,'alert');
      IF NVL(htmlcode,'x') = 'x' THEN
        htmlcode := num_cp(val,warn,'warn');
      END IF;
      RETURN htmlcode;
    EXCEPTION
      WHEN OTHERS THEN RETURN '';
    END;

  FUNCTION alert_lt_warn(val IN NUMBER, alert IN NUMBER,warn IN NUMBER) RETURN VARCHAR2 IS
    htmlcode VARCHAR2(30);
    BEGIN
      htmlcode := num_cp(warn,val,'warn');
      IF NVL(htmlcode,'x') = 'x' THEN
        htmlcode := num_cp(alert,val,'alert');
      END IF;
      RETURN htmlcode;
    EXCEPTION
      WHEN OTHERS THEN RETURN '';
    END;

ENDSQL

cat $PLUGINDIR/formatting.pls>>$SQLSET
if [ -n "$SYSSTATFUNCS" ]; then
  cat $SYSSTATFUNCS>>$SQLSET
fi

. $GETWAITS
. $GETDF

if [ -n "$ALLWAITHEAD" ]; then
  cat $ALLWAITHEAD>>$SQLSET
fi
if [ -n "$BGWAITHEAD" ]; then
  cat $BGWAITHEAD>>$SQLSET
fi
if [ -n "$TWAITHEAD" ]; then
  cat $TWAITHEAD>>$SQLSET;
fi
if [ -n "$TSQLHEAD" ]; then
  cat $TSQLHEAD>>$SQLSET
fi
if [ -n "$TABSHEAD" ]; then
  cat $TABSHEAD>>$SQLSET
fi
if [ -n "$IOHEAD" ]; then
  cat $IOHEAD>>$SQLSET
fi
if [ -n "$DBWRHEAD" ]; then
  cat $DBWRHEAD>>$SQLSET
fi
if [ -n "$LGWRHEAD" ]; then
  cat $LGWRHEAD>>$SQLSET
fi
if [ -n "$SEGSTATHEAD" ]; then
  cat $SEGSTATHEAD>>$SQLSET;
fi
if [ -n "$INSTACTHEAD" ]; then
  cat $INSTACTHEAD>>$SQLSET
fi
if [ -n "$RECOHEAD" ]; then
  cat $RECOHEAD>>$SQLSET
fi
if [ -n "$BUFFHEAD" ]; then
  cat $BUFFHEAD>>$SQLSET
fi
if [ -n "$USSTATHEAD" ]; then
  cat $USSTATHEAD>>$SQLSET
fi
if [ -n "$RBSHEAD" ]; then
  cat $RBSHEAD>>$SQLSET
fi
if [ -n "$LACTHEAD" ]; then
  cat $LACTHEAD>>$SQLSET
  if [ -n "$LMSHEAD" ]; then
    cat $LMSHEAD>>$SQLSET
  fi
fi
if [ -n "$PGAHEAD" ]; then
  cat $PGAHEAD>>$SQLSET
fi
if [ -n "$ENQHEAD" ]; then
  cat $ENQHEAD>>$SQLSET
else
  cat>>$SQLSET<<ENDSQL
  FUNCTION have_enqs RETURN BOOLEAN IS
    BEGIN
      RETURN FALSE;
    END;

ENDSQL
fi
if [ -n "$CACHEHEAD" ]; then
  cat $CACHEHEAD>>$SQLSET
fi
if [ -n "$SGAHEAD" ]; then
  cat $SGAHEAD>>$SQLSET
fi
if [ -n "$IORAHEAD" ]; then
  cat $IORAHEAD>>$SQLSET
fi
EXCLUDE_OWN=`echo $EXCLUDE_OWNERS|sed "s/'/''/g"`

if [ $DBVER -lt 92 ]; then
  DBMS_LIMIT=1000000
else
  DBMS_LIMIT="NULL"
fi

cat>>$SQLSET<<ENDSQL
BEGIN
  -- Configuration
  OSPVER := '$version';
  EXCLUDE_OWNERS := '$EXCLUDE_OWN';
  TOP_N_SQL := $TOP_N_SQL;
  TOP_N_WAITS := $TOP_N_WAITS;
  TOP_N_SEGSTAT := $TOP_N_SEGSTAT;
  MK_INSTEFF  := :MK_INSTEFF;
  MK_TOPWAITS := :MK_TOPWAITS;
  MK_ALLWAITS := :MK_ALLWAITS;
  MK_BGWAITS  := :MK_BGWAITS;
  MK_WAITOBJ  := :MK_WAITOBJ;
  MK_INSTACT  := :MK_INSTACT;
  MK_USS      := :MK_USS;
  MK_USSTAT   := :MK_USSTAT;
  MK_LACT     := :MK_LACT;
  MK_IORA     := :MK_IORA;
  MK_SGASUM   := :MK_SGASUM;
  MK_SGABREAK := :MK_SGABREAK;
  MK_CACHSIZ  := :MK_CACHSIZ;
  MK_DC       := :MK_DC;
  MK_LC       := :MK_LC;
  MK_RSSTAT   := :MK_RSSTAT;
  MK_RSSTOR   := :MK_RSSTOR;
  MK_ENQ      := :MK_ENQ;
  MK_PGAA     := :MK_PGAA;
  MK_PGAM     := :MK_PGAM;
  MK_SPSTAT   := :MK_SPSTAT;
  MK_BUFFP    := :MK_BUFFP;
  MK_BUFFW    := :MK_BUFFW;
  MK_RECO     := :MK_RECO;
  MK_DFSTAT   := :MK_DFSTAT;
  MK_TSIO     := :MK_TSIO;
  MK_FIO      := :MK_FIO;
  MK_DBWR     := :MK_DBWR;
  MK_LGWR     := :MK_LGWR;
  MK_TOPSQL   := :MK_TOPSQL;
  SQL_MAXLEN  := :SQL_MAXLEN;
  MK_EP       := :MK_EP;
  MK_SEG_LR   := :MK_SEG_LR;
  MK_SEG_PR   := :MK_SEG_PR;
  MK_SEG_BUSY := :MK_SEG_BUSY;
  MK_SEG_LOCK := :MK_SEG_LOCK;
  MK_SEG_ITL  := :MK_SEG_ITL;
  MK_SEGSTAT  := :MK_SEGSTAT;
  IF :MK_RLIMS = 1 THEN
    MK_RLIMS := TRUE;
  END IF;
  IF $MK_TSCAN = 1 THEN MK_TABSCAN := TRUE;
    ELSE MK_TABSCAN := FALSE;
  END IF;
  WR_IE_BUFFNW := :WR_IE_BUFFNW;
  AR_IE_BUFFNW := :AR_IE_BUFFNW;
  WR_IE_REDONW := :WR_IE_REDONW;
  AR_IE_REDONW := :AR_IE_REDONW;
  WR_IE_BUFFHIT := :WR_IE_BUFFHIT;
  AR_IE_BUFFHIT := :AR_IE_BUFFHIT;
  WR_IE_IMSORT := :WR_IE_IMSORT;
  AR_IE_IMSORT := :AR_IE_IMSORT;
  WR_IE_LIBHIT := :WR_IE_LIBHIT;
  AR_IE_LIBHIT := :AR_IE_LIBHIT;
  WR_IE_SOFTPRS := :WR_IE_SOFTPRS;
  AR_IE_SOFTPRS := :AR_IE_SOFTPRS;
  WR_IE_PRSC2E := :WR_IE_PRSC2E;
  AR_IE_PRSC2E := :AR_IE_PRSC2E;
  WR_IE_LAHIT := :WR_IE_LAHIT;
  AR_IE_LAHIT := :AR_IE_LAHIT;
  WR_RLIM := :WR_RLIM;
  AR_RLIM := :AR_RLIM;
  WR_RWP := :WR_RWP;
  AR_RWP := :AR_RWP;
  AR_EP_FTS := :AR_EP_FTS;
  WR_DF_CHNG := :WR_DF_CHNG;
  AR_DF_CHNG := :AR_DF_CHNG;
  WR_TS_BLKRD := :WR_TS_BLKRD;
  AR_TS_BLKRD := :AR_TS_BLKRD;
  WR_TS_RD := :WR_TS_RD;
  AR_TS_RD := :AR_TS_RD;
  WR_LC_MISS := :WR_LC_MISS;
  AR_LC_MISS := :AR_LC_MISS;
  WR_LC_RLPRQ := :WR_LC_RLPRQ;
  AR_LC_RLPRQ := :AR_LC_RLPRQ;
  WR_LC_INVPRQ := :WR_LC_INVPRQ;
  AR_LC_INVPRQ := :AR_LC_INVPRQ;
  WR_ET := :WR_ET;
  AR_ET := :AR_ET;
  START_ID := $START_ID;
  END_ID := $END_ID;
  dbms_output.enable($DBMS_LIMIT);
  IF $IS_AWR = 1 THEN
    R_TITLE := 'AWR Report for $ORACLE_SID';
  ELSE
    R_TITLE := 'StatsPack Report for $ORACLE_SID';
  END IF;
  TABLE_OPEN := '<TABLE ALIGN="center" BORDER="1">';
  TABLE_CLOSE := '</TABLE>'||CHR(10)||'<BR CLEAR="all">'||CHR(10);

  SELECT d.dbid,d.name,i.instance_number,i.instance_name
    INTO DB_ID,DB_NAME,INST_NUM,INST_NAME
    FROM v\$database d,v\$instance i;

  FOR R_SnapID IN C_MaxSnap LOOP
    I1 := R_SnapID.maxid;
  END LOOP;

  --- Get Max Snap_ID:
  IF NVL($END_ID,0) = 0 THEN
    EID := I1;
  ELSE
    IF $END_ID > I1 THEN
      EID := I1;
    ELSE
      FOR R_SnapID IN C_MaxSnapRel($END_ID +1) LOOP
        EID := R_SnapId.maxid;
      END LOOP;
    END IF;
  END IF;
  --- Get Min Snap_id:
  IF NVL($START_ID,0) = 0
  THEN
    FOR R_SnapID IN C_MinSnap LOOP
      I1 := R_SnapID.minid;
      IF $SKIP_DBSTART_ID = 1 THEN
        I2 := I1;
        IF R_SnapID.startup_time > ( R_SnapID.snap_time - 1/24 ) THEN
          FOR R_Snap_ID IN C_MinSnapRel(I2) LOOP
            I1 := R_Snap_ID.minid;
          END LOOP;
        END IF;
      END IF;
    END LOOP;
    IF NVL($MAX_REP_INTERVAL,0) != 0 THEN
      SELECT (snap_time - $MAX_REP_INTERVAL) INTO TDATE FROM stats\$snapshot WHERE snap_id=EID;
      SELECT MAX(snap_id) INTO BID FROM stats\$snapshot WHERE snap_time<TDATE
         AND dbid = DB_ID AND instance_number = INST_NUM;
      IF BID < I1 THEN
        BID := I1;
      END IF;
    ELSE
      BID := I1;
    END IF;
  ELSE
    FOR R_Snap_ID IN C_MinSnapRel($START_ID -1) LOOP
      BID := R_Snap_ID.minid;
    END LOOP;
    IF NVL($END_ID,0) = 0 THEN
      FOR R_SnapID IN C_MaxSnapFor LOOP
        EID := R_SnapID.maxid;
      END LOOP;
    END IF;
  END IF;
  --- Check the range
  IF BID >= EID THEN
    RAISE invalid_snap_range;
  END IF;

  FOR R_SnapBind IN C_SnapBind LOOP
    PARA  := R_SnapBind.parallel;
    VERSN := R_SnapBind.version;
    DBVER := TO_NUMBER(SUBSTR(VERSN,1,INSTR(VERSN,'.')-1));
    DBSUBVER  := TO_NUMBER(SUBSTR(VERSN,INSTR(VERSN,'.')+1,INSTR(VERSN,'.',1,2)-1 -INSTR(VERSN,'.')));
    HOST_NAME := R_SnapBind.host_name;
  END LOOP;

  SELECT value INTO DB_BLOCKSIZE
    FROM stats\$parameter
   WHERE name='db_block_size'
     AND snap_id = EID
     AND instance_number=INST_NUM
     AND dbid=DB_ID;

--  IF MK_RLIMS = 1 THEN
    MK_RLIMS := have_rlims;
--  END IF;

  -- HTML Head
  L_LINE := '<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">'||CHR(10)||
            '<HTML><HEAD>'||CHR(10)||
            ' <META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-15">'||
            CHR(10)||' <TITLE>'||R_TITLE||'</TITLE>';
  print(L_LINE);
  L_LINE := ' <LINK REL="stylesheet" TYPE="text/css" HREF="$CSS">'||CHR(10)||
            ' <SCRIPT LANGUAGE="JavaScript" TYPE="text/javascript">'||CHR(10)||
            '   function popup(page) {'||CHR(10)||
            '     url = "help/" + page + ".html";';
  print(L_LINE);
  L_LINE := '     pos = (screen.width/2)-400;'||CHR(10)||
            '     helpwin = eval("window.open(url,'||CHR(39)||'help'||CHR(39)||
            ','||CHR(39)||'toolbar=no,location=no,titlebar=no,directories=no,'||
            'status=yes,copyhistory=no,scrollbars=yes,width=600,height=400,top=0,left="+pos+"'||
            CHR(39)||')");';
  print(L_LINE);
  L_LINE := '   }'||CHR(10)||'  version="'||OSPVER||'";'||CHR(10)||
            ' </SCRIPT>'||CHR(10)||'</HEAD><BODY>'||CHR(10)||'<H2>'||
            R_TITLE||'</H2>'||CHR(10);
  print(L_LINE);

  -- Navigation
  L_LINE := TABLE_OPEN||'<TR><TD ALIGN="center"><DIV CLASS="small">[ <A HREF="#snapinfo">SnapShot Info</A> ] '||
            '[ <A HREF="#loads">Load Profile</A> ]';
  print(L_LINE);
  IF MK_INSTEFF = 1 THEN
    L_LINE :=  ' [ <A HREF="#efficiency">Efficiency</A> ]';
  ELSE
    L_LINE := '';
  END IF;
  IF MK_INSTACT = 1 THEN
    L_LINE := L_LINE||' [ <A HREF="#instact">Instance Activity</A> ]';
  END IF;
  IF MK_TOPWAITS = 1 THEN
    L_LINE := L_LINE||' [ <A HREF="#top5wait">Top '||TOP_N_WAITS||' Wait</A> ] ';
  END IF;
  IF MK_ALLWAITS = 1 THEN
    L_LINE := L_LINE||' [ <A HREF="#waitevents">Wait Events</A> ]';
  END IF;
  IF MK_BGWAITS = 1 THEN
    L_LINE := L_LINE||' [ <A HREF="#bgwaitevents">Background Waits</A> ]';
  END IF;
  print(L_LINE);
  IF MK_WAITOBJ = 1 THEN
    S1 := 'istats\$waitobjects'; I1 := 1; I2 := 0;
    tab_exists(S1,I1,I2);
    IF I2 = 1
    THEN
      print(' [ <A HREF="#waitobjects">Wait Objects</A> ]');
    END IF;
  END IF;
  IF MK_TOPSQL = 1 THEN
    L_LINE := ' [ <A HREF="#sqlbygets">SQL by Gets</A> ] [ <A HREF="#sqlbyreads">SQL by Reads</A> ]'||
              ' [ <A HREF="#sqlbyexec">SQL by Exec</A> ] [ <A HREF="#sqlbyparse">SQL by Parse</A> ]'||
              ' [ <A HREF="#sqlbycpu">SQL by CPU</A> ] [ <A HREF="#sqlbyela">SQL by Ela</A> ]'||
              ' [ <A HREF="#sqlbyinv">SQL by Invs</A> ]';
  ELSE
    L_LINE := '';
  END IF;
  IF LENGTH(L_LINE) > 0 THEN
    print(L_LINE);
    L_LINE := '';
  END IF;
  IF $MK_TABS = 1 THEN
    L_LINE := L_LINE||' [ <A HREF="#tabs">TableStats</A> ]';
  END IF;
  IF MK_DFSTAT = 1 THEN
    S1 := 'istats\$datafiles'; I1 := 1; I2 := 0;
    tab_exists(S1,I1,I2);
    IF I2 = 1
    THEN
      L_LINE := L_LINE||(' [ <A HREF="#filestats">Datafiles</A> ]');
    END IF;
  END IF;
  IF MK_TSIO + MK_FIO > 0 THEN
    L_LINE := L_LINE ||' [ <A HREF="#fileio">File IO</A> ]';
  END IF;
  IF MK_DBWR = 1 THEN
    L_LINE := L_LINE ||'[ <A HREF="#dbwr">DBWR</A> ]';
  END IF;
  IF MK_LGWR = 1 THEN
    L_LINE := L_LINE ||'[ <A HREF="#lgwr">LGWR</A> ]';
  END IF;
  IF MK_SEGSTAT = 1 THEN
--    IF MK_SEG_LR + MK_SEG_PR + MK_SEG_BUSY + MK_SEG_LOCK + MK_SEG_ITL > 0 THEN
      L_LINE := L_LINE ||' [ <A HREF="#segstat">Segment Stats</A> ]';
--    END IF;
  END IF;
  IF MK_RECO = 1 THEN
    L_LINE := L_LINE||' [ <A HREF="#recover">Instance Recovery</A> ]';
  END IF;
  IF LENGTH(L_LINE) > 0 THEN
    print(L_LINE);
  END IF;
  IF MK_PGAA + MK_PGAM > 0 THEN
    L_LINE := ' [ <A HREF="#pga">Memory Stats</A> ]';
  ELSE
    L_LINE := '';
  END IF;
  IF MK_ENQ = 1 THEN
    HAVENQ := have_enqs;
    IF HAVENQ THEN
      L_LINE := L_LINE||' [ <A HREF="#enq">Enqueue Activity</A> ]';
    END IF;
  END IF;
  IF MK_USS + MK_USSTAT > 0 THEN
    L_LINE := L_LINE||' [ <A HREF="#undo">Undo Segs</A> ]';
  END IF;
  IF MK_RSSTAT + MK_RSSTOR > 0 THEN
    L_LINE := L_LINE||' [ <A HREF="#rbs">RBS</A> ]';
  END IF;
  IF MK_LACT = 1 THEN
    L_LINE := L_LINE||' [ <A HREF="#latches">Latches</A> ]';
  END IF;
  IF LENGTH(L_LINE) > 0 THEN
    print(L_LINE);
  END IF;
  IF MK_SPSTAT = 1 THEN
    L_LINE := ' [ <A HREF="#sharedpool">Shared Pool</A> ]';
  ELSE
    L_LINE := '';
  END IF;
  IF MK_BUFFP + MK_BUFFW > 0 THEN
    L_LINE := L_LINE||' [ <A HREF="#buffstat">Buffer Stats</A> ]';
  END IF;
  IF MK_CACHSIZ + MK_DC + MK_LC > 0 THEN
    L_LINE := L_LINE||' [ <A HREF="#caches">Caches</A> ]';
  END IF;
  IF MK_SGASUM = 1 THEN
    L_LINE := L_LINE||' [ <A HREF="#sga">SGA</A> ]';
  END IF;
  IF MK_RLIMS THEN
    L_LINE := L_LINE||' [ <A HREF="#resourcelimits">Resource Limits</A> ]';
  END IF;
  IF MK_IORA = 1 THEN
    L_LINE := L_LINE||' [ <A HREF="#initora">Init Params</A> ]</DIV></TD></TR>';
  END IF;
  IF LENGTH(L_LINE) > 0 THEN
    print(L_LINE);
  END IF;
  print(TABLE_CLOSE);

  -- Initial information about this instance
  SELECT to_char(SYSDATE,'DD.MM.YYYY HH24:MI') INTO S4 FROM DUAL;
  L_LINE := TABLE_OPEN||'<TR><TH COLSPAN="2">Common Instance Information</TH></TR>'||CHR(10)||
            ' <TR><TD class="td_name">Hostname:</TD><TD>'||HOST_NAME||'</TD></TR>'||CHR(10)||
            ' <TR><TD class="td_name">Instance:</TD><TD>'||INST_NAME||'</TD></TR>';
  print(L_LINE);
  L_LINE := ' <TR><TD class="td_name">Version:</TD><TD>'||VERSN||'</TD></TR>'||CHR(10)||
            ' <TR><TD class="td_name">Cluster:</TD><TD>'||PARA||'</TD></TR>'||CHR(10);
  print(L_LINE);
  SELECT SUM(members*bytes) INTO I1 FROM v\$log;
  SELECT SUM(bytes) INTO I2 from v\$datafile;
  I3 := (I1+I2)/1048576;
  S1 := to_char(I3,'999,999,999.99');
  SELECT to_char(startup_time,'DD.MM.YYYY HH24:MI'),
         to_char(sysdate - startup_time,'9990.00'),
         sysdate - startup_time
    INTO S2,S3,DB_UPTIME FROM v\$instance;
  L_LINE := ' <TR><TD class="td_name">FileSize (Data+Log)</TD><TD>'||S1||' MB</TD></TR>'||CHR(10)||
            ' <TR><TD class="td_name">Startup / Uptime</TD><TD>'||S2||' / '||S3||' d</TD></TR>';
  print(L_LINE);
  L_LINE := ' <TR><TD class="td_name">Report generated:</TD><TD>'||S4||'</TD></TR>'||CHR(10)||
            TABLE_CLOSE;
  print(L_LINE);

ENDSQL