oracle 估算undo脚本

最新推荐文章于 2023-08-03 17:23:21 发布

zw_hard

最新推荐文章于 2023-08-03 17:23:21 发布

阅读量590

点赞数

CC 4.0 BY-SA版权

分类专栏： Frequent Use Script 文章标签： oracle 估算undo 脚本

本文链接：https://blog.youkuaiyun.com/zhengwei125/article/details/47947693

Frequent Use Script 专栏收录该内容

7 篇文章

订阅专栏

本文提供了Oracle数据库中UNDO空间需求的估算方法，包括10g之前的版本及之后的不同计算方式，并介绍了如何使用DBMS_ADVISOR来获取Oracle关于UNDO的建议设置。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

摘自老道博客： http://blog.youkuaiyun.com/daohengshangqian/article/details/39268609

metalink 给出的公式是
UR =undo_retention参数值
UPS = 每秒产生的undo block数量
DBS = undo tablespace block size

10g之前

SELECT (UR * (UPS * DBS)) AS "Bytes" 
   FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'), 
        (SELECT undoblks / ((end_time - begin_time) * 86400) AS UPS 
           FROM v$undostat 
          WHERE undoblks = (SELECT MAX(undoblks) FROM v$undostat)), 
        (SELECT block_size AS DBS 
           FROM dba_tablespaces 
          WHERE tablespace_name = 
                (SELECT UPPER(value) 
                   FROM v$parameter 
                  WHERE name = 'undo_tablespace'));

10g 以后

SELECT (UR * (UPS * DBS)) AS "Bytes" 
  FROM (select max(tuned_undoretention) AS UR from v$undostat), 
       (SELECT undoblks / ((end_time - begin_time) * 86400) AS UPS 
          FROM v$undostat 
         WHERE undoblks = (SELECT MAX(undoblks) FROM v$undostat)), 
       (SELECT block_size AS DBS 
          FROM dba_tablespaces 
         WHERE tablespace_name = 
               (SELECT UPPER(value) 
                  FROM v$parameter 
                 WHERE name = 'undo_tablespace'));

当然也可以用过DBMS_ADVISOR来获取Oracle关于undo的建议设置，当然不是每次都能获取到建议，相比还是上面提供的脚本比较方便。

下面语句来自于官方文档稍微做了一点修改

其中123 124是snapshot的ID 需要根据实际做修改。

DECLARE 
   tid    NUMBER; 
   tname  VARCHAR2( 30); 
   oid    NUMBER; 
BEGIN 
   DBMS_ADVISOR.CREATE_TASK('Undo Advisor' , tid, tname, 'Undo Advisor Task' ); 
   DBMS_ADVISOR.CREATE_OBJECT(tname, 'UNDO_TBS' , null, null, null, 'null', oid); 
   DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'TARGET_OBJECTS' , oid); 
   DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'START_SNAPSHOT' , 123); 
   DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'END_SNAPSHOT' , 124); 
   DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'INSTANCE' , 1); 
   DBMS_ADVISOR.execute_task(tname); 
   DBMS_OUTPUT.put_line( 'select * dba_advisor_log  WHERE TASK_ID ='||tid || ' ;'); 
   DBMS_OUTPUT.put_line( 'select * DBA_ADVISOR_FINDINGS  WHERE TASK_ID ='||tid|| ' ;'); 
   DBMS_OUTPUT.put_line( 'select * DBA_ADVISOR_RECOMMENDATIONS  WHERE TASK_ID ='||tid||' ;'); 
END; 
/