India Localization ! R12 Upgrade Performance Issues…..

First, let me wish my readers a very Happy & Prosperous New Year.

While the heading of this blog relates to India Localization and Performance Issues post R12 Upgrade, the solution that I talk about actually translates to Effective Query Writing. One of my customer is in a final phase of Apps R12 Upgrade and is facing some slowness in a Test Environment. While I am working on all of these, I thought of writing on One Issue at a time, as and when, these are resolved. The database is 11gR2. Slowness in Matched Receipt Application was reported and an 10046 Trace file was sent to me for analysis. From the trace file, I could see 2 Queries that were consuming most of the CPU time and resolving these would bring down the execution time. The Queries are mentioned below :

SELECT wda.delivery_id, count(wda.delivery_detail_id) delivery_detail_count
FROM	wsh_new_deliveries wnd, 
	wsh_delivery_assignments wda, 
	wsh_delivery_details wdd, 
	jai_om_oe_so_lines jsl 
WHERE 	wnd.status_code NOT IN ( 'CO', 'CL', 'IT') 
AND 	wnd.organization_id = :1 
AND 	wda.delivery_id = wnd.delivery_id 
AND  	wdd.delivery_detail_id = wda.delivery_detail_id 
AND 	wdd.source_code = 'OE'  
AND 	wdd.organization_id = :2 
AND 	wdd.source_header_id = jsl.header_id 
AND  	wdd.source_line_id = jsl.line_id 
AND 	((:3 = 'UN MATCH' 
AND 	EXISTS (select ref_line_id 
		from 	JAI_CMN_MATCH_RECEIPTS 
		where 	ref_line_id = wda.delivery_detail_id 
		AND 	order_invoice ='O' 
		and 	ship_status IS NULL) ) OR
  	(:4 = 'MATCH' 
  	AND NOT EXISTS (select ref_line_id 
  			from 	JAI_CMN_MATCH_RECEIPTS 
  			where 	ref_line_id = wda.delivery_detail_id 
  			AND  	order_invoice ='O' 
  			and 	ship_status IS NULL) ) ) 
  AND 	wdd.customer_id = nvl(:5, wdd.customer_id ) 
  AND 	wdd.source_header_id = nvl(:6,wdd.source_header_id ) 
  GROUP BY wda.delivery_id ;

SELECT ott.name, ott.transaction_type_id
FROM 	oe_transaction_types_tl ott, 
	oe_order_headers_all oh, 
	oe_order_lines_all ol,
  	jai_om_oe_so_lines jsl 
WHERE 	(NAME LIKE :b1) 
AND 	( ott.transaction_type_id = oh.order_type_id 
AND 	oh.header_id = ol.header_id 
AND 	ol.header_id = jsl.header_id 
AND 	ol.line_id = jsl.line_id 
AND 	ol.ship_from_org_id = :2 
AND	ol.sold_to_org_id = nvl(:3, ol.sold_to_org_id ) ) 
GROUP BY ott.name, ott.transaction_type_id

Since the solution for both the Queries are same, to make this smaller, a detailed explanation on Query 1 wll dominate the blog. The Execution Plan of Query 1 is pasted below and the problematic part is marked as bold (and arrow marked, in case, some browser does not display characters in BOLD). I have also mentioned the run time values of the Bind Variables from v$sql_bind_capture. These values usually help to run the query from SQL*Plus and validate the working of a query.

Bind Capture Value
------------------
SQL_ID        CHILD_ADDRESS    NAME         POSITION DATATYPE_STRING      VALUE_STRING
------------- ---------------- ---------- ---------- -------------------- --------------------
byzqm6cf3j226 07000009D7614120 :1                  1 NUMBER               52
byzqm6cf3j226 07000009D7614120 :2                  2 NUMBER               52
byzqm6cf3j226 07000009D7614120 :3                  3 CHAR(32)             MATCH
byzqm6cf3j226 07000009D7614120 :4                  4 CHAR(32)             MATCH
byzqm6cf3j226 07000009D7614120 :5                  5 CHAR(32)             NULL
byzqm6cf3j226 07000009D7614120 :6                  6 NUMBER               NULL

