字典基表与SMON的清理

Unless and until the database is shutdown, the col_usage$ table will continue to grow.

Solution

To implement the workaround, please execute the following steps:

 

oracle认证 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.

 

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

 

oracle认证 我们来观察一下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.

 

oracle认证 我们可以通过以下查询了解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

 

oracle认证 针对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#)

 

如何禁止SMON维护col_usage$字典基表

 

设置隐藏参数_column_tracking_level(column usage tracking)，该参数默认为1即启用column使用情况跟踪。设置该参数为0，将禁用column tracking，该参数可以在session和system级别动态修改:

SQL> col name for a25

SQL> col DESCRIB for a25

SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ

  2   FROM SYS.x$ksppi x, SYS.x$ksppcv y

  3   WHERE x.inst_id = USERENV ('Instance')

  4   AND y.inst_id = USERENV ('Instance')

  5   AND x.indx = y.indx

  6  AND x.ksppinm LIKE '%_column_tracking_level%';

 

NAME                      VALUE      DESCRIB

------------------------- ---------- -------------------------

_column_tracking_level    1          column usage tracking

 

SQL> alter session set "_column_tracking_level"=0 ;

Session altered.

 

SQL> alter system set "_column_tracking_level"=0 scope=both;

System altered.