Shared Pool(共享池)

set echo off
spool pool_est
 
set serveroutput on;
 
declare
        object_mem number;
        shared_sql number;
        cursor_mem number;
        mts_mem number;
        used_pool_size number;
        free_mem number;
        pool_size varchar2(512); -- same as V$PARAMETER.VALUE
begin
 
-- 数据库存储的对象(packages, views)
select sum(sharable_mem) into object_mem from v$db_object_cache;
 
-- 共享SQL -- need to have additional memory if dynamic SQL used
select sum(sharable_mem) into shared_sql from v$sqlarea;
 
-- 游标使用内存 assumes 250 bytes per open cursor, for each concurrent user.
select sum(250*users_opening) into cursor_mem from v$sqlarea;
 
 
-- 多线程环境
select sum(value) into mts_mem from v$sesstat s, v$statname n
       where s.statistic#=n.statistic#
       and n.name='session uga memory max';
 
-- 空余内存
select bytes into free_mem from v$sgastat
        where name = 'free memory'
        and pool = 'shared pool';
 
-- 使用内存(非多线程环境)
used_pool_size := round(1.2*(object_mem+shared_sql+cursor_mem));
 
-- 使用内存(多线程环境)
--used_pool_size := round(1.2*(object_mem+shared_sql+cursor_mem+mts_mem));
-- Pre-9i or if using manual SGA management, issue
select value into pool_size from v$parameter where name='shared_pool_size';
 
-- 显示结果
dbms_output.put_line ('对象内存:  '||to_char (object_mem) || ' 字节');
dbms_output.put_line ('共享sql:  '||to_char (shared_sql) || ' 字节');
dbms_output.put_line ('游标:  '||to_char (cursor_mem) || ' 字节');
--dbms_output.put_line ('多线程会话: '||to_char (mts_mem) || ' 字节');
dbms_output.put_line ('空闲内存: '||to_char (free_mem) || ' 字节 ' || '('
|| to_char(round(free_mem/1024/1024,2)) || 'MB)');
dbms_output.put_line ('共享池预计使用:  '||
to_char(used_pool_size) || ' 字节 ' || '(' ||
to_char(round(used_pool_size/1024/1024,2)) || 'MB)');
dbms_output.put_line ('共享池实际大小:  '|| pool_size ||' 字节 ' || '(' || to_char(round
(pool_size/1024/1024,2)) || 'MB)');
dbms_output.put_line ('使用百分比:  '||to_char
(round(used_pool_size/pool_size*100)) || '%');
end;
/
 

///
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模式中是相等，可以使用缓存的已分析代码.