After quite a while I was beenrequested to solve an RPM performance issue.
While executing conflict check procedure in rpm_future_retail package, duration of one SQL inside the procedure push_back_error_rows takes 36 times longer than average.
Here is the SQL text responsible for performance degradation, that I want to expose to be sure it’s not a simple problem:
SELECT
conflict_check_error_rec(
price_event_id,
future_retail_id,
error_type,
error_string,
cs_promo_fr_id
)
FROM
(
SELECT
price_event_id,
future_retail_id,
error_type,
error_string,
cs_promo_fr_id,
item,
location,
action_date
FROM
(
SELECT /*+ CARDINALITY(ccet,10) LEADING(CCET) USE_HASH(CCET,rfrg1) */
rfrg2.price_event_id,
rfrg2.future_retail_id,
ccet.error_type,
ccet.error_string,
NULL cs_promo_fr_id,
RANK() OVER(PARTITION BY
rfrg2.price_event_id,
rfrg2.item,
rfrg2.location
ORDER BY
rfrg2.action_date
DESC
) AS rank_value,
rfrg2.item,
rfrg2.location,
rfrg2.action_date
FROM
TABLE ( CAST(:b1 AS conflict_check_error_tbl) ) ccet,
rpm_future_retail_gtt rfrg1,
rpm_future_retail_gtt rfrg2
WHERE
rfrg2.rfr_rowid IS NOT NULL
AND
rfrg2.action_date < rfrg1.action_date
AND
rfrg2.location = rfrg1.location
AND
rfrg2.item = rfrg1.item
AND
nvl(
rfrg2.diff_id,
-99999
) = nvl(
rfrg1.diff_id,
-99999
)
AND
rfrg2.zone_node_type = rfrg1.zone_node_type
AND
rfrg1.rfr_rowid IS NULL
AND
rfrg1.future_retail_id = ccet.future_retail_id
AND
rfrg1.price_event_id = ccet.price_event_id
AND
rfrg2.price_event_id = ccet.price_event_id
AND
ccet.error_type =:b2
AND
ccet.cs_promo_fr_id IS NULL
UNION ALL
SELECT /*+ CARDINALITY(ccet,10) LEADING(CCET) */
cspfrg2.price_event_id,
ccet.future_retail_id,
ccet.error_type,
ccet.error_string,
cspfrg2.cust_segment_promo_id,
RANK() OVER(PARTITION BY
cspfrg2.price_event_id,
cspfrg2.item,
cspfrg2.location
ORDER BY
cspfrg2.action_date
DESC
) AS rank_value,
cspfrg2.item,
cspfrg2.location,
cspfrg2.action_date
FROM
TABLE ( CAST(:b1 AS conflict_check_error_tbl) ) ccet,
rpm_cust_segment_promo_fr_gtt cspfrg1,
rpm_cust_segment_promo_fr_gtt cspfrg2
WHERE
cspfrg2.cspfr_rowid IS NOT NULL
AND
cspfrg2.action_date < cspfrg1.action_date
AND
cspfrg2.location = cspfrg1.location
AND
cspfrg2.item = cspfrg1.item
AND
nvl(
cspfrg2.diff_id,
-99999
) = nvl(
cspfrg1.diff_id,
-99999
)
AND
cspfrg2.zone_node_type = cspfrg1.zone_node_type
AND
cspfrg1.cspfr_rowid IS NULL
AND
cspfrg1.cust_segment_promo_id = ccet.cs_promo_fr_id
AND
cspfrg1.price_event_id = ccet.price_event_id
AND
cspfrg2.price_event_id = ccet.price_event_id
AND
ccet.error_type =:b2
AND
ccet.cs_promo_fr_id IS NOT NULL
UNION ALL
SELECT /*+ CARDINALITY(ccet,10) LEADING(CCET) */
rfrg2.price_event_id,
rfrg2.future_retail_id,
ccet.error_type,
ccet.error_string,
ccet.cs_promo_fr_id,
RANK() OVER(PARTITION BY
rfrg2.price_event_id,
rfrg2.item,
rfrg2.location
ORDER BY
rfrg2.action_date
DESC
) AS rank_value,
rfrg2.item,
rfrg2.location,
rfrg2.action_date
FROM
TABLE ( CAST(:b1 AS conflict_check_error_tbl) ) ccet,
rpm_cust_segment_promo_fr_gtt cspfrg,
rpm_future_retail_gtt rfrg1,
rpm_future_retail_gtt rfrg2
WHERE
ccet.cs_promo_fr_id IS NOT NULL
AND
cspfrg.cust_segment_promo_id = ccet.cs_promo_fr_id
AND
cspfrg.cspfr_rowid IS NULL
AND
cspfrg.price_event_id = ccet.price_event_id
AND
rfrg1.future_retail_id = ccet.future_retail_id
AND
rfrg1.price_event_id = ccet.price_event_id
AND
rfrg2.price_event_id = ccet.price_event_id
AND
rfrg2.rfr_rowid IS NOT NULL
AND
rfrg2.action_date <= rfrg1.action_date
AND
rfrg2.location = rfrg1.location
AND
rfrg2.item = rfrg1.item
AND
nvl(
rfrg2.diff_id,
-99999
) = nvl(
rfrg1.diff_id,
-99999
)
AND
rfrg2.zone_node_type = rfrg1.zone_node_type
AND
ccet.error_type =:b2
AND NOT
EXISTS (
SELECT
'x'
FROM
rpm_cust_segment_promo_fr_gtt cspfrg2
WHERE
cspfrg.dept = cspfrg2.dept
AND
cspfrg.item = cspfrg2.item
AND
nvl(
cspfrg.diff_id,
-99999
) = nvl(
cspfrg2.diff_id,
-99999
)
AND
cspfrg.zone_node_type = cspfrg2.zone_node_type
AND
cspfrg.location = cspfrg2.location
AND
cspfrg.customer_type = cspfrg2.customer_type
AND
cspfrg2.action_date < cspfrg.action_date
AND
cspfrg2.cspfr_rowid IS NOT NULL
)
)
WHERE
rank_value = 1
UNION
SELECT /*+ ORDERED NO_MERGE */
t.price_event_id,
rfr.future_retail_id,
t.error_type,
t.error_string,
t.cs_promo_fr_id,
rfr.item,
rfr.location,
rfr.action_date
FROM
(
SELECT /*+ CARDINALITY(ccet,10) LEADING(CCET) USE_HASH(CCET,rfrg1) */
rfrg2.price_event_id,
rfrg2.original_fr_id,
ccet.error_type,
ccet.error_string,
NULL cs_promo_fr_id,
RANK() OVER(PARTITION BY
rfrg2.price_event_id,
rfrg2.item,
rfrg2.location
ORDER BY
rfrg2.action_date
DESC
) AS rank_value
FROM
TABLE ( CAST(:b1 AS conflict_check_error_tbl) ) ccet,
rpm_future_retail_gtt rfrg1,
rpm_future_retail_gtt rfrg2
WHERE
rfrg2.rfr_rowid IS NULL
AND
rfrg2.original_fr_id IS NOT NULL
AND
rfrg2.action_date < rfrg1.action_date
AND
rfrg2.location = rfrg1.location
AND
rfrg2.item = rfrg1.item
AND
nvl(
rfrg2.diff_id,
-99999
) = nvl(
rfrg1.diff_id,
-99999
)
AND
rfrg2.zone_node_type = rfrg1.zone_node_type
AND
rfrg1.rfr_rowid IS NULL
AND
rfrg1.original_fr_id IS NULL
AND
rfrg1.future_retail_id = ccet.future_retail_id
AND
rfrg1.price_event_id = ccet.price_event_id
AND
rfrg2.price_event_id = ccet.price_event_id
AND
ccet.error_type =:b2
AND
ccet.cs_promo_fr_id IS NULL
) t,
rpm_future_retail rfr
WHERE
t.rank_value = 1
AND
rfr.future_retail_id = t.original_fr_id
UNION
SELECT /*+ CARDINALITY(ccet,10) LEADING(CCET) USE_HASH(CCET,rfrg) */
ccet.price_event_id,
ccet.future_retail_id,
ccet.error_type,
ccet.error_string,
ccet.cs_promo_fr_id,
rfrg.item,
rfrg.location,
rfrg.action_date
FROM
TABLE ( CAST(:b1 AS conflict_check_error_tbl) ) ccet,
rpm_future_retail_gtt rfrg
WHERE
rfrg.rfr_rowid IS NOT NULL
AND
rfrg.future_retail_id = ccet.future_retail_id
AND
rfrg.price_event_id = ccet.price_event_id
AND
ccet.error_type =:b2
AND
ccet.cs_promo_fr_id IS NULL
UNION
SELECT /*+ CARDINALITY(ccet,10) LEADING(CCET) USE_HASH(CCET,rfrg) */
ccet.price_event_id,
rfr.future_retail_id,
ccet.error_type,
ccet.error_string,
ccet.cs_promo_fr_id,
rfr.item,
rfr.location,
rfr.action_date
FROM
TABLE ( CAST(:b1 AS conflict_check_error_tbl) ) ccet,
rpm_future_retail_gtt rfrg,
rpm_future_retail rfr
WHERE
rfrg.rfr_rowid IS NULL
AND
rfrg.original_fr_id IS NOT NULL
AND
rfrg.future_retail_id = ccet.future_retail_id
AND
rfrg.price_event_id = ccet.price_event_id
AND
ccet.error_type =:b2
AND
ccet.cs_promo_fr_id IS NULL
AND
rfr.future_retail_id = rfrg.original_fr_id
UNION
SELECT /*+ CARDINALITY(ccet,10) LEADING(CCET) USE_HASH(CCET,rfrg) */
ccet.price_event_id,
ccet.future_retail_id,
ccet.error_type,
ccet.error_string,
ccet.cs_promo_fr_id,
rfrg.item,
rfrg.location,
rfrg.action_date
FROM
TABLE ( CAST(:b1 AS conflict_check_error_tbl) ) ccet,
rpm_cust_segment_promo_fr_gtt rfrg
WHERE
rfrg.cspfr_rowid IS NOT NULL
AND
rfrg.cust_segment_promo_id = ccet.cs_promo_fr_id
AND
rfrg.price_event_id = ccet.price_event_id
AND
ccet.error_type =:b2
AND
ccet.cs_promo_fr_id IS NOT NULL
UNION
SELECT /*+ CARDINALITY(ccet,10) LEADING(CCET) USE_HASH(CCET,rfrg) */
ccet.price_event_id,
ccet.future_retail_id,
ccet.error_type,
ccet.error_string,
rfr.cust_segment_promo_id,
rfrg.item,
rfrg.location,
rfrg.action_date
FROM
TABLE ( CAST(:b1 AS conflict_check_error_tbl) ) ccet,
rpm_cust_segment_promo_fr_gtt rfrg,
rpm_cust_segment_promo_fr rfr
WHERE
rfrg.cspfr_rowid IS NULL
AND
rfrg.original_cs_promo_id IS NOT NULL
AND
rfrg.original_cs_promo_id = rfr.cust_segment_promo_id
AND
rfrg.cust_segment_promo_id = ccet.cs_promo_fr_id
AND
rfrg.price_event_id = ccet.price_event_id
AND
ccet.error_type =:b2
AND
ccet.cs_promo_fr_id IS NOT NULL
UNION
SELECT
ccet.price_event_id,
ccet.future_retail_id,
ccet.error_type,
ccet.error_string,
ccet.cs_promo_fr_id,
NULL,
NULL,
NULL
FROM
TABLE ( CAST(:b1 AS conflict_check_error_tbl) ) ccet
WHERE
ccet.error_type =:b3
)
ORDER BY
item,
location,
action_date;
and here is the problematic execution plan:
Plan hash value: 2444528231
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 339 (100)|
| 1 | SORT ORDER BY | | 646 | 136K| 339 (14)| 00:00:01
| 2 | VIEW | | 646 | 136K| 338 (14)| 00:00:01
| 3 | SORT UNIQUE | | 646 | 2257 | 338 (71)| 00:00:01
| 4 | UNION-ALL | | | | |
|* 5 | VIEW | | 3 | 690 | 114 (14)| 00:00:01
| 6 | UNION-ALL | | | | |
| 7 | WINDOW SORT | | 1 | 32 | 38 (14)| 00:00:01
|* 8 | HASH JOIN | | 1 | 32 | 37 (11)| 00:00:01
|* 9 | HASH JOIN | | 1 | 70 | 35 (12)| 00:00:01
|* 10 | COLLECTION ITERATOR PICKLER FETCH | | 10 | 20 | 32 (10)| 00:00:01
|* 11 | TABLE ACCESS FULL | RPM_FUTURE_RETAIL_GTT | 7 | 658 | 2 (0)| 00:00:01
|* 12 | TABLE ACCESS FULL | RPM_FUTURE_RETAIL_GTT | 14 | 1316 | 2 (0)| 00:00:01
| 13 | WINDOW SORT | | 1 | 32 | 37 (14)| 00:00:01
| 14 | NESTED LOOPS | | 1 | 32 | 36 (12)| 00:00:01
|* 15 | HASH JOIN | | 1 | 44 | 35 (12)| 00:00:01
|* 16 | COLLECTION ITERATOR PICKLER FETCH | | 10 | 20 | 32 (10)| 00:00:01
|* 17 | TABLE ACCESS FULL | RPM_CUST_SEGMENT_PROMO_FR_GTT | 1 | 94 | 2 (0)| 00:00:01
|* 18 | TABLE ACCESS BY INDEX ROWID | RPM_CUST_SEGMENT_PROMO_FR_GTT | 1 | 94 | 1 (0)| 00:00:01
|* 19 | INDEX RANGE SCAN | RPM_CUST_SEGMENT_PROMO_GTT_I1 | 1 | | 1 (0)| 00:00:01
| 20 | WINDOW SORT | | 1 | 88 | 39 (13)| 00:00:01
|* 21 | FILTER | | | | |
| 22 | NESTED LOOPS | | 1 | 88 | 37 (11)| 00:00:01
| 23 | NESTED LOOPS | | 1 | 138 | 36 (12)| 00:00:01
|* 24 | HASH JOIN | | 1 | 70 | 35 (12)| 00:00:01
|* 25 | COLLECTION ITERATOR PICKLER FETCH| | 10 | 20 | 32 (10)| 00:00:01
|* 26 | TABLE ACCESS FULL | RPM_CUST_SEGMENT_PROMO_FR_GTT | 1 | 120 | 2 (0)| 00:00:01
| 27 | TABLE ACCESS BY INDEX ROWID | RPM_FUTURE_RETAIL_GTT | 1 | 94 | 1 (0)| 00:00:01
|* 28 | INDEX RANGE SCAN | RPM_FUTURE_RETAIL_GTT_I3 | 1 | | 1 (0)| 00:00:01
|* 29 | TABLE ACCESS BY INDEX ROWID | RPM_FUTURE_RETAIL_GTT | 1 | 82 | 1 (0)| 00:00:01
|* 30 | INDEX RANGE SCAN | RPM_FUTURE_RETAIL_GTT_I1 | 1 | | 1 (0)| 00:00:01
|* 31 | TABLE ACCESS BY INDEX ROWID | RPM_CUST_SEGMENT_PROMO_FR_GTT | 1 | 94 | 1 (0)| 00:00:01
|* 32 | INDEX RANGE SCAN | RPM_CUST_SEGMENT_PROMO_GTT_I2 | 1 | | 1 (0)| 00:00:01
| 33 | NESTED LOOPS | | | | |
| 34 | NESTED LOOPS | | 1 | 197 | 41 (13)| 00:00:01
|* 35 | VIEW | | 1 | 183 | 38 (14)| 00:00:01
|* 36 | WINDOW SORT PUSHED RANK | | 1 | 45 | 38 (14)| 00:00:01
|* 37 | HASH JOIN | | 1 | 45 | 37 (11)| 00:00:01
|* 38 | HASH JOIN | | 1 | 70 | 35 (12)| 00:00:01
|* 39 | COLLECTION ITERATOR PICKLER FETCH | | 10 | 20 | 32 (10)| 00:00:01
|* 40 | TABLE ACCESS FULL | RPM_FUTURE_RETAIL_GTT | 1 | 94 | 2 (0)| 00:00:01
|* 41 | TABLE ACCESS FULL | RPM_FUTURE_RETAIL_GTT | 14 | 1498 | 2 (0)| 00:00:01
|* 42 | INDEX UNIQUE SCAN | PK_RPM_FUTURE_RETAIL | 1 | | 2 (0)| 00:00:01
| 43 | TABLE ACCESS BY GLOBAL INDEX ROWID | RPM_FUTURE_RETAIL | 1 | 28 | 3 (0)| 00:00:01
|* 44 | HASH JOIN | | 1 | 24 | 35 (12)| 00:00:01
|* 45 | COLLECTION ITERATOR PICKLER FETCH | | 10 | 20 | 32 (10)| 00:00:01
|* 46 | TABLE ACCESS FULL | RPM_FUTURE_RETAIL_GTT | 92 | 6808 | 2 (0)| 00:00:01
| 47 | NESTED LOOPS | | 1 | 15 | 38 (11)| 00:00:01
|* 48 | HASH JOIN | | 1 | 1 | 35 (12)| 00:00:01
|* 49 | COLLECTION ITERATOR PICKLER FETCH | | 10 | 20 | 32 (10)| 00:00:01
|* 50 | TABLE ACCESS FULL | RPM_FUTURE_RETAIL_GTT | 1 | 51 | 2 (0)| 00:00:01
| 51 | TABLE ACCESS BY GLOBAL INDEX ROWID | RPM_FUTURE_RETAIL | 1 | 28 | 3 (0)| 00:00:01
|* 52 | INDEX UNIQUE SCAN | PK_RPM_FUTURE_RETAIL | 1 | | 2 (0)| 00:00:01
|* 53 | HASH JOIN | | 1 | 24 | 35 (12)| 00:00:01
|* 54 | COLLECTION ITERATOR PICKLER FETCH | | 10 | 20 | 32 (10)| 00:00:01
|* 55 | TABLE ACCESS FULL | RPM_CUST_SEGMENT_PROMO_FR_GTT | 1 | 74 | 2 (0)| 00:00:01
| 56 | NESTED LOOPS | | 1 | 31 | 36 (12)| 00:00:01
|* 57 | HASH JOIN | | 1 | 37 | 35 (12)| 00:00:01
|* 58 | COLLECTION ITERATOR PICKLER FETCH | | 10 | 20 | 32 (10)| 00:00:01
|* 59 | TABLE ACCESS FULL | RPM_CUST_SEGMENT_PROMO_FR_GTT | 1 | 87 | 2 (0)| 00:00:01
|* 60 | INDEX UNIQUE SCAN | PK_RPM_CUST_SEGMENT_PROMO_FR | 1 | 6 | 1 (0)| 00:00:01
|* 61 | collection iterator pickler fetch | | 638 | 1276 | 32 (10)| 00:00:01
----------------------------------------------------------------------------------------------------------------------------
After analyzing what is going on, I’ve managed to isolate part of the plan responsible for performance degradation.
After I fix the plan, the new plan now look like this:
Plan hash value: 3668104064
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 306 (100)|
| 1 | SORT ORDER BY | | 9 | 1953 | 306 (6)| 00:00:04
| 2 | VIEW | | 9 | 1953 | 305 (5)| 00:00:04
| 3 | SORT UNIQUE | | 9 | 983 | 305 (68)| 00:00:04
| 4 | UNION-ALL | | | | |
| 5 | VIEW | | 3 | 690 | 103 (5)| 00:00:02
| 6 | UNION-ALL | | | | |
| 7 | WINDOW SORT | | 1 | 32 | 34 (6)| 00:00:01
| 8 | NESTED LOOPS | | 1 | 32 | 33 (4)| 00:00:01
| 9 | HASH JOIN | | 1 | 44 | 32 (4)| 00:00:01
| 10 | COLLECTION ITERATOR PICKLER FETCH | | 10 | 20 | 29 (0)| 00:00:01
| 11 | TABLE ACCESS FULL | RPM_FUTURE_RETAIL_GTT | 1 | 94 | 2 (0)| 00:00:01
| 12 | TABLE ACCESS BY INDEX ROWID | RPM_FUTURE_RETAIL_GTT | 1 | 94 | 1 (0)| 00:00:01
| 13 | INDEX RANGE SCAN | RPM_FUTURE_RETAIL_GTT_I1 | 1 | | 1 (0)| 00:00:01
| 14 | WINDOW SORT | | 1 | 32 | 34 (6)| 00:00:01
| 15 | NESTED LOOPS | | 1 | 32 | 33 (4)| 00:00:01
| 16 | HASH JOIN | | 1 | 44 | 32 (4)| 00:00:01
| 17 | COLLECTION ITERATOR PICKLER FETCH | | 10 | 20 | 29 (0)| 00:00:01
| 18 | TABLE ACCESS FULL | RPM_CUST_SEGMENT_PROMO_FR_GTT | 1 | 94 | 2 (0)| 00:00:01
| 19 | TABLE ACCESS BY INDEX ROWID | RPM_CUST_SEGMENT_PROMO_FR_GTT | 1 | 94 | 1 (0)| 00:00:01
| 20 | INDEX RANGE SCAN | RPM_CUST_SEGMENT_PROMO_GTT_I1 | 1 | | 1 (0)| 00:00:01
| 21 | WINDOW SORT | | 1 | 88 | 36 (6)| 00:00:01
| 22 | FILTER | | | | |
| 23 | NESTED LOOPS | | 1 | 88 | 34 (3)| 00:00:01
| 24 | NESTED LOOPS | | 1 | 138 | 33 (4)| 00:00:01
| 25 | HASH JOIN | | 1 | 70 | 32 (4)| 00:00:01
| 26 | COLLECTION ITERATOR PICKLER FETCH| | 10 | 20 | 29 (0)| 00:00:01
| 27 | TABLE ACCESS FULL | RPM_CUST_SEGMENT_PROMO_FR_GTT | 1 | 120 | 2 (0)| 00:00:01
| 28 | TABLE ACCESS BY INDEX ROWID | RPM_FUTURE_RETAIL_GTT | 1 | 94 | 1 (0)| 00:00:01
| 29 | INDEX RANGE SCAN | RPM_FUTURE_RETAIL_GTT_I3 | 1 | | 1 (0)| 00:00:01
| 30 | TABLE ACCESS BY INDEX ROWID | RPM_FUTURE_RETAIL_GTT | 1 | 82 | 1 (0)| 00:00:01
| 31 | INDEX RANGE SCAN | RPM_FUTURE_RETAIL_GTT_I1 | 1 | | 1 (0)| 00:00:01
| 32 | TABLE ACCESS BY INDEX ROWID | RPM_CUST_SEGMENT_PROMO_FR_GTT | 1 | 94 | 1 (0)| 00:00:01
| 33 | INDEX RANGE SCAN | RPM_CUST_SEGMENT_PROMO_GTT_I2 | 1 | | 1 (0)| 00:00:01
| 34 | NESTED LOOPS | | | | |
| 35 | NESTED LOOPS | | 1 | 197 | 37 (6)| 00:00:01
| 36 | VIEW | | 1 | 183 | 34 (6)| 00:00:01
| 37 | WINDOW SORT PUSHED RANK | | 1 | 45 | 34 (6)| 00:00:01
| 38 | NESTED LOOPS | | 1 | 45 | 33 (4)| 00:00:01
| 39 | HASH JOIN | | 1 | 57 | 32 (4)| 00:00:01
| 40 | COLLECTION ITERATOR PICKLER FETCH | | 10 | 20 | 29 (0)| 00:00:01
| 41 | TABLE ACCESS FULL | RPM_FUTURE_RETAIL_GTT | 1 | 107 | 2 (0)| 00:00:01
| 42 | TABLE ACCESS BY INDEX ROWID | RPM_FUTURE_RETAIL_GTT | 1 | 94 | 1 (0)| 00:00:01
| 43 | INDEX RANGE SCAN | RPM_FUTURE_RETAIL_GTT_I1 | 1 | | 1 (0)| 00:00:01
| 44 | INDEX UNIQUE SCAN | PK_RPM_FUTURE_RETAIL | 1 | | 2 (0)| 00:00:01
| 45 | TABLE ACCESS BY GLOBAL INDEX ROWID | RPM_FUTURE_RETAIL | 1 | 28 | 3 (0)| 00:00:01
| 46 | HASH JOIN | | 1 | 24 | 32 (4)| 00:00:01
| 47 | COLLECTION ITERATOR PICKLER FETCH | | 10 | 20 | 29 (0)| 00:00:01
| 48 | TABLE ACCESS FULL | RPM_FUTURE_RETAIL_GTT | 1125 | 83250 | 2 (0)| 00:00:01
| 49 | NESTED LOOPS | | 1 | 15 | 35 (3)| 00:00:01
| 50 | HASH JOIN | | 1 | 1 | 32 (4)| 00:00:01
| 51 | COLLECTION ITERATOR PICKLER FETCH | | 10 | 20 | 29 (0)| 00:00:01
| 52 | TABLE ACCESS FULL | RPM_FUTURE_RETAIL_GTT | 1 | 51 | 2 (0)| 00:00:01
| 53 | TABLE ACCESS BY GLOBAL INDEX ROWID | RPM_FUTURE_RETAIL | 1 | 28 | 3 (0)| 00:00:01
| 54 | INDEX UNIQUE SCAN | PK_RPM_FUTURE_RETAIL | 1 | | 2 (0)| 00:00:01
| 55 | HASH JOIN | | 1 | 24 | 32 (4)| 00:00:01
| 56 | COLLECTION ITERATOR PICKLER FETCH | | 10 | 20 | 29 (0)| 00:00:01
| 57 | TABLE ACCESS FULL | RPM_CUST_SEGMENT_PROMO_FR_GTT | 1 | 74 | 2 (0)| 00:00:01
| 58 | NESTED LOOPS | | 1 | 31 | 33 (4)| 00:00:01
| 59 | HASH JOIN | | 1 | 37 | 32 (4)| 00:00:01
| 60 | COLLECTION ITERATOR PICKLER FETCH | | 10 | 20 | 29 (0)| 00:00:01
| 61 | TABLE ACCESS FULL | RPM_CUST_SEGMENT_PROMO_FR_GTT | 1 | 87 | 2 (0)| 00:00:01
| 62 | INDEX UNIQUE SCAN | PK_RPM_CUST_SEGMENT_PROMO_FR | 1 | 6 | 1 (0)| 00:00:01
| 63 | collection iterator pickler fetch | | 1 | 2 | 29 (0)| 00:00:01
----------------------------------------------------------------------------------------------------------------------------
Problem was in data that needs to be processed as well as the usage of object types that are changing quickly, and are involved in complex processing.
RPM is the most demanding/complex module of the Oracle Retail Suite, where user action (like promotion, clearance etc.) basically start the very complex job (comparable with ETL chain when loading data into the Data Warehouse system).
For that reason, it’s not an easy task to maintain good performance of the RPM, but if you can do it, business benefit can be huge.
Comments