-
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.
-
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 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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|