Hmm..reminds me of a MetaLink thread where I actually saw an Oracle Support analyst claim that it's not uncommon for large OLTP systems to have 2-4GB shared pools....yes, that's 2-4 Gigabytes. I wasn't actually sure how to respond to that, so, I just sort of let it drop. I mean, what do you say?
-Mark Mark J. Bobak Oracle DBA ProQuest Company Ann Arbor, MI "Imagination was given to man to compensate him for what he is not, and a sense of humor was provided to console him for what he is." --Unknown -----Original Message----- Sent: Wednesday, January 14, 2004 4:09 PM To: Multiple recipients of list ORACLE-L wa! what kind of application is it? ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> 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:[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). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Goulet, Dick > 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: eric king 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: Bobak, Mark 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).