1)
above sql did full tablescan
it executed 37 time
and took around 10 min
DELETE FROM PF_LIQUDATION_DETAILS_EOD WHERE PORTFOLIOID = :B2 AND ASOFDATE = :B1 |
We need to look at the indexes for these PF_* tables and add indexes so that we can reduce the EOD calculations time big time
2) Check the below SQL and it’s Execution Plan. This is what we use in calculating the “Sale Activity” tab in EOD Calculations. It’s doing lot of FULL TABLE SCANS which is not good at all.
We run this for 35+ portfolios and if we can fine tune this, it reduces our time big time
select a.swaptradeid, a.tradedate selltradedate, a.settledate sellsettledate, a.counterpartycd, a.quantity sellquantity, a.price sellprice, b.facilitydesc facilitytype, b.loanxid, b.facilitycusip, c.issuername, d.CURRPERIODEFFECTIVESTARTDATE, e.liquidationamount, e.realizedgain, f.positionid, f.tradedate buytradedate, f.settledate buysettledate, f.purchaseprice, f.commitmentamount, g.TRSSETTLEDDATE, a.tradeccy, d.currency, pkg_asset_eod.getfxrate(a.tradeccy, d.currency, f.tradedate) init_spotrate, pkg_asset_eod.getfxrate(a.tradeccy, d.currency, a.settledate) settle_spotrate, pkg_asset_eod.getlastfxrate(a.tradeccy, d.currency) latest_spotrate, pkg_asset_eod.getfxrate(a.tradeccy, d.currency, a.tradedate) sell_spotrate
from swaptrade_eod a, loanfacility_eod b, loandeal_eod c, pvtrs_eod d, ptrsliquidation_eod e, ptrsposition_eod f, TradeExtend g
where a.ptrsid = :1 and a.tradeside = 'S' and a.loanfacilityid = b.loanfacilityid and b.loandealid = c.loandealid and a.ptrsid = d.pvtrsid and a.swaptradeid = e.SELLTRADEID(+) and e.positionid = f.positionid (+) and a.closedate = b.closedate and a.closedate = c.closedate and a.closedate = d.closedate and a.closedate = e.closedate(+) and e.closedate = f.closedate(+) and a.closedate = pkg_eod_util.getlastclosedate() and a.LOANTRADEID = g.LOANTRADEID (+) order by a.tradedate asc, a.tradeside asc, a.lqtticketid asc, e.positionid asc
SELECT STATEMENT | 17 | 0 | 0 | 0 | 23,871 | 100 | 0:0:0 | ||
SORT ORDER BY | 16 | 12,266 | 3.217M | 3.438M | 23,871 | 1 | 0:4:47 | SEL$1 | |
HASH JOIN RIGHT OUTER | 15 | 12,266 | 3.217M | 0 | 23,141 | 1 | 0:4:38 | ||
TABLE ACCESS FULL | 1 | 54,511 | 851.734K | 0 | 112 | 2 | 0:0:2 | SEL$1 / G@SEL$1 | |
HASH JOIN | 14 | 12,266 | 3.03M | 0 | 23,027 | 1 | 0:4:37 | ||
TABLE ACCESS BY INDEX ROWID | 3 | 22 | 550 | 0 | 25 | 0 | 0:0:1 | SEL$1 / D@SEL$1 | |
INDEX SKIP SCAN | 2 | 22 | 0 | 0 | 19 | 0 | 0:0:1 | SEL$1 / D@SEL$1 | |
HASH JOIN | 13 | 12,267 | 2.738M | 2.469M | 23,002 | 1 | 0:4:37 | ||
HASH JOIN OUTER | 11 | 12,318 | 2.326M | 0 | 22,308 | 1 | 0:4:28 | ||
HASH JOIN OUTER | 9 | 12,318 | 1.821M | 0 | 19,552 | 1 | 0:3:55 | ||
HASH JOIN | 7 | 3,863 | 464.013K | 0 | 6,828 | 0 | 0:1:22 | ||
TABLE ACCESS BY INDEX ROWID | 5 | 3,863 | 294.252K | 0 | 5,911 | 0 | 0:1:11 | SEL$1 / A@SEL$1 | |
INDEX RANGE SCAN | 4 | 93,064 | 0 | 0 | 514 | 0 | 0:0:7 | SEL$1 / A@SEL$1 | |
TABLE ACCESS FULL | 6 | 95,855 | 4.114M | 0 | 915 | 1 | 0:0:11 | SEL$1 / B@SEL$1 | |
TABLE ACCESS FULL | 8 | 4,654,086 | 142.031M | 0 | 12,675 | 2 | 0:2:33 | SEL$1 / E@SEL$1 | |
TABLE ACCESS FULL | 10 | 542,466 | 22.245M | 0 | 2,750 | 1 | 0:0:33 | SEL$1 / F@SEL$1 | |
TABLE ACCESS FULL |
标签:eod,变慢,tradedate,查询,ACCESS,pkg,oracle,closedate,SEL From: https://blog.51cto.com/u_16174476/6612704