Batch IB-SPDB runs very slowly (Doc ID 2073105.1)

Last updated on SEPTEMBER 28, 2022

Applies to:

Symptoms

On : 2.4.0.3.0 version,

ACTUAL BEHAVIOR
---------------
While running batch job IB-SPDB by online mode, it runs very slowing which costs several hours.

ADM reports shows this SQL Query runs a long time,

Query being executed:
SELECT  COUNT(                 *),
        MIN(CB1.REG_DATA_DTTM) - (:1 /1440)
FROM    CI_REG_DATA_SET CA1,
        CI_REG_DATA CB1
WHERE   CA1.REG_ID                = :2
        AND CA1.REG_DATA_SET_ID   = CB1.REG_DATA_SET_ID
        AND CA1.REG_DS_STATUS_FLG = 'C'
        AND CB1.REG_DATA_DTTM     > :3
        AND CB1.REG_DATA_DTTM    <= :4
        AND CA1.SET_DTTM BETWEEN :5 AND :6
        AND CA1.REG_DS_TYPE_FLG <> :7
        AND NOT EXISTS
        (
                SELECT 'x'
                FROM    CI_REG_DATA_SET CA11,
                        CI_REG_DATA CB11
                WHERE   CA11.REG_ID                = CA1.REG_ID
                        AND CB11.REG_DATA_DTTM     = CB1.REG_DATA_DTTM
                        AND CA11.REG_DATA_SET_ID   = CB11.REG_DATA_SET_ID
                        AND CA11.REG_DS_STATUS_FLG = 'C'
                        AND CA11.SET_DTTM         <= :8
                        AND CA11.SET_DTTM          > CA1.SET_DTTM
        )
        /* SQL for COBOL program 'CIPIICCN', SQL name 'S_CNTINTV' */



EXPECTED BEHAVIOR
-----------------------
Batch job IB-SPDB runs normally.

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.