RE: CBO Friday Fun!
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 7 Jun 2014 05:47:16 +0000
Iggy,
It's always possible that any slightly unusual example will find a hole in the
optimizer's strategy for handlng ANSI; but I think your explanation of the
effect of the optimizer_search_limit on the optimizer's decision to bypass the
Cartesian join is correct. In this case the join condition that appears in the
WHERE clause is:
AND (so1.email_address = so2.email_address
OR (so1.person_last_name = so2.person_last_name
AND so1.postal_code = so2.postal_code))
Because of the OR clause the optimizer has two choices - concatenation (i.e.
split the query into a "UNION ALL" with a predicate to eliminate double
reporting) or a Cartesian join. The concatenation would (guessing, here)
perhaps result in a cost that was roughly double the cost of the forced
Cartesian path so, give or take a bit, very close to the cost of the plan that
the optimizer actually took unhinted.
As you say, setting the session _optimizer_search_limit to 6 and forcing a
re-optimisation could be a good idea - even if the subsequent plan didn't use
the Cartesian we might see it in the 10053 trace.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Iggy Fernandez [iggy_fernandez@xxxxxxxxxxx]
Sent: 07 June 2014 02:58
To: vxsmimmcp@xxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: CBO Friday Fun!
Also, I wonder whether the CROSS JOIN clause is blindsiding the optimizer. The
developer has chosen to express the INNER JOIN ON clause in CROSS JOIN WHERE
fashion.
Iggy
________________________________
From: iggy_fernandez@xxxxxxxxxxx
To: vxsmimmcp@xxxxxxxxxx
CC: oracle-l@xxxxxxxxxxxxx
Subject: RE: CBO Friday Fun!
Date: Fri, 6 Jun 2014 15:25:29 -0700
re: does the fact that the cost is lower on the hinted version mean that CBO is
simply not considering the join order?
That sounds logical enough. The 10053 trace lists all the permutations that
were considered so you have confirmation. The optimizer does not consider all
join orders even if _optimizer_max_permutations has not been exceeded. The
optimizer only considers all join permutations if the number of tables is not
greater than _optimizer_search_limit whose default is 5. You could try
increasing the value to 6.
________________________________
From: vxsmimmcp@xxxxxxxxxx
To: oratune@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: CBO Friday Fun!
Date: Fri, 6 Jun 2014 21:33:36 +0000
>> There are only 5 tables in this query – 120 permutations. It seems that CBO
>> should be finding this plan on its own.
Miscounted – 6 tables. Still…?
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of McPeak, Matt
Sent: Friday, June 06, 2014 5:30 PM
To: David Fitzjarrell; oracle-l@xxxxxxxxxxxxx
Subject: RE: CBO Friday Fun!
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: "" <dmarc-noreply@xxxxxxxxxxxxx> (Redacted sender "oratune@xxxxxxxxx" for
DMARC)
Sent: Friday, June 06, 2014 5:21 PM
To: McPeak, Matt; oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxx<mailto:vxsmimmcp@xxxxxxxxxx>> 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
Other related posts:
