oracle 表查询变慢的原因－项目

1)     

 above sql did full tablescan

it executed 37 time

and took around 10 min

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