Performance Issues after Upgrading to 10g Release 2 - Apps Upgrade 11.5.9 to 11.5.10

Upgrading from 9i to 10g has been giving sleepness nights to many of the dba's and application team. There are many changes that has been incorporated in 10g, like, method_opt that defaults to "for all columns size auto", System Statistics is mandatory in 10g etc.



Optimizer behaviour has also significantly changed between the two versions, therefore, a proper testing of the entire application before planning for production upgrade seems to be a viable choice.



Recently, I visited one of my customer for resolving a performance issue reported on their test instance. One thing that I would like to appreciate of this customer is that any major change, be it an Apps Upgrade or a DB Upgrade or a Migrating to a new Storage or Hardware or Platform, they have a planned strategy that involves thorough testing of an application (functional and performance) and once satisfied, plan for production.




This time the customer had planned an Apps Upgrade from 11.5.9 to 11.5.10 alongwith Database Upgrade from 9i to 10g release 2. After the upgrade (on test), there were some performance issues reported with almost 20% of the custom queries taking significant amount of time as compared to 9i. As mentioned, these issues were reported in the custom queries on the base apps tables. There are many reports developed by their development team and were very critical.

Therefore, though the base apps was working fine, the concern was for these reports. Moreover, a major performance issue was inevitable after production upgrade due to the significant amount of I/O's done by these queries. Therefore, resolving this issue before production upgrade was one of the top most agenda for the customer.



Whenever a performance issue arises after an Upgrade, and if this issue is due to the change in the execution plans, there are two approach that needs to be adopted depending on the situation. These are :

1. Rewrite a Query - This can be done if small number of queries are affected and this option is feasible.
   
2. Global Fix - This is the only feasible solution if a large number of queries are affected as rewrite would be time consuming.
   

In this blog, I will go through each of the above two approach that I used to optimize the performance of the queries, and this ultimately means, the performance of the overall database. I will also walk through the reasons for taking the approach best suited for each of these cases alongwith the execution plans in 9i and 10g.



Let us start with Approach 1, which is, Rewrite a Query. There were certain queries, that were taking most of the resources, on MTL_MATERIAL_TRANSACTIONS table. This is a base apps table. Since the query is too huge, I will paste only the relevant problematic portion of the query, alongwith the execution plan.

Relevant Problematic portion of the Query

FROM JA_IN_RECEIPT_TAX_LINES  JIRTL,
 RCV_SHIPMENT_HEADERS   RSH,
 RCV_TRANSACTIONS   A,
 RCV_LOT_TRANSACTIONS  RLT,
 MTL_SYSTEM_ITEMS_B  MSI,
 (
 SELECT  /*+ INDEX(MMT  MTL_MATERIAL_TRANSACTIONS_U1,MTL_MATERIAL_TRANSACTIONS_N5)
      INDEX(MTLN  MTL_TRANSACTION_LOT_NUMBERS_N1)
  */ 
  MMT.TRANSACTION_ID   TRANS_ID,
  MMT.ATTRIBUTE4    SUB_CONT_ID,
  MMT.ATTRIBUTE3    SUB_CONT_NAME,
  TRUNC(MMT.TRANSACTION_DATE)   ISS_DT,
  LOT_NUMBER    ISS_LOT,
  MMT.INVENTORY_ITEM_ID   ISS_ITEM,
  MMT.TRANSACTION_UOM   ISS_UOM,
  ABS((MTLN.TRANSACTION_QUANTITY))  ISS_QTY
 FROM  MTL_TRANSACTION_LOT_NUMBERS  MTLN,
  MTL_MATERIAL_TRANSACTIONS  MMT
 WHERE TRUNC(MMT.TRANSACTION_DATE) BETWEEN :START_DATE AND :END_DATE 
 AND MMT.TRANSACTION_TYPE_ID    = :TRANSACTION_TYPE_ID
 AND MMT.ORGANIZATION_ID     = :ORGANIZATION_ID
 AND MTLN.TRANSACTION_ID     = MMT.TRANSACTION_ID  
 ) ISS_DTL

The Execution plan for the Query in the Inline View is worth Investigating as this is where the problem lies. As can be seen from the Query, the developer has hinted the query in the inline view to use an Index MTL_MATERIAL_TRANSACTIONS_N5 and the optimizer obeyed the hint. The difference in the way this Index has been used is clearly visible from the plan.

