RE: ERR: ORA-04031 unable to allocate

Barbara,

What is the size of
shared_pool_size and
shared_pool_reserved_size..

If shared_pool_reserved_size not set then set it 10% of your shared_pool. In 8i default is 5% of shared_pool if not set. In 7.3.4 default was most possibly 0(zero)...

I hope this may help in resolving your shared pool problem...

As regard querry, it definately requires attention whether it is using indexes properly and indexes themselves are not much fragmented and might require rebuilding..I shall go to check indexes of all relevant tables and it is rebuilding before changing any init.ora parameters...

Regards
Rafiq

Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: Fri, 15 Feb 2002 08:33:29 -0800

Barbara - Been there done that. I have stubbornly made the same statements. This is why some co-workers say DBAs are cantankerous. My best guess is that there is something about that query. Give up, increase SHARED_POOL_SIZE. Then your coworkers will start saying how easy you are to get along with. Dennis Williams
DBA (now the friendly DBA)
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Friday, February 15, 2002 9:14 AM
To: Multiple recipients of list ORACLE-L

Jared:
I did look at this article.
I'd buy that I have a shared pool problem if I were seeing other problems in the database.
However, I'm still confused that I see the problem only with this 1 query, that it's so easily reproducible only for this query, and the the other 100-or-so users in the database have not experienced problems.

The database was bounced last night. I did increase open_cursors, just for grins. I'll take a look this morning and see how things look. Thanks!
Barb

> ----------
> From: Jared.Still_at_radisys.com[SMTP:Jared.Still_at_radisys.com]
> Reply To: ORACLE-L_at_fatcity.com
> Sent: Thursday, February 14, 2002 6:08 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: ERR: ORA-04031 unable to allocate
>
> see
>

http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_dat
> abase_id=NOT&p_id=146599.1
>
> Jared
>
>
>
>
>
>
>
> "Baker, Barbara" <bbaker_at_denvernewspaperagency.com>
> Sent by: root_at_fatcity.com
> 02/14/02 04:03 PM
> Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> cc:
> Subject: ERR: ORA-04031 unable to allocate
>
>
> Oracle 8.0.5
> Solaris 2.6
>
> List:
> One of our users is getting this error message running a query:
>
> ERROR:
> ORA-04031: unable to allocate 4194304 bytes of shared memory ("shared
> pool","unknown object","cursor work he","KKRH Hash Table")
>
> The query is very simple (see below). Happens consistently. Error
occurs
> after about 1 minute of execution. No other problems in the database (no
> errors in alert, etc.)
>
> I'm seeing latch wait this for the sid involved:
>
>
>
>
>
> SID EVENT P1TEXT P1 P2TEXT P2 P3TEXT
> P3
> ----- ---------------- ---------- ----------- -------- -------- --------
> ------
> 409 latch free address 2147519876 number 59 tries
> 0
> 1 pmon timer duration 300 0
> 0
> 6 smon timer sleep time 300 failed 0
> 0
>
>
>
> and then I see this for the same sid in v$lock (resource is type+id1+id2,
> query is from Steve Adams' site)
>
>
>
>
> RESOURCE NSID SID HOLDING WANTING SECONDS
> -------------------- ----- ---- ------- ------- ----------
> CU--1595636348-0 409 409 X 0
> RT-1-0 4 LGWR X 0
> TS-1-8388610 6 SMON SX 55604
>
>
>
> According to Anjo Kolk's description, a CU is a Bind enqueue. Does this
> mean I'm running out of open_cursors?? (And if so, why are no other
> processes affected?)
>
> Anyone seen this one before?
>
> Thanks for any help!
> Barb
>
>
> Here's the query:
>
> select distinct
> v.sales_id,
> a.receiver,
> a.adno,
> a.unet,
> a.vno,
> a.enddate
> from advdb.ad a,
> advdb.pub p,
> advdb.voluntary_reps v
> where a.adno = p.adno
> and a.vno = p.vno
> and a.receiver = v.name
> and p.state = 'VAR'
> and p.vnoflag = 'Y'
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Baker, Barbara
> INET: bbaker_at_denvernewspaperagency.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: Jared.Still_at_radisys.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Baker, Barbara
   INET: bbaker_at_denvernewspaperagency.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: DENNIS WILLIAMS
   INET: DWILLIAMS_at_LIFETOUCH.COM

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




MOHAMMAD RAFIQ


_________________________________________________________________
Send and receive Hotmail on your mobile device: http://mobile.msn.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mohammad Rafiq
  INET: rafiq9857_at_hotmail.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).