[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 laterInformation 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 |