|
Gathering v$bh status for multiple buffer pools
When using multiple blocksizes, standard v$bh
scripts would report large amounts of free
buffers even when the default pool was full and
needed more when the optional areas had free.
So, here is a first cut at finding the status of
the blocks by blocksize in the buffer.
all_vbh_status.sql
set pages 50
@title80 'All Buffers Status'
spool rep_out\&&db\all_vbh_status
select
'32k '||status as status,
count(*) as num
from
v$bh
where file# in(
select file_id
from dba_data_files
where tablespace_name in (
select tablespace_name
from dba_tablespaces
where block_size=32768))
group by '32k '||status
union
select
'16k '||status as status,
count(*) as num
from
v$bh
where
file# in(
select file_id
from dba_data_files
where tablespace_name in (
select tablespace_name
from dba_tablespaces
where block_size=16384))
group by '16k '||status
union
select
'8k '||status as status,
count(*) as num
from
v$bh
where
file# in(
select file_id
from dba_data_files
where tablespace_name in (
select tablespace_name
from dba_tablespaces
where block_size=8192))
group by '8k '||status
union
select
'4k '||status as status,
count(*) as num
from
v$bh
where
file# in(
select file_id
from dba_data_files
where tablespace_name in (
select tablespace_name
from dba_tablespaces
where block_size=4096))
group by '4k '||status
union
select
'2k '||status as status,
count(*) as num
from
v$bh
where
file# in(
select file_id
from dba_data_files
where tablespace_name in (
select tablespace_name
from dba_tablespaces
where block_size=2048))
group by '2k '||status
union
select
status,
count(*) as num
from
v$bh
where status='free'
group by status
order by 1
/
spool off
ttitle off
Here is a sample of the v$bh output for a
database with multiple blocksizes:
STATUS
NUM
--------- ----------
32k cr 1456
32k xcur 30569
8k cr 32452
8k free
6
8k xcur 340742
free 15829
|
