查看: 4651|回复: 6

[性能调整] 一次嵌套循环的优化过程

[复制链接]
论坛徽章:
5
ITPUB9周年纪念徽章
日期:2010-10-08 09:28:51数据库板块每日发贴之星
日期:2011-07-22 01:01:02蜘蛛蛋
日期:2011-08-24 14:10:13ITPUB十周年纪念徽章
日期:2011-11-01 16:24:042012新春纪念徽章
日期:2012-01-04 11:54:26
发表于 2011-7-21 22:04 | 显示全部楼层 |阅读模式
主要环境如下:

SQL> select * from v$version;

BANNER
-------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

4节点 HPUX RAC OLAP 环境

SQL> show parameter db_block_size

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------
db_block_size                        integer                           16384
SQL> show parameter db_file

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------
db_file_multiblock_read_count        integer                           64


ETL 开发人员发来邮件说有个long running job,跑了2小时左右 还未完成 叫我check一下

SQL> select * from table(dbms_xplan.display_cursor('gh1hw18uz6dcm',0));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  gh1hw18uz6dcm, child number 0
-------------------------------------
create table OPT_REF_BASE_UOM_TEMP_SDIM  parallel 2   TABLESPACE
OPTIMA01M  nologging as SELECT PROD_SKID,         RELTV_CURR_QTY,
  STAT_CURR_VAL,         BAR_CURR_CODE    FROM OPT_REF_BASE_UOM_DIM_VW

Plan hash value: 2933813170

