what is the hitratios( db hit, lib hit, etc...) that have to achieved on different systems like DSS, OLTP etc...
Usually the db hitratio you check for is 90%, but i would like to know if this is same for all the systems, like DSS and OLTP..?
If there is any article with all the recommended hit ratios that should be achieved on different systems, please pass on it to me. Its URGENT!
Thank you all in advance.
Venu G. (OCP 8i & 9i)
Can you please tell me what version of Oracle you are using.
if it is Oracle 8i then you can not judge the performance based on hit ratio's.
u need to go thru many things. even though hit ration is 99 % u cannot say performance is high.
I think it would be a brave person who would give you specific figures for this. It depends so much on the type of work done on the machine and the amount of resources you've got.
Large OLTP systems which generally fetch individual blocks which are used once only. Regardless of the amount of buffer cache you assign, if a block is not reused you will get no benefit from having it cached.
Large DSS systems which process so much data that you do not have enough physical memory to cache it so disk access is necessary.
As for the library cache, once again it depends on your system. If the majority of your workload consists of a few dozen queries then you should expect a good library cache hit ratio. If on the other your system produces adhoc reports with almost limitless flexibility you are unlikely to find the exact same statement already parsed.
I would suggest adding more memory to both until you reach acceptable performance for YOU, assuming you're no breaking into swap space.
Oracle recommend baselining to establish the current performance. Then decide what an acceptable performance level would be for YOU. At that point you can take steps to acheive that goal. Saying that a database is not tuned if it has less than 89% cache hit ratio is plainly rediculous. This is why performance tuning is considered a "black art" by some.
A wise old DBA once told me, "You can tune a DB to your hearts content, but sometimes you just need to put it onto a bigger box!"
Click Here to Expand Forum to Full Width