| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE : Dictionary Cache HIT Ratio
Rajesh,
My personal version would rather be :
select 'DICTIONARY CACHE',
least(100, round(100 * sum(gets - getmisses + (usage - fixed))
/ sum(gets), 2))
Explanation : you have 'slots' holding information for specific data dictionary information. This is what Oracle uses to pull all the information it requires. Quite obviously, some of those slots are pre-filled at startup (with information taken from the CACHE segment I presume), because if you do not know where to find OBJ$, COL$ and this kind of table in the first place you are in a bad mess. So, when the database is up and running, you have 'usage' used slots, 'fixed' of them having been filled with information at startup, 'usage - fixed' having been filled to answer specific requests, or 'gets'. What we are interested in is trying to find out whether Oracle has or has not enough storage. If we are short on storage, then quite obviously some already filled slots will have to be overwritten by new information, and when this old information will be required again, then we shall have a 'miss' which we might have avoided with more slots (more memory).
BUT, must we count the 'misses' we have to fill EMPTY slots? I don't
think so.
Which is why I remove from the 'getmisses' the 'usage - fixed' somewhat
mandatory misses I had to fill up to 'usage'.
All this said, IMHO it's hair splitting. The dictionary cache is just one part of the shared pool which you can no longer, since Oracle 7.0, tune separately (a little tear in memory of all the Oracle 6 dc_ ... parameters). We are interested in broad trends, and I think that all three formulae should yield very close results.
HTH,
Stephane Faroult
email: sfaroult_at_oriolecorp.com
Oriole Corporation
Voice: +44 (0) 7050-696-269
Fax: +44 (0) 7050-696-449
Performance Tools & Free Scripts
 |
 |