"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
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 |