SQL> select * from (select rows_.*, rownum rn from ( select piresult.* from (select null IMPLEMENT_RESULT_ID, baseinfo.implement_id,
2 baseinfo.cycle_id, baseinfo.emp_class_id, baseinfo.org_id, (select org.ORG_NAME from v_org_organization_now org where org.ORG_ID =
3 baseinfo.org_id and rownum = 1) org_name, baseinfo.cycle_type, baseinfo.year, cycle.cycle_name, baseinfo.implement_state,
4 relation.IMPLEMENT_RETION_ID, relation.dept_id, relation.dept_name, relation.org_id "dept3Id", relation.org_name "dept3Name",
5 relation.checked_man_id, relation.checked_man_code, relation.checked_man_name, relation.CHECK_MAN_ID, relation.CHECK_MAN_CODE,
6 relation.CHECK_MAN_NAME, relation.CHECK_MAN_DEPT_ID, relation.CHECK_MAN_DEPT_NAME, relation.FINAL_CHECK_MAN_ID, relation.FINAL_CHECK_MAN_CODE,
7 relation.FINAL_CHECK_MAN_NAME, relation.IS_CHECK_APPLYING, relation.implement_relation_state, rem.REMARK_INFO_ID,
8 (case when relation.implement_relation_state >= 6 then rem.CHECK_MARK else null end) CHECK_MARK,
9 (case when relation.implement_relation_state >= 6 then rem.CHECK_LVL else null end) CHECK_LVL,
10 (case when relation.implement_relation_state >= 6 then rem.FINAL_CHECK_LVL else null end) FINAL_CHECK_LVL,
11 nvl((SELECT PWB.Plan_State FROM PFM_WORK_PLAN_BASEINFO PWB WHERE PWB.CYCLE_ID = baseinfo.cycle_id AND PWB.YEAR = baseinfo.year AND
12 PWB.PLAN_MAN_ID = relation.checked_man_id AND ROWNUM = 1), '-1') Plan_State, nvl((SELECT PSB.Self_Remark_State FROM PFM_SELF_REMARK_BASEINFO PSB
13 WHERE PSB.CYCLE_ID = baseinfo.cycle_id AND PSB.YEAR = baseinfo.year AND PSB.SELF_REMARK_MAN_ID = relation.checked_man_id AND ROWNUM = 1), '-1')
14 Self_Remark_State, nvl((SELECT PIBI.Interview_State FROM PFM_INTERVIEW_BASEINFO PIBI WHERE PIBI.CYCLE_ID = baseinfo.cycle_id AND PIBI.YEAR =
15 baseinfo.year AND PIBI.INTERVIEW_MAN_ID = relation.checked_man_id AND ROWNUM = 1), '-1') Interview_State, 1 "RESULT_SOURCE",
16 nvl((select decode(path.check_man_id, NULL, '管理员', (SELECT n.employee_name FROM v_org_employee n WHERE n.employee_id = path.check_man_id))
17 from implement_pth path where path.IMPLEMENT_RETION_ID = relation.IMPLEMENT_RETION_ID AND path.end_time IS NULL AND path.IS_CUR_STEP = 1
18 and rownum = 1), '') "CUR_CHECK_MAN" from pfm_implement_baseinfo baseinfo, PFM_IMPLEMENT_RELATION relation, PFM_REMARK_INFO rem,
19 pfm_cycle_category cycle where baseinfo.implement_id = relation.implement_id and baseinfo.cycle_id = cycle.cycle_id and
20 relation.IMPLEMENT_RETION_ID = rem.IMPLEMENT_RETION_ID(+) and relation.IS_CHECK = 1 union all select pir.implement_result_id,
21 pir.implement_id, pir.cycle_id, null, pir.company_id, pir.company_name, pir.cycle_type, pir.year, cycle2.cycle_name, '7',
22 pir.IMPLEMENT_RETION_ID, pir.dept_id, pir.dept_name, pir.org_id "dept3Id", pir.org_name "dept3Name", pir.checked_man_id, pir.checked_man_code,
23 pir.checked_man_name, null, null, null, null, null, null, null, null, '0', '7', null, pir.check_mark, pir.check_lvl, pir.final_check_lvl, '-1',
24 '-1', '-1', pir.result_source, null "CUR_CHECK_MAN" from pfm_implement_result pir, pfm_cycle_category cycle2 where pir.cycle_id = cycle2.cycle_id
25 and pir.result_source = 2 ) piresult, v_org_employee e where piresult.checked_man_id = e.EMPLOYEE_ID(+) and 1 = 1 and (piresult.year >= 2012)
26 and (piresult.year <= 2013) and (piresult.implement_state >= 5 ) and (piresult.implement_state <= 7 ) and ( ( piresult.checked_man_id in
27 (select * from Table(F_GET_EMPLOYEE_ME_SUB_NOW(1516269, null))) ) ) order by piresult.YEAR desc, piresult.cycle_id, piresult.IMPLEMENT_RETION_ID desc,
28 piresult.IMPLEMENT_RESULT_ID desc ) rows_ where rownum <= 20 ) where rn > 0 ;
20 rows selected.
Elapsed: 00:05:31.87
Execution Plan
----------------------------------------------------------
Plan hash value: 3262236664
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 17840 | | 1614K (1)| 05:22:52 |
|* 1 | VIEW | | 20 | 17840 | | 1614K (1)| 05:22:52 |
|* 2 | COUNT STOPKEY | | | | | | |
| 3 | VIEW | | 8484K| 7112M| | 1614K (1)| 05:22:52 |
|* 4 | SORT ORDER BY STOPKEY | | 8484K| 7266M| 14G| 1614K (1)| 05:22:52 |
|* 5 | HASH JOIN | | 8484K| 7266M| | 12903 (3)| 00:02:35 |
| 6 | VIEW | VW_NSO_1 | 8168 | 103K| | 26 (8)| 00:00:01 |
| 7 | HASH UNIQUE | | 8168 | 16336 | | 26 (8)| 00:00:01 |
| 8 | COLLECTION ITERATOR PICKLER FETCH| F_GET_EMPLOYEE_ME_SUB_NOW | | | | | |
| 9 | NESTED LOOPS OUTER | | 103K| 87M| | 12798 (2)| 00:02:34 |
| 10 | VIEW | | 103K| 86M| | 12780 (2)| 00:02:34 |
| 11 | UNION-ALL | | | | | | |
| 12 | NESTED LOOPS OUTER | | 379 | 68220 | | 1193 (1)| 00:00:15 |
| 13 | NESTED LOOPS | | 379 | 60640 | | 102 (0)| 00:00:02 |
| 14 | NESTED LOOPS | | 1 | 38 | | 11 (0)| 00:00:01 |
|* 15 | TABLE ACCESS FULL | PFM_IMPLEMENT_BASEINFO | 1 | 28 | | 10 (0)| 00:00:01 |
| 16 | TABLE ACCESS BY INDEX ROWID | PFM_CYCLE_CATEGORY | 1 | 10 | | 1 (0)| 00:00:01 |
|* 17 | INDEX UNIQUE SCAN | PK_PFM_CYCLE_CATEGORY | 1 | | | 0 (0)| 00:00:01 |
|* 18 | TABLE ACCESS BY INDEX ROWID | PFM_IMPLEMENT_RELATION | 441 | 53802 | | 91 (0)| 00:00:02 |
|* 19 | INDEX RANGE SCAN | ASSOCIATION23_FK | 882 | | | 5 (0)| 00:00:01 |
| 20 | TABLE ACCESS BY INDEX ROWID | PFM_REMARK_INFO | 1 | 20 | | 3 (0)| 00:00:01 |
|* 21 | INDEX RANGE SCAN | IX_PFM_REMARK_INFO_N02 | 1 | | | 2 (0)| 00:00:01 |
|* 22 | HASH JOIN | | 103K| 13M| | 11587 (2)| 00:02:20 |
| 23 | TABLE ACCESS FULL | PFM_CYCLE_CATEGORY | 23 | 230 | | 3 (0)| 00:00:01 |
|* 24 | TABLE ACCESS FULL | PFM_IMPLEMENT_RESULT | 103K| 12M| | 11583 (2)| 00:02:19 |
|* 25 | INDEX UNIQUE SCAN | PK_EMP_BASEINFO2 | 1 | 6 | | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">0)
2 - filter(ROWNUM<=20)
4 - filter(ROWNUM<=20)
5 - access("PIRESULT"."CHECKED_MAN_ID"="COLUMN_VALUE")
15 - filter("BASEINFO"."YEAR">=2012 AND TO_NUMBER("BASEINFO"."IMPLEMENT_STATE")>=5 AND
TO_NUMBER("BASEINFO"."IMPLEMENT_STATE")<=7 AND "BASEINFO"."YEAR"<=2013)
17 - access("BASEINFO"."CYCLE_ID"="CYCLE"."CYCLE_ID")
18 - filter(TO_NUMBER("RELATION"."IS_CHECK")=1)
19 - access("BASEINFO"."IMPLEMENT_ID"="RELATION"."IMPLEMENT_ID")
21 - access("RELATION"."IMPLEMENT_RETION_ID"="REM"."IMPLEMENT_RETION_ID"(+))
22 - access("PIR"."CYCLE_ID"="CYCLE2"."CYCLE_ID")
24 - filter("PIR"."YEAR">=2012 AND "PIR"."RESULT_SOURCE"=2 AND "PIR"."YEAR"<=2013)
25 - access("PIRESULT"."CHECKED_MAN_ID"="EMPLOYEE_ID"(+))
Statistics
----------------------------------------------------------
103 recursive calls
2 db block gets
5452994 consistent gets
59435 physical reads
0 redo size
4880 bytes sent via SQL*Net to client
2586 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
20 rows processed
可以看出这个sql是个分页语句，逻辑读有五百万，坑爹的是驱动表是union all之后的内联视图，将内联视图和被驱动表改写join之后如下：
SQL> select *
2 from (select rows_.*, rownum rn
3 from (select *
4 from (select null IMPLEMENT_RESULT_ID,
5 baseinfo.implement_id,
6 baseinfo.cycle_id,
7 baseinfo.emp_class_id,
8 baseinfo.org_id,
9 (select org.ORG_NAME
10 from v_org_organization_now org
11 where org.ORG_ID = baseinfo.org_id
12 and rownum = 1) org_name,
13 baseinfo.cycle_type,
14 baseinfo.year,
15 cycle.cycle_name,
16 baseinfo.implement_state,
17 relation.IMPLEMENT_RETION_ID,
18 relation.dept_id,
19 relation.dept_name,
20 relation.org_id "dept3Id",
21 relation.org_name "dept3Name",
22 relation.checked_man_id,
23 relation.checked_man_code,
24 relation.checked_man_name,
25 relation.CHECK_MAN_ID,
26 relation.CHECK_MAN_CODE,
27 relation.CHECK_MAN_NAME,
28 relation.CHECK_MAN_DEPT_ID,
29 relation.CHECK_MAN_DEPT_NAME,
30 relation.FINAL_CHECK_MAN_ID,
31 relation.FINAL_CHECK_MAN_CODE,
32 relation.FINAL_CHECK_MAN_NAME,
33 relation.IS_CHECK_APPLYING,
34 relation.implement_relation_state,
35 rem.REMARK_INFO_ID,
36 (case
37 when relation.implement_relation_state >= 6 then
38 rem.CHECK_MARK
39 else
40 null
41 end) CHECK_MARK,
42 (case
43 when relation.implement_relation_state >= 6 then
44 rem.CHECK_LVL
45 else
46 null
47 end) CHECK_LVL,
48 (case
49 when relation.implement_relation_state >= 6 then
50 rem.FINAL_CHECK_LVL
51 else
52 null
53 end) FINAL_CHECK_LVL,
54 nvl((SELECT PWB.Plan_State
55 FROM PFM_WORK_PLAN_BASEINFO PWB
56 WHERE PWB.CYCLE_ID = baseinfo.cycle_id
57 AND PWB.YEAR = baseinfo.year
58 AND PWB.PLAN_MAN_ID =
59 relation.checked_man_id
60 AND ROWNUM = 1),
61 '-1') Plan_State,
62 nvl((SELECT PSB.Self_Remark_State
63 FROM PFM_SELF_REMARK_BASEINFO PSB
64 WHERE PSB.CYCLE_ID = baseinfo.cycle_id
65 AND PSB.YEAR = baseinfo.year
66 AND PSB.SELF_REMARK_MAN_ID =
67 relation.checked_man_id
68 AND ROWNUM = 1),
69 '-1') Self_Remark_State,
70 nvl((SELECT PIBI.Interview_State
71 FROM PFM_INTERVIEW_BASEINFO PIBI
72 WHERE PIBI.CYCLE_ID = baseinfo.cycle_id
73 AND PIBI.YEAR = baseinfo.year
74 AND PIBI.INTERVIEW_MAN_ID =
75 relation.checked_man_id
76 AND ROWNUM = 1),
77 '-1') Interview_State,
78 1 "RESULT_SOURCE",
79 nvl((select decode(path.check_man_id,
80 NULL,
81 '管理员',
82 (SELECT n.employee_name
83 FROM v_org_employee n
84 WHERE n.employee_id =
85 path.check_man_id))
86 from implement_pth path
87 where path.IMPLEMENT_RETION_ID =
88 relation.IMPLEMENT_RETION_ID
89 AND path.end_time IS NULL
90 AND path.IS_CUR_STEP = 1
91 and rownum = 1),
92 '') "CUR_CHECK_MAN"
93 from pfm_implement_baseinfo baseinfo,
94 PFM_IMPLEMENT_RELATION relation,
95 PFM_REMARK_INFO rem,
96 pfm_cycle_category cycle,
97 v_org_employee e
98 where baseinfo.implement_id = relation.implement_id
99 and baseinfo.cycle_id = cycle.cycle_id
100 and relation.IMPLEMENT_RETION_ID =
101 rem.IMPLEMENT_RETION_ID(+)
102 and relation.IS_CHECK = 1
103 and checked_man_id = e.EMPLOYEE_ID(+)
104 and 1 = 1
105 and (year >= 2012)
and (year <= 2013)
106 107 and (implement_state >= 5)
and (implement_state <= 7)
108 109 and relation.checked_man_id in
110 (select *
111 from Table(F_GET_EMPLOYEE_ME_SUB_NOW(1516269,
112 null)))
113 union all
114 select pir.implement_result_id,
115 pir.implement_id,
116 pir.cycle_id,
117 null,
118 pir.company_id,
119 pir.company_name,
120 pir.cycle_type,
121 pir.year,
122 cycle2.cycle_name,
123 '7',
124 pir.IMPLEMENT_RETION_ID,
125 pir.dept_id,
126 pir.dept_name,
127 pir.org_id "dept3Id",
128 pir.org_name "dept3Name",
129 pir.checked_man_id,
130 pir.checked_man_code,
131 pir.checked_man_name,
132 null,
133 null,
134 null,
135 null,
136 null,
137 null,
138 null,
139 null,
140 '0',
141 '7',
142 null,
143 pir.check_mark,
144 pir.check_lvl,
145 pir.final_check_lvl,
146 '-1',
147 '-1',
148 '-1',
149 pir.result_source,
150 null "CUR_CHECK_MAN"
151 from pfm_implement_result pir,
152 pfm_cycle_category cycle2,
153 v_org_employee e
154 where pir.cycle_id = cycle2.cycle_id
155 and pir.result_source = 2
156 and checked_man_id = e.EMPLOYEE_ID(+)
157 and 1 = 1
158 and (year >= 2012)
159 and (year <= 2013)
160 /*and (implement_state >= 5)
161 and (implement_state <= 7)*/
162 and checked_man_id in
163 (select *
164 from Table(F_GET_EMPLOYEE_ME_SUB_NOW(1516269,
165 null))))
166 order by YEAR desc,
167 cycle_id desc,
168 IMPLEMENT_RETION_ID desc,
169 IMPLEMENT_RESULT_ID desc) rows_
170 where rownum <= 20)
171 where rn > 0;
20 rows selected.
将内联视图改写join后，如下：
Elapsed: 00:00:03.09
Execution Plan
----------------------------------------------------------
Plan hash value: 2272649659
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 2676 | 185 (3)| 00:00:03 |
|* 1 | VIEW | | 3 | 2676 | 185 (3)| 00:00:03 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 3 | 2637 | 185 (3)| 00:00:03 |
|* 4 | SORT ORDER BY STOPKEY | | 3 | 2637 | 185 (3)| 00:00:03 |
| 5 | VIEW | | 3 | 2637 | 184 (2)| 00:00:03 |
| 6 | UNION-ALL | | | | | |
| 7 | NESTED LOOPS OUTER | | 1 | 199 | 130 (1)| 00:00:02 |
| 8 | NESTED LOOPS OUTER | | 1 | 193 | 130 (1)| 00:00:02 |
|* 9 | HASH JOIN SEMI | | 1 | 173 | 127 (1)| 00:00:02 |
|* 10 | TABLE ACCESS BY INDEX ROWID | PFM_IMPLEMENT_RELATION | 441 | 53802 | 91 (0)| 00:00:02 |
| 11 | NESTED LOOPS | | 379 | 60640 | 102 (0)| 00:00:02 |
| 12 | NESTED LOOPS | | 1 | 38 | 11 (0)| 00:00:01 |
|* 13 | TABLE ACCESS FULL | PFM_IMPLEMENT_BASEINFO | 1 | 28 | 10 (0)| 00:00:01 |
| 14 | TABLE ACCESS BY INDEX ROWID | PFM_CYCLE_CATEGORY | 1 | 10 | 1 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | PK_PFM_CYCLE_CATEGORY | 1 | | 0 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | ASSOCIATION23_FK | 882 | | 5 (0)| 00:00:01 |
| 17 | VIEW | VW_NSO_1 | 8168 | 103K| 24 (0)| 00:00:01 |
| 18 | COLLECTION ITERATOR PICKLER FETCH | F_GET_EMPLOYEE_ME_SUB_NOW | | | | |
| 19 | TABLE ACCESS BY INDEX ROWID | PFM_REMARK_INFO | 1 | 20 | 3 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | IX_PFM_REMARK_INFO_N02 | 1 | | 2 (0)| 00:00:01 |
|* 21 | INDEX UNIQUE SCAN | PK_EMP_BASEINFO2 | 1 | 6 | 0 (0)| 00:00:01 |
| 22 | NESTED LOOPS | | 2 | 306 | 54 (4)| 00:00:01 |
| 23 | NESTED LOOPS OUTER | | 2 | 286 | 52 (4)| 00:00:01 |
| 24 | NESTED LOOPS | | 2 | 274 | 52 (4)| 00:00:01 |
| 25 | VIEW | VW_NSO_2 | 8168 | 103K| 24 (0)| 00:00:01 |
| 26 | HASH UNIQUE | | 1 | 16336 | | |
| 27 | COLLECTION ITERATOR PICKLER FETCH| F_GET_EMPLOYEE_ME_SUB_NOW | | | | |
|* 28 | TABLE ACCESS BY INDEX ROWID | PFM_IMPLEMENT_RESULT | 2 | 248 | 26 (0)| 00:00:01 |
|* 29 | INDEX RANGE SCAN | IX_PFM_IMPLEMENT_RESULT_N06 | 58 | | 2 (0)| 00:00:01 |
|* 30 | INDEX UNIQUE SCAN | PK_EMP_BASEINFO2 | 1 | 6 | 0 (0)| 00:00:01 |
| 31 | TABLE ACCESS BY INDEX ROWID | PFM_CYCLE_CATEGORY | 1 | 10 | 1 (0)| 00:00:01 |
|* 32 | INDEX UNIQUE SCAN | PK_PFM_CYCLE_CATEGORY | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">0)
2 - filter(ROWNUM<=20)
4 - filter(ROWNUM<=20)
9 - access("RELATION"."CHECKED_MAN_ID"="COLUMN_VALUE")
10 - filter(TO_NUMBER("RELATION"."IS_CHECK")=1)
13 - filter("YEAR">=2012 AND TO_NUMBER("IMPLEMENT_STATE")>=5 AND TO_NUMBER("IMPLEMENT_STATE")<=7 AND "YEAR"<=2013)
15 - access("BASEINFO"."CYCLE_ID"="CYCLE"."CYCLE_ID")
16 - access("BASEINFO"."IMPLEMENT_ID"="RELATION"."IMPLEMENT_ID")
20 - access("RELATION"."IMPLEMENT_RETION_ID"="REM"."IMPLEMENT_RETION_ID"(+))
21 - access("CHECKED_MAN_ID"="EMPLOYEE_ID"(+))
28 - filter("YEAR">=2012 AND "PIR"."RESULT_SOURCE"=2 AND "YEAR"<=2013)
29 - access("CHECKED_MAN_ID"="COLUMN_VALUE")
30 - access("CHECKED_MAN_ID"="EMPLOYEE_ID"(+))
32 - access("PIR"."CYCLE_ID"="CYCLE2"."CYCLE_ID")
Statistics
----------------------------------------------------------
50 recursive calls
0 db block gets
21107 consistent gets
966 physical reads
0 redo size
4512 bytes sent via SQL*Net to client
8348 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
9 sorts (memory)
0 sorts (disk)
20 rows processed
逻辑读从5452994降到21107，大大提升了性能。
911
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
