SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jun 8 10:40:07 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>set autot trace exp stat;
SQL> select /* use_hash(zb fb) */
2 FB.KSBM_CL FB_KSBM_CL, 0.0 jfshlje, 0.0 jfybje, 0.0 jffbje, 0.0 jfbbje, 0.0 dfshlje, 0.0 dfybje, 0.0 dffbje, 0.0 dfbbje,
3 sum ( fb.fx * fb.shlye ) qcshlye, sum ( fb.fx * fb.ybye ) qcybye, sum ( fb.fx * fb.fbye ) qcfbye, sum ( fb.fx * fb.bbye ) qcbbye
4 from arap_djzb_part zb, arap_djfb fb, bd_cumandoc ksglda
5 where zb.vouchid = fb.vouchid and fb.ksbm_cl = ksglda.pk_cumandoc ( + ) and
6 ( ( zb.dwbm = '1011' ) and zb.djzt >= 1 and zb.hzbz is null and fb.wldx = 0 and
7 ( zb.qcbz = 'Y' or zb.djrq < '2009-06-02' ) and fb.xgbh <> 1 and
8 ( fb.bz_date is null or fb.bz_date < '2009-06-02' ) and ( zb.djdl = 'ys' or zb.djdl = 'sk' ) and
9 ( fb.ksbm_cl is not null ) and ( ksglda.custflag = '0' or ksglda.custflag = '2' ) and
10 ( fb.dr = 0 and zb.dr = 0 and ksglda.dr = 0 ) ) group by FB.KSBM_CL
11 /
1042 rows selected.
Elapsed: 00:05:05.38
Execution Plan
----------------------------------------------------------
Plan hash value: 1784893704
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 36581 | 5144K| | 109K (1)| 00:10:26 | | |
| 1 | HASH GROUP BY | | 36581 | 5144K| | | | | |
| 2 | CONCATENATION | | | | | | | | |
| 3 | NESTED LOOPS | | 373 | 53712 | | 1052 (6)| 00:00:07 | | |
| 4 | NESTED LOOPS | | 386 | 45934 | | 878 (7)| 00:00:06 | | |
| 5 | PARTITION LIST SINGLE | | 142 | 7100 | | 750 (8)| 00:00:05 | KEY | KEY |
|* 6 | TABLE ACCESS FULL | ARAP_DJZB_PART | 142 | 7100 | | 750 (8)| 00:00:05 | 10 | 10 |
|* 7 | TABLE ACCESS BY INDEX ROWID| ARAP_DJFB | 3 | 207 | | 1 (0)| 00:00:01 | | |
|* 8 | INDEX RANGE SCAN | I_ARAP_DJFB001 | 3 | | | 1 (0)| 00:00:01 | | |
|* 9 | TABLE ACCESS BY INDEX ROWID | BD_CUMANDOC | 1 | 25 | | 1 (0)| 00:00:01 | | |
|* 10 | INDEX UNIQUE SCAN | PK_BD_CUMANDOC | 1 | | | 1 (0)| 00:00:01 | | |
|* 11 | HASH JOIN | | 298K| 40M| 6936K| 105K (1)| 00:10:04 | | |
|* 12 | TABLE ACCESS FULL | BD_CUMANDOC | 191K| 4684K| | 1591 (13)| 00:00:10 | | |
| 13 | NESTED LOOPS | | 308K| 35M| | 103K (1)| 00:09:49 | | |
| 14 | PARTITION LIST SINGLE | | 113K| 5529K| | 772 (10)| 00:00:05 | KEY | KEY |
|* 15 | TABLE ACCESS FULL | ARAP_DJZB_PART | 113K| 5529K| | 772 (10)| 00:00:05 | 10 | 10 |
|* 16 | TABLE ACCESS BY INDEX ROWID| ARAP_DJFB | 3 | 207 | | 1 (0)| 00:00:01 | | |
|* 17 | INDEX RANGE SCAN | I_ARAP_DJFB001 | 3 | | | 1 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter("ZB"."QCBZ"='Y' AND ("ZB"."DJDL"='sk' OR "ZB"."DJDL"='ys') AND "ZB"."DR"=0 AND "ZB"."DWBM"='1011'
AND "ZB"."DJZT">=1 AND "ZB"."HZBZ" IS NULL)
7 - filter("FB"."KSBM_CL" IS NOT NULL AND "FB"."WLDX"=0 AND "FB"."DR"=0 AND "FB"."XGBH"<>1 AND ("FB"."BZ_DATE"
IS NULL OR "FB"."BZ_DATE" 8 - access("ZB"."VOUCHID"="FB"."VOUCHID")
9 - filter(("KSGLDA"."CUSTFLAG"='0' OR "KSGLDA"."CUSTFLAG"='2') AND "KSGLDA"."DR"=0)
10 - access("FB"."KSBM_CL"="KSGLDA"."PK_CUMANDOC")
11 - access("FB"."KSBM_CL"="KSGLDA"."PK_CUMANDOC")
12 - filter(("KSGLDA"."CUSTFLAG"='0' OR "KSGLDA"."CUSTFLAG"='2') AND "KSGLDA"."DR"=0)
15 - filter(("ZB"."DJDL"='sk' OR "ZB"."DJDL"='ys') AND "ZB"."DR"=0 AND "ZB"."DWBM"='1011' AND
"ZB"."DJRQ"=1 AND LNNVL("ZB"."QCBZ"='Y'))
16 - filter("FB"."KSBM_CL" IS NOT NULL AND "FB"."WLDX"=0 AND "FB"."DR"=0 AND "FB"."XGBH"<>1 AND ("FB"."BZ_DATE"
IS NULL OR "FB"."BZ_DATE" 17 - access("ZB"."VOUCHID"="FB"."VOUCHID")
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
10 recursive calls
0 db block gets
429997 consistent gets
89631 physical reads
0 redo size
43761 bytes sent via SQL*Net to client
730 bytes received via SQL*Net from client
71 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1042 rows processed
SQL>alter system flush buffer_cache;
System altered.
Elapsed: 00:00:09.21
SQL>select /*+ use_hash(zb fb) */
2 FB.KSBM_CL FB_KSBM_CL, 0.0 jfshlje, 0.0 jfybje, 0.0 jffbje, 0.0 jfbbje, 0.0 dfshlje, 0.0 dfybje, 0.0 dffbje, 0.0 dfbbje,
3 sum ( fb.fx * fb.shlye ) qcshlye, sum ( fb.fx * fb.ybye ) qcybye, sum ( fb.fx * fb.fbye ) qcfbye, sum ( fb.fx * fb.bbye ) qcbbye
4 from arap_djzb_part zb, arap_djfb fb, bd_cumandoc ksglda
5 where zb.vouchid = fb.vouchid and fb.ksbm_cl = ksglda.pk_cumandoc ( + ) and
6 ( ( zb.dwbm = '1011' ) and zb.djzt >= 1 and zb.hzbz is null and fb.wldx = 0 and
7 ( zb.qcbz = 'Y' or zb.djrq < '2009-06-02' ) and fb.xgbh <> 1 and
8 ( fb.bz_date is null or fb.bz_date < '2009-06-02' ) and ( zb.djdl = 'ys' or zb.djdl = 'sk' ) and
9 ( fb.ksbm_cl is not null ) and ( ksglda.custflag = '0' or ksglda.custflag = '2' ) and
10 ( fb.dr = 0 and zb.dr = 0 and ksglda.dr = 0 ) ) group by FB.KSBM_CL
11 /
1042 rows selected.
Elapsed: 00:01:47.15
Execution Plan
----------------------------------------------------------
Plan hash value: 896126388
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 36581 | 5144K| | 122K (8)| 00:11:42 | | |
| 1 | HASH GROUP BY | | 36581 | 5144K| 53M| 122K (8)| 00:11:42 | | |
|* 2 | HASH JOIN | | 322K| 44M| 5808K| 119K (8)| 00:11:24 | | |
|* 3 | TABLE ACCESS FULL | BD_CUMANDOC | 160K| 3919K| | 1589 (12)| 00:00:10 | | |
|* 4 | HASH JOIN | | 333K| 37M| 7240K| 117K (8)| 00:11:09 | | |
| 5 | PARTITION LIST SINGLE| | 119K| 5832K| | 764 (9)| 00:00:05 | KEY | KEY |
|* 6 | TABLE ACCESS FULL | ARAP_DJZB_PART | 119K| 5832K| | 764 (9)| 00:00:05 | 10 | 10 |
|* 7 | TABLE ACCESS FULL | ARAP_DJFB | 8194K| 539M| | 102K (8)| 00:09:45 | | |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("FB"."KSBM_CL"="KSGLDA"."PK_CUMANDOC")
3 - filter(("KSGLDA"."CUSTFLAG"='0' OR "KSGLDA"."CUSTFLAG"='2') AND "KSGLDA"."DR"=0)
4 - access("ZB"."VOUCHID"="FB"."VOUCHID")
6 - filter(("ZB"."DJDL"='sk' OR "ZB"."DJDL"='ys') AND "ZB"."DR"=0 AND ("ZB"."DJRQ" "ZB"."QCBZ"='Y') AND "ZB"."DJZT">=1 AND "ZB"."HZBZ" IS NULL)
7 - filter("FB"."KSBM_CL" IS NOT NULL AND "FB"."WLDX"=0 AND "FB"."DR"=0 AND "FB"."XGBH"<>1 AND
("FB"."BZ_DATE" IS NULL OR "FB"."BZ_DATE"
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
10 recursive calls
0 db block gets
875121 consistent gets
870908 physical reads
80 redo size
44285 bytes sent via SQL*Net to client
730 bytes received via SQL*Net from client
71 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1042 rows processed
SQL>
从上面效果看出,用HASH JOIN效率远比NL的快,为什么优化器会作第1种选择? 附件是两语句的10053事件,
用语句 explain plan for 得来的.
CBO自选的执行计划 nl, 执行时间 00:05:05.38, 逻辑读 429997, 物理读 89631.
加提示以后用hash join, 执行时间 00:01:47.15, 逻辑读 875121, 物理读 870908.
奇怪, 后者的逻辑读和物理读都更大, 为什么执行时间会更短呢.
flush buffer_cache,只是从db cache搞出去,数据可能在文件系统缓存或者存储的缓存里
NCV31@NCDB>show parameter index
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_caching integer 80
optimizer_index_cost_adj integer 45
skip_unusable_indexes boolean TRUE
NCV31@NCDB>alter session set optimizer_index_caching=30;
Session altered.
Elapsed: 00:00:00.00
NCV31@NCDB>explain plan for
2 select /* use_hash(zb fb) */
3 FB.KSBM_CL FB_KSBM_CL, 0.0 jfshlje, 0.0 jfybje, 0.0 jffbje, 0.0 jfbbje, 0.0 dfshlje, 0.0 dfybje, 0.0 dffbje, 0.0 dfbbje,
4 sum ( fb.fx * fb.shlye ) qcshlye, sum ( fb.fx * fb.ybye ) qcybye, sum ( fb.fx * fb.fbye ) qcfbye, sum ( fb.fx * fb.bbye ) qcbbye
5 from arap_djzb_part zb, arap_djfb fb, bd_cumandoc ksglda
6 where zb.vouchid = fb.vouchid and fb.ksbm_cl = ksglda.pk_cumandoc ( + ) and
7 ( ( zb.dwbm = '1011' ) and zb.djzt >= 1 and zb.hzbz is null and fb.wldx = 0 and
8 ( zb.qcbz = 'Y' or zb.djrq < '2009-06-02' ) and fb.xgbh <> 1 and
9 ( fb.bz_date is null or fb.bz_date < '2009-06-02' ) and ( zb.djdl = 'ys' or zb.djdl = 'sk' ) and
10 ( fb.ksbm_cl is not null ) and ( ksglda.custflag = '0' or ksglda.custflag = '2' ) and
11 ( fb.dr = 0 and zb.dr = 0 and ksglda.dr = 0 ) ) group by FB.KSBM_CL
12 /
Explained.
Elapsed: 00:00:01.21
NCV31@NCDB>
NCV31@NCDB>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1784893704
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 36590 | 5145K| | 109K (1)| 00:10:26 | | |
| 1 | HASH GROUP BY | | 36590 | 5145K| | | | | |
| 2 | CONCATENATION | | | | | | | | |
| 3 | NESTED LOOPS | | 374 | 53856 | | 1053 (6)| 00:00:07 | | |
| 4 | NESTED LOOPS | | 387 | 46053 | | 878 (7)| 00:00:06 | | |
| 5 | PARTITION LIST SINGLE | | 142 | 7100 | | 750 (8)| 00:00:05 | KEY | KEY |
|* 6 | TABLE ACCESS FULL | ARAP_DJZB_PART | 142 | 7100 | | 750 (8)| 00:00:05 | 10 | 10 |
|* 7 | TABLE ACCESS BY INDEX ROWID| ARAP_DJFB | 3 | 207 | | 1 (0)| 00:00:01 | | |
|* 8 | INDEX RANGE SCAN | I_ARAP_DJFB001 | 3 | | | 1 (0)| 00:00:01 | | |
|* 9 | TABLE ACCESS BY INDEX ROWID | BD_CUMANDOC | 1 | 25 | | 1 (0)| 00:00:01 | | |
|* 10 | INDEX UNIQUE SCAN | PK_BD_CUMANDOC | 1 | | | 1 (0)| 00:00:01 | | |
|* 11 | HASH JOIN | | 298K| 40M| 6936K| 105K (1)| 00:10:04 | | |
|* 12 | TABLE ACCESS FULL | BD_CUMANDOC | 191K| 4684K| | 1591 (13)| 00:00:10 | | |
| 13 | NESTED LOOPS | | 308K| 35M| | 103K (1)| 00:09:49 | | |
| 14 | PARTITION LIST SINGLE | | 113K| 5529K| | 772 (10)| 00:00:05 | KEY | KEY |
|* 15 | TABLE ACCESS FULL | ARAP_DJZB_PART | 113K| 5529K| | 772 (10)| 00:00:05 | 10 | 10 |
|* 16 | TABLE ACCESS BY INDEX ROWID| ARAP_DJFB | 3 | 207 | | 1 (0)| 00:00:01 | | |
|* 17 | INDEX RANGE SCAN | I_ARAP_DJFB001 | 3 | | | 1 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter("ZB"."QCBZ"='Y' AND ("ZB"."DJDL"='sk' OR "ZB"."DJDL"='ys') AND "ZB"."DR"=0 AND "ZB"."DWBM"='1011'
AND "ZB"."DJZT">=1 AND "ZB"."HZBZ" IS NULL)
7 - filter("FB"."KSBM_CL" IS NOT NULL AND "FB"."WLDX"=0 AND "FB"."XGBH"<>1 AND "FB"."DR"=0 AND ("FB"."BZ_DATE"
IS NULL OR "FB"."BZ_DATE" 8 - access("ZB"."VOUCHID"="FB"."VOUCHID")
9 - filter(("KSGLDA"."CUSTFLAG"='0' OR "KSGLDA"."CUSTFLAG"='2') AND "KSGLDA"."DR"=0)
10 - access("FB"."KSBM_CL"="KSGLDA"."PK_CUMANDOC")
11 - access("FB"."KSBM_CL"="KSGLDA"."PK_CUMANDOC")
12 - filter(("KSGLDA"."CUSTFLAG"='0' OR "KSGLDA"."CUSTFLAG"='2') AND "KSGLDA"."DR"=0)
15 - filter(("ZB"."DJDL"='sk' OR "ZB"."DJDL"='ys') AND "ZB"."DR"=0 AND "ZB"."DWBM"='1011' AND
"ZB"."DJRQ"=1 AND LNNVL("ZB"."QCBZ"='Y'))
16 - filter("FB"."KSBM_CL" IS NOT NULL AND "FB"."WLDX"=0 AND "FB"."XGBH"<>1 AND "FB"."DR"=0 AND ("FB"."BZ_DATE"
IS NULL OR "FB"."BZ_DATE" 17 - access("ZB"."VOUCHID"="FB"."VOUCHID")
Note
-----
- dynamic sampling used for this statement
46 rows selected.
Elapsed: 00:00:01.14
NCV31@NCDB>alter session set optimizer_index_cost_adj=60;
Session altered.
Elapsed: 00:00:00.00
NCV31@NCDB>explain plan for
2 select /* use_hash(zb fb) */
3 FB.KSBM_CL FB_KSBM_CL, 0.0 jfshlje, 0.0 jfybje, 0.0 jffbje, 0.0 jfbbje, 0.0 dfshlje, 0.0 dfybje, 0.0 dffbje, 0.0 dfbbje,
4 sum ( fb.fx * fb.shlye ) qcshlye, sum ( fb.fx * fb.ybye ) qcybye, sum ( fb.fx * fb.fbye ) qcfbye, sum ( fb.fx * fb.bbye ) qcbbye
5 from arap_djzb_part zb, arap_djfb fb, bd_cumandoc ksglda
6 where zb.vouchid = fb.vouchid and fb.ksbm_cl = ksglda.pk_cumandoc ( + ) and
7 ( ( zb.dwbm = '1011' ) and zb.djzt >= 1 and zb.hzbz is null and fb.wldx = 0 and
8 ( zb.qcbz = 'Y' or zb.djrq < '2009-06-02' ) and fb.xgbh <> 1 and
9 ( fb.bz_date is null or fb.bz_date < '2009-06-02' ) and ( zb.djdl = 'ys' or zb.djdl = 'sk' ) and
10 ( fb.ksbm_cl is not null ) and ( ksglda.custflag = '0' or ksglda.custflag = '2' ) and
11 ( fb.dr = 0 and zb.dr = 0 and ksglda.dr = 0 ) ) group by FB.KSBM_CL
12 /
Explained.
Elapsed: 00:00:00.17
NCV31@NCDB>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 896126388
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 36590 | 5145K| | 122K (8)| 00:11:42 | | |
| 1 | HASH GROUP BY | | 36590 | 5145K| 53M| 122K (8)| 00:11:42 | | |
|* 2 | HASH JOIN | | 322K| 44M| 5808K| 119K (8)| 00:11:24 | | |
|* 3 | TABLE ACCESS FULL | BD_CUMANDOC | 160K| 3919K| | 1589 (12)| 00:00:10 | | |
|* 4 | HASH JOIN | | 333K| 37M| 7240K| 117K (8)| 00:11:09 | | |
| 5 | PARTITION LIST SINGLE| | 119K| 5832K| | 764 (9)| 00:00:05 | KEY | KEY |
|* 6 | TABLE ACCESS FULL | ARAP_DJZB_PART | 119K| 5832K| | 764 (9)| 00:00:05 | 10 | 10 |
|* 7 | TABLE ACCESS FULL | ARAP_DJFB | 8195K| 539M| | 102K (8)| 00:09:45 | | |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("FB"."KSBM_CL"="KSGLDA"."PK_CUMANDOC")
3 - filter(("KSGLDA"."CUSTFLAG"='0' OR "KSGLDA"."CUSTFLAG"='2') AND "KSGLDA"."DR"=0)
4 - access("ZB"."VOUCHID"="FB"."VOUCHID")
6 - filter(("ZB"."DJDL"='sk' OR "ZB"."DJDL"='ys') AND "ZB"."DR"=0 AND ("ZB"."DJRQ" "ZB"."QCBZ"='Y') AND "ZB"."DJZT">=1 AND "ZB"."HZBZ" IS NULL)
7 - filter("FB"."KSBM_CL" IS NOT NULL AND "FB"."WLDX"=0 AND "FB"."XGBH"<>1 AND "FB"."DR"=0 AND
("FB"."BZ_DATE" IS NULL OR "FB"."BZ_DATE"
Note
-----
- dynamic sampling used for this statement
29 rows selected.
Elapsed: 00:00:00.12
NCV31@NCDB>alter session set optimizer_index_cost_adj=50;
Session altered.
Elapsed: 00:00:00.00
NCV31@NCDB>explain plan for
2 select /* use_hash(zb fb) */
3 FB.KSBM_CL FB_KSBM_CL, 0.0 jfshlje, 0.0 jfybje, 0.0 jffbje, 0.0 jfbbje, 0.0 dfshlje, 0.0 dfybje, 0.0 dffbje, 0.0 dfbbje,
4 sum ( fb.fx * fb.shlye ) qcshlye, sum ( fb.fx * fb.ybye ) qcybye, sum ( fb.fx * fb.fbye ) qcfbye, sum ( fb.fx * fb.bbye ) qcbbye
5 from arap_djzb_part zb, arap_djfb fb, bd_cumandoc ksglda
6 where zb.vouchid = fb.vouchid and fb.ksbm_cl = ksglda.pk_cumandoc ( + ) and
7 ( ( zb.dwbm = '1011' ) and zb.djzt >= 1 and zb.hzbz is null and fb.wldx = 0 and
8 ( zb.qcbz = 'Y' or zb.djrq < '2009-06-02' ) and fb.xgbh <> 1 and
9 ( fb.bz_date is null or fb.bz_date < '2009-06-02' ) and ( zb.djdl = 'ys' or zb.djdl = 'sk' ) and
10 ( fb.ksbm_cl is not null ) and ( ksglda.custflag = '0' or ksglda.custflag = '2' ) and
11 ( fb.dr = 0 and zb.dr = 0 and ksglda.dr = 0 ) ) group by FB.KSBM_CL
12 /
Explained.
Elapsed: 00:00:00.18
NCV31@NCDB>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1784893704
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 36590 | 5145K| | 121K (1)| 00:11:32 | | |
| 1 | HASH GROUP BY | | 36590 | 5145K| | | | | |
| 2 | CONCATENATION | | | | | | | | |
| 3 | NESTED LOOPS | | 374 | 53856 | | 1086 (5)| 00:00:07 | | |
| 4 | NESTED LOOPS | | 387 | 46053 | | 892 (6)| 00:00:06 | | |
| 5 | PARTITION LIST SINGLE | | 142 | 7100 | | 750 (8)| 00:00:05 | KEY | KEY |
|* 6 | TABLE ACCESS FULL | ARAP_DJZB_PART | 142 | 7100 | | 750 (8)| 00:00:05 | 10 | 10 |
|* 7 | TABLE ACCESS BY INDEX ROWID| ARAP_DJFB | 3 | 207 | | 1 (0)| 00:00:01 | | |
|* 8 | INDEX RANGE SCAN | I_ARAP_DJFB001 | 3 | | | 1 (0)| 00:00:01 | | |
|* 9 | TABLE ACCESS BY INDEX ROWID | BD_CUMANDOC | 1 | 25 | | 1 (0)| 00:00:01 | | |
|* 10 | INDEX UNIQUE SCAN | PK_BD_CUMANDOC | 1 | | | 1 (0)| 00:00:01 | | |
|* 11 | HASH JOIN | | 298K| 40M| 6936K| 116K (1)| 00:11:09 | | |
|* 12 | TABLE ACCESS FULL | BD_CUMANDOC | 191K| 4684K| | 1591 (13)| 00:00:10 | | |
| 13 | NESTED LOOPS | | 308K| 35M| | 114K (1)| 00:10:54 | | |
| 14 | PARTITION LIST SINGLE | | 113K| 5529K| | 772 (10)| 00:00:05 | KEY | KEY |
|* 15 | TABLE ACCESS FULL | ARAP_DJZB_PART | 113K| 5529K| | 772 (10)| 00:00:05 | 10 | 10 |
|* 16 | TABLE ACCESS BY INDEX ROWID| ARAP_DJFB | 3 | 207 | | 1 (0)| 00:00:01 | | |
|* 17 | INDEX RANGE SCAN | I_ARAP_DJFB001 | 3 | | | 1 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter("ZB"."QCBZ"='Y' AND ("ZB"."DJDL"='sk' OR "ZB"."DJDL"='ys') AND "ZB"."DR"=0 AND "ZB"."DWBM"='1011'
AND "ZB"."DJZT">=1 AND "ZB"."HZBZ" IS NULL)
7 - filter("FB"."KSBM_CL" IS NOT NULL AND "FB"."WLDX"=0 AND "FB"."XGBH"<>1 AND "FB"."DR"=0 AND ("FB"."BZ_DATE"
IS NULL OR "FB"."BZ_DATE" 8 - access("ZB"."VOUCHID"="FB"."VOUCHID")
9 - filter(("KSGLDA"."CUSTFLAG"='0' OR "KSGLDA"."CUSTFLAG"='2') AND "KSGLDA"."DR"=0)
10 - access("FB"."KSBM_CL"="KSGLDA"."PK_CUMANDOC")
11 - access("FB"."KSBM_CL"="KSGLDA"."PK_CUMANDOC")
12 - filter(("KSGLDA"."CUSTFLAG"='0' OR "KSGLDA"."CUSTFLAG"='2') AND "KSGLDA"."DR"=0)
15 - filter(("ZB"."DJDL"='sk' OR "ZB"."DJDL"='ys') AND "ZB"."DR"=0 AND "ZB"."DWBM"='1011' AND
"ZB"."DJRQ"=1 AND LNNVL("ZB"."QCBZ"='Y'))
16 - filter("FB"."KSBM_CL" IS NOT NULL AND "FB"."WLDX"=0 AND "FB"."XGBH"<>1 AND "FB"."DR"=0 AND ("FB"."BZ_DATE"
IS NULL OR "FB"."BZ_DATE" 17 - access("ZB"."VOUCHID"="FB"."VOUCHID")
Note
-----
- dynamic sampling used for this statement
46 rows selected.
Elapsed: 00:00:00.17
NCV31@NCDB>alter session set optimizer_index_cost_adj=55;
Session altered.
Elapsed: 00:00:00.01
NCV31@NCDB>explain plan for
2 select /* use_hash(zb fb) */
3 FB.KSBM_CL FB_KSBM_CL, 0.0 jfshlje, 0.0 jfybje, 0.0 jffbje, 0.0 jfbbje, 0.0 dfshlje, 0.0 dfybje, 0.0 dffbje, 0.0 dfbbje,
4 sum ( fb.fx * fb.shlye ) qcshlye, sum ( fb.fx * fb.ybye ) qcybye, sum ( fb.fx * fb.fbye ) qcfbye, sum ( fb.fx * fb.bbye ) qcbbye
5 from arap_djzb_part zb, arap_djfb fb, bd_cumandoc ksglda
6 where zb.vouchid = fb.vouchid and fb.ksbm_cl = ksglda.pk_cumandoc ( + ) and
7 ( ( zb.dwbm = '1011' ) and zb.djzt >= 1 and zb.hzbz is null and fb.wldx = 0 and
8 ( zb.qcbz = 'Y' or zb.djrq < '2009-06-02' ) and fb.xgbh <> 1 and
9 ( fb.bz_date is null or fb.bz_date < '2009-06-02' ) and ( zb.djdl = 'ys' or zb.djdl = 'sk' ) and
10 ( fb.ksbm_cl is not null ) and ( ksglda.custflag = '0' or ksglda.custflag = '2' ) and
11 ( fb.dr = 0 and zb.dr = 0 and ksglda.dr = 0 ) ) group by FB.KSBM_CL
12 /
Explained.
Elapsed: 00:00:00.26
NCV31@NCDB>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 896126388
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 36590 | 5145K| | 122K (8)| 00:11:42 | | |
| 1 | HASH GROUP BY | | 36590 | 5145K| 53M| 122K (8)| 00:11:42 | | |
|* 2 | HASH JOIN | | 322K| 44M| 5808K| 119K (8)| 00:11:24 | | |
|* 3 | TABLE ACCESS FULL | BD_CUMANDOC | 160K| 3919K| | 1589 (12)| 00:00:10 | | |
|* 4 | HASH JOIN | | 333K| 37M| 7240K| 117K (8)| 00:11:09 | | |
| 5 | PARTITION LIST SINGLE| | 119K| 5832K| | 764 (9)| 00:00:05 | KEY | KEY |
|* 6 | TABLE ACCESS FULL | ARAP_DJZB_PART | 119K| 5832K| | 764 (9)| 00:00:05 | 10 | 10 |
|* 7 | TABLE ACCESS FULL | ARAP_DJFB | 8195K| 539M| | 102K (8)| 00:09:45 | | |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("FB"."KSBM_CL"="KSGLDA"."PK_CUMANDOC")
3 - filter(("KSGLDA"."CUSTFLAG"='0' OR "KSGLDA"."CUSTFLAG"='2') AND "KSGLDA"."DR"=0)
4 - access("ZB"."VOUCHID"="FB"."VOUCHID")
6 - filter(("ZB"."DJDL"='sk' OR "ZB"."DJDL"='ys') AND "ZB"."DR"=0 AND ("ZB"."DJRQ" "ZB"."QCBZ"='Y') AND "ZB"."DJZT">=1 AND "ZB"."HZBZ" IS NULL)
7 - filter("FB"."KSBM_CL" IS NOT NULL AND "FB"."WLDX"=0 AND "FB"."XGBH"<>1 AND "FB"."DR"=0 AND
("FB"."BZ_DATE" IS NULL OR "FB"."BZ_DATE"
Note
-----
- dynamic sampling used for this statement
29 rows selected.
Elapsed: 00:00:00.14
问题是:如何判断这两参数值多少,适合当前的数据库?
optimizer_index_caching integer 80
optimizer_index_cost_adj integer 45
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/13165828/viewspace-605673/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/13165828/viewspace-605673/