9i Plan

|             PARTITION RANGE ALL                    |                               |       |      |        |
|              TABLE ACCESS BY LOCAL INDEX ROWID     |MTL_MATERIAL_TRANSACTIONS      |    14 |  476 |    434 |
|               INDEX RANGE SCAN                     |MTL_MATERIAL_TRANSACTIONS_N5   |     1K|      |   2476 |

10g Plan

|  15 |                PARTITION RANGE ALL               |                                |    16 |   544 | 21384   (3)| 00:04:17 |     1 |    24 |
|* 16 |                 TABLE ACCESS BY LOCAL INDEX ROWID| MTL_MATERIAL_TRANSACTIONS      |    16 |   544 | 21384   (3)| 00:04:17 |     1 |    24 |
|* 17 |                  INDEX FULL SCAN                 | MTL_MATERIAL_TRANSACTIONS_N5   |  1611 |       | 21323   (3)| 00:04:16 |     1 |    24 |

An INDEX FULL SCAN in 10g against INDEX RANGE SCAN in 9i. Why has this changed ? Moreover, the access_predicates and filter_predicates for this step does not give any clue. The access predicate for the Index Scan step (from 10g) is shown below (for 9i, the access predicate was also almost same) :

  17 - access("MMT"."ORGANIZATION_ID"=:ORGANIZATION_ID)
       filter(("MMT"."ORGANIZATION_ID"=:ORGANIZATION_ID AND TRUNC(INTERNAL_FUNCTION("MMT"."TRANSACTION_DATE"))>=:START_DATE AND
              TRUNC(INTERNAL_FUNCTION("MMT"."TRANSACTION_DATE"))<=:END_DATE))

It is only the Execution plan that shows the difference between the two plans, and the change in 10g that has triggered this performance issue. The question here was, Why RANGE SCAN has changed to FULL SCAN in 10g ? Finally, it was then noticed that the Index Structure of MTL_MATERIAL_TRANSACTIONS_N5 has changed, as a part of 11.5.10 upgrade. Database version is irrevelant here as the change is a part of 11.5.10 upgrade. It is a Composite Index. In 11.5.9 (and db 9i), the index was on (ORGANIZATION_ID, TRANSACTION_DATE), whereas, in 11.5.10, it has changed to (TRANSACTION_DATE, ORGANIZATION_ID). Now, if you look carefully at the WHERE clause of the Query, the condition specified is TRUNC(TRANSACTION_DATE). Therefore, in 9i, since ORGANIZATION_ID was the first column, though not selective, optimizer used this index and the column from an Index to obey the hint. In 10g, after the change, since ORGANIZATION_ID was the second column, the optimizer had to use this index and could not take the benefit of RANGE SCAN and therefore, opted for a FULL SCAN.

The Queries that were impacted due to this index structure change were all custom queries and all of these had been hinted to make use of this index.

