Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

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

 

 

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.


 

SEE CODE DEPOT FOR FULL SCRIPTS


This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference". 

You can buy it direct from the publisher for 50%-off and get instant access to the code depot of Oracle tuning scripts:

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm

 

 

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 
 
email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.