业务人员报告说不能登录系统，业务几乎停顿.
听到这个消息首先登录主机执行如下命令
[/@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语句拖垮整个系统的案例.