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

全部博文(178)

文章存档

2014年(3)

2013年(66)

2012年(109)

分类: Oracle

2012-12-31 09:31:51

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

System altered.

 

关闭DML monitoring,可以通过设置隐藏参数_dml_monitoring_enabled(enable modification monitoring)false实现,disable dml monitoringCBO的影响较大,所以我们一般推荐上一种方式:

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

 

实际上dba_tablesmonitoring列来源于内部参数_dml_monitoring_enabled

 

SQL> set long 99999

 

SQL> select text from dba_views where view_name='DBA_TABLES';

 

TEXT

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

select u.name, o.name, decode(bitand(t.property,2151678048), 0, ts.name, null),

       decode(bitand(t.property, 1024), 0, null, co.name),

       decode((bitand(t.property, 512)+bitand(t.flags, 536870912)),

              0, null, co.name),

       decode(bitand(t.trigflag, 1073741824), 1073741824, 'UNUSABLE', 'VALID'),

       decode(bitand(t.property, 32+64), 0, mod(t.pctfree$, 100), 64, 0, null),

       decode(bitand(ts.flags, 32), 32, to_number(NULL),

          decode(bitand(t.property, 32+64), 0, t.pctused$, 64, 0, null)),

       decode(bitand(t.property, 32), 0, t.initrans, null),

       decode(bitand(t.property, 32), 0, t.maxtrans, null),

       s.iniexts * ts.blocksize,

       decode(bitand(ts.flags, 3), 1, to_number(NULL),

                                      s.extsize * ts.blocksize),

       s.minexts, s.maxexts,

       decode(bitand(ts.flags, 3), 1, to_number(NULL),

                                      s.extpct),

       decode(bitand(ts.flags, 32), 32, to_number(NULL),

         decode(bitand(o.flags, 2), 2, 1, decode(s.lists, 0, 1, s.lists))),

       decode(bitand(ts.flags, 32), 32, to_number(NULL),

         decode(bitand(o.flags, 2), 2, 1, decode(s.groups, 0, 1, s.groups))),

       decode(bitand(t.property, 32+64), 0,

                decode(bitand(t.flags, 32), 0, 'YES', 'NO'), null),

       decode(bitand(t.flags,1), 0, 'Y', 1, 'N', '?'),

       t.rowcnt,

       decode(bitand(t.property, 64), 0, t.blkcnt, null),

       decode(bitand(t.property, 64), 0, t.empcnt, null),

       t.avgspc, t.chncnt, t.avgrln, t.avgspc_flb,

       decode(bitand(t.property, 64), 0, t.flbcnt, null),

       lpad(decode(t.degree, 32767, 'DEFAULT', nvl(t.degree,1)),10),

       lpad(decode(t.instances, 32767, 'DEFAULT', nvl(t.instances,1)),10),

       lpad(decode(bitand(t.flags, 8), 8, 'Y', 'N'),5),

       decode(bitand(t.flags, 6), 0, 'ENABLED', 'DISABLED'),

       t.samplesize, t.analyzetime,

       decode(bitand(t.property, 32), 32, 'YES', 'NO'),

       decode(bitand(t.property, 64), 64, 'IOT',

               decode(bitand(t.property, 512), 512, 'IOT_OVERFLOW',

               decode(bitand(t.flags, 536870912), 536870912, 'IOT_MAPPING', null

))),

       decode(bitand(o.flags, 2), 0, 'N', 2, 'Y', 'N'),

       decode(bitand(o.flags, 16), 0, 'N', 16, 'Y', 'N'),

       decode(bitand(t.property, 8192), 8192, 'YES',

              decode(bitand(t.property, 1), 0, 'NO', 'YES')),

       decode(bitand(o.flags, 2), 2, 'DEFAULT',

             decode(s.cachehint, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL)),

       decode(bitand(t.flags, 131072), 131072, 'ENABLED', 'DISABLED'),

       decode(bitand(t.flags, 512), 0, 'NO', 'YES'),

       decode(bitand(t.flags, 256), 0, 'NO', 'YES'),

       decode(bitand(o.flags, 2), 0, NULL,

          decode(bitand(t.property, 8388608), 8388608,

                 'SYS$SESSION', 'SYS$TRANSACTION')),

       decode(bitand(t.flags, 1024), 1024, 'ENABLED', 'DISABLED'),

       decode(bitand(o.flags, 2), 2, 'NO',

           decode(bitand(t.property, 2147483648), 2147483648, 'NO',

              decode(ksppcv.ksppstvl, 'TRUE', 'YES', 'NO'))),

       decode(bitand(t.property, 1024), 0, null, cu.name),

       decode(bitand(t.flags, 8388608), 8388608, 'ENABLED', 'DISABLED'),

       decode(bitand(t.property, 32), 32, null,

                decode(bitand(s.spare1, 2048), 2048, 'ENABLED', 'DISABLED')),

       decode(bitand(o.flags, 128), 128, 'YES', 'NO')

from sys.user$ u, sys.ts$ ts, sys.seg$ s, sys.obj$ co, sys.tab$ t, sys.obj$ o,

     sys.obj$ cx, sys.user$ cu, x$ksppcv ksppcv, x$ksppi ksppi

where o.owner# = u.user#

  and o.obj# = t.obj#

  and bitand(t.property, 1) = 0

  and bitand(o.flags, 128) = 0

  and t.bobj# = co.obj# (+)

  and t.ts# = ts.ts#

  and t.file# = s.file# (+)

  and t.block# = s.block# (+)

  and t.ts# = s.ts# (+)

  and t.dataobj# = cx.obj# (+)

  and cx.owner# = cu.user# (+)

  and ksppi.indx = ksppcv.indx

  and ksppi.ksppinm = '_dml_monitoring_enabled' oracle视频http://www.cuug.com/

 

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