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:[EMAIL PROTECTED] >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: [EMAIL PROTECTED] > >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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
