EIM performance issue on "SELECT 'touch' ... FOR UPDATE" SQL due to full index scan (Doc ID 519254.1)

Last updated on NOVEMBER 14, 2016

Applies to:

Siebel Enterprise Integration Manager - Version 7.5.3.4 SIA [16180] and later
Oracle Solaris on SPARC (64-bit)
Product Release: V7 (Enterprise)
Version: 7.5.3.4 [16180] Com/Med
Database: Oracle 9.2.0.2
Application Server OS: Sun Solaris 8
Database Server OS: Sun Solaris 8

This document was previously published as Siebel SR 38-1532518671.


Symptoms

We have a problem with two similar SQL statements that are occurring for our EIM_ACCOUNT interfaces. I am confident that this issue is NOT anything to do with the EIM_ACCOUNT customisations that we have made to date.

In our Production 7.5.2 platform (Retail customers), this issue does not occur despite the exact statement being present and us running all our EIM interfaces in Cost Based Mode (alter session set optimizer_mode='ALL_ROWS').

This is essentially an issue with our Oracle configuration but I wondered whether Siebel Tech Support could leverage other customers' experience in resolving this.

The problem statements are:

SELECT 'touch'
FROM siebel.S_ORG_EXT
WHERE (ROW_ID IN
       (SELECT T_ORG_BU_ORG_ID
        FROM siebel.EIM_ACCOUNT
        WHERE (IF_ROW_BATCH_NUM = 3010
        AND   T_ORG_BU__EXS = 'N'
        AND   T_ORG_BU__STA = 0
        AND   T_ORG_BU__UNQ = 'Y'
        AND   IF_ROW_STAT_NUM = 0))) FOR UPDATE

SELECT 'touch'
FROM siebel.S_ORG_EXT
WHERE (ROW_ID IN
        (SELECT T_ACCNTPOST_OUEXTI
             FROM siebel.EIM_ACCOUNT
             WHERE (IF_ROW_BATCH_NUM = 3010
             AND   T_ACCNTPOST__EXS = 'N'
             AND   T_ACCNTPOST__STA = 0
             AND   T_ACCNTPOST__UNQ = 'Y'
             AND   IF_ROW_STAT_NUM = 0))) FOR UPDATE

The problem is that these are causing a Full Index Scan (FIS) against S_ORG_EXT_P1, which is not acceptable as we have about 20m rows in S_ORG_EXT and the FIS takes a few mins. This accounts for 60% and upwards of each batch's end-to-end runtime. We managed to reduce the number of Logical IOs from 30+m down to less than 100k by eliminating a hash join. The current plan is a Merge Join:

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHO...

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms