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

Barry Roomberg (barry_roomberg_at_iacnet.com) wrote:

: I've been running 7.1.4.1 for about a month now under SCO 3.2.4.2
: and have been pretty happy. I've just started to play with
: user defined functions, and really like them.
 

: Just as my code that uses the user defined functions went
: into production use, with 30 users executing my new function around
: 30 times an hour PER user, Oracle starting barfing with the following
: errors:
 

: ORA-00600: internal error code, arguments: [15428],
: [PLS Error during synonym translation],[],[],[],[],[],[]
: ORA-04031: unable to allocate 9712 bytes of shared memory
: ("STANDARD","PL/SQL MPCODE","BAMIMA: Grow Bam Buffer")
 

: I saw 1 user executing my code get this error.
 

: It also showed up in the RDBMS/LOGS/ALERT file, but I'm not
: sure which SQL statement triggered it.
 

: Oracle support says this is due to shared memory fragmentation and
: had me run the following SQLDBA command:
 

: ALTER SYSTEM FLUSH SHARED_POOL;
 
: They said run the ALTER daily. Didn't help, got the same error
: the next day.
 

: They also told me to double my SHARED_POOL_SIZE
: from 1.7MB to 3.5MB. Haven't seen the error since
: I upped it, but that is only with 4 hours of operation
: behind me. Seems that upping this type of parameter
: will only delay the inevitable.
 

: They said that it has NOTHING to do with me user defined function,
: but I detect a strong cause/effect relationship do to the timing
: of the error showing up.
 

: Anybody experience this problem? If so, what did you do?!?!?

pin your packages, procedures and functions immediately after every reboot of your database so that all that code is in the beginning of the shared_pool and never gets pushed out later.

what's happening here is that your shared_pool is getting fragmented due to code being loaded and pushed out based on internal LRU algorithms. If you pin these often used functions and procedures into the share_pool at the very beginning, the are marked and kept in there for good. And since you are doing so when the shared pool is completely fresh (after a db reboot), you are ensuring they are all in the beginning of the shared pool area and will reduce the likelihood of fragmentation. As you suspected, flushing your shared_pool or making it bigger will only delay the inevitable.

check out $ORACLE_HOME/rdbms/admin/dbsmpool.sql for further info and instructions for how to pin (and unpin) packages. I have a script which just pins *everything* into the shared pool right of the bat (I also have the luxery of being able to have a huge shared pool, so my hit ratio is still very good).

Johnny Chan
Independent Oracle Specialist Received on Mon Jul 24 1995 - 00:00:00 CEST