首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >老生常谈:关于undo表空间的使用率

老生常谈:关于undo表空间的使用率

作者头像
Alfred Zhao
发布2019-05-24 19:55:42
8470
发布2019-05-24 19:55:42
举报

就在前几天,又有一个客户向我咨询undo表空间使用率的问题。 这让我想起几年前曾经有个省份的案例,客户的实际运维人员是一位刚毕业不久的女孩,几乎不懂Oracle原理,项目经理交给她的任务也是基础运维工作,比如其中一项就是监测数据库各个表空间的使用率,并对使用率超过95%的表空间进行扩展,他们的Oracle版本是10gR2。 由于该客户业务是运营商话单相关的,业务数据量很大(几十T的规模),所以预留存储的空间也很充足。 有一次该客户有其他问题找到我远程处理的时候,我惊奇的发现他们的undo表空间居然有2个多T大小。进而询问运维人员是怎么回事,想必结果大家已经猜到了,这女孩说她日常巡检经常发现undo表空间使用率超过95%,所以她就不停地扩展,直到如今已经加到2个多T规模的大小。她甚至认为undo表空间也是某一个业务的表空间,这就尴尬了。 那么,究竟什么是undo?undo都有哪些实际作用呢?Oracle 10g的官方文档是这样描述的:

What Is Undo? Every Oracle Database must have a method of maintaining information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. These records are collectively referred to as undo. Undo records are used to: Roll back transactions when a ROLLBACK statement is issued Recover the database Provide read consistency Analyze data as of an earlier point in time by using Oracle Flashback Query Recover from logical corruptions using Oracle Flashback features When a ROLLBACK statement is issued, undo records are used to undo changes that were made to the database by the uncommitted transaction. During database recovery, undo records are used to undo any uncommitted changes applied from the redo log to the datafiles. Undo records provide read consistency by maintaining the before image of the data for users who are accessing the data at the same time that another user is changing it.

具体来看下我10.2.0.5实验环境下undo相关参数的默认设置:

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

可以看到undo_management默认设置为AUTO,关于这个值,官档这样描述:

Automatic undo management uses an undo tablespace.To enable automatic undo management, set the UNDO_MANAGEMENT initialization parameter to AUTO in your initialization parameter file. In this mode, undo data is stored in an undo tablespace and is managed by Oracle Database.

而对于undo_retention这个值,默认是900,单位是s,也就是15分钟。很多实际的环境,也会考虑将其设置的大一些,比如10800,即3小时。 来看下官档对于undo retention和与之相关的retention guarantee的具体描述:

Undo Retention After a transaction is committed, undo data is no longer needed for rollback or transaction recovery purposes. However, for consistent read purposes, long-running queries may require this old undo information for producing older images of data blocks. Furthermore, the success of several Oracle Flashback features can also depend upon the availability of older undo information. For these reasons, it is desirable to retain the old undo information for as long as possible. When automatic undo management is enabled, there is always a current undo retention period, which is the minimum amount of time that Oracle Database attempts to retain old undo information before overwriting it. Old (committed) undo information that is older than the current undo retention period is said to be expired. Old undo information with an age that is less than the current undo retention period is said to be unexpired. Oracle Database automatically tunes the undo retention period based on undo tablespace size and system activity. You can specify a minimum undo retention period (in seconds) by setting the UNDO_RETENTION initialization parameter. The database makes its best effort to honor the specified minimum undo retention period, provided that the undo tablespace has space available for new transactions. When available space for new transactions becomes short, the database begins to overwrite expired undo. If the undo tablespace has no space for new transactions after all expired undo is overwritten, the database may begin overwriting unexpired undo information. If any of this overwritten undo information is required for consistent read in a current long-running query, the query could fail with the snapshot too old error message. The following points explain the exact impact of the UNDO_RETENTION parameter on undo retention: The UNDO_RETENTION parameter is ignored for a fixed size undo tablespace. The database may overwrite unexpired undo information when tablespace space becomes low. For an undo tablespace with the AUTOEXTEND option enabled, the database attempts to honor the minimum retention period specified by UNDO_RETENTION. When space is low, instead of overwriting unexpired undo information, the tablespace auto-extends. If the MAXSIZE clause is specified for an auto-extending undo tablespace, when the maximum size is reached, the database may begin to overwrite unexpired undo information. Retention Guarantee To guarantee the success of long-running queries or Oracle Flashback operations, you can enable retention guarantee. If retention guarantee is enabled, the specified minimum undo retention is guaranteed; the database never overwrites unexpired undo data even if it means that transactions fail due to lack of space in the undo tablespace. If retention guarantee is not enabled, the database can overwrite unexpired undo when space is low, thus lowering the undo retention for the system. This option is disabled by default. WARNING: Enabling retention guarantee can cause multiple DML operations to fail. Use with caution. You enable retention guarantee by specifying the RETENTION GUARANTEE clause for the undo tablespace when you create it with either the CREATE DATABASE or CREATE UNDO TABLESPACE statement. Or, you can later specify this clause in an ALTER TABLESPACE statement. You disable retention guarantee with the RETENTION NOGUARANTEE clause. You can use the DBA_TABLESPACES view to determine the retention guarantee setting for the undo tablespace. A column named RETENTION contains a value of GUARANTEE, NOGUARANTEE, or NOT APPLY (used for tablespaces other than the undo tablespace).

看到这里,我们已经可以明白,对于本文开头我说到的那个案例,为什么undo明明是可以循环利用的,却不断增长最终使得那个女孩不断扩展undo表空间。 之前看到Maclean在群里答复一个网友的相关提问,给出了如下语句来查询undo真实的使用率:

prompt 
prompt  ############## IN USE Undo Data ############## 
prompt 

select 
((select (nvl(sum(bytes),0)) 
from dba_undo_extents 
where tablespace_name in (select tablespace_name from dba_tablespaces
   where retention like '%GUARANTEE' )
and status in ('ACTIVE','UNEXPIRED')) *100) / 
(select sum(bytes) 
from dba_data_files 
where tablespace_name in (select tablespace_name from dba_tablespaces
   where retention like '%GUARANTEE' )) "PCT_INUSE" 
from dual; 

可以看到,这个语句实际上就是将状态为ACTIVE和UNEXPIRED的,计算为已使用。如果retention guarantee并没有设置的话,那么这个使用率高也不一定会有问题,因为Oracle会将unexpired状态的也拿来重用。 另外需要注意,如果是RAC,上述的查询会将两个实例的结果平均,而实际上我们希望是各自统计各自的。所以可以直接指定我们要查询的undo表空间名称:

select 
((select (nvl(sum(bytes),0)) 
from dba_undo_extents 
where tablespace_name = '&TABLESPACE_NAME'
and status in ('ACTIVE','UNEXPIRED')) *100) / 
(select sum(bytes) 
from dba_data_files 
where tablespace_name = '&TABLESPACE_NAME') "PCT_INUSE" 
from dual; 

也可以通过dba_undo_extents监控undo表空间的使用情况,按状态分组:

select tablespace_name, status, sum(bytes/1024/1024) "MB"
from dba_undo_extents
group by tablespace_name, status
order by 1, 2;

根据上面的知识,我们只需关注结果中状态为ACTIVE的占用多少,如果设置了retention guarantee,那么还要同时关注UNEXPIRED的占用多少。

此外,从Maclean的博客中找到两条实用的UNDO表空间监控的查询SQL:

--在Oracle 10g版本中可以使用V$UNDOSTAT视图用于监控实例中当前事务使用UNDO表空间的情况。视图中的每行列出了每隔十分钟从实例中收集到的统计信息。
--每行都表示了在过去7*24小时里每隔十分钟UNDO表空间的使用情况,事务量和查询长度等信息的统计快照。
--UNDO表空间的使用情况会因事务量变化而变化,一般我们在计算时同时参考UNDO表空间的平均使用情况和峰值使用情况

--以下SQL语句用于计算过去7*24小时中UNDO表空间的平均使用量
select ur undo_retention,
       dbs db_block_size,
       ((ur * (ups * dbs)) + (dbs * 24)) / 1024 / 1024 as "M_bytes"
  from (select value as ur from v$parameter where name = 'undo_retention'),
       (select (sum(undoblks) / sum(((end_time - begin_time) * 86400))) ups
          from v$undostat),
       (select value as dbs from v$parameter where name = 'db_block_size');
       
--以下SQL语句则按峰值情况计算UNDO表空间所需空间:
select ur undo_retention,
       dbs db_block_size,
       ((ur * (ups * dbs)) + (dbs * 24)) / 1024 / 1024 as "M_bytes"
  from (select value as ur from v$parameter where name = 'undo_retention'),
       (select (undoblks / ((end_time - begin_time) * 86400)) ups
          from v$undostat
         where undoblks in (select max(undoblks) from v$undostat)),
       (select value as dbs from v$parameter where name = 'db_block_size');

最后,实际我们透过这个简单的案例来看,实际很多项目上,也的确真实存在一些运维人员,他们并不具备相应的知识储备,就直接去做相应工作了,其结果就是让本不复杂的系统布满了各种各样的坑。 所以,无论是学什么做什么,对于基础知识还是要深入的去学习和思考的,不积跬步无以至千里。

本文参与 腾讯云自媒体分享计划,分享自作者个人站点/博客。
原始发表:2017-10-31 ,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
数据库
云数据库为企业提供了完善的关系型数据库、非关系型数据库、分析型数据库和数据库生态工具。您可以通过产品选择和组合搭建,轻松实现高可靠、高可用性、高性能等数据库需求。云数据库服务也可大幅减少您的运维工作量,更专注于业务发展,让企业一站式享受数据上云及分布式架构的技术红利!
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档