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

biggs_at_lone-star.net (Ken) wrote in message news:<150b6aa0.0201241252.6d1741ff_at_posting.google.com>...
> 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
>

Ken, on instance start up are you pinning your large packages and the heavy hit Oracle provided packages? If not you should do this using dbms_shared_pool.keep procedure.

If you have support go to metalink.oracle.com (support button off Oracle home site) and perform an advanced search on ORA-04031. Remove the forums from your search path to reduce the quanity of garbage returned and you will find some white papers on the subject.

You did not answer my question on why you had the reserved pool sized at 50% of the shared pool. The default is only 10% but it is not unusual to need to make it 20% or maybe 30% but 50% seems unusual. I asked because you could be starving the small object section of the pool, but it is impossible for me to say without really looking at everything related to the matter and that is not possible.

Once again there were over 20 fixes to space management problems between 8.1.7.0 and 8.1.7.2 so you should look at upgrading if you cannot isolate the problem. If this is a severe problem an iTar may be in order.

• Mark D Powell --
  • WAS CANCELLED BY =======: From: mark.powell_at_eds.com (Mark D Powell) Control: cancel <178d2795.0201250646.2b61c564_at_posting.google.com> Subject: cmsg cancel <178d2795.0201250646.2b61c564_at_posting.google.com> Date: Mon, 28 Jan 2002 00:53:53 GMT Message-ID: <cancel.178d2795.0201250646.2b61c564_at_posting.google.com> X-No-Archive: yes Newsgroups: microsoft.test,alt.flame.niggers,comp.databases.oracle.misc NNTP-Posting-Host: w088.z064003087.lax-ca.dsl.cnc.net 64.3.87.88 Lines: 1 Path: news.uni-stuttgart.de!news.fh-hannover.de!fu-berlin.de!area.cu.mi.it!news.mailgate.org!news2.euro.net!news.stealth.net!msrtrans1!msrnewsc1!cppssbbsa01.microsoft.com!tkmsftngp01!tkmsftngp03!u&n&a&c&anceller Xref: news.uni-stuttgart.de control:40716344

This message was cancelled from within The Unacanceller's glorious new software, Lotus 1-2-3 For Rogue Cancellers. Received on Fri Jan 25 2002 - 08:46:02 CST