相同配置相同SQL效率 相差400秒

wengtf

Env：11.1.0.7  rac on linux 64bit
业务系统需要在早上7点半跑一个批（存储过程），但是现在生产环境和测试环境执行计划中，prod比testing Fatch数据的时间长400秒，目前排除了网络问题。附件是7月11日上午的statspack 和2个前2天 每个节点的awr，有劳。

##AWR报告中SQL * Net more data from dblink 等待事件较多。

生产环境
*****************************************************************

1. SQL ID: 8c21hbhf1t608
   
2. Plan Hash: 384927267
   
3. SELECT A.M2PNO, A.INS_CODE SOURCELIB, A.M2PCO, B.ACPLAN,
   
4.   SUM(DECODE(TRIM(M2TCOD),'TUC',A.M2AMTF,'TUP',A.M2AMTF,'LSC',A.M2AMTF,'LSP',
   
5.   A.M2AMTF,'CNL',-1*A.M2AMTF)) AMT652
   
6. FROM
   
7. RLS_LFPUFTDH@BFR A, (SELECT ACPNO,MAX(ACPLAN) ACPLAN FROM RLS_TEMP_LFPACT
   
8.   WHERE ACTCD IN ('Z1', 'Z2', 'Z3', 'Z4') AND ACDOT = :B1 GROUP BY ACPNO) B
   
9.   WHERE A.M2TCOD IN ('CNL','TUC','TUP','LSC','LSP') AND A.M2PNO = B.ACPNO
   
10.   GROUP BY A.M2PNO, A.INS_CODE, A.M2PCO, B.ACPLAN
    
11. 
    
12. 
    
13. call     count       cpu    elapsed       disk      query    current        rows
    
14. ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    
15. Parse        1      0.00       0.00          0          0          0           0
    
16. Execute      1      0.00       0.02          0          1          0           0
    
17. Fetch       53      0.24     502.94          0         27          0          52
    
18. ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    
19. total       55      0.25     502.96          0         28          0          52
    
20. 
    
21. Misses in library cache during parse: 1
    
22. Misses in library cache during execute: 1
    
23. Optimizer mode: ALL_ROWS
    
24. Parsing user id: 82     (recursive depth: 1)
    
25. 
    
26. Rows     Row Source Operation
    
27. -------  ---------------------------------------------------
    
28.      52  HASH GROUP BY (cr=27 pr=0 pw=0 time=0 us cost=11268 size=2432 card=38)
    
29.      57   HASH JOIN  (cr=27 pr=0 pw=0 time=0 us cost=11267 size=60416 card=944)
    
30.     159    VIEW  (cr=27 pr=0 pw=0 time=0 us cost=10 size=2703 card=159)
    
31.     159     HASH GROUP BY (cr=27 pr=0 pw=0 time=0 us cost=10 size=5406 card=159)
    
32.     159      TABLE ACCESS FULL RLS_TEMP_LFPACT (cr=27 pr=0 pw=0 time=0 us cost=9 size=5406 card=159)
    
33. 138372    REMOTE  RLS_LFPUFTDH (cr=0 pr=0 pw=0 time=10377402 us cost=11251 size=36344207 card=773281)
    
34. 
    
35. ********************************************************************************
    
36. 
    

复制代码

测试环境：

1. ********************************************************************************
   
2. 
   
3. SQL ID: 8c21hbhf1t608
   
4. Plan Hash: 384927267
   
5. SELECT A.M2PNO, A.INS_CODE SOURCELIB, A.M2PCO, B.ACPLAN,
   
6.   SUM(DECODE(TRIM(M2TCOD),'TUC',A.M2AMTF,'TUP',A.M2AMTF,'LSC',A.M2AMTF,'LSP',
   
7.   A.M2AMTF,'CNL',-1*A.M2AMTF)) AMT652
   
8. FROM
   
9. RLS_LFPUFTDH@BFR A, (SELECT ACPNO,MAX(ACPLAN) ACPLAN FROM RLS_TEMP_LFPACT
   
10.   WHERE ACTCD IN ('Z1', 'Z2', 'Z3', 'Z4') AND ACDOT = :B1 GROUP BY ACPNO) B
    
11.   WHERE A.M2TCOD IN ('CNL','TUC','TUP','LSC','LSP') AND A.M2PNO = B.ACPNO
    
