SQL优化从6分半到秒出

来源：互联网发布：淘宝商品品牌编辑：程序博客网时间：2024/06/05 21:06

开发找到我说有一条SQL查数量很慢，查具体的数据时很快，下面来看下具体的SQL。

alter session set statistics_level=all;

SELECT COUNT(1)
FROM (SELECT GG_PROJECT.PROJECT_ID, GG_PROJECT.EXECUTE_DEPARTMENT_CODE
FROM GG_PROJECT GG_PROJECT,
GG_SCHEDULE_PLAN_AUDIT GG_SCHEDULE_PLAN_AUDIT,
V_GG_PROJECT_INVEST V_GG_INVEST_PLAN,
V_PROJECT_MILESTONE_TRACEDATE V_PROJECT_MILESTONE_TASKDATE
WHERE (GG_SCHEDULE_PLAN_AUDIT.OBJECT_ID = GG_PROJECT.PROJECT_ID OR
GG_SCHEDULE_PLAN_AUDIT.OBJECT_ID =
GG_PROJECT.PROJECT_ID || '-C' || '2017')
AND GG_SCHEDULE_PLAN_AUDIT.OBJECT_ID =
V_PROJECT_MILESTONE_TASKDATE.OBJECT_ID
AND GG_SCHEDULE_PLAN_AUDIT.PLAN_TYPE =
V_PROJECT_MILESTONE_TASKDATE.PLAN_TYPE
AND GG_SCHEDULE_PLAN_AUDIT.PLAN_TYPE = 1
AND GG_PROJECT.PROJECT_ID = V_GG_INVEST_PLAN.PROJECT_ID
AND V_GG_INVEST_PLAN.INVEST_YEAR = 2017
AND GG_SCHEDULE_PLAN_AUDIT.INVEST_YEAR = 2017
AND ((GG_PROJECT.PROJECT_STATUS < 40 AND
GG_PROJECT.PROJECT_STATUS >= 25) OR
GG_PROJECT.PROJECT_STATUS = 170 OR
GG_PROJECT.PROJECT_STATUS = 171)
AND NVL(GG_PROJECT.PROJECT_FROM, 0) != 'mm'
AND GG_PROJECT.PROJECT_KIND = 1
) MAIN_TABLE;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:06:22.27 | 10M| | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:06:22.27 | 10M| | | |
|* 2 | HASH JOIN | | 1 | 1 | 1574 |00:06:22.27 | 10M| 1301K| 1301K| 1200K (0)|
| 3 | JOIN FILTER CREATE | :BF0000 | 1 | 1 | 1585 |00:06:22.02 | 10M| | | |
| 4 | NESTED LOOPS | | 1 | 1 | 1585 |00:06:22.01 | 10M| | | |
| 5 | NESTED LOOPS | | 1 | 1 | 1613 |00:00:01.35 | 44441 | | | |
| 6 | VIEW | V_PROJECT_MILESTONE_TRACEDATE | 1 | 11 | 8625 |00:00:01.22 | 27141 | | | |
|* 7 | FILTER | | 1 | | 8625 |00:00:01.22 | 27141 | | | |
| 8 | HASH GROUP BY | | 1 | 11 | 8625 |00:00:01.21 | 27141 | 8145K| 2230K| 2474K (0)|
|* 9 | HASH JOIN OUTER | | 1 | 9332 | 47258 |00:00:01.16 | 27141 | 6578K| 2421K| 5858K (0)|
|* 10 | HASH JOIN | | 1 | 9329 | 47258 |00:00:00.93 | 22310 | 1531K| 1361K| 1479K (0)|
| 11 | NESTED LOOPS OUTER | | 1 | 7588 | 8628 |00:00:00.26 | 13560 | | | |
|* 12 | TABLE ACCESS FULL | GG_SCHEDULE_PLAN | 1 | 7588 | 8628 |00:00:00.21 | 6800 | | | |
|* 13 | TABLE ACCESS BY INDEX ROWID | GG_SCHEDULE_PLAN_TRACE | 8628 | 1 | 2246 |00:00:00.05 | 6760 | | | |
|* 14 | INDEX UNIQUE SCAN | PK_GG_SCHEDULE_PLAN_TRACE | 8628 | 1 | 2246 |00:00:00.02 | 4495 | | | |
| 15 | TABLE ACCESS BY INDEX ROWID | GG_SCHEDULE_TASK | 1 | 31695 | 47295 |00:00:00.62 | 8750 | | | |
| 16 | BITMAP CONVERSION TO ROWIDS | | 1 | | 47295 |00:00:00.46 | 1246 | | | |
| 17 | BITMAP AND | | 1 | | 2 |00:00:00.45 | 1246 | | | |
| 18 | BITMAP OR | | 1 | | 6 |00:00:00.22 | 576 | | | |
| 19 | BITMAP CONVERSION FROM ROWIDS| | 1 | | 4 |00:00:00.04 | 113 | | | |
|* 20 | INDEX RANGE SCAN | PK_GG_SCHEDULE_TASK_CODE | 1 | | 56613 |00:00:00.02 | 113 | | | |
| 21 | BITMAP CONVERSION FROM ROWIDS| | 1 | | 4 |00:00:00.04 | 116 | | | |
|* 22 | INDEX RANGE SCAN | PK_GG_SCHEDULE_TASK_CODE | 1 | | 58009 |00:00:00.02 | 116 | | | |
| 23 | BITMAP CONVERSION FROM ROWIDS| | 1 | | 4 |00:00:00.04 | 114 | | | |
|* 24 | INDEX RANGE SCAN | PK_GG_SCHEDULE_TASK_CODE | 1 | | 56784 |00:00:00.02 | 114 | | | |
| 25 | BITMAP CONVERSION FROM ROWIDS| | 1 | | 1 |00:00:00.01 | 28 | | | |
|* 26 | INDEX RANGE SCAN | PK_GG_SCHEDULE_TASK_CODE | 1 | | 12849 |00:00:00.01 | 28 | | | |
| 27 | BITMAP CONVERSION FROM ROWIDS| | 1 | | 4 |00:00:00.04 | 101 | | | |
|* 28 | INDEX RANGE SCAN | PK_GG_SCHEDULE_TASK_CODE | 1 | | 53958 |00:00:00.02 | 101 | | | |
| 29 | BITMAP CONVERSION FROM ROWIDS| | 1 | | 4 |00:00:00.04 | 104 | | | |
|* 30 | INDEX RANGE SCAN | PK_GG_SCHEDULE_TASK_CODE | 1 | | 55966 |00:00:00.02 | 104 | | | |
| 31 | BITMAP CONVERSION FROM ROWIDS | | 1 | | 2 |00:00:00.22 | 670 | | | |
| 32 | SORT ORDER BY | | 1 | | 111K|00:00:00.19 | 670 | 3454K| 810K| 3070K (0)|
|* 33 | INDEX RANGE SCAN | INX_GG_SCHEDULE_TASK | 1 | | 111K|00:00:00.07 | 670 | | | |
| 34 | TABLE ACCESS BY INDEX ROWID | GG_SCHEDULE_TASK_TRACE | 1 | 70251 | 68042 |00:00:00.12 | 4831 | | | |
|* 35 | INDEX RANGE SCAN | PK_TRACE_ID | 1 | 70251 | 68042 |00:00:00.02 | 223 | | | |
|* 36 | INDEX RANGE SCAN | IDX_PLAN_AUDIT_STATUS | 8625 | 1 | 1613 |00:00:00.12 | 17300 | | | |
|* 37 | INDEX FAST FULL SCAN | IDX_GG_PROJECT_VIEW | 1613 | 14 | 1585 |00:06:20.65 | 10M| | | |
| 38 | VIEW | V_GG_PROJECT_INVEST | 1 | 113K| 1573 |00:00:00.24 | 5918 | | | |
| 39 | HASH UNIQUE | | 1 | 113K| 1573 |00:00:00.24 | 5918 | 1088K| 1088K| 4594K (0)|
| 40 | VIEW | | 1 | 113K| 2943 |00:00:00.24 | 5918 | | | |
| 41 | JOIN FILTER USE | :BF0000 | 1 | | 2943 |00:00:00.23 | 5918 | | | |
| 42 | UNION-ALL | | 1 | | 4082 |00:00:00.23 | 5918 | | | |
|* 43 | INDEX FAST FULL SCAN | IDX_GG_ANNUAL_INVESTMENT | 1 | 2005 | 1880 |00:00:00.01 | 35 | | | |
|* 44 | HASH JOIN | | 1 | 111K| 2202 |00:00:00.23 | 5883 | 2150K| 2024K| 3361K (0)|
|* 45 | INDEX RANGE SCAN | IDX_COMMISSION | 1 | 22389 | 19318 |00:00:00.01 | 103 | | | |
|* 46 | INDEX FAST FULL SCAN | IDX_GG_INVEST_PLAN | 1 | 111K| 95170 |00:00:00.18 | 5780 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("GG_PROJECT"."PROJECT_ID"="V_GG_INVEST_PLAN"."PROJECT_ID")
7 - filter(MAX("GG_SCHEDULE_PLAN"."PLAN_TYPE")=1)
9 - access("GG_SCHEDULE_TASK_TRACE"."TASK_ID"="GG_SCHEDULE_TASK"."TASK_ID")
10 - access("GG_SCHEDULE_TASK"."PLAN_ID"="GG_SCHEDULE_PLAN"."PLAN_ID")
12 - filter("GG_SCHEDULE_PLAN"."PLAN_TYPE"=1)
13 - filter("GG_SCHEDULE_PLAN_TRACE"."PLAN_TYPE"=1)
14 - access("GG_SCHEDULE_PLAN_TRACE"."TRACE_ID"="GG_SCHEDULE_PLAN"."TRACE_ID")
20 - access("GG_SCHEDULE_TASK"."TASK_CODE"='10')
22 - access("GG_SCHEDULE_TASK"."TASK_CODE"='11')
24 - access("GG_SCHEDULE_TASK"."TASK_CODE"='12')
26 - access("GG_SCHEDULE_TASK"."TASK_CODE"='13')
28 - access("GG_SCHEDULE_TASK"."TASK_CODE"='7')
30 - access("GG_SCHEDULE_TASK"."TASK_CODE"='9')
33 - access("GG_SCHEDULE_TASK"."TASK_TYPE"=1)
filter("GG_SCHEDULE_TASK"."TASK_TYPE"=1)
35 - access("GG_SCHEDULE_TASK_TRACE"."TASK_TYPE"=1)
36 - access("GG_SCHEDULE_PLAN_AUDIT"."OBJECT_ID"="V_PROJECT_MILESTONE_TASKDATE"."OBJECT_ID" AND "GG_SCHEDULE_PLAN_AUDIT"."INVEST_YEAR"=2017)
filter(("GG_SCHEDULE_PLAN_AUDIT"."INVEST_YEAR"=2017 AND TO_NUMBER("GG_SCHEDULE_PLAN_AUDIT"."PLAN_TYPE")=1 AND
"V_PROJECT_MILESTONE_TASKDATE"."PLAN_TYPE"=TO_NUMBER("GG_SCHEDULE_PLAN_AUDIT"."PLAN_TYPE")))
37 - filter(("GG_PROJECT"."PROJECT_KIND"=1 AND (INTERNAL_FUNCTION("GG_PROJECT"."PROJECT_STATUS") OR ("GG_PROJECT"."PROJECT_STATUS"<40 AND
"GG_PROJECT"."PROJECT_STATUS">=25)) AND "GG_PROJECT"."SYS_NC00132$"<>'mm' AND ("GG_SCHEDULE_PLAN_AUDIT"."OBJECT_ID"="GG_PROJECT"."PROJECT_ID" OR
"GG_SCHEDULE_PLAN_AUDIT"."OBJECT_ID"="GG_PROJECT"."PROJECT_ID"||'-C'||'2017')))
43 - filter(("PI"."FEE_LEVEL"=1 AND "PI"."PLAN_KIND"=1 AND "PI"."INVEST_YEAR"=2017))
44 - access("PS"."PROJECT_ID"="P"."PROJECT_ID")
45 - access("PS"."PROJECT_TYPE"=1)
46 - filter(("P"."AUDIT_STATUS"=3 AND "P"."INVEST_YEAR"=2017))
Note
-----
- dynamic sampling used for this statement (level=2)

