10g迁移到11g查询变得很慢

Question

10g是32位的服务器配置为：CPU E5520*2 内存8G win200311g是64位的服务器配置为：CPU E5-4607*2 内存32G win2008r2同样的查询语句
select A.*, B.*, C.*, D.*
  from MTR_RPT_CELL_E      A,
       Mtr_RPT_BASE        B,
       Sys_v_SecurityUsers C,
       Sys_v_District      D
 where A.RptID = B.ID
   And B.UserID = C.EMDID
   And B.DistrictID = D.DistrictID
   And D.Class <= 3
   And B.TemplateID = 21
Sys_v_SecurityUsers，Sys_v_District为视图。
在10g中执行5秒，而在11g中半个小时都查询不出来。Oracle 11g

Answer 1

这是10G中的SELECT STATEMENT, GOAL = ALL_ROWS 720073 2571251 3342626300
 HASH UNIQUE 4 3 60
  TABLE ACCESS FULL SCSME SYS_USERTYPEROLE 3 3 60
 SORT ORDER BY 720073 2571251 3342626300
  HASH JOIN 18882 2571251 3342626300
   HASH JOIN RIGHT OUTER 1222 63364 77494172
    TABLE ACCESS FULL SCSME SYS_DISTRICT 8 5028 120672
    HASH JOIN 1212 63364 75973436
     VIEW SCSME SYS_V_DISTRICT 34 219 73803
      UNION-ALL
       TABLE ACCESS FULL SCSME SYS_DISTRICT 8 1 32
       TABLE ACCESS FULL SCSME SYS_DISTRICT 8 21 672
       HASH JOIN RIGHT OUTER 17 196 12544
        TABLE ACCESS FULL SCSME SYS_DISTRICT 8 21 672
        TABLE ACCESS FULL SCSME SYS_DISTRICT 8 196 6272
       FILTER
        TABLE ACCESS BY INDEX ROWID SCSME SYS_DISTRICT 2 1 21
         NESTED LOOPS 12 1 85
          NESTED LOOPS 10 1 64
           TABLE ACCESS FULL SCSME SYS_DISTRICT 8 1 32
           TABLE ACCESS BY INDEX ROWID SCSME SYS_DISTRICT 2 1 32
            INDEX RANGE SCAN SCSME PK_SYS_DISTRICT 1 1
          INDEX RANGE SCAN SCSME PK_SYS_DISTRICT 1 1
     HASH JOIN 1177 83617 72077854
      VIEW SCSME 410 7425 5613300
       UNION-ALL
        HASH JOIN 26 257 46517
         TABLE ACCESS FULL SCSME PUB_MANAGEMENT 3 261 18270
         INLIST ITERATOR
          TABLE ACCESS BY INDEX ROWID SCSME SYS_SECURITYUSERS 22 257 28527
           INDEX RANGE SCAN SCSME IDX_SYS_SECURITYUSERS 6 257
        TABLE ACCESS FULL SCSME SYS_SECURITYUSERS 57 48 5328
        HASH JOIN RIGHT OUTER 160 6739 1819530
         VIEW SCSME PUB_V_ENTERPRISERELA 1 1 40
          NESTED LOOPS 1 1 102
           NESTED LOOPS 0 1 73
            INDEX FULL SCAN SCSME PK_PUB_ENTERPRISERELA 0 1 40
            INDEX UNIQUE SCAN SCSME PK_PUB_ENTERPRISEINFO 0 1 33
           INDEX RANGE SCAN SCSME IDX_SYS_SECURITYUSERS 1 1 29
         HASH JOIN 158 6739 1549970
          TABLE ACCESS FULL SCSME PUB_ENTERPRISEINFO 100 6717 799323
          TABLE ACCESS FULL SCSME SYS_SECURITYUSERS 57 12061 1338771
        HASH JOIN 158 349 80270
         TABLE ACCESS FULL SCSME PUB_ENTERPRISEINFO 100 348 41412
         TABLE ACCESS FULL SCSME SYS_SECURITYUSERS 57 12061 1338771
        HASH JOIN 8 32 6080
         INLIST ITERATOR
          TABLE ACCESS BY INDEX ROWID SCSME SYS_SECURITYUSERS 5 32 3552
           INDEX RANGE SCAN SCSME IDX_SYS_SECURITYUSERS 3 32
         TABLE ACCESS FULL SCSME PUB_SRVPLATFORMINFO 3 32 2528
      TABLE ACCESS FULL SCSME MTR_RPT_BASE 212 50249 5326394
   TABLE ACCESS FULL SCSME MTR_RPT_CELL_E 5282 2038976 157001152这是11G中的