12.   GROUP BY A.M2PNO, A.INS_CODE, A.M2PCO, B.ACPLAN
    
13. 
    
14. 
    
15. call     count       cpu    elapsed       disk      query    current        rows
    
16. ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    
17. Parse        1      0.00       0.00          0          0          0           0
    
18. Execute      1      0.00       0.00          0          1          0           0
    
19. Fetch       53      0.25     119.88          0         27          0          52
    
20. ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    
21. total       55      0.26     119.88          0         28          0          52
    
22. 
    
23. Misses in library cache during parse: 1
    
24. Misses in library cache during execute: 1
    
25. Optimizer mode: ALL_ROWS
    
26. Parsing user id: 134     (recursive depth: 1)
    
27. 
    
28. Rows     Row Source Operation
    
29. -------  ---------------------------------------------------
    
30.      52  HASH GROUP BY (cr=27 pr=0 pw=0 time=0 us cost=11268 size=2432 card=38)
    
31.      57   HASH JOIN  (cr=27 pr=0 pw=0 time=0 us cost=11267 size=60416 card=944)
    
32.     159    VIEW  (cr=27 pr=0 pw=0 time=0 us cost=10 size=2703 card=159)
    
33.     159     HASH GROUP BY (cr=27 pr=0 pw=0 time=0 us cost=10 size=5406 card=159)
    
34.     159      TABLE ACCESS FULL RLS_TEMP_LFPACT (cr=27 pr=0 pw=0 time=0 us cost=9 size=5406 card=159)
    
35. 138372    REMOTE  RLS_LFPUFTDH (cr=0 pr=0 pw=0 time=125985 us cost=11251 size=36344207 card=773281)
    
36. 
    
37. ********************************************************************************
    
38. 
    

复制代码

Maclean Liu(刘相兵

138372    REMOTE  RLS_LFPUFTDH (cr=0 pr=0 pw=0 time=10377402 us cost=11251 size=36344207 card=773281)

由于涉及到 远程对象 所以这里看到的 逻辑读等因素是不真实的

Maclean Liu(刘相兵

REMOTE  RLS_LFPUFTDH (cr=0 pr=0 pw=0 time=10377402 us cost=11251 size=36344207 card=773281)

time=10377402 us

VS


138372    REMOTE  RLS_LFPUFTDH (cr=0 pr=0 pw=0 time=125985 us cost=11251 size=36344207 card=773281)
time=125985 us

看这2个时间点可以 确定 不是真的没有网络问题

wengtf

Maclean Liu(刘相兵 发表于 2013-7-11 21:45
REMOTE  RLS_LFPUFTDH (cr=0 pr=0 pw=0 time=10377402 us cost=11251 size=36344207 card=773281)

time=10 ...

我不确定这个时间点是否是网络上消耗的时间，但是从比例上来看，相差10倍的时间和最终fetch得到数据的时间最多4倍相差有点大啊。

Maclean Liu(刘相兵

select /*+ gather_plan_statistics*/ A.M2PNO, A.INS_CODE SOURCELIB, A.M2PCO, B.ACPLAN,
  SUM(DECODE(TRIM(M2TCOD),'TUC',A.M2AMTF,'TUP',A.M2AMTF,'LSC',A.M2AMTF,'LSP',
  A.M2AMTF,'CNL',-1*A.M2AMTF)) AMT652
FROM
RLS_LFPUFTDH@BFR A, (SELECT ACPNO,MAX(ACPLAN) ACPLAN FROM RLS_TEMP_LFPACT
  WHERE ACTCD IN ('Z1', 'Z2', 'Z3', 'Z4') AND ACDOT = :B1 GROUP BY ACPNO) B
  WHERE A.M2TCOD IN ('CNL','TUC','TUP','LSC','LSP') AND A.M2PNO = B.ACPNO
  GROUP BY A.M2PNO, A.INS_CODE, A.M2PCO, B.ACPLAN;

set linesize 140 pagesize 2000
SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));


给出以上输出

wengtf

加hint后的sqlplan，有点晚到了（我朋友测试）时，由于本地的这张表是张临时表，在存储过程跑完之后就清零了，现在执行那条SQL语句，由于本地表是0条记录，所以很快就出了结果，有劳ML再提比较赞的建议

Maclean Liu(刘相兵

无法比较 度量真实的情况，我们无法给出有价值的意见