undo tablespace 已达到了96G还报ora-01555错误

yobyin

如title ,目前默认的undo_retention=900 s  ，两个undo  已经开到96g，还在报错，现将alte.log 与undostat 发上来，请各位给看一下，谢谢.

yobyin

这个问题，理论上可以修retetion的时间减少错误。但是别的现场 undo tablespace 只有32g .所以我想找到真正的原因。

Maclean Liu(刘相兵

ODM DATA:
Linux x86 64-bit 11.1.0.7.0 RAC=yes

Redo size:        18,421,504.8        507,954.2         redo  17MB/s
Hard parses:        39.6                每秒硬解析 39次
Transactions:        36.3               每秒事务 36个

Elapsed:                 60.32 (mins)                  
DB Time:                 777.45 (mins)                

db time / elapsed = 12  负载较高

ORA-01555 caused by SQL statement below (SQL ID: ftpjxbvnurggw, Query Duration=1805 sec, SCN: 0x0000.a394673e):
merge into t_mo_temp moTemp using (select DISTINCT p1.moentityname as p1_moentityname, p1.moentityid as p1_moentityid, p0.t0_moentityid as p2_moentityid  from (select t0.motypeid as t0_motypeid, t0.moentityid as t0_moentityid, t0.abovemotypeid as t0_abovemotypeid, t0.abovemoentityid as t0_abovemoentityid   from pm4h_sum.v_mo_entity_relation t0  where t0.motypeid = '982c8e3f-bf32-4828-86e8-0baf1dac8d08' and t0.abovemotypeid = 'ccfc8c6e-f3e6-4836-a7a1-757bbaa0e02f') p0  left join pm4h_sum.v_mo_entity p1 on p1.moentityid = p0.t0_abovemoentityid ) moEnity on (moTemp.id0 = moEnity.p2_moentityid)  when matched then update  set moTemp.id1 = moEnity.p1_moentityid, moTemp.name1 = moEnity.p1_moentityname
Tue Apr 24 09:55:48 2012
Thread 2 advanced to log sequence 71782 (LGWR switch)
  Current log# 17 seq# 71782 mem# 0: +DG_DATA/mosmir/onlinelog/redo17.log
Tue Apr 24 09:57:17 2012
ORA-01555 caused by SQL statement below (SQL ID: ftpjxbvnurggw, Query Duration=3536 sec, SCN: 0x0000.a338c56c):
merge into t_mo_temp moTemp using (select DISTINCT p1.moentityname as p1_moentityname, p1.moentityid as p1_moentityid, p0.t0_moentityid as p2_moentityid  from (select t0.motypeid as t0_motypeid, t0.moentityid as t0_moentityid, t0.abovemotypeid as t0_abovemotypeid, t0.abovemoentityid as t0_abovemoentityid   from pm4h_sum.v_mo_entity_relation t0  where t0.motypeid = '982c8e3f-bf32-4828-86e8-0baf1dac8d08' and t0.abovemotypeid = 'ccfc8c6e-f3e6-4836-a7a1-757bbaa0e02f') p0  left join pm4h_sum.v_mo_entity p1 on p1.moentityid = p0.t0_abovemoentityid ) moEnity on (moTemp.id0 = moEnity.p2_moentityid)  when matched then update  set moTemp.id1 = moEnity.p1_moentityid, moTemp.name1 = moEnity.p1_moentityname


ORA-01555 caused by SQL statement below (SQL ID: 71qbcdwgg9kqt, Query Duration=4002 sec, SCN: 0x0000.a7318812):
merge into INDICATOR_12761 m using ( select tt2.moentityid,startday,starttime,6000 period, nvl(INDICATOR_9575_5,0)+nvl(INDICATOR_9581_1,0)+nvl(INDICATOR_9581_2,0)+nvl(INDICATOR_9591_10,0)+nvl(INDICATOR_9589_10,0) as INDICATOR_12761_44,nvl(INDICATOR_9576_1,0) as INDICATOR_12761_34,nvl(INDICATOR_9592_1,0)+nvl(INDICATOR_9592_3,0)+nvl(INDICATOR_9592_2,0)+nvl(INDICATOR_9592_5,0)+nvl(INDICATOR_9592_11,0)+nvl(INDICATOR_9592_7,0)+nvl(INDICATOR_9592_9,0)+nvl(INDICATOR_9592_4,0)+nvl(INDICATOR_9592_8,0)+nvl(INDICATOR_9592_12,0)+nvl(INDICAT

merge into INDICATOR_12761 m using ( select tt2.moentityid,startday,starttime,6000 period, nvl(INDICATOR_9575_5,0)+nvl(INDICATOR_9581_1,0)+nvl(INDICATOR_9581_2,0)+nvl(INDICATOR_9591_10,0)+nvl(INDICATOR_9589_10,0) as INDICATOR_12761_44,nvl(INDICATOR_9576_1,0) as INDICATOR_12761_34,nvl(INDICATOR_9592_1,0)+nvl(INDICATOR_9592_3,0)+nvl(INDICATOR_9592_2,0)+nvl(INDICATOR_9592_5,0)+nvl(INDICATOR_9592_11,0)+nvl(INDICATOR_9592_7,0)+nvl(INDICATOR_9592_9,0)+nvl(INDICATOR_9592_4,0)+nvl(INDICATOR_9592_8,0)+nvl(INDICATOR_9592_1

引起 ORA-01555的主要是 merge into using (select ...) 的语句


AWR中的undo信息 ，并发事务Max Tx Concy 较高


Undo Segment Summary

    Min/Max TR (mins) - Min and Max Tuned Retention (minutes)
    STO - Snapshot Too Old count, OOS - Out of Space count
    Undo segment block stats:
    uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed
    eS - expired Stolen, eR - expired Released, eU - expired reUsed

Undo TS#        Num Undo Blocks (K)        Number of Transactions        Max Qry Len (s)        Max Tx Concurcy        Min/Max TR (mins)        STO/ OOS        uS/uR/uU/ eS/eR/eU
4        7,309.49        2,772,725        1,281        33        26.7/41.4        0/0        0/0/0/0/0/0

Back to Undo Statistics
Back to Top

Undo Segment Stats

    Most recent 35 Undostat rows, ordered by Time desc

End Time        Num Undo Blocks        Number of Transactions        Max Qry Len (s)        Max Tx Concy        Tun Ret (mins)        STO/ OOS        uS/uR/uU/ eS/eR/eU
07-May 07:51        1,935,982        778,280        382        26        34        0/0        0/0/0/0/0/0
07-May 07:41        1,413,824        618,534        865        33        33        0/0        0/0/0/0/0/0
07-May 07:31        1,483,901        634,973        781        31        41        0/0        0/0/0/0/0/0
07-May 07:21        397,271        186,290        1,281        30        32        0/0        0/0/0/0/0/0
07-May 07:11        370,075        16,748        679        13        35        0/0        0/0/0/0/0/0
07-May 07:01        1,708,433        537,900        878        28        27        0/0        0/0/0/0/0/0



在07-May-12 07:00:08 到 07-May-12 08:00:27 没有发生 expired Stolen unexpired Stolen


10g 中默认启用 undo auto tune 所以在undo 表空间够大的情况下 undo_retention 自动调优会比 实际的 undo_retention(默认900) 参数要大得多，


在 AWR中可以看到 Tun Ret (mins)约为 30分钟= 1800s， 这说明系统对undo的 需求非常大


1. 建议 调优merge 语句相关的select 查询 ，减少查询时间 避免出现ORA-01555

另一个 非常值得关注的是 问题是：

table fetch continued row        13,826,364        3,820.20        105.34

每秒 table fetch continued row         达到 3820次 说明 系统中存在大量的chained row， 这可能是 造成查询时间过长的直接原因 之一 ， 建议你找出chained rows相关的表



2.

执行以下脚本 并贴出输出 ，找出合适的undo 表空间大小

1. set serveroutput on;
   
2. DECLARE
   
3. tablespacename        varchar2(30);
   
4. tablespacesize        number;
   
5. autoextend            boolean;
   
6. autoextendtf          char(5);
   
7. undoretention         number;
   
8. retentionguarantee    boolean;
   
9. retentionguaranteetf  char(5);
   
10. autotuneenabled       boolean;
    
11. autotuneenabledtf     char(5);
    
12. longestquery          number;
    
13. requiredretention     number;
    
14. bestpossibleretention number;
    
15. requireundosize       number;
    
16. 
    
17. 
    
18. problem               varchar2(100);
    
19. recommendation        varchar2(100);
    
20. rationale             varchar2(100);
    
21. retention             number;
    
22. utbsize               number;
    
23. nbr                   number;
    
24. undoadvisor           varchar2(100);
    
25. instancenumber        number;
    
26. ret                   boolean;
    
27. rettf                 char(5);
    
28. BEGIN
    
29.    dbms_output.put_line('--x--x--x--x--x--x--x');
    
30.    dbms_output.put_line('--undo_info');
    
31.    dbms_output.put_line('--x--x--x--x--x--x--x');
    
32.    ret := dbms_undo_adv.undo_info(tablespacename,
    
33. tablespacesize,
    
34.           autoextend, undoretention,
    
35. retentionguarantee);
    
36.    if ret then rettf := 'TRUE'; else rettf :=
    
37. 'FALSE'; end if;
    
38.    if autoextend then autoextendtf := 'TRUE';
    
39.       else autoextendtf := 'FALSE'; end if;
    
40.    if retentionguarantee then retentionguaranteetf
    
41. := 'TRUE';
    
42.       else retentionguaranteetf := 'FALSE'; end if;
    
43.    dbms_output.put_line ('Information Valid    :
    
44. '||rettf);
    
45.    dbms_output.put_line ('Tablespace Name      :
    
46. '||tablespacename);
    
47.    dbms_output.put_line ('Tablespace Size      :
    
48. '||tablespacesize);
    
49.    dbms_output.put_line ('Extensiable          :
    
50. '||autoextendtf);
    
51.    dbms_output.put_line ('undo_retention       :
    
52. '||undoretention);
    
53.    dbms_output.put_line ('Guaranteed Retention :
    
54. '||retentionguaranteetf);
    
55. 
    
56. 
    
57.    dbms_output.put_line('--x--x--x--x--x--x--x');
    
58.    dbms_output.put_line('--undo_health');
    
59.    dbms_output.put_line('--x--x--x--x--x--x--x');
    
60.    nbr := dbms_undo_adv.undo_health(problem,
    
61. recommendation, rationale, retention, utbsize);
    
62.    dbms_output.put_line ('Information Valid    :
    
63. '||nbr);
    
64.    dbms_output.put_line ('Problem              :
    
65. '||problem);
    
66.    dbms_output.put_line ('Recommendation       :
    
67. '||recommendation);
    
68.    dbms_output.put_line ('Rationale            :
    
69. '||rationale);
    
70.    dbms_output.put_line ('Retention            :
    
71. '||retention);
    
72.    dbms_output.put_line ('UTBSize              :
    
73. '||utbsize);
    
74. 
    
75. 
    
76.    dbms_output.put_line('--x--x--x--x--x--x--x');
    
77.    dbms_output.put_line('--undo_advisor');
    
78.    dbms_output.put_line('--x--x--x--x--x--x--x');
    
79.    select instance_number into instancenumber from
    
80. v$instance;
    
81.    undoadvisor :=
    
82. dbms_undo_adv.undo_advisor(instancenumber);
    
83.    dbms_output.put_line ('Undo Advisor         :
    
84. '||undoadvisor);
    
85. 
    
86. 
    
87.    dbms_output.put_line('--x--x--x--x--x--x--x');
    
88.    dbms_output.put_line('--undo_autotune');
    
89.    dbms_output.put_line('--x--x--x--x--x--x--x');
    
90.    ret :=
    
91. dbms_undo_adv.undo_autotune(autotuneenabled);
    
92.    if autotuneenabled then autotuneenabledtf :=
    
93. 'TRUE';
    
94.        else autotuneenabledtf := 'FALSE'; end if;
    
95.    dbms_output.put_line ('Auto Tuning Enabled  :
    
96. '||autotuneenabledtf);
    
97. 
    
98. 
    
99.    dbms_output.put_line('--x--x--x--x--x--x--x');
    
100.    dbms_output.put_line('--longest_query');
     
101.    dbms_output.put_line('--x--x--x--x--x--x--x');
     
102.    longestquery :=
     
103. dbms_undo_adv.longest_query(sysdate-1,sysdate);
     
104.    dbms_output.put_line ('Longest Run Query    :
     
105. '||longestquery);
     
106. 
     
107. 
     
108.    dbms_output.put_line('--x--x--x--x--x--x--x');
     
109.    dbms_output.put_line('--required_retention');
     
110.    dbms_output.put_line('--x--x--x--x--x--x--x');
     
111.    requiredretention :=
     
112. dbms_undo_adv.required_retention;
     
113.    dbms_output.put_line ('Required Retention   :
     
114. '||requiredretention);
     
115. 
     
116. 
     
117.    dbms_output.put_line('--x--x--x--x--x--x--x');
     
118.   
     
119. dbms_output.put_line('--best_possible_retention');
     
120.    dbms_output.put_line('--x--x--x--x--x--x--x');
     
121.    bestpossibleretention :=
     
122. dbms_undo_adv.best_possible_retention;
     
123.    dbms_output.put_line ('Best Retention       :
     
124. '||bestpossibleretention);
     
125. 
     
126. 
     
127.    dbms_output.put_line('--x--x--x--x--x--x--x');
     
128.    dbms_output.put_line('--required_undo_size');
     
129.    dbms_output.put_line('--x--x--x--x--x--x--x');
     
130.    requireundosize := dbms_undo_adv.required_undo_size(444);
     
131.    dbms_output.put_line ('Required Undo Size   :
     
132. '||requireundosize);
     
133. 
     
134. 
     
135. END;
     
136. /

复制代码

yobyin

真是专业，多向你学习。谢谢.

不了峰

我也小分析一下，可能有些跑题

1.db time / elapsed = 12  负载较高
  我认为应该 是IO的负载较高，cpu有一定的空闲
  从NUM_CPUS=48,LOAD=6        来看，cpu有一定的空闲
  但从log file sync Avg wait (ms)        ＝26 说明写入有一点的慢，也许这是由大量的db file sequential read 所相互影响的。另外从File IO Stats  的Av   Rd(ms) 来看.

2.Estd Interconnect traffic (KB)＝12MB ,应该可以说明cache fusion 交换比较厉害。

3.Library Hit %:95% ，是不是有必要提高一下这个的值 ，当然我认为这一定和01555的错误无关。

4.我非常困惑的是每秒产生 17MB 的redo.从awr报告 中看，我不认为insert ,与delete会产生那么大的redo. 当然这是我主观的意测。
  楼主能不能评估一下数据，看比如说每秒会有多少的数据导入，及多少的数据会被delete. 然后 与这个17MB/s的值 进行比较。
  
  如果相差很远。另从merge的语句我简单分析，从awr生成的时间是7号的7点到8点。而merge的查询数据时间应该是7号这一天的某一段的数据，
  那么接合这两个，应该 很有可能就是 延迟块清除 引起的ora-01555        .
  
欢迎刘大指正

[ 本帖最后由 不了峰 于 2012-5-8 14:00 编辑 ]

Maclean Liu(刘相兵

ODM DATA:

deferred (CURRENT) block cleanout applications        2,530,103        699.06        19.28


就AWR 看 确实存在一定的 deferred (CURRENT) block cleanout ， 但 延迟块清除 造成ORA-01555的本质 仍是 查询时间过久 导致undo被覆盖。

解决的思路：

1. 优化查询语句 缩短查询时间 包括减少 链式行
or
2. 通过undo advisor了解undo的需求 ，可能进一步增大undo TBS

gdpr-dba

请问Library Hit和01555有什么关系？

不了峰

笔误，  不好意思，是一定没有关系