ORACLE 11G下ALL_SYNONYMS对系统系能影响调优

bgvf

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

生成AWR报告显示

DB Name	DB Id	Instance	Inst num	Startup Time	Release	RAC
SAPP	1862119142	sapp	1	02-8月 -13 10:08	11.1.0.7.0	NO

Host Name	Platform	CPUs	Cores	Sockets	Memory (GB)
WINDOWS-IP3EJ67	Microsoft Windows x86 64-bit	4	4	1	7.99

	Snap Id	Snap Time	Sessions	Cursors/Session
Begin Snap:	16411	27-11月-13 15:00:18	108	6.9
End Snap:	16412	27-11月-13 15:44:30	117	8.0
Elapsed:		44.21 (mins)
DB Time:		672.21 (mins)

Report Summary

Cache Sizes

	Begin	End
Buffer Cache:	784M	784M	Std Block Size:	8K
Shared Pool Size:	1,264M	1,264M	Log Buffer:	15,676K

Load Profile

	Per Second	Per Transaction	Per Exec	Per Call
DB Time(s):	15.2	8.8	0.20	0.06
DB CPU(s):	3.8	2.2	0.05	0.01
Redo size:	3,518.4	2,024.6
Logical reads:	214,807.7	123,610.7
Block changes:	33.8	19.4
Physical reads:	46.9	27.0
Physical writes:	1.5	0.8
User calls:	260.9	150.1
Parses:	87.9	50.6
Hard parses:	2.5	1.4
W/A MB processed:	284,578,472.9	163,760,157.5
Logons:	0.2	0.1
Executes:	77.4	44.5
Rollbacks:	0.0	0.0
Transactions:	1.7

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %:	100.00	Redo NoWait %:	100.00
Buffer Hit %:	100.00	In-memory Sort %:	100.00
Library Hit %:	95.14	Soft Parse %:	97.18
Execute to Parse %:	-13.67	Latch Hit %:	99.58
Parse CPU to Parse Elapsd %:	0.00	% Non-Parse CPU:	99.41

Shared Pool Statistics

	Begin	End
Memory Usage %:	89.21	90.63
% SQL with executions>1:	84.46	73.29
% Memory for SQL w/exec>1:	87.03	81.13

Top 5 Timed Foreground Events

Event	Waits	Time(s)	Avg wait (ms)	% DB time	Wait Class
DB CPU		10,087		25.01
direct path read	65,262	6,869	105	17.03	User I/O
log file sync	5,233	369	71	0.92	Commit
latch: cache buffers chains	587	218	371	0.54	Concurrency
enq: TX - row lock contention	9	69	7631	0.17	Application

TOP SQL

Elapsed Time (s)	Executions	Elapsed Time per Exec (s)	%Total	%CPU	%IO	SQL Id	SQL Module	SQL Text
20,520.13	137,160	0.15	39.51	80.90	0.00	ac4n7xg9v9p6u	w3wp.exe	select table_owner, table_name...
18,947.22	126,752	0.15	36.49	81.01	0.00	bcf1hsvhg50mw	w3wp.exe	select table_owner, table_name...
6,182.35	294,984	0.02	11.91	96.92	0.00	gcfyqusdngbv4	PublishService.exe	select ac.constraint_name key_...
987.33	6,065	0.16	1.90	76.42	0.00	44fhq3rpmuqjm	w3wp.exe	select table_owner, table_name...

一

很多类似 第一个SQL
select table_owner, table_name from all_synonyms where owner in ('PUBLIC', user) and synonym_name = 'TAPP_DAY' order by decode(owner, 'PUBLIC', 2, 1)；
这样的查询语句。


还有  第二个SQL
select 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加大 调整资源利用率。

半只蚂蚁

好想与你牵手走在路上即使疲惫也不愿停下直到窒息