Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1146208
  • 博文数量: 178
  • 博客积分: 2776
  • 博客等级: 少校
  • 技术积分: 2809
  • 用 户 组: 普通用户
  • 注册时间: 2012-03-22 15:36
文章分类

全部博文(178)

文章存档

2014年(3)

2013年(66)

2012年(109)

分类: Oracle

2012-12-28 09:19:52

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 instanceSMON会着手清理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认证 针对SMON10046 level 12 trace

 

SQL> oradebug setospid 30225;

Oracle pid: 8, Unix process pid: 30225, image: oracle@rh2.oracle.com (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,该参数可以在sessionsystem级别动态修改:

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.

阅读(2349) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~