Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Purely for your amusement

RE: Purely for your amusement

From: Jeremy Pulcifer <Jeremy.Pulcifer_at_kadiri.com>
Date: Thu, 27 Feb 2003 15:29:45 -0800
Message-ID: <F001.0055C544.20030227152945@fatcity.com>


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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jeremy Pulcifer
  INET: Jeremy.Pulcifer_at_kadiri.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).
Received on Thu Feb 27 2003 - 17:29:45 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US