My Oracle Support Banner

[MDM V2.2sp3 Upgrade]How To Reduce Processing Time For MDM_Pre_Upgrede.sql (Doc ID 2587444.1)

Last updated on OCTOBER 24, 2023

Applies to:

Oracle Utilities Meter Data Management - Version 2.2.0.3.0 and later
Information in this document applies to any platform.

Goal

On : 2.2.0.3.0 version, Database

[MDM v2.2sp3 Upgrade]How to reduce processing time for MDM_Pre_Upgrede.sql

D1_INIT_MSRMT_DATA: There are more than 15,500,000,000,000 pieces and it is estimated to take more than 80 minutes for processing even with the hints for a parallel execution. This means that the processing will not be finished within the server replacement period. We would like to know how to reduce the processing time for MDM_Pre_Upgrede.sql (target: 30 min.)
Attachment:MDM_Pre_Upgrade.sql(provided by Product)


Plan 1.Divide MDM_PreUpgrade.sql and run a parallel execution.
 MDM_PreUpgrade.sql is to be divided as shown below.
 B is divided into 8, and executed in parallel.
 SQL for C is performed after all the SQLs for the divided B are complete.

  A. CREATE TABLE D1_IMD_CTRL_SEEDER
  B. SELECT - INSERT (Sub partition key for D1_INIT_MSRMT_DATA: divided with IMD.MEASR_COMP_ID)
  COMMIT for each divided SQLs.
  C. DELETE D1_IMD_CTRL (duplicated IMD_ID with D1_IMD_CTRL_SEEDER to be removed) +COMMIT
Attachment:PreUpgrade_SQL8.zip(SQL for Plan 1)


Plan 2. No insert for D1_IMD_CTRL_SEEDER
 Because the "D1_GNMD/D1_IMD" batch is not used, no insert processing is done for D1_IMD_CTRL_SEEDER,
 and CREATE TABLE D1_IMD_CTRL_SEEDERとDELETE D1_IMD_CTRL is run.
 The condition for deletion for D1_IMD_CTR is BUS_OBJ_CD = 'D1-IMDSeeder' AND BO_STATUS_CD = 'ERROR'.
Attachment:MDM_Pre_Upgrade-2.sql
 

Solution

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
Goal
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.