[20220606]purge dba_recyclebin後臺操作.txt

[20220606]purge dba_recyclebin後臺操作.txt

--//連結
--//演示了執行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