Re: Shared Pool Size (Hard to Explain)

Here's something you may want to do:

startup sqldba

run following command at sqldba prompt:
1. alter system flush shared_pool;

(this command will flush all sql/pl-sql statements cached in shared_pool
    of sga)

2. start your application.
3. at sqldba start monitoring using following:
4. set serveroutput on;
5. execute dbms_shared_pool.sizes(ip_size);

(Above command with display all objects cached in shared_pool with
   size greater than or equal to ip_size(kilobytes). 6. If at any point a large object(cursor,sql_statement,procedure) has

   to be cached or brought-in and if ORACLE doesnot find contiguous memory    blocks even after aging out other small objects from shared_pood, chances    are you'll get ORA-04031 message.
(Also check, if you application is "keeping" a particular database object
   locked in shared_pool. This is possible using dbms_shared_pool.keep    procedure, if so, you may want to reconsider sizing your    shared_pool_size in sga).

   more information about dbms_shared_pool package can be found in    /oracle/rdbms directory.

Hope it is helpful.
(If get real sound solution to the problem, let us know)

• sanjay d.s.