10g ALL_SYNONYMS同義詞查詢效能下降

在10g中，由於解決了9I ALL_SYNONYMS的bug，導致查詢ALL_SYNONYMS效能下降。

9.2上ALL_SYNONYMS中缺少指向同義詞的同義詞：http://yangtingkun.itpub.net/post/468/520089

上一篇介紹了9i中，如果建立一個指向同義詞的同義詞，則在ALL_SYNONYMS檢視中可能看不到這個同義詞。10g中則解決了這個問題。可以看到，9i和10g的ALL_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

而10g在9i的基礎上新增了新的部分：

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語句，解決了9i中bug的同時，也帶來了效能影響。

問題是不僅僅是使用者訪問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的語法重建10g的ALL_SYNONYMS檢視，同樣9i中的bug就會在10g中再次出現。

Oracle在文件ID 377037.1詳細描述了這個問題。看來有的時候正確性和效能很難完全兼顧。