How to monitor buffer cache and shared pool usage?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: How to monitor buffer cache and shared pool usage?

  1. #1
    Join Date
    Apr 2003
    Location
    Kuala Lumpur
    Posts
    59

    How to monitor buffer cache and shared pool usage?

    Hi,

    The users complain the database access to Oracle 9i is very slow. And I increase the shared pool size to double and it speeds up substantially. May I know how can I monitor how much buffer cache and shared pool have been used? Tks.

  2. #2
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    Use the following query to calculate the buffer cache hit ratio using the formulae given below the code:
    Code:
    SELECT NAME, VALUE
      FROM V$SYSSTAT
    WHERE NAME IN ('session logical reads','physical reads',
                   'physical reads direct','physical reads direct (lob)');
    Hit Ratio = 1 - ((physical reads - physical reads direct - physical reads direct (lob)) /
    (db block gets + consistent gets - physical reads direct - physical reads direct (lob))

    This should be > 95%

    Use the following link for the shared pool: http://download-west.oracle.com/docs...mory.htm#32137

    HTH.
    Last edited by ggnanaraj; 06-19-2003 at 07:02 AM.

  3. #3
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    then go to hotsos and take a look at

    Are you still using cache hit ratios?

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    turn on cache advice, and oracle wil tell you the effect of increasing/decreasing your cache sizes.

    Many people seem to make the mistake of using to small a shared pool size, so it's not a suprising result
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width