Thanks for the info.
JW
"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message
news:40c6aab7$0$13784$afc38c87_at_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 Sat Jun 12 2004 - 17:22:02 CDT
