9I数据库迁移到10G后同样的查询语句为什么执行时间变慢了很多。

Question

         SELECT DISTINCT SOA_GET_NENAME(TPA_UNIC_BTS_SUM.NE_ID,TPA_UNIC_BTS_SUM.NE_TYPE)                                           G60,
                   TPA_UNIC_BTS_SUM.FIRST_RESULT                                                                            FIRST_RESULT,
                   NVL(SFB_DIVFLOAT_1(TPA_UNIC_MSC_SUM.LOC_UPD_SUC,TPA_UNIC_MSC_SUM.LOC_UPD_REQ,0,0)*100,0)                       G61,
                               NVL(SFB_DIVFLOAT_1(TPA_UNIC_MSC_SUM.ROUTING_QUERY_SUC,TPA_UNIC_MSC_SUM.ROUTING_QUERY_REQ,0,0)*100,0)           G62,
                               NVL(SFB_DIVFLOAT_1(TPA_UNIC_BTS_SUM.HO_I_SUC,TPA_UNIC_BTS_SUM.HO_I_REQ,0,0)*100,0)                             G63,
                               NVL(SFB_DIVFLOAT_1(TPA_UNIC_MSC_SUM.HO_I_INTERMSC_SUC,TPA_UNIC_MSC_SUM.HO_I_INTERMSC_REQ,0,0)*100,0)           G64,
                               NVL(SFB_DIVFLOAT_1(TPA_UNIC_BTS_SUM.DROP_CALL_TCH,TPA_UNIC_BTS_SUM.TCH_SEIZE_NHO,0,0)*100,0)                   G65,
                               NVL(SFB_DIVFLOAT_1(TPA_UNIC_BTS_SUM.TCH_TRAFFIC*60,TPA_UNIC_BTS_SUM.DROP_CALL_TCH,0,0),0)                  G66,
                               NVL(SFB_DIVFLOAT_1(TPA_UNIC_BTS_SUM.WORST_CELL,TPA_GSMA_UNIC_SUM.TOTAL_CELL,0,0)*100,0)                        G67,
                               NVL(SFB_DIVFLOAT_1(TPA_UNIC_MSC_SUM.MSC2MC_SEND_SUC,TPA_UNIC_MSC_SUM.MSC2MC_SEND_REQ,0,0)*100,0)               G68,
                               NVL(SFB_DIVFLOAT_1(TPA_UNIC_MSC_SUM.MC2MSC_SEND_SUC,TPA_UNIC_MSC_SUM.MC2MSC_SEND_REQ,0,0)*100,0)               G69,
                               NVL(SFB_DIVFLOAT_1(TPA_UNIC_BTS_SUM.DROP_CALL_SDCCH,TPA_UNIC_BTS_SUM.RAND_ACC_ATT,0,0)*100,0)                  G610,
                                              
                               TPA_UNIC_MSC_SUM.FIRST_RESULT T1 ,                                      
             TPA_UNIC_MSC_SUM.NE_TYPE D1 ,                                           
             TPA_UNIC_MSC_SUM.NE_ID D2,                                              
             TPA_UNIC_MSC_SUM.NE2_TYPE D3, 
             TPA_UNIC_MSC_SUM.NE2_ID D4,
             TPA_UNIC_MSC_SUM.SV_ID F1,
             F_DD_BTS_TCC(TPA_UNIC_BTS_SUM.NE_ID,TIA_BUSY_TIME.COMPRESS_DATE-1) G21,
             F_DD_BTS_TCC(TPA_UNIC_BTS_SUM.NE_ID,TIA_BUSY_TIME.COMPRESS_DATE-2) G22,
             F_DD_BTS_TCC(TPA_UNIC_BTS_SUM.NE_ID,TIA_BUSY_TIME.COMPRESS_DATE-3) G23 
                               FROM TPA_UNIC_MSC_SUM TPA_UNIC_MSC_SUM ,TPA_GSMA_UNIC_SUM TPA_GSMA_UNIC_SUM,TPA_UNIC_BTS_SUM TPA_UNIC_BTS_SUM
                               ,TIA_BUSY_TIME TIA_BUSY_TIME
                               WHERE   (  (TIA_BUSY_TIME.COMPRESS_DATE = '2010-01-25') ) 
                               AND (TPA_UNIC_BTS_SUM.NE_TYPE = 200 AND TPA_UNIC_BTS_SUM.NE_ID IN (2135221080,-2109832876)) and  (TPA_UNIC_BTS_SUM.NE2_TYPE=-1 AND TPA_UNIC_BTS_SUM.NE2_ID=-1)   
                               AND TPA_UNIC_BTS_SUM.SUM_LEVEL = 0
                               AND TIA_BUSY_TIME.NE_TYPE=10000
                               AND TPA_UNIC_BTS_SUM.FIRST_RESULT = TIA_BUSY_TIME.FIRST_RESULT(+)
                               AND TPA_UNIC_BTS_SUM.FIRST_RESULT = TPA_UNIC_MSC_SUM.FIRST_RESULT(+)
                               AND TPA_UNIC_BTS_SUM.FIRST_RESULT = TPA_GSMA_UNIC_SUM.FIRST_RESULT(+)
                               
                               AND TPA_UNIC_BTS_SUM.NE_ID = TPA_UNIC_MSC_SUM.NE_ID(+)
                               AND TPA_UNIC_BTS_SUM.NE_ID = TPA_GSMA_UNIC_SUM.NE_ID(+)
                               
                               AND TPA_UNIC_BTS_SUM.NE_TYPE = TPA_UNIC_MSC_SUM.NE_TYPE(+)
                               AND TPA_UNIC_BTS_SUM.NE_TYPE = TPA_GSMA_UNIC_SUM.NE_TYPE(+)
                               
                               AND TPA_UNIC_BTS_SUM.SUM_LEVEL = TPA_UNIC_MSC_SUM.SUM_LEVEL(+)
                               AND TPA_UNIC_BTS_SUM.SUM_LEVEL = TPA_GSMA_UNIC_SUM.SUM_LEVEL(+)
                               
                               AND (TPA_UNIC_MSC_SUM.SV_ID(+) = -1 )
                ORDER BY 1,2
