search cancel

Performance Issue - Financial management domain

book

Article ID: 254488

calendar_today

Updated On:

Products

Clarity PPM On Premise Clarity PPM SaaS

Issue/Introduction

When the Financial Management Domain is used to create an Adhoc report, if the SQL query that gets formed (due to the columns that get selected), contains both dwh_fin_plan_virtual_v and dwh_fin_plan_virtual_sum_v DWH views in it, then it is noticed that the query execution is slower in Oracle 19c than in Oracle 12c. An additional constraint to observe this issue is that the number of records returned by the query is at least greater than 1000.

Environment

Database: Oracle 19c

Resolution

There have been no changes in the views mentioned above. The DB has been noted to behave differently in how the query is executed by it.

Following approaches can be taken:

  • Custom report can be created, by tuning the query that runs on the DB
  • NO_QUERY_TRANSFORMATION hint can be used in the custom query
  • A custom domain can be created, with only those tables and columns that are required