Plan hash value: 2965431451

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                             |       |       | 64721 (100)|          |
|   1 |  SORT GROUP BY                     |                             |     1 |    67 |            |          |
|   2 |   CONCATENATION                    |                             |       |       |            |          |
|*  3 |    FILTER                          |                             |       |       |            |          |
|*  4 |     FILTER                         |                             |       |       |            |          |
|   5 |      NESTED LOOPS                  |                             |     1 |    67 | 64704  (61)| 00:00:01 |
|   6 |       NESTED LOOPS                 |                             |     1 |    55 | 64704  (61)| 00:00:01 |
|   7 |        NESTED LOOPS                |                             |     7 |   294 | 64691  (61)| 00:00:01 |
|*  8 |         TABLE ACCESS BY INDEX ROWID| WSH_DELIVERY_DETAILS        |     7 |   217 | 64677  (61)| 00:00:01 |<--------
|*  9 |          INDEX RANGE SCAN          | WSH_DELIVERY_DETAILS_TI6    |   142K|       |  4194  (91)| 00:00:01 |<--------
|* 10 |         TABLE ACCESS BY INDEX ROWID| WSH_DELIVERY_ASSIGNMENTS    |     1 |    11 |     2  (50)| 00:00:01 |
|* 11 |          INDEX RANGE SCAN          | WSH_DELIVERY_ASSIGNMENTS_N3 |     1 |       |     0   (0)|          |
|* 12 |        INDEX RANGE SCAN            | WSH_NEW_DELIVERIES_TI1      |     1 |    13 |     2  (50)| 00:00:01 |
|* 13 |       INDEX RANGE SCAN             | JAI_OM_OE_SO_LINES_N1       |     1 |    12 |     0   (0)|          |
|* 14 |     TABLE ACCESS BY INDEX ROWID    | JAI_CMN_MATCH_RECEIPTS      |     1 |    15 |     8  (50)| 00:00:01 |
|* 15 |      INDEX RANGE SCAN              | JAI_CMN_MATCH_RECEIPTS_N1   |     1 |       |     6  (50)| 00:00:01 |
|* 16 |     TABLE ACCESS BY INDEX ROWID    | JAI_CMN_MATCH_RECEIPTS      |     1 |    15 |     8  (50)| 00:00:01 |
|* 17 |      INDEX RANGE SCAN              | JAI_CMN_MATCH_RECEIPTS_N1   |     1 |       |     6  (50)| 00:00:01 |
|* 18 |    FILTER                          |                             |       |       |            |          |
|* 19 |     FILTER                         |                             |       |       |            |          |
|  20 |      NESTED LOOPS                  |                             |     1 |    67 |    16  (50)| 00:00:01 |
|  21 |       NESTED LOOPS                 |                             |     1 |    54 |    14  (50)| 00:00:01 |
|  22 |        NESTED LOOPS                |                             |     1 |    43 |    12  (50)| 00:00:01 |
|* 23 |         TABLE ACCESS BY INDEX ROWID| WSH_DELIVERY_DETAILS        |     1 |    31 |    12  (50)| 00:00:01 |
|* 24 |          INDEX RANGE SCAN          | WSH_DELIVERY_DETAILS_N2     |     9 |       |     6  (50)| 00:00:01 |
|* 25 |         INDEX RANGE SCAN           | JAI_OM_OE_SO_LINES_N1       |     1 |    12 |     0   (0)|          |
|* 26 |        TABLE ACCESS BY INDEX ROWID | WSH_DELIVERY_ASSIGNMENTS    |     1 |    11 |     2  (50)| 00:00:01 |
|* 27 |         INDEX RANGE SCAN           | WSH_DELIVERY_ASSIGNMENTS_N3 |     1 |       |     0   (0)|          |
|* 28 |       INDEX RANGE SCAN             | WSH_NEW_DELIVERIES_TI1      |     1 |    13 |     2  (50)| 00:00:01 |
|* 29 |     TABLE ACCESS BY INDEX ROWID    | JAI_CMN_MATCH_RECEIPTS      |     1 |    15 |     8  (50)| 00:00:01 |
|* 30 |      INDEX RANGE SCAN              | JAI_CMN_MATCH_RECEIPTS_N1   |     1 |       |     6  (50)| 00:00:01 |
|* 31 |     TABLE ACCESS BY INDEX ROWID    | JAI_CMN_MATCH_RECEIPTS      |     1 |    15 |     8  (50)| 00:00:01 |
|* 32 |      INDEX RANGE SCAN              | JAI_CMN_MATCH_RECEIPTS_N1   |     1 |       |     6  (50)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------

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

   3 - filter(((:B4='UN MATCH' AND  IS NOT NULL) OR (:B3='MATCH' AND  IS NULL)))
   4 - filter(:B1 IS NULL)
   8 - filter(("WDD"."SOURCE_CODE"='OE' AND "WDD"."SOURCE_HEADER_ID" IS NOT NULL AND
              "WDD"."CUSTOMER_ID"=NVL(:B2,"WDD"."CUSTOMER_ID")))
   9 - access("WDD"."ORGANIZATION_ID"=:B5)
  10 - filter("WDA"."DELIVERY_ID" IS NOT NULL)
  11 - access("WDD"."DELIVERY_DETAIL_ID"="WDA"."DELIVERY_DETAIL_ID")
  12 - access("WND"."ORGANIZATION_ID"=:B6 AND "WDA"."DELIVERY_ID"="WND"."DELIVERY_ID")
       filter(("WND"."STATUS_CODE"'IT' AND "WND"."STATUS_CODE"'CL' AND "WND"."STATUS_CODE"'CO'))
  13 - access("WDD"."SOURCE_LINE_ID"="JSL"."LINE_ID" AND "WDD"."SOURCE_HEADER_ID"="JSL"."HEADER_ID")
  14 - filter(("SHIP_STATUS" IS NULL AND "ORDER_INVOICE"='O'))
  15 - access("REF_LINE_ID"=:B1)
  16 - filter(("SHIP_STATUS" IS NULL AND "ORDER_INVOICE"='O'))
  17 - access("REF_LINE_ID"=:B1)
  18 - filter(((:B4='UN MATCH' AND  IS NOT NULL) OR (:B3='MATCH' AND  IS NULL)))
  19 - filter(:B1 IS NOT NULL)
  23 - filter(("WDD"."ORGANIZATION_ID"=:B5 AND "WDD"."SOURCE_CODE"='OE' AND
              "WDD"."CUSTOMER_ID"=NVL(:B2,"WDD"."CUSTOMER_ID")))
  24 - access("WDD"."SOURCE_HEADER_ID"=:B1)
  25 - access("WDD"."SOURCE_LINE_ID"="JSL"."LINE_ID" AND "WDD"."SOURCE_HEADER_ID"="JSL"."HEADER_ID")
  26 - filter("WDA"."DELIVERY_ID" IS NOT NULL)
  27 - access("WDD"."DELIVERY_DETAIL_ID"="WDA"."DELIVERY_DETAIL_ID")
  28 - access("WND"."ORGANIZATION_ID"=:B6 AND "WDA"."DELIVERY_ID"="WND"."DELIVERY_ID")
       filter(("WND"."STATUS_CODE"'IT' AND "WND"."STATUS_CODE"'CL' AND "WND"."STATUS_CODE"'CO'))
  29 - filter(("SHIP_STATUS" IS NULL AND "ORDER_INVOICE"='O'))
  30 - access("REF_LINE_ID"=:B1)
  31 - filter(("SHIP_STATUS" IS NULL AND "ORDER_INVOICE"='O'))
  32 - access("REF_LINE_ID"=:B1)

