Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: ora-04031 error

Re: ora-04031 error

From: John Wood <>
Date: Sat, 12 Jun 2004 22:22:02 GMT
Message-ID: <emLyc.12773$lN.8898@edtnps84>

Thanks for the info.

"Howard J. Rogers" <> wrote in message news:40c6aab7$0$13784$
> "John Wood" <> wrote in message
> news:XCxxc.1898$cS.1398_at_edtnps89...
> > I have Oracle 9202 database on window2000. One morning I noticed that
> the
> > database was down. In the .trc trace file it showed "ORA-04031:unable
> > allocate 18420 bytes of shared memory ("shared pool","unknown
> > heap(1,0)","session param values")". When I tried to log on with SYS
> > account, I got the same error message.
> >
> > I shutdown and restart the win2000 server, then the database started
> > and I managed to log on with dba account.
> >
> > My question is: Is there any other way I can do to start the database
> other
> > than the win2000 restart ?
> In principle, yes: alter system flush shared_pool;
> But the catch is that you have to be logged on to issue that command, and
> the memory fragmentation has gotten so bad you can't even log on, you're
> going to have too many choices, really.
> > Can I find out which job or sql statement caused
> > it to happen ? What can I do to prevent it from happening ?
> You can do lots of things. You can write SQL that is shareable, by
> it to use bind variables instead of literals. Shareable SQL can share the
> same execution plans, which means you don't age old one-off plans out, and
> if you don't age things out of memory, you don't fragment it. If you can't
> modify your appplication code so that bind variables are used
> you might be able to get some benefit from setting CURSOR_SHARING=SIMILAR.
> Then you can try and arrange for large memory allocations from the shared
> pool to be made in a different area of the pool from small ones. Set
> shared_pool_reserved_size to some higher value than it already is at, and
> memory allocations over a certain threshold are then made from the
> pool, not the general pool. The idea there is that fragmentation is what
> happens when lots of little things pinch memory, and then get aged out:
> leave behind little 'holes' of free memory, which are too small for
> something more substantial to make use of. So if you keep the small stuff
> separate from the big stuff, it should help.
> Then you can try pre-loading your shared pool at startup and preventing
> things from being aged out of it. You need to have run dbmspool.sql in
> \rdbms\admin if you haven't already done so: that creates a
> package. One of whose procedures is "keep". If you keep something in the
> pool, it can't be aged out. If it can't be aged out, it can't leave behind
> memory 'hole', and hence can't cause fragmentation. If you then write a
> trigger that says something like:
> create trigger keepme
> after startup on database
> begin
> dbms_shared_pool.keep('XXX');
> dbms_shared_pool.keep('YYY');
> dbms_shared_pool.keep('ZZZ');
> ...and so on...
> end;
> /
> And you list every one of your application's major packages in the body of
> that trigger, such that most of your application code can't be aged out of
> the library cache/shared pool and thus cause fragmentation. The nice side
> effect of this is that the trigger fires immediately on startup; a package
> cannot be "kept" until it has been loaded into the pool; therefore the
> trigger pre-warms your library cache for you.
> And then of course, you could try increasing the size of your shared pool
> that although fragmentation still takes place, it takes a lot longer to
> become a major problem. However, you would want to make sure your pool
> too big already before trying that, because an over-sized shared pool can
> a performance problem.
> So yes, there's lots you can do.
> Regards
Received on Sat Jun 12 2004 - 17:22:02 CDT

Original text of this message