Therefore, the solution was either: to create a new function based index on (ORGANIZATION_ID,TRUNC(TRANSACTION_DATE) or to remove the hint or make a proper change in the query to make use of the hinted Index with a RANGE SCAN. All these options required modification in the queries. The customer willingly agreed to to make the changes and since they were more comfortable with the 9i plan, it was decided to implement a change that will restore 9i execution plan. The change suggested was very simple.

CHANGE

 WHERE   /* TRUNC(MMT.TRANSACTION_DATE) BETWEEN :START_DATE AND :END_DATE */
  MMT.TRANSACTION_DATE BETWEEN to_date(:START_DATE) AND to_date(:END_DATE)+1-1/86400

Making this change in all similar queries made an impact as the performance of these were back to normal.



Another approach is to make a global change for all the problematic queries. This approach is mainly used if the behaviour of the queries change due to the change in the Optimizer behaviour. Again, for small number of queries, rewrite can be a solution rather than changing a parameter. In our previous query, the change was not due to the optimizer behaviour but it was due to structural change. There were other set of custom queries, and these were large number, that were impacted. This impact seemed to be surely because of the change in Optimizer behaviour in 10g and 9i.



Subquery Unnesting


Subquery Unnesting is one of the Query Transformation mechanism done by Oracle. An underscore parameter "_unnest_subquery" that defaults to TRUE, 9i onwards, control this mechanism. Unnesting a Subquery means : transforming a correlated subquery into an Inline View or Unnesting that merges a subquery into its Outer Query. Until 9i, the former was Heuristic and later was imperative, whereas in 10g, the former is Cost Based.



Let us walk through an example from a production system.

Query

SELECT DECODE(NVL(:reissue_flag,'N'),'Y',SUBSTR(lot_number,1,INSTR(lot_number,'~',1,2)-1),
 DECODE(SUBSTR(lot_number,1,INSTR(lot_number,'~')-1),
     NULL,lot_number,
     SUBSTR(lot_number,1,INSTR(lot_number,'~')-1))) lot_number,
        transaction_type_id                 trn_type,
        SUM(Wsh_Wv_Utils.convert_uom(msib.primary_uom_code,'Kg',
        Wsh_Wv_Utils.convert_uom(twt.primary_uom_code,msib.primary_uom_code,
        transaction_qty,twt.inventory_item_id),twt.inventory_item_id))        kgs,
     issue_id
FROM mtl_system_items_b     msib,
        ti_wh_transactions     twt
WHERE  msib.organization_id       = twt.organization_id
AND     msib.inventory_item_id     = twt.inventory_item_id
AND     transaction_type_id    IN (:receipt_id,:mr_id,:scrap_id)
AND     twt.vendor_id         = :vendor_id
AND     twt.sub_inventory_code     = :sub_inventory_code
AND     twt.organization_id       = :organization_id
AND  EXISTS 
 (SELECT 'x' 
 FROM  ti_wh_transactions twt1
        WHERE  organization_id = :organization_id 
        AND  twt1.transaction_id = twt.issue_id 
        AND  EXISTS 
         (SELECT 'y' 
         FROM  tpi_wh_item_equivalent twie
         WHERE  twie.organization_id = :organization_id
         AND  twie.equivalent_item_id = :inventory_item_id
         AND  twie.source_item_id = twt1.inventory_item_id))
GROUP BY lot_number, transaction_type_id,issue_id

The Original Query was a UNION of 2 queries. I have pasted the second part of the query (after UNION) as this was actually causing problem.

This I could validate after running the two in isolation. Therefore, the execution plan will contain the steps for both of these. The portion of the query in bold is an EXISTS query, and, based on the Cost Based Transformation, it seems, this was converted into an INLINE View, which is evident from the execution plan.

Query Plan in 10g

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                            |       |       |    81 (100)|          |
|*  1 |  FILTER                               |                            |       |       |            |          |
|   2 |   SORT GROUP BY                       |                            |     2 |   132 |    81   (9)| 00:00:01 |
|   3 |    VIEW                               |                            |     2 |   132 |    80   (8)| 00:00:01 |
|   4 |     SORT UNIQUE                       |                            |     2 |   179 |    80  (95)| 00:00:01 |
|   5 |      UNION-ALL                        |                            |       |       |            |          |
|*  6 |       TABLE ACCESS BY INDEX ROWID     | TI_WH_TRANSACTIONS         |     1 |    67 |     2   (0)| 00:00:01 |
|   7 |        NESTED LOOPS                   |                            |     1 |    94 |     5  (20)| 00:00:01 |
|   8 |         NESTED LOOPS                  |                            |     1 |    27 |     3  (34)| 00:00:01 |
|   9 |          SORT UNIQUE                  |                            |     1 |    14 |     1   (0)| 00:00:01 |
|* 10 |           INDEX RANGE SCAN            | TPI_WH_ITEM_EQUIVALENT_PK1 |     1 |    14 |     1   (0)| 00:00:01 |
|  11 |          TABLE ACCESS BY INDEX ROWID  | MTL_SYSTEM_ITEMS_B         |     1 |    13 |     1   (0)| 00:00:01 |
|* 12 |           INDEX UNIQUE SCAN           | MTL_SYSTEM_ITEMS_B_U1      |     1 |       |     1   (0)| 00:00:01 |
|* 13 |         INDEX RANGE SCAN              | TI_WH_TRANSACTIONS_TI3     |    28 |       |     1   (0)| 00:00:01 |
|  14 |       SORT GROUP BY                   |                            |     1 |    85 |    73   (6)| 00:00:01 |
|  15 |        NESTED LOOPS                   |                            |     1 |    85 |    71   (3)| 00:00:01 |
|  16 |         NESTED LOOPS                  |                            |     1 |    72 |    70   (3)| 00:00:01 |
|  17 |          VIEW                         | VW_SQ_1                    |     1 |     7 |     6  (17)| 00:00:01 |
|  18 |           SORT UNIQUE                 |                            |     1 |    30 |            |          |
|  19 |            TABLE ACCESS BY INDEX ROWID| TI_WH_TRANSACTIONS         |     5 |    80 |     4   (0)| 00:00:01 |
|  20 |             NESTED LOOPS              |                            |     1 |    30 |     6  (17)| 00:00:01 |
|  21 |              SORT UNIQUE              |                            |     1 |    14 |     1   (0)| 00:00:01 |
|* 22 |               INDEX RANGE SCAN        | TPI_WH_ITEM_EQUIVALENT_PK1 |     1 |    14 |     1   (0)| 00:00:01 |
|* 23 |              INDEX RANGE SCAN         | TI_WH_TRANSACTIONS_N1      |    49 |       |     1   (0)| 00:00:01 |
|* 24 |          TABLE ACCESS BY INDEX ROWID  | TI_WH_TRANSACTIONS         |     1 |    65 |    64   (0)| 00:00:01 |
|* 25 |           INDEX RANGE SCAN            | TI_WH_TRANSACTIONS_TI3     |   788 |       |     1   (0)| 00:00:01 |
|  26 |         TABLE ACCESS BY INDEX ROWID   | MTL_SYSTEM_ITEMS_B         |     1 |    13 |     1   (0)| 00:00:01 |
|* 27 |          INDEX UNIQUE SCAN            | MTL_SYSTEM_ITEMS_B_U1      |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TRUNC(NVL(SUM(DECODE("TRN_TYPE",:ISSUE_ID,NVL("KGS",0),:CI_ID,NVL("KGS",0))),0)-NVL(SUM(DECODE
              ("TRN_TYPE",:RECEIPT_ID,NVL("KGS",0),:MR_ID,NVL("KGS",0))),0)-NVL(SUM(DECODE("TRN_TYPE",:SCRAP_ID,NVL("KGS",
              0))),0),2)>0)
   6 - filter(("TRANSACTION_TYPE_ID"=DECODE(NVL(:REISSUE_FLAG,'N'),'Y',:CI_ID,:ISSUE_ID) AND
              "TWT"."SUB_INVENTORY_CODE"=:SUB_INVENTORY_CODE AND "LOT_NUMBER" IS NOT NULL))
  10 - access("TWIE"."EQUIVALENT_ITEM_ID"=:INVENTORY_ITEM_ID AND "TWIE"."ORGANIZATION_ID"=:ORGANIZATION_ID)
       filter("TWIE"."ORGANIZATION_ID"=:ORGANIZATION_ID)
  12 - access("TWIE"."SOURCE_ITEM_ID"="MSIB"."INVENTORY_ITEM_ID" AND
              "MSIB"."ORGANIZATION_ID"=:ORGANIZATION_ID)
  13 - access("TWT"."ORGANIZATION_ID"=:ORGANIZATION_ID AND "TWT"."VENDOR_ID"=:VENDOR_ID AND
              "TWT"."INVENTORY_ITEM_ID"="MSIB"."INVENTORY_ITEM_ID")
  22 - access("TWIE"."EQUIVALENT_ITEM_ID"=:INVENTORY_ITEM_ID AND "TWIE"."ORGANIZATION_ID"=:ORGANIZATION_ID)
       filter("TWIE"."ORGANIZATION_ID"=:ORGANIZATION_ID)
  23 - access("TWIE"."SOURCE_ITEM_ID"="TWT1"."INVENTORY_ITEM_ID" AND "ORGANIZATION_ID"=:ORGANIZATION_ID)
  24 - filter(("TWT"."ISSUE_ID" IS NOT NULL AND INTERNAL_FUNCTION("TRANSACTION_TYPE_ID") AND
              DECODE(NVL(:REISSUE_FLAG,'N'),'Y',SUBSTR("LOT_NUMBER",1,INSTR("LOT_NUMBER",'~',1,2)-1),DECODE(SUBSTR("LOT_NU
              MBER",1,INSTR("LOT_NUMBER",'~')-1),NULL,"LOT_NUMBER",SUBSTR("LOT_NUMBER",1,INSTR("LOT_NUMBER",'~')-1))) IS
              NOT NULL AND "TWT"."SUB_INVENTORY_CODE"=:SUB_INVENTORY_CODE AND "TRANSACTION_ID"="TWT"."ISSUE_ID"))
  25 - access("TWT"."ORGANIZATION_ID"=:ORGANIZATION_ID AND "TWT"."VENDOR_ID"=:VENDOR_ID)
  27 - access("MSIB"."INVENTORY_ITEM_ID"="TWT"."INVENTORY_ITEM_ID" AND
              "MSIB"."ORGANIZATION_ID"=:ORGANIZATION_ID)

From the above explain plan, ID 17 and 18 gives a clue that the EXISTS query was unnested into an Inline View. When an EXISTS query is convertible into Inline View, then a DISTINCT clause is added during transformation phase to eliminate duplicates. Therefore, we see SORT UNIQUE at ID 18. Below is the plan from 9i Instance which shows that the Outer Query was executed first and then the rows were FILTERED based on the subquery evaluation.

Query Plan in 9i

-----------------------------------------------------------------------------------------------------------------
ID | Operation                                          | PHV/Object Name               |  Rows | Bytes|   Cost |
-----------------------------------------------------------------------------------------------------------------
0  |SELECT STATEMENT                                    |----- 248182068 -----          |       |      |    338 |
1  |FILTER                                              |                               |       |      |        |
2  | SORT GROUP BY                                      |                               |     2 |  132 |    338 |
3  |  VIEW                                              |                               |     2 |  132 |    294 |
4  |   SORT UNIQUE                                      |                               |     2 |  158 |    294 |
5  |    UNION-ALL                                       |                               |       |      |        |
6  |     NESTED LOOPS                                   |                               |     1 |   80 |     86 |
7  |      TABLE ACCESS BY INDEX ROWID                   |TI_WH_TRANSACTIONS             |   134 |    8K|     66 |
8  |       INDEX RANGE SCAN                             |TI_WH_TRANSACTIONS_TI3         |   803 |      |      6 |
9  |      TABLE ACCESS BY INDEX ROWID                   |MTL_SYSTEM_ITEMS_B             |     1 |   13 |      1 |
10 |       INDEX UNIQUE SCAN                            |MTL_SYSTEM_ITEMS_B_U1          |     1 |      |        |
11 |        INDEX UNIQUE SCAN                           |TPI_WH_ITEM_EQUIVALENT_PK1     |     1 |   14 |      1 |
12 |     SORT GROUP BY                                  |                               |     1 |   78 |    160 |
13 |      FILTER                                        |                               |       |      |        |
14 |       NESTED LOOPS                                 |                               |     1 |   78 |     67 |
15 |        TABLE ACCESS BY INDEX ROWID                 |TI_WH_TRANSACTIONS             |     1 |   65 |     66 |
16 |         INDEX RANGE SCAN                           |TI_WH_TRANSACTIONS_TI3         |   803 |      |      6 |
17 |        TABLE ACCESS BY INDEX ROWID                 |MTL_SYSTEM_ITEMS_B             |     1 |   13 |      1 |
18 |         INDEX UNIQUE SCAN                          |MTL_SYSTEM_ITEMS_B_U1          |     1 |      |        |
19 |       NESTED LOOPS                                 |                               |     1 |   30 |      2 |
20 |        TABLE ACCESS BY INDEX ROWID                 |TI_WH_TRANSACTIONS             |     1 |   16 |      1 |
21 |         INDEX UNIQUE SCAN                          |TI_WH_TRAN_PK                  |     1 |      |      2 |
22 |        INDEX UNIQUE SCAN                           |TPI_WH_ITEM_EQUIVALENT_PK1     |     1 |   14 |        |
-----------------------------------------------------------------------------------------------------------------

As mentioned earlier, the parameter "_unnest_subquery" defaults to TRUE in 9i as well, but Unnesting of a Subquery was based on some rule and as I understand, Subquery Unnesting does not take place if there are Filter Predicates in the Outer Query and there are Indexes on the JOIN columns

(correlated) of the subquery, then subquery should not be unnested. From the Query, its Execution Plan and the Access / Filter predicates, it can be seen that there are filter predicates in the outer query and Indexes on the Join Column of the Subquery. Therefore, Oracle 9i used FILTER rather than Unnest.

At present, I am not that sure about this behaviour and am experimenting more on this. I am preparing some more test cases on this feature and the impact of this parameter. Possibly, this should feature in my next blog.



CHANGE


Though not recommended, "_unnest_subquery" parameter was set to FALSE. Before disabling this, the query (and couple of others) were modified and executed from the SQLPlus to compare for the performance and the modified query, with /*+ no_unnest */ hint gave a better performance than the original. The modified change was in the subquery, as shown below :

AND  EXISTS 
 (SELECT /*+ no_unnest */ 'x' 
 FROM  ti_wh_transactions twt1
        WHERE  organization_id = :organization_id 
        AND  twt1.transaction_id = twt.issue_id 
        AND  EXISTS 
         (SELECT 'y' 
         FROM  tpi_wh_item_equivalent twie
          WHERE  twie.organization_id = :organization_id
               AND  twie.equivalent_item_id = :inventory_item_id
             AND  twie.source_item_id = twt1.inventory_item_id))    
GROUP BY lot_number, transaction_type_id,issue_id

Since modifying all the queries was a tedious task, it was decided to set this globally and check for the performance. After disabling Subquery Unnesting, no performance impact was observed in other queries and improvements were visible in most of the impacted queries. With this change, the query completion time was restored from 40 minutes to less than a minute.

10g Plan after disabling Subquery Unnesting

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                            |       |       |    97 (100)|          |
|*  1 |  FILTER                             |                            |       |       |            |          |
|   2 |   SORT GROUP BY                     |                            |     2 |   132 |    97   (6)| 00:00:02 |
|   3 |    VIEW                             |                            |     2 |   132 |    96   (5)| 00:00:02 |
|   4 |     SORT UNIQUE                     |                            |     2 |   172 |    96  (96)| 00:00:02 |
|   5 |      UNION-ALL                      |                            |       |       |            |          |
|*  6 |       TABLE ACCESS BY INDEX ROWID   | TI_WH_TRANSACTIONS         |     1 |    67 |     2   (0)| 00:00:01 |
|   7 |        NESTED LOOPS                 |                            |     1 |    94 |     5  (20)| 00:00:01 |
|   8 |         NESTED LOOPS                |                            |     1 |    27 |     3  (34)| 00:00:01 |
|   9 |          SORT UNIQUE                |                            |     1 |    14 |     1   (0)| 00:00:01 |
|* 10 |           INDEX RANGE SCAN          | TPI_WH_ITEM_EQUIVALENT_PK1 |     1 |    14 |     1   (0)| 00:00:01 |
|  11 |          TABLE ACCESS BY INDEX ROWID| MTL_SYSTEM_ITEMS_B         |     1 |    13 |     1   (0)| 00:00:01 |
|* 12 |           INDEX UNIQUE SCAN         | MTL_SYSTEM_ITEMS_B_U1      |     1 |       |     1   (0)| 00:00:01 |
|* 13 |         INDEX RANGE SCAN            | TI_WH_TRANSACTIONS_TI3     |    28 |       |     1   (0)| 00:00:01 |
|  14 |       SORT GROUP BY                 |                            |     1 |    78 |    89   (3)| 00:00:02 |
|* 15 |        FILTER                       |                            |       |       |            |          |
|  16 |         NESTED LOOPS                |                            |    20 |  1560 |    67   (0)| 00:00:01 |
|* 17 |          TABLE ACCESS BY INDEX ROWID| TI_WH_TRANSACTIONS         |    20 |  1300 |    64   (0)| 00:00:01 |
|* 18 |           INDEX RANGE SCAN          | TI_WH_TRANSACTIONS_TI3     |   788 |       |     1   (0)| 00:00:01 |
|  19 |          TABLE ACCESS BY INDEX ROWID| MTL_SYSTEM_ITEMS_B         |     1 |    13 |     1   (0)| 00:00:01 |
|* 20 |           INDEX UNIQUE SCAN         | MTL_SYSTEM_ITEMS_B_U1      |     1 |       |     1   (0)| 00:00:01 |
|  21 |         NESTED LOOPS                |                            |     1 |    30 |     2   (0)| 00:00:01 |
|* 22 |          TABLE ACCESS BY INDEX ROWID| TI_WH_TRANSACTIONS         |     1 |    16 |     1   (0)| 00:00:01 |
|* 23 |           INDEX UNIQUE SCAN         | TI_WH_TRAN_PK              |     1 |       |     1   (0)| 00:00:01 |
|* 24 |          INDEX UNIQUE SCAN          | TPI_WH_ITEM_EQUIVALENT_U1  |     1 |    14 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TRUNC(NVL(SUM(DECODE("TRN_TYPE",:ISSUE_ID,NVL("KGS",0),:CI_ID,NVL("KGS",0))),0)-NVL(SUM(DECO
              DE("TRN_TYPE",:RECEIPT_ID,NVL("KGS",0),:MR_ID,NVL("KGS",0))),0)-NVL(SUM(DECODE("TRN_TYPE",:SCRAP_ID,NVL("K
              GS",0))),0),2)>0)
   6 - filter(("TRANSACTION_TYPE_ID"=DECODE(NVL(:REISSUE_FLAG,'N'),'Y',:CI_ID,:ISSUE_ID) AND
              "TWT"."SUB_INVENTORY_CODE"=:SUB_INVENTORY_CODE AND "LOT_NUMBER" IS NOT NULL))
  10 - access("TWIE"."EQUIVALENT_ITEM_ID"=:INVENTORY_ITEM_ID AND
              "TWIE"."ORGANIZATION_ID"=:ORGANIZATION_ID)
       filter("TWIE"."ORGANIZATION_ID"=:ORGANIZATION_ID)
  12 - access("TWIE"."SOURCE_ITEM_ID"="MSIB"."INVENTORY_ITEM_ID" AND
              "MSIB"."ORGANIZATION_ID"=:ORGANIZATION_ID)
  13 - access("TWT"."ORGANIZATION_ID"=:ORGANIZATION_ID AND "TWT"."VENDOR_ID"=:VENDOR_ID AND
              "TWT"."INVENTORY_ITEM_ID"="MSIB"."INVENTORY_ITEM_ID")
  15 - filter( IS NOT NULL)
  17 - filter((INTERNAL_FUNCTION("TRANSACTION_TYPE_ID") AND
              DECODE(NVL(:REISSUE_FLAG,'N'),'Y',SUBSTR("LOT_NUMBER",1,INSTR("LOT_NUMBER",'~',1,2)-1),DECODE(SUBSTR("LOT_
              NUMBER",1,INSTR("LOT_NUMBER",'~')-1),NULL,"LOT_NUMBER",SUBSTR("LOT_NUMBER",1,INSTR("LOT_NUMBER",'~')-1)))
              IS NOT NULL AND "TWT"."SUB_INVENTORY_CODE"=:SUB_INVENTORY_CODE))
  18 - access("TWT"."ORGANIZATION_ID"=:ORGANIZATION_ID AND "TWT"."VENDOR_ID"=:VENDOR_ID)
  20 - access("MSIB"."INVENTORY_ITEM_ID"="TWT"."INVENTORY_ITEM_ID" AND
              "MSIB"."ORGANIZATION_ID"=:ORGANIZATION_ID)
  22 - filter("ORGANIZATION_ID"=:ORGANIZATION_ID)
  23 - access("TWT1"."TRANSACTION_ID"=:B1)
  24 - access("TWIE"."SOURCE_ITEM_ID"="TWT1"."INVENTORY_ITEM_ID" AND
              "TWIE"."EQUIVALENT_ITEM_ID"=:INVENTORY_ITEM_ID AND "TWIE"."ORGANIZATION_ID"=:ORGANIZATION_ID)

As mentioned earlier, in 10g Subquery Unnesting is Cost Based and the plan shows that Unnested Plan was costlier than the Original and hence, optimizer opted for the cheaper plan.




Any change in the Underscore Parameter needs thorough and careful testing of the application. Behaviour of each and every parameter can change release to release and an application dependant on these settings need frequent tweaking as they upgrade from version to version.