# 1366133.1 SQLHC 23.1 Report: sqlhc_20240418_0914_8tyvq2bp8xkfy_3_execution_plans.html

    License    : T
    Input      : 8tyvq2bp8xkfy
    SIGNATURE  : 407805424935029650
    SIGNATUREF : 17968218173887074447
    RDBMS      : 19.0.0.0.0
    Platform   :
    Database   : absdb
    DBID       : 3340965127
    Host       : absracprd13
    Instance   : 3
    CPU_Count  : 192
    Num CPUs   : 192
    Num Cores  : 96
    Num Sockets: 4
    Block Size : 8192
    OFE        : 19.1.0
    DYN_SAMP   : 2
    EBS        : ""
    SIEBEL     : ""
    PSFT       : ""
    Date       : 2024-04-18/09:14:10
    User       : SYS

- SQL Text
- Current Execution Plans (last execution)
- Current Execution Plans (all executions)
- Historical Execution Plans

## SQL Text

    select 1
    from PRODUCTLISTPERCUSTOMER PLPC
    join PRODUCT P
    on P.PRODUCT_ID = PLPC.PRODUCT_ID
    where PLPC.CUSTOMER_ID = :1
    and PLPC.PRODUCT_ID = :2
    and PLPC.SIZEDEFINITION_ID = :3
    and PLPC.FINISHINGMETHOD_ID = :4     and PLPC.DATEACTIVE <= :5
    and PLPC.DATEINACTIVE >= :6
    and P.TYPEOFMERCHANDISE in (:7 , :8 )
    and ((PLPC.BILLINGMETHOD = :9  and PLPC.CHARGEWASHESONLYFORCOG = :10 ) or
    (PLPC.BILLINGMETHOD = :11  and PLPC.CHARGEWASHESONLYFORCOG = :12  and
    PLPC.PRICEUSAGE <> :13 )
    )

## Current Execution Plans (last execution)

