/*****************************************************************************
 * File:	spviews80.sql
 * Author:	Tim Gorman (Evergreen Database Technologies, Inc)
 * Date:	20Sep01
 * Type:	SQL*Plus script
 *
 * Description:
 *	Script to create the V$BUFFER_POOL_STATISTICS and V$TEMPFILE in an
 *	Oracle8 v8.0.x database, so that STATSPACK can be installed.
 *
 *	The V$BUFFER_POOL_STATISTICS view is actually included with v8.0.6
 *	anyway, but V$TEMPFILE is not, and is just a NO-OP anyway...
 *
 * Modifications:
 ****************************************************************************/
REM
REM Adapted from "catperf.sql" in standard v8.0.5 distribution
REM

spool spviews80

connect internal

set echo on feedback on timing on

create or replace view v_$buffer_pool_statistics
( ID,
  NAME,
  SET_MSIZE,
  CNUM_REPL,
  CNUM_WRITE,
  CNUM_SET,
  BUF_GOT,
  SUM_WRITE,
  SUM_SCAN,
  FREE_BUFFER_WAIT,
  WRITE_COMPLETE_WAIT,
  BUFFER_BUSY_WAIT,
  FREE_BUFFER_INSPECTED,
  DIRTY_BUFFERS_INSPECTED,
  DB_BLOCK_CHANGE,
  DB_BLOCK_GETS,
  CONSISTENT_GETS,
  PHYSICAL_READS,
  PHYSICAL_WRITES)
as
select kcbwbpd.bp_id,
       kcbwbpd.bp_name,
       sum(kcbwds.set_msize),
       sum(kcbwds.cnum_repl),
       sum(kcbwds.cnum_write),
       sum(kcbwds.cnum_set),
       sum(kcbwds.buf_got),
       sum(kcbwds.sum_wrt),
       sum(kcbwds.sum_scn),
       sum(kcbwds.fbwait),
       sum(kcbwds.wcwait),
       sum(kcbwds.bbwait),
       sum(kcbwds.fbinsp),
       sum(kcbwds.dbinsp),
       sum(kcbwds.dbbchg),
       sum(kcbwds.dbbget),
       sum(kcbwds.conget),
       sum(kcbwds.pread),
       sum(kcbwds.pwrite)
  from x$kcbwds kcbwds, x$kcbwbpd kcbwbpd
 where kcbwds.set_id >= kcbwbpd.bp_lo_sid
   and kcbwds.set_id <= kcbwbpd.bp_hi_sid
   and kcbwbpd.bp_size != 0
 group by kcbwbpd.bp_id, kcbwbpd.bp_name
/

grant select on v_$buffer_pool_statistics to perfstat
/

create public synonym v$buffer_pool_statistics for v_$buffer_pool_statistics
/

create view v_$tempstat
as
select	*
from	v_$filestat
where	1 = 2
/

grant select on v_$tempstat to perfstat
/

create public synonym v$tempstat for v_$tempstat
/


create view v_$tempfile
as
select	*
from	v_$datafile
where	1 = 2
/

grant select on v_$tempfile to perfstat
/

create public synonym v$tempfile for v_$tempfile
/

spool off