oracle性能优化-共享池调整

矫情吗；* 2022-08-11 16:43 258阅读 0赞

一.简介:**数据库**调整中最为重要的一部分是重写运行效率差的**SQL**代码,重写之后的SQL代码在运行效率方面可能会与之前的产生极大的差别！  
      但是当遇到重写之后性能还是未能突破瓶颈或者你是用户(无法访问SQL代码)的时候,可以通过调整**ORACLE**的共享内存结构SGA(System Global Area),最大限度地提高性能！  
       SGA中比较重要的组件就是Shared Pool(共享池),它的作用就是高速缓存SQL语句!共享池由一个最近最少使用(LRU,Least Recently Used)算法来**管理**!  
      共享池的好处:  
1.select cust\_id,cust\_name from col\_cust  
2.上列语句的算法被转换成ASCII码,然后通过一个散列算法产生一个单独的散列值~~接着Process会查看该散列值在Shared Pool中是否存在,  
如果存在,就执行高速缓存中语句  
3.如果不存在,就必须对该语句进行语法分析,这些分析步骤会产生额外的系统开销,该操作是高代价的!  
4.查找到匹配的SQL叫做一次高速缓冲区命中(Cache Hit)  
5.反之叫做高速缓冲区脱靶(Cache Miss)  
6.注意是区分大小写的  
 SELECT CUST\_ID,CUST\_NAME FROM COL\_CUST   
   
      二.共享池有三个组件组成,Library Cache(库高速缓存区),Data Dictionary Cache(数据目录高速缓存区),User Global Area(用户全局区)  
      1.Library Cache(库高速缓存区)是用来缓存SQL语句的场所.可以通过下面这句话对动态视图进行查询,检查Library Cache的内容  
 select p.username,l.sql\_text,  
 lpad(' ' ,4\*(LEVEL-2)) || operation || ' ' || options || ' ' || object\_name as "Execution Plan"  
 from (  
 select s.username,p.address,p.hash\_value,p.operation,p.options,p.object\_name,p.id,p.parent\_id  
 from v$sql\_plan p,v$session s  
 where (p.address=s.sql\_address and p.hash\_value=s.sql\_hash\_value) and s.username='citictest'  
 ) p,v$sql l  
 where(l.address=p.address and l.hash\_value=p.hash\_value)  
 start with id=0  
 connect by prior id = parent\_id  
      2.Data Dictionary Cache(数据目录高速缓存区):数据目录是用来检查SQL语句所引用的那些表是否已经存在,列名和数据类型是否也正确! Library Cache和Data Dictionary Cache使用互相独立的LRU机制,好处是后续用户发布的语句与先前用户所发布的语句类似但不一致,虽然在Library Cache中无法找到匹配的,但是在数据目录中会存在,也会有性能上的提高。

三 .(1) 测量 Library Cache( 库高速缓存区 ) 的性能   
      select namespace,gethitratio,pinhitratio,reloads,invalidations  
       from v$librarycache  
       where namespace in ('SQL\_AREA','TABLE/PROCEDURE','BODY','TRIGGER');  
 主要看 gethitratio,pinhitratio>90% 说明调整充分

select sum(reloads)/sum(pins) "RELOAD 　 RATIO"  
       from v$librarycache  
 当 <1% 意味着不是经常重新语法分析以前被装载到 Library Cache 的语句   
   
       (2) 测量 Data Dictionary Cache( 数据目录高速缓存区 ) 的性能   
      select 1 - (sum(getmisses)/sum(gets)) "DATA DICTIONARY HIT RATIO"  
       from v$rowcache;  
 当 >85% 时说明调整充分

四 . 通过改进 Library Cache 和 Data Dictionary Cache 来提高 shared pool 性能   
(1). 得到当前 shared pool 的大小   
 select pool,sum(bytes) "SIZE" from v$sgastat where pool='shared pool' group by pool;  
