Re: Data Dictionary Hit Ratio - myth or fact?

  • From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
  • To: Oracle List <oracle-l@xxxxxxxxxxxxx>, Tony Sequeira <tony@xxxxxxxxxxxxxxx>
  • Date: Tue, 18 Mar 2008 13:08:03 +0100


   As always it depends on whether it hurts ... Perhaps you should take a look at queries in the SGA that hit dictionary tables (V$SGASTAT).
They may indicate some mistakes that are easy to correct. For instance, frequent SELECTs on seq$ usually mean that the CACHE value of some frequently used sequences is too low. High activity against seg$ may point to improperly sized objects. Queries against cdef$ and con$ may come from an abnormally high number of violations of unique constraints. And so on. Just some prior sanity check before guessing by how much one should increase the SGA for the symptom to disappear ...


Stéphane Faroult

On Die Mar 18 10:12 , Tony Sequeira sent:

I help support a Database running on MS Windows Server 2003

Someone has some old performance scripts that displays the data
dictionary hit ratio amongst others. This comes in in the mid to high
80s. The BCHR on this system is high 90s.

I have received a request to sort this out.

Data Dictionary Hit Ratio should be > 90% Check Oracle Memory

I am aware of the BCHR and the arguments against using it, but I cannot
find anything specific on the DDHR. I'm thinking of sending them a link
to Kolk's YAPP Method.

Any pointers?

Of course, they may be right, and this may need addressing.
S. Anthony Sequeira
Take everything in stride. Trample anyone who gets in your way.



Other related posts: