RE: SELECT and INSERT...SELECT use two different plans
Date: Wed, 7 May 2014 17:52:53 +0000
Message-ID: <D7864FA3E7830B428CB2A5A5301B63EE0172310D36_at_S7041VA005.soa.soaad.com>
>> 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_at_hotsos.com]
Sent: Wednesday, May 07, 2014 1:49 PM
To: McPeak, Matt; oracle-l_at_freelists.org
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. :)
+--+--+--+--+--+--+--+--+--+--+--+--+--+--+
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_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of McPeak, Matt
Sent: Wednesday, May 07, 2014 1:35 PM
To: oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>
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
-- http://www.freelists.org/webpage/oracle-lReceived on Wed May 07 2014 - 19:52:53 CEST