ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 执行计划-优化器选择

执行计划-优化器选择

原创 Linux操作系统 作者:fengjin821 时间:2009-06-09 20:51:45 0 删除 编辑
ORACLE 10204,AIX5300-07


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"<'2009-06-02'))
   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"<'2009-06-02' AND "ZB"."HZBZ" IS NULL AND "ZB"."DJZT">=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"<'2009-06-02'))
  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"<'2009-06-02' OR
              "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"<'2009-06-02'))

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 得来的.




ZALBB 上传了这个附件:
2009-6-9 17:17
  下载次数: 0
ncdb_ora_3563894.txt (426.88 KB)
 
2009-6-9 17:17
  下载次数: 0
ncdb_ora_5423330.txt (290.72 KB)
 

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"<'2009-06-02'))
   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"<'2009-06-02' AND "ZB"."HZBZ" IS NULL AND "ZB"."DJZT">=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"<'2009-06-02'))
  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"<'2009-06-02' OR
              "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"<'2009-06-02'))

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"<'2009-06-02'))
   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"<'2009-06-02' AND "ZB"."HZBZ" IS NULL AND "ZB"."DJZT">=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"<'2009-06-02'))
  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"<'2009-06-02' OR
              "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"<'2009-06-02'))

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-04-29

  • 博文量
    191
  • 访问量
    508000