两台服务器的ORACLE版本和初始化参数都配置的基本一样的, 但是同一条SQL的执行计划和效率相差很大(一个是HASH JOIN,一个是NESTED LOOP),不知为何?请看一下

Question

两台服务器的ORACLE版本和初始化参数都配置的基本一样的, 但是同一条SQL的执行计划和效率相差很大,
以下是SQL和其在两台机上的执行计划, 计划1是一台新装的服务器的,计划2是一台旧服务器的(新服务的硬件还优一些),这台旧服务器之前有个专业的DBA整理过,计划2中奇怪的是STOCK, GR_DTL,GR_BATCH三个表都是用HASH连接的全表扫描,SQL执行速度要比 计划1按INDEX快的多, 但是计划1这台机的HASH_JOIN_ENABLED为TRUE,且HASH_AREA_SIZE,SORT_AREA_SIZE与计划2机是相同的,就是不会用HASH连接, 这个不知是不是ORACLE如何配置可以配置到指定的表在用指定INDEX时就用HASH连接?
请DBA或高人分析一下, 主要是这种多表连接的情况很多,感觉好像基础的一些表HASH连接全表扫描快很多,怎么设置成与计划2机执行计划效果相同?
SQL如下
select a.com_id,d.ware_dist as shop_code,sum(L.PUR_QTY * A.Base_Unit_Cost) as PI_Total, SUM(L.PUR_QTY * (case when sys.pos_curr = 'MOP' then a.RtlMOP when sys.pos_curr = 'HKD' then a.RtlHKD when sys.pos_curr = 'RMB' then a.RtlRMB END)) as PI_Retail from GR_DTL a left join GR_HDR b on a.com_id = b.com_id and a.gr_num = b.gr_num left join Gr_Batch d on a.com_id = d.com_id and a.gr_num = d.gr_num and a.line_nbr = d.line_nbr LEFT JOIN GR_BATCH_DTL L ON  D.COM_ID = L.COM_ID AND D.GR_NUM = L.GR_NUM AND D.LINE_NBR = L.LINE_NBR AND D.BATCH_LINE = L.BATCH_LINE left join stock c on a.com_id = c.com_id and a.item_code = c.item_code left join sysdatabase sys on b.com_code = sys.com_id where a.com_id = 'ASC       ' and b.isConsign = 0 and b.POSTED<>' ' and c.nyear = '2007' and c.ssn_code = 'FW' AND d.ware_dist = '321' AND b.billdate <= TO_DATE('20071231 23:59:59','YYYYMMDD HH24:MI:SS') group by a.com_id,d.ware_dist执行计划1:
SELECT STATEMENT, GOAL = CHOOSE Cost=11 Cardinality=1 Bytes=458
 SORT GROUP BY NOSORT Cost=11 Cardinality=1 Bytes=458
  NESTED LOOPS OUTER Cost=11 Cardinality=1 Bytes=458
   NESTED LOOPS Cost=9 Cardinality=1 Bytes=375
    NESTED LOOPS OUTER Cost=7 Cardinality=1 Bytes=298
     NESTED LOOPS Cost=6 Cardinality=1 Bytes=279
      NESTED LOOPS Cost=5 Cardinality=1 Bytes=222
       TABLE ACCESS BY INDEX ROWID Object owner=ARMANI Object name=GR_HDR Cost=3 Cardinality=1 Bytes=81
        INDEX RANGE SCAN Object owner=ARMANI Object name=INDEX_GR_POSTED Cost=2 Cardinality=16
       TABLE ACCESS BY INDEX ROWID Object owner=ARMANI Object name=GR_DTL Cost=2 Cardinality=1 Bytes=141
        INDEX RANGE SCAN Object owner=ARMANI Object name=PK_GR_DTL Cost=1 Cardinality=1
      TABLE ACCESS BY INDEX ROWID Object owner=ARMANI Object name=STOCK Cost=1 Cardinality=1 Bytes=57
       INDEX UNIQUE SCAN Object owner=ARMANI Object name=PK_STOCK Cardinality=277222
     TABLE ACCESS BY INDEX ROWID Object owner=ARMANI Object name=SYSDATABASE Cost=1 Cardinality=1 Bytes=19
      INDEX UNIQUE SCAN Object owner=ARMANI Object name=PK_SYSDATABASE Cardinality=1
    TABLE ACCESS BY INDEX ROWID Object owner=ARMANI Object name=GR_BATCH Cost=2 Cardinality=1 Bytes=77
     INDEX RANGE SCAN Object owner=ARMANI Object name=IX_GR_BATCH_01 Cost=1 Cardinality=1
   TABLE ACCESS BY INDEX ROWID Object owner=ARMANI Object name=GR_BATCH_DTL Cost=2 Cardinality=1 Bytes=83
    INDEX RANGE SCAN Object owner=ARMANI Object name=PK_GR_BATCH_DTL Cost=1 Cardinality=1