WSH_DELIVERY_DETAIL is the diriving Table, which is scanned via an Index on ORGANIZATION_ID (Index WSH_DELIVERY_DETAILS_T16). See Step 9 from the Predicate Information Section.

Analysis

The Logical Reads of this query is around 0.23 Million per Execution. For Analysis, it was important to validate whether the Driving Table and Access Path chosen by the optimizer is Optimal. Therefore, I first targetted Step 8 & 9 of the Execution plan, which is the driving step. Step 9 says an Index Scan and the estimated rows from this step is around 142k. This means, after scanning and filtering the data for an Organization_ID, the optimizer estimates around 142k rows will be returned and passed to the next step, which is to visit the table to filter additional non-indexed predicates. From Step 8, it can be seen that these non-indexed predicates are SOURCE_CODE, CUSTOMER_ID and SOURCE_HEADER_ID. Once the non-indexed predicates are applied, the optimizer estimates 7 rows to be returned by the final step 8. Based on this, the next action, which is to come out with a Optimal Access Path. In this case, it is a Nested Loop Join. Only 7 rows out of 142K seemed to be a problem and therefore, the next step was to validate the optimizer calculation. Following table shows the Statistics that Optimizer takes into consideration and next we shall manually run the formula to check the calculation.

## Number of Rows in the Table

