| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> REPOST: Re: unable to allocate XXXX bytes of shared memory ("shared pool","TEXTINDEXMETHODS"
Mark,
Thank you for the help!!
Using the query on v$shared_pool_reserved (in fail state), I get:
free_space: 244856
avg_free_space: 122428
free_count: 1
max_free_size: 244856
used_space: 5144
avg_used: 2572
used_count: 1
max_used_space: 5144
requests: 1
last_miss_s??: 0
max_miss_s??: 0
request_failures: 0
last_request_failure_size:111
aborted_re??: 4176
last_abort: 214748
Using the query on X$KSMLRU (this was run in non-failure state), I get 3 lines as follows:
addr: 50001C64
indx: 7
inst_id: 1
ksmlrcom: kgldahds ksmlrsiz: 236 ksmlrnum: 8 ksmlrhon: select audit$, options from p... ksmlrohv: 3348238861 ksmlrses: 57DA0F6C
and
addr: 50001CA8
indx: 8
inst_id: 1
ksmlrcom: BAMIMA: Bam Buffer ksmlrsiz: 4132 ksmlrnum: 280 ksmlrhon: declare sel number; ... ksmlrohv: 3437985264 ksmlrses: 57DAF6C
and
addr: 50001CEC
indx: 9
inst_id: 1
ksmlrcom: pl/sql source ksmlrsiz: 4176 ksmlrnum: 960 ksmlrhon: TEXTINDEXMETHODS ksmlrohv: 2154769808 ksmlrses: 57DA0F6C
Does this help at all?
I'll make sure and run the query on X$KSMLRU when it's in fail state next time.
Thanks,
Ken
mark.powell_at_eds.com (Mark D Powell) wrote in message news:<178d2795.0201230620.5e1fd826_at_posting.google.com>...
> 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 --
This message was cancelled from within The Unacanceller's glorious new software, Lotus 1-2-3 For Rogue Cancellers. Received on Thu Jan 24 2002 - 14:52:42 CST
 |
 |