189 8069 5689

undo表空间使用率

      undo表空间是Oracle数据库非常重要的表空间，它的使用率也是DBA关注的重点，但是在繁忙的生产系统中，很容易看到undo表空间使用率非常高的情况，甚至达到100%。那么，undo表空间大小应该设置多大？undo块是怎样进行分配的？undo表空间使用率很高的时候应该怎么处理？本文针对上述问题进行说明。
      一、undo表空间大小设置
             1、首先根据数据库和业务的情况，先预设一个undo 表空间的大小；
             2、待数据库运行一段时间后，可以按照如下方法估算下合理的undo表空间大小：
                  计算公式：UR*(USP*DBS)
                                  UR表示undo_retention参数值
                                  UPS表示每秒产生的undo块数量
                                  DBS表示数据库块大小
  1. SELECT (UR * (UPS * DBS)) AS "Bytes"
  2.   FROM (select max(tuned_undoretention) AS UR from v$undostat),
  3.        (SELECT undoblks / ((end_time - begin_time) * 86400) AS UPS
  4.           FROM v$undostat
  5.          WHERE undoblks = (SELECT MAX(undoblks) FROM v$undostat)),
  6.        (SELECT block_size AS DBS
  7.           FROM dba_tablespaces
  8.          WHERE tablespace_name =
  9.                (SELECT UPPER(value)
  10.                   FROM v$parameter
  11.                  WHERE name = 'undo_tablespace'));
       二、自动管理的undo块的分配算法
             1、如果当前extent中还有空闲块，在需要空间时会继续使用本extent中的下一个空闲块；
             2、当前extent使用完后，如果下一个extent是expired，就跳转到下一个extent的第一个数据块；
             3、如果下一个extent不是expired，就从undo表空间申请空间，如果undo表空间中存在空闲的空间，就分配新的extent加入到undo
                 segment，然后跳转到新的undo extent的第一个数据块；
             4、如果undo表空间没有空闲的空间，就从offine的undo segment中偷取（steal）expired的extent，将offine的undo segment中的
                  expired的 extent分配给当前的undo segment，并跳转到新加入的extent的第一个数据块；
             5、如果在offine的undo segment中没有expired的extent，就从online的undo segment中偷取expired的extent加入到当前undo
                  segment，并跳转到新加入的extent的第一个空闲块；
             6、如果online的segment中没有expired的extent，就扩展undo表空间数据文件（如果开启了自动扩展），添加新的extent到当前的
                  undo segment；
             7、如果undo表空间数据文件不能扩展，调低10%的retention值，然后偷取现在变为expired的undo extent；
             8、从任意一个offine的undo segment中偷取unexpired的extent；
             9、尝试重用当前段中unexpired的extent，如果所有的extent处于currently busy（事务未提交），转入到第10步；
             10、尝试重用任意一个online的undo segment中的unexpired extent；
             11、如果上述所有的步骤都失败，抛出ORA-30036 unable to extend segment by %s in undo tablespace '%s'。

        从以上undo块的分配算法可以看出，当undo表空间的使用率很高的时候，不一定就要增加undo表空间的大小，因为oracle会重用expired
的extent或者unexpired的extent。即使undo表空间使用率到达100%，数据库也不一定会报错。
        undo表空间的使用率可以采用下面的方法查询：
  1. SELECT /* + RULE */ df.tablespace_name "Tablespace",
  2. df.bytes / (1024 * 1024) "Size (MB)",
  3. SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
  4. Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
  5. Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
  6. FROM dba_free_space fs,
  7. (SELECT tablespace_name,SUM(bytes) bytes
  8. FROM dba_data_files
  9. GROUP BY tablespace_name) df
  10. WHERE fs.tablespace_name (+) = df.tablespace_name
  11. GROUP BY df.tablespace_name,df.bytes
  12. UNION ALL
  13. SELECT /* + RULE */ df.tablespace_name tspace,
  14. fs.bytes / (1024 * 1024),
  15. SUM(df.bytes_free) / (1024 * 1024),
  16. Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
  17. Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
  18. FROM dba_temp_files fs,
  19. (SELECT tablespace_name,bytes_free,bytes_used
  20. FROM v$temp_space_header
  21. GROUP BY tablespace_name,bytes_free,bytes_used) df
  22. WHERE fs.tablespace_name (+) = df.tablespace_name
  23. GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
  24. ORDER BY 4 DESC;
       但是，如果undo表空间使用率非常高，且遭遇了ORA-01555或者ORA-30036，就需要进行分析。首先要分析undo表空间的大小设置是否合理，可以使用上文中估算undo表空间合理大小的算法进行计算。如果undo表空间已经足够大，但是还是遭遇ORA-01555或者ORA-30036，就需要从以下几个方面进行排查：
      1、对于ORA-01555需要查看查询语句的运行时间是不是太长，undo_retention设置是否过小；
      2、对于ORA-30036需要排查事务是否过大，undo_retention设置是否过大；
      3、对于ORA-30036还需要关注以下信息：

  1. --查看是否有长时间被占用的undo块
  2. select begin_time,
  3.      end_time,
  4.      undoblks,
  5.      tuned_undoretention,
  6.      maxquerylen,
  7.      maxqueryid
  8.   from v$undostat;
  9.  如果有长时间被占用的undo块，在10g版本中需要关注Bug 5387030
  10.  
  11.   
  12. --查询正在被使用的undo表空间的比例
  13. SELECT
  14.       ((SELECT (NVL(SUM(bytes),0))
  15.            FROM dba_undo_extents
  16.            WHERE tablespace_name=''
  17.            AND status IN ('ACTIVE','UNEXPIRED')) * 100)/
  18.          (SELECT SUM(bytes)
  19.           FROM dba_data_files
  20.           WHERE tablespace_name='')
  21.          "PCT_INUSE"
  22.      FROM dual;

  24. --查看分配给undo表空间的extent的状态
  25. SELECT DISTINCT STATUS, SUM(BYTES)/1024/1024, COUNT(*)
  26.   FROM DBA_UNDO_EXTENTS where tablespace_name = \'UNDOTBS1\' GROUP BY STATUS;

  28. --查询正在使用undo段的事务及使用的undo表空间的大小
  29. select start_time,
  30.        username,
  31.        s.MACHINE,
  32.        s.OSUSER,
  33.        r.name,
  34.        ubafil,
  35.        ubablk,
  36.        t.status,
  37.        (used_ublk * p.value) / 1024 blk, --使用undo表空间的大小
  38.        used_urec,
  39.        s1.SQL_ID,
  40.        s1.SQL_TEXT
  41.   from v$transaction t, v$rollname r, v$session s, v$parameter p,v$sql s1
  42.  where xidusn = usn
  43.    and s.saddr = t.ses_addr
  44.    and p.name = 'db_block_size'
  45.    and s.SADDR=s1.ADDRESS(+)
  46.  order by 1;
       对于ORA-01555，最优的解决办法是缩短查询语句的运行时间，需要对sql进行优化，对于ORA-30036，尽量将大事务进行拆分，分批提交。










分享名称：undo表空间使用率
标题路径：http://cdxtjz.cn/article/pggocj.html

其他资讯