RE: FW: [ Danone ] - Hung Oracle Query

yep, and before everyone else tries to waste time looking at this, I mentioned to the developer that he was using a non-indexed column (a.city code) in the where clause and that is why he is doing a full table scan on one of the large tables (address). His reply, exactly, now how do I get around this, AAARRGGHHH !!!! This is why when he removes references to the x_city table the query runs really quickly.

Once again thanks for everyone who has had a crack at this and apologies for wasting your time.

Off to sharpen my hatchet.

> Lee Robertson
> Acxiom
> Tel: 0191 525 7344
> Fax: 0191 525 7007
> Email: lerobe_at_acxiom.co.uk
>

-----Original Message-----
Sent: 14 February 2001 15:18
To: Multiple recipients of list ORACLE-L

Have you rerun the analyzer to generate new statistics? Just a thot...RBG

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, February 14, 2001 9:26 AM

> Thanks for the responses from you guys. I went back to our developer with
> your suggestions and he said, wait for this one !!!
>
> Oh I did select from all the tables in the query, I just chopped them out
> when I passed it onto you as I didn't think it was necessary.
>
> Original problem was ...
> Since the database was bounced Thursday morning I have a query which no
> > longer runs - it just hangs. If I take out the join to X_CITY it works
> > fine. I haven't changed any indexes and all the indexes are correct.
> > Any ideas ?
>
> Real SQL is as follows.
>
> SELECT i.surname,
> i.forename,
> a.address_line_1,
> a.address_line_2,
> a.address_line_3,
> a.address_line_4,
> a.postcode,
> c.city_desc,
> cp.phone_number,
> ce.email_address,
> i.date_of_birth,
> i.gender_code,
> i.ind_is_child,
> i.date_of_birth_calc_code,
> i.individual_id,
> h.household_id
> FROM changed_hh_keys chh,
> household h,
> address a,
> x_city c,
> individual i,
> consumer_phone cp,
> consumer_email ce
> WHERE h.household_id = chh.household_id
> AND a.household_id = h.household_id
> AND i.household_id = h.household_id
> AND c.city_code = a.city_code
> AND cp.consumer_id(+) = i.household_id
> AND ce.consumer_id(+) = i.individual_id
> AND i.consumer_type_code != 'H';
>
> and the plan is..
>
> > SELECT STATEMENT Optimizer=CHOOSE (Cost=7224597105017
> > Card=8024558972338880000000 Bytes=1492567968855030000000000)
> > NESTED LOOPS (Cost=7224597105017 Card=8024558972338880000000
> > Bytes=1492567968855030000000000)
> > MERGE JOIN (OUTER) (Cost=7224597105017 Card=501781749669299000
> > Bytes=86808242692788700000)
> > SORT (JOIN) (Cost=7224597102585 Card=69188658282644
> > Bytes=11070185325223000)
> > NESTED LOOPS (Cost=3327368730 Card=69188658282644
> > Bytes=11070185325223000)
> > MERGE JOIN (Cost=3327368730 Card=6918865828264370
> > Bytes=1017073276754860000)
> > SORT (JOIN) (Cost=3327245772 Card=55237676133
> > Bytes=6683758812093)
> > NESTED LOOPS (Cost=831 Card=55237676133
Bytes=6683758812093)
> > MERGE JOIN (OUTER) (Cost=831 Card=1054167
Bytes=86441694)
> > SORT (JOIN)
> > TABLE ACCESS (FULL) OF INDIVIDUAL (Cost=826
> > Card=1285570 Bytes=88704330)
> > SORT (JOIN) (Cost=4 Card=82 Bytes=1066)
> > TABLE ACCESS (FULL) OF CONSUMER_EMAIL (Cost=1
Card=82
> > Bytes=1066)
> > INDEX (UNIQUE SCAN) OF HOUSEHOLD_PK (UNIQUE)
> > SORT (JOIN) (Cost=122958 Card=12525628 Bytes=325666328)
> > TABLE ACCESS (FULL) OF ADDRESS (Cost=23275 Card=12525628
> > Bytes=325666328)
> > INDEX (UNIQUE SCAN) OF X_CITY_PK (UNIQUE)
> > SORT (JOIN) (Cost=2432 Card=725237 Bytes=9428081)
> > INDEX (FULL SCAN) OF CONSUMER_PHONE_PK (UNIQUE) (Cost=26
> > Card=725237 Bytes=9428081)
> > INDEX (UNIQUE SCAN) OF CHANGED_HH_KEYS_PK (UNIQUE)
>
> > Lee Robertson
> > Acxiom
> > Tel: 0191 525 7344
> > Fax: 0191 525 7007
> > Email: lerobe_at_acxiom.co.uk
> >
>
>
> -----Original Message-----
> Sent: 09 February 2001 17:52
> To: Multiple recipients of list ORACLE-L
>
>
> Why do you even have the tables consumer_phone and consumer_email
> included in the query? Obviously you don't care if they have entries in
> them, and you're not selecting any info out of them. Remove them and see
> what happens.
>
> >>> LEROBE_at_acxiom.co.uk 02/09/01 11:41AM >>>
> Can someone have alook at the problem below please and advise. I am
stumped.
>
> Regards
>
>
> > -----Original Message-----
> > From: jmydde - James Myddelton
> > Sent: 09 February 2001 16:33
> > To: lerobe - Lee Robertson
> > Cc: rkilbe - Robert Kilbey
> > Subject: [ Danone ] - Hung Oracle Query
> >
> >
> > Lee,
> >
> > Since the database was bounced Thursday morning I have a query which no
> > longer runs - it just hangs. If I take out the join to X_CITY it works
> > fine. I haven't changed any indexes and all the indexes are correct.
> > Any ideas ?
> >
> > James
> >
> > SELECT i.surname
> > FROM changed_hh_keys chh,
> > household h,
> > address a,
> > x_city c,
> > individual i,
> > consumer_phone cp,
> > consumer_email ce
> > WHERE h.household_id = chh.household_id
> > AND a.household_id = h.household_id
> > AND i.household_id = h.household_id
> > AND i.consumer_type_code != 'H'
> > AND c.city_code = a.city_code
> > AND cp.consumer_id(+) = i.household_id
> > AND ce.consumer_id(+) = i.individual_id;
> >
> > SELECT STATEMENT Optimizer=CHOOSE (Cost=7224597105017
> > Card=8024558972338880000000 Bytes=1492567968855030000000000)
> > NESTED LOOPS (Cost=7224597105017 Card=8024558972338880000000
> > Bytes=1492567968855030000000000)
> > MERGE JOIN (OUTER) (Cost=7224597105017 Card=501781749669299000
> > Bytes=86808242692788700000)
> > SORT (JOIN) (Cost=7224597102585 Card=69188658282644
> > Bytes=11070185325223000)
> > NESTED LOOPS (Cost=3327368730 Card=69188658282644
> > Bytes=11070185325223000)
> > MERGE JOIN (Cost=3327368730 Card=6918865828264370
> > Bytes=1017073276754860000)
> > SORT (JOIN) (Cost=3327245772 Card=55237676133
> > Bytes=6683758812093)
> > NESTED LOOPS (Cost=831 Card=55237676133
Bytes=6683758812093)
> > MERGE JOIN (OUTER) (Cost=831 Card=1054167
Bytes=86441694)
> > SORT (JOIN)
> > TABLE ACCESS (FULL) OF INDIVIDUAL (Cost=826
> > Card=1285570 Bytes=88704330)
> > SORT (JOIN) (Cost=4 Card=82 Bytes=1066)
> > TABLE ACCESS (FULL) OF CONSUMER_EMAIL (Cost=1
Card=82
> > Bytes=1066)
> > INDEX (UNIQUE SCAN) OF HOUSEHOLD_PK (UNIQUE)
> > SORT (JOIN) (Cost=122958 Card=12525628 Bytes=325666328)
> > TABLE ACCESS (FULL) OF ADDRESS (Cost=23275 Card=12525628
> > Bytes=325666328)
> > INDEX (UNIQUE SCAN) OF X_CITY_PK (UNIQUE)
> > SORT (JOIN) (Cost=2432 Card=725237 Bytes=9428081)
> > INDEX (FULL SCAN) OF CONSUMER_PHONE_PK (UNIQUE) (Cost=26
> > Card=725237 Bytes=9428081)
> > INDEX (UNIQUE SCAN) OF CHANGED_HH_KEYS_PK (UNIQUE)
>
>
> The information contained in this communication is
> confidential, is intended only for the use of the recipient
> named above, and may be legally privileged. If the reader
> of this message is not the intended recipient, you are
> hereby notified that any dissemination, distribution or
> copying of this communication is strictly prohibited.
> If you have received this communication in error, please
> re-send this communication to the sender and delete the
> original message or any copy of it from your computer
> system.
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: lerobe - Lee Robertson
> INET: LEROBE_at_acxiom.co.uk
>
> 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: 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.com
> --
> Author: Tim Sawmiller
> INET: sawmillert_at_state.mi.us
>
> 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: 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.com
> --
> Author: lerobe - Lee Robertson
> INET: LEROBE_at_acxiom.co.uk
>
> 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: 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.com
-- 
Author: Ruth Gramolini
  INET: rgramolini_at_tax.state.vt.us

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: 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.com
-- 
Author: lerobe - Lee Robertson
  INET: LEROBE_at_acxiom.co.uk

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