在shutdown instance时SMON清理“orphaned”

1. Periodically (eg once a day) run exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO will clean out redundant col_usage$ entries, and when
you come to shutdown the database you should not have a huge number of entries left to clean up.

该文档指出了在shutdown instance时SMON会着手清理col_usage$中已被drop表的相关predicate columns的“orphaned”记录，如果在本次实例的生命周期中曾生成大量最后被drop的中间表，那么col_usage$中已经堆积了众多的”orphaned”记录，SMON为了完成cleanup工作需要花费大量时间导致shutdown变慢。这个文档还指出定期执行DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO也可以清理col_usage$中的冗余记录。

我们来观察一下SMON的清理工作:
begin
for i in 1 .. 5000 loop
execute immediate 'create table maclean1' || i ||' tablespace fragment as select 1 t1 from dual';
execute immediate 'select * from maclean1' || i || ' where t1=1';
end loop;
DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
for i in 1 .. 5000 loop
execute immediate 'drop table maclean1' || i;
end loop;
end;
/

SQL> purge dba_recyclebin;
DBA Recyclebin purged.

我们可以通过以下查询了解col_usage$上的orphaned记录总数，这也将是在instance shutdown时
SMON所需要清理的数目

select count(*)
from sys.col_usage$ c
where not exists (select /*+ unnest */
1
from sys.obj$ o
where o.obj# = c.obj#);

COUNT(*)
----------
10224

针对SMON做10046 level 12 trace

SQL> oradebug setospid 30225;
Oracle pid: 8, Unix process pid: 30225, image: [e-mail] (SMON)

SQL> oradebug event 10046 trace name context forever,level 12;
Statement processed.

SQL> shutdown immediate;

=================10046 trace content==================
lock table sys.col_usage$ in exclusive mode nowait

delete from sys.col_usage$ where obj#= :1 and intcol#= :2

delete from sys.col_usage$ c
where not exists (select /*+ unnest */
1
from sys.obj$ o
where o.obj# = c.obj#)本文转自CUUG官网。