SQL> @table_stats
Enter value for 1: WSH_DELIVERY_DETAILS
old   2: where table_name=upper('&1')
new   2: where table_name=upper('WSH_DELIVERY_DETAILS')

OWNER                          PAR   NUM_ROWS     BLOCKS LAST_ANAL GLO DEGREE
------------------------------ --- ---------- ---------- --------- --- ----------
WSH                            NO    11148400     673435 14-DEC-11 YES          1

## Column Statistics (Only relevant columns specified in the Query are displayed)

SQL> @column_stats
Enter value for 1: WSH
old   3: where owner='&1'
new   3: where owner='WSH'
Enter value for 2: WSH_DELIVERY_DETAILS
old   4: and   table_name='&2'
new   4: and   table_name='WSH_DELIVERY_DETAILS'

COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS    DENSITY HISTOGRAM
------------------------------ ------------ ---------- ---------- ---------------
CUSTOMER_ID                            9250      31395 .000108108 NONE
ORGANIZATION_ID                          78          0 .012820513 NONE
SOURCE_CODE                               2          0         .5 NONE
SOURCE_HEADER_ID                    1171510    1090944 8.5360E-07 NONE

The table has around 11 Million Rows. The Optimizer Calculation for Number of Rows expected from Index Scan is = 11148400*1/78 (Num_Distinct of Organization_Id) = 142928.905 = 142k. This matches the calculation. Step 8 is a filter from non-indexed columns used in the WHERE clause. Therefore, it is 142928*1/9250*1/2 = 7.72 = 7 (9250 for Customer_id and 2 for Source_code). Optimizer, in this case, has not considered source_header_id in the computation, as this column is also used in the JOIN Predicate, else the expected rows would have gone down further. This miscalculation of 7 Rows is enough to generate a wrong execution plan, particularly if the values against the bind variables :5 and :6 is NULL. Again, let us revisit the WHERE predicate of the query that FILTERS a condition on Customer_id & Source_code.


AND 	wdd.source_code = 'OE'  <-- Non Indexed Column 
AND 	wdd.organization_id = :2 <--- Indexed Column (Bind Value passed as 52)
  AND 	wdd.customer_id = nvl(:5, wdd.customer_id ) <--- Non Indexed Column (Bind Value passed as NULL)

As mentioned in my previous paragraph, the NULL values means a a condition wdd.customer_id = wdd.customer_id, but Optimizer takes this predicate into calculation and therefore, misbehaves. While better indexing strategy or more detailed statistics would help Optimizer calculate nearly accurate cardinality, this can have an impact on other queries as well. Therefore, a better strategy would be to write the query, as per best practices, which would generate an Optimal Execution Plan.

The change would be, since the values for :5 & :6, which is for customer_id and source_header_id respectively, is passed as NULL, these predicates are unwanted in the query. When the Query was executed, with these 2 columns removed, the execution time and the I/O’s reduced. However, we cannot always assume that these will be NULL and therefore, the query needs to be written based on the runtime choice. In this case, since there are 2 columns, which could be either NULL or NOT NULL, there would be 4 Combinations by way of which this query can be written. These Combination will be :>/p>

   1. Customer_ID is NULL and Source_header_id is NULL
   2. Customer_id is NULL and Source_header_id is NOT NULL
   3. Customer_Id is NOT NULL and Source_header_id is NULL
   4. Customer_Id is NOT NULL and Source_header_id is NOT NULL

