4370 SORT AGGREGATE (cr=4034 pr=0 pw=0 time=236040 us)
4380 TABLE ACCESS BY INDEX ROWID CFMS_QUESTION_MODIFY (cr=4034 pr=0 pw=0 time=184813 us cost=4 size=26 card=1)
4380 INDEX RANGE SCAN IND_QM_QUESTION_ID (cr=1873 pr=0 pw=0 time=72275 us cost=1 size=0 card=3)(object id 134803)
4370 SORT AGGREGATE (cr=1673138 pr=0 pw=0 time=106229115 us)
4371 HASH JOIN (cr=1673138 pr=0 pw=0 time=106172615 us cost=32 size=118 card=1)
4373 NESTED LOOPS (cr=16908 pr=0 pw=0 time=294422 us cost=4 size=71 card=1)
4370 INDEX UNIQUE SCAN CFMS_QUESTIONS (cr=6308 pr=0 pw=0 time=70360 us cost=1 size=17 card=1)(object id 132669)
4373 TABLE ACCESS BY INDEX ROWID CFMS_QUESTION_WORKFLOW (cr=10600 pr=0 pw=0 time=164442 us cost=3 size=54 card=1)
4373 INDEX RANGE SCAN IND_QW_QUESTION_ID (cr=6230 pr=0 pw=0 time=59792 us cost=1 size=0 card=1)(object id 134804)
19175560 TABLE ACCESS FULL BPMS_RU_TODO_TASK (cr=1656230 pr=0 pw=0 time=32077237 us cost=27 size=66928 card=1424)
10 VIEW (cr=1683632 pr=0 pw=0 time=108051153 us cost=104 size=4407 card=1)
30 COUNT STOPKEY (cr=1683632 pr=0 pw=0 time=108051028 us)
30 VIEW (cr=1683632 pr=0 pw=0 time=108050887 us cost=104 size=4394 card=1)
30 SORT ORDER BY STOPKEY (cr=1683632 pr=0 pw=0 time=108050758 us cost=104 size=375 card=1)
4370 HASH JOIN RIGHT SEMI (cr=2065 pr=0 pw=0 time=325742 us cost=103 size=375 card=1)
4379 VIEW VW_SQ_1 (cr=1812 pr=0 pw=0 time=206454 us cost=35 size=24208 card=1424)
4379 NESTED LOOPS (cr=1812 pr=0 pw=0 time=188877 us cost=35 size=168032 card=1424)
4379 HASH JOIN (cr=439 pr=0 pw=0 time=102674 us cost=35 size=143824 card=1424)
4388 TABLE ACCESS FULL BPMS_RU_TODO_TASK (cr=379 pr=0 pw=0 time=14081 us cost=27 size=66928 card=1424)
5206 TABLE ACCESS FULL CFMS_QUESTION_WORKFLOW (cr=60 pr=0 pw=0 time=13333 us cost=7 size=90396 card=1674)
4379 INDEX UNIQUE SCAN CFMS_QUESTIONS (cr=1373 pr=0 pw=0 time=40158 us cost=0 size=17 card=1)(object id 132669)
5168 TABLE ACCESS FULL CFMS_QUESTIONS (cr=253 pr=0 pw=0 time=42738 us cost=68 size=1351092 card=3774)
定位到了问题就简单了，这是一个分页SQL，降低子查询的次数即可，将子查询提出来，则每次都只执行10次，执行时间立马将下来0.75s。
SQL> SELECT (select cs.system_name
2 from cfms_sys cs
3 where cs.sys_id = unpaged.system_id) as system_name,
4 (select m.name
5 from cfms_module m
6 where m.module_id = unpaged.module_id) as module_name,
7 (select count(1)
8 from cfms_replys cr
9 where cr.question_id = unpaged.id) reply_count,
10 (select v.version_no
12 where v.version_id = unpaged.ps_online_version) as ps_online_version_no,
13 (select to_char(wmsys.wm_concat(t.tag_id || ';' || t.name))
14 from cfms_tag t, cfms_tag_question tq
15 where unpaged.id = tq.question_id
16 and t.tag_id = tq.tag_id) as tags,
17 (select u.name from v_user u where u.user_id = unpaged.service_id) as service_name,
18 (select max(m.modify_at)
19 from cfms_question_modify m
20 where m.question_id = unpaged.id) as modify_at,
21 decode((select count(1)
22 from cfms_questions cq,
23 cfms_question_workflow cqw,
24 bpms_ru_todo_task brtt
25 where cq.id = cqw.question_id
26 and cqw.process_ins_id = brtt.cur_process_ins_id
27 and cq.id = unpaged.id
28 and brtt.trans_actor_id = 'N00251.sz'),
29 0,
30 0,
31 1) as can_handle
32 FROM (SELECT unpaged_.*, rownum rn_
33 FROM (select t2.*
34 from cfms_questions t2
35 where t2.state <> -1
36 and exists
37 (select 1
38 from cfms_questions cq,
39 cfms_question_workflow cqw,
40 bpms_ru_todo_task brtt
41 where cq.id = cqw.qu