RAC性能调优

结论：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