RAC性能调优

时间：2014-06-10 07:16:30 阅读：433 评论：0 收藏：0 [点我收藏+]

结论：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	3352:3124	7364
133	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	27472
134	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	27472
135	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	21541
136	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	21541
137	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	16796
138	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	16796
139	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	17995
140	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	17995
141	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	1392
142	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	1392
143	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	11291
144	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	11291
145	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	23620
146	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	23620
147	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	31500
148	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	31500
149	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	10032
150	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	10032
151	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	30754
152	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	30754
153	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	22562
154	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	22562
155	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	2371
156	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	2371
157	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	28068
158	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	28068
159	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	20466
160	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	20466
161	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	25857
162	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	25857
163	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	2369
164	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	2369
165	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	21699
166	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	21699
167	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	11837
168	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	11837
169	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	20984
170	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	20984
171	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	2720
172	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	2720
173	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	23445
174	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	23445
175	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	8702
176	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	8702
177	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	14054
178	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	14054
179	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	28525
180	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	28525
181	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	12287
182	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	12287
183	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	23097
184	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	23097
185	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	17033
186	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	17033
187	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	24867
188	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	24867
189	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	9270
190	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	9270
191	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	23317
192	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	23317
193	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	16484
194	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	16484
195	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	26333
196	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	26333
197	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	19890
198	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	19890
199	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	18074
200	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	18074
201	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	8214
202	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	8214
203	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	23656
204	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	23656
205	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	1673
206	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	1673
207	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	24975
208	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	24975
209	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	30786
210	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	30786
211	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	30800
212	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	30800
213	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	1235
214	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	1235
215	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	16290
216	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	16290
217	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	2433
218	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	2433
219	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	682
220	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	682
221	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	3781
222	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	3781
223	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	11058
224	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	11058
225	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	786
226	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	786
227	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	2073
228	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	2073
229	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	859
230	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	859
231	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	27306
232	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	27306
233	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	771
234	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	771
235	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	20039
236	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	20039
237	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	646
238	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	646
239	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	1002
240	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	1002
241	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	642
242	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	642
243	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	24273
244	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	24273
245	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	613
246	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	613
247	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	21363
248	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	21363
249	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	611
250	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	611
251	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	795
252	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	795
253	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	607
254	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	607
255	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	21880
256	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	21880
257	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	605
258	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	605
259	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	16561
260	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	16561
261	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	603
262	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	603
263	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	779
264	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	779
265	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	601
266	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	601
267	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	624
268	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	624
269	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	597
270	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	597
271	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	617
272	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	617
273	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	593
274	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	593
275	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	615
276	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	615
277	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	589
278	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	589
279	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	609
280	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	609
281	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	581
282	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	581
283	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	599
284	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	599
285	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	573
286	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	573
287	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	595
288	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	595
289	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	565
290	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	565
291	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	585
292	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	585
293	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	563
294	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	563
295	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	577
296	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	577
297	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	561
298	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	561
299	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	569
300	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	569
301	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	559
302	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	559
303	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124	567
304	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124	567
305	HDTMP_DEPTODEPLINE	485	14642	Administrator	3352:3124
306	HDTMP_DEPTODEP	485	14642	Administrator	3352:3124

需要分析的schema

存在一个历史锁定。

无效的对象还有很多，建议及时处理。

select disk_reads,
       executions,
       disk_reads / decode(executions,0,1, executions) reads_per_exec,
       hash_value,
       sql_text
from v$sqlarea
where disk_reads > 10000
order by disk_reads desc;

select rows_processed,
       executions,
       rows_processed / decode(executions,0,1, executions) rows_per_exec,
       hash_value,
       sql_text
from   v$sqlarea
where rows_processed > 10000
order by rows_processed desc;

select buffer_gets, lpad(rows_processed ||
       decode(users_opening + users_executing, 0, ‘ ‘,‘*‘),20) "rows_processed",
       executions, loads,
      (decode(rows_processed,0,1,1))*buffer_gets/ decode(rows_processed,0,1,rows_processed) avg_cost,
       sql_text
from   v$sqlarea
where decode(rows_processed,0,1,1) * buffer_gets/ decode(rows_processed,0,1,rows_processed) > 10000
order by 5 desc;

SELECT dob.OBJECT_NAME Table_Name,lo.SESSION_ID,vss.SERIAL#,
       vss.action Action,vss.osuser OSUSER,
       vss.process AP_Process_ID,VPS.SPID DB_Process_ID
from v$locked_object lo, dba_objects dob, v$session vss, V$PROCESS VPS
where lo.OBJECT_ID = dob.OBJECT_ID
   and lo.SESSION_ID = vss.SID
   AND VSS.paddr = VPS.addr
order by 2,3,DOB.object_name;

SELECT /*+ rule */ event,segment_type,segment_name,file_id,block_id,blocks
FROM   dba_extents, gv$session_wait
WHERE p1text=‘file#‘
       AND p2text=‘block#‘
       AND p1=file_id and
       p2 between block_id AND block_id+blocks
ORDER BY segment_type,segment_name;

select *
from (
select event,sum(decode(wait_Time,0,0,1)) "Prev",
         sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Total"
   from v$session_Wait
   group by event
   order by 4 desc
)
where rownum<=10;

RAC性能调优,布布扣,bubuko.com

RAC性能调优

原文：http://blog.csdn.net/jaminwm/article/details/29591695

踩

(0)

评论一句话评论（0）

分享档案

更多>

2021年09月23日 (328)
2021年09月24日 (313)
2021年09月17日 (191)
2021年09月15日 (369)
2021年09月16日 (411)
2021年09月13日 (439)
2021年09月11日 (398)
2021年09月12日 (393)
2021年09月10日 (160)
2021年09月08日 (222)