My Oracle Support Banner

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

Last updated on MAY 21, 2024

Applies to:

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

Symptoms

**Disclaimer:** This KM article may include the following abbreviations:

MDM - Oracle Utilities Meter Data Management
SQL - Standard Query Language

 

On MDM v2.1.0.2

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.

Changes

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution
References

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