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=58415
Bytes=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=58415
Bytes=6308820)
> ----------
> From: Stephane Faroult[SMTP:[EMAIL PROTECTED]]
> Reply To: [EMAIL PROTECTED]
> 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: [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).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Baker, Barbara
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).
