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

全部博文(178)

文章存档

2014年(3)

2013年(66)

2012年(109)

分类: Oracle

2012-12-12 13:14:52

  oracle视频教程下载COUNT(*)
  ----------
  10224
  针对SMON做10046 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$字典基表
  1.设置隐藏参数_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.
  2.关闭DML monitoring,可以通过设置隐藏参数_dml_monitoring_enabled(enable modification monitoring)为false实现,disable dml monitoring对CBO的影响较大,所以我们一般推荐上一种方式:
  SQL> SELECT monitoring, count(*) from DBA_TABLES group by monitoring;
  MON COUNT(*)
  --- ----------
  NO 79
  YES 2206
  SQL> alter system set "_dml_monitoring_enabled"=false;
  System altered.
  SQL> SELECT monitoring, count(*) from DBA_TABLES group by monitoring;
  MON COUNT(*)
  --- ----------
  NO 2285
阅读(1053) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~