RE: ORA-04031 unable to allocate

From: Vergara, Michael (TEM) <mvergara_at_guidant.com>
Date: Thu, 14 Feb 2002 17:00:59 -0800
Message-ID: <F001.004107CA.20020214165828@fatcity.com>

Barbara:

You need to:
1) Have the user log out, log back on, and try again. If that

fails ...

2)  Bounce the instance.  Before you restart, you should ...
3)  Increase the size of your shared pool, and maybe ...
4)  Increase the open_cursors parameter value.  The Open_Cursors

parameter is a *session* parameter, not an *instance* parameter, so this user may really be running out of cursors.

The message seems to indicate that it's trying to allocate space for a hash join, and it cannot. You might check the value of hash_area_size, and maybe increase that, too.

HTH,
Mike

-----Original Message-----
Sent: Thursday, February 14, 2002 4:04 PM To: Multiple recipients of list ORACLE-L

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

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: Vergara, Michael (TEM)
INET: mvergara_at_guidant.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). Received on Thu Feb 14 2002 - 19:00:59 CST