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

 

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	TRADEEXTEND	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	PVTRS_EOD	3	22	550	0	25	0	0:0:1	SEL$1 / D@SEL$1
INDEX SKIP SCAN	SYS_C00314828	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	SWAPTRADE_EOD	5	3,863	294.252K	0	5,911	0	0:1:11	SEL$1 / A@SEL$1
INDEX RANGE SCAN	SYS_C00314931	4	93,064	0	0	514	0	0:0:7	SEL$1 / A@SEL$1
TABLE ACCESS FULL	LOANFACILITY_EOD	6	95,855	4.114M	0	915	1	0:0:11	SEL$1 / B@SEL$1
TABLE ACCESS FULL	PTRSLIQUIDATION_EOD	8	4,654,086	142.031M	0	12,675	2	0:2:33	SEL$1 / E@SEL$1
TABLE ACCESS FULL	PTRSPOSITION_EOD	10	542,466	22.245M	0	2,750	1	0:0:33	SEL$1 / F@SEL$1
TABLE ACCESS FULL