| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Largest shared pool
Hi!
> As to your first point, I read here, I think it was stated by Tanel,
> that starting in 9iR2 there is an undocumented parameter that allows you
> to define multiple shared pool latches? Unfortunately, searching my
> archive proved unsuccessful. =20
>
> Tanel, can you refresh my memory?
Yes, there is a parameter _kghdsidx_count (might be available even from 9.0) which you can manually set from 1 to 7 (at least on my small test environments, since there seems to be only 7 shared pool child latches) .
As per K Gopalakrishnan's comment few weeks ago, Oracle will set the parameter automatically to a value greater than 1 with SGAs larger than 250MB and multiple CPUs.
(Tim, it would be interesting to know how many rows do you have there in your system with huge shared pool?)
At least my very brief tests showed that the shared pool heaps/latches were not chosen with round-robin fashion, at least it doesn't work very flexibly - you can have ORA-4031 errors when you hit a "full" shared pool heap, but have enough free space in others.
Anyway, when you have divided your shared pool to multiple heaps, you'll see stats for them from x$kghlu (one row per shared pool heap).
X$KGHLU contains stats for each shared pool heap's unpinned recreatable chunks' LRU list, which resides in each heap's heap descriptor. This table provides some valuable information like amount of recurrent and transient chunks for each heap (KGHLURCR and KGHLUTRN columns), if there are constantly much more transient (presumably once used) chunks in the list than recurrent (more than once used) chunks, then shared pool is probably unnecessarily big and may cause more CPU usage or latch contention when doing shared pool memory alloc/free operations. A rule of thumb I've seen (probably on Ixora) is that transient list should be less than 3x larger than recurrent list...
>
> As to the second point, yeah, that would be a cool idea. Maybe with
> hints that would automatically direct the statement to that pool? /*+
> KEEP */, /*+ DEFAULT */, or /*+ RECYCLE */, or something like that?
I think this can't be done that easily - since (as far as I understand) when free space is needed, Oracle ages out shared pool memory chunks (using generic heap manager (KGH) module), it doesn't necessarily know to whom these chunks belong, it only searches for free or unpinned recreatable chunks in shared pool paying no attention to chunk's other attributes...
So, it doesn't matter whether the chunks belong to library cache, rowcache or anything else, as long as they are free according to KGH module's logic. However there sometimes is a link between "dumb and trivial" KGH module and the module "owning" the chunk - when KGH wants to reuse a chunk, the owner has to know about it, otherwise it could read or write obsolete data from/to the reused memory location. That's why a callback routine can be used by KGH module, to execute special code of the chunk owner to clear out pointers to this chunk and state that this chunk has been reused. For example if Heap 0 for a library cache object has been aged out by KGH, the library cache object handle has to be updated with relevant information.
When Oracle introduced touch-count based aging algorithm for *buffer cache* in 8.1.6, there wasn't that much of a need for keep and recycle pools in regular systems anymore, since rarely used blocks could be aged out quickly...
Oracle actually has something similar for shared pool already as well -> when a client layer requests a recreatable chunk, it will automatically be pinned. When the chunk is explicitly unpinned, it is put to the MRU end of the transient part of heaps LRU list. When Oracle searches for unpinned recreatable chunks to be freed, it always starts from transient part of the LRU list, since these are presumably used only once. However, when this only once pinned chunk gets pinned again - the next time it get's unpinned, it will be put to the MRU end of recurrent part of the LRU list. (As I understand, there is one single list for recurrent & transient chunks, there is some kind of marker where transient part becomes recurrent one).
So this allows rarely used chunks to be aged out faster and just once used chunks won't be able to flush out recurrent often-used chunks that easily..
Huh, I planned to write couple of rows for the answer... ;)
Tanel.
>
> -Mark
>
> Mark J. Bobak
> Oracle DBA
> ProQuest Company
> Ann Arbor, MI
> "Post Hoc Ergo Propter Hoc"
>
>
> -----Original Message-----
> From: Riyaj Shamsudeen [mailto:rshamsud_at_jcpenney.com]=20
> Sent: Tuesday, April 06, 2004 11:02 AM
> To: oracle-l_at_freelists.org
> Subject: RE: Largest shared pool
>
>
> Since we are talking about shared pool, I wish, Oracle introduces two
> enhancements in this area:
>
> 1. Have multiple latches for the shared pool governance. Have the
> processes try the latches in a round robin way for n-1 latches and try
> to get the Nth latch in willing-to-wait mode. This should reduce the
> stress on shared pool latch. Having a solitaire latch on shared pool
> *really* gives headache, particularly with the app which does not use
> bind variables.
>
> 2. Have three pools for library cache also: KEEP, RECYCLE and DEFAULT.
> If the # of executions for a SQL is beyond a threshold move them to the
> correct pool. I know, dbms_shared_pool.keep emulates somewhat of the
> KEEP functionality, but we would really love to have RECYCLE and DEFAULT
> functionality also. I am not asking to change the library cache bucket
> structures and as such, but just the way the SQLs are flushed out. May
> be having couple of lists might solve the problem ?
>
> I know, there are many flaws in here, but may be, somebody is working
> on Oracle 11g scoping ;-)=20
>
> Thanks
> Riyaj "Re-yas" Shamsudeen
> Certified Oracle DBA
>
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Tue Apr 06 2004 - 12:13:41 CDT
 |
 |