上面的语句在9i中执行 15秒左右。
换到10G中需要5分钟左右 。实在是找不到问题原因。
2个库表和索引全部一样。  上面SQL涉及到的表我也都再次核对过表结构和索引关键字完全一样。
------------下面9i解析计划窗口--------
SELECT STATEMENT, GOAL = CHOOSE 697 120 25680
 SORT UNIQUE 691 120 25680
  NESTED LOOPS OUTER 684 120 25680
   NESTED LOOPS OUTER 444 120 21840
    NESTED LOOPS 204 120 11880
     INDEX RANGE SCAN NPMDB DATE_TIME_INX 2 101 3131
     INLIST ITERATOR
      TABLE ACCESS BY GLOBAL INDEX ROWID NPMDB TPA_UNIC_BTS_SUM 2 1 68
       INDEX RANGE SCAN NPMDB UQ_TPA_UNIC_BTS 1 1
    TABLE ACCESS BY GLOBAL INDEX ROWID NPMDB TPA_UNIC_MSC_SUM 2 1 83
     INDEX RANGE SCAN NPMDB UQ_TPA_UNIC_MSC 1 1
   TABLE ACCESS BY INDEX ROWID NPMDB TPA_GSMA_UNIC_SUM 2 1 32
    INDEX RANGE SCAN NPMDB SYS_C0060858139 1 1 ------------下面10g解析计划窗口--------
SELECT STATEMENT, GOAL = CHOOSE 193275 62113 12608939
 SORT UNIQUE 190520 62113 12608939
  HASH JOIN OUTER 187766 62113 12608939
   HASH JOIN OUTER 174254 62113 7453560
    HASH JOIN 49038 62113 5838622
     INDEX RANGE SCAN NPMDB DATE_TIME_INX 19 3981 79620
     PARTITION RANGE ALL 49015 416887 30849638
      TABLE ACCESS FULL NPMDB TPA_UNIC_BTS_SUM 49015 416887 30849638
    TABLE ACCESS FULL NPMDB TPA_GSMA_UNIC_SUM 118999 3255098 84632548
   PARTITION RANGE ALL 12637 107847 8951301
    TABLE ACCESS FULL NPMDB TPA_UNIC_MSC_SUM 12637 107847 8951301

Answer 1

