"ORA-01555: snapshot too old" Error or Slow Performance of Initial Query When Running D1-GNIMD Batch (Doc ID 2146805.1)

Last updated on JUNE 07, 2016

Applies to:

Oracle Utilities Meter Data Management - Version 2.1.0.2 to 2.1.0.3 [Release 2.1]
Information in this document applies to any platform.

Symptoms

On : 2.1.0.2 version, Database

ACTUAL BEHAVIOR
---------------
Performance issue on OUT OF BOX SQL for D1-GNIMD

SQL:

select distinct MO.INIT_MSRMT_DATA_ID as ID from D1_INIT_MSRMT_DATA MO, F1_BUS_OBJ BO, F1_BUS_OBJ_STATUS BOS, F1_LCBO_AUTO_ALG_VW AALG where MO.BUS_OBJ_CD != ' ' and MO.BUS_OBJ_CD= BO.BUS_OBJ_CD and MO.BO_STATUS_CD = BOS.BO_STATUS_CD and BO.LIFE_CYCLE_BO_CD = BOS.BUS_OBJ_CD and BO.LIFE_CYCLE_BO_CD = AALG.LIFE_CYCLE_BO_CD and BOS.BO_STATUS_CD = AALG.BO_STATUS_CD and ( BOS.BATCH_CD = :1 ) and MO.INIT_MSRMT_DATA_ID between :2 and :3 order by ID /* com.splwg.base.domain.common.businessObject.batch.AutoTransitionBatchProcess$AutoTransitionBatchProcessWorker */

This took way too long (over 1.5 hours) and failed with error as below.

ERROR
-----------------------
Caused by: java.sql.SQLException: ORA-01555: snapshot too old: rollback segment number 66 with name "_SYSSMU66_390298999$" too small.

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Run D1-GNIMD
2. Notice performance issue returning above query before batch begins
3. See error as above

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot run D1-GNIMD at all.

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