Captured while still in memory. Metrics below are for the last execution of each child cursor.
If STATISTICS_LEVEL was set to ALL at the time of the hard-parse then A-Rows column is populated.

    Inst: 3   Child: 0
    Plan hash value: 3547044130

    ---------------------------------------------------------------------------------------------------------------------

    | Id  | Operation                            | Name                        | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |

    ---------------------------------------------------------------------------------------------------------------------

    |   0 | SELECT STATEMENT                     |                             |        |       |     6 (100)|          |

    |   1 |  NESTED LOOPS                        |                             |      1 |    51 |     6   (0)| 00:00:01 |

    |*  2 |   TABLE ACCESS BY INDEX ROWID        | PRODUCT                     |      1 |     8 |     3   (0)| 00:00:01 |

    |*  3 |    INDEX UNIQUE SCAN                 | XPKPRODUCT                  |      1 |       |     2   (0)| 00:00:01 |

    |*  4 |   TABLE ACCESS BY INDEX ROWID BATCHED| PRODUCTLISTPERCUSTOMER      |      1 |    43 |     3   (0)| 00:00:01 |

    |*  5 |    INDEX RANGE SCAN                  | XIF85PRODUCTLISTPERCUSTOMER |      1 |       |     2   (0)| 00:00:01 |

    ---------------------------------------------------------------------------------------------------------------------

    Query Block Name / Object Alias (identified by operation id):

    -------------------------------------------------------------

       1 - SEL$58A6D7F6

       2 - SEL$58A6D7F6 / P@SEL$1

       3 - SEL$58A6D7F6 / P@SEL$1

       4 - SEL$58A6D7F6 / PLPC@SEL$1

       5 - SEL$58A6D7F6 / PLPC@SEL$1

    Outline Data

    -------------

      /*+

          BEGIN_OUTLINE_DATA

          IGNORE_OPTIM_EMBEDDED_HINTS

          OPTIMIZER_FEATURES_ENABLE('19.1.0')

          DB_VERSION('19.1.0')

          ALL_ROWS

          OUTLINE_LEAF(@"SEL$58A6D7F6")

          MERGE(@"SEL$1" >"SEL$2")

          OUTLINE(@"SEL$2")

          OUTLINE(@"SEL$1")

          INDEX_RS_ASC(@"SEL$58A6D7F6" "P"@"SEL$1" ("PRODUCT"."PRODUCT_ID"))

          INDEX_RS_ASC(@"SEL$58A6D7F6" "PLPC"@"SEL$1" ("PRODUCTLISTPERCUSTOMER"."SIZEDEFINITION_ID"))

          BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$58A6D7F6" "PLPC"@"SEL$1")

          LEADING(@"SEL$58A6D7F6" "P"@"SEL$1" "PLPC"@"SEL$1")

          USE_NL(@"SEL$58A6D7F6" "PLPC"@"SEL$1")

          END_OUTLINE_DATA

      */

    Peeked Binds (identified by position):

    --------------------------------------

       1 - (NUMBER): 189806

       2 - (NUMBER): 48082

       3 - (NUMBER): 142

       4 - (NUMBER): 1

       5 - (DATE): 04/16/2024 00:00:00

       6 - (DATE): 04/16/2024 00:00:00

       7 - (NUMBER): 1

       8 - (NUMBER): 2

       9 - (NUMBER): 4

      10 - (NUMBER): 3

      11 - (NUMBER): 3

      12 - (NUMBER): 3

      13 - (NUMBER): 2

    Predicate Information (identified by operation id):

    ---------------------------------------------------

       2 - filter(("P"."TYPEOFMERCHANDISE"=:7 OR "P"."TYPEOFMERCHANDISE"=:8))

       3 - access("P"."PRODUCT_ID"=:2)

       4 - filter(("PLPC"."CUSTOMER_ID"=:1 AND "PLPC"."PRODUCT_ID"=:2 AND (("PLPC"."CHARGEWASHESONLYFORCOG"=:12

                  AND "PLPC"."PRICEUSAGE"<>:13 AND "PLPC"."BILLINGMETHOD"=:11) OR ("PLPC"."BILLINGMETHOD"=:9 AND

                  "PLPC"."CHARGEWASHESONLYFORCOG"=:10)) AND "PLPC"."DATEINACTIVE">=:6 AND "PLPC"."FINISHINGMETHOD_ID"=:4 AND

                  "PLPC"."DATEACTIVE"<=:5))

       5 - access("PLPC"."SIZEDEFINITION_ID"=:3)

    Column Projection Information (identified by operation id):

    -----------------------------------------------------------

       3 - "P".ROWID[ROWID,10]

       5 - "PLPC".ROWID[ROWID,10]

    Note

    -----

       - Warning: basic plan statistics not available. These are only collected when:

           * hint 'gather_plan_statistics' is used for the statement or

           * parameter 'statistics_level' is set to 'ALL', at session or system level

    Inst: 1   Child: 0
    Plan hash value: 3707164884

    --------------------------------------------------------------------------------------------------------------------

    | Id  | Operation                            | Name                       | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |

    --------------------------------------------------------------------------------------------------------------------

    |   0 | SELECT STATEMENT                     |                            |        |       |     6 (100)|          |

    |   1 |  NESTED LOOPS                        |                            |      1 |    51 |     6   (0)| 00:00:01 |

    |*  2 |   TABLE ACCESS BY INDEX ROWID        | PRODUCT                    |      1 |     8 |     3   (0)| 00:00:01 |

    |*  3 |    INDEX UNIQUE SCAN                 | XPKPRODUCT                 |      1 |       |     2   (0)| 00:00:01 |

    |*  4 |   TABLE ACCESS BY INDEX ROWID BATCHED| PRODUCTLISTPERCUSTOMER     |      1 |    43 |     3   (0)| 00:00:01 |

    |*  5 |    INDEX RANGE SCAN                  | XAK3PRODUCTLISTPERCUSTOMER |      1 |       |     2   (0)| 00:00:01 |

    --------------------------------------------------------------------------------------------------------------------

    Query Block Name / Object Alias (identified by operation id):

    -------------------------------------------------------------

       1 - SEL$58A6D7F6

       2 - SEL$58A6D7F6 / P@SEL$1

       3 - SEL$58A6D7F6 / P@SEL$1

       4 - SEL$58A6D7F6 / PLPC@SEL$1

       5 - SEL$58A6D7F6 / PLPC@SEL$1

    Outline Data

    -------------

      /*+

          BEGIN_OUTLINE_DATA

          IGNORE_OPTIM_EMBEDDED_HINTS

          OPTIMIZER_FEATURES_ENABLE('19.1.0')

          DB_VERSION('19.1.0')

          ALL_ROWS

          OUTLINE_LEAF(@"SEL$58A6D7F6")

          MERGE(@"SEL$1" >"SEL$2")

          OUTLINE(@"SEL$2")

          OUTLINE(@"SEL$1")

          INDEX_RS_ASC(@"SEL$58A6D7F6" "P"@"SEL$1" ("PRODUCT"."PRODUCT_ID"))

          INDEX_RS_ASC(@"SEL$58A6D7F6" "PLPC"@"SEL$1" ("PRODUCTLISTPERCUSTOMER"."CUSTOMER_ID"

                  "PRODUCTLISTPERCUSTOMER"."PRODUCT_ID" "PRODUCTLISTPERCUSTOMER"."SIZEDEFINITION_ID"

                  "PRODUCTLISTPERCUSTOMER"."FINISHINGMETHOD_ID" "PRODUCTLISTPERCUSTOMER"."DATEINACTIVE"))

          BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$58A6D7F6" "PLPC"@"SEL$1")

          LEADING(@"SEL$58A6D7F6" "P"@"SEL$1" "PLPC"@"SEL$1")

          USE_NL(@"SEL$58A6D7F6" "PLPC"@"SEL$1")

          END_OUTLINE_DATA

      */

    Peeked Binds (identified by position):

    --------------------------------------

       1 - (NUMBER): 331726

       2 - (NUMBER): 51017

       3 - (NUMBER): 1535

       4 - (NUMBER): 1

       5 - (DATE): 04/16/2024 00:00:00

       6 - (DATE): 04/16/2024 00:00:00

       7 - (NUMBER): 1

       8 - (NUMBER): 2

       9 - (NUMBER): 4

      10 - (NUMBER): 3

      11 - (NUMBER): 3

      12 - (NUMBER): 3

      13 - (NUMBER): 2

    Predicate Information (identified by operation id):

    ---------------------------------------------------

       2 - filter(("P"."TYPEOFMERCHANDISE"=:7 OR "P"."TYPEOFMERCHANDISE"=:8))

       3 - access("P"."PRODUCT_ID"=:2)

       4 - filter(((("PLPC"."CHARGEWASHESONLYFORCOG"=:12 AND "PLPC"."PRICEUSAGE"<>:13 AND

                  "PLPC"."BILLINGMETHOD"=:11) OR ("PLPC"."BILLINGMETHOD"=:9 AND "PLPC"."CHARGEWASHESONLYFORCOG"=:10)) AND

                  "PLPC"."DATEACTIVE"<=:5))

       5 - access("PLPC"."CUSTOMER_ID"=:1 AND "PLPC"."PRODUCT_ID"=:2 AND "PLPC"."SIZEDEFINITION_ID"=:3 AND

                  "PLPC"."FINISHINGMETHOD_ID"=:4 AND "PLPC"."DATEINACTIVE">=:6)

    Column Projection Information (identified by operation id):

    -----------------------------------------------------------

       3 - "P".ROWID[ROWID,10]

       5 - "PLPC".ROWID[ROWID,10]

    Note

    -----

       - Warning: basic plan statistics not available. These are only collected when:

           * hint 'gather_plan_statistics' is used for the statement or

           * parameter 'statistics_level' is set to 'ALL', at session or system level

    Inst: 2   Child: 1
    Plan hash value: 4257544664

    --------------------------------------------------------------------------------------------------------------------

    | Id  | Operation                            | Name                       | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |

    --------------------------------------------------------------------------------------------------------------------

    |   0 | SELECT STATEMENT                     |                            |        |       |     6 (100)|          |

    |   1 |  NESTED LOOPS                        |                            |      1 |    51 |     6   (0)| 00:00:01 |

    |*  2 |   TABLE ACCESS BY INDEX ROWID        | PRODUCT                    |      1 |     8 |     3   (0)| 00:00:01 |

    |*  3 |    INDEX UNIQUE SCAN                 | XPKPRODUCT                 |      1 |       |     2   (0)| 00:00:01 |

    |*  4 |   TABLE ACCESS BY INDEX ROWID BATCHED| PRODUCTLISTPERCUSTOMER     |      1 |    43 |     3   (0)| 00:00:01 |

    |*  5 |    INDEX RANGE SCAN                  | XAK2PRODUCTLISTPERCUSTOMER |      1 |       |     2   (0)| 00:00:01 |

    --------------------------------------------------------------------------------------------------------------------

    Query Block Name / Object Alias (identified by operation id):

    -------------------------------------------------------------

       1 - SEL$58A6D7F6

       2 - SEL$58A6D7F6 / P@SEL$1

       3 - SEL$58A6D7F6 / P@SEL$1

       4 - SEL$58A6D7F6 / PLPC@SEL$1

       5 - SEL$58A6D7F6 / PLPC@SEL$1

    Outline Data

    -------------

      /*+

          BEGIN_OUTLINE_DATA

          IGNORE_OPTIM_EMBEDDED_HINTS

          OPTIMIZER_FEATURES_ENABLE('19.1.0')

          DB_VERSION('19.1.0')

          ALL_ROWS

          OUTLINE_LEAF(@"SEL$58A6D7F6")

          MERGE(@"SEL$1" >"SEL$2")

          OUTLINE(@"SEL$2")

          OUTLINE(@"SEL$1")

          INDEX_RS_ASC(@"SEL$58A6D7F6" "P"@"SEL$1" ("PRODUCT"."PRODUCT_ID"))

          INDEX_RS_ASC(@"SEL$58A6D7F6" "PLPC"@"SEL$1" ("PRODUCTLISTPERCUSTOMER"."CUSTOMER_ID"

                  "PRODUCTLISTPERCUSTOMER"."PRODUCT_ID" "PRODUCTLISTPERCUSTOMER"."SIZEDEFINITION_ID"

                  "PRODUCTLISTPERCUSTOMER"."FINISHINGMETHOD_ID" "PRODUCTLISTPERCUSTOMER"."DATEACTIVE"))

          BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$58A6D7F6" "PLPC"@"SEL$1")

          LEADING(@"SEL$58A6D7F6" "P"@"SEL$1" "PLPC"@"SEL$1")

          USE_NL(@"SEL$58A6D7F6" "PLPC"@"SEL$1")

          END_OUTLINE_DATA

      */

    Peeked Binds (identified by position):

    --------------------------------------

       1 - (NUMBER): 415121

       2 - (NUMBER): 52335

       3 - (NUMBER): 623

       4 - (NUMBER): 1

       5 - (DATE): 04/16/2024 00:00:00

       6 - (DATE): 04/16/2024 00:00:00

       7 - (NUMBER): 1

       8 - (NUMBER): 2

       9 - (NUMBER): 4

      10 - (NUMBER): 3

      11 - (NUMBER): 3

      12 - (NUMBER): 3

      13 - (NUMBER): 2

    Predicate Information (identified by operation id):

    ---------------------------------------------------

       2 - filter(("P"."TYPEOFMERCHANDISE"=:7 OR "P"."TYPEOFMERCHANDISE"=:8))

       3 - access("P"."PRODUCT_ID"=:2)

       4 - filter(((("PLPC"."CHARGEWASHESONLYFORCOG"=:12 AND "PLPC"."PRICEUSAGE"<>:13 AND

                  "PLPC"."BILLINGMETHOD"=:11) OR ("PLPC"."BILLINGMETHOD"=:9 AND "PLPC"."CHARGEWASHESONLYFORCOG"=:10)) AND

                  "PLPC"."DATEINACTIVE">=:6))

       5 - access("PLPC"."CUSTOMER_ID"=:1 AND "PLPC"."PRODUCT_ID"=:2 AND "PLPC"."SIZEDEFINITION_ID"=:3 AND

                  "PLPC"."FINISHINGMETHOD_ID"=:4 AND "PLPC"."DATEACTIVE"<=:5)

    Column Projection Information (identified by operation id):

    -----------------------------------------------------------

       3 - "P".ROWID[ROWID,10]

       5 - "PLPC".ROWID[ROWID,10]

    Note

    -----

       - Warning: basic plan statistics not available. These are only collected when:

           * hint 'gather_plan_statistics' is used for the statement or

           * parameter 'statistics_level' is set to 'ALL', at session or system level

