Re: row cache objects latch and shared_pool_size

This turned out to be an oracle bug related to queries over the link and CBO

http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_id=1065010&p_database_id=BUG http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=BUG&p_id=2738315

It had to do with dc_histogram_defs
this was a value from statspack for 10 mins before bug was fixed dc_histogram_defs 11,002,938
this is the value from statspack for an hour after bug was fixed dc_histogram_defs 5,720

It's now fixed.

As to reloads, I am still puzzled, since all queries reloading were identified as executing against objects that don't exist, as soon as such objects are created, reloads stop, however, i couldn't reproduce this in sqlplus (the harparses would increase, but query wouldn't even make it into v$sql).

Why we are querying against tables that don't exist is a whole different question.. but of course, in developer's and manager's view it's always a 'database problem'

shared pool size has been decreased to 200m, btw, and we are doing fine (from 400m)

On Sun, 6 Apr 2003 11:31:17 +0100, "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:

>
>I won't add to Howard's response. But taking a different
>tack I'd like to point out that the row cache objects latch
>is a parent latch (in your version of Oracle) with about 22
>child latches that correspond to the different "parameter"
>parent types in the v$rowcache view.
>
>If you are certain that there is significant contention for this
>latch you should check whether it is restricted to particular
>elements of the rowcache. This may give you some clues.
>
>Example: if you are using pipelined functions heavily, then
>there are three entries in v$rowcache that will get hammered -
>off the top of my head, these are:
> dc_users
> dc_object_ids
> dc_global_oids
>If your problem happens to be is in this area, then sizing of
>pools is irrelevant.
>
>
>
>(In fact, noting your other post about reloads reports in v$sql,
>there is a hint that perhaps your problem is "shared pool too small" -
>excessive reloads would lead to heavy thrashing on the 'commoner'
>bits of the row cache)
>
>--
>Regards
>
>Jonathan Lewis
>http://www.jlcomp.demon.co.uk

>
>
>"NetComrade" <andreyNSPAM_at_bookexchange.net> wrote in message
>news:3e89cf77.3113443725_at_nyc.news.speakeasy.net...
>> Row cache objects latch:
>> ........................
>>
>> In order to reduce contention for this latch, we need to
>> tune the data dictionary cache. In Oracle7 this basically
>> means
>> increasing the size of the shared pool (SHARED_POOL_SIZE)
>>
>>
>> Could it be the other way around? Could the shared_pool_size be too
>> large? how can one tell?
>> .......
>> We use Oracle 8.1.7.4 on Solaris 2.7 boxes
>> remove NSPAM to email
>
>

.......
We use Oracle 8.1.7.4 on Solaris 2.7 boxes remove NSPAM to email Received on Tue Apr 08 2003 - 13:05:36 CDT