Home » RDBMS Server » Performance Tuning » Why such a difference in explain plans (Oracle 9.2.5.0)
Why such a difference in explain plans [message #280723] Wed, 14 November 2007 10:35
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
There was a performance issue in our system.
I isolated the offending query and was able to tune it but am curious as to why such a dramatic difference.
The only difference in the select statements is the 2nd query adds the count(*) to the select list.

The 1st query takes minutes to run, the 2nd query runs in about 3 seconds.

I obviously will use the 2nd query but am very curious.

Query 1 (The bad one).
SELECT  plan_id,plan_updt_no, woi
      FROM pwui_oh_plan_v a
      where engine_type         = :vi_engine_type
        AND engine_model      = :vi_engine_model
        AND module_code      = :vi_module_code
        AND work_scope       = :vi_work_scope
        AND plnd_work_loc   = :vi_work_loc
        AND SYSDATE >= eff_from_date
        AND SYSDATE <= 
        NVL(eff_thru_date,TO_DATE(Sffnd_Date_Get('MaxDate'),
                           Sffnd_Date_Get('DateMask')))
        AND  woi                  is not null      
        AND act_no||nvl(sub_act_no,'0000') in 
        (
                select act_no||nvl(sub_act_no,'0000') 
                from  pwui_int_sap_order
                where sales_order    = :vi_sales_order and
                           engine_type   = :vi_engine_type and
                           engine_model = :vi_engine_model and
                           module_code = :vi_module_code and
                           work_scope  =  :vi_work_scope)
        AND (upper(cust_id) = upper(:vi_customer) 
         OR (upper(cust_id) = 'ALL'
                 and upper(:vi_customer) not in (select upper(e.exception_cust_id) 
from PWUI_DTL_PL_CUST_EXCEPTIONS e
  where e.plan_id = a.plan_id   and 
        e.plan_updt_no = a.plan_updt_no and 
        e.subject_no = a.subject_no
        and e.cust_id = a.cust_id)  -- 0003
                 )
             ) 
group by plan_id,plan_updt_no, woi


Here is the explain plan:

SELECT STATEMENT, GOAL = ALL_ROWS			Cost=10	Cardinality=1	Bytes=557
 SORT GROUP BY			Cost=10	Cardinality=1	Bytes=557
  FILTER					
   NESTED LOOPS			Cost=8	Cardinality=1	Bytes=557
    MERGE JOIN CARTESIAN			Cost=8	Cardinality=1	Bytes=535
     MERGE JOIN CARTESIAN			Cost=6	Cardinality=1	Bytes=506
      NESTED LOOPS			Cost=6	Cardinality=1	Bytes=459
       FILTER					
        NESTED LOOPS OUTER					
         NESTED LOOPS			Cost=6	Cardinality=1	Bytes=331
          NESTED LOOPS			Cost=6	Cardinality=1	Bytes=266
           MERGE JOIN CARTESIAN			Cost=6	Cardinality=1	Bytes=201
            NESTED LOOPS			Cost=4	Cardinality=1	Bytes=127
             MERGE JOIN CARTESIAN			Cost=4	Cardinality=1	Bytes=89
              TABLE ACCESS FULL	Object owner=SFMFG	Object name=PWUI_INT_SAP_ORDER	Cost=2	Cardinality=1	Bytes=47
              BUFFER SORT			Cost=2	Cardinality=1	Bytes=42
               TABLE ACCESS FULL	Object owner=SFMFG	Object name=PW_SFPL_PLAN_DESC_EXT	Cost=2	Cardinality=1	Bytes=42
             TABLE ACCESS BY INDEX ROWID	Object owner=SFMFG	Object name=SFPL_PLAN_DESC		Cardinality=1	Bytes=38
              INDEX UNIQUE SCAN	Object owner=SFMFG	Object name=SFPL_PLAN_DESC_PK		Cardinality=1	
            BUFFER SORT			Cost=6	Cardinality=1	Bytes=74
             TABLE ACCESS FULL	Object owner=SFMFG	Object name=PW_SFPL_PLAN_SUBJECT	Cost=2	Cardinality=1	Bytes=74
           TABLE ACCESS BY INDEX ROWID	Object owner=SFMFG	Object name=PWUI_DTL_PL_WORK_SCOPE		Cardinality=1	Bytes=65
            INDEX UNIQUE SCAN	Object owner=SFMFG	Object name=PWUI_DTL_PL_WORK_SCOPE_PK		Cardinality=1	
          TABLE ACCESS BY INDEX ROWID	Object owner=SFMFG	Object name=PWUI_DTL_PL_NETWORK_ACTIVITY		Cardinality=1	Bytes=65
           INDEX RANGE SCAN	Object owner=SFMFG	Object name=PWUI_DTL_PL_NETWORKACTIVITY_PK		Cardinality=1	
         INDEX RANGE SCAN	Object owner=SFMFG	Object name=PWUI_DTL_PL_SUBNETACTIVITY_PK		Cardinality=1	Bytes=58
       INDEX RANGE SCAN	Object owner=SFMFG	Object name=PWUI_DTL_PL_CUSTOMER_PK		Cardinality=1	Bytes=70
      BUFFER SORT			Cost=6	Cardinality=1	Bytes=47
       INDEX FULL SCAN	Object owner=SFMFG	Object name=PWUI_PL_DTL_ENGINE_MODEL_PK		Cardinality=1	Bytes=47
     BUFFER SORT			Cost=8	Cardinality=3	Bytes=87
      TABLE ACCESS FULL	Object owner=SFMFG	Object name=SFPL_PLAN_EFF	Cost=2	Cardinality=3	Bytes=87
    TABLE ACCESS BY INDEX ROWID	Object owner=SFMFG	Object name=SFPL_PLAN_REV		Cardinality=1	Bytes=22
     INDEX UNIQUE SCAN	Object owner=SFMFG	Object name=SFPL_PLAN_REV_PK		Cardinality=1	
   INDEX RANGE SCAN	Object owner=SFMFG	Object name=PWUI_DTL_PL_CUST_EXCEPTIONS_PK	Cost=1	Cardinality=1	Bytes=23



Here is the explain plan from the "good" query with the only difference being count(*) added to the select list.

SELECT STATEMENT, GOAL = ALL_ROWS			Cost=12	Cardinality=1	Bytes=518
 SORT GROUP BY			Cost=12	Cardinality=1	Bytes=518
  FILTER					
   HASH JOIN			Cost=11	Cardinality=1	Bytes=518
    NESTED LOOPS			Cost=6	Cardinality=1	Bytes=510
     NESTED LOOPS OUTER			Cost=4	Cardinality=1	Bytes=481
      NESTED LOOPS			Cost=4	Cardinality=1	Bytes=423
       NESTED LOOPS			Cost=4	Cardinality=1	Bytes=358
        NESTED LOOPS			Cost=4	Cardinality=1	Bytes=288
         NESTED LOOPS			Cost=4	Cardinality=1	Bytes=223
          NESTED LOOPS			Cost=3	Cardinality=1	Bytes=149
           NESTED LOOPS			Cost=3	Cardinality=1	Bytes=107
            NESTED LOOPS			Cost=2	Cardinality=1	Bytes=69
             INDEX SKIP SCAN	Object owner=SFMFG	Object name=PWUI_PL_DTL_ENGINE_MODEL_PK		Cardinality=1	Bytes=47
             INDEX RANGE SCAN	Object owner=SFMFG	Object name=SFPL_PLAN_REV_INDX001	Cost=1	Cardinality=1	Bytes=22
            TABLE ACCESS BY INDEX ROWID	Object owner=SFMFG	Object name=SFPL_PLAN_DESC	Cost=1	Cardinality=1	Bytes=38
             INDEX UNIQUE SCAN	Object owner=SFMFG	Object name=SFPL_PLAN_DESC_PK		Cardinality=1	
           TABLE ACCESS BY INDEX ROWID	Object owner=SFMFG	Object name=PW_SFPL_PLAN_DESC_EXT		Cardinality=1	Bytes=42
            INDEX UNIQUE SCAN	Object owner=SFMFG	Object name=PW_SFPL_PLAN_DESC_EXT_PK		Cardinality=1	
          TABLE ACCESS BY INDEX ROWID	Object owner=SFMFG	Object name=PW_SFPL_PLAN_SUBJECT	Cost=1	Cardinality=1	Bytes=74
           INDEX RANGE SCAN	Object owner=SFMFG	Object name=PW_SFPL_PLAN_SUBJECT_PK	Cost=1	Cardinality=1	
         TABLE ACCESS BY INDEX ROWID	Object owner=SFMFG	Object name=PWUI_DTL_PL_WORK_SCOPE		Cardinality=1	Bytes=65
          INDEX UNIQUE SCAN	Object owner=SFMFG	Object name=PWUI_DTL_PL_WORK_SCOPE_PK		Cardinality=1	
        INDEX RANGE SCAN	Object owner=SFMFG	Object name=PWUI_DTL_PL_CUSTOMER_PK		Cardinality=1	Bytes=70
       TABLE ACCESS BY INDEX ROWID	Object owner=SFMFG	Object name=PWUI_DTL_PL_NETWORK_ACTIVITY		Cardinality=1	Bytes=65
        INDEX RANGE SCAN	Object owner=SFMFG	Object name=PWUI_DTL_PL_NETWORKACTIVITY_PK		Cardinality=1	
      INDEX RANGE SCAN	Object owner=SFMFG	Object name=PWUI_DTL_PL_SUBNETACTIVITY_PK		Cardinality=1	Bytes=58
     TABLE ACCESS BY INDEX ROWID	Object owner=SFMFG	Object name=SFPL_PLAN_EFF	Cost=2	Cardinality=1	Bytes=29
      INDEX RANGE SCAN	Object owner=SFMFG	Object name=SFPL_PLAN_EFF_PK	Cost=1	Cardinality=1	
    VIEW	Object owner=SYS	Object name=VW_NSO_1	Cost=4	Cardinality=1	Bytes=8
     SORT UNIQUE			Cost=4	Cardinality=1	Bytes=47
      TABLE ACCESS FULL	Object owner=SFMFG	Object name=PWUI_INT_SAP_ORDER	Cost=2	Cardinality=1	Bytes=47
   INDEX RANGE SCAN	Object owner=SFMFG	Object name=PWUI_DTL_PL_CUST_EXCEPTIONS_PK	Cost=1	Cardinality=1	Bytes=23



Sorry for the length of this stuff.

[Updated on: Wed, 14 November 2007 10:38]

Report message to a moderator

Previous Topic: Need to perform INDEX RANGE SCAN instead of FTS
Next Topic: Urgent Please
Goto Forum:
  


Current Time: Sat Dec 10 03:28:03 CST 2016

Total time taken to generate the page: 0.05243 seconds