How to monitor buffer cache and shared pool usage?

[richyip]

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.

[ggnanaraj]

Use the following query to calculate the buffer cache hit ratio using the formulae given below the 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.

[davey23uk]

then go to hotsos and take a look at

Are you still using cache hit ratios?

[slimdave]

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