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