[20220606]purge dba_recyclebin后台操作.txt

--//链接https://connor-mcdonald.com/2022/05/30/recycling-saves-the-planet-but-maybe-not-your-database/
--//演示了执行purge dba_recyclebin中,执行缓慢的问题,主要是删除对象太多导致的问题,简单演示:

1.环境:
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.测试:
$ seq 100 | xargs -IQ echo "create table deptQ as select * from dept where rownum=1;" | sqlplus -s -l scott/book > /dev/null
$ seq 100 | xargs -IQ echo "drop table  deptQ; " | sqlplus -s -l scott/book  > /dev/null

SYS@book> @ 10046on 12
Session altered.

SYS@book> purge dba_recyclebin;
DBA Recyclebin purged.

SYS@book> @ 10046off
Session altered.

SYS@book> @ t
TRACEFILE
----------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_34572.trc

$ tkprof /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_34572.trc a.prf
TKPROF: Release 11.2.0.4.0 - Development on Tue Jun 7 16:21:44 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

--//使用tkprof整理分析,内容如下:
delete from RecycleBin$
where
 bo=:1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      100      0.00       0.00          0          0          0           0
Execute    100      0.01       0.01          0        300        710         100
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      200      0.01       0.01          0        300        710         100

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)
Number of plan statistics captured: 100

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  DELETE  RECYCLEBIN$ (cr=3 pr=0 pw=0 time=70 us)
         1          1          1   TABLE ACCESS FULL RECYCLEBIN$ (cr=3 pr=0 pw=0 time=17 us cost=2 size=52 card=1)

--// 如果RECYCLEBIN$对象不多,全表扫描问题不大,如果要删除很多,你可以给RECYCLEBIN$建立索引:
create index RecycleBin$_bo on RecycleBin$(bo);
exec dbms_stats.gather_table_stats('SYS','RecycleBin$',cascade=>TRUE);
re-run purge