@H_502_5@
@H_502_5@
ASH结合AWR实战解决oracle高负载@H_502_5@
@H_502_5@
线上oracle数据库负载比较高，所以需要优化，一般进去看负载高的进程，如果是oracle进程，那么就是oracle运行不健康出异常了，我们通常可以用ash来分析问题，如果ash还无法解决，我们就需要扩大分析范围，需要采用awr分析日志来进行分析。@H_502_5@
@H_502_5@
@H_502_5@
1、ASH分析日志分析Events@H_502_5@
Top sql with Top Events@H_502_5@
Planhash@H_502_5@ |
Sampled # of Executions@H_502_5@ |
% Activity@H_502_5@ |
Event@H_502_5@ |
% Event@H_502_5@ |
Top Row Source@H_502_5@ |
% RwSrc@H_502_5@ |
||
2xg4n5sj1sx5h@H_502_5@ |
2047223008@H_502_5@ |
5066@H_502_5@ |
11.18@H_502_5@ |
latch: cache buffers chains@H_502_5@ |
5.88@H_502_5@ |
TABLE ACCESS - FULL@H_502_5@ |
2.34@H_502_5@ |
select * from (select a.bis_pr...@H_502_5@ |
@H_502_5@ |
@H_502_5@ |
@H_502_5@ |
@H_502_5@ |
5.24@H_502_5@ |
TABLE ACCESS - FULL@H_502_5@ |
2.35@H_502_5@ |
||
19gkjf4r3zkpw@H_502_5@ |
2939541073@H_502_5@ |
1013@H_502_5@ |
2.23@H_502_5@ |
latch: cache buffers chains@H_502_5@ |
1.27@H_502_5@ |
TABLE ACCESS - FULL@H_502_5@ |
1.00@H_502_5@ |
select round(sum(t.current_fac...@H_502_5@ |
6qhrc4ax6zr1a@H_502_5@ |
2939541073@H_502_5@ |
960@H_502_5@ |
2.12@H_502_5@ |
latch: cache buffers chains@H_502_5@ |
1.21@H_502_5@ |
TABLE ACCESS - FULL@H_502_5@ |
0.96@H_502_5@ |
select round(sum(t.current_fac...@H_502_5@ |
6nmdjjxfv7aqp@H_502_5@ |
2939541073@H_502_5@ |
847@H_502_5@ |
1.87@H_502_5@ |
latch: cache buffers chains@H_502_5@ |
1.03@H_502_5@ |
TABLE ACCESS - FULL@H_502_5@ |
0.78@H_502_5@ |
select round(sum(t.current_fac...@H_502_5@ |
1qq2h1qkxkaha@H_502_5@ |
2939541073@H_502_5@ |
776@H_502_5@ |
1.71@H_502_5@ |
latch: cache buffers chains@H_502_5@ |
1.02@H_502_5@ |
TABLE ACCESS - FULL@H_502_5@ |
0.83@H_502_5@ |
select round(sum(t.current_fac...@H_502_5@ |
@H_502_5@
Top sql with Top Row Sources@H_502_5@
PlanHash@H_502_5@ |
Sampled # of Executions@H_502_5@ |
% Activity@H_502_5@ |
Row Source@H_502_5@ |
% RwSrc@H_502_5@ |
Top Event@H_502_5@ |
% Event@H_502_5@ |
||
2xg4n5sj1sx5h@H_502_5@ |
2047223008@H_502_5@ |
5066@H_502_5@ |
11.18@H_502_5@ |
TABLE ACCESS - FULL@H_502_5@ |
4.70@H_502_5@ |
2.35@H_502_5@ |
select * from (select a.bis_pr...@H_502_5@ |
|
@H_502_5@ |
@H_502_5@ |
@H_502_5@ |
@H_502_5@ |
TABLE ACCESS - FULL@H_502_5@ |
3.90@H_502_5@ |
latch: cache buffers chains@H_502_5@ |
2.30@H_502_5@ |
|
@H_502_5@ |
@H_502_5@ |
@H_502_5@ |
@H_502_5@ |
TABLE ACCESS - FULL@H_502_5@ |
1.13@H_502_5@ |
latch: cache buffers chains@H_502_5@ |
0.66@H_502_5@ |
|
19gkjf4r3zkpw@H_502_5@ |
2939541073@H_502_5@ |
1013@H_502_5@ |
2.23@H_502_5@ |
TABLE ACCESS - FULL@H_502_5@ |
1.73@H_502_5@ |
latch: cache buffers chains@H_502_5@ |
1.00@H_502_5@ |
select round(sum(t.current_fac...@H_502_5@ |
6qhrc4ax6zr1a@H_502_5@ |
2939541073@H_502_5@ |
960@H_502_5@ |
2.12@H_502_5@ |
TABLE ACCESS - FULL@H_502_5@ |
1.59@H_502_5@ |
latch: cache buffers chains@H_502_5@ |
0.96@H_502_5@ |
select round(sum(t.current_fac...@H_502_5@ |
6nmdjjxfv7aqp@H_502_5@ |
2939541073@H_502_5@ |
847@H_502_5@ |
1.87@H_502_5@ |
TABLE ACCESS - FULL@H_502_5@ |
1.39@H_502_5@ |
latch: cache buffers chains@H_502_5@ |
0.78@H_502_5@ |
select round(sum(t.current_fac...@H_502_5@ |
1qq2h1qkxkaha@H_502_5@ |
2939541073@H_502_5@ |
776@H_502_5@ |
1.71@H_502_5@ |
TABLE ACCESS - FULL@H_502_5@ |
1.34@H_502_5@ |
latch: cache buffers chains@H_502_5@ |
0.83@H_502_5@ |
select round(sum(t.current_fac...@H_502_5@ |
@H_502_5@
从这里分析，可以看到2xg4n5sj1sx5h的sql引发了性能瓶颈，因为短短的20分钟内执行了5066次，而且诱发了latch: cache buffers chains以及TABLE ACCESS – FULL全表扫描，所以针对这个sql，分析后，建立优化索引：@H_502_5@
createindex IDX_PROJECT_ID_BRRQon BIS_REPORT_RENT_QUICK(BIS_PROJECT_ID,SEQUNCE_NO,YEAR,MONTH);@H_502_5@
@H_502_5@
之后继续观察cpu负载，已经降低了一半，但是还不够，还没有达到正常值，而看ash报告已经不能提供帮助了。接下来我们需要更加全面的awr报告。@H_502_5@
@H_502_5@
@H_502_5@
2、Awr看以下个主要方向@H_502_5@
（1）sql ordered by Sharable Memory@H_502_5@
（2）sql ordered by cpu Tim@H_502_5@
3、先看sql ordered by Sharable Memory@H_502_5@
- Only Statements with Sharable Memory greater than 1048576 are displayed
Sharable Mem (b)@H_502_5@ |
Executions@H_502_5@ |
% Total@H_502_5@ |
|||
5,890,119@H_502_5@ |
19@H_502_5@ |
0.04@H_502_5@ |
8jfndnfr9hct8@H_502_5@ |
@H_502_5@ |
select * from ( select * from ...@H_502_5@ |
4,980,367@H_502_5@ |
3@H_502_5@ |
0.04@H_502_5@ |
3gnf4zamgyb7x@H_502_5@ |
@H_502_5@ |
select * from ( select * from ...@H_502_5@ |
3,816,036@H_502_5@ |
8@H_502_5@ |
0.03@H_502_5@ |
277rt4gknr76h@H_502_5@ |
@H_502_5@ |
update POWERDESK.cont_ledger s...@H_502_5@ |
2,966,255@H_502_5@ |
21@H_502_5@ |
0.02@H_502_5@ |
f2b3a59mdr7wb@H_502_5@ |
@H_502_5@ |
select * from ( select * from ...@H_502_5@ |
2,694,091@H_502_5@ |
12@H_502_5@ |
0.02@H_502_5@ |
a8y6k4gt09xxn@H_502_5@ |
@H_502_5@ |
select * from ( select * from ...@H_502_5@ |
2,316,487@H_502_5@ |
21@H_502_5@ |
0.02@H_502_5@ |
amyymj7a06gum@H_502_5@ |
@H_502_5@ |
select * from ( select * from ...@H_502_5@ |
2,003,767@H_502_5@ |
18@H_502_5@ |
0.01@H_502_5@ |
7z4hxbs26uz8d@H_502_5@ |
@H_502_5@ |
select * from ( select * from ...@H_502_5@ |
2,743@H_502_5@ |
6@H_502_5@ |
0.01@H_502_5@ |
96264xfwmxcm7@H_502_5@ |
@H_502_5@ |
select * from ( select * from ...@H_502_5@ |
1,979,443@H_502_5@ |
12@H_502_5@ |
0.01@H_502_5@ |
a8y6k4gt09xxn@H_502_5@ |
@H_502_5@ |
select * from ( select * from ...@H_502_5@ |
1,626,167@H_502_5@ |
18@H_502_5@ |
0.01@H_502_5@ |
7z4hxbs26uz8d@H_502_5@ |
@H_502_5@ |
select * from ( select * from ...@H_502_5@ |
1,354,011@H_502_5@ |
9@H_502_5@ |
0.01@H_502_5@ |
g371ncnjwg9zx@H_502_5@ |
@H_502_5@ |
select * from ( select * from ...@H_502_5@ |
1,325,857@H_502_5@ |
1,827@H_502_5@ |
0.01@H_502_5@ |
fm2hxa2nb9gzx@H_502_5@ |
@H_502_5@ |
select count(*) from ( select ...@H_502_5@ |
1,303,125@H_502_5@ |
192@H_502_5@ |
0.01@H_502_5@ |
dq8xug6mzj7dj@H_502_5@ |
@H_502_5@ |
select count(*) from (select r...@H_502_5@ |
1,277,136@H_502_5@ |
11@H_502_5@ |
0.01@H_502_5@ |
1jubuhx6cdxw8@H_502_5@ |
@H_502_5@ |
select sum(rent_square) from( ...@H_502_5@ |
1,147,400@H_502_5@ |
4@H_502_5@ |
0.01@H_502_5@ |
6gj7zqb64m29q@H_502_5@ |
@H_502_5@ |
select count(*) from ( select ...@H_502_5@ |
1,135,232@H_502_5@ |
4@H_502_5@ |
0.01@H_502_5@ |
6gj7zqb64m29q@H_502_5@ |
@H_502_5@ |
select count(*) from ( select ...@H_502_5@ |
1,126,672@H_502_5@ |
1@H_502_5@ |
0.01@H_502_5@ |
242g0k9qdh0q0@H_502_5@ |
@H_502_5@ |
select sum(rent_square) from( ...@H_502_5@ |
1,064,607@H_502_5@ |
3,723@H_502_5@ |
0.01@H_502_5@ |
7sum7dh4hcmbh@H_502_5@ |
@H_502_5@ |
select * from ( select jbpmtas...@H_502_5@ |
看到有fm2hxa2nb9gzx这条sql记录，执行次数比较多，有优化的空间：@H_502_5@
SELECT@H_502_5@ COUNT(*) @H_502_5@ FROM@H_502_5@ (SELECT@H_502_5@ res.*,@H_502_5@ 1 rec_status @H_502_5@ FROM@H_502_5@ Res_Approve_Info res @H_502_5@ WHEREEXISTS@H_502_5@ (SELECT@H_502_5@ 1@H_502_5@ FROM@H_502_5@ Res_Approve_User u @H_502_5@ WHERE u.res_Approve_Info_Id = res.res_Approve_Info_Id @H_502_5@ AND u.user_Cd = :1)@H_502_5@ @H_502_5@ UNION@H_502_5@ SELECT@H_502_5@ res.*,@H_502_5@ 9 rec_status @H_502_5@ FROM@H_502_5@ Res_Approve_Info res @H_502_5@ WHEREEXISTS@H_502_5@ (SELECT@H_502_5@ 1@H_502_5@ FROM@H_502_5@ Res_Approve_User u,@H_502_5@ Res_Accredit_Info a @H_502_5@ WHERE u.res_Approve_Info_Id = res.res_Approve_Info_Id @H_502_5@ AND U.USER_CD = A.USER_CD @H_502_5@ AND A.ACC_USER_CD = :2)) res @H_502_5@ WHERE1 = 1@H_502_5@ AND res.status_cd = '1'@H_502_5@ |
createindex IXU_RES_USER_CD_2 on RES_APPROVE_USER(USER_CD);@H_502_5@
@H_502_5@
@H_502_5@
@H_502_5@
4、再看sql ordered by cpu Time@H_502_5@
- Resources reported for PL/sql code includes the resources used by all sql statements called by the code.
- %Total - cpu Time as a percentage of Total DB cpu
- %cpu - cpu Time as a percentage of Elapsed Time
- %IO - User I/O Time as a percentage of Elapsed Time
- Captured sql account for 23.1% of Total cpu Time (s): 11,858
- Captured PL/sql account for 0.0% of Total cpu Time (s): 11,858
Executions@H_502_5@ |
%Total@H_502_5@ |
Elapsed Time (s)@H_502_5@ |
%IO@H_502_5@ |
||||||
146.67@H_502_5@ |
11,055@H_502_5@ |
0.01@H_502_5@ |
1.24@H_502_5@ |
192.16@H_502_5@ |
76.33@H_502_5@ |
0.00@H_502_5@ |
4198t22zum3dr@H_502_5@ |
@H_502_5@ |
select t1.module_cd,count(t1....@H_502_5@ |
140.39@H_502_5@ |
65@H_502_5@ |
2.16@H_502_5@ |
1.18@H_502_5@ |
300.45@H_502_5@ |
46.73@H_502_5@ |
0.00@H_502_5@ |
a8s9kjk7mk6yc@H_502_5@ |
@H_502_5@ |
select nvl(sum(nvl(sales_money...@H_502_5@ |
133.99@H_502_5@ |
63@H_502_5@ |
2.13@H_502_5@ |
1.13@H_502_5@ |
365.39@H_502_5@ |
36.67@H_502_5@ |
0.00@H_502_5@ |
15uv0hbq9dmm7@H_502_5@ |
@H_502_5@ |
select nvl(sum(nvl(sales_money...@H_502_5@ |
130.97@H_502_5@ |
65@H_502_5@ |
2.01@H_502_5@ |
1.10@H_502_5@ |
286.23@H_502_5@ |
45.75@H_502_5@ |
0.00@H_502_5@ |
bhnjxfa4av3kw@H_502_5@ |
@H_502_5@ |
select nvl(sum(nvl(sales_money...@H_502_5@ |
127.93@H_502_5@ |
4,215@H_502_5@ |
0.03@H_502_5@ |
1.08@H_502_5@ |
157.88@H_502_5@ |
81.03@H_502_5@ |
0.00@H_502_5@ |
aajc7r9y7z1qs@H_502_5@ |
@H_502_5@ |
select * from ( select this_.d...@H_502_5@ |
124.72@H_502_5@ |
63@H_502_5@ |
1.98@H_502_5@ |
1.05@H_502_5@ |
317.81@H_502_5@ |
39.24@H_502_5@ |
0.00@H_502_5@ |
1na6hypkn1q2f@H_502_5@ |
@H_502_5@ |
select nvl(sum(nvl(sales_money...@H_502_5@ |
112.56@H_502_5@ |
347@H_502_5@ |
0.32@H_502_5@ |
0.95@H_502_5@ |
444.87@H_502_5@ |
25.30@H_502_5@ |
0.00@H_502_5@ |
cpw6nx6gdv937@H_502_5@ |
@H_502_5@ |
select sum(rent_square) from( ...@H_502_5@ |
110.81@H_502_5@ |
19,013@H_502_5@ |
0.01@H_502_5@ |
0.93@H_502_5@ |
156.71@H_502_5@ |
70.71@H_502_5@ |
0.00@H_502_5@ |
ckkvz3r38xa4k@H_502_5@ |
@H_502_5@ |
select * from ( select kmpush0...@H_502_5@ |
103.00@H_502_5@ |
19,013@H_502_5@ |
0.01@H_502_5@ |
0.87@H_502_5@ |
136.82@H_502_5@ |
75.28@H_502_5@ |
0.00@H_502_5@ |
8n23w37kwdn3p@H_502_5@ |
@H_502_5@ |
select count(*) as col_0_0_ fr...@H_502_5@ |
97.71@H_502_5@ |
7,400@H_502_5@ |
0.01@H_502_5@ |
0.82@H_502_5@ |
130.32@H_502_5@ |
74.98@H_502_5@ |
0.00@H_502_5@ |
f1z0k5gx90tqz@H_502_5@ |
@H_502_5@ |
select (case when t1.module_cd...@H_502_5@ |
@H_502_5@
@H_502_5@
@H_502_5@
依据以上awr统计分析cpu time（s）得出的结论，点击进去，找出sql记录，并给出优化方案如下：@H_502_5@
@H_502_5@
4198t22zum3dr@H_502_5@ |
select t1.module_cd,count(t1.jbpm_task_id) num from jbpm_task t1,jbpm_task_candidate t2 where t1.jbpm_task_id=t2.jbpm_task_id and (t2.user_cd=:1 or t2.user_cd like :2 ) group by t1.module_cd order by decode(t1.module_cd,'mesMeetingInfo','0','resApprove','1','planTarget','2') asc@H_502_5@ |
优化建议：@H_502_5@ 将order by后面的decode(t1.module_cd,'2') asc移到from前面，在外层做order by操作@H_502_5@ |
@H_502_5@ |
||
bhnjxfa4av3kw@H_502_5@ |
select nvl(sum(nvl(sales_money,0)),0) from bis_sales_day where to_char(sales_date,'yyyy-MM')='2016-06' and bis_cont_id in (select bis_cont_id from bis_cont bc where cont_type_cd in ('1','2') and ((bc.status_cd = '2' and '2016-06' >= to_char(bc.cont_start_date,'yyyy-MM') and to_char(bc.cont_to_fail_date,'yyyy-MM')>='2016-06') or (bc.status_cd in ('1','3') and '2016-06' >= to_char(bc.cont_start_date,'yyyy-MM') and to_char(bc.cont_end_date,'yyyy-MM')>='2016-06')) and bc.bis_project_id in (select bis_project_id from bis_project where is_business_project ='1' ) and bc.store_type='1' )@H_502_5@ |
优化建议：@H_502_5@ |
15uv0hbq9dmm7@H_502_5@ |
select nvl(sum(nvl(sales_money,'yyyy-MM')='2016-07' and bis_cont_id in (select bis_cont_id from bis_cont bc where cont_type_cd in ('1','2') and ((bc.status_cd = '2' and '2016-07' >= to_char(bc.cont_start_date,'yyyy-MM')>='2016-07') or (bc.status_cd in ('1','3') and '2016-07' >= to_char(bc.cont_start_date,'yyyy-MM')>='2016-07')) and bc.bis_project_id in (select bis_project_id from bis_project where is_business_project ='1' ) and bc.store_type in ('1','2') )@H_502_5@ |
优化建议：@H_502_5@ |
bhnjxfa4av3kw@H_502_5@ |
select nvl(sum(nvl(sales_money,'yyyy-MM')>='2016-06')) and bc.bis_project_id in (select bis_project_id from bis_project where is_business_project ='1' ) and bc.store_type='1' )@H_502_5@ |
优化建议：@H_502_5@ |
@H_502_5@ |
aajc7r9y7z1qs@H_502_5@ |
select * from ( select this_.dly_note_id as dly1_396_0_,this_.created_center_cd as created2_396_0_,this_.created_date as created3_396_0_,this_.created_dept_cd as created4_396_0_,this_.created_position_cd as created5_396_0_,this_.creator as creator396_0_,this_.note_biz_type_cd as note7_396_0_,this_.note_content as note8_396_0_,this_.note_title as note9_396_0_,this_.record_version as record10_396_0_,this_.remark as remark396_0_,this_.send_mail_flg as send12_396_0_,this_.seq as seq396_0_,this_.tip_end_date as tip14_396_0_,this_.tip_flg as tip15_396_0_,this_.tip_period_cd as tip16_396_0_,this_.tip_start_date as tip17_396_0_,this_.tip_type_cd as tip18_396_0_,this_.updated_center_cd as updated19_396_0_,this_.updated_date as updated20_396_0_,this_.updated_dept_cd as updated21_396_0_,this_.updated_position_cd as updated22_396_0_,this_.updator as updator396_0_ from POWERDESK.dly_note this_ where this_.creator=:1 order by this_.seq asc ) where rownum <= :2@H_502_5@ |
优化方案，添加索引：createindex IDX_CREATOR on DLY_NOTE (creator);@H_502_5@ @H_502_5@ |
在添加好IDX_CREATOR、IXU_RES_USER_CD_2 这2个索引后，cpu负载已经再次降低了一半，虽然没有到正常值，但是已经接近最低级别的告警线，暂时数据库脱离危险了，就等其它的几个需要修改sql的优化继续完成后，cpu负载基本就可以恢复到正常值了。@H_502_5@
@H_502_5@
所以，一般oracle服务器负载飙升，90%都是由不规范的sql、性能差的sql造成的，而ash和awr分析报告是我们解决的最有效的途径。@H_502_5@