(2). 得到推荐的 shared pool 大小   
  set echo off  
  set feedback off  
  set serveroutput on  
  declare  
  v\_total\_plsql\_mem number := 0;  
  v\_total\_sql\_mem   number := 0;  
  v\_total\_sharable\_mem number := 0;  
  begin  
       select sum(sharable\_mem) into v\_total\_plsql\_mem from v$db\_object\_cache;  
        
       select sum(sharable\_mem) into v\_total\_sql\_mem from v$sqlarea where executions > 10;  
        
       v\_total\_sharable\_mem := v\_total\_plsql\_mem + v\_total\_sql\_mem;  
        
       Dbms\_Output.put\_line('Estimated required shared pool size is:' || to\_char(v\_total\_sharable\_mem,'fm9,999,999,999,999') || ' bytes');  
  end;  
  /  
(3). 动态加大 shared pool 大小   
alert system set shared\_pool\_size = 200M;  
\* 大小不能超过 SGA\_MAX\_SIZE 的值   
(4). 初始 SGA 大小的计算   
(TSGA)SGA 总的大小 = 服务器物理内存 \*0.55   (1G 以上物理内存的话可以相应 60%-75%)  
(TSGAI) 每个实例的总 SGA 大小 =TSGA/oracle 上实例的个数   
shared pool 的总内存 =TSGAI\*0.45  
(5). 上述四点是其实完成的是同一个做法，就是使 Shared Pool 更大   
(6). 可以将 PL/SQL 程序包装入 Shared Pool Reserved Area( 共享池保留区 )  
Shared\_Pool\_Reserved\_Size 用来设置这一区域的大小，默认是 5% ，这是不够的   
select owner,name,sharable\_mem from v$db\_object\_cache  
where type in('PACKAGE','PACKAGE BODY') order by sharable\_mem desc;  
 上述这句语句可以查看当前缓存区中的 PL/SQL 程序包的名称和大小，当发现大小 > Shared\_Pool\_Reserved\_Size 时说明保留区的大小不够，你需要增加 Shared\_Pool\_Reserved\_Size 的值   
(7). 把重要的 PL/SQL 代码保持在内存中   
 你可以把常用的 PROCEDURE 销定 (Pinning) 在 Shared\_Pool\_Reserved\_Size 中   
 做法如下 :

a)sys 用户登录   
          b) 运行 @%ORACLE\_HOME%/rdbms/admin/dbmspool.sql  
          c)SQL>execute DBMS\_SHARED\_POOL.KEEP('PROCEDURENAME') ( 销定 , 必须用 sys 完成 )  
          d) 找到销定的对象 select owner,name,type from v$db\_object\_cache where kept='YES';  
          e) 销定对单独的 SQL 语句无法操作 , 尽可能的把大语句做成 PROCEDURE ，可以用一下语句寻找出比较大的语句   
select substr(sql\_text,1,45),length(sql\_text) "STMT\_SIZE" from v$sqlarea where command\_type=47 order by length(sql\_text) desc;  
          f) 可以编写一个脚本，当实例启动之后运行，把所有需要销定的语句执行一下   
          h) 只有使用 UNKEEP 或者实例关闭时才会取消销定   
(8). 其他的一些调整 Library Cache 参数   
a)open\_cursors : 默认 50  
b)cursor\_space\_for\_time 默认 false  
c)session\_cached\_cursors 默认 0( 无游标高速缓存 )  
d)cursor\_sharing 默认 EXACT --2 条 SQL 语句必须完全匹配才能共享 shared pool 中所缓存的已分析代码 .  
                 SIMILAR -- 允许 2 条仅在字面上不同的 SQL 语句共享 shared pool 中所缓存的已分析代码 .  
 例如 :select cust\_id from col\_cust where cust\_name = 'wang'  
     select cust\_id from col\_cust where cust\_name = 'huang'  
 上述两句在 SIMILAR 模式中是相等，可以使用缓存的已分析代码 .

转载： [http://space.itpub.net/79499/viewspace-341794][http_space.itpub.net_79499_viewspace-341794]

[http_space.itpub.net_79499_viewspace-341794]: http://space.itpub.net/79499/viewspace-341794