Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

Re: ora-04031 error

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Wed, 9 Jun 2004 16:14:38 +1000
Message-ID: <40c6aab7$0$13784$afc38c87@news.optusnet.com.au>

"John Wood" <jwood_at_hotmail.com> 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 to
> allocate 18420 bytes of shared memory ("shared pool","unknown object","sga
> 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 fine
> 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 if the memory fragmentation has gotten so bad you can't even log on, you're not 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 allowing 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 intelligently, 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 reserved 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: they 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 dbms_shared_pool 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 a 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 so 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 isn't too big already before trying that, because an over-sized shared pool can be a performance problem.

So yes, there's lots you can do.

Regards
HJR Received on Wed Jun 09 2004 - 01:14:38 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US