RE: Purely for your amusement

More wonderful is that the users are complaining about poor performance and not of wrong data it is returning.  

Are the users comfortable with catesian products returned to them with meaningless data?  

Regards
Naveen

-----Original Message-----
Sent: Friday, February 28, 2003 5:00 AM
To: Multiple recipients of list ORACLE-L

Heh. 6 tables, 2 where statements, one of which is an outer. Gotta love it.

I'm thinking of building a tool that intercepts dumb SQL and emails back a raspberry. Whaddya think, is there a market for such a utility?

> -----Original Message-----
> From: Barbara Baker [ mailto:barbarabbaker_at_yahoo.com]
> Sent: Thursday, February 27, 2003 2:51 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Purely for your amusement
>
>
> 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).
>

DISCLAIMER:
This message (including attachment if any) is confidential and may be privileged. Before opening attachments please check them for viruses and defects. MindTree Consulting Private Limited (MindTree) will not be responsible for any viruses or defects or any forwarded attachments emanating either from within MindTree or outside. If you have received this message by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change and MindTree shall not be liable for any improper, untimely or incomplete transmission.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Naveen Nahata
  INET: naveen_nahata_at_mindtree.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).