RE: [sqr-users] ORA-04031 Error --- How can I resolve this??

["George Jansen" <GJANSEN@aflcio.org>]

oerr ora 4031
04031, 00000, "unable to allocate %s bytes of shared memory
(\"%s\",\"%s\",\"%s\",\"%s\")"
// *Cause:  More shared memory is needed than was allocated in the
shared
//          pool.
// *Action: If the shared pool is out of memory, either use the
//          dbms_shared_pool package to pin large packages,
//          reduce your use of shared memory, or increase the amount
of
//          available shared memory by increasing the value of the
//          INIT.ORA parameters "shared_pool_reserved_size" and 
//          "shared_pool_size".
//          If the large pool is out of memory, increase the INIT.ORA
//          parameter "large_pool_size".  

The in-clauses are a problem because Oracle is parsing your text and
stashing it in the shared pool for possible (as far as it knows) reuse.
Enough huge queries like that, and you _will_ have a problem, though
jacking up SHARED_POOL_SIZE will offer quick relief for now.

If you are on 8i, consider something like

CREATE GLOBAL TEMPORARY TABLE bf_data_tmp ON COMMMIT DELETE ROWS AS
 SELECT bf_fund_cd FROM bf_data WHERE 1=2;

ALTER TABLE bf_data_tmp ADD CONSTRAINT pk$bf_data_tmp PRIMARY KEY
(bf_fund_cd);

Then, rather than build this long dynamic list, you can do a lot of
inserts:

BEGIN-SQL
  INSERT INTO bf_data_tmp(bf_fund_cd) VALUES(#x)
END-SQL

which are relatively lightweight with a GLOBAL TEMPORARY TABLE, and the
select becomes

FROM bf_data bd, bf_data_tmp bdt
WHERE bf.bf_fund_cd = bdt.bf_fund_cd

Or, depending on size of bf_data, it may be more advantageoust to use a
subquery rather than a join. Then you can skip the primary key on the
temporary table and the insert becomes even lighter weight.

Or can you construct your logic so that you don't have to build the
list at all?


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users