Re: Shared Pool fragmentation

wa! what kind of application is it?

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, January 14, 2004 3:29 PM

> John,
>
> THANKS A TON!!! I've got a vendor trying to convince my boss that their
application needs to be on a separate server with a 1GB shared pool. Now I know these guys are blowing snow better than any SnowKing, but I needed some help proving it.
>
> BTW: For you southern, snow unaware, a SnowKing is a snow blower of the
highest degree.
>
> Dick Goulet
> Senior Oracle DBA
> Oracle Certified 8i DBA
>
> -----Original Message-----
> Sent: Tuesday, January 13, 2004 6:34 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Rick,
>
> I forgot about shared_pool_reserved_size and the min_alloc parameter
(hidden
> since 8i). See Note 146599.1 Diagnosing and Resolving Error ORA-04031.
>
> John
>
> >-----Original Message-----
> >From: John Kanagaraj [mailto:john.kanagaraj_at_hds.com]
> >Sent: Tuesday, January 13, 2004 2:59 PM
> >To: Multiple recipients of list ORACLE-L
> >Subject: RE: Shared Pool fragmentation
> >
> >
> >Rick,
> >
> >I think the best answer is 'know thy application'. And in
> >this, knowledge of
> >bind var vs hardcoded value usage, looking at V$SQL and
> >V$SQLAREA, the ratio
> >(!!) of 'parse count (hard)' to 'parse count (total)', pinning of
> >packages/sequences, etc., can help...
> >
> >You cannot actually 'catch' a 4031 before it occurs, but you can always
> >straighten things out before it occurs. I have found that a
> >combination of
> >pinning Packages/Sequences followed by judicious (once in a
> >while) use of
> >shared pool flush helps. Of course, the shared pool has to be correctly
> >sized - too much and you waste time latching and memory, too
> >little and you
> >_might_ run into 4031. Sizing shared pool is an art that has a little
> >science behind it - science that involves understanding and
> >using values
> >from X$KGLOB and X$KSMSP and your application....
> >
> >OTOH, I have seen good results with a flush shared pool during
> >quiet times
> >for non-bind hungry 3rd party apps... See below (script
> >courtersy Steve!) -
> >the number of chunks has dropped dramatically freeing up
> >largish globs of
> >shared pool that would otherwise have to be freed up when a
> >largish object
> >(in this case > 15456 bytes) has to load. As well, you will
> >see that the
> >number of 'freeabl' chunks (x$ksmsp.ksmchcls) comes down
> >drastically as the
> >system frees up 'freeable' chunks ahead of time, reducing the chance of
> >4031s....
> >
> >My (very limited) understanding is that when a package/cursor
> >has to load
> >and a large-enough chunk of shared pool memory is not free,
> >then the kernel
> >will try and flush out the 'freeable' (not in use) memory and
> >merge adjacent
> >free chunks. If this still does not staisfy the memory
> >requirements, then a
> >4031 is signalled/ The 'alter system flush shared pool'
> >performs a manual
> >flush instead, ahead of time and could (possibly) prevent a 4031 ...
> >
> >John Kanagaraj
> >DB Soft Inc
> >Phone: 408-970-7002 (W)
> >
> >Listen to great, commercial-free christian music 24x7x365 at
> >http://www.klove.com
> >
> >** The opinions and facts contained in this message are
> >entirely mine and do
> >not reflect those of my employer or customers **
> >
> >08:35:00 SQL> @shared_pool_free_lists
> >
> > BUCKET FREE_SPACE FREE_CHUNKS AVERAGE_SIZE BIGGEST
> >---------- ---------- ----------- ------------ ----------
> > 0 1089784 23488 46 76
> > 1 394136 4656 84 140
> > 2 681284 3678 185 268
> > 3 315504 875 360 524
> > 4 4901952 7300 671 1036
> > 5 6158896 4099 1502 2060
> > 6 5546516 1966 2821 4048
> > 7 1125720 263 4280 7624
> > 8 989584 101 9797 15456
> >
> >9 rows selected.
> >
> >08:35:29 SQL> alter system flush shared_pool;
> >
> >System altered.
> >
> >08:36:32 SQL> @shared_pool_free_lists
> >
> > BUCKET FREE_SPACE FREE_CHUNKS AVERAGE_SIZE BIGGEST
> >---------- ---------- ----------- ------------ ----------
> > 0 14364 330 43 76
> > 1 6528 76 85 140
> > 6 3964 1 3964 3964
> > 9 29580 1 29580 29580
> > 10 5028636 103 48821 65436
> > 11 13860744 150 92404 130872
> > 12 32192980 173 186086 261016
> > 13 64490864 172 374946 522764
> > 14 83609184 112 746510 1048432
> > 15 79829220 57 1400512 2068384
> > 16 38149220 14 2724944 3705320
> >
> >11 rows selected.
> >
> >-----Original Message-----
> >Sent: Tuesday, January 13, 2004 9:34 AM
> >To: Multiple recipients of list ORACLE-L
> >
> >
> >Is there a way to catch shared_pool fragmentation before you
> >get the 4031
> >errors? I have looked at Steve Adams site which has scripts
> >to show the
> >free lists chunks in the shared pool. At what point do I know
> >that it is
> >fragmented too much? I know that I can prevent this by using bind
> >variables, and keeping objects, but until I can modify all the
> >apps, I would
> >like to know a little before these errors happen. Any ideas?
> >
> >Thanks,
> >
> >Rick Stephenson
> >
> >
> >
> >This email and any files transmitted with it are confidential
> >and intended
> >solely for the use of the individual or entity to which they
> >are addressed.
> >This message contains confidential information and is intended
> >only for the
> >individual named. If you are not the named addressee you should not
> >disseminate, distribute or copy this e-mail. Please notify the sender
> >immediately by e-mail if you have received this e-mail by
> >mistake and delete
> >this e-mail from your system. If you are not the intended
> >recipient you are
> >notified that disclosing, copying, forwarding or otherwise
> >distributing or
> >taking any action in reliance on the contents of this information is
> >strictly prohibited.
> >--
> >Please see the official ORACLE-L FAQ: http://www.orafaq.net
> >--
> >Author: John Kanagaraj
> > INET: john.kanagaraj_at_hds.com
> >
> >Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> >San Diego, California -- Mailing list and web hosting services
> >---------------------------------------------------------------------
> >To REMOVE yourself from this mailing list, send an E-Mail message
> >to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> >the message BODY, include a line containing: UNSUB ORACLE-L
> >(or the name of mailing list you want to be removed from). You may
> >also send the HELP command for other information (like subscribing).
> >
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: John Kanagaraj
> INET: john.kanagaraj_at_hds.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Goulet, Dick
> INET: DGoulet_at_vicr.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: eric king
  INET: erickingus_at_hotmail.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).