FWIR, if there is a tie between two indexes, oracle will use the index that was
created/recreated
most recently.
Ron
[EMAIL PROTECTED]
[EMAIL PROTECTED]
"Karaoke: Japanese for migraine"
Brijesh.Gupta@Airl
iquide.com To: [EMAIL PROTECTED]
Sent by: cc:
[EMAIL PROTECTED] Subject: Rule base optimizer
selecting different execution path
( Tuning )
06/22/01 11:12 AM
Please respond to
ORACLE-L
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
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.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'
********************************************************************************
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ron Thomas
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
