Allocation Rule on Management Ledger Performance Issue at Select Distinct ISO_CURRENCY_CD (Doc ID 2886121.1)

Last updated on OCTOBER 12, 2022

Applies to:

Symptoms

On PFT 8.1.0.1.2 version, user has about 80 million records in fsi_d_management_ledger for all the dates. When running any allocation rule, found query below that takes time to complete:

  SELECT DISTINCT ISO_CURRENCY_CD
  FROM FSI_D_MANAGEMENT_LEDGER
  WHERE ISO_CURRENCY_CD <> '002'
  AND ISO_CURRENCY_CD <> 'USD';

The query does not have as_of_date as filter due to which no partition pruning happening.


Steps To Replicate:
1. User has over 1000 allocation rules to run on management ledger monthly. This takes 14 hours to complete.
2. During 14 hour, the above SQL was executed approximately 676 times, it takes 15.17 seconds each with total time of 2.8 hour.
    User is looking for ways to reduce the monthly processing time.

Changes

Cause

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