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

 

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值