|* 19 | INDEX UNIQUE SCAN | CFMS_QUESTIONS | 4368 | 1 | 4368 |00:00:00.09 | 6304 | | | |
|* 20 | TABLE ACCESS FULL | CFMS_QUESTION_WORKFLOW | 4368 | 1 | 4371 |00:00:02.87 | 262K| | | |
|* 21 | TABLE ACCESS FULL | BPMS_RU_TODO_TASK | 4368 | 1424 | 19M|00:00:33.73 | 1659K| | | |
|* 22 | VIEW | | 1 | 1 | 10 |00:01:59.41 | 2244K| | | |
|* 23 | COUNT STOPKEY | | 1 | | 30 |00:01:59.41 | 2244K| | | |
| 24 | VIEW | | 1 | 1 | 30 |00:01:59.41 | 2244K| | | |
|* 25 | SORT ORDER BY STOPKEY | | 1 | 1 | 30 |00:01:59.41 | 2244K| 80896 | 80896 |71680 (0)|
|* 26 | HASH JOIN RIGHT SEMI | | 1 | 1 | 4368 |00:00:00.33 | 2065 | 1049K| 1049K| 1413K (0)|
| 27 | VIEW | VW_SQ_1 | 1 | 1424 | 4377 |00:00:00.21 | 1811 | | | |
| 28 | NESTED LOOPS | | 1 | 1424 | 4377 |00:00:00.19 | 1811 | | | |
|* 29 | HASH JOIN | | 1 | 1424 | 4377 |00:00:00.11 | 440 | 974K| 974K| 1446K (0)|
|* 30 | TABLE ACCESS FULL | BPMS_RU_TODO_TASK | 1 | 1424 | 4386 |00:00:00.01 | 380 | | | |
| 31 | TABLE ACCESS FULL | CFMS_QUESTION_WORKFLOW | 1 | 1674 | 5201 |00:00:00.02 | 60 | | | |
|* 32 | INDEX UNIQUE SCAN | CFMS_QUESTIONS | 4377 | 1 | 4377 |00:00:00.04 | 1371 | | | |
|* 33 | TABLE ACCESS FULL | CFMS_QUESTIONS | 1 | 3774 | 5163 |00:00:00.04 | 254 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CS"."SYS_ID"=:B1)
4 - access("M"."MODULE_ID"=:B1)
6 - filter("CR"."QUESTION_ID"=:B1)
7 - filter("V"."VERSION_ID"=:B1)
9 - access("T"."TAG_ID"="TQ"."TAG_ID")
10 - filter("TQ"."QUESTION_ID"=:B1)
13 - access("U"."USER_ID"=:B1)
15 - filter("M"."QUESTION_ID"=:B1)
17 - access("CQW"."PROCESS_INS_ID"="BRTT"."CUR_PROCESS_INS_ID")
20 - filter("CQW"."QUESTION_ID"=:B1)
21 - filter("BRTT"."TRANS_ACTOR_ID"='N00251.sz')
22 - filter("RN_">20)
23 - filter(ROWNUM<=30)
25 - filter(ROWNUM<=30)
26 - access("ITEM_1"="T2"."ID")
29 - access("CQW"."PROCESS_INS_ID"="BRTT"."CUR_PROCESS_INS_ID")
30 - filter("BRTT"."TRANS_ACTOR_ID"='N00251.sz')
32 - access("CQ"."ID"="CQW"."QUESTION_ID")
33 - filter("T2"."STATE"<>(-1))
10046trace的结果是(如何使用10046请看我以前的blog)，通过神器10046一眼就可以看出是对BPMS_RU_TODO_TASK这个子查询查询太多次，表了也有点大：
Rows Row Source Operation
------- ---------------------------------------------------
3 TABLE ACCESS BY INDEX ROWID CFMS_SYS (cr=5 pr=0 pw=0 time=156 us cost=1 size=52 card=1)
3 INDEX UNIQUE SCAN CFMS_SYS_PK (cr=2 pr=0 pw=0 time=70 us cost=0 size=0 card=1)(object id 132684)
4 TABLE ACCESS BY INDEX ROWID CFMS_MODULE (cr=6 pr=0 pw=0 time=161 us cost=1 size=50 card=1)
4 INDEX UNIQUE SCAN CFMS_MODUL (cr=2 pr=0 pw=0 time=70 us cost=0 size=0 card=1)(object id 132664)
4370 SORT AGGREGATE (cr=3 pr=0 pw=0 time=84950 us)
0 INDEX RANGE SCAN IND_CR_QUESTION_ID (cr=3 pr=0 pw=0 time=48849 us cost=1 size=51 card=3)(object id 134801)
0 TABLE ACCESS FULL CFMS_VERSIONS (cr=6 pr=0 pw=0 time=85 us cost=3 size=42 card=1)
4370 SORT AGGREGATE (cr=4370 pr=0 pw=0 time=986114 us)
0 HASH JOIN (cr=4370 pr=0 pw=0 time=499642 us cost=7 size=485 card=5)
0 TABLE ACCESS BY INDEX ROWID CFMS_TAG_QUESTION (cr=4370 pr=0 pw=0 time=128932 us cost=3 size=270 card=5)
0 INDEX RANGE SCAN IND_TQ_QUESTION_ID (cr=4370 pr=0 pw=0 time=57400 us cost=1 size=0 card=5)(object id 134802)
0 TABLE ACCESS FULL CFMS_TAG (cr=0 pr=0 pw=0 time=0 us cost=3 size=387 card=9)
2 MAT_VIEW ACCESS BY INDEX ROWID V_USER (cr=5 pr=0 pw=0 time=137 us cost=2 size=17 card=1)
2 INDEX UNIQUE SCAN PK_PUB_USER (cr=3 pr=