Re: Work Around ora-604 ?

In article <3kq3dv$c93_at_hustle.rahul.net>, Dan Bikle <dbikle_at_rahul.net> writes:
|> Hi there,
|>
|> I'm dba of a database which grinds to a halt with ora-604 messages.
|>
|> The only fix is to SHUTDOWN/STARTUP 2 or 3 times a day.
|>
|> Were are running 7.1.3
|>
|> The word from Tech support is the shared sql area is getting fragmented.
|>
|> I ran
|>
|> set serveroutput on size 123456
|> execute sys.dbms_shared_pool.sizes (1);
|>
|> and got page after page after page of output.
|>
|> The application running against the dbs generates thousands of small,
|> unique sql statements.
|>
|> So, these small sql statements fill the shared sql area.
|>
|> Then once in awhile, a large sql statement or procedure comes along.
|> The rdbms tries to load it into the shared sql area, but cannot find
|> a contiguous chunk of shared memory large enough to do hold the item.
|> Then, the rdbms coughs up a fur ball (ora-604).
|>
|> You know of a work-around?
|>
|> -Dan
|> ---------------------------------------------------------------------------
|> Daniel B. Bikle/Independent Oracle Consultant
|> dbikle_at_alumni.caltech.edu | 415/854-9542 | P.O. BOX 'd' MENLO PARK CA 94026
|> ---------------------------------------------------------------------------
|>

Try flushing the shared pool with ALTER SYSTEM FLUSH SHARED POOL. You can also pin commonly used procedures/packages with dbms_shared_pool.keep(). Received on Thu Mar 23 1995 - 18:25:45 CET