row cache objects latch and shared_pool_size
NetComrade
........................
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
DA Morgan
Could be.
There is a lot of information on sizeing the various pools at
http://tahiti.oracle.com
Daniel Morgan
Howard J. Rogers
context of other statistics.
For example, if your shared pool really was too large, I would expect to see
very, very few reloads-to-pins -because a reload is what you get when the
library cache is too small, and cursors have to be aged out. With a large
shared pool, you won't be ageing too many things out, so the reloads to pins
should be very good (ie, much, much smaller than 1%). So check
v$librarycache.
A good gethitratio might also indicate the shared pool was too large, in the
context of contention for your latch, because too small a shared pool would
cause lots of get misses.
If you don't have ORA-4031's, that's another indication that it's not too
small.
And so on... that's just a flavour of what you look for. If all other
indicators are reasonably healthy, in other words, then it is likely a
matter of too big a cache rather than too small.
Regards
HJR
"NetComrade" <andre...@bookexchange.net> wrote in message
news:3e89cf77....@nyc.news.speakeasy.net...
Jonathan Lewis
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
The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick Jr
One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
____UK_______April 8th
____UK_______April 22nd
____Denmark__May 21-23rd
____USA_(FL)_May 2nd
Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK_(Manchester)_May
____Estonia___June (provisional)
____USA_(CA, TX)_August
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
"NetComrade" <andre...@bookexchange.net> wrote in message
news:3e89cf77....@nyc.news.speakeasy.net...
NetComrade
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)
>
>