| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: ERR: ORA-04031 unable to allocate
Stephane:
Today, original query either runs in 1 minute, or errors with 4031 in 1
minute. I find that odd . . .
Regardless, your examples are quite lovely! The first returns the result set in less than 1 second.
Thanks for taking the time to look at this, and for the code. I really
appreciate it!
Barb
(If you're interested . . . I've attached the explain plans for the
original, and for your 2 suggestions.)
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3321 Card=68805 Bytes=11628045)
1 0 SORT (UNIQUE) (Cost=3321 Card=68805 Bytes=11628045)
2 1 HASH JOIN (Cost=1605 Card=68805 Bytes=11628045) 3 2 TABLE ACCESS (FULL) OF 'VOLUNTARY_REPS' (Cost=1 Card=82 Bytes=2460) 4 2 HASH JOIN (Cost=1602 Card=83909 Bytes=11663351) 5 4 TABLE ACCESS (BY INDEX ROWID) OF 'PUB' (Cost=641 Card=83909 Bytes=2601179) 6 5 INDEX (RANGE SCAN) OF 'I_PUB_STATE' (NON-UNIQUE) (Cost=354 Card=83909) 7 4 TABLE ACCESS (FULL) OF 'AD' (Cost=433 Card=58415Bytes=6308820)
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=147 Card=2395 Bytes=330510)
1 0 FILTER
2 1 NESTED LOOPS (Cost=147 Card=2395 Bytes=330510) 3 2 TABLE ACCESS (FULL) OF 'AD' (Cost=433 Card=2921 Bytes=315468) 4 2 TABLE ACCESS (BY INDEX ROWID) OF 'VOLUNTARY_REPS' (Cost=1 Card=82 Bytes=2460) 5 4 INDEX (RANGE SCAN) OF 'VOL_REPS_IDX1' (NON-UNIQUE) 6 1 TABLE ACCESS (BY INDEX ROWID) OF 'PUB' (Cost=1 Card=1 Bytes=31) 7 6 INDEX (RANGE SCAN) OF 'I_PUB1' (UNIQUE) (Cost=3 Card=1)
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2025 Card=68805 Bytes=11284020)
1 0 HASH JOIN (Cost=2025 Card=68805 Bytes=11284020) 2 1 TABLE ACCESS (FULL) OF 'VOLUNTARY_REPS' (Cost=1 Card=82 Bytes=2460)
3 1 HASH JOIN (Cost=2022 Card=83909 Bytes=11243806) 4 3 VIEW (Cost=1122 Card=83909 Bytes=2181634) 5 4 SORT (UNIQUE) (Cost=1122 Card=83909 Bytes=2601179) 6 5 TABLE ACCESS (BY INDEX ROWID) OF 'PUB' (Cost=641 Card=83909 Bytes=2601179) 7 6 INDEX (RANGE SCAN) OF 'I_PUB_STATE' (NON-UNIQUE)(Cost=354 Card=83909) 8 3 TABLE ACCESS (FULL) OF 'AD' (Cost=433 Card=58415Bytes=6308820)
> ----------
> From: Stephane Faroult[SMTP:sfaroult_at_oriole.com]
> Reply To: ORACLE-L_at_fatcity.com
> Sent: Friday, February 15, 2002 1:53 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: ERR: ORA-04031 unable to allocate
>
> "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")
> >
> > 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).
>
-- 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).Received on Fri Feb 15 2002 - 10:17:18 CST
 |
 |