Re: unable to allocate XXXX bytes of shared memory ("shared pool","TEXTINDEXMETHODS"

biggs_at_lone-star.net (Ken) wrote in message news:<150b6aa0.0201221421.7436c45b_at_posting.google.com>...
> Could sure use some help,
>
> Running oracle ver: 8.1.7.0.1 on Redhat Linux ver: 6.2
> kernal version: 2.2.16-4.lfssmp on dual processor PIII 1 ghz
> w/ 2 gig ram & 4 gig of swap
>
> Doing lots of context searches on a table w/2.5 million records.
>
> Get lots of these in the java log:
> ORA-04031: unable to allocate 4124 bytes of shared memory ("shared
> pool","TEXTINDEXMETHODS","pl/sql source","pl/sql source")
>
> A few of these:
> ORA-0431: unable to allocate 4096 bytes of shared memory ("shared
> pool,"CTXQUERY","PL/SQL MPCODE","BAMIMA: Bam Buffer")
>
> here's the stuff from init.ora
>
> db_files = 1500
> db_file_multiblock_read_count = 32
> db_block_buffers = 3200
> shared_pool_size = 200000000
> shared_pool_reserved_size = 100000000
> large_pool_size = 6144000
> java_pool_size = 20971520
> log_checkpoint_interval = 10000
> processes = 200
> parallel_max_servers = 8
> log_buffer = 163840
> max_dump_file_size = 10240
>
> I can't figure out how to optimize memory setup for oracle - way to
> complex for me. Thanks for any help!!
>
> Ken

Ken, why do you have the reserved size set to half your pool? How full is the reserved area? Query v$shared_pool_reserved. Do you have sql causing flushing?
Here is some code to help gather information. Also there are at least 20 fixes to shared pool memory management between version 8.1.7.0 and 8.1.7.2 so upgrading may be wise. Once you run the query I would go to metalink and perform an advanced search on 04031 skipping the forum entries and look for any bug reports that may apply to your situation.

set echo off

--
-- SQL to show shared investigate shr pool memory prob
--     Based on Oracle support tar response
--   a) show stat for reserved area
--   b) show sql causing flushing
--
--  Note - You must be sys to run this code, uses x$ table.
--
-- 20010627  Mark D Powell   Save code to show shr pool
--
col avg_free_size heading 'AVG|FREE|SIZE'
col avg_used_size heading 'AVG|USED|SIZE'
spool pool_flushed

SELECT
  free_space
 ,avg_free_size
 ,used_space
 ,avg_used_size
 ,request_failures
 ,last_failure_size
FROM  v$shared_pool_reserved;

-- code that causes code to be flushed from the shared pool
-- selecting against the ksmlru fixed table flushes its contents
SELECT
  *
FROM   X$KSMLRU
WHERE  ksmlrsiz > 0;

spool off

If you find your reserved area has free space larger than the amount
being asked for when the error occurs then you may need to set the
threshold where an object is considered large, which is now an
underbar parameter in 8.1.7.2.  See thread on
shared_pool_reserved_min_alloc for SQL to see default value.

HTH

-- Mark D Powell --