这里的问题点有两点：
1.dynamic sampling used for this statement (level=2) 说明有表没有收集统计信息，所以所有的表都收集一遍。收集之后执行计划可能会变。
--收集之后，执行计划没有变化，还是这么慢。
2.执行计划中显示消耗时间是在第4行nested loops上，花费6分22秒，时间耗在34行INDEX FAST FULL SCAN IDX_GG_PROJECT_VIEW上。
--这个索引上是多个字段的联合索引，果断干掉，之后执行计划变了，时间是13s。
------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:12.92 | 7110K| | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:12.92 | 7110K| | | |
| 2 | NESTED LOOPS | | 1 | 1 | 1574 |00:00:12.92 | 7110K| | | |
| 3 | NESTED LOOPS | | 1 | 2679 | 1586 |00:00:12.89 | 7108K| | | |
| 4 | MERGE JOIN CARTESIAN | | 1 | 2679 | 3593K|00:00:02.56 | 50358 | | | |
| 5 | NESTED LOOPS | | 1 | 1 | 1613 |00:00:00.91 | 44440 | | | |
| 6 | VIEW | V_PROJECT_MILESTONE_TRACEDATE | 1 | 11 | 8625 |00:00:00.86 | 27140 | | | |
|* 7 | FILTER | | 1 | | 8625 |00:00:00.86 | 27140 | | | |
| 8 | HASH GROUP BY | | 1 | 11 | 8625 |00:00:00.85 | 27140 | 8145K| 2230K| 2515K (0)|
|* 9 | HASH JOIN OUTER | | 1 | 9332 | 47258 |00:00:00.82 | 27140 | 6578K| 2421K| 5800K (0)|
|* 10 | HASH JOIN | | 1 | 9329 | 47258 |00:00:00.65 | 22309 | 1531K| 1361K| 1525K (0)|
| 11 | NESTED LOOPS OUTER | | 1 | 7588 | 8628 |00:00:00.12 | 13560 | | | |
|* 12 | TABLE ACCESS FULL | GG_SCHEDULE_PLAN | 1 | 7588 | 8628 |00:00:00.08 | 6800 | | | |
|* 13 | TABLE ACCESS BY INDEX ROWID | GG_SCHEDULE_PLAN_TRACE | 8628 | 1 | 2246 |00:00:00.04 | 6760 | | | |
|* 14 | INDEX UNIQUE SCAN | PK_GG_SCHEDULE_PLAN_TRACE | 8628 | 1 | 2246 |00:00:00.02 | 4495 | | | |
| 15 | TABLE ACCESS BY INDEX ROWID | GG_SCHEDULE_TASK | 1 | 31695 | 47295 |00:00:00.49 | 8749 | | | |
| 16 | BITMAP CONVERSION TO ROWIDS | | 1 | | 47295 |00:00:00.43 | 1246 | | | |
| 17 | BITMAP AND | | 1 | | 2 |00:00:00.42 | 1246 | | | |
| 18 | BITMAP OR | | 1 | | 6 |00:00:00.22 | 576 | | | |
| 19 | BITMAP CONVERSION FROM ROWIDS| | 1 | | 4 |00:00:00.04 | 113 | | | |
|* 20 | INDEX RANGE SCAN | PK_GG_SCHEDULE_TASK_CODE | 1 | | 56613 |00:00:00.02 | 113 | | | |
| 21 | BITMAP CONVERSION FROM ROWIDS| | 1 | | 4 |00:00:00.04 | 116 | | | |
|* 22 | INDEX RANGE SCAN | PK_GG_SCHEDULE_TASK_CODE | 1 | | 58009 |00:00:00.02 | 116 | | | |
| 23 | BITMAP CONVERSION FROM ROWIDS| | 1 | | 4 |00:00:00.04 | 114 | | | |
|* 24 | INDEX RANGE SCAN | PK_GG_SCHEDULE_TASK_CODE | 1 | | 56784 |00:00:00.02 | 114 | | | |
| 25 | BITMAP CONVERSION FROM ROWIDS| | 1 | | 1 |00:00:00.01 | 28 | | | |
|* 26 | INDEX RANGE SCAN | PK_GG_SCHEDULE_TASK_CODE | 1 | | 12849 |00:00:00.01 | 28 | | | |
| 27 | BITMAP CONVERSION FROM ROWIDS| | 1 | | 4 |00:00:00.04 | 101 | | | |
|* 28 | INDEX RANGE SCAN | PK_GG_SCHEDULE_TASK_CODE | 1 | | 53958 |00:00:00.02 | 101 | | | |
| 29 | BITMAP CONVERSION FROM ROWIDS| | 1 | | 4 |00:00:00.04 | 104 | | | |
|* 30 | INDEX RANGE SCAN | PK_GG_SCHEDULE_TASK_CODE | 1 | | 55966 |00:00:00.02 | 104 | | | |
| 31 | BITMAP CONVERSION FROM ROWIDS | | 1 | | 2 |00:00:00.21 | 670 | | | |
| 32 | SORT ORDER BY | | 1 | | 111K|00:00:00.18 | 670 | 3454K| 810K| 3070K (0)|
|* 33 | INDEX RANGE SCAN | INX_GG_SCHEDULE_TASK | 1 | | 111K|00:00:00.05 | 670 | | | |
| 34 | TABLE ACCESS BY INDEX ROWID | GG_SCHEDULE_TASK_TRACE | 1 | 70251 | 68042 |00:00:00.08 | 4831 | | | |
|* 35 | INDEX RANGE SCAN | PK_TRACE_ID | 1 | 70251 | 68042 |00:00:00.02 | 223 | | | |
|* 36 | INDEX RANGE SCAN | IDX_PLAN_AUDIT_STATUS | 8625 | 1 | 1613 |00:00:00.04 | 17300 | | | |
| 37 | BUFFER SORT | | 1613 | 113K| 3593K|00:00:00.95 | 5918 | 133K| 133K| 118K (0)|
| 38 | VIEW | V_GG_PROJECT_INVEST | 1 | 113K| 2228 |00:00:00.18 | 5918 | | | |
| 39 | HASH UNIQUE | | 1 | 113K| 2228 |00:00:00.18 | 5918 | 1160K| 1160K| 4604K (0)|
| 40 | VIEW | | 1 | 113K| 4082 |00:00:00.18 | 5918 | | | |
| 41 | UNION-ALL | | 1 | | 4082 |00:00:00.18 | 5918 | | | |
|* 42 | INDEX FAST FULL SCAN | IDX_GG_ANNUAL_INVESTMENT | 1 | 2005 | 1880 |00:00:00.01 | 35 | | | |
|* 43 | HASH JOIN | | 1 | 111K| 2202 |00:00:00.18 | 5883 | 2150K| 2024K| 3372K (0)|
|* 44 | INDEX RANGE SCAN | IDX_COMMISSION | 1 | 22389 | 19318 |00:00:00.01 | 103 | | | |
|* 45 | INDEX FAST FULL SCAN | IDX_GG_INVEST_PLAN | 1 | 111K| 95170 |00:00:00.12 | 5780 | | | |
|* 46 | INDEX UNIQUE SCAN | PK_GG_PROJECT | 3593K| 1 | 1586 |00:00:09.12 | 7058K| | | |
|* 47 | TABLE ACCESS BY INDEX ROWID | GG_PROJECT | 1586 | 1 | 1574 |00:00:00.02 | 1578 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - filter(MAX("GG_SCHEDULE_PLAN"."PLAN_TYPE")=1)
9 - access("GG_SCHEDULE_TASK_TRACE"."TASK_ID"="GG_SCHEDULE_TASK"."TASK_ID")
10 - access("GG_SCHEDULE_TASK"."PLAN_ID"="GG_SCHEDULE_PLAN"."PLAN_ID")
12 - filter("GG_SCHEDULE_PLAN"."PLAN_TYPE"=1)
13 - filter("GG_SCHEDULE_PLAN_TRACE"."PLAN_TYPE"=1)
14 - access("GG_SCHEDULE_PLAN_TRACE"."TRACE_ID"="GG_SCHEDULE_PLAN"."TRACE_ID")
20 - access("GG_SCHEDULE_TASK"."TASK_CODE"='10')
22 - access("GG_SCHEDULE_TASK"."TASK_CODE"='11')
24 - access("GG_SCHEDULE_TASK"."TASK_CODE"='12')
26 - access("GG_SCHEDULE_TASK"."TASK_CODE"='13')
28 - access("GG_SCHEDULE_TASK"."TASK_CODE"='7')
30 - access("GG_SCHEDULE_TASK"."TASK_CODE"='9')
33 - access("GG_SCHEDULE_TASK"."TASK_TYPE"=1)
filter("GG_SCHEDULE_TASK"."TASK_TYPE"=1)
35 - access("GG_SCHEDULE_TASK_TRACE"."TASK_TYPE"=1)
36 - access("GG_SCHEDULE_PLAN_AUDIT"."OBJECT_ID"="V_PROJECT_MILESTONE_TASKDATE"."OBJECT_ID" AND "GG_SCHEDULE_PLAN_AUDIT"."INVEST_YEAR"=2017)
filter(("GG_SCHEDULE_PLAN_AUDIT"."INVEST_YEAR"=2017 AND TO_NUMBER("GG_SCHEDULE_PLAN_AUDIT"."PLAN_TYPE")=1 AND
"V_PROJECT_MILESTONE_TASKDATE"."PLAN_TYPE"=TO_NUMBER("GG_SCHEDULE_PLAN_AUDIT"."PLAN_TYPE")))
42 - filter(("PI"."FEE_LEVEL"=1 AND "PI"."PLAN_KIND"=1 AND "PI"."INVEST_YEAR"=2017))
43 - access("PS"."PROJECT_ID"="P"."PROJECT_ID")
44 - access("PS"."PROJECT_TYPE"=1)
45 - filter(("P"."AUDIT_STATUS"=3 AND "P"."INVEST_YEAR"=2017))
46 - access("GG_PROJECT"."PROJECT_ID"="V_GG_INVEST_PLAN"."PROJECT_ID")
filter(("GG_SCHEDULE_PLAN_AUDIT"."OBJECT_ID"="GG_PROJECT"."PROJECT_ID" OR
"GG_SCHEDULE_PLAN_AUDIT"."OBJECT_ID"="GG_PROJECT"."PROJECT_ID"||'-C'||'2017'))
47 - filter(("GG_PROJECT"."PROJECT_KIND"=1 AND (INTERNAL_FUNCTION("GG_PROJECT"."PROJECT_STATUS") OR ("GG_PROJECT"."PROJECT_STATUS"<40 AND
"GG_PROJECT"."PROJECT_STATUS">=25)) AND NVL("PROJECT_FROM",'0')<>'mm'))

