UNDO占用问题

最新推荐文章于 2024-09-03 14:41:05 发布
最新推荐文章于 2024-09-03 14:41:05 发布
阅读量479 收藏
点赞数 1
分类专栏： Oracle 文章标签： 数据库 sql 服务器
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_40066204/article/details/122955618
版权

发现大量UNDO相关的等待事件，enq: US - contention，latch: undo global data
latch: undo global data一般发生在UNDO表空间不足时，应急处理为增加UNDO表空间，或者把UNDO RETENTION调小（如UNEXPIRED EXTENTS较多时有效)

在自己的库上还原问题
环境 19.9 rac pdb

模拟
创建dblink


SQL> alter session set container=wxoadb;

Session altered.

SQL> create public database link sjz111
  2   connect to dbmt identified by dbmt
  3    using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.3.111)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = sjz)))';

Database link created.

SQL> select sysdate from dual@sjz111;

SYSDATE
-------------------
2022-02-12 16:30:17

当时查到的是dblinks 查询sql
每个DBLINK查询产生的分布式事务会分配一个UNDO BLOCK，事务不提交不会重复分配
提交后再查会产生新的，所以产生了问题

防止意外把源端和目标端数据文件 autoextend off


SQL> select ' alter database datafile '''||FILE_NAME||'''  autoextend off;' from dba_data_files where AUTOEXTENSIBLE='YES';

'ALTERDATABASEDATAFILE'''||FILE_NAME||'''AUTOEXTENDOFF;'
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 alter database datafile '+DATA/SJZ/DATAFILE/system.257.1084219591'  autoextend off;
 alter database datafile '+DATA/SJZ/DATAFILE/sysaux.258.1084219663'  autoextend off;
 alter database datafile '+DATA/SJZ/DATAFILE/users.260.1084219691'  autoextend off;
 alter database datafile '+DATA/SJZ/DATAFILE/undotbs1.259.1084219689'  autoextend off;

在目标端创建测试表


  CREATE TABLE "DBMT"."TOBJ"
   (    
        "OBJECT_ID" NUMBER,
        
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "USERS" ;

Table created.

查看一下undo表空间


SQL> @df

TABLESPACE_NAME                   TotalMB     UsedMB     FreeMB % Used Ext Used
------------------------------ ---------- ---------- ---------- ------ --- ----------------------
SYSAUX                                650        607         43    94% NO  |################### |
SYSTEM                                820        730         90    90% NO  |##################  |
TEMP                                   59         59          0   100% NO  |####################|
UNDOTBS1                              200         25        175    13% NO  |###                 |  《《《《
UNDO_2                                100         16         84    16% NO  |####                |
USERS                                   5          2          3    40% NO  |########            |
YCDATA                              84003      75852       8151    91% NO  |################### |

模仿出现问题的sql，改变sql

declare
cnt number;
begin
for i in 1..100000 loop
   select count(*) into cnt from dbmt.tobj@sjz111;
   commit;
end loop;
end


SQL> @trans

 SID SERIAL# USERNAME TADDR            SES_ADDR          USED_UBLK  USED_UREC    0xFLAG STATUS  START_DATE          XIDUSN XIDSLOT XIDSQN XID              PRV_XID          PTX_XID
---- ------- -------- ---------------- ---------------- ---------- ---------- --------- ------- ------------------- ------ ------- ------ ---------------- ---------------- ----------------
 128   18418 SYS      00000000ABB43090 00000000B5833C48          1          1    401603 ACTIVE  2022-02-12 17:14:53      9      20   2348 090014002C090000 0000000000000000 0000000000000000
 

SQL> select status,count(*),tablespace_name,owner from dba_undo_extents group by tablespace_name,owner ,status;

STATUS      COUNT(*) TABLESPACE_NAME                OWN
--------- ---------- ------------------------------ ---
UNEXPIRED        265 UNDOTBS1                       SYS   <<<<<<<<<<<<<<<<
ACTIVE             1 UNDOTBS1                       SYS
EXPIRED            8 UNDOTBS1                       SYS
EXPIRED           36 UNDO_2                         SYS
UNEXPIRED         10 UNDO_2                         SYS

查一下当前的活动会话，和undo的保留时间


SQL> show parameter undo_re

PARAMETER_NAME                                               TYPE        VALUE
------------------------------------------------------------ ----------- ----------------------------------------------------------------------------------------------------
undo_retention                                               integer     900
SQL> @a
A-Script: Display CURRENT active sessions...

  COUNT(*) SQL_ID        STATE   EVENT
---------- ------------- ------- ----------------------------------------------------------------
         1 2m3945pc0f7xa WAITING SQL*Net message from dblink

SQL> select sql_fulltext from v$sqlarea where sql_id='&sql_id'; 
Enter value for sql_id: 2m3945pc0f7xa

SQL_FULLTEXT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
declare
cnt number;
begin
for i in 1..100000 loop
   select count(*) into cnt from dbmt.tobj@sjz111;
   commit;
end loop;
end;

最后发现UNEXPIRED


SQL>select status,count(*),tablespace_name,owner from dba_undo_extents group by tablespace_name,owner ,status;


STATUS      COUNT(*) TABLESPACE_NAME                OWN
--------- ---------- ------------------------------ ---
UNEXPIRED        303 UNDOTBS1                       SYS <<<<<<<<<<<
EXPIRED           36 UNDO_2                         SYS
UNEXPIRED         10 UNDO_2                         SYS

QL> @df

TABLESPACE_NAME                   TotalMB     UsedMB     FreeMB % Used Ext Used
------------------------------ ---------- ---------- ---------- ------ --- ----------------------
SYSAUX                                650        607         43    94% NO  |################### |
SYSTEM                                820        730         90    90% NO  |##################  |
TEMP                                   59         59          0   100% NO  |####################|
UNDOTBS1                              200        200          0   100% NO  |####################| <<<<<<<<<<<<<<<<
UNDO_2                                100         16         84    16% NO  |####                |
USERS                                   5          2          3    40% NO  |########            |
YCDATA                              84003      75852       8151    91% NO  |################### |

7 rows selected.

可以看到这个循环执行前UNDO FREE 200M,执行这个PL/SQL块，很快就用完了

测试把commit 或者dblink 去掉一个undo 段都不会有变化


SQL> select status,count(*),tablespace_name,owner from dba_undo_extents group by tablespace_name,owner ,status;

STATUS      COUNT(*) TABLESPACE_NAME                OWN
--------- ---------- ------------------------------ ---
UNEXPIRED          9 UNDOTBS1                       SYS
ACTIVE             1 UNDOTBS1                       SYS
EXPIRED          309 UNDOTBS1                       SYS
EXPIRED           35 UNDO_2                         SYS
UNEXPIRED         11 UNDO_2                         SYS

SQL> 
SQL> 
SQL> select status,count(*),tablespace_name,owner from dba_undo_extents group by tablespace_name,owner ,status;

STATUS      COUNT(*) TABLESPACE_NAME                OWN
--------- ---------- ------------------------------ ---
UNEXPIRED          9 UNDOTBS1                       SYS
ACTIVE             1 UNDOTBS1                       SYS
EXPIRED          309 UNDOTBS1                       SYS
EXPIRED           35 UNDO_2                         SYS
UNEXPIRED         11 UNDO_2                         SYS

SQL>

----发现并没有问题 ，
结论：只有1、循环DBLINK查询会生一个事务（DBLINK为分布式事务）。
2、每次循环都会提交。同时满足
另外还可以看到这个DBLINK查询导致了REDO的增长。这也是除了延迟块清除之外的另一个查询产生REDO的例子。

Oracle脚本：排查占用UNDO段的SQL
Sebastien23的博客
09-15 393
排查占用UNDO段未释放的SQL。
oracle怎么释放undo空间,如何回缩Undo表空间 释放过度占用空间
weixin_32301287的博客
04-07 1838
【IT168 技术文档】摘要：在使用Oracle9i/Oracle10g的自动Undo管理表空间(AUM)特性时，经常会因为各种原因而导致Undo表空间过度扩展。本文通过一则案例诊断，介绍如何收缩过度扩展的Undo表空间，释放占用空间。环境:OS: Red Hat Enterprise Linux AS release 4 (Nahant)DB: Oracle Database 10g Enter...
mysql中undo空间爆满原因_UNDO空间满的处理方法（含UNDO的学习与相关解释）
weixin_35659543的博客
02-08 1909
1、查看数据库当前实例使用的是哪个UNDO表空间：showparameterundo_tablespace2、查看UNDO表空间对应的数据文件和大小setlines200pages200colfile_namefora60coltablespace_namefora20;selecttablespace_name,file_name,bytes/1024/1024MB...
查询undo的占用
weixin_30537451的博客
11-16 162
select s.username,s.SID, u.name, r.RSSIZE, r.WRITES, r.HWMSIZE from v$transaction t, v$rollstat r, v$rollname u, v$session s where s.taddr = t.addr and t.xidusn = r.usn and r.usn = u.usn ...
oracle----undo表空间
最新发布
睡不醒的每天
09-03 737
SMON进程会去查看 undo segment头部（头部即 undo segment的第一个数据块）记录的事务表（每个事务在使用undo块时，因为其他用户读到的数据是undo segment中原数据块中的数据，若undo块中存放的被修改的数据被其他事务覆盖，则无法找到被修改前的数据，就会抛出ORA-1555 snapshot too old 错误。若将undo_tablespace 参数设置为另一个undo表空间，被称为undo表空间的切换；undo_tablespace系统的默认的UNDO表空间。
找出是什么消耗了最多的UNDO空间
事后诸葛亮的程序人生（微信：zq9017197）
11-07 3161
找出数据库中哪个查询的运行时间最长： select s.sql_text from v$sql s, v$undostat u where u.maxqueryid=s.sql_id; 可以将v$transaction和v$session视图连接起来，找出当前执行的事务中，使用undo空间最多的会话： select s.sid, s.username, t.used
查询UNDO使用情况
diuluoshuai2303的博客
03-13 450
查看UNDO事务占用情况 SELECT s.username, s.sid, pr.PID, s.OSUSER, s.MACHINE, s.PROGRAM, rs.segment_id, r.usn, rs.segment_name, r.rssize/1024/10...
查看undo中sql语句的占用情况
whz110的专栏
08-06 1683
查看undo中sql语句的占用情况 select * from (select maxqueryid, round(sum(undoblks) * 8 / 1024) consumed_size_MB from v$undostat group by maxqueryid order by consumed_size_MB d...
如何Shrink Undo表空间,释放过度占用的空间
09-11
本篇文章主要讲解如何Shrink Undo表空间，释放过度占用的空间，以解决Oracle数据库中的表空间扩展问题。 1. 表空间扩展问题 在Oracle数据库中，表空间的扩展是非常重要的。随着数据的增长，表空间的大小也需要相应...
达梦修改UNDO_RETENTION.zip
11-17
反之，如果设置得过长，会占用大量的撤销空间，影响数据库的整体性能和存储成本。 以下是一些关于如何在达梦数据库中调整UNDO_RETENTION参数的步骤和注意事项： 1. **了解业务需求**：首先，你需要理解你的应用...
ORACLE undo表空间满查询占用sql
gaobudong1234的博客
10-12 9079
select start_time,  --事务起始时间          username,    --用户名          s.MACHINE,   --机器名称          s.OSUSER,    --登录名          r.name,      --回滚段名称          ubafil,      --Undo block address (UBA) fi
Scripts:查询使用UNDO最多的10个SQL hundo10.sql
elvis的Oracle Lab
05-29 1279
set linesize 333                                                                                                                                       col USERNAME format a15                        
Oracle 查看 使用 UNDO 段的事务SQL
hbhe0316的专栏
10-20 791
1.查看oracle undo segment段的信息 https://www.cndba.cn/hbhe0316/article/4961 https://www.cndba.cn/hbhe0316/article/4961https://www.cndba.cn/hbhe0316/article/4961https://www.cndba.cn/hbhe0316/ar...
查看Undo使用情况的SQL
cuijianli8434的博客
06-13 347
Undo整体的使用状况： SELECTseg.tablespace_name "Tablespace Name", ts.bytes/1024/1024 "TS Size(MB)",ue.status "U...
查看UNDO相关数据字典和视图
喝醉酒的小白
08-08 1960
col comments format a100 select * from dict where table_name like '%UNDO%';
undo表空间不足,ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS2'
热门推荐
zilong00007的专栏
08-09 1万+
故障现象：UNDO表空间越来越大,长此下去最终数据因为磁盘空间不足而崩溃； 问题分析：本问题在ORACLE系统管理中属于比较正常的一现象,产生问题的原因主要以下两点： 1. 有较大的事务量让Oracle Undo自动扩展，产生过度占用磁盘空间的情况； 2. 有较大事务没有收
查看undo表空间及undo_retention
a475952074的博客
07-02 6756
命令行模式:show parameter undo_;
查看undo tablespace空间的大小
wonder的地下室
05-02 3134
SELECT (rd * (ups * overhead) + overhead) AS "Bytes"  FROM (SELECT VALUE AS rd FROM v$parameter WHERE NAME = undo_retention),       (SELECT (SUM(undoblks) / SUM(((end_time - begin_time) * 86400))) A
Oracle 12c新特性：Temporary Undo详解
2. **优化Undo保留期管理**：由于临时Undo不会占用Undo表空间，因此对Undo保留期的管理变得更加有效，可以更好地控制Undo的生命周期。 3. **减小重做日志的大小**：不记录临时表的Undo到重做日志，降低了日志文件的...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值