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