Since the choice is between 2 values (NULL & NOT NULL), the number of permutation and combination would depend on the number of columns. In this case, 2^2 = 4. If there are 3 columns, it will be 2^3=8. Following table shows the change in the WHERE clause to accommodate the 4 Combinations mentioned above.

## MODIFICATION 1 both customer_id and source_header_id is null

SELECT /*+ MODIFICATION 1 REMOVED BOTH NULL COLUMNS */ wda.delivery_id, count(wda.delivery_detail_id) delivery_detail_count
		FROM	wsh_new_deliveries wnd, 
		wsh_delivery_assignments wda, 
		wsh_delivery_details wdd, 
		jai_om_oe_so_lines jsl 
	WHERE 	wnd.status_code NOT IN ( 'CO', 'CL', 'IT') 
	AND 	wnd.organization_id = :1 
	AND 	wda.delivery_id = wnd.delivery_id 
	AND  	wdd.delivery_detail_id = wda.delivery_detail_id 
	AND 	wdd.source_code = 'OE'  
	AND 	wdd.organization_id = :2 
	AND 	wdd.source_header_id = jsl.header_id 
	AND  	wdd.source_line_id = jsl.line_id 
	AND 	((:3 = 'UN MATCH' 
	AND 	EXISTS (select ref_line_id 
			from 	JAI_CMN_MATCH_RECEIPTS 
			where 	ref_line_id = wda.delivery_detail_id 
			AND 	order_invoice ='O' 
			and 	ship_status IS NULL) ) OR
	  	(:4 = 'MATCH' 
	  	AND NOT EXISTS (select ref_line_id 
	  			from 	JAI_CMN_MATCH_RECEIPTS 
	  			where 	ref_line_id = wda.delivery_detail_id 
	  			AND  	order_invoice ='O' 
	  			and 	ship_status IS NULL) ) ) 
--	  AND 	wdd.customer_id = nvl(:5, wdd.customer_id ) 
--	  AND 	wdd.source_header_id = nvl(:6,wdd.source_header_id ) 
	  GROUP BY wda.delivery_id;

## MODIFICATION 2 customer_id is null and source_header_id is not null

SELECT /*+ MODIFICATION 2 CUSTOMER ID IS NULL AND SOURCE ID IS NOT NULL */ wda.delivery_id, count(wda.delivery_detail_id) delivery_detail_count
		FROM	wsh_new_deliveries wnd, 
		wsh_delivery_assignments wda, 
		wsh_delivery_details wdd, 
		jai_om_oe_so_lines jsl 
	WHERE 	wnd.status_code NOT IN ( 'CO', 'CL', 'IT') 
	AND 	wnd.organization_id = :1 
	AND 	wda.delivery_id = wnd.delivery_id 
	AND  	wdd.delivery_detail_id = wda.delivery_detail_id 
	AND 	wdd.source_code = 'OE'  
	AND 	wdd.organization_id = :2 
	AND 	wdd.source_header_id = jsl.header_id 
	AND  	wdd.source_line_id = jsl.line_id 
	AND 	((:3 = 'UN MATCH' 
	AND 	EXISTS (select ref_line_id 
			from 	JAI_CMN_MATCH_RECEIPTS 
			where 	ref_line_id = wda.delivery_detail_id 
			AND 	order_invoice ='O' 
			and 	ship_status IS NULL) ) OR
	  	(:4 = 'MATCH' 
	  	AND NOT EXISTS (select ref_line_id 
	  			from 	JAI_CMN_MATCH_RECEIPTS 
	  			where 	ref_line_id = wda.delivery_detail_id 
	  			AND  	order_invoice ='O' 
	  			and 	ship_status IS NULL) ) ) 
--	  AND 	wdd.customer_id = nvl(:5, wdd.customer_id ) 
	  AND 	wdd.source_header_id = :5 
	  GROUP BY wda.delivery_id;

## MODIFICATION 4 customer_id is not null and source_header_id is not null

