"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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
