oracle 11 cpu 100,ORACLE11.2.0.3中访问ALL_SYNONYMS导致ORACLE CPU100%

以前写过一篇文章关于《PL/SQL Developer工具优化》 提到过在Oracle 11.2.0.3.0 中

对all_synonyms的访问会导致PL/SQL Developer变得非常慢，观察oracle主机的cpu会

发现有两颗cpu使用率到100%，退出PL/SQL Developer后，cpu恢复正常。

详见：http://blog.csdn.net/lwei_998/article/details/7038001

通过禁用PL/SQL Developer的“Describe context”功能，临时解决了这个问题。

现在可以通过重建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]