不同库上同一个SQL，执行计划相同，性能相差很多，为何？

kurz

SQL语句如下：
  SELECT dtl.corp_org_id org_id,
         sub1.org_name_c branch_name,
           SUM (
              CASE
                 WHEN dtl.account_book_id = 0 THEN dtl.total_charge
                 ELSE 0
              END)
         / 100
            一次性退费
    FROM cs.cs_app app
         INNER JOIN cs.cs_app appr ON app.relat_app_id = appr.app_id
         INNER JOIN rep.kd_app_bill_month dtl ON dtl.app_id = app.app_id
         LEFT JOIN rep.kd_subscriber_connect_month sub1
            ON sub1.subscriber_id = dtl.subscriber_id
   WHERE     app.app_action IN ('CS_CANCEL', 'CS_REVERSE')
         AND dtl.busy_type = 2
         AND appr.app_time < DATE '2013-12-01'
         AND app.app_time BETWEEN DATE '2013-12-01' AND DATE '2013-12-31' + 1
GROUP BY dtl.corp_org_id, sub1.org_name_c;


库A的执行计划（慢，几分钟）：
Plan
SELECT STATEMENT  ALL_ROWSCost: 63,717  Bytes: 8,463  Cardinality: 91                                                         
        11 HASH GROUP BY  Cost: 63,717  Bytes: 8,463  Cardinality: 91                                                 
                10 HASH JOIN OUTER  Cost: 63,716  Bytes: 101,463  Cardinality: 1,091                                         
                        8 TABLE ACCESS BY INDEX ROWID TABLE REP.KD_APP_BILL_MONTH Cost: 4  Bytes: 56  Cardinality: 2                                 
                                7 NESTED LOOPS  Cost: 62,093  Bytes: 86,189  Cardinality: 1,091                         
                                        5 NESTED LOOPS  Cost: 60,922  Bytes: 29,376  Cardinality: 576                 
                                                2 TABLE ACCESS BY INDEX ROWID TABLE CS.CS_APP Cost: 59,769  Bytes: 20,736  Cardinality: 576         
                                                        1 INDEX RANGE SCAN INDEX CS.IDX_CS_APP_APP_TIME Cost: 206  Cardinality: 76,566  
                                                4 TABLE ACCESS BY INDEX ROWID TABLE CS.CS_APP Cost: 2  Bytes: 15  Cardinality: 1         
                                                        3 INDEX UNIQUE SCAN INDEX (UNIQUE) CS.IDX_PK_CS_APP Cost: 1  Cardinality: 1  
                                        6 INDEX RANGE SCAN INDEX REP.IDX_KD_APP_BILL_MONTH_APPID Cost: 2  Cardinality: 2                 
                        9 TABLE ACCESS FULL TABLE REP.KD_SUBSCRIBER_CONNECT_MONTH Cost: 1,603  Bytes: 21,743,960  Cardinality: 1,553,140                                 
                       

库B的执行计划（快，十几秒）：
Plan
SELECT STATEMENT  ALL_ROWSCost: 58,408  Bytes: 7,440  Cardinality: 80                                                         
        11 HASH GROUP BY  Cost: 58,408  Bytes: 7,440  Cardinality: 80                                                 
                10 HASH JOIN OUTER  Cost: 58,407  Bytes: 74,493  Cardinality: 801                                         
                        8 TABLE ACCESS BY INDEX ROWID TABLE REP.KD_APP_BILL_MONTH Cost: 3  Bytes: 28  Cardinality: 1                                 
                                7 NESTED LOOPS  Cost: 56,799  Bytes: 63,279  Cardinality: 801                         
                                        5 NESTED LOOPS  Cost: 55,626  Bytes: 29,580  Cardinality: 580                 
                                                2 TABLE ACCESS BY INDEX ROWID TABLE CS.CS_APP Cost: 54,464  Bytes: 20,880  Cardinality: 580         
                                                        1 INDEX RANGE SCAN INDEX CS.IDX_CS_APP_APP_TIME Cost: 216  Cardinality: 76,569  
                                                4 TABLE ACCESS BY INDEX ROWID TABLE CS.CS_APP Cost: 2  Bytes: 15  Cardinality: 1         
                                                        3 INDEX UNIQUE SCAN INDEX (UNIQUE) CS.IDX_PK_CS_APP Cost: 1  Cardinality: 1  
                                        6 INDEX RANGE SCAN INDEX REP.IDX_KD_APP_BILL_MONTH_APPID Cost: 2  Cardinality: 1                 
                        9 TABLE ACCESS FULL TABLE REP.KD_SUBSCRIBER_CONNECT_MONTH Cost: 1,588  Bytes: 21,765,408  Cardinality: 1,554,672                                 