SELECT STATEMENT, GOAL = ALL_ROWS 41 1 3369
 HASH UNIQUE 4 2 38
  TABLE ACCESS FULL SCSME SYS_USERTYPEROLE 3 2 38
 SORT ORDER BY 41 1 3369
  NESTED LOOPS 40 1 3369
   NESTED LOOPS OUTER 29 1 3028
    NESTED LOOPS 27 1 3004
     NESTED LOOPS 2 1 2246
      TABLE ACCESS FULL SCSME MTR_RPT_CELL_E 2 1 1075
      TABLE ACCESS BY INDEX ROWID SCSME MTR_RPT_BASE 0 1 1171
       INDEX RANGE SCAN SCSME PK_MTR_RPT_BASE 0 1
     VIEW SCSME 25 1 758
      UNION ALL PUSHED PREDICATE
       HASH JOIN 7 1 172
        TABLE ACCESS BY INDEX ROWID SCSME SYS_SECURITYUSERS 3 1 103
         INDEX RANGE SCAN SCSME PK_SYS_SECURITYUSERS 2 1
        TABLE ACCESS FULL SCSME PUB_MANAGEMENT 3 1 69
       TABLE ACCESS BY INDEX ROWID SCSME SYS_SECURITYUSERS 3 1 103
        INDEX RANGE SCAN SCSME IDX_SYS_SECURITYUSERS 2 1
       TABLE ACCESS BY INDEX ROWID SCSME PUB_SRVORGINFO 1 1 40
        INDEX UNIQUE SCAN SCSME PK_PUB_SRVORGINFO 0 1
       TABLE ACCESS BY INDEX ROWID SCSME PUB_SRVORGINFO 1 1 43
        INDEX UNIQUE SCAN SCSME PK_PUB_SRVORGINFO 0 1
       TABLE ACCESS BY INDEX ROWID SCSME PUB_SRVORGINFO 1 1 38
        INDEX UNIQUE SCAN SCSME PK_PUB_SRVORGINFO 0 1
       NESTED LOOPS OUTER 6 1 258
        NESTED LOOPS 4 1 236
         TABLE ACCESS BY INDEX ROWID SCSME PUB_ENTERPRISEINFO 2 1 133
          INDEX UNIQUE SCAN SCSME PK_PUB_ENTERPRISEINFO 1 1
         TABLE ACCESS BY INDEX ROWID SCSME SYS_SECURITYUSERS 2 1 103
          INDEX RANGE SCAN SCSME PK_SYS_SECURITYUSERS 1 1
        VIEW PUSHED PREDICATE SCSME PUB_V_ENTERPRISERELA 2 1 22
         NESTED LOOPS 2 1 100
          NESTED LOOPS 1 1 73
           INDEX UNIQUE SCAN SCSME PK_PUB_ENTERPRISEINFO 1 1 33
           INDEX RANGE SCAN SCSME PK_PUB_ENTERPRISERELA 0 1 40
          INDEX RANGE SCAN SCSME IDX_SYS_SECURITYUSERS 1 1 27
       TABLE ACCESS BY INDEX ROWID SCSME PUB_SRVORGINFO 1 1 38
        INDEX UNIQUE SCAN SCSME PK_PUB_SRVORGINFO 0 1
       NESTED LOOPS 4 1 236
        TABLE ACCESS BY INDEX ROWID SCSME PUB_ENTERPRISEINFO 2 1 133
         INDEX UNIQUE SCAN SCSME PK_PUB_ENTERPRISEINFO 1 1
        TABLE ACCESS BY INDEX ROWID SCSME SYS_SECURITYUSERS 2 1 103
         INDEX RANGE SCAN SCSME PK_SYS_SECURITYUSERS 1 1
       NESTED LOOPS
        NESTED LOOPS 6 1 182
         TABLE ACCESS FULL SCSME PUB_SRVPLATFORMINFO 3 1 79
         INLIST ITERATOR
          INDEX RANGE SCAN SCSME IDX_SYS_SECURITYUSERS 2 1
        TABLE ACCESS BY INDEX ROWID SCSME SYS_SECURITYUSERS 3 1 103
    TABLE ACCESS BY INDEX ROWID SCSME SYS_DISTRICT 2 1 24
     INDEX RANGE SCAN SCSME PK_SYS_DISTRICT 1 1
   VIEW SCSME SYS_V_DISTRICT 11 1 341
    UNION ALL PUSHED PREDICATE
     TABLE ACCESS BY INDEX ROWID SCSME SYS_DISTRICT 3 1 33
      INDEX RANGE SCAN SCSME PK_SYS_DISTRICT 2 1
     TABLE ACCESS BY INDEX ROWID SCSME SYS_DISTRICT 3 1 33
      INDEX RANGE SCAN SCSME PK_SYS_DISTRICT 2 1
     NESTED LOOPS OUTER 5 1 66
      TABLE ACCESS BY INDEX ROWID SCSME SYS_DISTRICT 3 1 33
       INDEX RANGE SCAN SCSME PK_SYS_DISTRICT 2 1
      TABLE ACCESS BY INDEX ROWID SCSME SYS_DISTRICT 2 1 33
       INDEX RANGE SCAN SCSME PK_SYS_DISTRICT 1 1
     FILTER
      NESTED LOOPS
       NESTED LOOPS 7 1 87
        NESTED LOOPS 5 1 66
         TABLE ACCESS BY INDEX ROWID SCSME SYS_DISTRICT 3 1 33
          INDEX RANGE SCAN SCSME PK_SYS_DISTRICT 2 1
         TABLE ACCESS BY INDEX ROWID SCSME SYS_DISTRICT 2 1 33
          INDEX RANGE SCAN SCSME PK_SYS_DISTRICT 1 1
        INDEX RANGE SCAN SCSME PK_SYS_DISTRICT 1 1
       TABLE ACCESS BY INDEX ROWID SCSME SYS_DISTRICT 2 1 21以前一直用mssql的，这是第一次使用oracle，很多都不懂

