RE: Rule base optimizer selecting different execution path ( Tuni

From: Gupta, Brijesh <Brijesh.Gupta_at_Airliquide.com>
Date: Fri, 22 Jun 2001 12:26:07 -0700
Message-ID: <F001.003343F8.20010622123857@fatcity.com>

Thanks
Riyaj
 It was the degree of parallelism on the indexes.

Brijesh

-----Original Message-----From: Riyaj_Shamsudeen_at_i2.com [mailto:Riyaj_Shamsudeen_at_i2.com]Sent: Friday, June 22, 2001 1:39 PMTo: Multiple recipients of list ORACLE-LSubject: 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 ? ThanksRiyaj "Re-yas"
 ShamsudeenCertified Oracle DBAi2 technologies www.i2.com

      
      "Gupta, Brijesh" 
        <Brijesh.Gupta_at_Airliquide.com> <FONT 
        face=sans-serif size=1>Sent by: root_at_fatcity.com 
        06/22/01 01:12 PM <FONT 
        face=sans-serif size=1>Please respond to ORACLE-L 
              <FONT 
        face=sans-serif size=1>        To:     
           Multiple recipients of list ORACLE-L 
        <ORACLE-L_at_fatcity.com> <FONT face=sans-serif 
        size=1>        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 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) <FONT
face="Times New Roman" size=2> 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) <FONT
face="Times New Roman" size=2>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 <FONT
face="Times New Roman" size=2> 'MTL_ITEM_LOCATIONS_U1' (UNIQUE)

<FONT face="Times New Roman"

  size=2>********************************************************************************<FONT

face="Times New Roman" size=3>

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 <FONT
face="Times New Roman" size=2> 'MTL_MATERIAL_TRANSACTIONS_N5'
(NON-UNIQUE) <FONT
face="Times New Roman" size=2> 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 <FONT
face="Times New Roman" size=2> 'MTL_SYSTEM_ITEMS_U1' (UNIQUE) 223 TABLE ACCESS (FULL) OF 'MTL_PARAMETERS'

<FONT face="Times New Roman"

  size=2>********************************************************************************<FONT

face="Times New Roman" size=3> Received on Fri Jun 22 2001 - 14:26:07 CDT