As others have pointed out 6 tables, 2 joins does not a happy execution plan make - and it also looks like some of your tables are views to add to the mix.
My diagnosis, this is an ad-hoc query constructed by an end user. To find the source of the problem you'll need to listen carefully, the loudest whine will be coming from the source of the problem. If this turns out to be the CEO, some disgression may be called for.
On the bright side if you save out the sql, then any time someone complains about the ratios you can always run it a few times till performance is better :(.
Niall Litchfield
Oracle DBA
Audit Commission
0117 975 7805
-----Original Message-----
Sent: 27 February 2003 22:51
To: barbarabbaker_at_yahoo.com; ORACLE-L_at_fatcity.com
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)
>
>
>
> __________________________________________________
> Do you Yahoo!?
> Yahoo! Tax Center - forms, calculators, tips, more
> http://taxes.yahoo.com/
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> --
> Author: Barbara Baker
> INET: barbarabbaker_at_yahoo.com
>
> 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).
>
>
>
>
>
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
> Privileged/Confidential information may be
> contained in this message.
> If you are not the addressee indicated in
> this message
> (or responsible for delivery of the message
> to such person),
> you may not copy or deliver this message
> to anyone.
> In such case, you should destroy this message and
> kindly notify the sender
> by reply e-mail or by telephone on (61 3) 9612-6999.
> Please advise immediately if you or your employer
> does not consent to
> Internet e-mail for messages of this
> kind.
> Opinions, conclusions and other information
> in this message
> that do not relate to the official
> business of
> Transurban City Link Ltd
> shall be understood as neither given nor
> endorsed by it.
>
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> --
> Author: Mark Richard
> INET: mrichard_at_transurban.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
=== message truncated ===
Do you Yahoo!?
Yahoo! Tax Center - forms, calculators, tips, more
http://taxes.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Barbara Baker
INET: barbarabbaker_at_yahoo.com
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).
**********************************************************************
This email contains information intended for the addressee only.
It may be confidential and may be the subject of legal and/or
professional privilege. Any dissemination, distribution, copyright
or use of this communication without prior permission of the
sender is strictly prohibited.
**********************************************************************
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Niall Litchfield
INET: n-litchfield_at_audit-commission.gov.uk
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 Fri Feb 28 2003 - 03:38:39 CST
