案例精解：insert逻辑读暴增至20万，只因Oracle Recyclebin过大

最新推荐文章于 2023-07-06 09:06:54 发布

数据和云

最新推荐文章于 2023-07-06 09:06:54 发布

阅读量377

点赞数

文章标签：数据库 mysql oracle ext db2

本文链接：https://blog.csdn.net/enmotech/article/details/112855434

版权

本文通过一个案例分析了当Oracle数据库Recyclebin占用空间过大时，如何引发insert操作逻辑读显著增加的问题。测试过程揭示了由于没有合适索引，导致full scan并严重影响性能。解决方案包括清理回收站对象，以及在RecycleBin$上创建索引以提高purge操作的效率。

摘要由CSDN通过智能技术生成

墨墨导读：某客户的数据库一条insert某段时间突然变慢，平均单次执行逻辑读暴增至20万，最终定位为insert时因可用空间不充足递归清理recyclebin中的对象导致。

概述

某客户的数据库一条insert语句某段时间突然变慢，平均单次执行逻辑读暴增至20万，最终定位为insert时因可用空间不充足递归清理recyclebin中的对象导致，递归delete from RECYCLEBIN$，因没有合适的索引走full scan，正常情况下也没啥影响，但该客户的这套库SYS.RECYCLEBIN$竟然达到700M.最终导致了该问题。
这里在我的测试环境中，模拟重现该问题。

测试过程

清空回收

SQL> purge dba_recyclebin;
DBA Recyclebin purged.

创建测试表空间

SQL> create tablespace testtbs datafile '/home/oracle/data/testtbs.dbf' size 10m;


Tablespace created.


SQL> @df


TABLESPACE_NAME                   TotalMB     UsedMB     FreeMB % Used Ext Used
------------------------------ ---------- ---------- ---------- ------ --- ----------------------
SYSAUX                                350        328         22    94% YES |################### |
SYSTEM                                280        271          9    97% YES |####################|
TBS                                    20         17          3    85% NO  |#################   |
TBS2                                   10          2          8    20% NO  |####                |
TEMP                                   36        -64        100  -177% YES ||
TESTTBS                                10          1          9    10% NO  |##                  |
UNDOTBS1                              100         48         52    48% YES |##########          |


7 rows selected.


SQL>

3. 创建3个测试表
tmp1,tmp2 10000条记录
tmp3 空表

SQL> create table test.tmp1 tablespace testtbs as select * from dba_objects where rownum<=10000;
Table created.
SQL> SQL> create table test.tmp2 tablespace testtbs as select * from dba_objects where rownum<10000;
Table created.
SQL> SQL> create table test.tmp3 tablespace testtbs as select * from dba_objects where 1=0;
Table created.

删除表 test.tmp1

SQL> drop table  test.tmp1 ;
Table dropped.

insert sys.RECYCLEBIN$,把sys.RECYCLEBIN$ 撑大

SQL> insert into sys.RECYCLEBIN$  select t.* from sys.RECYCLEBIN$ t connect by level<2000000;insert into sys.RECYCLEBIN$  select t.* from sys.RECYCLEBIN$ t connect by level<2000000                *ERROR at line 1:ORA-30009: Not enough memory for CONNECT BY operation

这个报错，无影响，达到撑大sys.RECYCLEBIN$的目的就行

SQL> rollback;
Rollback complete.
SQL> @seg sys.RECYCLEBIN$
    SEG_MB OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                BLOCKS     HDRFIL     HDRBLK-