Calculation Performance Due to Custom Function (Doc ID 2588173.1)

Last updated on MARCH 18, 2024

Applies to:

Symptoms

Review Calculation performance and noticed that the long running sql statement is from the custom plsql function.

AWR (SQL Ordered by Elapsed Time) shows :
Elapsed                         Elapsed Time
Time(s)      Executions     per Exec(s)      SQL Id                    SQL Text                                      
67,462.43    435,740          0.15            bqthqq55bagm2       SELECT SRPG.TARGET_PER , SRPG....
43,203.91      57,110          0.76            ftswy67z9y1k5         begin ADXX_CN_FORMULA_17035_PK...

The sql_id bqthqq55bagm2 is taking 0.15 second per execution.

Full SQL
SELECT SRPG.TARGET_PER , SRPG.ALT_TARGET_1_PER , SRPG.ALT_TARGET_2_PER , SRPG.ALT_TARGET_3_PER , SRPG.ALT_TARGET_4_PER , SRPG.ALT_TARGET_5_PER
FROM CN_PLAN_COMPONENTS_ALL_B PC , CN_SRP_PARTICIPANTS_ALL SRP , CN_SRP_PLAN_COMPONENTS_ALL SPC , CN_SRP_PER_GOALS_ALL SRPG , CN_FORMULAS_ALL_B F
WHERE 1=1
AND SRP.PARTY_NUMBER = :B4
AND PC.ATTRIBUTE1 = :B3
AND F.ATTRIBUTE1 = :B2
AND SPC.PARTICIPANT_ID = SRP.PARTICIPANT_ID
AND SPC.PLAN_COMPONENT_ID = PC.PLAN_COMPONENT_ID
AND SRPG.SRP_COMP_PLAN_ID = SPC.SRP_COMP_PLAN_ID
AND SRPG.PLAN_COMPONENT_ID = SPC.PLAN_COMPONENT_ID
AND SRPG.FORMULA_ID = F.FORMULA_ID
AND SRPG.PERIOD_ID = :B1

Elapsed Time (sec) 0.21
CPU Time (sec)      0.19
Buffer Gets           9,207

Cause

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