关闭

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

1130人阅读 评论(0) 收藏 举报
一个朋友生产库缓慢要帮忙看下一个数据库 数据库版本是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加大 调整资源利用率。

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:29722次
    • 积分:627
    • 等级:
    • 排名:千里之外
    • 原创:33篇
    • 转载:2篇
    • 译文:1篇
    • 评论:1条
    最新评论