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