RE: SELECT and INSERT...SELECT use two different plans
- From: "Ric Van Dyke" <ric.van.dyke@xxxxxxxxxx>
- To: "McPeak, Matt" <vxsmimmcp@xxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 7 May 2014 13:03:48 -0500
And there you go! Although a bug, it does prove my point. The plans
are different because it did different things because it was DML and not
a SELECT.
Isn't Oracle great?
- Ric
From: McPeak, Matt [mailto:vxsmimmcp@xxxxxxxxxx]
Sent: Wednesday, May 07, 2014 2:01 PM
To: Ric Van Dyke; oracle-l@xxxxxxxxxxxxx
Subject: RE: SELECT and INSERT...SELECT use two different plans
Incidentally... found a bug on MetaLink (bug 10013899) saying that in
11.2.0.3., the CBO does not have access to all the same query
transformations when optimizing DDL as it does when optimizing a
straight SELECT.
Exact wording: "Certain DML/DDL operations may get a different plan to
the same
query used in a select statement due to lack of cost based query
transformation. "
I think that might be it...
I guess I should have looked before posting.
Thanks,
Matt
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of McPeak, Matt
Sent: Wednesday, May 07, 2014 1:53 PM
To: Ric Van Dyke; oracle-l@xxxxxxxxxxxxx
Subject: RE: SELECT and INSERT...SELECT use two different plans
>> why would they? Indexes has to be maintained, constraints have to be
checked, maybe some triggers fire... so it seems quite reasonable that
the cost and even the plan might be different.
Because (a) none of the work you mentioned shows up in the plan though
and (b) none of the work depends on HOW the final rows are determined -
they only depend on what the final rows are (i.e., they're independent
of the access plan). Or so it seems to me.
Thanks,
Matt
From: Ric Van Dyke [mailto:ric.van.dyke@xxxxxxxxxx]
Sent: Wednesday, May 07, 2014 1:49 PM
To: McPeak, Matt; oracle-l@xxxxxxxxxxxxx
Subject: RE: SELECT and INSERT...SELECT use two different plans
From a theory point of view, why would they? The INSERT does something
different with the data then just a select by itself. Indexes has to be
maintained, constraints have to be checked, maybe some triggers fire...
so it seems quite reasonable that the cost and even the plan might be
different.
If you change the statement - in any way - don't expect to get the same
plan. Adding the good old "ROWNUM = 1" can cause for a different plan.
Of course if you have spare cycles some day and want to REALLY dig into
it you could do a 10053 trace on the two and start comparing things.
You best have a lot of spare cycles if you decided to go down that path.
J
+--+--+--+--+--+--+--+--+--+--+--+--+--+--+
Ric Van Dyke
Education Director
Oracle Ace Associate
Hotsos Ltd.
Cell 248-705-0624
**** Hotsos Symposium
**** March 1-5 2015
Make your plans to be there now!
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of McPeak, Matt
Sent: Wednesday, May 07, 2014 1:35 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: SELECT and INSERT...SELECT use two different plans
Without getting too much into the specific situation (which I'll include
in the post below, for reference), is there any logical reason why
Oracle would take a SELECT statement having plan "X" with a cost of,
say, 300, and replace it with plan "Y", with a cost of, say, 16000 when
I stick an INSERT INTO... in front of it?
The SELECT statement refers to views (e.g., BOM_BILL_OF_MATERIALS) that
use non-deterministic PL/SQL functions - the problem seems to be around
that area.
I'm not looking for anyone to dig into the specific query (but, hey, if
you want to do my work for me, great!). I'm more looking for
general/theoretical reasons why something like this would happen. It
seems to me that the SELECT should use the same plan, regardless of
whether the results are then being inserted into a table.
Here is the exact SQL:
INSERT INTO verp_otm_staged_von_accys (von,
accessory_code,
labor_hours,
heavy_flag)
SELECT DISTINCT v.von,
msi.segment1 accessory_code,
NVL (bsor.usage_rate_or_amount, 0) labor_hours,
CASE WHEN ',' || msid.heavy_ports || ',' LIKE '%' ||
v.port || '%' THEN 'Y' ELSE 'N' END heavy_flag
FROM verp_otm_staged_vons v
INNER JOIN (SELECT oel.header_id, oel.inventory_item_id,
oel.item_type_code
FROM oe_order_lines oel
WHERE oel.item_type_code IN ('OPTION', 'CLASS')
UNION ALL
SELECT vosal.header_id, vosal.inventory_item_id,
vosal.item_type_code
FROM verp_order_std_accy_lines vosal
WHERE vosal.item_type_code IN ('OPTION', 'CLASS'))
oel
ON oel.header_id = v.header_id
LEFT JOIN bom_bill_of_materials bbom
ON bbom.organization_id = 92
AND bbom.assembly_item_id = oel.inventory_item_id
AND oel.item_type_code = 'CLASS'
LEFT JOIN bom_inventory_components bic
ON bic.bill_sequence_id = bbom.bill_sequence_id
AND NVL (bic.disable_date, SYSDATE + 1) > SYSDATE
INNER JOIN mtl_system_items msi
ON msi.organization_id = 92
AND msi.inventory_item_id = NVL (bic.component_item_id,
oel.inventory_item_id)
INNER JOIN mtl_system_items_b_dfv msid
ON msid.row_id = msi.ROWID
INNER JOIN mtl_parameters mp
ON mp.organization_code = v.port
LEFT JOIN bom_standard_operations bso
ON bso.operation_code = msi.segment1
AND bso.organization_id = mp.organization_id
LEFT JOIN bom_std_op_resources bsor
ON bsor.standard_operation_id = bso.standard_operation_id
LEFT JOIN bom_resources br
ON br.resource_id = bsor.resource_id
AND br.resource_code IN ('Labor', 'Std Labor')
WHERE v.header_id = :b1
Without the INSERT INTO... , the plan is:
Plan
SELECT STATEMENT ALL_ROWSCost: 304 Bytes: 3,762 Cardinality: 19
Partition #: 0
36 HASH UNIQUE Cost: 304 Bytes: 3,762 Cardinality: 19 Partition #:
0
35 NESTED LOOPS OUTER Cost: 303 Bytes: 3,762 Cardinality: 19
Partition #: 0
32 NESTED LOOPS OUTER Cost: 284 Bytes: 3,496 Cardinality: 19
Partition #: 0
29 NESTED LOOPS OUTER Cost: 247 Bytes: 3,268 Cardinality: 19
Partition #: 0
26 VIEW SYS. Cost: 227 Bytes: 3,021 Cardinality: 19
Partition #: 0
25 NESTED LOOPS Cost: 0 Bytes: 0 Cardinality: 0
Partition #: 0
23 NESTED LOOPS Cost: 227 Bytes: 3,667 Cardinality: 19
Partition #: 0
21 NESTED LOOPS OUTER Cost: 53 Bytes: 10,962
Cardinality: 87 Partition #: 0
18 NESTED LOOPS OUTER Cost: 29 Bytes: 492
Cardinality: 6 Partition #: 0
13 MERGE JOIN CARTESIAN Cost: 5 Bytes: 402
Cardinality: 6 Partition #: 0
4 NESTED LOOPS Cost: 1 Bytes: 37 Cardinality: 1
Partition #: 0
2 TABLE ACCESS BY INDEX ROWID
VERPCUST.VERP_OTM_STAGED_VONS Cost: 0 Bytes: 29 Cardinality: 1
Partition #: 0
1 INDEX UNIQUE SCAN
VERPCUST.VERP_OTM_STAGED_VONS_U2 [Analyzed]Cost: 0 Bytes: 0
Cardinality: 1 Partition #: 0
3 INDEX FULL SCAN VERPCUST.VERP_VPS_SUPPLY_MP_N1
[Analyzed]Cost: 1 Bytes: 8 Cardinality: 1 Partition #: 0
12 BUFFER SORT Cost: 4 Bytes: 180 Cardinality:
6 Partition #: 0
11 VIEW APPS. Cost: 4 Bytes: 180 Cardinality:
6 Partition #: 0
10 UNION-ALL Cost: 0 Bytes: 0 Cardinality:
0 Partition #: 0
7 FILTER Cost: 0 Bytes: 0 Cardinality: 0
Partition #: 0
6 TABLE ACCESS BY INDEX ROWID
ONT.OE_ORDER_LINES_ALL [Analyzed]Cost: 7 Bytes: 160 Cardinality: 8
Partition #: 0
5 INDEX RANGE SCAN
VERPCUST.VERP_OE_ORDER_LINES_N1 [Analyzed]Cost: 4 Bytes: 0
Cardinality: 7 Partition #: 0
9 TABLE ACCESS BY INDEX ROWID
VERPCUST.VERP_ORDER_STD_ACCY_LINES [Analyzed]Cost: 4 Bytes: 95
Cardinality: 5 Partition #: 0
8 INDEX RANGE SCAN
VERPCUST.VERP_ORDER_STD_ACCY_LINES_N1 [Analyzed]Cost: 3 Bytes: 0
Cardinality: 5 Partition #: 0
17 VIEW PUSHED PREDICATE APPS.BOM_BILL_OF_MATERIALS
Cost: 4 Bytes: 15 Cardinality: 1 Partition #: 0
16 FILTER Cost: 0 Bytes: 0 Cardinality: 0
Partition #: 0
15 TABLE ACCESS BY INDEX ROWID
BOM.BOM_STRUCTURES_B [Analyzed]Cost: 4 Bytes: 36 Cardinality: 1
Partition #: 0
14 INDEX RANGE SCAN BOM.BOM_STRUCTURES_B_N2
[Analyzed]Cost: 3 Bytes: 0 Cardinality: 1 Partition #: 0
20 TABLE ACCESS BY INDEX ROWID BOM.BOM_COMPONENTS_B
[Analyzed]Cost: 4 Bytes: 616 Cardinality: 14 Partition #: 0
19 INDEX RANGE SCAN BOM.BOM_COMPONENTS_B_N2
[Analyzed]Cost: 2 Bytes: 0 Cardinality: 15 Partition #: 0
22 INDEX UNIQUE SCAN INV.MTL_SYSTEM_ITEMS_B_U1
[Analyzed]Cost: 1 Bytes: 0 Cardinality: 1 Partition #: 0
24 TABLE ACCESS BY INDEX ROWID INV.MTL_SYSTEM_ITEMS_B
[Analyzed]Cost: 2 Bytes: 67 Cardinality: 1 Partition #: 0
28 TABLE ACCESS BY INDEX ROWID BOM.BOM_STANDARD_OPERATIONS
[Analyzed]Cost: 2 Bytes: 13 Cardinality: 1 Partition #: 0
27 INDEX RANGE SCAN BOM.BOM_STANDARD_OPERATIONS_U1
[Analyzed]Cost: 1 Bytes: 0 Cardinality: 1 Partition #: 0
31 TABLE ACCESS BY INDEX ROWID BOM.BOM_STD_OP_RESOURCES
[Analyzed]Cost: 2 Bytes: 12 Cardinality: 1 Partition #: 0
30 INDEX RANGE SCAN BOM.BOM_STD_OP_RESOURCES_U1
[Analyzed]Cost: 1 Bytes: 0 Cardinality: 1 Partition #: 0
34 TABLE ACCESS BY INDEX ROWID BOM.BOM_RESOURCES [Analyzed]Cost: 1
Bytes: 14 Cardinality: 1 Partition #: 0
33 INDEX UNIQUE SCAN BOM.BOM_RESOURCES_U2 [Analyzed]Cost: 0
Bytes: 0 Cardinality: 1 Partition #: 0
With the INSERT INTO, the plan is:
Plan
INSERT STATEMENT ALL_ROWSCost: 15,986 Bytes: 4,009 Cardinality: 19
Partition #: 0
35 LOAD TABLE CONVENTIONAL VERPCUST.VERP_OTM_STAGED_VON_ACCYS Cost: 0
Bytes: 0 Cardinality: 0 Partition #: 0
34 HASH UNIQUE Cost: 15,986 Bytes: 4,009 Cardinality: 19
Partition #: 0
33 NESTED LOOPS OUTER Cost: 15,985 Bytes: 4,009 Cardinality: 19
Partition #: 0
30 NESTED LOOPS OUTER Cost: 15,966 Bytes: 3,743 Cardinality:
19 Partition #: 0
27 NESTED LOOPS OUTER Cost: 15,929 Bytes: 3,515
Cardinality: 19 Partition #: 0
24 VIEW SYS. Cost: 15,909 Bytes: 3,268 Cardinality: 19
Partition #: 0
23 NESTED LOOPS Cost: 0 Bytes: 0 Cardinality: 0
Partition #: 0
21 NESTED LOOPS Cost: 15,909 Bytes: 4,370
Cardinality: 19 Partition #: 0
19 NESTED LOOPS OUTER Cost: 15,735 Bytes: 14,181
Cardinality: 87 Partition #: 0
16 HASH JOIN OUTER Cost: 15,712 Bytes: 714
Cardinality: 6 Partition #: 0
13 MERGE JOIN CARTESIAN Cost: 5 Bytes: 480
Cardinality: 6 Partition #: 0
4 NESTED LOOPS Cost: 1 Bytes: 37 Cardinality:
1 Partition #: 0
2 TABLE ACCESS BY INDEX ROWID
VERPCUST.VERP_OTM_STAGED_VONS Cost: 0 Bytes: 29 Cardinality: 1
Partition #: 0
1 INDEX UNIQUE SCAN
VERPCUST.VERP_OTM_STAGED_VONS_U2 [Analyzed]Cost: 0 Bytes: 0
Cardinality: 1 Partition #: 0
3 INDEX FULL SCAN
VERPCUST.VERP_VPS_SUPPLY_MP_N1 [Analyzed]Cost: 1 Bytes: 8 Cardinality:
1 Partition #: 0
12 BUFFER SORT Cost: 4 Bytes: 258
Cardinality: 6 Partition #: 0
11 VIEW APPS. Cost: 4 Bytes: 258
Cardinality: 6 Partition #: 0
10 UNION-ALL Cost: 0 Bytes: 0
Cardinality: 0 Partition #: 0
7 FILTER Cost: 0 Bytes: 0 Cardinality:
0 Partition #: 0
6 TABLE ACCESS BY INDEX ROWID
ONT.OE_ORDER_LINES_ALL [Analyzed]Cost: 7 Bytes: 160 Cardinality: 8
Partition #: 0
5 INDEX RANGE SCAN
VERPCUST.VERP_OE_ORDER_LINES_N1 [Analyzed]Cost: 4 Bytes: 0
Cardinality: 7 Partition #: 0
9 TABLE ACCESS BY INDEX ROWID
VERPCUST.VERP_ORDER_STD_ACCY_LINES [Analyzed]Cost: 4 Bytes: 95
Cardinality: 5 Partition #: 0
8 INDEX RANGE SCAN
VERPCUST.VERP_ORDER_STD_ACCY_LINES_N1 [Analyzed]Cost: 3 Bytes: 0
Cardinality: 5 Partition #: 0
15 VIEW APPS.BOM_BILL_OF_MATERIALS Cost: 15,707
Bytes: 166,296 Cardinality: 4,264 Partition #: 0
14 TABLE ACCESS FULL BOM.BOM_STRUCTURES_B
[Analyzed]Cost: 15,707 Bytes: 153,504 Cardinality: 4,264 Partition #:
0
18 TABLE ACCESS BY INDEX ROWID BOM.BOM_COMPONENTS_B
[Analyzed]Cost: 4 Bytes: 616 Cardinality: 14 Partition #: 0
17 INDEX RANGE SCAN BOM.BOM_COMPONENTS_B_N2
[Analyzed]Cost: 2 Bytes: 0 Cardinality: 15 Partition #: 0
20 INDEX UNIQUE SCAN INV.MTL_SYSTEM_ITEMS_B_U1
[Analyzed]Cost: 1 Bytes: 0 Cardinality: 1 Partition #: 0
22 TABLE ACCESS BY INDEX ROWID INV.MTL_SYSTEM_ITEMS_B
[Analyzed]Cost: 2 Bytes: 67 Cardinality: 1 Partition #: 0
26 TABLE ACCESS BY INDEX ROWID BOM.BOM_STANDARD_OPERATIONS
[Analyzed]Cost: 2 Bytes: 13 Cardinality: 1 Partition #: 0
25 INDEX RANGE SCAN BOM.BOM_STANDARD_OPERATIONS_U1
[Analyzed]Cost: 1 Bytes: 0 Cardinality: 1 Partition #: 0
29 TABLE ACCESS BY INDEX ROWID BOM.BOM_STD_OP_RESOURCES
[Analyzed]Cost: 2 Bytes: 12 Cardinality: 1 Partition #: 0
28 INDEX RANGE SCAN BOM.BOM_STD_OP_RESOURCES_U1
[Analyzed]Cost: 1 Bytes: 0 Cardinality: 1 Partition #: 0
32 TABLE ACCESS BY INDEX ROWID BOM.BOM_RESOURCES [Analyzed]Cost:
1 Bytes: 14 Cardinality: 1 Partition #: 0
31 INDEX UNIQUE SCAN BOM.BOM_RESOURCES_U2 [Analyzed]Cost: 0
Bytes: 0 Cardinality: 1 Partition #: 0
Other related posts:
