跪求解决sql消耗CPU过高的方法！！

[iamBosss]

1：sql语句：
select cl.id,
cl.login_name,
cl.staffId,
null,
(select count(tcl.id)
from call_list_2011112800000656 tcl
where tcl.task_fk = t.id
and tcl.cl_handled_by_fk = cl.staffId
and tcl.handled_grp_fk is null) totalcounts,
GETCREYTIME('YYYY-MM-DD', cl.creyTime) creyTime,
(select count(cl.id)
from call_list_2011112800000656 tcl
where tcl.task_fk = cl.id
and tcl.cl_handled_by_fk = cl.staffId
and tcl.handled_grp_fk is null
and to_date('2012-03-22 00:00:00', 'yyyy-mm-dd hh24:mi:ss') <=
tcl.called_time
and to_date('2012-03-22 23:59:59', 'yyyy-mm-dd hh24:mi:ss') >=
tcl.called_time
and to_char(tcl.called_time, 'YYYY-MM-DD') = cl.creyTime
and tcl.status_fk = '4') jxcounts,
(select count(cl.id)
from call_list_2011112800000656 tcl
where tcl.task_fk = cl.id
and tcl.cl_handled_by_fk = cl.staffId
and tcl.handled_grp_fk is null
and to_date('2012-03-22 00:00:00', 'yyyy-mm-dd hh24:mi:ss') <=
tcl.called_time
and to_date('2012-03-22 23:59:59', 'yyyy-mm-dd hh24:mi:ss') >=
tcl.called_time
and to_char(tcl.called_time, 'YYYY-MM-DD') = cl.creyTime
and tcl.status_fk = '3') yycounts,
(select count(cl.id)
from call_list_2011112800000656 tcl
where tcl.task_fk = cl.id
and tcl.cl_handled_by_fk = cl.staffId
and tcl.handled_grp_fk is null
and to_date('2012-03-22 00:00:00', 'yyyy-mm-dd hh24:mi:ss') <=
tcl.called_time
and to_date('2012-03-22 23:59:59', 'yyyy-mm-dd hh24:mi:ss') >=
tcl.called_time
and to_char(tcl.called_time, 'YYYY-MM-DD') = cl.creyTime
and tcl.status_fk = '2'
and tcl.call_result_fk = '成功') wccounts,
(select count(tcl.id)
from qnaire_result qr, call_list_2011112800000656 tcl
where qr.responser_id = tcl.id
and tcl.status_fk in ('2', '3', '4', '6')
and tcl.task_fk = cl.id
and tcl.cl_handled_by_fk = cl.staffId
and tcl.handled_grp_fk is null
and to_date('2012-03-22 00:00:00', 'yyyy-mm-dd hh24:mi:ss') <=
tcl.called_time
and to_date('2012-03-22 23:59:59', 'yyyy-mm-dd hh24:mi:ss') >=
tcl.called_time
and to_char(tcl.called_time, 'YYYY-MM-DD') = cl.creyTime
and qr.result_status = 'GiveUp') Giveup,
(select count(tcl.id)
from qnaire_result qr, call_list_2011112800000656 tcl
where qr.responser_id = tcl.id
and tcl.status_fk in ('2', '3', '4', '6')
and tcl.task_fk = cl.id
and tcl.cl_handled_by_fk = cl.staffId
and tcl.handled_grp_fk is null
and to_date('2012-03-22 00:00:00', 'yyyy-mm-dd hh24:mi:ss') <=
tcl.called_time
and to_date('2012-03-22 23:59:59', 'yyyy-mm-dd hh24:mi:ss') >=
tcl.called_time
and to_char(tcl.called_time, 'YYYY-MM-DD') = cl.creyTime
and qr.result_status = 'Success') susTask,
(select count(tcl.id)
from qnaire_result qr, call_list_2011112800000656 tcl
where qr.responser_id = tcl.id
and tcl.status_fk in ('2', '3', '4', '6')
and tcl.task_fk = cl.id
and tcl.cl_handled_by_fk = cl.staffId
and tcl.handled_grp_fk is null
and to_date('2012-03-22 00:00:00', 'yyyy-mm-dd hh24:mi:ss') <=
tcl.called_time
and to_date('2012-03-22 23:59:59', 'yyyy-mm-dd hh24:mi:ss') >=
tcl.called_time
and to_char(tcl.called_time, 'YYYY-MM-DD') = cl.creyTime
and qr.result_status = 'Unfinished') Unfinished,
(select count(tcl.id)
from call_list_2011112800000656 tcl
where tcl.task_fk = cl.id
and tcl.status_fk in ('2', '3', '4', '6')
and tcl.cl_handled_by_fk = cl.staffId
and tcl.handled_grp_fk is null
and to_date('2012-03-22 00:00:00', 'yyyy-mm-dd hh24:mi:ss') <=
tcl.called_time
and to_date('2012-03-22 23:59:59', 'yyyy-mm-dd hh24:mi:ss') >=
tcl.called_time
and to_char(tcl.called_time, 'YYYY-MM-DD') = cl.creyTime
and tcl.status_fk = '2'
and tcl.call_result_fk = '失败') faild,
t.name,
to_char(t.validate_from, 'yyyy-mm-dd hh24:mi:ss') validate_from,
to_char(t.validate_to, 'yyyy-mm-dd hh24:mi:ss') validate_to,
decode(t.is_open, '0', '关闭', '1', '开启') state
from (select cl.task_fk id,
s.id staffId,
s.login_name,
to_char(cl.called_time, 'YYYY-MM-DD') creyTime
from qnaire_result qr,
call_list_2011112800000656 cl,
staff s,
group_ g
where cl.cl_handled_by_fk = s.id(+)
and cl.handled_grp_fk = g.id(+)
and to_date('2012-03-22 00:00:00', 'yyyy-mm-dd hh24:mi:ss') <=
cl.called_time
and to_date('2012-03-22 23:59:59', 'yyyy-mm-dd hh24:mi:ss') >=
cl.called_time
and qr.responser_id(+) = cl.id
and cl.cl_handled_by_fk in
('2011112800000927', '2011112800000933', '2011112800000900',
'2011112800000950', '2011112800000965', '2011112800001002',
'2011112800001004', '2011112800001009', '2011112800001011',
'2011112800000853', '2011112800000887', '2011112800000912',
'2011112800000924', '2011112800000930', '2011112800000969',
'2011112800000867', '2011112800000898', '2011112800000908',
'2011112800000920', '2011112800000936', '2011112800000938',


'2012020300000586', '2012020300000593', '2012020300000712',
'2012020300000749', '2012020300000764', '2012020300000787',
'2012020300000816', '2012020300000825', '2012020300000877')
and cl.handled_grp_fk is null
and cl.task_fk in ('2012030100002702')
group by to_char(cl.called_time, 'YYYY-MM-DD'),
s.login_name,
s.id,
cl.task_fk) cl,
task2 t
where cl.id(+) = t.id
and t.id in ('2012030100002702')
and t.campaign_type = '1'
and t.tenant_id = '2011112800000656'

2.之前参数有100多个，现在把参数改为20个，查询速度是快了好几倍但是cpu消耗仍然很高。。。

[我心飞翔]

先把图片上传到自己的空间相册中，然后打开图片，右键->复制图片网址，回到帖子，点“插入图片”按钮，粘贴刚才的网址，就可以发图片了。

[test2050]

使用工具中的计划执行啊，里面列出语句哪里花多少时间的

[iamBosss]

时间很快，基本都1秒左右。最大不会超过2秒。大部分不到1秒。但是cpu任然很高。

[iamBosss]

执行计划的图片也传不上去，估计是传的方式不对吧

[啊彪123]

这种业务真他妈蛋疼，哪个jb设计搞出来的！

[jdsnhan]

CPU高确认是由这个语句引起的吗？
看v$session_wait里面有没有其他等待事件

[zlb_chen]

为什么非要一条语句写完，就不能分成多条语句写，然后组装成想要的结果集？