一条SQL执行接近两分钟，一看便知子查询非常多，可以先使用调优利器dbms_xplan.display_cursor。
SQL>alter session set statistics_level=all;
SQL>set pagesize 100
SQL>SELECT *
FROM (SELECT unpaged_.*, rownum rn_
FROM (select t2.*,
(select cs.system_name
from cfms_sys cs
where cs.sys_id = t2.system_id) as system_name,
(select m.name
from cfms_module m
where m.module_id = t2.module_id) as module_name,
(select count(1)
from cfms_replys cr
where cr.question_id = t2.id) reply_count,
(select v.version_no
from cfms_versions v
where v.version_id = t2.ps_online_version) as ps_online_version_no,
(select to_char(wmsys.wm_concat(t.tag_id || ';' ||
t.name))
from cfms_tag t, cfms_tag_question tq
where t2.id = tq.question_id
and t.tag_id = tq.tag_id) as tags,
(select u.name
from v_user u
where u.user_id = t2.service_id) as service_name,
(select max(m.modify_at)
from cfms_question_modify m
where m.question_id = t2.id) as modify_at,
decode((select count(1)
from cfms_questions cq,
cfms_question_workflow cqw,
bpms_ru_todo_task brtt
where cq.id = cqw.question_id
and cqw.process_ins_id =
brtt.cur_process_ins_id
and cq.id = t2.id
and brtt.trans_actor_id = 'N00251.sz'),
0,
0,
1) as can_handle
from cfms_questions t2
where t2.state <> -1
and exists
(select 1
from cfms_questions cq,
cfms_question_workflow cqw,
bpms_ru_todo_task brtt
where cq.id = cqw.question_id
and cqw.process_ins_id = brtt.cur_process_ins_id
and cq.id = t2.id
and brtt.trans_actor_id = 'N00251.sz')
order by t2.discover_time desc, t2.id) unpaged_
WHERE rownum <= 30)
WHERE rn_ > 20；
已用时间: 00: 01: 53.13
SQL>select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
关于下面列的解释，请看http://blog.csdn.net/stevendbaguo/article/details/13776221 ，初步判断子查询次数太多（看Starts便知），很多表都是全表扫描太多次导致。对相应的表加上索引后，有些子查询还是很慢，现在不得不使用神器10046 。
----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:01:59.41 | 2244K| | | |
| 1 | TABLE ACCESS BY INDEX ROWID | CFMS_SYS | 3 | 1 | 3 |00:00:00.01 | 5 | | | |
|* 2 | INDEX UNIQUE SCAN | CFMS_SYS_PK | 3 | 1 | 3 |00:00:00.01 | 2 | | | |
| 3 | TABLE ACCESS BY INDEX ROWID | CFMS_MODULE | 5 | 1 | 4 |00:00:00.01 | 6 | | | |
|* 4 | INDEX UNIQUE SCAN | CFMS_MODUL | 5 | 1 | 4 |00:00:00.01 | 2 | | | |
| 5 | SORT AGGREGATE | | 4368 | 1 | 4368 |00:00:00.29 | 26208 | | | |
|* 6 | TABLE ACCESS FULL | CFMS_REPLYS | 4368 | 3 | 0 |00:00:00.25 | 26208 | | | |
|* 7 | TABLE ACCESS FULL | CFMS_VERSIONS | 1 | 1 | 0 |00:00:00.01 | 6 | | | |
| 8 | SORT AGGREGATE | | 4368 | 1 | 4368 |00:00:01.08 | 26208 | | | |
|* 9 | HASH JOIN | | 4368 | 5 | 0 |00:00:00.58 | 26208 | 821K| 821K| 221K (0)|
|* 10 | TABLE ACCESS FULL | CFMS_TAG_QUESTION | 4368 | 5 | 0 |00:00:00.19 | 26208 | | | |
| 11 | TABLE ACCESS FULL | CFMS_TAG | 0 | 9 | 0 |00:00:00.01 | 0 | | | |
| 12 | MAT_VIEW ACCESS BY INDEX ROWID| V_USER | 3 | 1 | 2 |00:00:00.01 | 5 | | | |
|* 13 | INDEX UNIQUE SCAN | PK_PUB_USER | 3 | 1 | 2 |00:00:00.01 | 3 | | | |
| 14 | SORT AGGREGATE | | 4368 | 1 | 4368 |00:00:03.24 | 262K| | | |
|* 15 | TABLE ACCESS FULL | CFMS_QUESTION_MODIFY | 4368 | 1 | 4378 |00:00:03.16 | 262K| | | |
| 16 | SORT AGGREGATE | | 4368 | 1 | 4368 |00:01:54.25 | 1928K| | | |
|* 17 | HASH JOIN | | 4368 | 1 | 4369 |00:01:54.19 | 1928K| 894K| 894K| 381K (0)|
| 18 | NESTED LOOPS | | 4368 | 1 | 4371 |00:00:03.04 | 268