当前位置:Gxlcms > mysql > RAC性能调优

RAC性能调优

时间:2021-07-01 10:21:17 帮助过:43人阅读

结论:RAC存在故障,当前RAC运行为RAC1节点上,负载均衡没有起到作用。集成商提供的ASM的帐号密码不正确,RAC运行存在大量SGA/PGA的死锁,而且session存在严重等待。部分SQL语句需要进行优化。 配置问题 1、 RAC 的ASM权限不足 2、 RAC1支撑运行,RAC2状态正

结论:RAC存在故障,当前RAC运行为RAC1节点上,负载均衡没有起到作用。集成商提供的ASM的帐号密码不正确,RAC运行存在大量SGA/PGA的死锁,而且session存在严重等待。部分SQL语句需要进行优化。

配置问题

1、 RAC 的ASM权限不足

2、 RAC1支撑运行,RAC2状态正常,但RAC2无法进行负载。

3、 死锁严重,这是造成性能慢低下的表现原因。

4、 操作系统的IO和内存利用效果不好,需要重新优化SGA,PGA。磁盘性能低下。

5、 存在session等待。

6、 无效对象较多

7、 部分业务执行语句需要进行SQL调优。

8、 针对HD40的schema进行索引分析,增强性能。当前schema未进行索引分析

解决方案

1、 申请计划内停机时间进行数据备份、RAC故障调整。由于没有ASM权限,系统无法完成磁盘IO的不停机调整。

2、 重新实施RAC。

3、 共享存储优化。

4、 配置合理的SGA/PGA。解决session问题。

5、 配置schema的索引分析

6、 优化核心SQL业务代码

证据

物理读的SQL最慢的语句

declare vbdate date; begin delete from hdtmp_card_deptodep; delete from hdtmp_deptodep; delete from hdtmp_deptodepline; insert into hdtmp_deptodep select * from deptodep; insert into hdtmp_deptodepline select * fromdeptodepline; commit; insert intohdtmp_card_deptodep(V_NUM,STARTERCODE,STARTERNAME,RECDEPCODE,RECDEPNAME, FLINE, i_status,LASTMODIFYTIME,LASTMODIFYDATE,V_BOX_CODE,N_CARD_QTY) select A.V_NUM 领出单号,A.STARTERCODE发送单位代码,A.STARTERNAME 发送单位名称,A.RECDEPCODE 收货单位代码,A.RECDEPNAME 收货单位名称--,A.RECSTORECODE,A.RECSTORENAME ,FLINE 物流线路 ,decode(A.i_status,'ABORTED','已作废','AUDITED','已审核','UNAUDITED','未审核','未知') 单据状态 ,A.LASTMODIFYTIME 状态时间,trunc(A.LASTMODIFYTIME)领出日期 ,B.V_BOX_CODE 盒号,B.N_CARD_QTY盒内卡总数量 from hdtmp_deptodep A,hdtmp_deptodepline B,v_tserialstore c where a.I_RECEIVEFLAG = 'GET' and a.recstorecode is null and A.RECDE

INSERT INTOHDTMP_CARD_DEPTODEP(V_NUM,STARTERCODE,STARTERNAME,RECDEPCODE,RECDEPNAME, FLINE,I_STATUS,LASTMODIFYTIME,LASTMODIFYDATE,V_BOX_CODE,N_CARD_QTY) SELECT A.V_NUM 领出单号,A.STARTERCODE发送单位代码,A.STARTERNAME 发送单位名称,A.RECDEPCODE 收货单位代码,A.RECDEPNAME 收货单位名称 ,FLINE 物流线路 ,DECODE(A.I_STATUS,'ABORTED','已作废','AUDITED','已审核','UNAUDITED','未审核','未知') 单据状态,A.LASTMODIFYTIME 状态时间,TRUNC(A.LASTMODIFYTIME) 领出日期 ,B.V_BOX_CODE 盒号,B.N_CARD_QTY盒内卡总数量 FROM HDTMP_DEPTODEP A, HDTMP_DEPTODEPLINE B,V_TSERIALSTORE C WHEREA.I_RECEIVEFLAG = 'GET' AND A.RECSTORECODE IS NULL AND A.RECDEPCODE <>'1111' AND A.I_STATUS = 'AUDITED' AND A.UUID = B.V_DEPTODEP_NUM ANDA.RECDEPCODE = C.FSTORE ORDER BY C.FLINE,A.RECDEPCODE,B.V_BOX_CODE

行处理最慢的语句

其中:

BEGIN :Result :="PWM_ALCJOB"."GENBILL" (piJobId => :piJobId,piBin =>:piBin,piOperGid => :piOperGid,poErrMsg => :poErrMsg); END;

SELECT /*+rule*/ COUNT(*) ,COUNT(Mst.Num)Num from TALCDIFF mst, ModuleStat ms,TAlcJob aj, TStore ts, (selectdistinct FWrhCode, FWrhName From TWrhZone) wz where mst.Stat = ms.No(+) andmst.fjobid = aj.fjobid(+) and mst.FStoreGid = ts.FGid(+) and mst.FWrh = wz.FWrhCode(+) and mst.NUM Like '999914%' and ts.FCodein (select FCLIENT FromTOWNERCLIENT_STORE whereFOwnerStoreCode <> '-' andFOwnerStoreCode like 'C01%') and mst.STAT = 0

平均成本最高

