[/@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语句拖垮整个系统的案例.