Re: Poor DB performance (Dictionary Cache?)

From: Jgreene <jgreene_at_aol.com>
Date: 19 Feb 1995 19:50:15 -0500
Message-ID: <3i8p07$ar3_at_newsbf02.news.aol.com>


> I ran SQL*DBA and noticed that in the "system statistics" readout, the
 value of
> the "recursive calls" variable ranges between '15000' and '20000'.
 However, the
> Corrigan and Gurry "Oracle Performance Tuning" book states that this
 should be
> '0'. They say that this is due to an incorrectly tuned dictionary cache.
 But I
> am not sure what exactly has to be done to rectify the situation.

Here is a check that is designed around Oracle recommendations that should tell you for sure. The real problem is knowing if it just that you have a large volume of transactions (in which case while your numbers are large, they are relatively infrequent). Anyway, here is a script that should check for you. The recommended limit of 10% is in a prompt statement, hope this helps.

rem



*

rem Data dictionary cache checks

column ddcache format 99.99 heading 'Data Dictionary Cache Miss Ratio (%)'

prompt

########################################################################
prompt
prompt Data Dictionary Cache Check
prompt
prompt Goal: <10%
prompt
prompt Corrective Actions: Increase shared_pool_size prompt

select sum(getmisses)/sum(gets) * 100 ddcache from v$rowcache
/

prompt
prompt Received on Mon Feb 20 1995 - 01:50:15 CET

Original text of this message