以前写过一篇文章关于《PL/SQL Developer工具优化》 提到过在Oracle 11.2.0.3.0 中
现在可以通过重建ALL_SYNONYMS彻底解决这个问题了
ALL_SYNONYMS视图的创建脚本位于$ORACLE_HOME/rdbms/admin/cdcore.sql
修正后的ALL_SYNONYMS创建脚本:
参考【MOS】[ID 1242844.1]
Selects Against ALL_SYNONYMS Very Slow in 11.2.0.1. and 11.2.0.2. [ID 1242844.1]
对all_synonyms的访问会导致PL/SQL Developer变得非常慢,观察oracle主机的cpu会
发现有两颗cpu使用率到100%,退出PL/SQL Developer后,cpu恢复正常。
详见:http://blog.csdn.net/lwei_998/article/details/7038001
现在可以通过重建ALL_SYNONYMS彻底解决这个问题了
ALL_SYNONYMS视图的创建脚本位于$ORACLE_HOME/rdbms/admin/cdcore.sql
修正后的ALL_SYNONYMS创建脚本:
create or replace view ALL_SYNONYMS
(OWNER, SYNONYM_NAME, TABLE_OWNER, TABLE_NAME, DB_LINK)
as
SELECT U.NAME, O.NAME, S.OWNER, S.NAME, S.NODE
FROM SYS.USER$ U, SYS.SYN$ S, SYS."_CURRENT_EDITION_OBJ" O
WHERE O.OBJ# = S.OBJ#
AND O.TYPE# = 5
AND O.OWNER# = U.USER#
AND (O.OWNER# IN (USERENV('SCHEMAID'), 1 /* PUBLIC */
) 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 */
))) OR /* user has any privs on base object in local database */
EXISTS
(SELECT NULL
FROM SYS.OBJAUTH$ BA, SYS."_CURRENT_EDITION_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'))))
UNION
SELECT U.NAME, O.NAME, S.OWNER, S.NAME, S.NODE
FROM SYS.USER$ U,
SYS.SYN$ S,
SYS."_CURRENT_EDITION_OBJ" O,
(SELECT S.SYN_ID
FROM (SELECT S.OBJ# SYN_ID, BO.OBJ# BASE_SYN_ID
FROM SYS.SYN$ S,
SYS."_CURRENT_EDITION_OBJ" BO,
SYS.USER$ BU
WHERE S.OWNER = BU.NAME
AND BU.USER# = BO.OWNER#
AND S.NAME = BO.NAME
AND BO.TYPE# = 5) S
START WITH EXISTS (SELECT NULL
FROM SYS."_ALL_SYNONYMS_FOR_AUTH_OBJECTS" SA
WHERE S.BASE_SYN_ID = SA.SYN_ID)
CONNECT BY NOCYCLE PRIOR S.SYN_ID = S.BASE_SYN_ID) ST
WHERE O.OBJ# = S.OBJ#
AND O.TYPE# = 5
AND O.OWNER# = U.USER#
AND O.OBJ# = ST.SYN_ID
AND S.OBJ# = ST.SYN_ID
参考【MOS】[ID 1242844.1]
Selects Against ALL_SYNONYMS Very Slow in 11.2.0.1. and 11.2.0.2. [ID 1242844.1]