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

原创 2013年12月05日 15:26:26
一个朋友生产库缓慢要帮忙看下一个数据库 数据库版本是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加大 调整资源利用率。

ORACLE11.2.0.3中访问ALL_SYNONYMS导致ORACLE CPU100%

以前写过一篇文章关于《PL/SQL Developer工具优化》 提到过在Oracle 11.2.0.3.0 中 对all_synonyms的访问会导致PL/SQL Developer变得非常慢...
  • lwei_998
  • lwei_998
  • 2012年03月27日 16:09
  • 1801

静默安装oracle 11g及参数配置优化详解

书本及网上的很多知识较零散,或者很多参数解释的不是很清楚,这里自己通过基础实验归纳总结了一番,实际上是Oracle DBA的初学者希望不足之处能够指出!   一、安装前准备工作 1、修改主机名 #vi...
  • ZoneJ
  • ZoneJ
  • 2016年02月17日 15:44
  • 10422

Oracle性能调优自己总结的18条经验

Oracle性能调优自己总结的18条经验
  • zmycoco2
  • zmycoco2
  • 2014年03月14日 08:36
  • 2713

Oracle性能优化图文详解——利用第三方工具

开发中或者是正在运行的系统性能显著恶化的场合,需要进行性能优化。当听到性能优化时,有些人可能会感觉到非常困难,如果使用OB的话,通过使用索引或者内存等可以非常简单的进行性能优化。这篇文章将要介绍怎样使...
  • xiaohaiyaoer
  • xiaohaiyaoer
  • 2013年07月17日 16:20
  • 2367

SQL Tuning Advisor 使用11G的自动调优建议

ORACLE 提供了自动SQL优化的工具,当对DBA来说 可以节约下思考的时间,或许提供更多的参考价值...
  • ZengMuAnSha
  • ZengMuAnSha
  • 2016年06月03日 11:40
  • 6073

oracle笔记整理14——性能调优之oracle执行计划

1) 优化器(optimizer) a) RBO(rule-based optimizer)方式:基于规划的优化方式 所遵循的是oracle内部预定的一些规则. b) CBO(cost-base...
  • thinkpadshi
  • thinkpadshi
  • 2016年01月16日 22:59
  • 630

Oracle SQL 自动调优

Oracle SQL 自动调优Oracle 11版本之后,Oracle支持自动调优,默认开启自动调优任务,每天执行一次,可以查看生成的调优建议。本文的SQL语句基于 11.2.0.1.0版本,不保证在...
  • wwlhz
  • wwlhz
  • 2017年04月14日 13:19
  • 452

【性能优化】 之 RAC架构性能优化

1.演示通过设置不同的服务,达到RAC业务分割的效果。 2.对比将并行操作放在RAC多个节点执行和单个节点执行的效率。 3.演示RAC的cache fusion对数据块访问效率的影响。 4.写出...
  • miyatang
  • miyatang
  • 2014年01月23日 14:03
  • 3901

oracle11g 一条几百行行的sql语句 优化 需要适可而止才行

1,同事报告了一条非常复杂的sql,说很慢,叫我优化下。很慢的sql,也很长有几百行吧,^_^,如下: select bm2.bis_must_id,        bm2.qz_year_m...
  • mchdba
  • mchdba
  • 2016年06月06日 20:10
  • 4782

Oracle 11g体系结构

详细介绍Oracle 11g体系结构。
  • Fortyone41
  • Fortyone41
  • 2017年01月10日 16:32
  • 964
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:ORACLE 11G下ALL_SYNONYMS对系统系能影响调优
举报原因:
原因补充:

(最多只允许输入30个字)