两台机器硬件配置完全相同，
请刘大帮忙分析下，是否还需要提供其它信息？

Liu Maclean(刘相兵

这个格式 难以阅读

一个好的问题 的基本要素：一个好的标题、　完整的版本信息、 完整的日志信息、 报错信息
请使用 sql health check 脚本 分析该SQL 然后上传HTML
http://www.oracledatabase12g.com ... h-check-script.html



PS: 你标题这种问题 实在是在国内很普遍的问题，究其原因无非是 大家喜欢忽略细节，以及唯有忽略细节才能活下去

http://t.askmaclean.com/thread-3245-1-1.html
http://t.askmaclean.com/thread-3821-1-1.html

kurz

参考链接已经看过，情况不同，意义不大

Liu Maclean(刘相兵

Current SQL Statistics (GV$SQL)

Performance metrics of child cursors of 5zn5rwavxhdcu while still in memory.
#        Inst
ID        Child
Num        Plan HV        Execs        Fetch        Loads        Inval        Parse
Calls        Buffer
Gets        Disk
Reads        Direct
Writes        Rows
Proc        Elapsed
Time
(secs)        CPU
Time
(secs)        IO
Time
(secs)        Conc
Time
(secs)        Appl
Time
(secs)        Clus
Time
(secs)        PLSQL
Time
(secs)        Java
Time
(secs)        Optimizer
Mode        Cost        Opt Env HV        Parsing
Schema
Name        Module        Action        Outline        Profile        First Load        Last Load        Last Active
1        1        0        1974753989        1        1        1        0        1        2782643        61188        0        12         165.992         18.540         155.031         0.000         0.000         0.000         0.000         0.000        ALL_ROWS        63717        2513728184        SYSTEM        TOAD background query session                                2014-01-17/15:11:32        2014-01-17/15:11:32        2014-01-17/15:14:22



VS


Current SQL Statistics (GV$SQL)

Performance metrics of child cursors of 5zn5rwavxhdcu while still in memory.
#        Inst
ID        Child
Num        Plan HV        Execs        Fetch        Loads        Inval        Parse
Calls        Buffer
Gets        Disk
Reads        Direct
Writes        Rows
Proc        Elapsed
Time
(secs)        CPU
Time
(secs)        IO
Time
(secs)        Conc
Time
(secs)        Appl
Time
(secs)        Clus
Time
(secs)        PLSQL
Time
(secs)        Java
Time
(secs)        Optimizer
Mode        Cost        Opt Env HV        Parsing
Schema
Name        Module        Action        Outline        Profile        First Load        Last Load        Last Active
1        1        0        1974753989        1        1        1        0        1        101977        61380        0        12         46.019         10.430         42.085         0.000         0.000         0.000         0.000         0.000        ALL_ROWS        58408        1134700906        SYSTEM        TOAD background query session                                2014-01-17/15:06:15        2014-01-17/15:06:15        2014-01-17/15:07:02




Plan HV 一样说明确实执行计划一样


Buffer  Gets   2782643   Disk Reads 61188   CPU Time  18s    IO TIME 155s
vs
Buffer  Gets   101977  Disk Reads 61380  CPU Time  10s   IO TIME 42.085


给一下这2个 SQL 的 raw 的10046 TRACE 和这2个库的AWR

kurz

AWR目前无法提供

Liu Maclean(刘相兵

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2     25.13     221.76      80888    2814070          0          12
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     25.13     221.77      80888    2814070          0          12

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows     Row Source Operation
-------  ---------------------------------------------------
     12  HASH GROUP BY (cr=2814070 pr=80888 pw=0 time=221769108 us)
    101   HASH JOIN OUTER (cr=2814070 pr=80888 pw=0 time=222580556 us)
    101    HASH JOIN  (cr=2807064 pr=73891 pw=0 time=217940971 us)
     63     NESTED LOOPS  (cr=2775323 pr=49001 pw=0 time=6731973 us)
   1476      TABLE ACCESS BY INDEX ROWID CS_APP (cr=2770893 pr=47981 pw=0 time=160940455 us)
3445081       INDEX RANGE SCAN IDX_CS_APP_APP_TIME (cr=9142 pr=9140 pw=0 time=6919360 us)(object id 937218)
     63      TABLE ACCESS BY INDEX ROWID CS_APP (cr=4430 pr=1020 pw=0 time=8771442 us)
   1476       INDEX UNIQUE SCAN IDX_PK_CS_APP (cr=2954 pr=967 pw=0 time=8351212 us)(object id 881020)
3000793     TABLE ACCESS FULL KD_APP_BILL_MONTH (cr=31741 pr=24890 pw=0 time=7199 us)
1558326    TABLE ACCESS FULL KD_SUBSCRIBER_CONNECT_MONTH (cr=7006 pr=6997 pw=0 time=75193 us)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       3        0.00          0.00
  SQL*Net message from client                     3       17.25         27.99
  db file sequential read                     49145        0.53        203.02
  latch: cache buffers chains                     2        0.00          0.00
  db file scattered read                       2046        0.07          2.25


vs

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      9.46      10.92      38857     133453          0          12
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      9.46      10.93      38857     133453          0          12

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows     Row Source Operation
-------  ---------------------------------------------------
     12  HASH GROUP BY (cr=133453 pr=38857 pw=0 time=10926602 us)
    101   HASH JOIN OUTER (cr=133453 pr=38857 pw=0 time=11842583 us)
    101    HASH JOIN  (cr=126454 pr=31866 pw=0 time=6241637 us)
     63     NESTED LOOPS  (cr=94662 pr=92 pw=0 time=124835 us)
   1476      TABLE ACCESS BY INDEX ROWID CS_APP (cr=90232 pr=4 pw=0 time=4968855 us)
3445081       INDEX RANGE SCAN IDX_CS_APP_APP_TIME (cr=9142 pr=0 pw=0 time=3962 us)(object id 331843)
     63      TABLE ACCESS BY INDEX ROWID CS_APP (cr=4430 pr=88 pw=0 time=731313 us)
   1476       INDEX UNIQUE SCAN IDX_PK_CS_APP (cr=2954 pr=86 pw=0 time=720815 us)(object id 319263)
3000793     TABLE ACCESS FULL KD_APP_BILL_MONTH (cr=31792 pr=31774 pw=0 time=3006926 us)
1558326    TABLE ACCESS FULL KD_SUBSCRIBER_CONNECT_MONTH (cr=6999 pr=6991 pw=0 time=9406 us)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       3        0.00          0.00
  SQL*Net message from client                     3        6.47          9.30
  db file sequential read                        96        0.07          0.70
  db file scattered read                       2461        0.08          2.82

Liu Maclean(刘相兵

db file sequential read                     49145        0.53        203.02

主要区别在于 db file sequential read      

TABLE ACCESS BY INDEX ROWID CS_APP (cr=2770893 pr=47981 pw=0 time=160940455 us)


怀疑2边的配置 有较大的区别， 需要AWR 证实

kurz

因包含敏感信息，AWR不方便提供，sorry
你说的配置具体指哪些？

Liu Maclean(刘相兵

kurz 发表于 2014-1-20 10:14
因包含敏感信息，AWR不方便提供，sorry
你说的配置具体指哪些？

everything

kurz

Thanks! anyway