Re: ERR: ORA-04031 unable to allocate

From: Stephane Faroult <sfaroult_at_oriole.com>
Date: Fri, 15 Feb 2002 01:13:24 -0800
Message-ID: <F001.00410B0D.20020215005322@fatcity.com>

"Baker, Barbara" wrote:
>
> 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'
>

Barbara,

   I am always suspicious of 'very simple queries' which run for a minute, whether they fail with a mysterious error or they don't. Moreover, I always jump on my bazooka any time I see a SELECT DISTINCT, especially with a join. To me, the execution plan could probably have shed more light than V$LOCK. I guess that the poor beast is just exhausting itself trying the impossible.  If I were you, I would try something such as :

 select v.sales_id,
        a.receiver,
        a.adno,
        a.unet,
        a.vno,
        a.enddate
  from advdb.ad a,
       advdb.voluntary_reps v

  where a.receiver = v.name
    and exists (select null

                from advdb.pub p
                where p.adno = a.adno
                  and p.vno = a.vno
                  and p.state = 'VAR'
                  and p.vnoflag = 'Y')
 

or (probably better)

select v.sales_id,
       a.receiver,
       a.adno,
       a.unet,
       a.vno,
       a.enddate
from advdb.ad a,
     advdb.voluntary_reps v
where (a.adno, a.vno) in (select p.adno, p.vno
                          from advdb.pub p
                          where p.state = 'VAR'
                            and p.vnoflag = 'Y')

    and a.receiver = v.name

or possibly something else involving inline views. The best solution depends of course on the volume of data returned and which columns are indexed. You may well solve your problem with a query which will run much faster.

-- 
Regards,

Stephane Faroult
Oriole Ltd
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.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 Fri Feb 15 2002 - 03:13:24 CST