TASK_CODE 这个也需要做一下直方图。

exec dbms_stats.gather_table_stats(user,'GG_SCHEDULE_TASK',cascade => true,degree=>4,method_opt => 'for columns size auto TASK_CODE ',no_invalidate=>FALSE);
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:13.62 | 7111K| | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:13.62 | 7111K| | | |
| 2 | NESTED LOOPS | | 1 | 1 | 1574 |00:00:13.62 | 7111K| | | |
| 3 | NESTED LOOPS | | 1 | 4411 | 1586 |00:00:13.60 | 7109K| | | |
| 4 | MERGE JOIN CARTESIAN | | 1 | 4411 | 3593K|00:00:02.98 | 51409 | | | |
|* 5 | HASH JOIN | | 1 | 1 | 1613 |00:00:01.29 | 45491 | 1494K| 1494K| 1508K (0)|
| 6 | VIEW | V_PROJECT_MILESTONE_TRACEDATE | 1 | 92 | 8625 |00:00:01.27 | 45034 | | | |
|* 7 | FILTER | | 1 | | 8625 |00:00:01.27 | 45034 | | | |
| 8 | HASH GROUP BY | | 1 | 92 | 8625 |00:00:01.27 | 45034 | 8145K| 2230K| 2621K (0)|
|* 9 | HASH JOIN OUTER | | 1 | 11383 | 47258 |00:00:01.24 | 45034 | 6578K| 2421K| 5823K (0)|
|* 10 | HASH JOIN | | 1 | 11380 | 47258 |00:00:01.08 | 40203 | 1531K| 1361K| 1519K (0)|
| 11 | NESTED LOOPS OUTER | | 1 | 9183 | 8628 |00:00:00.12 | 13560 | | | |
|* 12 | TABLE ACCESS FULL | GG_SCHEDULE_PLAN | 1 | 9183 | 8628 |00:00:00.07 | 6800 | | | |
|* 13 | TABLE ACCESS BY INDEX ROWID| GG_SCHEDULE_PLAN_TRACE | 8628 | 1 | 2246 |00:00:00.04 | 6760 | | | |
|* 14 | INDEX UNIQUE SCAN | PK_GG_SCHEDULE_PLAN_TRACE | 8628 | 1 | 2246 |00:00:00.02 | 4495 | | | |
|* 15 | VIEW | index$_join$_007 | 1 | 32960 | 47295 |00:00:00.92 | 26643 | | | |
|* 16 | HASH JOIN | | 1 | | 47295 |00:00:00.91 | 26643 | 4345K| 1449K| 5044K (0)|
|* 17 | HASH JOIN | | 1 | | 47295 |00:00:00.63 | 1242 | 16M| 4150K| 15M (0)|
| 18 | INLIST ITERATOR | | 1 | | 294K|00:00:00.24 | 572 | | | |
|* 19 | INDEX RANGE SCAN | PK_GG_SCHEDULE_TASK_CODE | 6 | 32960 | 294K|00:00:00.12 | 572 | | | |
|* 20 | INDEX RANGE SCAN | INX_GG_SCHEDULE_TASK | 1 | 32960 | 111K|00:00:00.04 | 670 | | | |
|* 21 | INDEX FAST FULL SCAN | INX_GG_TASK_TYPE | 1 | 32960 | 111K|00:00:00.19 | 25401 | | | |
| 22 | TABLE ACCESS BY INDEX ROWID | GG_SCHEDULE_TASK_TRACE | 1 | 64916 | 68042 |00:00:00.08 | 4831 | | | |
|* 23 | INDEX RANGE SCAN | PK_TRACE_ID | 1 | 64916 | 68042 |00:00:00.02 | 223 | | | |
|* 24 | INDEX FAST FULL SCAN | IDX_PLAN_AUDIT_STATUS | 1 | 236 | 1655 |00:00:00.01 | 457 | | | |
| 25 | BUFFER SORT | | 1613 | 22761 | 3593K|00:00:00.95 | 5918 | 133K| 133K| 118K (0)|
| 26 | VIEW | V_GG_PROJECT_INVEST | 1 | 22761 | 2228 |00:00:00.17 | 5918 | | | |
| 27 | HASH UNIQUE | | 1 | 22761 | 2228 |00:00:00.16 | 5918 | 1160K| 1160K| 2734K (0)|
| 28 | VIEW | | 1 | 22761 | 4082 |00:00:00.16 | 5918 | | | |
| 29 | UNION-ALL | | 1 | | 4082 |00:00:00.16 | 5918 | | | |
|* 30 | INDEX FAST FULL SCAN | IDX_GG_ANNUAL_INVESTMENT | 1 | 1981 | 1880 |00:00:00.01 | 35 | | | |
|* 31 | HASH JOIN | | 1 | 20780 | 2202 |00:00:00.16 | 5883 | 2150K| 2024K| 2352K (0)|
|* 32 | INDEX RANGE SCAN | IDX_COMMISSION | 1 | 20202 | 19318 |00:00:00.01 | 103 | | | |
|* 33 | INDEX FAST FULL SCAN | IDX_GG_INVEST_PLAN | 1 | 113K| 95170 |00:00:00.11 | 5780 | | | |
|* 34 | INDEX UNIQUE SCAN | PK_GG_PROJECT | 3593K| 1 | 1586 |00:00:09.39 | 7058K| | | |
|* 35 | TABLE ACCESS BY INDEX ROWID | GG_PROJECT | 1586 | 1 | 1574 |00:00:00.01 | 1294 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("GG_SCHEDULE_PLAN_AUDIT"."OBJECT_ID"="V_PROJECT_MILESTONE_TASKDATE"."OBJECT_ID" AND
"V_PROJECT_MILESTONE_TASKDATE"."PLAN_TYPE"=TO_NUMBER("GG_SCHEDULE_PLAN_AUDIT"."PLAN_TYPE"))
7 - filter(MAX("GG_SCHEDULE_PLAN"."PLAN_TYPE")=1)
9 - access("GG_SCHEDULE_TASK_TRACE"."TASK_ID"="GG_SCHEDULE_TASK"."TASK_ID")
10 - access("GG_SCHEDULE_TASK"."PLAN_ID"="GG_SCHEDULE_PLAN"."PLAN_ID")
12 - filter("GG_SCHEDULE_PLAN"."PLAN_TYPE"=1)
13 - filter("GG_SCHEDULE_PLAN_TRACE"."PLAN_TYPE"=1)
14 - access("GG_SCHEDULE_PLAN_TRACE"."TRACE_ID"="GG_SCHEDULE_PLAN"."TRACE_ID")
15 - filter(("GG_SCHEDULE_TASK"."TASK_CODE"='10' OR "GG_SCHEDULE_TASK"."TASK_CODE"='11' OR "GG_SCHEDULE_TASK"."TASK_CODE"='12' OR
"GG_SCHEDULE_TASK"."TASK_CODE"='13' OR "GG_SCHEDULE_TASK"."TASK_CODE"='7' OR "GG_SCHEDULE_TASK"."TASK_CODE"='9'))
16 - access(ROWID=ROWID)
17 - access(ROWID=ROWID)
19 - access(("GG_SCHEDULE_TASK"."TASK_CODE"='10' OR "GG_SCHEDULE_TASK"."TASK_CODE"='11' OR "GG_SCHEDULE_TASK"."TASK_CODE"='12' OR
"GG_SCHEDULE_TASK"."TASK_CODE"='13' OR "GG_SCHEDULE_TASK"."TASK_CODE"='7' OR "GG_SCHEDULE_TASK"."TASK_CODE"='9'))
20 - access("GG_SCHEDULE_TASK"."TASK_TYPE"=1)
21 - filter("GG_SCHEDULE_TASK"."TASK_TYPE"=1)
23 - access("GG_SCHEDULE_TASK_TRACE"."TASK_TYPE"=1)
24 - filter(("GG_SCHEDULE_PLAN_AUDIT"."INVEST_YEAR"=2017 AND TO_NUMBER("GG_SCHEDULE_PLAN_AUDIT"."PLAN_TYPE")=1))
30 - filter(("PI"."INVEST_YEAR"=2017 AND "PI"."PLAN_KIND"=1 AND "PI"."FEE_LEVEL"=1))
31 - access("PS"."PROJECT_ID"="P"."PROJECT_ID")
32 - access("PS"."PROJECT_TYPE"=1)
33 - filter(("P"."INVEST_YEAR"=2017 AND "P"."AUDIT_STATUS"=3))
34 - access("GG_PROJECT"."PROJECT_ID"="V_GG_INVEST_PLAN"."PROJECT_ID")
filter(("GG_SCHEDULE_PLAN_AUDIT"."OBJECT_ID"="GG_PROJECT"."PROJECT_ID" OR
"GG_SCHEDULE_PLAN_AUDIT"."OBJECT_ID"="GG_PROJECT"."PROJECT_ID"||'-C'||'2017'))
35 - filter(("GG_PROJECT"."PROJECT_KIND"=1 AND (("GG_PROJECT"."PROJECT_STATUS"<40 AND "GG_PROJECT"."PROJECT_STATUS">=25) OR

INTERNAL_FUNCTION("GG_PROJECT"."PROJECT_STATUS")) AND NVL("PROJECT_FROM",'0')<>'mm'))

