PeopleSoft Community Network
This query used to run fast but lately has timed out often. Does anyone have any suggestions as to ways to improve the query run time? Thanks.
SELECT A.BUSINESS_UNIT, A.ACCOUNT, A.N_STAT_PROD, A.PRODUCT, A.N_DISTR, SUM( A.POSTED_TOTAL_AMT), B.DESCR, A.N_REINS, A.N_ACCT_BOOK, A.N_SAID, TO_CHAR(:1) || 'THRU' || TO_CHAR(:2), A.FISCAL_YEAR, A.LEDGER, /*+ index(a, PSBLEDGER)*/ A.BUSINESS_UNIT ,B.SETID,B.ACCOUNT,TO_CHAR(B.EFFDT,'YYYY-MM-DD')
FROM PS_LEDGER A, PS_SP_BU_GL_CLSVW A1, PS_GL_ACCOUNT_TBL B
WHERE A.BUSINESS_UNIT = A1.BUSINESS_UNIT
AND A1.OPRCLASS = 'NWALLPAGES_ALLBUS'
AND ( A.FISCAL_YEAR = :3
AND A.LEDGER = 'ACTUAL'
AND A.BUSINESS_UNIT IN ('20101','20103')
AND A.ACCOUNT BETWEEN '00000000' AND '99999999'
AND A.ACCOUNTING_PERIOD BETWEEN :1 AND :2
AND A.ACCOUNT = B.ACCOUNT
AND B.EFFDT =
(SELECT MAX(B_ED.EFFDT) FROM PS_GL_ACCOUNT_TBL B_ED
WHERE B.SETID = B_ED.SETID
AND B.ACCOUNT = B_ED.ACCOUNT
AND B_ED.EFFDT <= SYSDATE)
AND ( A.PRODUCT BETWEEN 'B41300' AND 'B41370'
OR A.PRODUCT BETWEEN 'B44300' AND 'B44370')
AND A.CURRENCY_CD = 'USD'
AND B.SETID <> 'NANCY'
AND B.ACCOUNT BETWEEN '00000000' AND '99999999' )
GROUP BY A.BUSINESS_UNIT, A.ACCOUNT, A.N_STAT_PROD, A.PRODUCT, A.N_DISTR, B.DESCR, A.N_REINS, A.N_ACCT_BOOK, A.N_SAID, TO_CHAR(:1) || 'THRU' || TO_CHAR(:2), A.FISCAL_YEAR, A.LEDGER, /*+ index(a, PSBLEDGER)*/ A.BUSINESS_UNIT ,B.SETID,B.ACCOUNT,TO_CHAR(B.EFFDT,'YYYY-MM-DD')
ORDER BY 2, 1, 9, 4, 5, 8 DESC
Tags:
Hi,
You can give try by adding custom extra index on the record field(s).
If chosen properly, it works great.
I am not sure if you want to add index to delivered tables though.
In that case you might have to use a view and create index on it.
This one is tricky. Without actually seeing your plan i cannot really comment here. But assuming that they volume of data retrieved has been consistent over a period of time, but the query has slowed down, then it could be a DB issue with Temp table space which is the bottle neck. Maybe you can have your DBA team review it
© 2024 Created by Maisam Agha. Powered by