Re: Largest shared pool

  • From: "Sultan Syed" <ssyed@xxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 7 Apr 2004 10:10:34 +0400

Hi Tanel,
Please clarify me the following

From your reply
"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."

I was in the impression that if heap 0 ,which is freeable chunk,is aged out
the handle also will be aged out since heap 0 will be containing the handle
also.
But your post says that handle will be updated with relevant information.
Means handle still be there ?
And at the time of aging out,what will happen to the cursor which is already
cached
using session_cached_cursors .
Could you  put  your points more on this.
Syed

----- Original Message -----
From: "Tanel Põder" <tanel.poder.003@xxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Tuesday, April 06, 2004 9:17 PM
Subject: 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@xxxxxxxxxxxx]=20
> Sent: Tuesday, April 06, 2004 11:02 AM
> To: oracle-l@xxxxxxxxxxxxx
> 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
>


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: