计算undo表空间大小
来源:互联网 发布:晨讯资源网源码 编辑:程序博客网 时间:2024/05/29 19:30
计算undo表空间大小
Sizing an UNDO Tablespace
Determining a size for the UNDO tablespace requiresthree pieces of information
• (UR) UNDO_RETENTION in seconds
• (UPS) Number of undo data blocks generated persecond
• (DBS) Overhead varies based on extent and filesize (db_block_size)
UndoSpace = [UR * (UPS * DBS)] + (DBS * 24)
上面是oracle给出计算undo表空空间大小的公式
1. UP undo_retention可以查询v$paramter视图单位是秒
select value as UP from v$parameter where name='undo_retention'
SQL> select value as UP from v$parameter where name='undo_retention';
UP
--------------------------------------------------------------------------------
900
SQL>
2.UPS每秒产生的undo块通过v$undostat计算出单位块
select sum(undoblks)/sum((end_time-begin_time)*86400) as UPS from v$undostat;
SQL> select sum(undoblks)/sum((end_time-begin_time)*86400) as UPS from v$undostat;
UPS
----------
.350380097
SQL>
3.DB_BLOCK_SIZE通过v$paramter可以得到
select value/1024 as DBS from v$parameter where name='db_block_size';
SQL> select value/1024 as DBS from v$parameter where name='db_block_size';
DBS
----------
8
SQL>
4.最后通过公式计算
UndoSpace = [UR * (UPS * DBS)] + (DBS * 24)
用下面select直接可以计算出undo大小单位为K
select (UR * (UPS * DBS) + (DBS * 24)) as "size" from (select sum(undoblks)/sum((end_time-begin_time)*86400) as UPS from v$undostat),(select value as UR from v$parameter where name='undo_retention'),(select value/1024 as DBS from v$parameter where name='db_block_size');
SQL> select (UR * (UPS * DBS) + (DBS * 24)) as "size" from (select sum(undoblks)
/sum((end_time-begin_time)*86400) as UPS from v$undostat),(select value as UR fr
om v$parameter where name='undo_retention'),(select value/1024 as DBS from v$par
ameter where name='db_block_size');
size
----------
2691.54825
SQL>
5.上面计算的undo大小最好是在业务高峰期计算的值
- 计算undo表空间大小
- undo系列学习之如何计算undo表空间的大小
- UNDO表空间大小评估
- 估算Undo表空间大小
- Oracle undo表空间大小估算
- 如何缩小undo表空间的大小
- oracle undo 表空间 ——估算UNDO表空间的大小
- 如何确定或调整undo表空间的大小
- 查看undo tablespace空间的大小
- 重置UNDO 表空间.
- 监控Undo表空间
- 管理UNDO表空间
- undo表空间管理
- UNDO 表空间管理
- undo表空间
- UNDO表空间
- 释放UNDO表空间
- undo表空间
- MDL的理解
- adb logcat查看日志
- Debian 6 字体显示效果调整
- Android系统中具备6个模式(复制自网络)
- 单片机开发之声光报警Helloworld
- 计算undo表空间大小
- Android的情景模式(复制自网络)
- android电话录音(整理自网络)
- linux .deb文件安装
- 快速创建与现有表相同结构的表
- 注意力经济
- 2012-06-06
- java多线程
- 在linux下利用crontab定时执行PHP脚本