RE :

From: Stephane Faroult <sfaroult_at_oriolecorp.com>
Date: Mon, 22 May 2000 17:01:24 +0200
Message-Id: <10505.106231@fatcity.com>

Ruth,

    I would not fret with 5% but I would feel more comfortable in the 2/3% range or below. Like anything which has to do with the dictionary, you should have lower thresholds than with data blocks. This said, your query yields pessimistic values and I suggest you use the following instead :

   SELECT SUM(getmisses - usage + fixed) "Dict Misses",

          SUM(gets) "Requests",
          100-(100*(SUM(getmisses - usage + fixed)/SUM(gets))) "Hit%",
          100*(SUM(getmisses - usage + fixed)/SUM(gets)) "Miss%"

   from v$rowcache
  where getmisses > usage - fixed

 V$ROWCACHE contains one row per category of dictionary objects (a few categories manage to get several rows). Basically, you can consider them as 'slots', used up to the 'usage' level. Some of the slots are pre-filled at startup with stuff from the bootstrap segment, hence the non-zero value for some of the FIXED columns. Since you have to load the rest on the first call, the first 'gets' generate 'misses' - which is why I do not count any miss below usage - fixed as a 'true' miss.

-- 
Regards,

  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
------------------------------------------------------------------
http://www.oriolecorp.com, designed by Oracle DBAs for Oracle DBAs
------------------------------------------------------------------


> Hi all,

> 

> Can anyone tell me the acceptable range for dictionary misses when using

> this script?

> SELECT SUM(getmisses) "Dict Misses",

>       SUM(gets) "Requests

>      100-(100*(SUM(getmisses)/SUM(gets))) "Hit%",

>      100*(SUM(getmisses)/SUM(gets)) "Miss%"

>      FROM v$rowcache;

> 

> I think I remember it needs be less than 5% but I can't remember.

> 

> Thanks in advance,

> Ruth B. Gramolini

> ORACLE DBA

> VT Dept. of Taxes

> ph# 802.828.5708

> fax# 802.828..3754

Received on Mon May 22 2000 - 10:01:24 CDT