Rule base optimizer selecting different execution path ( Tuning )

From: Gupta, Brijesh <Brijesh.Gupta_at_Airliquide.com>
Date: Fri, 22 Jun 2001 10:04:19 -0700
Message-ID: <F001.00333F27.20010622101251@fatcity.com>

Hi All,
Can somebody help me in understanding this. We have a query which runs faster on development and not on production. When I checked the trace , it using different execution path on both database. Database is 8.0.5 running oracle application and the optimizer is RULE.

Does anybody know why two execution path oracle optimizer has selected when its a RULE base optimizer. I know that is possible if its cost base optimizer but in rule it should be same right ( All the indexes are same on both database )

Only thing I did was rebuild some of the indexes on Test instance.

Thanks
Brijesh

Here is the explain plan of both the database.

Development Database ******************* Optimizer goal: RULE Parsing user id: 45 (APPS)

select
substr(mil.segment1||'.'||mil.segment2||'.'||mil.segment3,1,45) Locator,

mmt.subinventory_code Subinventory,
msi.segment1 PART_NO,
mmt.transaction_uom UOM,

sum(mmt.transaction_quantity)
from

mtl_item_locations mil,
mtl_transaction_types typ,
mtl_material_transactions mmt,
mtl_system_items msi,
mtl_parameters mp

where
mp.organization_code='768'
and mp.organization_id+0=msi.organization_id and msi.organization_id=mmt.organization_id and msi.inventory_item_id=mmt.inventory_item_id and trunc(mmt.transaction_date) between '01-APR-01' and '31-MAY-01'

and mmt.transaction_type_id=typ.transaction_type_id
and typ.transaction_type_name='Account alias receipt'
and mmt.locator_id=mil.inventory_location_id(+)
and mmt.organization_id=mil.organization_id(+)
and mil.subinventory_code = 'CORROSIVE'

group by mil.segment1,

mil.segment2,
mil.segment3,
mmt.subinventory_code,
msi.segment1,
mmt.transaction_uom


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2     51.68     901.33      85134     395083          0           4

------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 51.69 901.34 85134 395083 0 4

Misses in library cache during parse: 0
Optimizer goal: RULE
Parsing user id: 45 (APPS)

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: RULE
      4   SORT (GROUP BY)


     33    FILTER

     32     NESTED LOOPS (OUTER)

     33      NESTED LOOPS

    132       NESTED LOOPS
   2445        NESTED LOOPS
      1         TABLE ACCESS (BY INDEX ROWID) OF 'MTL_PARAMETERS'
      2          INDEX (RANGE SCAN) OF 'ORGANIZATION_CODE'
                     (NON-UNIQUE)
   2445         TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
                    'MTL_SYSTEM_ITEMS'
   2446          INDEX   GOAL: ANALYZED (RANGE SCAN) OF
                     'MTL_SYSTEM_ITEMS_N1' (NON-UNIQUE)
 194874        TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
                   'MTL_MATERIAL_TRANSACTIONS'
 846350         AND-EQUAL
 500017          INDEX (RANGE SCAN) OF 'ORGANIZATION_ID'
                     (NON-UNIQUE)
 348778          INDEX (RANGE SCAN) OF 'INVENTORY_ITEM_ID'
                     (NON-UNIQUE)
    132       TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
                  'MTL_TRANSACTION_TYPES'
    132        INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF
                   'MTL_TRANSACTION_TYPES_U1' (UNIQUE)


     32      TABLE ACCESS (BY INDEX ROWID) OF 'MTL_ITEM_LOCATIONS'

32 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF

                  'MTL_ITEM_LOCATIONS_U1' (UNIQUE)





********************************************************************************




******************  TEST Database  ***********

Optimizer goal: RULE
Parsing user id: 45 (APPS)

select
substr(mil.segment1||'.'||mil.segment2||'.'||mil.segment3,1,45) Locator,

mmt.subinventory_code Subinventory,
msi.segment1 PART_NO,
mmt.transaction_uom UOM,

sum(mmt.transaction_quantity)
from

mtl_item_locations mil,
mtl_transaction_types typ,
mtl_material_transactions mmt,
mtl_system_items msi,
mtl_parameters mp

and mmt.transaction_type_id=typ.transaction_type_id
and typ.transaction_type_name='Account alias receipt'
and mmt.locator_id=mil.inventory_location_id(+)
and mmt.organization_id=mil.organization_id(+)
and mil.subinventory_code = 'CORROSIVE'

group by mil.segment1,

mil.segment2,
mil.segment3,
mmt.subinventory_code,
msi.segment1,
mmt.transaction_uom


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        6   3718.80    3718.10        211   59865263          9          68

------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 8 3718.80 3718.10 211 59865263 9 68

Misses in library cache during parse: 0
Optimizer goal: RULE
Parsing user id: 45 (APPS)

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: RULE


     87   SORT (GROUP BY)

     87    HASH JOIN

     87     NESTED LOOPS

     87      HASH JOIN

   4557       NESTED LOOPS
   8764        TABLE ACCESS (FULL) OF 'MTL_ITEM_LOCATIONS'
57874905        TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
                   'MTL_MATERIAL_TRANSACTIONS'
386356905         INDEX   GOAL: ANALYZED (RANGE SCAN) OF
                    'MTL_MATERIAL_TRANSACTIONS_N5' (NON-UNIQUE)


     43       TABLE ACCESS   GOAL: ANALYZED (FULL) OF

                  'MTL_TRANSACTION_TYPES'


     87      TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF

                 'MTL_SYSTEM_ITEMS'


     87       INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF

                  'MTL_SYSTEM_ITEMS_U1' (UNIQUE)

223 TABLE ACCESS (FULL) OF 'MTL_PARAMETERS'

Received on Fri Jun 22 2001 - 12:04:19 CDT