## Current Execution Plans (all executions)

Captured while still in memory. Metrics below are an aggregate for all the execution of each child cursor.
If STATISTICS_LEVEL was set to ALL at the time of the hard-parse then A-Rows column is populated.

    Inst: 3   Child: 0
    Plan hash value: 3547044130

    ---------------------------------------------------------------------------------------------------------------------

    | Id  | Operation                            | Name                        | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |

    ---------------------------------------------------------------------------------------------------------------------

    |   0 | SELECT STATEMENT                     |                             |        |       |     6 (100)|          |

    |   1 |  NESTED LOOPS                        |                             |      1 |    51 |     6   (0)| 00:00:01 |

    |*  2 |   TABLE ACCESS BY INDEX ROWID        | PRODUCT                     |      1 |     8 |     3   (0)| 00:00:01 |

    |*  3 |    INDEX UNIQUE SCAN                 | XPKPRODUCT                  |      1 |       |     2   (0)| 00:00:01 |

    |*  4 |   TABLE ACCESS BY INDEX ROWID BATCHED| PRODUCTLISTPERCUSTOMER      |      1 |    43 |     3   (0)| 00:00:01 |

    |*  5 |    INDEX RANGE SCAN                  | XIF85PRODUCTLISTPERCUSTOMER |      1 |       |     2   (0)| 00:00:01 |

    ---------------------------------------------------------------------------------------------------------------------

    Query Block Name / Object Alias (identified by operation id):

    -------------------------------------------------------------

    1 - SEL$58A6D7F6

    2 - SEL$58A6D7F6 / P@SEL$1

    3 - SEL$58A6D7F6 / P@SEL$1

    4 - SEL$58A6D7F6 / PLPC@SEL$1

    5 - SEL$58A6D7F6 / PLPC@SEL$1

    Outline Data

    -------------

    /*+

    BEGIN_OUTLINE_DATA

    IGNORE_OPTIM_EMBEDDED_HINTS

    OPTIMIZER_FEATURES_ENABLE('19.1.0')

    DB_VERSION('19.1.0')

    ALL_ROWS

    OUTLINE_LEAF(@"SEL$58A6D7F6")

    MERGE(@"SEL$1" >"SEL$2")

    OUTLINE(@"SEL$2")

    OUTLINE(@"SEL$1")

    INDEX_RS_ASC(@"SEL$58A6D7F6" "P"@"SEL$1" ("PRODUCT"."PRODUCT_ID"))

    INDEX_RS_ASC(@"SEL$58A6D7F6" "PLPC"@"SEL$1" ("PRODUCTLISTPERCUSTOMER"."SIZEDEFINITION_ID"))

    BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$58A6D7F6" "PLPC"@"SEL$1")

    LEADING(@"SEL$58A6D7F6" "P"@"SEL$1" "PLPC"@"SEL$1")

    USE_NL(@"SEL$58A6D7F6" "PLPC"@"SEL$1")

    END_OUTLINE_DATA

    */

    Peeked Binds (identified by position):

    --------------------------------------

    1 - (NUMBER): 189806

    2 - (NUMBER): 48082

    3 - (NUMBER): 142

    4 - (NUMBER): 1

    5 - (DATE): 04/16/2024 00:00:00

    6 - (DATE): 04/16/2024 00:00:00

    7 - (NUMBER): 1

    8 - (NUMBER): 2

    9 - (NUMBER): 4

    10 - (NUMBER): 3

    11 - (NUMBER): 3

    12 - (NUMBER): 3

    13 - (NUMBER): 2

    Predicate Information (identified by operation id):

    ---------------------------------------------------

    2 - filter(("P"."TYPEOFMERCHANDISE"=:7 OR "P"."TYPEOFMERCHANDISE"=:8))

    3 - access("P"."PRODUCT_ID"=:2)

    4 - filter(("PLPC"."CUSTOMER_ID"=:1 AND "PLPC"."PRODUCT_ID"=:2 AND (("PLPC"."CHARGEWASHESONLYFORCOG"=:12

    AND "PLPC"."PRICEUSAGE"<>:13 AND "PLPC"."BILLINGMETHOD"=:11) OR ("PLPC"."BILLINGMETHOD"=:9 AND

    "PLPC"."CHARGEWASHESONLYFORCOG"=:10)) AND "PLPC"."DATEINACTIVE">=:6 AND "PLPC"."FINISHINGMETHOD_ID"=:4 AND

    "PLPC"."DATEACTIVE"<=:5))

    5 - access("PLPC"."SIZEDEFINITION_ID"=:3)

    Column Projection Information (identified by operation id):

    -----------------------------------------------------------

    3 - "P".ROWID[ROWID,10]

    5 - "PLPC".ROWID[ROWID,10]

    Note

    -----

    - Warning: basic plan statistics not available. These are only collected when:

    * hint 'gather_plan_statistics' is used for the statement or

    * parameter 'statistics_level' is set to 'ALL', at session or system level

    Inst: 1   Child: 0
    Plan hash value: 3707164884

    --------------------------------------------------------------------------------------------------------------------

    | Id  | Operation                            | Name                       | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |

    --------------------------------------------------------------------------------------------------------------------

    |   0 | SELECT STATEMENT                     |                            |        |       |     6 (100)|          |

    |   1 |  NESTED LOOPS                        |                            |      1 |    51 |     6   (0)| 00:00:01 |

    |*  2 |   TABLE ACCESS BY INDEX ROWID        | PRODUCT                    |      1 |     8 |     3   (0)| 00:00:01 |

    |*  3 |    INDEX UNIQUE SCAN                 | XPKPRODUCT                 |      1 |       |     2   (0)| 00:00:01 |

    |*  4 |   TABLE ACCESS BY INDEX ROWID BATCHED| PRODUCTLISTPERCUSTOMER     |      1 |    43 |     3   (0)| 00:00:01 |

    |*  5 |    INDEX RANGE SCAN                  | XAK3PRODUCTLISTPERCUSTOMER |      1 |       |     2   (0)| 00:00:01 |

    --------------------------------------------------------------------------------------------------------------------

    Query Block Name / Object Alias (identified by operation id):

    -------------------------------------------------------------

    1 - SEL$58A6D7F6

    2 - SEL$58A6D7F6 / P@SEL$1

    3 - SEL$58A6D7F6 / P@SEL$1

    4 - SEL$58A6D7F6 / PLPC@SEL$1

    5 - SEL$58A6D7F6 / PLPC@SEL$1

    Outline Data

    -------------

    /*+

    BEGIN_OUTLINE_DATA

    IGNORE_OPTIM_EMBEDDED_HINTS

    OPTIMIZER_FEATURES_ENABLE('19.1.0')

    DB_VERSION('19.1.0')

    ALL_ROWS

    OUTLINE_LEAF(@"SEL$58A6D7F6")

    MERGE(@"SEL$1" >"SEL$2")

    OUTLINE(@"SEL$2")

    OUTLINE(@"SEL$1")

    INDEX_RS_ASC(@"SEL$58A6D7F6" "P"@"SEL$1" ("PRODUCT"."PRODUCT_ID"))

    INDEX_RS_ASC(@"SEL$58A6D7F6" "PLPC"@"SEL$1" ("PRODUCTLISTPERCUSTOMER"."CUSTOMER_ID"

    "PRODUCTLISTPERCUSTOMER"."PRODUCT_ID" "PRODUCTLISTPERCUSTOMER"."SIZEDEFINITION_ID"

    "PRODUCTLISTPERCUSTOMER"."FINISHINGMETHOD_ID" "PRODUCTLISTPERCUSTOMER"."DATEINACTIVE"))

    BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$58A6D7F6" "PLPC"@"SEL$1")

    LEADING(@"SEL$58A6D7F6" "P"@"SEL$1" "PLPC"@"SEL$1")

    USE_NL(@"SEL$58A6D7F6" "PLPC"@"SEL$1")

    END_OUTLINE_DATA

    */

    Peeked Binds (identified by position):

    --------------------------------------

    1 - (NUMBER): 331726

    2 - (NUMBER): 51017

    3 - (NUMBER): 1535

    4 - (NUMBER): 1

    5 - (DATE): 04/16/2024 00:00:00

    6 - (DATE): 04/16/2024 00:00:00

    7 - (NUMBER): 1

    8 - (NUMBER): 2

    9 - (NUMBER): 4

    10 - (NUMBER): 3

    11 - (NUMBER): 3

    12 - (NUMBER): 3

    13 - (NUMBER): 2

    Predicate Information (identified by operation id):

    ---------------------------------------------------

    2 - filter(("P"."TYPEOFMERCHANDISE"=:7 OR "P"."TYPEOFMERCHANDISE"=:8))

    3 - access("P"."PRODUCT_ID"=:2)

    4 - filter(((("PLPC"."CHARGEWASHESONLYFORCOG"=:12 AND "PLPC"."PRICEUSAGE"<>:13 AND

    "PLPC"."BILLINGMETHOD"=:11) OR ("PLPC"."BILLINGMETHOD"=:9 AND "PLPC"."CHARGEWASHESONLYFORCOG"=:10)) AND

    "PLPC"."DATEACTIVE"<=:5))

    5 - access("PLPC"."CUSTOMER_ID"=:1 AND "PLPC"."PRODUCT_ID"=:2 AND "PLPC"."SIZEDEFINITION_ID"=:3 AND

    "PLPC"."FINISHINGMETHOD_ID"=:4 AND "PLPC"."DATEINACTIVE">=:6)

    Column Projection Information (identified by operation id):

    -----------------------------------------------------------

    3 - "P".ROWID[ROWID,10]

    5 - "PLPC".ROWID[ROWID,10]

    Note

    -----

    - Warning: basic plan statistics not available. These are only collected when:

    * hint 'gather_plan_statistics' is used for the statement or

    * parameter 'statistics_level' is set to 'ALL', at session or system level

    Inst: 2   Child: 1
    Plan hash value: 4257544664

    --------------------------------------------------------------------------------------------------------------------

    | Id  | Operation                            | Name                       | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |

    --------------------------------------------------------------------------------------------------------------------

    |   0 | SELECT STATEMENT                     |                            |        |       |     6 (100)|          |

    |   1 |  NESTED LOOPS                        |                            |      1 |    51 |     6   (0)| 00:00:01 |

    |*  2 |   TABLE ACCESS BY INDEX ROWID        | PRODUCT                    |      1 |     8 |     3   (0)| 00:00:01 |

    |*  3 |    INDEX UNIQUE SCAN                 | XPKPRODUCT                 |      1 |       |     2   (0)| 00:00:01 |

    |*  4 |   TABLE ACCESS BY INDEX ROWID BATCHED| PRODUCTLISTPERCUSTOMER     |      1 |    43 |     3   (0)| 00:00:01 |

    |*  5 |    INDEX RANGE SCAN                  | XAK2PRODUCTLISTPERCUSTOMER |      1 |       |     2   (0)| 00:00:01 |

    --------------------------------------------------------------------------------------------------------------------

    Query Block Name / Object Alias (identified by operation id):

    -------------------------------------------------------------

    1 - SEL$58A6D7F6

    2 - SEL$58A6D7F6 / P@SEL$1

    3 - SEL$58A6D7F6 / P@SEL$1

    4 - SEL$58A6D7F6 / PLPC@SEL$1

    5 - SEL$58A6D7F6 / PLPC@SEL$1

    Outline Data

    -------------

    /*+

    BEGIN_OUTLINE_DATA

    IGNORE_OPTIM_EMBEDDED_HINTS

    OPTIMIZER_FEATURES_ENABLE('19.1.0')

    DB_VERSION('19.1.0')

    ALL_ROWS

    OUTLINE_LEAF(@"SEL$58A6D7F6")

    MERGE(@"SEL$1" >"SEL$2")

    OUTLINE(@"SEL$2")

    OUTLINE(@"SEL$1")

    INDEX_RS_ASC(@"SEL$58A6D7F6" "P"@"SEL$1" ("PRODUCT"."PRODUCT_ID"))

    INDEX_RS_ASC(@"SEL$58A6D7F6" "PLPC"@"SEL$1" ("PRODUCTLISTPERCUSTOMER"."CUSTOMER_ID"

    "PRODUCTLISTPERCUSTOMER"."PRODUCT_ID" "PRODUCTLISTPERCUSTOMER"."SIZEDEFINITION_ID"

    "PRODUCTLISTPERCUSTOMER"."FINISHINGMETHOD_ID" "PRODUCTLISTPERCUSTOMER"."DATEACTIVE"))

    BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$58A6D7F6" "PLPC"@"SEL$1")

    LEADING(@"SEL$58A6D7F6" "P"@"SEL$1" "PLPC"@"SEL$1")

    USE_NL(@"SEL$58A6D7F6" "PLPC"@"SEL$1")

    END_OUTLINE_DATA

    */

    Peeked Binds (identified by position):

    --------------------------------------

    1 - (NUMBER): 415121

    2 - (NUMBER): 52335

    3 - (NUMBER): 623

    4 - (NUMBER): 1

    5 - (DATE): 04/16/2024 00:00:00

    6 - (DATE): 04/16/2024 00:00:00

    7 - (NUMBER): 1

    8 - (NUMBER): 2

    9 - (NUMBER): 4

    10 - (NUMBER): 3

    11 - (NUMBER): 3

    12 - (NUMBER): 3

    13 - (NUMBER): 2

    Predicate Information (identified by operation id):

    ---------------------------------------------------

    2 - filter(("P"."TYPEOFMERCHANDISE"=:7 OR "P"."TYPEOFMERCHANDISE"=:8))

    3 - access("P"."PRODUCT_ID"=:2)

    4 - filter(((("PLPC"."CHARGEWASHESONLYFORCOG"=:12 AND "PLPC"."PRICEUSAGE"<>:13 AND

    "PLPC"."BILLINGMETHOD"=:11) OR ("PLPC"."BILLINGMETHOD"=:9 AND "PLPC"."CHARGEWASHESONLYFORCOG"=:10)) AND

    "PLPC"."DATEINACTIVE">=:6))

    5 - access("PLPC"."CUSTOMER_ID"=:1 AND "PLPC"."PRODUCT_ID"=:2 AND "PLPC"."SIZEDEFINITION_ID"=:3 AND

    "PLPC"."FINISHINGMETHOD_ID"=:4 AND "PLPC"."DATEACTIVE"<=:5)

    Column Projection Information (identified by operation id):

    -----------------------------------------------------------

    3 - "P".ROWID[ROWID,10]

    5 - "PLPC".ROWID[ROWID,10]

    Note

    -----

    - Warning: basic plan statistics not available. These are only collected when:

    * hint 'gather_plan_statistics' is used for the statement or

    * parameter 'statistics_level' is set to 'ALL', at session or system level

