How to monitor buffer cache and shared pool usage?
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.
Use the following query to calculate the buffer cache hit ratio using the formulae given below the code:
Hit Ratio = 1 - ((physical reads - physical reads direct - physical reads direct (lob)) /
SELECT NAME, VALUE
WHERE NAME IN ('session logical reads','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
Last edited by ggnanaraj; 06-19-2003 at 06:02 AM.
then go to hotsos and take a look at
Are you still using cache hit ratios?
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
Click Here to Expand Forum to Full Width