Answer 2

这是10G中的SELECT STATEMENT, GOAL = ALL_ROWS 720073 2571251 3342626300
 HASH UNIQUE 4 3 60
  TABLE ACCESS FULL SCSME SYS_USERTYPEROLE 3 3 60
 SORT ORDER BY 720073 2571251 3342626300
  HASH JOIN 18882 2571251 3342626300
   HASH JOIN RIGHT OUTER 1222 63364 77494172
    TABLE ACCESS FULL SCSME SYS_DISTRICT 8 5028 120672
    HASH JOIN 1212 63364 75973436
     VIEW SCSME SYS_V_DISTRICT 34 219 73803
      UNION-ALL
       TABLE ACCESS FULL SCSME SYS_DISTRICT 8 1 32
       TABLE ACCESS FULL SCSME SYS_DISTRICT 8 21 672
       HASH JOIN RIGHT OUTER 17 196 12544
        TABLE ACCESS FULL SCSME SYS_DISTRICT 8 21 672
        TABLE ACCESS FULL SCSME SYS_DISTRICT 8 196 6272
       FILTER
        TABLE ACCESS BY INDEX ROWID SCSME SYS_DISTRICT 2 1 21
         NESTED LOOPS 12 1 85
          NESTED LOOPS 10 1 64
           TABLE ACCESS FULL SCSME SYS_DISTRICT 8 1 32
           TABLE ACCESS BY INDEX ROWID SCSME SYS_DISTRICT 2 1 32
            INDEX RANGE SCAN SCSME PK_SYS_DISTRICT 1 1
          INDEX RANGE SCAN SCSME PK_SYS_DISTRICT 1 1
     HASH JOIN 1177 83617 72077854
      VIEW SCSME 410 7425 5613300
       UNION-ALL
        HASH JOIN 26 257 46517
         TABLE ACCESS FULL SCSME PUB_MANAGEMENT 3 261 18270
         INLIST ITERATOR
          TABLE ACCESS BY INDEX ROWID SCSME SYS_SECURITYUSERS 22 257 28527
           INDEX RANGE SCAN SCSME IDX_SYS_SECURITYUSERS 6 257
        TABLE ACCESS FULL SCSME SYS_SECURITYUSERS 57 48 5328
        HASH JOIN RIGHT OUTER 160 6739 1819530
         VIEW SCSME PUB_V_ENTERPRISERELA 1 1 40
          NESTED LOOPS 1 1 102
           NESTED LOOPS 0 1 73
            INDEX FULL SCAN SCSME PK_PUB_ENTERPRISERELA 0 1 40
            INDEX UNIQUE SCAN SCSME PK_PUB_ENTERPRISEINFO 0 1 33
           INDEX RANGE SCAN SCSME IDX_SYS_SECURITYUSERS 1 1 29
         HASH JOIN 158 6739 1549970
          TABLE ACCESS FULL SCSME PUB_ENTERPRISEINFO 100 6717 799323
          TABLE ACCESS FULL SCSME SYS_SECURITYUSERS 57 12061 1338771
        HASH JOIN 158 349 80270
         TABLE ACCESS FULL SCSME PUB_ENTERPRISEINFO 100 348 41412
         TABLE ACCESS FULL SCSME SYS_SECURITYUSERS 57 12061 1338771
        HASH JOIN 8 32 6080
         INLIST ITERATOR
          TABLE ACCESS BY INDEX ROWID SCSME SYS_SECURITYUSERS 5 32 3552
           INDEX RANGE SCAN SCSME IDX_SYS_SECURITYUSERS 3 32
         TABLE ACCESS FULL SCSME PUB_SRVPLATFORMINFO 3 32 2528
      TABLE ACCESS FULL SCSME MTR_RPT_BASE 212 50249 5326394
   TABLE ACCESS FULL SCSME MTR_RPT_CELL_E 5282 2038976 157001152这是11G中的
