一条SQL优化

paulyibinyi

rbo?

tan9030

set autotrace trace expain 看不到统计数据
使用autotrace on/traceonly,运行sql会失败，
不过我刚刚对上面的sql加hint,可以明显的提高性能（如下）

select /*+ ordered full(a) full(b) full(c) full(d) use_hash(a b c d) */ distinct
        b.WL_KEY,
        b.DATA_TYPE,
        b.TEST_AREA,
        b.START_T,
        b.FINISH_T,
        b.TESTER_TYPE,
        b.NODE_NAME,
        b.JOB_NAME,
        b.JOB_REV,
        b.LOT_ID,
        b.SUBLOT_ID,
        b.TEST_TEMP,
        a.TEST_NUMBER,
        d.TEST_NAME,
        d.RESULT_SCALE,
        a.WL_TEST_MIN_VALUE,
        a.WL_TEST_MAX_VALUE,
        a.WL_TEST_SUM_VALUE,
        a.WL_TEST_SUM_SQUARES,
        a.POPULATION,
        b.SITE_NUMBERS,
        b.SITE_COUNT,
        c.WL_PART_CNT,
        c.WL_GOOD_CNT,
        c.WL_RTST_CNT,
        a.WL_TEST_EXECUTION_CNT
from TDB_TEST_RESULTS_WL a,
        TDB_REGISTER_WL b,
        TDB_PART_RESULTS_WL c,
        TDB_JOB_TEST d
where b.WL_KEY = a.WL_KEY
   and b.WL_KEY = c.WL_KEY
   and a.WL_KEY = c.WL_KEY
   and a.SITE_NUMBER = c.SITE_NUMBER
   and a.TEST_NUMBER = d.TEST_NUMBER
   and b.JOB_KEY = d.JOB_KEY
   and d.TEST_NUMBER=0
   and c.WL_PART_CNT>0
   and b.START_T BETWEEN to_date('27-Feb-2008 20:00:00','DD-MON-YYYY HH24:MI:SS')
   AND to_date('27-Feb-2008 23:59:59','DD-MON-YYYY HH24:MI:SS');

tan9030

从4个小时出现rollback segment错误, 现在只需要6分钟就可以得到正确的结果。应该是改变了计算方式，我一会儿把加hint的sql地执行计划贴上来，稍等。

tan9030

原帖由 paulyibinyi 于 2008-3-4 11:41 发表
rbo?

choose.

wobenkl

看来是选择优化器的时候出的问题

枯荣长老

1. d.TEST_NUMBER=0   --> 这个对应多少记录数

2. c.WL_PART_CNT>0   --> 这个对应多少记录数

3. b.START_T BETWEEN to_date('27-Feb-2008 20:00:00','DD-MON-YYYY HH24:MI:SS')
   AND to_date('27-Feb-2008 23:59:59','DD-MON-YYYY HH24:MI:SS')); ----> 这个对应多少记录数

tan9030

应该是，下面是修改后的执行计划：

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=338271 Card=1 Bytes=
          139)

   1    0   SORT (AGGREGATE)
   2    1     HASH JOIN (Cost=338271 Card=1 Bytes=139)
   3    2       HASH JOIN (Cost=331480 Card=1 Bytes=113)
   4    3         HASH JOIN (Cost=330671 Card=5699 Bytes=421726)
   5    4           TABLE ACCESS (FULL) OF 'TDB_TEST_RESULTS_WL' (Cost
          =327931 Card=208095 Bytes=8115705)

   6    4           TABLE ACCESS (FULL) OF 'TDB_REGISTER_WL' (Cost=257
          4 Card=5463 Bytes=191205)

   7    3         TABLE ACCESS (FULL) OF 'TDB_PART_RESULTS_WL' (Cost=7
          94 Card=33632 Bytes=1311648)

   8    2       TABLE ACCESS (FULL) OF 'TDB_JOB_TEST' (Cost=6790 Card=
          57655 Bytes=1499030)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
    4382559  consistent gets
    4353562  physical reads
          0  redo size
        493  bytes sent via SQL*Net to client
        655  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

tan9030

原帖由 枯荣长老 于 2008-3-4 13:01 发表
1. d.TEST_NUMBER=0   --> 这个对应多少记录数

2. c.WL_PART_CNT>0   --> 这个对应多少记录数

3. b.START_T BETWEEN to_date('27-Feb-2008 20:00:00','DD-MON-YYYY HH24:MI:SS')
   AND to_date('27-Feb-2008 23:59:59','DD-MON-YYYY HH24:MI:SS')); ----> 这个对应多少记录数

--1.  d.TEST_NUMBER=0   -->41263
--2.  c.WL_PART_CNT>0   -->1468553
--3.  b.START_T BETWEEN to_date('27-Feb-2008 20:00:00','DD-MON-YYYY HH24:MI:SS')
   AND to_date('27-Feb-2008 23:59:59','DD-MON-YYYY HH24:MI:SS'));    -->10171

请参考

ZALBB

里面约束条件最强的是b.START_T BETWEEN to_date('27-Feb-2008 20:00:00','DD-MON-YYYY HH24:MI:SS')
   AND to_date('27-Feb-2008 23:59:59','DD-MON-YYYY HH24:MI:SS'))，

尝试改变相关的索引字段的位置，以该字段START_T为开头．

foxmile

加提示之前和加之后效率差多少