执行计划2:
SELECT STATEMENT, GOAL = CHOOSE Cost=1228 Cardinality=1 Bytes=335
 SORT GROUP BY NOSORT Cost=1228 Cardinality=1 Bytes=335
  NESTED LOOPS OUTER Cost=1228 Cardinality=15 Bytes=5025
   NESTED LOOPS OUTER Cost=1216 Cardinality=4 Bytes=1128
    NESTED LOOPS Cost=1212 Cardinality=4 Bytes=1060
     HASH JOIN Cost=1192 Cardinality=10 Bytes=2000
      HASH JOIN Cost=930 Cardinality=471 Bytes=68295
       TABLE ACCESS FULL Object owner=ARMANI Object name=STOCK Cost=503 Cardinality=278 Bytes=14734
       TABLE ACCESS FULL Object owner=ARMANI Object name=GR_DTL Cost=424 Cardinality=59332 Bytes=5458544
      TABLE ACCESS FULL Object owner=ARMANI Object name=GR_BATCH Cost=261 Cardinality=1297 Bytes=71335
     TABLE ACCESS BY INDEX ROWID Object owner=ARMANI Object name=GR_HDR Cost=2 Cardinality=1 Bytes=65
      INDEX UNIQUE SCAN Object owner=ARMANI Object name=PK_GR_HDR Cost=1 Cardinality=1
    TABLE ACCESS BY INDEX ROWID Object owner=ARMANI Object name=SYSDATABASE Cost=1 Cardinality=1 Bytes=17
     INDEX UNIQUE SCAN Object owner=ARMANI Object name=PK_SYSDATABASE Cardinality=1
   TABLE ACCESS BY INDEX ROWID Object owner=ARMANI Object name=GR_BATCH_DTL Cost=3 Cardinality=4 Bytes=212
    INDEX RANGE SCAN Object owner=ARMANI Object name=PK_GR_BATCH_DTL Cost=2 Cardinality=1

Answer 1

执行计划2有好几个用到TABLE ACCESS FULL啊，两台数据库上的表数据量都一样吗，还有建有索引吗，检查一下吧．

Answer 2

估计和参数已经表的分析有关.
执行下面的分析后，再看看对应的执行计划:
ANALYZE TABLE GR_DTL COMPUTE STATISTICS FOR TABLE  FOR ALL INDEXED COLUMNS FOR ALL INDEXES;
ANALYZE TABLE GR_HDR COMPUTE STATISTICS FOR TABLE  FOR ALL INDEXED COLUMNS FOR ALL INDEXES;
ANALYZE TABLE GR_BATCH COMPUTE STATISTICS FOR TABLE  FOR ALL INDEXED COLUMNS FOR ALL INDEXES;
ANALYZE TABLE GR_BATCH_DTL COMPUTE STATISTICS FOR TABLE  FOR ALL INDEXED COLUMNS FOR ALL INDEXES;
ANALYZE TABLE STOCK COMPUTE STATISTICS FOR TABLE  FOR ALL INDEXED COLUMNS FOR ALL INDEXES;
ANALYZE TABLE SYSDATABASE COMPUTE STATISTICS FOR TABLE  FOR ALL INDEXED COLUMNS FOR ALL INDEXES;