ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 10g ALL_SYNONYMS同义词查询性能下降

10g ALL_SYNONYMS同义词查询性能下降

原创 Linux操作系统 作者:yangtingkun 时间:2011-07-03 23:59:13 0 删除 编辑

10g中,由于解决了9I ALL_SYNONYMSbug,导致查询ALL_SYNONYMS性能下降。

9.2ALL_SYNONYMS中缺少指向同义词的同义词:http://yangtingkun.itpub.net/post/468/520089

 

 

上一篇介绍了9i中,如果创建一个指向同义词的同义词,则在ALL_SYNONYMS视图中可能看不到这个同义词。10g中则解决了这个问题。可以看到,9i10gALL_SYNONYMS视图的定义发生了变化:

SQL> select dbms_metadata.get_ddl('VIEW', 'ALL_SYNONYMS') from dual;

DBMS_METADATA.GET_DDL('VIEW','ALL_SYNONYMS')
--------------------------------------------------------------------------------

CREATE OR REPLACE FORCE VIEW "SYS"."ALL_SYNONYMS" ("OWNER", "SYNONYM_NAME", "TABLE_OWNER", "TABLE_NAME", "DB_LINK") A
S
select u.name, o.name, s.owner, s.name, s.node
from sys.user$ u, sys.syn$ s, sys.obj$ o
where o.obj# = s.obj#
and o.type# = 5
and o.owner# = u.user#
and (
o.owner# in (USERENV('SCHEMAID'), 1 /* PUBLIC */) /* user's private, any public */
or /* user has any privs on base object */
exists
(select null from sys.objauth$ ba, sys.obj$ bo, sys.user$ bu
where bu.name = s.owner
and bo.name = s.name
and bu.user# = bo.owner#
and ba.obj# = bo.obj#
and ( ba.grantee# in (select kzsrorol from x$kzsro)
or ba.grantor# = USERENV('SCHEMAID')
)
)
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 */)
)
)

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

10g9i的基础上添加了新的部分:

SQL> select dbms_metadata.get_ddl('VIEW', 'ALL_SYNONYMS') from dual;

DBMS_METADATA.GET_DDL('VIEW','ALL_SYNONYMS')
--------------------------------------------------------------------------------

CREATE OR REPLACE FORCE VIEW "SYS"."ALL_SYNONYMS" ("OWNER", "SYNONYM_NAME", "TABLE_OWNER", "TABLE_NAME", "DB_LINK") A
S
select u.name, o.name, s.owner, s.name, s.node
from sys.user$ u, sys.syn$ s, sys.obj$ o
where o.obj# = s.obj#
and o.type# = 5
and o.owner# = u.user#
and (
o.owner# in (USERENV('SCHEMAID'), 1 /* PUBLIC */) /* user's private, any public */
or /* user has any privs on base object in local database */
exists
(select null
from sys.objauth$ ba, sys.obj$ bo, sys.user$ bu
where s.node is null /* don't know accessibility if syn for db link */
and bu.name = s.owner
and bo.name = s.name
and bu.user# = bo.owner#
and ba.obj# = bo.obj#
and ( ba.grantee# in (select kzsrorol from x$kzsro)
or ba.grantor# = USERENV('SCHEMAID')
)
)
or /* local object, and user has system privileges */
(s.node is null /* don't know accessibility if syn is for db link */
and 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 */)
)
)
)
union
select u.name, o.name, s.owner, s.name, s.node
from sys.user$ u, sys.syn$ s, sys.obj$ o, sys."_ALL_SYNONYMS_TREE" st
where o.obj# = s.obj#
and o.type# = 5
and o.owner# = u.user#
and o.obj# = st.syn_id /* syn is in tree pointing to accessible base obj */
and s.obj# = st.syn_id /* syn is in tree pointing to accessible base obj */

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

正是增加的这部分UNION语句,解决了9ibug的同时,也带来了性能影响。

问题是不仅仅是用户访问ALL_SYNONYMS同义词变慢,而很多工具会导致系统递归调用中访问ALL_SYNONYMS视图,从而导致系统整体性能下降。

对于用户访问这个视图的性能下降,最简单的办法是仿照9i的语法,在当前用户建立一个视图,比如ALL_SYNONYMS_V92,然后创建一个当前用户下的同义词ALL_SYNONYMS指向当前用户的ALL_SYNONYMS_V92视图。由于用户下的同义词优先级高于PUBLIC SYNONYMS,用户在访问的时候就会访问用户下的ALL_SYNONYMS_V92视图,不过9i中指向同义词的同义词就可能在ALL_SYNONYMS中无法访问。

不过这种方式对于一些系统调用或者工具产生的调用无效,因为这些SQL可能访问的并不是PUBLIC同义词,而是SYS.ALL_SYNONYMS对象,这就导致了用户创建的同义词无效。如果是这种情况,那么为了解决性能问题,只要仿照9i的语法重建10gALL_SYNONYMS视图,同样9i中的bug就会在10g中再次出现。

Oracle在文档ID 377037.1详细描述了这个问题。看来有的时候正确性和性能很难完全兼顾。

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-701245/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10524289