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

Donald K. Burleson

Oracle Tips


Oracle data Buffer Cache Metrics

If you want a deeper understanding of how the buffer cache is being utilized, there are a few additional queries you can run to gain such insight.  If the keep and recycle buffer caches are being used, you can run the cacheobjcnt.sql query to get an idea on how many objects have been assigned to each cache:

 

See Code depot for complete script

 

select

      decode(cachehint, 0, 'default', 1,

      'keep', 2, 'recycle', null) cache,

      count(*) objects

from 

      sys.seg$ s

where

. . .

group by

       decode(cachehint, 0, 'default', 1,

       'keep', 2, 'recycle', null)

order by

    1;

 

Output may resemble something like the following:

 

CACHE     OBJECTS

-----------------

default      2023

keep            5

 

Finally, you may wish to analyze the buffer cache activity from time to time to see how it is being utilized.  The buffutl.sql script will show you how full the cache currently is along with the state of the buffers in the cache:

 

See Code depot for complete script

 

select

       'free' buffer_state,

        nvl(sum(blocksize) / 1024 ,0) amt_kb

from

        sys.x$bh a,

        sys.ts$ b

where

. . .

union all

select

        'read/mod' buffer_state,

        nvl(sum(blocksize) / 1024 ,0) amt_kb

from

        sys.x$bh a,

        sys.ts$ b

where

. . .

union all

select

       'read/notmod',

       nvl(sum(blocksize) / 1024 ,0) amt_kb

from

       sys.x$bh a,

       sys.ts$ b

where

. . .

union all

select

       'being read' buffer_state,

       nvl(sum(blocksize) / 1024 ,0) amt_kb

from

       sys.x$bh a,

       sys.ts$ b

where

. . .

order by

      1;

 

Output from the above query might look something like this:

 

BUFFER_STATE      AMT_KB

------------------------

being read          5920

free               23568

read/mod           47952

read/notmod            0

 

Now that you have a good understanding of how to interrogate the buffer cache, the next area you'll want to examine is the shared pool.


The above is an excerpt from Oracle Performance Troubleshooting by Robin Schumacher.

It's only $19.95 and you can order it and get instant access to the Oracle scripts here:

http://www.rampant-books.com/book_2003_1_perf.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.