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
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
it's a pleasure to meet you. I am USA Army personnel, i have an important thing to discus with you.
Please write me on my email (firstname.lastname@example.org)