SELECT STATEMENT, GOAL = ALL_ROWS 41 1 3369
 HASH UNIQUE 4 2 38
  TABLE ACCESS FULL SCSME SYS_USERTYPEROLE 3 2 38
 SORT ORDER BY 41 1 3369
  NESTED LOOPS 40 1 3369
   NESTED LOOPS OUTER 29 1 3028
    NESTED LOOPS 27 1 3004
     NESTED LOOPS 2 1 2246
      TABLE ACCESS FULL SCSME MTR_RPT_CELL_E 2 1 1075
      TABLE ACCESS BY INDEX ROWID SCSME MTR_RPT_BASE 0 1 1171
       INDEX RANGE SCAN SCSME PK_MTR_RPT_BASE 0 1
     VIEW SCSME 25 1 758
      UNION ALL PUSHED PREDICATE
       HASH JOIN 7 1 172
        TABLE ACCESS BY INDEX ROWID SCSME SYS_SECURITYUSERS 3 1 103
         INDEX RANGE SCAN SCSME PK_SYS_SECURITYUSERS 2 1
        TABLE ACCESS FULL SCSME PUB_MANAGEMENT 3 1 69
       TABLE ACCESS BY INDEX ROWID SCSME SYS_SECURITYUSERS 3 1 103
        INDEX RANGE SCAN SCSME IDX_SYS_SECURITYUSERS 2 1
       TABLE ACCESS BY INDEX ROWID SCSME PUB_SRVORGINFO 1 1 40
        INDEX UNIQUE SCAN SCSME PK_PUB_SRVORGINFO 0 1
       TABLE ACCESS BY INDEX ROWID SCSME PUB_SRVORGINFO 1 1 43
        INDEX UNIQUE SCAN SCSME PK_PUB_SRVORGINFO 0 1
       TABLE ACCESS BY INDEX ROWID SCSME PUB_SRVORGINFO 1 1 38
        INDEX UNIQUE SCAN SCSME PK_PUB_SRVORGINFO 0 1
       NESTED LOOPS OUTER 6 1 258
        NESTED LOOPS 4 1 236
         TABLE ACCESS BY INDEX ROWID SCSME PUB_ENTERPRISEINFO 2 1 133
          INDEX UNIQUE SCAN SCSME PK_PUB_ENTERPRISEINFO 1 1
         TABLE ACCESS BY INDEX ROWID SCSME SYS_SECURITYUSERS 2 1 103
          INDEX RANGE SCAN SCSME PK_SYS_SECURITYUSERS 1 1
        VIEW PUSHED PREDICATE SCSME PUB_V_ENTERPRISERELA 2 1 22
         NESTED LOOPS 2 1 100
          NESTED LOOPS 1 1 73
           INDEX UNIQUE SCAN SCSME PK_PUB_ENTERPRISEINFO 1 1 33
           INDEX RANGE SCAN SCSME PK_PUB_ENTERPRISERELA 0 1 40
          INDEX RANGE SCAN SCSME IDX_SYS_SECURITYUSERS 1 1 27
       TABLE ACCESS BY INDEX ROWID SCSME PUB_SRVORGINFO 1 1 38
        INDEX UNIQUE SCAN SCSME PK_PUB_SRVORGINFO 0 1
       NESTED LOOPS 4 1 236
        TABLE ACCESS BY INDEX ROWID SCSME PUB_ENTERPRISEINFO 2 1 133
         INDEX UNIQUE SCAN SCSME PK_PUB_ENTERPRISEINFO 1 1
        TABLE ACCESS BY INDEX ROWID SCSME SYS_SECURITYUSERS 2 1 103
         INDEX RANGE SCAN SCSME PK_SYS_SECURITYUSERS 1 1
       NESTED LOOPS
        NESTED LOOPS 6 1 182
         TABLE ACCESS FULL SCSME PUB_SRVPLATFORMINFO 3 1 79
         INLIST ITERATOR
          INDEX RANGE SCAN SCSME IDX_SYS_SECURITYUSERS 2 1
        TABLE ACCESS BY INDEX ROWID SCSME SYS_SECURITYUSERS 3 1 103
    TABLE ACCESS BY INDEX ROWID SCSME SYS_DISTRICT 2 1 24
     INDEX RANGE SCAN SCSME PK_SYS_DISTRICT 1 1
   VIEW SCSME SYS_V_DISTRICT 11 1 341
    UNION ALL PUSHED PREDICATE
     TABLE ACCESS BY INDEX ROWID SCSME SYS_DISTRICT 3 1 33
      INDEX RANGE SCAN SCSME PK_SYS_DISTRICT 2 1
     TABLE ACCESS BY INDEX ROWID SCSME SYS_DISTRICT 3 1 33
      INDEX RANGE SCAN SCSME PK_SYS_DISTRICT 2 1
     NESTED LOOPS OUTER 5 1 66
      TABLE ACCESS BY INDEX ROWID SCSME SYS_DISTRICT 3 1 33
       INDEX RANGE SCAN SCSME PK_SYS_DISTRICT 2 1
      TABLE ACCESS BY INDEX ROWID SCSME SYS_DISTRICT 2 1 33
       INDEX RANGE SCAN SCSME PK_SYS_DISTRICT 1 1
     FILTER
      NESTED LOOPS
       NESTED LOOPS 7 1 87
        NESTED LOOPS 5 1 66
         TABLE ACCESS BY INDEX ROWID SCSME SYS_DISTRICT 3 1 33
          INDEX RANGE SCAN SCSME PK_SYS_DISTRICT 2 1
         TABLE ACCESS BY INDEX ROWID SCSME SYS_DISTRICT 2 1 33
          INDEX RANGE SCAN SCSME PK_SYS_DISTRICT 1 1
        INDEX RANGE SCAN SCSME PK_SYS_DISTRICT 1 1
       TABLE ACCESS BY INDEX ROWID SCSME SYS_DISTRICT 2 1 21以前一直用mssql的，这是第一次使用oracle，很多都不懂
执行计划不同，重新收集 下 11g库 里面 涉及到表的统计信息 ，再查下看看。

Answer 3

你是说把数据重新导入一次吗？11g的表和10g的表都是手工建立的（结构，索引都是一样的）。然后数据走mssql导入进去的

Answer 4

奇怪的事情.. 为什么迁移至后很多都是走了索引,但是10G下面,全表扫描的方式反而要快一些?LZ查询出结果集的大小?是否有锁资源?对涉及到的表做一下表分析呢? 

Answer 5

show parameter optimizer_mode;
看下优化器模式

Answer 6

这样的情况，我更加不觉得应该是头痛医头脚痛医脚，而是要根据awr等信息，具体分析，对比优化应该不难