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
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 countcpu elapseddisk query currentrows
--- -- -- -- -- -- --
Parse1 0.010.01 0 0 0 0
Execute 1 0.000.00 0 0 0 0
Fetch2 51.68 901.33 85134 395083 0 4
--- -- -- -- -- -- --
total4 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
132NESTED LOOPS
2445NESTED 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)
194874TABLE 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)
132TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'MTL_TRANSACTION_TYPES'
132INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'MTL_TRANSACTION_TYPES_U1' (UNIQUE)
32 TABLE ACCESS (BY INDEX ROWID) OF 'MTL_ITEM_LOCATIONS'
32INDEX 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)
