RE: Shared Pool fragmentation

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).