select T.pickarea1 拣货分区,nvl(T.sumrealqty,0)已拣件数,NVL(K.sumuqty,0) 未拣件数,(nvl(T.sumrealqty,0)+NVL(K.sumuqty,0))总件数 from(select a.fpickarea pickarea1,trunc(sum(b.frealqty/b.fqpc))sumrealqty fromtpickup a,tpickupdtl b where a.num=b.numand a.fpickmethod<>'手工单据' and a.fjobid >TO_CHAR(SYSDATE-1, 'YYMMDD%') andfpicktime> trunc(sysdate) group bya.fpickarea order by A.fpickarea) T, (select a.fpickareapickarea2,trunc(sum(b.fqty/b.fqpc)) sumuqty from tpickup a,tpickupdtl b where a.num=b.num and a.fpickmethod<>'手工单据' and a.fjobid > TO_CHAR(SYSDATE-1,'YYMMDD%') and fpicktime is null group by a.fpickarea order by A.fpickarea)K WHERE T.pickarea1=K.pickarea2(+)order by T.pickarea1

等待的部分,其中等待最多的是59个等待,这些等待都是由session和死锁造成的

死锁找到集中点了,是ID为243的死锁

详细结果集:

1

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

23321

2

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

20014

3

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

22831

4

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

14715

5

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

22583

6

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

17820

7

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

14517

8

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

27236

9

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

22458

10

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

14927

11

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

30770

12

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

13967

13

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

27262

14

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

15464

15

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

7364

16

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

27472

17

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

21541

18

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

16796

19

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

17995

20

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

1392

21

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

11291

22

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

23620

23

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

31500

24

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

10032

25

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

30754

26

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

22562

27

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

2371

28

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

28068

29

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

20466

30

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

25857

31

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

2369

32

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

21699

33

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

11837

34

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

20984

35

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

2720

36

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

23445

37

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

8702

38

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

14054

39

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

28525

40

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

12287

41

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

23097

42

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

17033

43

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

24867

44

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

9270

45

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

23317

46

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

16484

47

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

26333

48

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

19890

49

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

18074

50

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

8214

51

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

23656

52

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

1673

53

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

24975

54

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

30786

55

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

30800

56

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

1235

57

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

16290

58

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

2433

59

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

682

60

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

3781

61

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

11058

62

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

786

63

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

2073

64

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

859

65

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

27306

66

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

771

67

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

20039

68

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

646

69

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

1002

70

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

642

71

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

24273

72

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

613

73

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

21363

74

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

611

75

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

795

76

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

607

77

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

21880

78

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

605

79

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

16561

80

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

603

81

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

779

82

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

601

83

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

624

84

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

597

85

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

617

86

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

593

87

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

615

88

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

589

89

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

609

90

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

581

91

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

599

92

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

573

93

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

595

94

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

565

95

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

585

96

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

563

97

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

577

98

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

561

99

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

569

100

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

559

101

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

567

102

HDTMP_DEPTODEP

243

1175

Administrator

592:4392

103

HDTMP_DEPTODEPLINE

485

14642

Administrator

3352:3124

23321

104

HDTMP_DEPTODEP

485

14642

Administrator

3352:3124

23321

105

HDTMP_DEPTODEPLINE

485

14642

Administrator

3352:3124

20014

106

HDTMP_DEPTODEP

485

14642

Administrator

3352:3124

20014

107

HDTMP_DEPTODEPLINE

485

14642

Administrator

3352:3124

22831

108

HDTMP_DEPTODEP

485

14642

Administrator

3352:3124

22831

109

HDTMP_DEPTODEPLINE

485

14642

Administrator

3352:3124

14715

110

HDTMP_DEPTODEP

485

14642

Administrator

3352:3124

14715

111

HDTMP_DEPTODEPLINE

485

14642

Administrator

3352:3124

22583

112

HDTMP_DEPTODEP

485

14642

Administrator

3352:3124

22583

113

HDTMP_DEPTODEPLINE

485

14642

Administrator

3352:3124

17820

114

HDTMP_DEPTODEP

485

14642

Administrator

3352:3124

17820

115

HDTMP_DEPTODEPLINE

485

14642

Administrator

3352:3124

14517

116

HDTMP_DEPTODEP

485

14642

Administrator

3352:3124

14517

117

HDTMP_DEPTODEPLINE

485

14642

Administrator

3352:3124

27236

118

HDTMP_DEPTODEP

485

14642

Administrator

3352:3124

27236

119

HDTMP_DEPTODEPLINE

485

14642

Administrator

3352:3124

22458

120

HDTMP_DEPTODEP

485

14642

Administrator

3352:3124

22458

121

HDTMP_DEPTODEPLINE

485

14642

Administrator

3352:3124

14927

122

HDTMP_DEPTODEP

485

14642

Administrator

3352:3124

14927

123

HDTMP_DEPTODEPLINE

485

14642

Administrator

3352:3124

30770

124

HDTMP_DEPTODEP

485

14642

Administrator

3352:3124

30770

125

HDTMP_DEPTODEPLINE

485

14642

Administrator

3352:3124

13967

126

HDTMP_DEPTODEP

485

14642

Administrator

3352:3124

13967

127

HDTMP_DEPTODEPLINE

485

14642

Administrator

3352:3124

27262

128

HDTMP_DEPTODEP

485

14642

Administrator

3352:3124

27262

129

HDTMP_DEPTODEPLINE

485

14642

Administrator

3352:3124

15464

130

HDTMP_DEPTODEP

485

14642

Administrator

3352:3124

15464

131

HDTMP_DEPTODEPLINE

485

14642

Administrator

3352:3124

7364

132

HDTMP_DEPTODEP

485

14642

Administrator

人气教程排行