Barbara,
This might be an obvious comment, but isn't the where clause missing a few join conditions?
eg - these 3 tables don't seem to get mentioned:
ADMARC.CNR CNR,
ADMARC.WOEMANIFEST_VIEW WOEMANIFEST_VIEW,
ADMARC.WO_CHARG_VIEW WO_CHARG_VIEW
Regards,
Bruce Reardon
-----Original Message-----
Sent: Friday, 28 February 2003 9:51 AM
I found it in our "offending sql statements" report we
get from the database each morning. Went looking when
someone complained about poor performance. (Ya, do
you THINK!!!!) Probably not Discoverer. I think it
was either Access or Crystal. (When I found it, it
had double quotes around everything, which is usually
Access.)
On the bright side, our BHR looks great!!! Guess I'll
go home and have a beer.
For Jared's entertainment, here's the sql:
SELECT WO.ACCT_KEY, WO.PUB, WO.ISSUE, WO.STATUS,
WO.AD_TYPE, WO.EDITION, WO.SECTION, WO.AD_DESC,
WO.COMMENTS, WO.JOB_NBR, WO.ACT_SIZE, WO.BASE_AMT,
WO.DISC_TOT,
WO.AGY_COMM, WO.SP_AGY_COMM, WO.INVC_AMT, WO.FREQ,
WO.CIRCULATION,
WO.RC_NBR, WO.AVG_RATE, NAD.NAM1, NAD.PROD_4,
PUB.SLS_EFF_ISS_1, PUB.SLS_NBR_1_1, PUB.SLS_EFF_ISS_2,
PUB.SLS_NBR_2_1, PUB.SLS_NBR_3_1, CNR.SLS_NBR_1,
WOEMANIFEST_VIEW.WOE_EDITION, WO_CHARG_VIEW.PROD1,
WO_CHARG_VIEW.AMT1, WO_CHARG_VIEW.PROD2,
WO_CHARG_VIEW.AMT2, WO_CHARG_VIEW.PROD3,
WO_CHARG_VIEW.AMT3, WO_CHARG_VIEW.PROD4,
WO_CHARG_VIEW.AMT4, WO_CHARG_VIEW.AMT5,
WO_CHARG_VIEW.AMT6, WO_CHARG_VIEW.AMT7,
WO_CHARG_VIEW.AMT8, WO_CHARG_VIEW.AMT9
FROM ADMARC.WO WO,
ADMARC.NAD NAD,
ADMARC.PUB PUB,
ADMARC.CNR CNR,
ADMARC.WOEMANIFEST_VIEW WOEMANIFEST_VIEW,
ADMARC.WO_CHARG_VIEW WO_CHARG_VIEW
WHERE WO.ACCT_KEY = NAD.ACCT_NBR (+)
AND WO.ACCT_KEY = PUB.ACCT_KEY
- John Shaw <John.Shaw_at_correctionscorp.com> wrote:
> This wouldn't happen to be Discoverer would it?
>
> >>> mrichard_at_transurban.com.au 02/27/03 03:24PM >>>
> Barbara,
>
> Don't worry about the explain plan... What's the
> BHR looking like?
>
> I guess in all seriousness this query is most likely
> missing some joins
> given the CARD result and the CARTESIAN's in the
> plan. Have fun. Oh, and
> is the CNR table analyzed?
>
> Regards,
> Mark.
>
>
>
>
>
>
> Barbara Baker
>
>
> <barbarabbaker@ To:
> Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> yahoo.com> cc:
>
>
> Sent by: Subject:
> Purely for your amusement
>
> root_at_fatcity.co
>
>
> m
>
>
>
>
>
>
>
>
> 28/02/2003
>
>
> 05:23
>
>
> Please respond
>
>
> to ORACLE-L
>
>
>
>
>
>
>
>
>
>
>
>
> And you thought your job was bad . . .
> (Yes, this IS a production job)
>
> 0 SELECT STATEMENT Optimizer=CHOOSE
> (Cost=623898383366807000000000
> Card=81143391896031200000000000000
> 1 0 MERGE JOIN
> (Cost=623898383366807000000000
> Card=81143391896031200000000000000
> Bytes=535546386513806
> 2 1 SORT (JOIN)
> 3 2 MERGE JOIN (CARTESIAN)
> (Cost=1323033411994580000000
> Card=518075818229206000000000 Bytes=321725
> 4 3 MERGE JOIN (CARTESIAN)
> (Cost=190827283721623000 Card=28145586908741600000
> Bytes=150578889961
> 5 4 MERGE JOIN (OUTER)
> (Cost=301983192525 Card=40967578733025
> Bytes=21385076098639100)
> 6 5 MERGE JOIN (Cost=301983182527
> Card=40967578733025 Bytes=19377664740720800)
> 7 6 SORT (JOIN)
> 8 7 MERGE JOIN (CARTESIAN)
> (Cost=2532679282 Card=1590809983140
> Bytes=322934426577420)
> 9 8 VIEW OF 'WO_CHARG_VIEW'
> (Cost=725242 Card=2615655 Bytes=347882115)
> 10 9 SORT (GROUP BY)
> 11 10 TABLE ACCESS (BY
> ROWID) OF 'WO_CHARG' (Cost=690469 Card=2615655
> Bytes=68007030
> 12 11 INDEX (FULL SCAN)
> OF
> 'PK_WO_CHARG' (UNIQUE)
> 13 8 SORT (JOIN)
> 14 13 TABLE ACCESS (FULL) OF
> 'PUB' (Cost=968 Card=608188 Bytes=42573160)
> 15 6 SORT (JOIN)
> 16 15 TABLE ACCESS (FULL) OF
> 'WO'
> (Cost=190903 Card=15662455 Bytes=4228862850)
> 17 5 SORT (JOIN)
> 18 17 TABLE ACCESS (FULL) OF 'NAD'
> (Cost=2792 Card=500401 Bytes=24519649)
> 19 4 SORT (JOIN)
> 20 19 TABLE ACCESS (FULL) OF 'CNR'
> 21 3 SORT (JOIN)
> 22 21 TABLE ACCESS (FULL) OF 'WOE'
> (Cost=47 Card=18407 Bytes=1583002)
> 23 1 SORT (JOIN)
> 24 23 TABLE ACCESS (FULL) OF 'WO'
> (Cost=190903 Card=15662455 Bytes=610835745)
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Reardon, Bruce (CALBBAY)
INET: Bruce.Reardon_at_comalco.riotinto.com.au
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
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 27 2003 - 17:11:49 CST
