业务人员报告说不能登录系统，业务几乎停顿.
听到这个消息首先登录主机执行如下命令
[/@zzld03]#sar -u 1 10
HP-UX zzld03 B.11.23 U ia64 04/16/13
10:32:25 %usr %sys %wio %idle
10:32:26 63 1 26 10
10:32:27 53 1 23 23
10:32:28 49 3 33 16
10:32:29 43 1 39 18
10:32:30 39 1 34 26
10:32:31 35 0 40 24
10:32:32 41 1 37 21
10:32:33 43 1 42 15
10:32:34 40 4 40 15
10:32:35 57 11 27 5
Average 46 2 34 17
发现cpu消耗很高
执行top命令检查
从上面的top命令的监控情况来看pid=9362的进程消耗了95.55%的cpu
select s.sid,s.username,s.event,s.wait_time,s.state,s.seconds_in_wait,p.PROGRAM,s.MACHINE
from v$session s,v$process p
where p.spid=9362 and s.PADDR=p.ADDR
根据进程9362找到的语句如下:
select tt.indi_id as indi_id, tt.folk_code as folk_code, tt.urban_type as urban_type, e.mt_pers_type as pers_type, tt.pers_type as pers_type_detail, tt.culture_code as culture_code, tt.housereg_type as housereg_type, tt.job_sta_code as job_sta_code, tt.city_code as city_code, tt.occu_grade_code as occu_grade_code, tt.indi_sta as indi_sta, tt.kindred_code as kindred_code, tt.insr_code as insr_code, tt.name as name, decode(tt.sex, '1', '男', '0', '女', '未知') as sex, to_char(tt.birthday, 'yyyy-mm-dd') as birthday, tt.idcard as idcard, tt.native as native, to_char(tt.job_date, 'yyyy-mm-dd') as job_date, tt.retire_date as retire_date, tt.telephone as telephone, tt.address as address, tt.post_code as post_code, tt.marri_sta as marri_sta, tt.pre_job_years as pre_job_years, tt.all_job_years as all_job_years, tt.host_indi_id as host_indi_id, tt.nothing_flag as nothing_flag, tt.speical_pers_flag as speical_pers_flag, tt.remark as remark, d.folk_name as folk_name, e.pers_name as pers_name, f.culture_name as culture_name, g.housereg_name as housereg_name, h.job_sta_name as job_sta_name, i.city_name as city_name, i.city_class as city_class, j.occu_grade_name as occu_grade_name, k.indi_sta_name as indi_sta_name, l.kindred_name as kindred_name, tt.insr_detail_code as insr_detail_code, tt.corp_id as corp_id, to_char(tt.begin_date, 'yyyy-mm-dd') as begin_date, to_char(tt.end_date,'yyyy-mm-dd') as end_date, tt.indi_join_sta as indi_join_sta, tt.occu_code as occu_code, tt.freeze_sta as freeze_sta, tt.posi_code as posi_code, tt.hire_type as hire_type, tt.work_type as work_type, nvl(tt.official_code, '00') as official_code, tt.special_code as special_code, tt.indi_ins_no as indi_ins_no, tt.total_salary as total_salary, tt.indi_join_flag as indi_join_flag, m.occupation_name as occupation_name, n.position_name as position_name, o.hire_name as hire_name, p.work_type_name as work_type_name, q.special_name as special_name, tt.corp_name as corp_name, tt.corp_code as corp_code, tt.corp_type_code as corp_type_code, tt.corp_type_name as corp_type_name, tt.center_id as center_id, tt.veteran_benefit_name as veteran_benefit, decode(tt.official_code,'0','一般干部','1','副厅以上','2','副厅以下', t.official_name) as official_name, u.center_name as center_name, nvl(v.last_balance, 0) as last_balance from (select /*+rule*/ a.indi_id, a.folk_code, a.urban_type, a.pers_type, a.culture_code, a.housereg_type, a.job_sta_code, a.city_code, a.occu_grade_code, a.indi_sta, a.kindred_code, a.insr_code, a.name, a.sex, a.birthday, a.idcard, a.native, a.job_date, a.retire_date, a.telephone, a.address, a.post_code, a.marri_sta, a.pre_job_years, a.all_job_years, a.host_indi_id, a.nothing_flag, a.speical_pers_flag, decode(a.city_code,null,a.remark,(select city.city_name From bs_city city where city.city_code = a.city_code)) remark, (select bct.corp_type_name From bs_corp_type bct where bct.corp_type_code = s.corp_type_code and bct.center_id = s.center_id) corp_type_name, b.insr_detail_code, b.corp_id, b.begin_date, b.end_date, b.indi_join_sta, c.occu_code, decode( decode(nvl(p.freeze_sta,'0'),'0',nvl(b.freeze_sta, '0'),'1','9') , '0', '基金未冻结', '1','基金已冻结','9','单位已冻结') as freeze_sta, c.posi_code, c.hire_type, c.work_type, nvl(x.veteran_benefit_name,'非优抚对象') as veteran_benefit_name, decode(a.pers_type,'3',nvl(c.office_grade,'0'),c.official_code) as official_code, c.special_code, c.indi_ins_no, c.total_salary, c.indi_join_flag, s.corp_name, s.corp_code, s.corp_type_code, s.center_id from bs_corp s, bs_pres_insur b, bs_corp_insure p, bs_corp_pres c, bs_veteran_benefit x, bs_insured a where (a.idcard ='430204850922611' or a.idcard = '430204198509226110') and a.indi_id = b.indi_id and a.veteran_benefit_type = x.veteran_benefit_type(+) and decode(b.insr_detail_code,'12','2','21','2','17','2',b.insr_detail_code) = DECODE(a.Sex||'~'||a.Pers_Type||'~'||C.INDI_JOIN_FLAG||'~'||'2','0~1~1~7','2','0~2~1~7','2','2') and decode(p.insr_detail_code,'12','2','21','2','17','2',p.insr_detail_code) = DECODE(a.Sex||'~'||a.Pers_Type||'~'||C.INDI_JOIN_FLAG||'~'||'2','0~1~1~7','2','0~2~1~7','2','2') and s.corp_id = c.corp_id and s.corp_id = p.corp_id and b.insr_detail_code = p.insr_detail_code and b.indi_id = c.indi_id and b.corp_id = s.corp_id) tt, bs_folk d, bs_person_type e, bs_culture_stac f, bs_housereg_type g, bs_job_stac h, bs_city i, bs_occupation_grade j, bs_pers_status k, bs_kindred l, bs_occupation m, bs_position n, bs_hired_type o, bs_work_type p, bs_special q, bs_official t, bs_center u, bs_mdi_indi_acc v where tt.folk_code = d.folk_code (+) and tt.pers_type = e.pers_type (+) and tt.center_id = e.center_id (+) and tt.culture_code = f.culture_code (+) and tt.housereg_type = g.housereg_type (+) and tt.job_sta_code = h.job_sta_code (+) and tt.city_code = i.city_code (+) and tt.occu_grade_code = j.occu_grade_code (+) and tt.indi_sta = k.indi_sta (+) and tt.kindred_code = l.kindred_code (+) and tt.occu_code = m.occu_code (+) and tt.posi_code = n.posi_code (+) and tt.hire_type = o.hire_type (+) and tt.work_type = p.work_type (+) and tt.special_code = q.special_code (+) and tt.official_code = t.official_code (+) and tt.indi_id = v.indi_id (+) and 1 = v.acco_sta (+) and tt.center_id = u.center_id and tt.center_id in ('430300')
后通过查看该语句的执行计划,发现没有选择合适的索引造成的,而没有选择合适的索引是因为在查询中使用了/*+rule */提示,将该提示去掉后,语句执行正常cpu的利用率也恢复正常,应用也恢复正常.
这就是典刑的一条sql语句拖垮整个系统的案例.