Re: ORA-600 Internal shared memory fragmentation - HELP!

From: Peter Moore <pt_at_chaff.demon.co.uk>
Date: 1995/07/21
Message-ID: <288731164wnr_at_chaff.demon.co.uk>#1/1


Barry,

I think this is more evidence that Oracle Support just don't know what they are doing sometimes.

What is happening is that even after FLUSHing your shared pool it fills up again with all the code used by your application. When a user tries to execute a lump of code the parser (or something) looks for room in the pool. If a big enough space has not been cleared by the LRU algorithm then you'll get those errors (or similar ones!).

What you need to do is PIN these functions in the shared pool so that they don't age out. After FLUSHing or as part of the startup script you want to do

  EXECUTE SYS.RDBMS_UTILITIES.KEEP('package_name');

after making sure that your functions are packaged, of course.

These will be lost if you ever flush the pool - along with any other frequently re-used code - as well as when the D/B is taken down; so you should have a script for pinning all your functions, procedures, etc.

BTW, the official line, as far as I am aware, is that you should NEVER flush your shared pool unless you REALLY NEED TO. If your application code is optimally written to be shared between users then flushing will lose it all!!

HTH Regards,

Peter

-- 

-------------------------------------------------------------------

| Peter Moore - Database Administrator - MAT Transport Ltd.
| pt_at_chaff.demon.co.uk : +44 (171) 410 6373
| "With a little study you'll go a long ways & I wish you'd start now!"
Received on Fri Jul 21 1995 - 00:00:00 CEST

Original text of this message