Connie_Fu

Performance Problem On Project Pages

Discussion created by Connie_Fu on Sep 13, 2012
Latest reply on Aug 21, 2013 by katiyar
A new KB TEC578149 has been published with details on the following issue discovered on some of the recent patches and release. Here is a quick summary:

CLRT-69140 "Slow performance caused by new view inv_wip_cost_v"

Affects Version/s:
13.0.1.2, 13.0.1.3, 13.0.1.4, 13.1 (base)

Symptoms: Some of the project pages are much slower than prior to the upgrade or patch application, these pages include My Projects portlet, Project List view, Project Team page, Project Task page

The readme file for 13.0.1.4 includes CLRT-69140, new indices were introduced to improve the performance of a new view. However the performance was improved from getting time out errora to still a considerable amount of time. We are fixing it further in 13.0.1.5 and 13.1.0.1 (first generic patch for 13.1) by taking out the view.

If you are on one of the affected version listed above and for any reason cannot apply/wait for these new patches, use the following workaround, it will nullify a view which does not affect users in any way, it is a view added for two private (not for customer use) attributes and will be taken out in future releases

1. Backup the existing view INV_WIP_COST_V
2. Recreate the view:

CREATE OR REPLACE VIEW INV_WIP_COST_V
AS
SELECT /* + index( b PPA_WIP_VALUES_U1) index(a PPA_WIP_TRANSNO_STATUS_INV) */ a.investment_id investment_id,
SUM(NVL(b.totalcost, 0)) totalcost
FROM
ppa_wip a,
ppa_wip_values b
WHERE
a.transno = b.transno AND
b.currency_type = 'HOME'AND
a.status = 0 and 0=1
GROUP BY
a.investment_id

Regards
Connie Fu

Outcomes