SELECT /*+ MODIFICATION 3 BOTH ARE NOT NULL COLUMNS */ wda.delivery_id, count(wda.delivery_detail_id) delivery_detail_count
		FROM	wsh_new_deliveries wnd, 
		wsh_delivery_assignments wda, 
		wsh_delivery_details wdd, 
		jai_om_oe_so_lines jsl 
	WHERE 	wnd.status_code NOT IN ( 'CO', 'CL', 'IT') 
	AND 	wnd.organization_id = :1 
	AND 	wda.delivery_id = wnd.delivery_id 
	AND  	wdd.delivery_detail_id = wda.delivery_detail_id 
	AND 	wdd.source_code = 'OE'  
	AND 	wdd.organization_id = :2 
	AND 	wdd.source_header_id = jsl.header_id 
	AND  	wdd.source_line_id = jsl.line_id 
	AND 	((:3 = 'UN MATCH' 
	AND 	EXISTS (select ref_line_id 
			from 	JAI_CMN_MATCH_RECEIPTS 
			where 	ref_line_id = wda.delivery_detail_id 
			AND 	order_invoice ='O' 
			and 	ship_status IS NULL) ) OR
	  	(:4 = 'MATCH' 
	  	AND NOT EXISTS (select ref_line_id 
	  			from 	JAI_CMN_MATCH_RECEIPTS 
	  			where 	ref_line_id = wda.delivery_detail_id 
	  			AND  	order_invoice ='O' 
	  			and 	ship_status IS NULL) ) ) 
	  AND 	wdd.customer_id = :5 
	  AND 	wdd.source_header_id = :6 
	  GROUP BY wda.delivery_id;

## MODIFICATION 4 customer_id is not null and source_header_id is null

SELECT /*+ MODIFICATION 4 CUSTOMER_ID is NOT NULL */ wda.delivery_id, count(wda.delivery_detail_id) delivery_detail_count
		FROM	wsh_new_deliveries wnd, 
		wsh_delivery_assignments wda, 
		wsh_delivery_details wdd, 
		jai_om_oe_so_lines jsl 
	WHERE 	wnd.status_code NOT IN ( 'CO', 'CL', 'IT') 
	AND 	wnd.organization_id = :1 
	AND 	wda.delivery_id = wnd.delivery_id 
	AND  	wdd.delivery_detail_id = wda.delivery_detail_id 
	AND 	wdd.source_code = 'OE'  
	AND 	wdd.organization_id = :2 
	AND 	wdd.source_header_id = jsl.header_id 
	AND  	wdd.source_line_id = jsl.line_id 
	AND 	((:3 = 'UN MATCH' 
	AND 	EXISTS (select ref_line_id 
			from 	JAI_CMN_MATCH_RECEIPTS 
			where 	ref_line_id = wda.delivery_detail_id 
			AND 	order_invoice ='O' 
			and 	ship_status IS NULL) ) OR
	  	(:4 = 'MATCH' 
	  	AND NOT EXISTS (select ref_line_id 
	  			from 	JAI_CMN_MATCH_RECEIPTS 
	  			where 	ref_line_id = wda.delivery_detail_id 
	  			AND  	order_invoice ='O' 
	  			and 	ship_status IS NULL) ) ) 
	  AND 	wdd.customer_id = :5
--	  AND 	wdd.source_header_id = nvl(:6,wdd.source_header_id ) 
	  GROUP BY wda.delivery_id;

The change require an IF..THEN..ELSE loop to check for the values of the Bind Variables at Run Time and execute the query that is relevant. With this change, the Optimizer calculated correct cardinality based on the predicates actually used by the User and came up with a perfect execution plan. Below is the plan, when the query was run at the customer site for both values as null.

