[20220606]purge dba_recyclebin后台操作.txt

[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