RE: SELECT and INSERT...SELECT use two different plans

From: Ric Van Dyke <ric.van.dyke_at_hotsos.com>
Date: Wed, 7 May 2014 13:03:48 -0500
Message-ID: <C970F08BBE1E164AA8063E01502A71CF022EB4AA_at_WIN02.hotsos.com>

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?

From: McPeak, Matt [mailto:vxsmimmcp_at_subaru.com] Sent: Wednesday, May 07, 2014 2:01 PM
To: Ric Van Dyke; oracle-l_at_freelists.org 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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of McPeak, Matt
Sent: Wednesday, May 07, 2014 1:53 PM
To: Ric Van Dyke; oracle-l_at_freelists.org 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_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. 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_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
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-l

Received on Wed May 07 2014 - 20:03:48 CEST

This message: [ Message body ]
Next message: Adric Norris: "Re: conditional unique index to restrict duplicates of a particular set"
Previous message: McPeak, Matt: "RE: SELECT and INSERT...SELECT use two different plans"
Maybe in reply to: McPeak, Matt: "SELECT and INSERT...SELECT use two different plans"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message