Re: CBO Friday Fun!
From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Fri, 6 Jun 2014 14:48:55 -0700 (PDT)
Message-ID: <1402091335.448.YahooMailNeo_at_web124702.mail.ne1.yahoo.com>
I can't say for certain but there may be issues with 11.2.0.2 and similar queries. I don't have any table definitions or I'd run them on my 11.2.0.3 database and see if both queries generate the same plan. David Fitzjarrell Principal author, "Oracle Exadata Survival Guide" On Friday, June 6, 2014 3:30 PM, "McPeak, Matt" <vxsmimmcp_at_subaru.com> wrote: 11.2.0.2 STATS are current. Histograms, maybe…. SO2.CANCELLED_FLAG could benefit from one, maybe. But that’s all irrelevant! With an ORDERED hint, CBO is computing a cost a lower cost *with the stats and histograms that it has*. Why would CBO skip a plan that is obviously legal and lower cost? I say “obviously legal” because, if it were not legal, the ORDERED hint wouldn’t give me the lower cost plan. There are only 5 tables in this query – 120 permutations. It seems that CBO should be finding this plan on its own. Thanks, Matt From:David Fitzjarrell [mailto:oratune_at_yahoo.com] Sent: Friday, June 06, 2014 5:21 PM To: McPeak, Matt; oracle-l_at_freelists.org Subject: Re: CBO Friday Fun! Oracle version? Are stats current? Are histograms representative of the data? More information would be appreciated. David Fitzjarrell Principal author, "Oracle Exadata Survival Guide" On Friday, June 6, 2014 3:17 PM, "McPeak, Matt" <vxsmimmcp_at_subaru.com> wrote: Hi, Gurus! I have a query that someone gave me. I’ve been looking at a 10053 event trace for it, trying to figure out why CBO will not come up with the “obvious” plan without a hint. I’ve boiled the query down to a relatively simple form, and here is what I find: · If I explain plan the query, the cost is 1,918 · If I put an /*+ ORDERED */ hint in the query and explain plan that one, the cost is 959 I’ll paste the two queries below, for reference. My question is: does the fact that the cost is lower on the hinted version mean that CBO is simply not considering the join order? In the 10053 trace, all the join orders considered want to go to table vvmi2 before oel2 (joining on the car_line column instead of the stars_model_item_id which has a unique index). That move makes that cardinality going into the CROSS JOIN on so2 much higher than 1, making that cross join too expensive. Anyway, at this point, I guess I’m wondering why the CBO doesn’t look at and choose the plan with the 959 cost without needing me to specify /*+ ORDERED */. Thanks, in advance, for your thoughts. The queries are below. (The purpose of the query is to find orders that are potential duplicates with a given order). Thanks, Matt *** This query explains with a cost of 1,918 *** SELECT MIN (so2.order_number) dup_order FROM verp_om_sold_orders so1 CROSS JOIN verp_om_sold_orders so2 INNER JOIN oe_order_lines_all oel1 ON oel1.header_id = so1.order_header_id AND oel1.item_type_code = 'MODEL' INNER JOIN verp_vps_model_items vvmi1 ON oel1.inventory_item_id = vvmi1.stars_model_item_id INNER JOIN oe_order_lines_all oel2 ON oel2.header_id = so2.order_header_id AND oel2.item_type_code = 'MODEL' INNER JOIN verp_vps_model_items vvmi2 ON vvmi2.stars_model_item_id = oel2.inventory_item_id WHERE 1 = 1 AND so1.order_header_id = 41356864 AND so2.order_header_id != 41356864 AND so2.entered_date > to_date('06-JAN-2014') AND NVL (so2.cancelled_flag, 'N') = 'N' -- Is duplicate AND (so1.email_address = so2.email_address OR (so1.person_last_name = so2.person_last_name AND so1.postal_code = so2.postal_code)) AND vvmi1.car_line = vvmi2.car_line *** This query explains with a cost of 959 *** SELECT /*+ ORDERED */ MIN (so2.order_number) dup_order FROM verp_om_sold_orders so1 CROSS JOIN verp_om_sold_orders so2 INNER JOIN oe_order_lines_all oel1 ON oel1.header_id = so1.order_header_id AND oel1.item_type_code = 'MODEL' INNER JOIN verp_vps_model_items vvmi1 ON oel1.inventory_item_id = vvmi1.stars_model_item_id INNER JOIN oe_order_lines_all oel2 ON oel2.header_id = so2.order_header_id AND oel2.item_type_code = 'MODEL' INNER JOIN verp_vps_model_items vvmi2 ON vvmi2.stars_model_item_id = oel2.inventory_item_id WHERE 1 = 1 AND so1.order_header_id = 41356864 AND so2.order_header_id != 41356864 AND so2.entered_date > to_date('06-JAN-2014') AND NVL (so2.cancelled_flag, 'N') = 'N' -- Is duplicate AND (so1.email_address = so2.email_address OR (so1.person_last_name = so2.person_last_name AND so1.postal_code = so2.postal_code)) AND vvmi1.car_line = vvmi2.car_line
Date: Fri, 6 Jun 2014 14:48:55 -0700 (PDT)
Message-ID: <1402091335.448.YahooMailNeo_at_web124702.mail.ne1.yahoo.com>
I can't say for certain but there may be issues with 11.2.0.2 and similar queries. I don't have any table definitions or I'd run them on my 11.2.0.3 database and see if both queries generate the same plan. David Fitzjarrell Principal author, "Oracle Exadata Survival Guide" On Friday, June 6, 2014 3:30 PM, "McPeak, Matt" <vxsmimmcp_at_subaru.com> wrote: 11.2.0.2 STATS are current. Histograms, maybe…. SO2.CANCELLED_FLAG could benefit from one, maybe. But that’s all irrelevant! With an ORDERED hint, CBO is computing a cost a lower cost *with the stats and histograms that it has*. Why would CBO skip a plan that is obviously legal and lower cost? I say “obviously legal” because, if it were not legal, the ORDERED hint wouldn’t give me the lower cost plan. There are only 5 tables in this query – 120 permutations. It seems that CBO should be finding this plan on its own. Thanks, Matt From:David Fitzjarrell [mailto:oratune_at_yahoo.com] Sent: Friday, June 06, 2014 5:21 PM To: McPeak, Matt; oracle-l_at_freelists.org Subject: Re: CBO Friday Fun! Oracle version? Are stats current? Are histograms representative of the data? More information would be appreciated. David Fitzjarrell Principal author, "Oracle Exadata Survival Guide" On Friday, June 6, 2014 3:17 PM, "McPeak, Matt" <vxsmimmcp_at_subaru.com> wrote: Hi, Gurus! I have a query that someone gave me. I’ve been looking at a 10053 event trace for it, trying to figure out why CBO will not come up with the “obvious” plan without a hint. I’ve boiled the query down to a relatively simple form, and here is what I find: · If I explain plan the query, the cost is 1,918 · If I put an /*+ ORDERED */ hint in the query and explain plan that one, the cost is 959 I’ll paste the two queries below, for reference. My question is: does the fact that the cost is lower on the hinted version mean that CBO is simply not considering the join order? In the 10053 trace, all the join orders considered want to go to table vvmi2 before oel2 (joining on the car_line column instead of the stars_model_item_id which has a unique index). That move makes that cardinality going into the CROSS JOIN on so2 much higher than 1, making that cross join too expensive. Anyway, at this point, I guess I’m wondering why the CBO doesn’t look at and choose the plan with the 959 cost without needing me to specify /*+ ORDERED */. Thanks, in advance, for your thoughts. The queries are below. (The purpose of the query is to find orders that are potential duplicates with a given order). Thanks, Matt *** This query explains with a cost of 1,918 *** SELECT MIN (so2.order_number) dup_order FROM verp_om_sold_orders so1 CROSS JOIN verp_om_sold_orders so2 INNER JOIN oe_order_lines_all oel1 ON oel1.header_id = so1.order_header_id AND oel1.item_type_code = 'MODEL' INNER JOIN verp_vps_model_items vvmi1 ON oel1.inventory_item_id = vvmi1.stars_model_item_id INNER JOIN oe_order_lines_all oel2 ON oel2.header_id = so2.order_header_id AND oel2.item_type_code = 'MODEL' INNER JOIN verp_vps_model_items vvmi2 ON vvmi2.stars_model_item_id = oel2.inventory_item_id WHERE 1 = 1 AND so1.order_header_id = 41356864 AND so2.order_header_id != 41356864 AND so2.entered_date > to_date('06-JAN-2014') AND NVL (so2.cancelled_flag, 'N') = 'N' -- Is duplicate AND (so1.email_address = so2.email_address OR (so1.person_last_name = so2.person_last_name AND so1.postal_code = so2.postal_code)) AND vvmi1.car_line = vvmi2.car_line *** This query explains with a cost of 959 *** SELECT /*+ ORDERED */ MIN (so2.order_number) dup_order FROM verp_om_sold_orders so1 CROSS JOIN verp_om_sold_orders so2 INNER JOIN oe_order_lines_all oel1 ON oel1.header_id = so1.order_header_id AND oel1.item_type_code = 'MODEL' INNER JOIN verp_vps_model_items vvmi1 ON oel1.inventory_item_id = vvmi1.stars_model_item_id INNER JOIN oe_order_lines_all oel2 ON oel2.header_id = so2.order_header_id AND oel2.item_type_code = 'MODEL' INNER JOIN verp_vps_model_items vvmi2 ON vvmi2.stars_model_item_id = oel2.inventory_item_id WHERE 1 = 1 AND so1.order_header_id = 41356864 AND so2.order_header_id != 41356864 AND so2.entered_date > to_date('06-JAN-2014') AND NVL (so2.cancelled_flag, 'N') = 'N' -- Is duplicate AND (so1.email_address = so2.email_address OR (so1.person_last_name = so2.person_last_name AND so1.postal_code = so2.postal_code)) AND vvmi1.car_line = vvmi2.car_line
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Jun 06 2014 - 23:48:55 CEST