-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Friday, June 22, 2001 1:39 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: Rule base optimizer selecting different execution path ( Tuning )
Brijesh
There are few conditions where the optimizer will select CBO even when the optimizer_mode is set to rule. Do you have any objects in the schema with a parallelism >1 (tables and indexes) ? One of the common problem is that DBAs rebuild the index with higher parallelism and forget to change the parallelism back to noparallel. Parallelism >1 will turn on CBO. What is your optimizer_mode ? I assume it is rule. Let us know if not.
Seeing hash_join in your second plan, I am positive that the CBO is being turned on. Do you have any hints in the code ? Does your session change the optimizer_goal at the session level by any chance ?
Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies www.i2.com
"Gupta, Brijesh" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]06/22/01 01:12 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: Rule base optimizer selecting different execution path ( Tuning )
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
BrijeshHere 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_uomcall 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 4Misses 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_uomcall 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 68Misses 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'
********************************************************************************
Thanks
Riyaj
It was the degree of
parallelism on the indexes.
Brijesh
