UNDO占用问题

发现大量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的例子。