-
I found out that couple of users have negative percentage of buffer hit ratio....like -49% and it is keep decreasing....what can I do to resolve the problem? Users query is being hung up several times already....any suggestions?
-
-
This is the query that I used to get the hit ratio.
SELECT Username,OSUSER,Consistent_Gets,Block_Gets,Physical_Reads,
100*( Consistent_Gets + Block_Gets - Physical_Reads)/
( Consistent_Gets + Block_Gets ) "Hit Ratio %"
FROM V$SESSION,V$SESS_IO
WHERE V$SESSION.SID = V$SESS_IO.SID
AND ( Consistent_Gets + Block_Gets )>0
AND username is not null
ORDER BY Username,"Hit Ratio %";
I don't think it's miscalculations..is it?
-
Was the column previously formatted? Try issuing CLEAR COL and run it again.
I've done this before, DOH!
MH
I remember when this place was cool.
-
I cleared col and reran.....same result....now it's at -83.2%
-
Yeah, looks OK to me.
What are your values for consistent_gets, block_gets, & physical_reads?
Jeff Hunter
-
consistent_gets=2614
block_gets=407
physical_reads=5534
-
Originally posted by gaegoori
consistent_gets=2614
block_gets=407
physical_reads=5534
This is expected behavior :-) The problem you have faced is that after the DB has been up and running quite some
time some of the component values becomes negative or extremely large. I did wonder abot that some time ago myself. I had a DB that was not shutdown for months.
Oracle does not offer us any ways or methods to clear the statistics, except the SHUTDOWN one.
You can rewrite your query (using decode for example) toshow negative values as 100 for I think it is very close to the truth.
-
Doesn't this mean that buffer cache is way too small, thus most datafile blocks are read from disk rather than buffer cache ?
I can't understand how the system's up time could influence figures so dramatically.
Gaegoori....
The best thing for you to do is run utlbstat/estat or statspack at peak time (not at time when activity is heavily increasing/decreasing)
This will give you a definitive buffer cache hit ratio.
Once you have eliminated all of the impossible,
whatever remains however improbable,
must be true.
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
|