|
-
A good place to start would be to look at hit ratios of the follwing areas in the databse:
Library cache(should be 99%+) :
SELECT (SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE"
FROM V$LIBRARYCACHE
Rowcache (data dictionnary cahce, should be 85%+):
SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW CACHE"
FROM V$ROWCACHE
Buffer cache(should be at least 90%, 95%+ preferred):
select (1-(phy.value/(db.value+cons.value)))*100 "Buffer Cache Hit"
from v$sysstat phy,v$sysstat db,v$sysstat cons
where phy.name='physical reads' and
db.name='db block gets' and
cons.name='consistent gets'
Also look at redo log file(bothe these values should be near 0):
select name,value
from v$sysstat
where name in ('redo log space requests','redo buffer allocation retries')
You could should also look at rollback segment hit rates(should be 100%) :
select name,waits,gets,extends,rssize, (100-(waits/gets)) "HIT RATIO"
from v$rollstat a,v$rollname b
where a.usn=b.usn
This should be ok for starters. Once you have done this if there are any problems you can start to look at rectifying them.
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
|