D1-PSACC Running Too Slow (Doc ID 2648442.1)

Last updated on NOVEMBER 14, 2023

Applies to:

Goal

On : 2.6.0.1.0 version, Framework

D1-PSACC running too slow

It appears that this job is using a full table explain plan rather than an index scan. You noticed this job is running a few select queries in the background that is using a full table explain plan which is causing the job to run slowly.

SELECT IMD.BO_STATUS_CD, IMD.BUS_OBJ_CD, COUNT (*) AS CNT
  FROM CISADM.D1_INIT_MSRMT_DATA IMD
  WHERE IMD.IMD_EXT_ID = :1
GROUP BY IMD.BO_STATUS_CD, IMD.BUS_OBJ_CD
ORDER BY IMD.BO_STATUS_CD, IMD.BUS_OBJ_CD /* SQL for Zone 'D1-ACCSTATS' */
  /* com.splwg.base.web.dataExplorer.common.DataExplorerSQLExecuter */

If you notice it is using a count(*) which is causing the explain plan to use Table access INMEMORY full. the D1_INIT_MSRMT_DATA table currently has 24 million records.
 

Solution

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