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

Views: 245

Reply to This

Replies to This Discussion

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

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 (captkristen899@gmail.com)

RSS

PeopleSoft Jobs in US

Videos

  • Add Videos
  • View All

© 2021   Created by Maisam Agha.   Powered by

Badges  |  Report an Issue  |  Terms of Service