create index ind_pspa_PLAN_TYPE on GG_SCHEDULE_PLAN_AUDIT(to_number(PLAN_TYPE)) nologging;
exec dbms_stats.gather_table_stats(user,'GG_SCHEDULE_PLAN_AUDIT',cascade => true,degree=>4,method_opt => 'for columns (to_number(PLAN_TYPE)) size skewonly',no_invalidate=>FALSE);

可以看到时间消耗在第3行，第4行消耗了3s,且评估出来的行数和实际返回的差异很大。第4行出现笛卡尔积的原因是第5行评估错了，

本来是返回1613行数据，评估返回1条。第5行有谓词信息"V_PROJECT_MILESTONE_TASKDATE"."PLAN_TYPE"=TO_NUMBER("GG_SCHEDULE_PLAN_AUDIT"."PLAN_TYPE")
有隐式转换导致，把SQL修改一下to_number(GG_SCHEDULE_PLAN_AUDIT.PLAN_TYPE) = V_PROJECT_MILESTONE_TASKDATE.PLAN_TYPE，然后收集一下to_number(PLAN_TYPE)的直方图。
SELECT COUNT(1)
FROM (SELECT GG_PROJECT.PROJECT_ID, GG_PROJECT.EXECUTE_DEPARTMENT_CODE
FROM GG_PROJECT GG_PROJECT,
GG_SCHEDULE_PLAN_AUDIT GG_SCHEDULE_PLAN_AUDIT,
V_GG_PROJECT_INVEST V_GG_INVEST_PLAN,
V_PROJECT_MILESTONE_TRACEDATE V_PROJECT_MILESTONE_TASKDATE
WHERE (GG_SCHEDULE_PLAN_AUDIT.OBJECT_ID = GG_PROJECT.PROJECT_ID OR
GG_SCHEDULE_PLAN_AUDIT.OBJECT_ID =
GG_PROJECT.PROJECT_ID || '-C' || '2017')
AND GG_SCHEDULE_PLAN_AUDIT.OBJECT_ID =
V_PROJECT_MILESTONE_TASKDATE.OBJECT_ID
AND to_number(GG_SCHEDULE_PLAN_AUDIT.PLAN_TYPE) =
V_PROJECT_MILESTONE_TASKDATE.PLAN_TYPE
AND GG_SCHEDULE_PLAN_AUDIT.PLAN_TYPE = 1
AND GG_PROJECT.PROJECT_ID = V_GG_INVEST_PLAN.PROJECT_ID
AND V_GG_INVEST_PLAN.INVEST_YEAR = 2017
AND GG_SCHEDULE_PLAN_AUDIT.INVEST_YEAR = 2017
AND ((GG_PROJECT.PROJECT_STATUS < 40 AND
GG_PROJECT.PROJECT_STATUS >= 25) OR
GG_PROJECT.PROJECT_STATUS = 170 OR
GG_PROJECT.PROJECT_STATUS = 171)
AND NVL(GG_PROJECT.PROJECT_FROM, 0) != 'mm'
AND GG_PROJECT.PROJECT_KIND = 1
) MAIN_TABLE;
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:04.36 | 179K| | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:04.36 | 179K| | | |
| 2 | CONCATENATION | | 1 | | 1574 |00:00:04.36 | 179K| | | |
|* 3 | HASH JOIN | | 1 | 15 | 1275 |00:00:03.23 | 124K| 867K| 867K| 1354K (0)|
|* 4 | HASH JOIN | | 1 | 152 | 1275 |00:00:03.03 | 118K| 914K| 914K| 1293K (0)|
|* 5 | HASH JOIN | | 1 | 18 | 1613 |00:00:01.57 | 45891 | 1087K| 1087K| 1366K (0)|
|* 6 | TABLE ACCESS FULL | GG_SCHEDULE_PLAN_AUDIT | 1 | 225 | 1655 |00:00:00.03 | 857 | | | |
| 7 | VIEW | V_PROJECT_MILESTONE_TRACEDATE | 1 | 9183 | 8625 |00:00:01.53 | 45034 | | | |
| 8 | HASH GROUP BY | | 1 | 9183 | 8625 |00:00:01.52 | 45034 | 8145K| 2230K| 2617K (0)|
|* 9 | HASH JOIN OUTER | | 1 | 11387 | 47258 |00:00:01.49 | 45034 | 6578K| 2421K| 5812K (0)|
|* 10 | HASH JOIN | | 1 | 11384 | 47258 |00:00:01.30 | 40203 | 1531K| 1361K| 1521K (0)|
| 11 | NESTED LOOPS OUTER | | 1 | 9183 | 8628 |00:00:00.25 | 13560 | | | |
|* 12 | TABLE ACCESS FULL | GG_SCHEDULE_PLAN | 1 | 9183 | 8628 |00:00:00.19 | 6800 | | | |
|* 13 | TABLE ACCESS BY INDEX ROWID | GG_SCHEDULE_PLAN_TRACE | 8628 | 1 | 2246 |00:00:00.05 | 6760 | | | |
|* 14 | INDEX UNIQUE SCAN | PK_GG_SCHEDULE_PLAN_TRACE | 8628 | 1 | 2246 |00:00:00.02 | 4495 | | | |
|* 15 | VIEW | index$_join$_007 | 1 | 33016 | 47295 |00:00:01.01 | 26643 | | | |
|* 16 | HASH JOIN | | 1 | | 47295 |00:00:00.99 | 26643 | 4345K| 1449K| 5044K (0)|
|* 17 | HASH JOIN | | 1 | | 47295 |00:00:00.52 | 1242 | 16M| 4150K| 15M (0)|
| 18 | INLIST ITERATOR | | 1 | | 294K|00:00:00.20 | 572 | | | |
|* 19 | INDEX RANGE SCAN | PK_GG_SCHEDULE_TASK_CODE | 6 | 33016 | 294K|00:00:00.10 | 572 | | | |
|* 20 | INDEX RANGE SCAN | INX_GG_SCHEDULE_TASK | 1 | 33016 | 111K|00:00:00.04 | 670 | | | |
|* 21 | INDEX FAST FULL SCAN | INX_GG_TASK_TYPE | 1 | 33016 | 111K|00:00:00.39 | 25401 | | | |
| 22 | TABLE ACCESS BY INDEX ROWID | GG_SCHEDULE_TASK_TRACE | 1 | 67391 | 68042 |00:00:00.11 | 4831 | | | |
|* 23 | INDEX RANGE SCAN | PK_TRACE_ID | 1 | 67391 | 68042 |00:00:00.02 | 223 | | | |
|* 24 | TABLE ACCESS FULL | GG_PROJECT | 1 | 231K| 301K|00:00:01.26 | 72835 | | | |
| 25 | VIEW | V_GG_PROJECT_INVEST | 1 | 22761 | 2228 |00:00:00.20 | 5918 | | | |
| 26 | HASH UNIQUE | | 1 | 22761 | 2228 |00:00:00.20 | 5918 | 1160K| 1160K| 2712K (0)|
| 27 | VIEW | | 1 | 22761 | 4082 |00:00:00.20 | 5918 | | | |
| 28 | UNION-ALL | | 1 | | 4082 |00:00:00.20 | 5918 | | | |
|* 29 | INDEX FAST FULL SCAN | IDX_GG_ANNUAL_INVESTMENT | 1 | 1981 | 1880 |00:00:00.01 | 35 | | | |
|* 30 | HASH JOIN | | 1 | 20780 | 2202 |00:00:00.20 | 5883 | 2150K| 2024K| 2350K (0)|
|* 31 | INDEX RANGE SCAN | IDX_COMMISSION | 1 | 20202 | 19318 |00:00:00.01 | 103 | | | |
|* 32 | INDEX FAST FULL SCAN | IDX_GG_INVEST_PLAN | 1 | 113K| 95170 |00:00:00.14 | 5780 | | | |
|* 33 | HASH JOIN | | 1 | 1 | 299 |00:00:01.12 | 55181 | 812K| 812K| 1269K (0)|
| 34 | NESTED LOOPS | | 1 | 1 | 310 |00:00:00.95 | 49263 | | | |
| 35 | NESTED LOOPS | | 1 | 18 | 328 |00:00:00.95 | 48936 | | | |
|* 36 | HASH JOIN | | 1 | 18 | 1613 |00:00:00.94 | 45891 | 1087K| 1087K| 1362K (0)|
|* 37 | TABLE ACCESS FULL | GG_SCHEDULE_PLAN_AUDIT | 1 | 225 | 1655 |00:00:00.01 | 857 | | | |
| 38 | VIEW | V_PROJECT_MILESTONE_TRACEDATE | 1 | 9183 | 8625 |00:00:00.93 | 45034 | | | |
| 39 | HASH GROUP BY | | 1 | 9183 | 8625 |00:00:00.92 | 45034 | 8145K| 2230K| 2616K (0)|
|* 40 | HASH JOIN OUTER | | 1 | 11387 | 47258 |00:00:00.90 | 45034 | 6578K| 2421K| 5816K (0)|
|* 41 | HASH JOIN | | 1 | 11384 | 47258 |00:00:00.74 | 40203 | 1531K| 1361K| 1521K (0)|
| 42 | NESTED LOOPS OUTER | | 1 | 9183 | 8628 |00:00:00.08 | 13560 | | | |
|* 43 | TABLE ACCESS FULL | GG_SCHEDULE_PLAN | 1 | 9183 | 8628 |00:00:00.06 | 6800 | | | |
|* 44 | TABLE ACCESS BY INDEX ROWID| GG_SCHEDULE_PLAN_TRACE | 8628 | 1 | 2246 |00:00:00.02 | 6760 | | | |
|* 45 | INDEX UNIQUE SCAN | PK_GG_SCHEDULE_PLAN_TRACE | 8628 | 1 | 2246 |00:00:00.01 | 4495 | | | |
|* 46 | VIEW | index$_join$_007 | 1 | 33016 | 47295 |00:00:00.63 | 26643 | | | |
|* 47 | HASH JOIN | | 1 | | 47295 |00:00:00.61 | 26643 | 4345K| 1449K| 5044K (0)|
|* 48 | HASH JOIN | | 1 | | 47295 |00:00:00.34 | 1242 | 16M| 4150K| 15M (0)|
| 49 | INLIST ITERATOR | | 1 | | 294K|00:00:00.11 | 572 | | | |
|* 50 | INDEX RANGE SCAN | PK_GG_SCHEDULE_TASK_CODE | 6 | 33016 | 294K|00:00:00.06 | 572 | | | |
|* 51 | INDEX RANGE SCAN | INX_GG_SCHEDULE_TASK | 1 | 33016 | 111K|00:00:00.03 | 670 | | | |
|* 52 | INDEX FAST FULL SCAN | INX_GG_TASK_TYPE | 1 | 33016 | 111K|00:00:00.19 | 25401 | | | |
| 53 | TABLE ACCESS BY INDEX ROWID | GG_SCHEDULE_TASK_TRACE | 1 | 67391 | 68042 |00:00:00.08 | 4831 | | | |
|* 54 | INDEX RANGE SCAN | PK_TRACE_ID | 1 | 67391 | 68042 |00:00:00.02 | 223 | | | |
|* 55 | INDEX UNIQUE SCAN | PK_GG_PROJECT | 1613 | 1 | 328 |00:00:00.01 | 3045 | | | |
|* 56 | TABLE ACCESS BY INDEX ROWID | GG_PROJECT | 328 | 1 | 310 |00:00:00.01 | 327 | | | |
| 57 | VIEW | V_GG_PROJECT_INVEST | 1 | 22761 | 2228 |00:00:00.17 | 5918 | | | |
| 58 | HASH UNIQUE | | 1 | 22761 | 2228 |00:00:00.17 | 5918 | 1160K| 1160K| 2706K (0)|
| 59 | VIEW | | 1 | 22761 | 4082 |00:00:00.16 | 5918 | | | |
| 60 | UNION-ALL | | 1 | | 4082 |00:00:00.16 | 5918 | | | |
|* 61 | INDEX FAST FULL SCAN | IDX_GG_ANNUAL_INVESTMENT | 1 | 1981 | 1880 |00:00:00.01 | 35 | | | |
|* 62 | HASH JOIN | | 1 | 20780 | 2202 |00:00:00.16 | 5883 | 2150K| 2024K| 2348K (0)|
|* 63 | INDEX RANGE SCAN | IDX_COMMISSION | 1 | 20202 | 19318 |00:00:00.01 | 103 | | | |
|* 64 | INDEX FAST FULL SCAN | IDX_GG_INVEST_PLAN | 1 | 113K| 95170 |00:00:00.11 | 5780 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("GG_PROJECT"."PROJECT_ID"="V_GG_INVEST_PLAN"."PROJECT_ID")
4 - access("GG_SCHEDULE_PLAN_AUDIT"."OBJECT_ID"="GG_PROJECT"."PROJECT_ID"||'-C'||'2017')
5 - access("GG_SCHEDULE_PLAN_AUDIT"."OBJECT_ID"="V_PROJECT_MILESTONE_TASKDATE"."OBJECT_ID" AND
TO_NUMBER("PLAN_TYPE")="V_PROJECT_MILESTONE_TASKDATE"."PLAN_TYPE")
6 - filter(("GG_SCHEDULE_PLAN_AUDIT"."INVEST_YEAR"=2017 AND TO_NUMBER("PLAN_TYPE")=1))
9 - access("GG_SCHEDULE_TASK_TRACE"."TASK_ID"="GG_SCHEDULE_TASK"."TASK_ID")
10 - access("GG_SCHEDULE_TASK"."PLAN_ID"="GG_SCHEDULE_PLAN"."PLAN_ID")
12 - filter("GG_SCHEDULE_PLAN"."PLAN_TYPE"=1)
13 - filter("GG_SCHEDULE_PLAN_TRACE"."PLAN_TYPE"=1)
14 - access("GG_SCHEDULE_PLAN_TRACE"."TRACE_ID"="GG_SCHEDULE_PLAN"."TRACE_ID")
15 - filter(("GG_SCHEDULE_TASK"."TASK_CODE"='10' OR "GG_SCHEDULE_TASK"."TASK_CODE"='11' OR "GG_SCHEDULE_TASK"."TASK_CODE"='12' OR
"GG_SCHEDULE_TASK"."TASK_CODE"='13' OR "GG_SCHEDULE_TASK"."TASK_CODE"='7' OR "GG_SCHEDULE_TASK"."TASK_CODE"='9'))
16 - access(ROWID=ROWID)
17 - access(ROWID=ROWID)
19 - access(("GG_SCHEDULE_TASK"."TASK_CODE"='10' OR "GG_SCHEDULE_TASK"."TASK_CODE"='11' OR "GG_SCHEDULE_TASK"."TASK_CODE"='12' OR
"GG_SCHEDULE_TASK"."TASK_CODE"='13' OR "GG_SCHEDULE_TASK"."TASK_CODE"='7' OR "GG_SCHEDULE_TASK"."TASK_CODE"='9'))
20 - access("GG_SCHEDULE_TASK"."TASK_TYPE"=1)
21 - filter("GG_SCHEDULE_TASK"."TASK_TYPE"=1)
23 - access("GG_SCHEDULE_TASK_TRACE"."TASK_TYPE"=1)
24 - filter(("GG_PROJECT"."PROJECT_KIND"=1 AND ("GG_PROJECT"."PROJECT_FROM" IS NULL OR INTERNAL_FUNCTION("GG_PROJECT"."PROJECT_FROM")) AND
(("GG_PROJECT"."PROJECT_STATUS"<40 AND "GG_PROJECT"."PROJECT_STATUS">=25) OR INTERNAL_FUNCTION("GG_PROJECT"."PROJECT_STATUS"))))
29 - filter(("PI"."INVEST_YEAR"=2017 AND "PI"."PLAN_KIND"=1 AND "PI"."FEE_LEVEL"=1))
30 - access("PS"."PROJECT_ID"="P"."PROJECT_ID")
31 - access("PS"."PROJECT_TYPE"=1)
32 - filter(("P"."INVEST_YEAR"=2017 AND "P"."AUDIT_STATUS"=3))
33 - access("GG_PROJECT"."PROJECT_ID"="V_GG_INVEST_PLAN"."PROJECT_ID")
36 - access("GG_SCHEDULE_PLAN_AUDIT"."OBJECT_ID"="V_PROJECT_MILESTONE_TASKDATE"."OBJECT_ID" AND
TO_NUMBER("PLAN_TYPE")="V_PROJECT_MILESTONE_TASKDATE"."PLAN_TYPE")
37 - filter(("GG_SCHEDULE_PLAN_AUDIT"."INVEST_YEAR"=2017 AND TO_NUMBER("PLAN_TYPE")=1))
40 - access("GG_SCHEDULE_TASK_TRACE"."TASK_ID"="GG_SCHEDULE_TASK"."TASK_ID")
41 - access("GG_SCHEDULE_TASK"."PLAN_ID"="GG_SCHEDULE_PLAN"."PLAN_ID")
43 - filter("GG_SCHEDULE_PLAN"."PLAN_TYPE"=1)
44 - filter("GG_SCHEDULE_PLAN_TRACE"."PLAN_TYPE"=1)
45 - access("GG_SCHEDULE_PLAN_TRACE"."TRACE_ID"="GG_SCHEDULE_PLAN"."TRACE_ID")
46 - filter(("GG_SCHEDULE_TASK"."TASK_CODE"='10' OR "GG_SCHEDULE_TASK"."TASK_CODE"='11' OR "GG_SCHEDULE_TASK"."TASK_CODE"='12' OR
"GG_SCHEDULE_TASK"."TASK_CODE"='13' OR "GG_SCHEDULE_TASK"."TASK_CODE"='7' OR "GG_SCHEDULE_TASK"."TASK_CODE"='9'))
47 - access(ROWID=ROWID)
48 - access(ROWID=ROWID)
50 - access(("GG_SCHEDULE_TASK"."TASK_CODE"='10' OR "GG_SCHEDULE_TASK"."TASK_CODE"='11' OR "GG_SCHEDULE_TASK"."TASK_CODE"='12' OR
"GG_SCHEDULE_TASK"."TASK_CODE"='13' OR "GG_SCHEDULE_TASK"."TASK_CODE"='7' OR "GG_SCHEDULE_TASK"."TASK_CODE"='9'))
51 - access("GG_SCHEDULE_TASK"."TASK_TYPE"=1)
52 - filter("GG_SCHEDULE_TASK"."TASK_TYPE"=1)
54 - access("GG_SCHEDULE_TASK_TRACE"."TASK_TYPE"=1)
55 - access("GG_SCHEDULE_PLAN_AUDIT"."OBJECT_ID"="GG_PROJECT"."PROJECT_ID")
filter(LNNVL("GG_SCHEDULE_PLAN_AUDIT"."OBJECT_ID"="GG_PROJECT"."PROJECT_ID"||'-C'||'2017'))
56 - filter(("GG_PROJECT"."PROJECT_KIND"=1 AND ("GG_PROJECT"."PROJECT_FROM" IS NULL OR INTERNAL_FUNCTION("GG_PROJECT"."PROJECT_FROM")) AND
(("GG_PROJECT"."PROJECT_STATUS"<40 AND "GG_PROJECT"."PROJECT_STATUS">=25) OR INTERNAL_FUNCTION("GG_PROJECT"."PROJECT_STATUS"))))
61 - filter(("PI"."INVEST_YEAR"=2017 AND "PI"."PLAN_KIND"=1 AND "PI"."FEE_LEVEL"=1))
62 - access("PS"."PROJECT_ID"="P"."PROJECT_ID")
63 - access("PS"."PROJECT_TYPE"=1)
64 - filter(("P"."INVEST_YEAR"=2017 AND "P"."AUDIT_STATUS"=3))
已选择147行。

