oracle性能优化-共享池调整

    一 . 简介 : 数据库调整中最为重要的一部分是重写运行效率差的 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 模式中是相等，可以使用缓存的已分析代码 .

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/79499/viewspace-341794/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/79499/viewspace-341794/