## Historical Execution Plans

This section includes data captured by AWR. If this is a stand-by read-only database then the AWR information below is from the Primary database.

    SQL_ID 8tyvq2bp8xkfy
    --------------------
    select 1   from PRODUCTLISTPERCUSTOMER PLPC   join PRODUCT P     on
    P.PRODUCT_ID = PLPC.PRODUCT_ID  where PLPC.CUSTOMER_ID = :1     and
    PLPC.PRODUCT_ID = :2     and PLPC.SIZEDEFINITION_ID = :3   and
    PLPC.FINISHINGMETHOD_ID = :4     and PLPC.DATEACTIVE <= :5     and
    PLPC.DATEINACTIVE >= :6     and P.TYPEOFMERCHANDISE in (:7 , :8 )
    and ((PLPC.BILLINGMETHOD = :9  and PLPC.CHARGEWASHESONLYFORCOG = :10 )
    or        (PLPC.BILLINGMETHOD = :11  and PLPC.CHARGEWASHESONLYFORCOG =
    :12  and        PLPC.PRICEUSAGE <> :13 ) )

    Plan hash value: 3547044130

    --------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                            | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                     |                             |       |       |     6 (100)|          |
    |   1 |  NESTED LOOPS                        |                             |     1 |    51 |     6   (0)| 00:00:01 |
    |*  2 |   TABLE ACCESS BY INDEX ROWID        | PRODUCT                     |     1 |     8 |     3   (0)| 00:00:01 |
    |*  3 |    INDEX UNIQUE SCAN                 | XPKPRODUCT                  |     1 |       |     2   (0)| 00:00:01 |
    |*  4 |   TABLE ACCESS BY INDEX ROWID BATCHED| PRODUCTLISTPERCUSTOMER      |     1 |    43 |     3   (0)| 00:00:01 |
    |*  5 |    INDEX RANGE SCAN                  | XIF85PRODUCTLISTPERCUSTOMER |     1 |       |     2   (0)| 00:00:01 |
    --------------------------------------------------------------------------------------------------------------------

    Query Block Name / Object Alias (identified by operation id):
    -------------------------------------------------------------

       1 - SEL$58A6D7F6
       2 - SEL$58A6D7F6 / P@SEL$1
       3 - SEL$58A6D7F6 / P@SEL$1
       4 - SEL$58A6D7F6 / PLPC@SEL$1
       5 - SEL$58A6D7F6 / PLPC@SEL$1

    Outline Data
    -------------

      /*+
          BEGIN_OUTLINE_DATA
          IGNORE_OPTIM_EMBEDDED_HINTS
          OPTIMIZER_FEATURES_ENABLE('19.1.0')
          DB_VERSION('19.1.0')
          ALL_ROWS
          OUTLINE_LEAF(@"SEL$58A6D7F6")
          MERGE(@"SEL$1" >"SEL$2")
          OUTLINE(@"SEL$2")
          OUTLINE(@"SEL$1")
          INDEX_RS_ASC(@"SEL$58A6D7F6" "P"@"SEL$1" ("PRODUCT"."PRODUCT_ID"))
          INDEX_RS_ASC(@"SEL$58A6D7F6" "PLPC"@"SEL$1" ("PRODUCTLISTPERCUSTOMER"."SIZEDEFINITION_ID"))
          BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$58A6D7F6" "PLPC"@"SEL$1")
          LEADING(@"SEL$58A6D7F6" "P"@"SEL$1" "PLPC"@"SEL$1")
          USE_NL(@"SEL$58A6D7F6" "PLPC"@"SEL$1")
          END_OUTLINE_DATA
      */

    Peeked Binds (identified by position):
    --------------------------------------

       1 - :1 (NUMBER): 335188
       2 - :2 (NUMBER): 51233
       3 - :3 (NUMBER): 402
       4 - :4 (NUMBER): 1
       5 - :5 (DATE): 04/09/2024 00:00:00
       6 - :6 (DATE): 04/09/2024 00:00:00
       7 - :7 (NUMBER): 1
       8 - :8 (NUMBER): 2
       9 - :9 (NUMBER): 4
      10 - :10 (NUMBER): 3
      11 - :11 (NUMBER): 3
      12 - :12 (NUMBER): 3
      13 - :13 (NUMBER): 2

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

       2 - filter(("P"."TYPEOFMERCHANDISE"=:7 OR "P"."TYPEOFMERCHANDISE"=:8))
       3 - access("P"."PRODUCT_ID"=:2)
       4 - filter(("PLPC"."CUSTOMER_ID"=:1 AND "PLPC"."PRODUCT_ID"=:2 AND (("PLPC"."CHARGEWASHESONLYFORCOG"=:12
                  AND "PLPC"."PRICEUSAGE"<>:13 AND "PLPC"."BILLINGMETHOD"=:11) OR ("PLPC"."BILLINGMETHOD"=:9 AND
                  "PLPC"."CHARGEWASHESONLYFORCOG"=:10)) AND "PLPC"."DATEINACTIVE">=:6 AND "PLPC"."FINISHINGMETHOD_ID"=:4 AND
                  "PLPC"."DATEACTIVE"<=:5))
       5 - access("PLPC"."SIZEDEFINITION_ID"=:3)

1366133.1 SQLHC 23.1 2024-04-18/09:14:10