Slow Performance of SQL To Create Temp Table for Management Ledger Allocations (Doc ID 3051207.1)

Last updated on NOVEMBER 27, 2024

Applies to:

Symptoms

In PFT 8.1.1, when running a production batch, several Management Ledger Allocations are running slower than usual.  They usually finish in 1-2 minutes and now run much longer.

Reviewing the timestamps in the Allocation Engine log from /<ftpshare>/logs/<as of date>/infodom/Allocation Engine, the "merge into" queries finish quickly but the total Allocation time is much longer.

Example:

Allocation Starts:
Module Logging OFS Access module data: :::: Starting Engine prepare at 09/ 29/ 2023 08:59:25 ::::

First Merge Query:
Module Logging OFS Access module data: MERGE QUERY START TIME :::: 09/ 29/ 2023 09:50:54
Module Logging OFS Access module data: MERGE QUERY END TIME :::: 09/ 29/ 2023 09:51:22

Second Merge Query:
Module Logging OFS Access module data: MERGE QUERY START TIME :::: 09/ 29/ 2023 09:51:22
Module Logging OFS Access module data: MERGE QUERY END TIME :::: 09/ 29/ 2023 09:51:48

Allocation Ends:
Module Logging OFS Access module data: :::: Exiting engine 09/ 29/ 2023 09:51:48 ::::

In the example above, the merge queries finish in less than a minute but there is 50 minutes between the Allocation start and the first merge query. 

This indicates the SQL to create the Global Temporary Table (GTT) is running a lot longer than usual.

Cause

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