上面语句可能太复杂了。我把问题简单了一下---------------在10g下执行---------------------select  * from TPA_UNIC_BTS_SUM TPA_UNIC_BTS_SUM
                                 left join     TIA_BUSY_TIME TIA_BUSY_TIME ON   TIA_BUSY_TIME.FIRST_RESULT =TPA_UNIC_BTS_SUM.FIRST_RESULT
                                 where TIA_BUSY_TIME.COMPRESS_DATE = '2010-01-25'
                       
                                  and  TIA_BUSY_TIME.NE_TYPE=10000SELECT STATEMENT, GOAL = CHOOSE 48544 954880 261637120
 HASH JOIN 48544 954880 261637120
  TABLE ACCESS BY INDEX ROWID NPMDB TIA_BUSY_TIME 160 3981 103506
   INDEX RANGE SCAN NPMDB COMPRESS_DATE_INX 19 3981
  PARTITION RANGE ALL 48331 6408935 1589415880
   TABLE ACCESS FULL NPMDB TPA_UNIC_BTS_SUM 48331 6408935 1589415880
--------------------修改条件执行这样条件查询速度飞快---------------------
select   * from TPA_UNIC_BTS_SUM TPA_UNIC_BTS_SUM
                                 left join     TIA_BUSY_TIME TIA_BUSY_TIME ON   TIA_BUSY_TIME.FIRST_RESULT =TPA_UNIC_BTS_SUM.FIRST_RESULT
                                 where    TIA_BUSY_TIME.FIRST_RESULT = '2010-01-25 19:00:00'  
                                  and  TIA_BUSY_TIME.NE_TYPE=10000SELECT STATEMENT, GOAL = CHOOSE 371 130673 35804402
 HASH JOIN 371 130673 35804402
  TABLE ACCESS BY INDEX ROWID NPMDB TIA_BUSY_TIME 245 553 14378
   INDEX RANGE SCAN NPMDB NE_ID_INX 13 553
  TABLE ACCESS BY GLOBAL INDEX ROWID NPMDB TPA_UNIC_BTS_SUM 124 236 58528
   INDEX RANGE SCAN NPMDB UQ_TPA_UNIC_BTS 4 236
--------------------------
---------------在9i执行很快---------------------select  * from TPA_UNIC_BTS_SUM TPA_UNIC_BTS_SUM
                                 left join     TIA_BUSY_TIME TIA_BUSY_TIME ON   TIA_BUSY_TIME.FIRST_RESULT =TPA_UNIC_BTS_SUM.FIRST_RESULT
                                 where TIA_BUSY_TIME.COMPRESS_DATE = '2010-01-25'
                       
                                  and  TIA_BUSY_TIME.NE_TYPE=10000
SELECT STATEMENT, GOAL = CHOOSE 205 49768 11994088
 TABLE ACCESS BY GLOBAL INDEX ROWID NPMDB TPA_UNIC_BTS_SUM 2 492 96924
  NESTED LOOPS 205 49768 11994088
   TABLE ACCESS BY INDEX ROWID NPMDB TIA_BUSY_TIME 3 101 4444
    INDEX RANGE SCAN NPMDB COMPRESS_DATE_INX 2 16
   INDEX RANGE SCAN NPMDB UQ_TPA_UNIC_BTS 1 492
--------------------
--------------------修改条件执行这样条件查询速度一样很快---------------------
select   * from TPA_UNIC_BTS_SUM TPA_UNIC_BTS_SUM
                                 left join     TIA_BUSY_TIME TIA_BUSY_TIME ON   TIA_BUSY_TIME.FIRST_RESULT =TPA_UNIC_BTS_SUM.FIRST_RESULT
                                 where    TIA_BUSY_TIME.FIRST_RESULT = '2010-01-25 19:00:00'  
                                  and  TIA_BUSY_TIME.NE_TYPE=10000SELECT STATEMENT, GOAL = CHOOSE 7 106 25546
 HASH JOIN 7 106 25546
  TABLE ACCESS BY INDEX ROWID NPMDB TIA_BUSY_TIME 3 101 4444
   INDEX RANGE SCAN NPMDB NE_ID_INX 2 40
  TABLE ACCESS BY GLOBAL INDEX ROWID NPMDB TPA_UNIC_BTS_SUM 3 492 96924
   INDEX RANGE SCAN NPMDB UQ_TPA_UNIC_BTS 2 1

Answer 2

我想原因是  9i  两种条件查询 都可以 INDEX RANGE SCAN NPMDB UQ_TPA_UNIC_BTS  所以查询很快，为什么10g 不能这样呢