------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT              |                       |       |       |  8883 (100)|       |           |      |            |
|   1 |  PX COORDINATOR                     |                       |       |       |            |       |           |      |            |
|   2 |   PX SEND QC (RANDOM)               | :TQ10001              |    54 |  2916 |  8882   (1)| 00:01:02 |  Q1,01 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT                   |                       |       |       |            |       |  Q1,01 | PCWP |               |
|   4 |     HASH GROUP BY                   |                       |    54 |  2916 |  8882   (1)| 00:01:02 |  Q1,01 | PCWP |            |
|   5 |      PX RECEIVE                     |                       |    54 |  2916 |  8882   (1)| 00:01:02 |  Q1,01 | PCWP |            |
|   6 |       PX SEND HASH                  | :TQ10000              |    54 |  2916 |  8882   (1)| 00:01:02 |  Q1,00 | P->P | HASH       |
|   7 |        HASH GROUP BY                |                       |    54 |  2916 |  8882   (1)| 00:01:02 |  Q1,00 | PCWP |            |
|   8 |         NESTED LOOPS                |                       |       |       |            |       |  Q1,00 | PCWP |               |
|   9 |          NESTED LOOPS               |                       |  3134 |   165K|  8881   (1)| 00:01:02 |  Q1,00 | PCWP |            |
|  10 |           PX BLOCK ITERATOR         |                       |       |       |            |       |  Q1,00 | PCWC |               |
|* 11 |            TABLE ACCESS FULL        | OPT_REF_UOM_TEMP_SDIM |  3065 |   104K|   355  (13)| 00:00:03 |  Q1,00 | PCWP |            |
|* 12 |           INDEX RANGE SCAN          | PROD_DIM_PK           |     3 |       |     1   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|* 13 |          TABLE ACCESS BY INDEX ROWID| PROD_DIM              |     1 |    19 |     3   (0)| 00:00:01 |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  11 - access(:Z>=:Z AND :Z<=:Z)
       filter("UOM"."RELTV_CURR_QTY"=1)
  12 - access("PROD"."PROD_SKID"="UOM"."PROD_SKID")
  13 - filter(("PROD"."BUOM_CURR_SKID" IS NOT NULL AND "PROD"."PROD_END_DATE"=TO_DATE(' 9999-12-31 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "PROD"."CURR_IND"='Y' AND "PROD"."BUOM_CURR_SKID"="UOM"."UOM_SKID"))


36 rows selected.

那么这个JOB 很简单,就是一个 create table xxx as select

上面的执行计划是有问题的,执行计划里面显示有PX 操作,其实我没发现有并行,不过上面的执行计划访问路径是对的
关于执行计划显示错误,这里不予讨论,11G上面有很多BUG 我们已经遇到5个了,另外也不要问我为什么升级到11gR1

那么实际上要执行的SQL就是
create table OPT_REF_BASE_UOM_TEMP_SDIM  parallel 2   TABLESPACE
OPTIMA01M  nologging as SELECT PROD_SKID,         RELTV_CURR_QTY,
  STAT_CURR_VAL,         BAR_CURR_CODE    FROM OPT_REF_BASE_UOM_DIM_VW

OPT_REF_BASE_UOM_DIM_VW是一个视图,该视图定义如下

SELECT UOM.PROD_SKID,
       MAX (UOM.RELTV_CURR_QTY) RELTV_CURR_QTY,
       MAX (UOM.STAT_CURR_VAL) STAT_CURR_VAL,
       MAX (UOM.BAR_CURR_CODE) BAR_CURR_CODE
--modified by Daniel on 10/26/2010 for CR 326285 of QMR10.1 hypercare retrofit begin
FROM OPT_REF_UOM_TEMP_SDIM UOM,
     REF_PROD_DIM PROD
WHERE UOM.RELTV_CURR_QTY = 1
      AND PROD.CURR_IND = 'Y'
      AND PROD.PROD_END_DATE = TO_DATE ('31-12-9999', 'dd-mm-yyyy')
      AND PROD.PROD_SKID = UOM.PROD_SKID
      AND PROD.BUOM_CURR_SKID = UOM.UOM_SKID
--modified by Daniel on 10/26/2010 for CR 326285 of QMR10.1 hypercare retrofit end
GROUP BY UOM.PROD_SKID

那么现在 这个视图的查询效率就直接决定了该JOB的效率,我们 现在来看这个视图的执行计划

SQL> explain plan for SELECT UOM.PROD_SKID,
  2         MAX (UOM.RELTV_CURR_QTY) RELTV_CURR_QTY,
  3         MAX (UOM.STAT_CURR_VAL) STAT_CURR_VAL,
  4         MAX (UOM.BAR_CURR_CODE) BAR_CURR_CODE
  5  --modified by Daniel on 10/26/2010 for CR 326285 of QMR10.1 hypercare retrofit begin
  6  FROM OPT_REF_UOM_TEMP_SDIM UOM,
  7       REF_PROD_DIM PROD
  8  WHERE UOM.RELTV_CURR_QTY = 1
  9        AND PROD.CURR_IND = 'Y'
10        AND PROD.PROD_END_DATE = TO_DATE ('31-12-9999', 'dd-mm-yyyy')
11        AND PROD.PROD_SKID = UOM.PROD_SKID
12        AND PROD.BUOM_CURR_SKID = UOM.UOM_SKID
13  --modified by Daniel on 10/26/2010 for CR 326285 of QMR10.1 hypercare retrofit end
14  GROUP BY UOM.PROD_SKID;

Explained.

Elapsed: 00:00:01.29
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------

Plan hash value: 3215660883

-------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                       |    78 |  4212 | 15507   (1)| 00:01:47 |
|   1 |  HASH GROUP BY                |                       |    78 |  4212 | 15507   (1)| 00:01:47 |
|   2 |   NESTED LOOPS                |                       |       |       |            |          |
|   3 |    NESTED LOOPS               |                       |  3034 |   159K| 15506   (1)| 00:01:47 |
|*  4 |     TABLE ACCESS FULL         | OPT_REF_UOM_TEMP_SDIM |  2967 |   101K|   650  (14)| 00:00:05 |
|*  5 |     INDEX RANGE SCAN          | PROD_DIM_PK           |     3 |       |     2   (0)| 00:00:01 |
|*  6 |    TABLE ACCESS BY INDEX ROWID| PROD_DIM              |     1 |    19 |     5   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("UOM"."RELTV_CURR_QTY"=1)
   5 - access("PROD"."PROD_SKID"="UOM"."PROD_SKID")
   6 - filter("PROD"."BUOM_CURR_SKID" IS NOT NULL AND "PROD"."PROD_END_DATE"=TO_DATE('
              9999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "PROD"."CURR_IND"='Y' AND
              "PROD"."BUOM_CURR_SKID"="UOM"."UOM_SKID")

22 rows selected.

Elapsed: 00:00:02.06

大家请看执行计划 中ID=3的步骤,cost从 650一下子飙升到15506,根据我的SQL调优经验,这是非常不能理解的。
我怀疑CBO选出了执行计划。

那么这里OPT_REF_UOM_TEMP_SDIM 作为驱动表,Oracle先对它做一次全表扫描,然后应用过滤条件
WHERE UOM.RELTV_CURR_QTY = 1 所得到的数据作为驱动行源(你可以看 filter("UOM"."RELTV_CURR_QTY"=1))
那么这里 CBO计算出 根据 filter("UOM"."RELTV_CURR_QTY"=1) 过滤后 会返回2967条数据


SQL> select count(*) from OPT_REF_UOM_TEMP_SDIM;

  COUNT(*)
----------
   2137706

Elapsed: 00:00:08.87
SQL> select count(*) from OPT_REF_UOM_TEMP_SDIM where "RELTV_CURR_QTY"=1;

  COUNT(*)
----------
    946432

Elapsed: 00:00:01.54
SQL> select 946432/2137706 from dual;

946432/2137706
--------------
    .442732537

Elapsed: 00:00:02.04

根据我下面的查询,那么很明显CBO计算错误,filter("UOM"."RELTV_CURR_QTY"=1) 过滤之后实际上会返回946432条数据
然而 CBO认为只返回 2967条数据,所以这里不应该走嵌套循环,应该走 hash。
为什么不应该走嵌套循环?原因在于

SQL> select 946432/2137706 from dual;

946432/2137706
--------------
    .442732537
全表扫描然后过滤之后 会返回44%的数据, 什么时候应该选择嵌套循环 ,通常情况下驱动表(行源) 应该返回
少量数据,而且被驱动表上面有选择性很高的索引才选择 嵌套循环。也就是说通常情况下驱动表应该返回不超过
源表10%的数据,而这里居然返回44%的数据,明显打错特错了。

我自己测试了一下,如果走嵌套循环要花 2小时40分钟

867176 rows selected.

Elapsed: 02:39:58.33

Execution Plan
----------------------------------------------------------                                                                                                   
Plan hash value: 3215660883                                                                                                                                 
                                                                                                                                                            
-------------------------------------------------------------------------------------------------------                                                      
| Id  | Operation                     | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |                                                      
-------------------------------------------------------------------------------------------------------                                                      
|   0 | SELECT STATEMENT              |                       |     2 |   108 | 15996   (1)| 00:01:50 |                                                      
|   1 |  HASH GROUP BY                |                       |     2 |   108 | 15996   (1)| 00:01:50 |                                                      
|   2 |   NESTED LOOPS                |                       |       |       |            |          |                                                      
|   3 |    NESTED LOOPS               |                       |  3134 |   165K| 15995   (1)| 00:01:50 |                                                      
|*  4 |     TABLE ACCESS FULL         | OPT_REF_UOM_TEMP_SDIM |  3065 |   104K|   649  (14)| 00:00:05 |                                                      
|*  5 |     INDEX RANGE SCAN          | PROD_DIM_PK           |     3 |       |     2   (0)| 00:00:01 |                                                      
|*  6 |    TABLE ACCESS BY INDEX ROWID| PROD_DIM              |     1 |    19 |     5   (0)| 00:00:01 |                                                      
-------------------------------------------------------------------------------------------------------                                                      
                                                                                                                                                            
Predicate Information (identified by operation id):                                                                                                         
---------------------------------------------------                                                                                                         
                                                                                                                                                            
   4 - filter("UOM"."RELTV_CURR_QTY"=1)                                                                                                                     
   5 - access("PROD"."PROD_SKID"="UOM"."PROD_SKID")                                                                                                         
   6 - filter("PROD"."BUOM_CURR_SKID" IS NOT NULL AND "PROD"."PROD_END_DATE"=TO_DATE('                                                                       
              9999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "PROD"."CURR_IND"='Y' AND                                                                  
              "PROD"."BUOM_CURR_SKID"="UOM"."UOM_SKID")                                                                                                      


注意,这个嵌套循环的数据和上面的嵌套循环的数据有点出入,原因是数据有变动,统计信息有变动,不过不影响案例

如果走 HASH 连接 ,只要 8 分钟左右,怎么让它走hash? 这里我给大家列举3中方法

1 使用hint full     强制PROD_DIM 走全表扫描
2 使用hint use_hash 强制2表做hash连接
3 使用hint leading  强制 PROD_DIM 作为驱动表

867176 rows selected.

Elapsed: 00:07:52.33

Execution Plan
----------------------------------------------------------                                                                                                   
Plan hash value: 612020119                                                                                                                                   
                                                                                                                                                            
-----------------------------------------------------------------------------------------------------                                                        
| Id  | Operation           | Name                  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                                        
-----------------------------------------------------------------------------------------------------                                                        
|   0 | SELECT STATEMENT    |                       |    60 |  3240 |       | 44136   (5)| 00:05:04 |                                                        
|   1 |  HASH GROUP BY      |                       |    60 |  3240 |       | 44136   (5)| 00:05:04 |                                                        
|*  2 |   HASH JOIN         |                       |  3065 |   161K|    29M| 44135   (5)| 00:05:04 |                                                        
|*  3 |    TABLE ACCESS FULL| PROD_DIM              |   998K|    18M|       | 43022   (5)| 00:04:56 |                                                        
|*  4 |    TABLE ACCESS FULL| OPT_REF_UOM_TEMP_SDIM |  3065 |   104K|       |   649  (14)| 00:00:05 |                                                        
-----------------------------------------------------------------------------------------------------                                                        
                                                                                                                                                            
Predicate Information (identified by operation id):                                                                                                         
---------------------------------------------------                                                                                                         
                                                                                                                                                            
   2 - access("PROD"."PROD_SKID"="UOM"."PROD_SKID" AND                                                                                                      
              "PROD"."BUOM_CURR_SKID"="UOM"."UOM_SKID")                                                                                                      
   3 - filter("PROD"."BUOM_CURR_SKID" IS NOT NULL AND "PROD"."PROD_END_DATE"=TO_DATE('                                                                       
              9999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "PROD"."CURR_IND"='Y')                                                                     
   4 - filter("UOM"."RELTV_CURR_QTY"=1)                                       

那么到这里 还没完,我们不仅仅要对SQL做优化,还要搞清楚为什么CBO选择错了执行计划,根据上面的分析,
CBO选择错误的执行计划的原因在于 它认为 filter("UOM"."RELTV_CURR_QTY"=1) 只返回2967 条数据,所以
为题出现在统计信息 上面,而且是列统计信息上面 那么我们来查询一些 列的统计信息

SQL> select a.owner ||'.'||a.table_name name ,a.column_name,b.num_rows,a.num_distinct Cardinality,a.num_distinct/b.num_rows selectivity,
  2  num_nulls,density,a.histogram,a.num_buckets from dba_tab_col_statistics a,dba_tables b where a.owner=b.owner and a.table_name=b.table_name
  3  and a.owner=upper('adwu_optima_we11') and a.table_name=upper('OPT_REF_UOM_TEMP_SDIM') and a.column_name=upper('RELTV_CURR_QTY');

NAME                                     COLUMN_NAME            NUM_ROWS CARDINALITY SELECTIVITY  NUM_NULLS    DENSITY HISTOGRAM
---------------------------------------- -------------------- ---------- ----------- ----------- ---------- ---------- --------------------------
ADWU_OPTIMA_WE11.OPT_REF_UOM_TEMP_SDIM   RELTV_CURR_QTY          2160000         728  .000337037       0 .001373626 NONE

大家请看,列上面一共只有728个基数(唯一值),然而表有200多万的数据,并且没有对列收集过直方图统计
那么我现在对该列收集直方图

SQL> BEGIN
  2  DBMS_STATS.GATHER_TABLE_STATS(ownname => 'adwu_optima_we11',
  3  tabname => 'OPT_REF_UOM_TEMP_SDIM',
  4  estimate_percent => 100,
  5  method_opt => 'for columns RELTV_CURR_QTY size 200',
  6  degree => DBMS_STATS.AUTO_DEGREE,
  7  cascade=>TRUE
  8  );
  9  END;
10  /

PL/SQL procedure successfully completed.

我再来查看一下该列的统计信息,这里基数上升到2110,并且直方图也收集了

SQL> select a.owner ||'.'||a.table_name name ,a.column_name,b.num_rows,a.num_distinct Cardinality,a.num_distinct/b.num_rows selectivity,
  2  num_nulls,density,a.histogram,a.num_buckets from dba_tab_col_statistics a,dba_tables b where a.owner=b.owner and a.table_name=b.table_name
  3  and a.owner=upper('adwu_optima_we11') and a.table_name=upper('OPT_REF_UOM_TEMP_SDIM') and a.column_name=upper('RELTV_CURR_QTY');

NAME                                     COLUMN_NAME            NUM_ROWS CARDINALITY SELECTIVITY  NUM_NULLS    DENSITY HISTOGRAM
---------------------------------------- -------------------- ---------- ----------- ----------- ---------- ---------- ----------------------------
ADWU_OPTIMA_WE11.OPT_REF_UOM_TEMP_SDIM   RELTV_CURR_QTY          2137706        2110  .000987039       0  .00217122 HEIGHT BALANCED

我们再来查看一下执行计划

SQL> explain plan for SELECT UOM.PROD_SKID,
  2         MAX (UOM.RELTV_CURR_QTY) RELTV_CURR_QTY,
  3         MAX (UOM.STAT_CURR_VAL) STAT_CURR_VAL,
  4         MAX (UOM.BAR_CURR_CODE) BAR_CURR_CODE
  5  --modified by Daniel on 10/26/2010 for CR 326285 of QMR10.1 hypercare retrofit begin
  6  FROM OPT_REF_UOM_TEMP_SDIM UOM,
  7       REF_PROD_DIM PROD
  8  WHERE UOM.RELTV_CURR_QTY = 1
  9        AND PROD.CURR_IND = 'Y'
10        AND PROD.PROD_END_DATE = TO_DATE ('31-12-9999', 'dd-mm-yyyy')
11        AND PROD.PROD_SKID = UOM.PROD_SKID
12        AND PROD.BUOM_CURR_SKID = UOM.UOM_SKID
13  --modified by Daniel on 10/26/2010 for CR 326285 of QMR10.1 hypercare retrofit end
14  GROUP BY UOM.PROD_SKID;

Explained.

Elapsed: 00:00:00.82

这回执行计划就走对了

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------

Plan hash value: 612020119

-----------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                       | 12097 |   637K|       | 44911   (5)| 00:05:09 |
|   1 |  HASH GROUP BY      |                       | 12097 |   637K|       | 44911   (5)| 00:05:09 |
|*  2 |   HASH JOIN         |                       |   951K|    48M|    29M| 44799   (5)| 00:05:08 |
|*  3 |    TABLE ACCESS FULL| PROD_DIM              |   998K|    18M|       | 43022   (5)| 00:04:56 |
|*  4 |    TABLE ACCESS FULL| OPT_REF_UOM_TEMP_SDIM |   951K|    31M|       |   654  (15)| 00:00:05 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("PROD"."PROD_SKID"="UOM"."PROD_SKID" AND
              "PROD"."BUOM_CURR_SKID"="UOM"."UOM_SKID")
   3 - filter("PROD"."BUOM_CURR_SKID" IS NOT NULL AND "PROD"."PROD_END_DATE"=TO_DATE('
              9999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "PROD"."CURR_IND"='Y')
   4 - filter("UOM"."RELTV_CURR_QTY"=1)

20 rows selected.

也许你们说 是由于统计信息过期导致的,那么我现在把直方图删除

SQL> BEGIN
  2  DBMS_STATS.GATHER_TABLE_STATS(ownname => 'adwu_optima_we11',
  3  tabname => 'OPT_REF_UOM_TEMP_SDIM',
  4  estimate_percent => 100,
  5  method_opt => 'for columns RELTV_CURR_QTY size 1',
  6  degree => DBMS_STATS.AUTO_DEGREE,
  7  cascade=>TRUE
  8  );
  9  END;
10  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:12.65
SQL> explain plan for SELECT UOM.PROD_SKID,
  2         MAX (UOM.RELTV_CURR_QTY) RELTV_CURR_QTY,
  3         MAX (UOM.STAT_CURR_VAL) STAT_CURR_VAL,
  4         MAX (UOM.BAR_CURR_CODE) BAR_CURR_CODE
  5  --modified by Daniel on 10/26/2010 for CR 326285 of QMR10.1 hypercare retrofit begin
  6  FROM OPT_REF_UOM_TEMP_SDIM UOM,
  7       REF_PROD_DIM PROD
  8  WHERE UOM.RELTV_CURR_QTY = 1
  9        AND PROD.CURR_IND = 'Y'
10        AND PROD.PROD_END_DATE = TO_DATE ('31-12-9999', 'dd-mm-yyyy')
11        AND PROD.PROD_SKID = UOM.PROD_SKID
12        AND PROD.BUOM_CURR_SKID = UOM.UOM_SKID
13  --modified by Daniel on 10/26/2010 for CR 326285 of QMR10.1 hypercare retrofit end
14  GROUP BY UOM.PROD_SKID;

Explained.

Elapsed: 00:00:00.82
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------

Plan hash value: 3215660883

-------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                       |     9 |   486 |  5722   (2)| 00:00:40 |
|   1 |  HASH GROUP BY                |                       |     9 |   486 |  5722   (2)| 00:00:40 |
|   2 |   NESTED LOOPS                |                       |       |       |            |          |
|   3 |    NESTED LOOPS               |                       |  1036 | 55944 |  5721   (2)| 00:00:40 |
|*  4 |     TABLE ACCESS FULL         | OPT_REF_UOM_TEMP_SDIM |  1013 | 35455 |   650  (14)| 00:00:05 |
|*  5 |     INDEX RANGE SCAN          | PROD_DIM_PK           |     3 |       |     2   (0)| 00:00:01 |
|*  6 |    TABLE ACCESS BY INDEX ROWID| PROD_DIM              |     1 |    19 |     5   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("UOM"."RELTV_CURR_QTY"=1)
   5 - access("PROD"."PROD_SKID"="UOM"."PROD_SKID")
   6 - filter("PROD"."BUOM_CURR_SKID" IS NOT NULL AND "PROD"."PROD_END_DATE"=TO_DATE('
              9999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "PROD"."CURR_IND"='Y' AND
              "PROD"."BUOM_CURR_SKID"="UOM"."UOM_SKID")

22 rows selected.

Elapsed: 00:00:01.86

所以,最终导致CBO选错执行计划的罪魁祸首就是列没有收集直方图统计

通过这一次的案例分析,相信大家对嵌套循环的优化应该不成问题了,大家一定要深刻理解嵌套循环的原理
另外一个就是,大家要深入研究统计信息,CBO能不能选对执行计划,很关键的就在于统计信息的准确与否。
论坛徽章:
0
发表于 2011-7-23 10:00 | 显示全部楼层
友情up

使用道具 举报

回复
论坛徽章:
9993
地主之星
日期:2015-07-20 17:15:36地主之星
日期:2015-09-01 14:14:25地主之星
日期:2015-09-01 17:59:09地主之星
日期:2015-08-31 16:17:58地主之星
日期:2015-08-31 16:17:58地主之星
日期:2015-08-31 16:17:58地主之星
日期:2015-08-31 16:17:58地主之星
日期:2015-08-31 16:17:58地主之星
日期:2015-08-31 16:17:58地主之星
日期:2015-08-31 16:17:58
发表于 2011-7-23 10:48 | 显示全部楼层
不错

使用道具 举报

回复
论坛徽章:
0
发表于 2011-7-23 12:22 | 显示全部楼层

学习了,很好的案例

使用道具 举报

回复
论坛徽章:
7
ITPUB十周年纪念徽章
日期:2011-11-01 16:25:222012新春纪念徽章
日期:2012-01-04 11:56:01迷宫蛋
日期:2012-02-22 18:49:07现代
日期:2013-12-26 11:24:06喜羊羊
日期:2015-03-04 14:52:462015年新春福章
日期:2015-03-06 11:58:18喜羊羊
日期:2015-03-15 23:32:11
发表于 2011-7-24 00:47 | 显示全部楼层
新手,看着很震撼,谢谢师傅

使用道具 举报

回复
论坛徽章:
13
迷宫蛋
日期:2011-07-15 14:34:42ITPUB14周年纪念章
日期:2015-10-26 17:23:44马上加薪
日期:2014-12-24 09:21:29马上加薪
日期:2014-10-29 09:58:26优秀写手
日期:2014-01-15 06:00:15双黄蛋
日期:2013-07-22 09:58:17茶鸡蛋
日期:2013-04-01 15:12:012013年新春福章
日期:2013-02-25 14:51:24奥运纪念徽章
日期:2012-11-13 15:08:422012新春纪念徽章
日期:2012-01-04 11:56:44
发表于 2011-7-24 18:02 | 显示全部楼层
从这个案例得到的一个信息就是列的统计信息收集不及时,如果直接收集该列所在的表的统计信息,不知道效果会是怎么样??

使用道具 举报

回复
论坛徽章:
1
ITPUB十周年纪念徽章
日期:2011-11-01 16:26:59
发表于 2011-7-24 18:34 | 显示全部楼层
貌似10g后,可以调度job让系统自动收集相关统计信息。
如果不能自动调度,每隔一段时间直方图就不准了,这就很烦了。

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

SACC2019中国系统架构师大会

【数字转型 架构演进】SACC2019中国系统架构师大会,7折限时优惠重磅来袭!
2019年10月31日~11月2日第11届中国系统架构师大会(SACC2019)将在北京隆重召开。四大主线并行的演讲模式,1个主会场、20个技术专场、超千人参与的会议规模,100+来自互联网、金融、制造业、电商等领域的嘉宾阵容,将为广大参会者提供一场最具价值的技术交流盛会。

限时七折期:2019年8月31日前


----------------------------------------

大会官网>>
TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表