This discussion is archived
3 Replies Latest reply: Mar 21, 2013 6:57 AM by AnkitV RSS

Explan plan question !

AnkitV Newbie
Currently Being Moderated
Hi

I faced a situation in which query response time degraded after limiting the data set and creating an Index (both being used). I wonder why Oracle is not using a better (first one below) plan.

DB is Oracle 9i R2.
My original explain plan is :
----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   |  Name                       | Rows  | Bytes | Cost  | Pstart| Pstop |  TQ    |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                             |     1 |   206 |  3758 |       |       |        |      |            |
|   1 |  SORT GROUP BY              |                             |     1 |   206 |  3758 |       |       | 65,02  | P->S | QC (RAND)  |
|   2 |   SORT GROUP BY             |                             |     1 |   206 |  3758 |       |       | 65,01  | P->P | HASH       |
|*  3 |    HASH JOIN                |                             |     1 |   206 |  3754 |       |       | 65,01  | PCWP |            |
|   4 |     MERGE JOIN CARTESIAN    |                             |     2 |   160 |    82 |       |       | 65,00  | S->P | BROADCAST  |
|   5 |      MERGE JOIN CARTESIAN   |                             |     1 |    60 |    62 |       |       |        |      |            |
|   6 |       MERGE JOIN CARTESIAN  |                             |     1 |    40 |    41 |       |       |        |      |            |
|   7 |        VIEW                 | MTC EXCPN CATEGORY CD V     |     1 |    20 |    21 |       |       |        |      |            |
|   8 |         SORT ORDER BY       |                             |     1 |   348 |    21 |       |       |        |      |            |
|*  9 |          TABLE ACCESS FULL  | MTC_CD_LKUP                 |     1 |   348 |     5 |       |       |        |      |            |
|  10 |        BUFFER SORT          |                             |     1 |    20 |    41 |       |       |        |      |            |
|  11 |         VIEW                | MTC EXCPN TYPE V            |     1 |    20 |    21 |       |       |        |      |            |
|  12 |          SORT ORDER BY      |                             |     1 |   348 |    21 |       |       |        |      |            |
|* 13 |           TABLE ACCESS FULL | MTC_CD_LKUP                 |     1 |   348 |     5 |       |       |        |      |            |
|  14 |       BUFFER SORT           |                             |     1 |    20 |    41 |       |       |        |      |            |
|  15 |        VIEW                 | MTC EXCPN NUM V             |     1 |    20 |    21 |       |       |        |      |            |
|  16 |         SORT ORDER BY       |                             |     1 |   348 |    21 |       |       |        |      |            |
|* 17 |          TABLE ACCESS FULL  | MTC_CD_LKUP                 |     1 |   348 |     5 |       |       |        |      |            |
|  18 |      BUFFER SORT            |                             |     2 |    40 |    61 |       |       |        |      |            |
|  19 |       VIEW                  | MTC EXCPN COMPANY V         |     2 |    40 |    21 |       |       |        |      |            |
|  20 |        SORT ORDER BY        |                             |     2 |   696 |    21 |       |       |        |      |            |
|* 21 |         FILTER              |                             |       |       |       |       |       |        |      |            |
|* 22 |          TABLE ACCESS FULL  | MTC_CD_LKUP                 |     2 |   696 |     5 |       |       |        |      |            |
|  23 |     PARTITION RANGE ITERATOR|                             |       |       |       |   KEY |   KEY | 65,01  | PCWP |            |
|* 24 |      TABLE ACCESS FULL      | MTC_BLNG_EXCEPTION_DTL_ODS  |     8 |  1008 |  3672 |   KEY |   KEY | 65,01  | PCWP |            |
----------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("MTC_EXCPN_NUM_V"."MTC CD LKUP ID"=DECODE("MTC_BLNG_EXCEPTION_DTL_ODS"."SOURCE_SYSTEM_CD",'D',"MTC_BLNG_EXCEPTION_DTL_ODS"
              ."DIS_EXCPN_NUM","MTC_BLNG_EXCEPTION_DTL_ODS"."CIS_EXCPN_NUM") AND "MTC_EXCPN_CATEGORY_CD_V"."MTC CD LKUP 
              ID"="MTC_BLNG_EXCEPTION_DTL_ODS"."CATEGORY_CD" AND "MTC_EXCPN_TYPE_V"."MTC CD LKUP 
              ID"=TO_NUMBER("MTC_BLNG_EXCEPTION_DTL_ODS"."EXCPN_TYPE") AND "MTC_EXCPN_COMPANY_V"."MTC CD LKUP 
              ID"="MTC_BLNG_EXCEPTION_DTL_ODS"."COMPANY_CD")
   9 - filter(UPPER(TRIM("MTC_CD_LKUP"."CD_LKUP_TYPE"))='BLNG_EXCPN_CATEGORY_CD' AND 
              DECODE("MTC_CD_LKUP"."MTC_CD_REQ_IND",'Y',"MTC_CD_LKUP"."MTC_CD_VAL","MTC_CD_LKUP"."NATIVE_CD")='CG')
  13 - filter(UPPER(TRIM("MTC_CD_LKUP"."CD_LKUP_TYPE"))='BLNG_EXCPN_EXCPN_TYPE' AND 
              (DECODE("MTC_CD_LKUP"."MTC_CD_REQ_IND",'Y',"MTC_CD_LKUP"."MTC_CD_VAL","MTC_CD_LKUP"."NATIVE_CD")='A' OR 
              DECODE("MTC_CD_LKUP"."MTC_CD_REQ_IND",'Y',"MTC_CD_LKUP"."MTC_CD_VAL","MTC_CD_LKUP"."NATIVE_CD")='I'))
  17 - filter((UPPER(TRIM("MTC_CD_LKUP"."CD_LKUP_TYPE"))='BLNG_EXCPN_CIS_EXCPN_NUM' OR 
              UPPER(TRIM("MTC_CD_LKUP"."CD_LKUP_TYPE"))='BLNG_EXCPN_DIS_EXCPN_NUM') AND 
              TO_NUMBER(DECODE("MTC_CD_LKUP"."MTC_CD_REQ_IND",'Y',"MTC_CD_LKUP"."MTC_CD_VAL","MTC_CD_LKUP"."NATIVE_CD"))<>35002 AND 
              TO_NUMBER(DECODE("MTC_CD_LKUP"."MTC_CD_REQ_IND",'Y',"MTC_CD_LKUP"."MTC_CD_VAL","MTC_CD_LKUP"."NATIVE_CD"))<>35004 AND 
              TO_NUMBER(DECODE("MTC_CD_LKUP"."MTC_CD_REQ_IND",'Y',"MTC_CD_LKUP"."MTC_CD_VAL","MTC_CD_LKUP"."NATIVE_CD"))<>35008 AND 
              TO_NUMBER(DECODE("MTC_CD_LKUP"."MTC_CD_REQ_IND",'Y',"MTC_CD_LKUP"."MTC_CD_VAL","MTC_CD_LKUP"."NATIVE_CD"))<>35009 AND 
              TO_NUMBER(DECODE("MTC_CD_LKUP"."MTC_CD_REQ_IND",'Y',"MTC_CD_LKUP"."MTC_CD_VAL","MTC_CD_LKUP"."NATIVE_CD"))<>35010 AND 
              TO_NUMBER(DECODE("MTC_CD_LKUP"."MTC_CD_REQ_IND",'Y',"MTC_CD_LKUP"."MTC_CD_VAL","MTC_CD_LKUP"."NATIVE_CD"))<>35011 AND 
              TO_NUMBER(DECODE("MTC_CD_LKUP"."MTC_CD_REQ_IND",'Y',"MTC_CD_LKUP"."MTC_CD_VAL","MTC_CD_LKUP"."NATIVE_CD"))<>35013 AND 
              TO_NUMBER(DECODE("MTC_CD_LKUP"."MTC_CD_REQ_IND",'Y',"MTC_CD_LKUP"."MTC_CD_VAL","MTC_CD_LKUP"."NATIVE_CD"))<>35017 AND 
              TO_NUMBER(DECODE("MTC_CD_LKUP"."MTC_CD_REQ_IND",'Y',"MTC_CD_LKUP"."MTC_CD_VAL","MTC_CD_LKUP"."NATIVE_CD"))<>35018 AND 
              TO_NUMBER(DECODE("MTC_CD_LKUP"."MTC_CD_REQ_IND",'Y',"MTC_CD_LKUP"."MTC_CD_VAL","MTC_CD_LKUP"."NATIVE_CD"))<>35022 AND 
              TO_NUMBER(DECODE("MTC_CD_LKUP"."MTC_CD_REQ_IND",'Y',"MTC_CD_LKUP"."MTC_CD_VAL","MTC_CD_LKUP"."NATIVE_CD"))<>35025 AND 
              TO_NUMBER(DECODE("MTC_CD_LKUP"."MTC_CD_REQ_IND",'Y',"MTC_CD_LKUP"."MTC_CD_VAL","MTC_CD_LKUP"."NATIVE_CD"))<>35026 AND 
              TO_NUMBER(DECODE("MTC_CD_LKUP"."MTC_CD_REQ_IND",'Y',"MTC_CD_LKUP"."MTC_CD_VAL","MTC_CD_LKUP"."NATIVE_CD"))<>35027 AND 
              TO_NUMBER(DECODE("MTC_CD_LKUP"."MTC_CD_REQ_IND",'Y',"MTC_CD_LKUP"."MTC_CD_VAL","MTC_CD_LKUP"."NATIVE_CD"))<>35028)
  21 - filter(TO_DATE(CASE  WHEN (SYSDATE@!=TRUNC(SYSDATE@!,'fmmm') OR (TO_CHAR(SYSDATE@!,'DY')='MON' AND 
              SYSDATE@!-3=TRUNC(SYSDATE@!,'fmmm')) OR SYSDATE@!-2=TRUNC(SYSDATE@!,'fmmm') OR SYSDATE@!-1=TRUNC(SYSDATE@!,'fmmm')) THEN 
              TRUNC(ADD_MONTHS(SYSDATE@!,-1),'fmmm') ELSE TRUNC(SYSDATE@!,'fmmm') END )<=TO_DATE(CASE  WHEN (SYSDATE@!=TRUNC(SYSDATE@!,'fmmm') OR 
              (TO_CHAR(SYSDATE@!,'DY')='MON' AND SYSDATE@!-3=TRUNC(SYSDATE@!,'fmmm')) OR SYSDATE@!-2=TRUNC(SYSDATE@!,'fmmm') OR 
              SYSDATE@!-1=TRUNC(SYSDATE@!,'fmmm')) THEN TRUNC(SYSDATE@!,'fmmm')-1 ELSE SYSDATE@!-1 END ))
  22 - filter(UPPER(TRIM("MTC_CD_LKUP"."CD_LKUP_TYPE"))='BLNG_EXCPN_COMPANY_CD' AND 
              (DECODE("MTC_CD_LKUP"."MTC_CD_REQ_IND",'Y',"MTC_CD_LKUP"."MTC_CD_VAL","MTC_CD_LKUP"."NATIVE_CD")='32' OR 
              DECODE("MTC_CD_LKUP"."MTC_CD_REQ_IND",'Y',"MTC_CD_LKUP"."MTC_CD_VAL","MTC_CD_LKUP"."NATIVE_CD")='34' OR 
              DECODE("MTC_CD_LKUP"."MTC_CD_REQ_IND",'Y',"MTC_CD_LKUP"."MTC_CD_VAL","MTC_CD_LKUP"."NATIVE_CD")='35' OR 
              DECODE("MTC_CD_LKUP"."MTC_CD_REQ_IND",'Y',"MTC_CD_LKUP"."MTC_CD_VAL","MTC_CD_LKUP"."NATIVE_CD")='37' OR 
              DECODE("MTC_CD_LKUP"."MTC_CD_REQ_IND",'Y',"MTC_CD_LKUP"."MTC_CD_VAL","MTC_CD_LKUP"."NATIVE_CD")='38' OR 
              DECODE("MTC_CD_LKUP"."MTC_CD_REQ_IND",'Y',"MTC_CD_LKUP"."MTC_CD_VAL","MTC_CD_LKUP"."NATIVE_CD")='53'))
  24 - filter("MTC_BLNG_EXCEPTION_DTL_ODS"."RPT_EXCPN_CREATE_DT">=TO_DATE(CASE  WHEN (SYSDATE@!=TRUNC(SYSDATE@!,'fmmm') OR 
              (TO_CHAR(SYSDATE@!,'DY')='MON' AND SYSDATE@!-3=TRUNC(SYSDATE@!,'fmmm')) OR SYSDATE@!-2=TRUNC(SYSDATE@!,'fmmm') OR 
              SYSDATE@!-1=TRUNC(SYSDATE@!,'fmmm')) THEN TRUNC(ADD_MONTHS(SYSDATE@!,-1),'fmmm') ELSE TRUNC(SYSDATE@!,'fmmm') END ) AND 
              "MTC_BLNG_EXCEPTION_DTL_ODS"."RPT_EXCPN_CREATE_DT"<=TO_DATE(CASE  WHEN (SYSDATE@!=TRUNC(SYSDATE@!,'fmmm') OR 
              (TO_CHAR(SYSDATE@!,'DY')='MON' AND SYSDATE@!-3=TRUNC(SYSDATE@!,'fmmm')) OR SYSDATE@!-2=TRUNC(SYSDATE@!,'fmmm') OR 
              SYSDATE@!-1=TRUNC(SYSDATE@!,'fmmm')) THEN TRUNC(SYSDATE@!,'fmmm')-1 ELSE SYSDATE@!-1 END ) AND 
              NVL("MTC_BLNG_EXCEPTION_DTL_ODS"."ASSIGNED_USER_ID",'MTCADMIN')<>'AUTOADJ' AND 
              NVL("MTC_BLNG_EXCEPTION_DTL_ODS"."ASSIGNED_USER_ID",'MTCADMIN')<>'BEOMEMO' AND 
              NVL("MTC_BLNG_EXCEPTION_DTL_ODS"."ASSIGNED_USER_ID",'MTCADMIN')<>'SYSTEM')
Afterwards, I modified the query to use a single partition and added a local prefix index named BLNG_EXCPN_RPT_INS_TMST_LO_IDX.
The explain plan in this case is :
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           |  Name                           | Rows  | Bytes | Cost  | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                                 |     1 |   180 |   101 |       |       |
|   1 |  SORT GROUP BY                      |                                 |     1 |   180 |   101 |       |       |
|*  2 |   HASH JOIN                         |                                 |     1 |   180 |    85 |       |       |
|*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID| MTC_BLNG_EXCEPTION_DTL_ODS      |     1 |   100 |     2 |    62 |    62 |
|   4 |     NESTED LOOPS                    |                                 |     1 |   160 |    64 |       |       |
|   5 |      MERGE JOIN CARTESIAN           |                                 |     1 |    60 |    62 |       |       |
|   6 |       MERGE JOIN CARTESIAN          |                                 |     1 |    40 |    41 |       |       |
|   7 |        VIEW                         | MTC EXCPN CATEGORY CD V         |     1 |    20 |    21 |       |       |
|   8 |         SORT ORDER BY               |                                 |     1 |   348 |    21 |       |       |
|*  9 |          TABLE ACCESS FULL          | MTC_CD_LKUP                     |     1 |   348 |     5 |       |       |
|  10 |        BUFFER SORT                  |                                 |     1 |    20 |    41 |       |       |
|  11 |         VIEW                        | MTC EXCPN TYPE V                |     1 |    20 |    21 |       |       |
|  12 |          SORT ORDER BY              |                                 |     1 |   348 |    21 |       |       |
|* 13 |           TABLE ACCESS FULL         | MTC_CD_LKUP                     |     1 |   348 |     5 |       |       |
|  14 |       BUFFER SORT                   |                                 |     1 |    20 |    41 |       |       |
|  15 |        VIEW                         | MTC EXCPN NUM V                 |     1 |    20 |    21 |       |       |
|  16 |         SORT ORDER BY               |                                 |     1 |   348 |    21 |       |       |
|* 17 |          TABLE ACCESS FULL          | MTC_CD_LKUP                     |     1 |   348 |     5 |       |       |
|* 18 |      INDEX RANGE SCAN               | BLNG_EXCPN_RPT_INS_TMST_LO_IDX  |  3330 |       |     1 |    62 |    62 |
|  19 |    VIEW                             | MTC EXCPN COMPANY V             |     2 |    40 |    21 |       |       |
|  20 |     SORT ORDER BY                   |                                 |     2 |   696 |    21 |       |       |
|* 21 |      FILTER                         |                                 |       |       |       |       |       |
|* 22 |       TABLE ACCESS FULL             | MTC_CD_LKUP                     |     2 |   696 |     5 |       |       |
-----------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("MTC_EXCPN_COMPANY_V"."MTC CD LKUP ID"="MTC_BLNG_EXCEPTION_DTL_ODS"."COMPANY_CD")
   3 - filter(NVL("MTC_BLNG_EXCEPTION_DTL_ODS"."ASSIGNED_USER_ID",'MTCADMIN')<>'AUTOADJ' AND 
              NVL("MTC_BLNG_EXCEPTION_DTL_ODS"."ASSIGNED_USER_ID",'MTCADMIN')<>'BEOMEMO' AND 
              NVL("MTC_BLNG_EXCEPTION_DTL_ODS"."ASSIGNED_USER_ID",'MTCADMIN')<>'SYSTEM' AND "MTC_EXCPN_NUM_V"."MTC CD LKUP 
              ID"=DECODE("MTC_BLNG_EXCEPTION_DTL_ODS"."SOURCE_SYSTEM_CD",'D',"MTC_BLNG_EXCEPTION_DTL_ODS"."DIS_EXCPN_NUM","MTC_BLNG_E
              XCEPTION_DTL_ODS"."CIS_EXCPN_NUM") AND "MTC_EXCPN_CATEGORY_CD_V"."MTC CD LKUP 
              ID"="MTC_BLNG_EXCEPTION_DTL_ODS"."CATEGORY_CD" AND "MTC_EXCPN_TYPE_V"."MTC CD LKUP 
              ID"=TO_NUMBER("MTC_BLNG_EXCEPTION_DTL_ODS"."EXCPN_TYPE"))
   9 - filter(UPPER(TRIM("MTC_CD_LKUP"."CD_LKUP_TYPE"))='BLNG_EXCPN_CATEGORY_CD' AND 
              DECODE("MTC_CD_LKUP"."MTC_CD_REQ_IND",'Y',"MTC_CD_LKUP"."MTC_CD_VAL","MTC_CD_LKUP"."NATIVE_CD")='CG')
  13 - filter(UPPER(TRIM("MTC_CD_LKUP"."CD_LKUP_TYPE"))='BLNG_EXCPN_EXCPN_TYPE' AND 
              (DECODE("MTC_CD_LKUP"."MTC_CD_REQ_IND",'Y',"MTC_CD_LKUP"."MTC_CD_VAL","MTC_CD_LKUP"."NATIVE_CD")='A' OR 
              DECODE("MTC_CD_LKUP"."MTC_CD_REQ_IND",'Y',"MTC_CD_LKUP"."MTC_CD_VAL","MTC_CD_LKUP"."NATIVE_CD")='I'))
  17 - filter((UPPER(TRIM("MTC_CD_LKUP"."CD_LKUP_TYPE"))='BLNG_EXCPN_CIS_EXCPN_NUM' OR 
              UPPER(TRIM("MTC_CD_LKUP"."CD_LKUP_TYPE"))='BLNG_EXCPN_DIS_EXCPN_NUM') AND 
              TO_NUMBER(DECODE("MTC_CD_LKUP"."MTC_CD_REQ_IND",'Y',"MTC_CD_LKUP"."MTC_CD_VAL","MTC_CD_LKUP"."NATIVE_CD"))<>35002 AND 
              TO_NUMBER(DECODE("MTC_CD_LKUP"."MTC_CD_REQ_IND",'Y',"MTC_CD_LKUP"."MTC_CD_VAL","MTC_CD_LKUP"."NATIVE_CD"))<>35004 AND 
              TO_NUMBER(DECODE("MTC_CD_LKUP"."MTC_CD_REQ_IND",'Y',"MTC_CD_LKUP"."MTC_CD_VAL","MTC_CD_LKUP"."NATIVE_CD"))<>35008 AND 
              TO_NUMBER(DECODE("MTC_CD_LKUP"."MTC_CD_REQ_IND",'Y',"MTC_CD_LKUP"."MTC_CD_VAL","MTC_CD_LKUP"."NATIVE_CD"))<>35009 AND 
              TO_NUMBER(DECODE("MTC_CD_LKUP"."MTC_CD_REQ_IND",'Y',"MTC_CD_LKUP"."MTC_CD_VAL","MTC_CD_LKUP"."NATIVE_CD"))<>35010 AND 
              TO_NUMBER(DECODE("MTC_CD_LKUP"."MTC_CD_REQ_IND",'Y',"MTC_CD_LKUP"."MTC_CD_VAL","MTC_CD_LKUP"."NATIVE_CD"))<>35011 AND 
              TO_NUMBER(DECODE("MTC_CD_LKUP"."MTC_CD_REQ_IND",'Y',"MTC_CD_LKUP"."MTC_CD_VAL","MTC_CD_LKUP"."NATIVE_CD"))<>35013 AND 
              TO_NUMBER(DECODE("MTC_CD_LKUP"."MTC_CD_REQ_IND",'Y',"MTC_CD_LKUP"."MTC_CD_VAL","MTC_CD_LKUP"."NATIVE_CD"))<>35017 AND 
              TO_NUMBER(DECODE("MTC_CD_LKUP"."MTC_CD_REQ_IND",'Y',"MTC_CD_LKUP"."MTC_CD_VAL","MTC_CD_LKUP"."NATIVE_CD"))<>35018 AND 
              TO_NUMBER(DECODE("MTC_CD_LKUP"."MTC_CD_REQ_IND",'Y',"MTC_CD_LKUP"."MTC_CD_VAL","MTC_CD_LKUP"."NATIVE_CD"))<>35022 AND 
              TO_NUMBER(DECODE("MTC_CD_LKUP"."MTC_CD_REQ_IND",'Y',"MTC_CD_LKUP"."MTC_CD_VAL","MTC_CD_LKUP"."NATIVE_CD"))<>35025 AND 
              TO_NUMBER(DECODE("MTC_CD_LKUP"."MTC_CD_REQ_IND",'Y',"MTC_CD_LKUP"."MTC_CD_VAL","MTC_CD_LKUP"."NATIVE_CD"))<>35026 AND 
              TO_NUMBER(DECODE("MTC_CD_LKUP"."MTC_CD_REQ_IND",'Y',"MTC_CD_LKUP"."MTC_CD_VAL","MTC_CD_LKUP"."NATIVE_CD"))<>35027 AND 
              TO_NUMBER(DECODE("MTC_CD_LKUP"."MTC_CD_REQ_IND",'Y',"MTC_CD_LKUP"."MTC_CD_VAL","MTC_CD_LKUP"."NATIVE_CD"))<>35028)
  18 - access("MTC_BLNG_EXCEPTION_DTL_ODS"."RPT_EXCPN_CREATE_DT">=TO_DATE(CASE  WHEN 
              (SYSDATE@!=TRUNC(SYSDATE@!,'fmmm') OR (TO_CHAR(SYSDATE@!,'DY')='MON' AND SYSDATE@!-3=TRUNC(SYSDATE@!,'fmmm')) OR 
              SYSDATE@!-2=TRUNC(SYSDATE@!,'fmmm') OR SYSDATE@!-1=TRUNC(SYSDATE@!,'fmmm')) THEN 
              TRUNC(ADD_MONTHS(SYSDATE@!,-1),'fmmm') ELSE TRUNC(SYSDATE@!,'fmmm') END ) AND 
              "MTC_BLNG_EXCEPTION_DTL_ODS"."ROW_INSERT_TMST">=SYSDATE@!-120 AND 
              "MTC_BLNG_EXCEPTION_DTL_ODS"."RPT_EXCPN_CREATE_DT"<=TO_DATE(CASE  WHEN (SYSDATE@!=TRUNC(SYSDATE@!,'fmmm') OR 
              (TO_CHAR(SYSDATE@!,'DY')='MON' AND SYSDATE@!-3=TRUNC(SYSDATE@!,'fmmm')) OR SYSDATE@!-2=TRUNC(SYSDATE@!,'fmmm') OR 
              SYSDATE@!-1=TRUNC(SYSDATE@!,'fmmm')) THEN TRUNC(SYSDATE@!,'fmmm')-1 ELSE SYSDATE@!-1 END ) AND 
              "MTC_BLNG_EXCEPTION_DTL_ODS"."ROW_INSERT_TMST"<=SYSDATE@!)
       filter("MTC_BLNG_EXCEPTION_DTL_ODS"."ROW_INSERT_TMST">=SYSDATE@!-120 AND 
              "MTC_BLNG_EXCEPTION_DTL_ODS"."ROW_INSERT_TMST"<=SYSDATE@!)
  21 - filter(SYSDATE@!-120<=SYSDATE@! AND TO_DATE(CASE  WHEN (SYSDATE@!=TRUNC(SYSDATE@!,'fmmm') OR 
              (TO_CHAR(SYSDATE@!,'DY')='MON' AND SYSDATE@!-3=TRUNC(SYSDATE@!,'fmmm')) OR SYSDATE@!-2=TRUNC(SYSDATE@!,'fmmm') OR 
              SYSDATE@!-1=TRUNC(SYSDATE@!,'fmmm')) THEN TRUNC(ADD_MONTHS(SYSDATE@!,-1),'fmmm') ELSE TRUNC(SYSDATE@!,'fmmm') END 
              )<=TO_DATE(CASE  WHEN (SYSDATE@!=TRUNC(SYSDATE@!,'fmmm') OR (TO_CHAR(SYSDATE@!,'DY')='MON' AND 
              SYSDATE@!-3=TRUNC(SYSDATE@!,'fmmm')) OR SYSDATE@!-2=TRUNC(SYSDATE@!,'fmmm') OR SYSDATE@!-1=TRUNC(SYSDATE@!,'fmmm')) 
              THEN TRUNC(SYSDATE@!,'fmmm')-1 ELSE SYSDATE@!-1 END ))
  22 - filter(UPPER(TRIM("MTC_CD_LKUP"."CD_LKUP_TYPE"))='BLNG_EXCPN_COMPANY_CD' AND 
              (DECODE("MTC_CD_LKUP"."MTC_CD_REQ_IND",'Y',"MTC_CD_LKUP"."MTC_CD_VAL","MTC_CD_LKUP"."NATIVE_CD")='32' OR 
              DECODE("MTC_CD_LKUP"."MTC_CD_REQ_IND",'Y',"MTC_CD_LKUP"."MTC_CD_VAL","MTC_CD_LKUP"."NATIVE_CD")='34' OR 
              DECODE("MTC_CD_LKUP"."MTC_CD_REQ_IND",'Y',"MTC_CD_LKUP"."MTC_CD_VAL","MTC_CD_LKUP"."NATIVE_CD")='35' OR 
              DECODE("MTC_CD_LKUP"."MTC_CD_REQ_IND",'Y',"MTC_CD_LKUP"."MTC_CD_VAL","MTC_CD_LKUP"."NATIVE_CD")='37' OR 
              DECODE("MTC_CD_LKUP"."MTC_CD_REQ_IND",'Y',"MTC_CD_LKUP"."MTC_CD_VAL","MTC_CD_LKUP"."NATIVE_CD")='38' OR 
              DECODE("MTC_CD_LKUP"."MTC_CD_REQ_IND",'Y',"MTC_CD_LKUP"."MTC_CD_VAL","MTC_CD_LKUP"."NATIVE_CD")='53'))
In case of first plan , query took *113* s to complete.
In case of second, it took *1980* s to complete. As can be seen from the plan above, teh new index is gettign utilized, even then the query performance has degraded !! I understand that sometimes indexes are not good.

My question is why Oracle is NOT using more efficient first plan (even if it involves full table scan and NO index use) which executed in very-2 less time ??

Thanks
  • 1. Re: Explan plan question !
    Dom Brooks Guru
    Currently Being Moderated
    See template tuning thread [url https://forums.oracle.com/forums/thread.jspa?threadID=863295]How to post a sql tuning request.

    You need to get the actual execution statistics and compare with the actuals.

    "Bad" plans happen when estimates are inaccurate for whatever reason.

    The prime suspect would be estimates for "1" row particular where MERGE JOIN CARTESIAN /BUFFER SORT is involved and where actual rowsource cardinalities are > 1.

    Also functions applied to columns are a typical cause for inaccurate estimates and/or preventing suitable index access.
  • 2. Re: Explan plan question !
    AnkitV Newbie
    Currently Being Moderated
    Hi

    Well, this time I ran some tests in 10gR2 database.
    I analysed whole schema using dbms_stats, so all tables and indexes are analysed now.

    I ran 2 queries, want your views about their EXPLAIN plans. Table here in concern is MTC_BLNG_EXCEPTION_DTL_ODS.

    First query finished in 243 s with FULL table scan when NO Index was there on table.
    When I created a local prefixed index, it took around 2000 s with following plan. Please note at bottom step #24 its doing Index range scan, then in #3, its accessing table using Index rowid. Performance has degraded USING INDEX. I wonder WHY Oracle did not choose FULL scan ?
    -----------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                           |  Name                           | Rows  | Bytes | Cost  | Pstart| Pstop |
    -----------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                    |                                 |     1 |   252 |   258 |       |       |
    |   1 |  WINDOW BUFFER                      |                                 |     1 |   252 |   258 |       |       |
    |   2 |   SORT GROUP BY                     |                                 |     1 |   252 |   258 |       |       |
    |*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID| MTC_BLNG_EXCEPTION_DTL_ODS      |     1 |    53 |   160 |    62 |    62 |
    |   4 |     NESTED LOOPS                    |                                 |     1 |   252 |   242 |       |       |
    |   5 |      MERGE JOIN CARTESIAN           |                                 |     1 |   199 |    82 |       |       |
    |   6 |       MERGE JOIN CARTESIAN          |                                 |     1 |   179 |    62 |       |       |
    |   7 |        MERGE JOIN CARTESIAN         |                                 |     1 |   167 |    41 |       |       |
    |   8 |         VIEW                        | MTC EXCPN COMPANY V             |     1 |    18 |    21 |       |       |
    |   9 |          SORT ORDER BY              |                                 |     1 |    91 |    21 |       |       |
    |* 10 |           FILTER                    |                                 |       |       |       |       |       |
    |* 11 |            TABLE ACCESS FULL        | MTC_CD_LKUP                     |     1 |    91 |     5 |       |       |
    |  12 |         BUFFER SORT                 |                                 |     1 |   149 |    41 |       |       |
    |  13 |          VIEW                       | MTC EXCPN NUM V                 |     1 |   149 |    21 |       |       |
    |  14 |           SORT ORDER BY             |                                 |     1 |    91 |    21 |       |       |
    |* 15 |            TABLE ACCESS FULL        | MTC_CD_LKUP                     |     1 |    91 |     5 |       |       |
    |  16 |        BUFFER SORT                  |                                 |     1 |    12 |    41 |       |       |
    |  17 |         VIEW                        | MTC EXCPN CATEGORY CD V         |     1 |    12 |    21 |       |       |
    |  18 |          SORT ORDER BY              |                                 |     1 |    91 |    21 |       |       |
    |* 19 |           TABLE ACCESS FULL         | MTC_CD_LKUP                     |     1 |    91 |     5 |       |       |
    |  20 |       BUFFER SORT                   |                                 |     1 |    20 |    61 |       |       |
    |  21 |        VIEW                         | MTC EXCPN TYPE V                |     1 |    20 |    21 |       |       |
    |  22 |         SORT ORDER BY               |                                 |     1 |    91 |    21 |       |       |
    |* 23 |          TABLE ACCESS FULL          | MTC_CD_LKUP                     |     1 |    91 |     5 |       |       |
    |* 24 |      INDEX RANGE SCAN               | BLNG_EXCPN_RPT_INS_TMST_LO_IDX  | 38019 |       |   145 |    62 |    62 |
    -----------------------------------------------------------------------------------------------------------------------
    In second query, following plan is coming. FULL scan was BAD, same INDEX was created, and following plan came out.
    NOTE that #31 is doing Index range scan and then IMMEDIATELY feeding to #30 for table access by rowid, thereby limiting rows to 1056 from 38019.
    --------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                              |  Name                           | Rows  | Bytes | Cost  | Pstart| Pstop |
    --------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                       |                                 |     1 |   222 |   298 |       |       |
    |   1 |  WINDOW SORT                           |                                 |     1 |   222 |   298 |       |       |
    |   2 |   VIEW                                 |                                 |     1 |   222 |   298 |       |       |
    |   3 |    WINDOW BUFFER                       |                                 |     1 |   266 |   298 |       |       |
    |   4 |     SORT GROUP BY                      |                                 |     1 |   266 |   298 |       |       |
    |*  5 |      HASH JOIN                         |                                 |     1 |   266 |   283 |       |       |
    |   6 |       MERGE JOIN CARTESIAN             |                                 |    22 |  4488 |   103 |       |       |
    |   7 |        MERGE JOIN CARTESIAN            |                                 |     1 |    70 |    82 |       |       |
    |   8 |         MERGE JOIN CARTESIAN           |                                 |     1 |    50 |    62 |       |       |
    |   9 |          MERGE JOIN CARTESIAN          |                                 |     1 |    38 |    41 |       |       |
    |  10 |           VIEW                         | MTC EXCPN TYPE V                |     1 |    20 |    21 |       |       |
    |  11 |            SORT ORDER BY               |                                 |     1 |    91 |    21 |       |       |
    |* 12 |             TABLE ACCESS FULL          | MTC_CD_LKUP                     |     1 |    91 |     5 |       |       |
    |  13 |           BUFFER SORT                  |                                 |     1 |    18 |    41 |       |       |
    |  14 |            VIEW                        | MTC EXCPN COMPANY V             |     1 |    18 |    21 |       |       |
    |  15 |             SORT ORDER BY              |                                 |     1 |    91 |    21 |       |       |
    |* 16 |              TABLE ACCESS FULL         | MTC_CD_LKUP                     |     1 |    91 |     5 |       |       |
    |  17 |          BUFFER SORT                   |                                 |     1 |    12 |    41 |       |       |
    |  18 |           VIEW                         | MTC EXCPN CATEGORY CD V         |     1 |    12 |    21 |       |       |
    |  19 |            SORT ORDER BY               |                                 |     1 |    91 |    21 |       |       |
    |* 20 |             TABLE ACCESS FULL          | MTC_CD_LKUP                     |     1 |    91 |     5 |       |       |
    |  21 |         BUFFER SORT                    |                                 |     1 |    20 |    61 |       |       |
    |  22 |          VIEW                          | MTC EXCPN NUM V                 |     1 |    20 |    21 |       |       |
    |  23 |           SORT ORDER BY                |                                 |     1 |    91 |    21 |       |       |
    |* 24 |            TABLE ACCESS FULL           | MTC_CD_LKUP                     |     1 |    91 |     5 |       |       |
    |  25 |        BUFFER SORT                     |                                 |    22 |  2948 |    82 |       |       |
    |  26 |         VIEW                           | MTC EXCPN CURRENT STATUS V      |    22 |  2948 |    21 |       |       |
    |  27 |          SORT ORDER BY                 |                                 |    22 |  2002 |    21 |       |       |
    |* 28 |           FILTER                       |                                 |       |       |       |       |       |
    |* 29 |            TABLE ACCESS FULL           | MTC_CD_LKUP                     |    22 |  2002 |     5 |       |       |
    |  30 |       TABLE ACCESS BY LOCAL INDEX ROWID| MTC_BLNG_EXCEPTION_DTL_ODS      |  1056 | 65472 |   179 |    62 |    62 |
    |* 31 |        INDEX RANGE SCAN                | BLNG_EXCPN_CMP_INS_TMST_LO_IDX  | 38019 |       |   123 |    62 |    62 |
    --------------------------------------------------------------------------------------------------------------------------
    I wonder, why Oracle didn't feed rowids immediately to table after doing Index range scan in First query, like it did in second query ??

    Thanks.
  • 3. Re: Explan plan question !
    AnkitV Newbie
    Currently Being Moderated
    Closing the question as will post a new question in a different manner.
    Thanks.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points