Improve The Performance For Computing The Resource Availability In ODS Loading Phase (Doc ID 2039640.1)

Last updated on JUNE 28, 2017

Applies to:

Oracle Advanced Supply Chain Planning - Version 12.1.3.9 and later
Information in this document applies to any platform.

Symptoms

While the Planning ODS Load Worker is running, the log file sync is frequently in the DB, and the entire system will seem to slow down.

Customer encountered the performance issue when computing the resource availability, the total wait time for "log file sync" was high in ODS loading.

From AWR in customer environment while the issue occurs, it seems "inserts into MSC_net_resource_availability" is strongly affects increase of the amount of generated REDO.

8t2zwn5hab0d6 INSERT INTO MSC_NET_RESOURCE_AVAIL( TRANSACTION_ID, PLAN_ID, ORGANIZATION_ID, SR_INSTANCE_ID,
DEPARTMENT_ID, RESOURCE_ID, SHIFT_NUM, SHIFT_DATE, FROM_TIME, TO_TIME, CAPACITY_UNITS, SIMULATION_SET,
AGGREGATE_RESOURCE_ID, STATUS, APPLIED, UPDATED, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,
CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE)
VALUES( MSC_NET_RESOURCE_AVAIL_S.NEXTVAL , -1 , :B14 , :B13 , :B12 , :B11 , 0 , :B1 , 0 , 24*60*60 ,
:B10 , :B9 , :B8 , NULL , NULL , 2 , :B2 , :B7 , :B2 , :B7 , :B6 , :B5 , :B4 , :B3 , :B2 )

SQL ordered by Executions
%CPU - CPU Time as a percentage of Elapsed Time
%IO - User I/O Time as a percentage of Elapsed Time
Total Executions: 1,062,591
Captured SQL account for 50.4% of Total
Executions Rows Processed Rows per Exec Elapsed Time (s) %CPU %IO SQL Id SQL Module SQL Text
149,529 67,168 0.45 2.38 102.6 .8 9tgj4g8y4rwy8 select type#, blocks, extents,...
102,067 102,067 1.00 5.41 100.9 0 2hxjvmag8hwbq MSCPDPW SELECT NVL(FND_PROFILE.VALUE('...
78,160 78,158 1.00 78.18 16.2 76 72k1x7nhk3jsy MSCPDCW UPDATE MSC_PROCESS_EFFECTIVITY...
59,483 59,483 1.00 8.91 64.3 34.9 ajd4f3805wh7z MSCPDCW INSERT INTO MSC_ITEM_SUPPLIERS...
36,524 36,524 1.00 3.28 82.4 17.4 573yaqmymbpp5 MSCPDC UPDATE MSC_CALENDAR_DATES SET ...
24,398 24,398 1.00 2.09 97.1 3 9n9z3c7hd8vus MSCPDCW INSERT INTO MSC_DEPARTMENT_RES...
24,382 6,388,084 262.00 285.06 60.8 1.6 8t2zwn5hab0d6 MSCPDCW INSERT INTO MSC_NET_RESOURCE_A...

Also, looking at the following statistics, seems to occur many update of the associated index block(not only table).

Segments by Physical Writes
Total Physical Writes: 1,519,894
Captured Segments account for 25.9% of Total
Owner Tablespace Name Object Name Subobject Name Obj. Type Physical Writes %Total
MSC APPS_TS_TX_IDX MSC_NET_RESOURCE_AVAIL_U2 NET_RESOURCE_AVAIL__1 INDEX PARTITION 81,663 5.37
MSC APPS_TS_TX_IDX MSC_NET_RESOURCE_AVAIL_N2 NET_RESOURCE_AVAIL__1 INDEX PARTITION 54,857 3.61
MSC APPS_TS_TX_IDX MSC_OPERATION_COMPONENTS_N1 INDEX 44,072 2.90
MSC APPS_TS_TX_IDX MSC_NET_RESOURCE_AVAIL_N1 NET_RESOURCE_AVAIL__1 INDEX PARTITION 39,486 2.60
MSC APPS_TS_TX_DATA MSC_NET_RESOURCE_AVAIL NET_RESOURCE_AVAIL__1 TABLE PARTITION 27,135 1.79

Segments by Physical Write Requests
Total Physical Write Requestss: 1,072,575
Captured Segments account for 25.5% of Total
Owner Tablespace Name Object Name Subobject Name Obj. Type Phys Write Requests %Total
MSC APPS_TS_TX_IDX MSC_NET_RESOURCE_AVAIL_U2 NET_RESOURCE_AVAIL__1 INDEX PARTITION 68,703 6.41
MSC APPS_TS_TX_IDX MSC_NET_RESOURCE_AVAIL_N2 NET_RESOURCE_AVAIL__1 INDEX PARTITION 45,907 4.28
MSC APPS_TS_TX_IDX MSC_OPERATION_COMPONENTS_N1 INDEX 33,223 3.10
MSC APPS_TS_TX_IDX MSC_NET_RESOURCE_AVAIL_N1 NET_RESOURCE_AVAIL__1 INDEX PARTITION 32,605 3.04
MSC APPS_TS_TX_DATA MSC_NET_RESOURCE_AVAIL NET_RESOURCE_AVAIL__1 TABLE PARTITION 22,265 2.08

Segments by DB Blocks Changes
% of Capture shows % of DB Block Changes for each top segment compared
with total DB Block Changes for all segments captured by the Snapshot
Owner Tablespace Name Object Name Subobject Name Obj. Type DB Block Changes % of Capture
MSC APPS_TS_TX_IDX MSC_NET_RESOURCE_AVAIL_U2 NET_RESOURCE_AVAIL__1 INDEX PARTITION 1,142,160 14.92
MSC APPS_TS_TX_DATA MSC_SR_ASSIGNMENTS TABLE 1,075,856 14.05
MSC APPS_TS_TX_IDX MSC_NET_RESOURCE_AVAIL_N2 NET_RESOURCE_AVAIL__1 INDEX PARTITION 763,392 9.97
MSC APPS_TS_TX_DATA MSC_NET_RESOURCE_AVAIL NET_RESOURCE_AVAIL__1 TABLE PARTITION 725,904 9.48
MSC APPS_TS_TX_IDX MSC_NET_RESOURCE_AVAIL_N1 NET_RESOURCE_AVAIL__1 INDEX PARTITION 664,848 8.68

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