oracle系统视图更新,ORACLE8I升级11G R2后，查询系统视图特别慢

比如打开PB SELECT TABLE窗口，选择某个表打开，很缓慢，PB9要20秒。跟踪SQL语句：

SELECT F.CONSTRAINT_NAME,

F.OWNER,

F.R_OWNER,

P.TABLE_NAME,

SYS.ALL_CONS_COLUMNS.COLUMN_NAME,

F.DELETE_RULE

FROM SYS.ALL_CONSTRAINTS F,

SYS.ALL_CONS_COLUMNS,

SYS.ALL_CONSTRAINTS P

WHERE F.OWNER = 'MEDADM' AND

F.TABLE_NAME = 'ACCI_REASON_DICT' AND

F.CONSTRAINT_TYPE ='R' AND

SYS.ALL_CONS_COLUMNS.CONSTRAINT_NAME = F.CONSTRAINT_NAME AND

SYS.ALL_CONS_COLUMNS.TABLE_NAME = 'ACCI_REASON_DICT' AND

SYS.ALL_CONS_COLUMNS.OWNER = 'MEDADM' AND

P.OWNER = F.R_OWNER AND

P.CONSTRAINT_NAME = F.R_CONSTRAINT_NAME

ORDER BY F.CONSTRAINT_NAME,

SYS.ALL_CONS_COLUMNS.POSITION

优化视图:     添加/*+RULE*/没有屁用。更改内存参数和选择优化模式都没有用。重做统计信息也没有用。估计和我为了兼容老程序，选择了优化模式RULE有关。

将10G R2下同名视图SYS.ALL_CONSTRAINTS ，语句如下：

select ou.name, oc.name,

decode(c.type#, 1, 'C', 2, 'P', 3, 'U',

4, 'R', 5, 'V', 6, 'O', 7,'C', '?'),

o.name, c.condition, ru.name, rc.name,

decode(c.type#, 4,

decode(c.refact, 1, 'CASCADE', 2, 'SET NULL', 'NO ACTION'),

NULL),

decode(c.type#, 5, 'ENABLED',

decode(c.enabled, NULL, 'DISABLED', 'ENABLED')),

decode(bitand(c.defer, 1), 1, 'DEFERRABLE', 'NOT DEFERRABLE'),

decode(bitand(c.defer, 2), 2, 'DEFERRED', 'IMMEDIATE'),

decode(bitand(c.defer, 4), 4, 'VALIDATED', 'NOT VALIDATED'),

decode(bitand(c.defer, 8), 8, 'GENERATED NAME', 'USER NAME'),

decode(bitand(c.defer,16),16, 'BAD', null),

decode(bitand(c.defer,32),32, 'RELY', null),

c.mtime,

decode(c.type#, 2, ui.name, 3, ui.name, null),

decode(c.type#, 2, oi.name, 3, oi.name, null),

decode(bitand(c.defer, 256), 256,

decode(c.type#, 4,

case when (bitand(c.defer, 128) = 128

or o.status in (3, 5)

or ro.status in (3, 5)) then 'INVALID'

else null end,

case when (bitand(c.defer, 128) = 128

or o.status in (3, 5)) then 'INVALID'

else null end

),

null),

decode(bitand(c.defer, 256), 256, 'DEPEND ON VIEW', null)

from sys.con$ oc, sys.con$ rc, sys.user$ ou, sys.user$ ru, sys.obj$ ro,

sys.obj$ o, sys.cdef$ c, sys.obj$ oi, sys.user$ ui

where oc.owner# = ou.user#

and oc.con# = c.con#

and c.obj# = o.obj#

and c.type# != 8

and c.type# != 12       /* don't include log groups */

and c.rcon# = rc.con#(+)

and c.enabled = oi.obj#(+)

and oi.obj# = ui.user#(+)

and rc.owner# = ru.user#(+)

and c.robj# = ro.obj#(+)

and (o.owner# = userenv('SCHEMAID')

or o.obj# in (select obj#

from sys.objauth$

where grantee# in ( select kzsrorol

from x$kzsro

)

)

or /* user has system privileges */

exists (select null from v$enabledprivs

where priv_number in (-45 /* LOCK ANY TABLE */,

-47 /* SELECT ANY TABLE */,

-48 /* INSERT ANY TABLE */,

-49 /* UPDATE ANY TABLE */,

-50 /* DELETE ANY TABLE */)

)

)

复制后替换11G R2下同名视图，11G 下视图语句：

select ou.name, oc.name,

decode(c.type#, 1, 'C', 2, 'P', 3, 'U',

4, 'R', 5, 'V', 6, 'O', 7,'C', 8, 'H', 9, 'F',

10, 'F', 11, 'F', 13, 'F', '?'),

o.name, c.condition, ru.name, rc.name,

decode(c.type#, 4,

decode(c.refact, 1, 'CASCADE', 2, 'SET NULL', 'NO ACTION'),

NULL),

decode(c.type#, 5, 'ENABLED',

decode(c.enabled, NULL, 'DISABLED', 'ENABLED')),

decode(bitand(c.defer, 1), 1, 'DEFERRABLE', 'NOT DEFERRABLE'),

decode(bitand(c.defer, 2), 2, 'DEFERRED', 'IMMEDIATE'),

decode(bitand(c.defer, 4), 4, 'VALIDATED', 'NOT VALIDATED'),

decode(bitand(c.defer, 8), 8, 'GENERATED NAME', 'USER NAME'),

decode(bitand(c.defer,16),16, 'BAD', null),

decode(bitand(c.defer,32),32, 'RELY', null),

c.mtime,

decode(c.type#, 2, ui.name, 3, ui.name, null),

decode(c.type#, 2, oi.name, 3, oi.name, null),

decode(bitand(c.defer, 256), 256,

decode(c.type#, 4,

case when (bitand(c.defer, 128) = 128

or o.status in (3, 5)

or ro.status in (3, 5)) then 'INVALID'

else null end,

case when (bitand(c.defer, 128) = 128

or o.status in (3, 5)) then 'INVALID'

else null end

),

null),

decode(bitand(c.defer, 256), 256, 'DEPEND ON VIEW', null)

from sys.con$ oc, sys.con$ rc, sys."_BASE_USER" ou, sys."_BASE_USER" ru,

sys."_CURRENT_EDITION_OBJ" ro, sys."_CURRENT_EDITION_OBJ" o, sys.cdef$ c,

sys.obj$ oi, sys.user$ ui

where oc.owner# = ou.user#

and oc.con# = c.con#

and c.obj# = o.obj#

and c.type# != 8

and (c.type# < 14 or c.type# > 17)    /* don't include supplog cons   */

and (c.type# != 12)                   /* don't include log group cons */

and c.rcon# = rc.con#(+)

and c.enabled = oi.obj#(+)

and oi.obj# = ui.user#(+)

and rc.owner# = ru.user#(+)

and c.robj# = ro.obj#(+)

and (o.owner# = userenv('SCHEMAID')

or o.obj# in (select obj#

from sys.objauth$

where grantee# in ( select kzsrorol

from x$kzsro

)

)

or /* user has system privileges */

exists (select null from v$enabledprivs

where priv_number in (-45 /* LOCK ANY TABLE */,

-47 /* SELECT ANY TABLE */,

-48 /* INSERT ANY TABLE */,

-49 /* UPDATE ANY TABLE */,

-50 /* DELETE ANY TABLE */)

)

)

替换重新便宜，再到PB9操作，速度飞快。

不知道有什么不良后果不？呵呵