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
阅读(1051) | 评论(0) | 转发(0) |