ODI Agent Session Status Query Impacts Database Performance Due To Missing Indexes In ODI 11g (Doc ID 1681972.1)

Last updated on MAY 06, 2021

Applies to:

Symptoms

After observing poor performance when executing Oracle Data Integrator (ODI) 11g Scenarios on an Agent, an auditing of the SQL instructions executed at runtime has been performed, and the following SQL instruction has been identified:

• To be executed very frequently... and
• Indicate the performing of full table scans...

.. therefore resulting in inefficient processing and poor performance.

The SQL command ODI 11g executes is:

select count(*) RUNNING_SESSIONS
from WORK_REP.SNP_SESSION where SESS_STATUS = :1 and AGENT_NAME = :2

The SQL explain plan states the following:

TABLE ACCESS FULL - (operation) | SNP_SESSION - (name) | 32217 - (rows) | 597K - (bytes) | 4403 (1) - (%CPU) | 00:00:53 (time)

Cause

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