Oracle v$bh x$bh Scripts

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.

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

STATUS     NUM
--------- ----------
32k cr     1456
32k xcur 30569
8k cr    32452
8k free      6
8k xcur 340742
free      15829

It is interesting to run this report repeatedly because the Oracle data buffers are so dynamic. Running the script frequently allows the DBA to view the blocks entering and leaving the data buffer. The midpoint insertion method can be seen in action and the hot and cold regions can be seen as they update.

The v$segment_statistics view is a goldmine for funding wait events that are associated with a specific Oracle table. I wrote the following script to show run-time details about a segment (usually a table or an index), and this powerful script interrogates the v$segment_statistics view use a CASE statement. If you examine the script below you will see that the v$segment_statistics view is grouped by object_name. For each object, we display counts of the major object wait events, as seen in the CASE expression. The most important of these object-level wait events will give us clues into the source of the contention.

For example, buffer busy waits and ITL waits all have a clear set of causes, and knowing this information is critical to understanding the root cause of your contention.

When reviewing objects for possible tuning issues, it is handy to have statistics such as the number of internal transaction list (ITL) waits, buffer busy waits and row lock waits that the object has experienced. Combined with the number of logical and physical reads the object has experienced, the above statistics give a complete picture of the usage of the object in question.