oracle下的synonyms,ORACLE 11G下ALL_SYNONYMS对系统系能影响调优

一个朋友生产库缓慢要帮忙看下一个数据库 数据库版本是11.2.0.1.0

生成AWR报告显示

DB NameDB IdInstanceInst numStartup TimeReleaseRAC

SAPP1862119142sapp102-8月 -13 10:0811.1.0.7.0NO

Host NamePlatformCPUsCoresSocketsMemory (GB)

WINDOWS-IP3EJ67Microsoft Windows x86 64-bit4417.99

Snap IdSnap TimeSessionsCursors/Session

Begin Snap:1641127-11月-13 15:00:181086.9

End Snap:1641227-11月-13 15:44:301178.0

Elapsed:44.21 (mins)

DB Time:672.21 (mins)

Report Summary

Cache Sizes

BeginEnd

Buffer Cache:784M784MStd Block Size:8K

Shared Pool Size:1,264M1,264MLog Buffer:15,676K

Load Profile

Per SecondPer TransactionPer ExecPer Call

DB Time(s):15.28.80.200.06

DB CPU(s):3.82.20.050.01

Redo size:3,518.42,024.6

Logical reads:214,807.7123,610.7

Block changes:33.819.4

Physical reads:46.927.0

Physical writes:1.50.8

User calls:260.9150.1

Parses:87.950.6

Hard parses:2.51.4

W/A MB processed:284,578,472.9163,760,157.5

Logons:0.20.1

Executes:77.444.5

Rollbacks:0.00.0

Transactions:1.7

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %:100.00Redo NoWait %:100.00

Buffer Hit %:100.00In-memory Sort %:100.00

Library Hit %:95.14Soft Parse %:97.18

Execute to Parse %:-13.67Latch Hit %:99.58

Parse CPU to Parse Elapsd %:0.00% Non-Parse CPU:99.41

Shared Pool Statistics

BeginEnd

Memory Usage %:89.2190.63

% SQL with executions>1:84.4673.29

% Memory for SQL w/exec>1:87.0381.13

Top 5 Timed Foreground Events

EventWaitsTime(s)Avg wait (ms)% DB timeWait Class

DB CPU10,08725.01

direct path read65,2626,86910517.03User I/O

log file sync5,233369710.92Commit

latch: cache buffers chains5872183710.54Concurrency

enq: TX - row lock contention96976310.17Application

TOP SQL

Elapsed Time (s)ExecutionsElapsed Time per Exec (s)%Total%CPU%IOSQL IdSQL ModuleSQL Text

20,520.13137,1600.1539.5180.900.00w3wp.exeselect table_owner, table_name...

18,947.22126,7520.1536.4981.010.00w3wp.exeselect table_owner, table_name...

6,182.35294,9840.0211.9196.920.00PublishService.exeselect ac.constraint_name key_...

987.336,0650.161.9076.420.00w3wp.exeselect table_owner, table_name...

一很多类似 第一个SQLselect table_owner, table_name from all_synonyms where owner in ('PUBLIC', user) and synonym_name = 'TAPP_DAY' order by decode(owner, 'PUBLIC', 2, 1)；

这样的查询语句。

还有  第二个SQLselect ac.constraint_name key_name, acc.column_name key_col, 1 from all_cons_columns acc, all_constraints ac where acc.owner = ac.owner and acc.constraint_name = ac.constraint_name

and acc.table_name = ac.table_name and ac.constraint_type = 'P' and ac.owner = user and ac.table_name = :TableName order by acc.constraint_name；

第一个SQL 全部是WEB前段调用的，问题就是出在视图上面

查询该语句执行计划

167  recursive calls

0  db block gets

356767  consistent gets

0  physical reads

0  redo size

632  bytes sent via SQL*Net to client

514  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

4  sorts (memory)

0  sorts (disk)

1  rows processed

发现有递归调用比较多，而且执行次数非常多，难怪对系统影响很大。

尝试通过重建ALL_SYNONYMS，我们可以先把ALL_SYNONYMS这个视图备份改成ALL_SYNONYMS_OLD

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

改完之后 CPU立刻降下来了。

二

第二个SQL 涉及到 这边的一个程序发布服务 该服务是.net写的

通过问开发 开发说这是发布扫描这块 只对指令表 进行扫描。每秒快有5000次的扫描，

通过协商修改下这个服务。

三

发现服务器内存有8G 还有可利用内存的空间 Library Hit %:命中率为 95%  Memory Usage %:89.21   90.63

故把SGA加大 调整资源利用率。