SQL> set autotrace traceonly
SQL> SELECT COUNT(1)
2 FROM (SELECT
3 GG_PROJECT.PROJECT_ID, GG_PROJECT.EXECUTE_DEPARTMENT_CODE
4 FROM GG_PROJECT GG_PROJECT,
5 GG_SCHEDULE_PLAN_AUDIT GG_SCHEDULE_PLAN_AUDIT,
6 V_GG_PROJECT_INVEST V_GG_INVEST_PLAN,
7 V_PROJECT_MILESTONE_TRACEDATE V_PROJECT_MILESTONE_TASKDATE
8 WHERE (GG_SCHEDULE_PLAN_AUDIT.OBJECT_ID = GG_PROJECT.PROJECT_ID OR
9 GG_SCHEDULE_PLAN_AUDIT.OBJECT_ID =
10 GG_PROJECT.PROJECT_ID || '-C' || '2017')
11 AND GG_SCHEDULE_PLAN_AUDIT.OBJECT_ID =
12 V_PROJECT_MILESTONE_TASKDATE.OBJECT_ID
13 AND to_number(GG_SCHEDULE_PLAN_AUDIT.PLAN_TYPE) =
14 V_PROJECT_MILESTONE_TASKDATE.PLAN_TYPE
15 AND GG_SCHEDULE_PLAN_AUDIT.PLAN_TYPE = 1
16 AND GG_PROJECT.PROJECT_ID = V_GG_INVEST_PLAN.PROJECT_ID
17 AND V_GG_INVEST_PLAN.INVEST_YEAR = 2017
18 AND GG_SCHEDULE_PLAN_AUDIT.INVEST_YEAR = 2017
19 AND ((GG_PROJECT.PROJECT_STATUS < 40 AND
20 GG_PROJECT.PROJECT_STATUS >= 25) OR
21 GG_PROJECT.PROJECT_STATUS = 170 OR
22 GG_PROJECT.PROJECT_STATUS = 171)
23 AND (GG_PROJECT.PROJECT_FROM in ('ps','TY') or GG_PROJECT.PROJECT_FROM is null)
24 AND GG_PROJECT.PROJECT_KIND = 1
25 ) MAIN_TABLE;
已用时间: 00: 00: 01.61
执行计划
----------------------------------------------------------
Plan hash value: 2577354375
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 111 | | 58259 (1)| 00:11:40 |
| 1 | SORT AGGREGATE | | 1 | 111 | | | |
| 2 | CONCATENATION | | | | | | |
|* 3 | HASH JOIN | | 15 | 1665 | | 39271 (1)| 00:07:52 |
|* 4 | HASH JOIN | | 152 | 13528 | | 37562 (1)| 00:07:31 |
|* 5 | HASH JOIN | | 18 | 1116 | | 17243 (1)| 00:03:27 |
|* 6 | TABLE ACCESS FULL | GG_SCHEDULE_PLAN_AUDIT | 225 | 4950 | | 243 (2)| 00:00:03 |
| 7 | VIEW | V_PROJECT_MILESTONE_TRACEDATE | 9183 | 358K| | 17000 (1)| 00:03:24 |
| 8 | HASH GROUP BY | | 9183 | 1416K| 1912K| 17000 (1)| 00:03:24 |
|* 9 | HASH JOIN OUTER | | 11387 | 1756K| | 16651 (1)| 00:03:20 |
|* 10 | HASH JOIN | | 11384 | 1478K| | 13432 (1)| 00:02:42 |
| 11 | NESTED LOOPS OUTER | | 9183 | 699K| | 2071 (1)| 00:00:25 |
|* 12 | TABLE ACCESS FULL | GG_SCHEDULE_PLAN | 9183 | 466K| | 1883 (1)| 00:00:23 |
|* 13 | TABLE ACCESS BY INDEX ROWID | GG_SCHEDULE_PLAN_TRACE | 1 | 26 | | 2 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | PK_GG_SCHEDULE_PLAN_TRACE | 1 | | | 1 (0)| 00:00:01 |
|* 15 | VIEW | index$_join$_007 | 33016 | 1773K| | 11360 (1)| 00:02:17 |
|* 16 | HASH JOIN | | | | | | |
|* 17 | HASH JOIN | | | | | | |
| 18 | INLIST ITERATOR | | | | | | |
|* 19 | INDEX RANGE SCAN | PK_GG_SCHEDULE_TASK_CODE | 33016 | 1773K| | 603 (2)| 00:00:08 |
|* 20 | INDEX RANGE SCAN | INX_GG_SCHEDULE_TASK | 33016 | 1773K| | 2005 (1)| 00:00:25 |
|* 21 | INDEX FAST FULL SCAN | INX_GG_TASK_TYPE | 33016 | 1773K| | 11065 (1)| 00:02:13 |
| 22 | TABLE ACCESS BY INDEX ROWID | GG_SCHEDULE_TASK_TRACE | 67391 | 1645K| | 3218 (1)| 00:00:39 |
|* 23 | INDEX RANGE SCAN | PK_TRACE_ID | 67391 | | | 147 (1)| 00:00:02 |
|* 24 | TABLE ACCESS FULL | GG_PROJECT | 231K| 6108K| | 20317 (2)| 00:04:04 |
| 25 | VIEW | V_GG_PROJECT_INVEST | 22761 | 489K| | 1709 (2)| 00:00:21 |
| 26 | HASH UNIQUE | | 22761 | 577K| | 1709 (2)| 00:00:21 |
| 27 | VIEW | | 22761 | 577K| | 1707 (2)| 00:00:21 |
| 28 | UNION-ALL | | | | | | |
|* 29 | INDEX FAST FULL SCAN | IDX_GG_ANNUAL_INVESTMENT | 1981 | 63392 | | 10 (0)| 00:00:01 |
|* 30 | HASH JOIN | | 20780 | 994K| | 1696 (2)| 00:00:21 |
|* 31 | INDEX RANGE SCAN | IDX_COMMISSION | 20202 | 414K| | 113 (0)| 00:00:02 |
|* 32 | INDEX FAST FULL SCAN | IDX_GG_INVEST_PLAN | 113K| 3094K| | 1582 (2)| 00:00:19 |
|* 33 | HASH JOIN | | 1 | 111 | | 18988 (1)| 00:03:48 |
| 34 | NESTED LOOPS | | 1 | 89 | | 17279 (1)| 00:03:28 |
| 35 | NESTED LOOPS | | 18 | 89 | | 17279 (1)| 00:03:28 |
|* 36 | HASH JOIN | | 18 | 1116 | | 17243 (1)| 00:03:27 |
|* 37 | TABLE ACCESS FULL | GG_SCHEDULE_PLAN_AUDIT | 225 | 4950 | | 243 (2)| 00:00:03 |
| 38 | VIEW | V_PROJECT_MILESTONE_TRACEDATE | 9183 | 358K| | 17000 (1)| 00:03:24 |
| 39 | HASH GROUP BY | | 9183 | 1416K| 1912K| 17000 (1)| 00:03:24 |
|* 40 | HASH JOIN OUTER | | 11387 | 1756K| | 16651 (1)| 00:03:20 |
|* 41 | HASH JOIN | | 11384 | 1478K| | 13432 (1)| 00:02:42 |
| 42 | NESTED LOOPS OUTER | | 9183 | 699K| | 2071 (1)| 00:00:25 |
|* 43 | TABLE ACCESS FULL | GG_SCHEDULE_PLAN | 9183 | 466K| | 1883 (1)| 00:00:23 |
|* 44 | TABLE ACCESS BY INDEX ROWID| GG_SCHEDULE_PLAN_TRACE | 1 | 26 | | 2 (0)| 00:00:01 |
|* 45 | INDEX UNIQUE SCAN | PK_GG_SCHEDULE_PLAN_TRACE | 1 | | | 1 (0)| 00:00:01 |
|* 46 | VIEW | index$_join$_007 | 33016 | 1773K| | 11360 (1)| 00:02:17 |
|* 47 | HASH JOIN | | | | | | |
|* 48 | HASH JOIN | | | | | | |
| 49 | INLIST ITERATOR | | | | | | |
|* 50 | INDEX RANGE SCAN | PK_GG_SCHEDULE_TASK_CODE | 33016 | 1773K| | 603 (2)| 00:00:08 |
|* 51 | INDEX RANGE SCAN | INX_GG_SCHEDULE_TASK | 33016 | 1773K| | 2005 (1)| 00:00:25 |
|* 52 | INDEX FAST FULL SCAN | INX_GG_TASK_TYPE | 33016 | 1773K| | 11065 (1)| 00:02:13 |
| 53 | TABLE ACCESS BY INDEX ROWID | GG_SCHEDULE_TASK_TRACE | 67391 | 1645K| | 3218 (1)| 00:00:39 |
|* 54 | INDEX RANGE SCAN | PK_TRACE_ID | 67391 | | | 147 (1)| 00:00:02 |
|* 55 | INDEX UNIQUE SCAN | PK_GG_PROJECT | 1 | | | 1 (0)| 00:00:01 |
|* 56 | TABLE ACCESS BY INDEX ROWID | GG_PROJECT | 1 | 27 | | 2 (0)| 00:00:01 |
| 57 | VIEW | V_GG_PROJECT_INVEST | 22761 | 489K| | 1709 (2)| 00:00:21 |
| 58 | HASH UNIQUE | | 22761 | 577K| | 1709 (2)| 00:00:21 |
| 59 | VIEW | | 22761 | 577K| | 1707 (2)| 00:00:21 |
| 60 | UNION-ALL | | | | | | |
|* 61 | INDEX FAST FULL SCAN | IDX_GG_ANNUAL_INVESTMENT | 1981 | 63392 | | 10 (0)| 00:00:01 |
|* 62 | HASH JOIN | | 20780 | 994K| | 1696 (2)| 00:00:21 |
|* 63 | INDEX RANGE SCAN | IDX_COMMISSION | 20202 | 414K| | 113 (0)| 00:00:02 |
|* 64 | INDEX FAST FULL SCAN | IDX_GG_INVEST_PLAN | 113K| 3094K| | 1582 (2)| 00:00:19 |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("GG_PROJECT"."PROJECT_ID"="V_GG_INVEST_PLAN"."PROJECT_ID")
4 - access("GG_SCHEDULE_PLAN_AUDIT"."OBJECT_ID"="GG_PROJECT"."PROJECT_ID"||'-C'||'2017')
5 - access("GG_SCHEDULE_PLAN_AUDIT"."OBJECT_ID"="V_PROJECT_MILESTONE_TASKDATE"."OBJECT_ID" AND
TO_NUMBER("PLAN_TYPE")="V_PROJECT_MILESTONE_TASKDATE"."PLAN_TYPE")
6 - filter("GG_SCHEDULE_PLAN_AUDIT"."INVEST_YEAR"=2017 AND TO_NUMBER("PLAN_TYPE")=1)
9 - access("GG_SCHEDULE_TASK_TRACE"."TASK_ID"(+)="GG_SCHEDULE_TASK"."TASK_ID")
10 - access("GG_SCHEDULE_TASK"."PLAN_ID"="GG_SCHEDULE_PLAN"."PLAN_ID")
12 - filter("GG_SCHEDULE_PLAN"."PLAN_TYPE"=1)
13 - filter("GG_SCHEDULE_PLAN_TRACE"."PLAN_TYPE"(+)=1)
14 - access("GG_SCHEDULE_PLAN_TRACE"."TRACE_ID"(+)="GG_SCHEDULE_PLAN"."TRACE_ID")
15 - filter("GG_SCHEDULE_TASK"."TASK_CODE"='10' OR "GG_SCHEDULE_TASK"."TASK_CODE"='11' OR
"GG_SCHEDULE_TASK"."TASK_CODE"='12' OR "GG_SCHEDULE_TASK"."TASK_CODE"='13' OR "GG_SCHEDULE_TASK"."TASK_CODE"='7' OR
"GG_SCHEDULE_TASK"."TASK_CODE"='9')
16 - access(ROWID=ROWID)
17 - access(ROWID=ROWID)
19 - access("GG_SCHEDULE_TASK"."TASK_CODE"='10' OR "GG_SCHEDULE_TASK"."TASK_CODE"='11' OR
"GG_SCHEDULE_TASK"."TASK_CODE"='12' OR "GG_SCHEDULE_TASK"."TASK_CODE"='13' OR "GG_SCHEDULE_TASK"."TASK_CODE"='7' OR
"GG_SCHEDULE_TASK"."TASK_CODE"='9')
20 - access("GG_SCHEDULE_TASK"."TASK_TYPE"=1)
21 - filter("GG_SCHEDULE_TASK"."TASK_TYPE"=1)
23 - access("GG_SCHEDULE_TASK_TRACE"."TASK_TYPE"(+)=1)
24 - filter("GG_PROJECT"."PROJECT_KIND"=1 AND ("GG_PROJECT"."PROJECT_FROM" IS NULL OR
("GG_PROJECT"."PROJECT_FROM"='TY' OR "GG_PROJECT"."PROJECT_FROM"='ps')) AND ("GG_PROJECT"."PROJECT_STATUS"<40 AND
"GG_PROJECT"."PROJECT_STATUS">=25 OR ("GG_PROJECT"."PROJECT_STATUS"=170 OR "GG_PROJECT"."PROJECT_STATUS"=171)))
29 - filter("PI"."INVEST_YEAR"=2017 AND "PI"."PLAN_KIND"=1 AND "PI"."FEE_LEVEL"=1)
30 - access("PS"."PROJECT_ID"="P"."PROJECT_ID")
31 - access("PS"."PROJECT_TYPE"=1)
32 - filter("P"."INVEST_YEAR"=2017 AND "P"."AUDIT_STATUS"=3)
33 - access("GG_PROJECT"."PROJECT_ID"="V_GG_INVEST_PLAN"."PROJECT_ID")
36 - access("GG_SCHEDULE_PLAN_AUDIT"."OBJECT_ID"="V_PROJECT_MILESTONE_TASKDATE"."OBJECT_ID" AND
TO_NUMBER("PLAN_TYPE")="V_PROJECT_MILESTONE_TASKDATE"."PLAN_TYPE")
37 - filter("GG_SCHEDULE_PLAN_AUDIT"."INVEST_YEAR"=2017 AND TO_NUMBER("PLAN_TYPE")=1)
40 - access("GG_SCHEDULE_TASK_TRACE"."TASK_ID"(+)="GG_SCHEDULE_TASK"."TASK_ID")
41 - access("GG_SCHEDULE_TASK"."PLAN_ID"="GG_SCHEDULE_PLAN"."PLAN_ID")
43 - filter("GG_SCHEDULE_PLAN"."PLAN_TYPE"=1)
44 - filter("GG_SCHEDULE_PLAN_TRACE"."PLAN_TYPE"(+)=1)
45 - access("GG_SCHEDULE_PLAN_TRACE"."TRACE_ID"(+)="GG_SCHEDULE_PLAN"."TRACE_ID")
46 - filter("GG_SCHEDULE_TASK"."TASK_CODE"='10' OR "GG_SCHEDULE_TASK"."TASK_CODE"='11' OR
"GG_SCHEDULE_TASK"."TASK_CODE"='12' OR "GG_SCHEDULE_TASK"."TASK_CODE"='13' OR "GG_SCHEDULE_TASK"."TASK_CODE"='7' OR
"GG_SCHEDULE_TASK"."TASK_CODE"='9')
47 - access(ROWID=ROWID)
48 - access(ROWID=ROWID)
50 - access("GG_SCHEDULE_TASK"."TASK_CODE"='10' OR "GG_SCHEDULE_TASK"."TASK_CODE"='11' OR
"GG_SCHEDULE_TASK"."TASK_CODE"='12' OR "GG_SCHEDULE_TASK"."TASK_CODE"='13' OR "GG_SCHEDULE_TASK"."TASK_CODE"='7' OR
"GG_SCHEDULE_TASK"."TASK_CODE"='9')
51 - access("GG_SCHEDULE_TASK"."TASK_TYPE"=1)
52 - filter("GG_SCHEDULE_TASK"."TASK_TYPE"=1)
54 - access("GG_SCHEDULE_TASK_TRACE"."TASK_TYPE"(+)=1)
55 - access("GG_SCHEDULE_PLAN_AUDIT"."OBJECT_ID"="GG_PROJECT"."PROJECT_ID")
filter(LNNVL("GG_SCHEDULE_PLAN_AUDIT"."OBJECT_ID"="GG_PROJECT"."PROJECT_ID"||'-C'||'2017'))
56 - filter("GG_PROJECT"."PROJECT_KIND"=1 AND ("GG_PROJECT"."PROJECT_FROM" IS NULL OR
("GG_PROJECT"."PROJECT_FROM"='TY' OR "GG_PROJECT"."PROJECT_FROM"='ps')) AND ("GG_PROJECT"."PROJECT_STATUS"<40 AND
"GG_PROJECT"."PROJECT_STATUS">=25 OR ("GG_PROJECT"."PROJECT_STATUS"=170 OR "GG_PROJECT"."PROJECT_STATUS"=171)))
61 - filter("PI"."INVEST_YEAR"=2017 AND "PI"."PLAN_KIND"=1 AND "PI"."FEE_LEVEL"=1)
62 - access("PS"."PROJECT_ID"="P"."PROJECT_ID")
63 - access("PS"."PROJECT_TYPE"=1)
64 - filter("P"."INVEST_YEAR"=2017 AND "P"."AUDIT_STATUS"=3)
统计信息
----------------------------------------------------------
43 recursive calls
0 db block gets
63924 consistent gets
0 physical reads
0 redo size
348 bytes sent via SQL*Net to client
472 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

在pl/sql devoper中执行1秒可以出来。

阅读全文

0 0