業務人員報告說不能登入系統，業務幾乎停頓.
聽到這個消息首先登入主機執行如下指令
[/@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語句拖垮整個系統的案例.
![JDBC連接配接資料庫（statement）[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)