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

There are no comments yet. Why not start the discussion?

Leave a Reply

Your email address will not be published. Required fields are marked *