Plan hash value: 466680131

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                             |       |       |       |   114K(100)|          |
|   1 |  SORT GROUP BY                   |                             |     1 |    62 |       |   114K (59)| 00:00:01 |
|*  2 |   FILTER                         |                             |       |       |       |            |          |
|   3 |    NESTED LOOPS                  |                             |   511 | 31682 |       |   114K (59)| 00:00:01 |
|*  4 |     HASH JOIN                    |                             |   511 | 25550 |  2400K|   114K (59)| 00:00:01 |
|*  5 |      TABLE ACCESS BY INDEX ROWID | WSH_DELIVERY_DETAILS        | 64471 |  1636K|       | 62327  (59)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN           | WSH_DELIVERY_DETAILS_TI6    |   142K|       |       |  4194  (91)| 00:00:01 |
|   7 |      NESTED LOOPS                |                             |       |       |       |            |          |
|   8 |       NESTED LOOPS               |                             | 72604 |  1701K|       | 48471  (55)| 00:00:01 |
|*  9 |        INDEX RANGE SCAN          | WSH_NEW_DELIVERIES_TI1      | 16146 |   204K|       |  2197  (91)| 00:00:01 |
|* 10 |        INDEX RANGE SCAN          | WSH_DELIVERY_ASSIGNMENTS_N1 |     5 |       |       |     0   (0)|          |
|  11 |       TABLE ACCESS BY INDEX ROWID| WSH_DELIVERY_ASSIGNMENTS    |     4 |    44 |       |     4  (50)| 00:00:01 |
|* 12 |     INDEX RANGE SCAN             | JAI_OM_OE_SO_LINES_N1       |     1 |    12 |       |     0   (0)|          |
|* 13 |    TABLE ACCESS BY INDEX ROWID   | JAI_CMN_MATCH_RECEIPTS      |     1 |    15 |       |     8  (50)| 00:00:01 |
|* 14 |     INDEX RANGE SCAN             | JAI_CMN_MATCH_RECEIPTS_N1   |     1 |       |       |     6  (50)| 00:00:01 |
|* 15 |    TABLE ACCESS BY INDEX ROWID   | JAI_CMN_MATCH_RECEIPTS      |     1 |    15 |       |     8  (50)| 00:00:01 |
|* 16 |     INDEX RANGE SCAN             | JAI_CMN_MATCH_RECEIPTS_N1   |     1 |       |       |     6  (50)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------

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

   2 - filter(((:B3='UN MATCH' AND  IS NOT NULL) OR (:B4='MATCH' AND  IS NULL)))
   4 - access("WDD"."DELIVERY_DETAIL_ID"="WDA"."DELIVERY_DETAIL_ID")
   5 - filter(("WDD"."SOURCE_CODE"='OE' AND "WDD"."SOURCE_HEADER_ID" IS NOT NULL))
   6 - access("WDD"."ORGANIZATION_ID"=:B2)
   9 - access("WND"."ORGANIZATION_ID"=:B1)
       filter(("WND"."STATUS_CODE"'IT' AND "WND"."STATUS_CODE"'CL' AND "WND"."STATUS_CODE"'CO'))
  10 - access("WDA"."DELIVERY_ID"="WND"."DELIVERY_ID")
       filter("WDA"."DELIVERY_ID" IS NOT NULL)
  12 - access("WDD"."SOURCE_LINE_ID"="JSL"."LINE_ID" AND "WDD"."SOURCE_HEADER_ID"="JSL"."HEADER_ID")
  13 - filter(("SHIP_STATUS" IS NULL AND "ORDER_INVOICE"='O'))
  14 - access("REF_LINE_ID"=:B1)
  15 - filter(("SHIP_STATUS" IS NULL AND "ORDER_INVOICE"='O'))
  16 - access("REF_LINE_ID"=:B1)

With the same indexes, but slight modification in the Query, the optimizer has come up with a plan that does a HASH Join (as against Nested Loop Join) based on the number of rows expected from WSH_DELIVERY_DETAILS Table.

This modification was applied via an application patch, which resolved the Match Receipt Application Performance Issues. The benefit was due to the reduced number of Logical Reads.

Advertisements

About Vivek Sharma
I am an Oracle Professional from Mumbai (India). I blog on the issues that I feel is Interesting for my readers. Some of these are my real life examples, which I hope, you would find interesting. Comments are always a welcome. The Technical Observations & Views here are my own and not necessarily those of Oracle or its affiliates. These are purely based on my understandings, learnings and resolutions of various customer issues.

One Response to India Localization ! R12 Upgrade Performance Issues…..

  1. Anand says:

    Great Analysis. Thanks for sharing 🙂

    Regards,
    Anand

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s