Rem
Rem $Header: catperf.sql 05-jan-99.17:02:39 anithrak Exp $
Rem
Rem catperf.sql
Rem
Rem  Copyright (c) Oracle Corporation 1997, 1999. All Rights Reserved.
Rem
Rem    NAME
Rem      catperf.sql - performance tuning views
Rem
Rem    DESCRIPTION
Rem      this file contains various views useful for performance tuning.
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    nmacnaug    12/29/99 - fix conget statistics
Rem    anithrak    01/05/99 - Fix columns that have been removed from x$kcbwds
Rem    aho         06/18/97 - extended dbwr statistics
Rem    aho         06/03/97 - catperf.sql
Rem    aho         06/03/97 - Created
Rem

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.cnum_set),
       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),
       0,
       0,
       0,
       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
/
comment on table v$buffer_pool_statistics is
'Buffer pool statistics'
/
comment on column v$buffer_pool_statistics.id is
'Buffer pool number'
/
comment on column v$buffer_pool_statistics.name is
'Buffer pool name'
/
comment on column v$buffer_pool_statistics.set_msize is
'Buffer pool maximum set size'
/
comment on column v$buffer_pool_statistics.cnum_repl is
'Number of buffers on replacement list'
/
comment on column v$buffer_pool_statistics.cnum_write is
'Number of buffers on write list'
/
comment on column v$buffer_pool_statistics.cnum_set is
'Number of buffers in set'
/
comment on column v$buffer_pool_statistics.buf_got is
'Number of buffers gotten by the set'
/
comment on column v$buffer_pool_statistics.sum_write is
'Number of buffers written by the set'
/
comment on column v$buffer_pool_statistics.sum_scan is
'Number of buffers scanned in the set'
/
comment on column v$buffer_pool_statistics.free_buffer_wait is
'Free buffer wait statistic'
/
comment on column v$buffer_pool_statistics.write_complete_wait is
'Write complete wait statistic'
/
comment on column v$buffer_pool_statistics.buffer_busy_wait is
'Buffer busy wait statistic'
/
comment on column v$buffer_pool_statistics.free_buffer_inspected is
'Free buffer inspected statistic'
/
comment on column v$buffer_pool_statistics.dirty_buffers_inspected is
'Dirty buffers inspected statistic'
/
comment on column v$buffer_pool_statistics.db_block_change is
'Database blocks changed  statistic'
/
comment on column v$buffer_pool_statistics.db_block_gets is
'Database blocks gotten statistic'
/
comment on column v$buffer_pool_statistics.consistent_gets is
'Consistent gets statistic'
/
comment on column v$buffer_pool_statistics.physical_reads is
'Physical reads statistic'
/
comment on column v$buffer_pool_statistics.physical_writes is
'Physical writes statistic'
/

Rem
Rem  DBWR statistics
Rem

create or replace view v$dbwr_write_histogram as
select trunc(indx / 100) * 100 buffers, sum(issued) count
  from x$kcbbhs group by  trunc(indx / 100);

create or replace view v$dbwr_write_log as
select val1 setid, val2 num, val3 total
  from x$trace where id = 220;
