Share Pool

最新推荐文章于 2021-04-04 20:02:25 发布

congxin0545

最新推荐文章于 2021-04-04 20:02:25 发布

阅读量192

点赞数

文章标签：数据库 java 运维

#####################################################################################
Share Pool edit by sky on 20111111 参考:beatony及performance guid
#####################################################################################

1.Shared Pool相关概念:
共享池缓存对象：PL/SQL和SQL的文本和执行计划、字典缓存数据、结果缓存数据和其它数据
共享池的组成:库缓存、字典缓存、结果缓存(可配置)
库缓存:近来引用的SQL和PL/SQL代码的解析或编译版本以及JAVA类
字典缓存:从数据字典引用的数据，包括用户名、段信息、配置文件数据、表空间信息、序列号、关于模式对象的描述信息和元数据(元数据被用于解析SQL游标或编译PL/SQL程序)
结果缓存:查询和PL/SQL函数的结果
共享池的缓存缺失比数据块缓存缺失对性能的影响更为严重
合适大小的共享池的好处:避免解析从而节省了CPU资源、Latching资源使用降低带来了可扩展性、由于SQL语句和字典资源的共享，所以减少了内存的需求、减少了I/O(不需要磁盘访问)
共享服务、并行查询、RMAN这些功能的启用将占用大量的shared pool，所以可以分隔这些功能到large pool
共享池的内存分配是以大块，这将允许超过5K的大对象被加载到缓存中而不需要请求连续的内存区域，从而避免由于碎片而没有足够连续的内存
不常见的一些Java、PL/SQL、或SQL游标由于所需要的share pool内存空间大于5K,这些对象将被放到保留池(共享池被隔离的一部分)
软解析(库缓存命中):重用在库缓存中已经被执行并且能被共享的代码
硬解析(库缓存未命中):如果不能重用库缓存中已经存在的代码，那么库缓存中相关对象的新的执行版本将被建立，这个过程称之为硬解析
在处理SQL语句时库硬解析既可发生在解析阶段又可以发生在执行阶段
硬解析消耗的资源包括:额外的CPU、库缓存闩锁获取(library cache latch gets)和共享池闩锁获取(shared pool latch gets)
软件解析消耗的资料包括:CPU和库缓存闩锁获取(library cache latch gets)
SQL共享标准:
a.比较SQL语句的hash值以及SQL语句的文本(包括空格及大小写)，----仅当CURSOR_SHARING参数被设置为SIMILAR or FORCE时相似的文本才可以共享
b.比较使用的sql语句所引用的模式对象是否相同
c.sql语句的绑定变量应该在名字、数据类型、长度上匹配
d.会话的环境必须相同(如，sql被优化应该处于相同的优化模式)
结果缓存:(oltp环境比较适合)
对于大内存的系统可以利用结果缓存(缓存SQL查询结果和PL/SQL函数的查询结果)去提高重复查询的响应时间
对于结果缓存的使用由参数RESULT_CACHE_MODE控制，当为MANUAL时必须使用result_cache hint，当设置为FORCE时所有的结果如果可能的话都使用缓存，但可以使用no_result_cache hint表明将不使用结果缓存
MANUAL:select /*+ result_cache */ deptno, avg(sal) from emp group by deptno;
LRU算法也被用于结果缓存，如果查询的结果缓存所需的空间大于结果缓存的可用空间，那么此结果将不会被缓存
在sql查询中使用下面的数据库对象或函数时查询结果将不能被缓存:
数据字典和临时表、序列CURRVAL和NEXTVAL伪列、函数current_date, current_timestamp, local_timestamp,userenv/sys_context(常量)、sys_guid, sysdate,sys_timestamp
非确定性的PL/SQL函数
如果下面的结构用在查询中，缓存结果将是一个带有值的参数:
绑定变量、sql函数dbtimezone, sessiontimezone,userenv/sys_context(常量)、uid,和user、NLS参数
如果查询是等价的、参数值是相同的，那么参数缓存的结果能被重用
如果一个查询结果是基于读一致性数据的快照(已经有最近提交的数据的版本)，这种情况下查询结果将不被缓存
如果任何被引用做查询结果缓存的表在当前的一个session的一个连续事务中已经被修改，那么这个查询的结果将从来都不被缓存
为了最大限度的重用缓存结果而在一个外查询的内嵌视图中使用RESULT_CACHE hint，这种行为将禁用优化，子查询结果是不能被缓存的
在数据仓库中环境中，由于存在大量的低并发高资源消耗的sql语句，在这种情况下使用实际值比使用绑定变量允许优化器对列做出一个好的选择估计，从而提供一个优化数据访问的计划
在OLTP系统中通过大量的共享以使得共享池和相关资源能够得到有效的利用，从而提高性能
2.有效的使用共享池:
共享游标:(CURSOR_SHARING参数，下面是游标共享的做法)
使用绑定变量、
避免大量动态未共享sql语句的使用
确保应用的用户在它们的独立的会话的行为当中sql的执行计划没有发生改变
对于开发人员尽量使用存储过程(已经被解析过，避免了解析)
对于sql语句相同而没有共享的情况查询V$SQL_SHARED_CURSOR去确定原因(优化设置或绑定变量不匹配)

在OLTP系统中用户使用自己账户连接到数据库，用一个限定的模式来引用其它用户的表比使用公共同义词要好的多(有效的减少了字典缓存条目的数量)
SELECT employee_id FROM hr.employees WHERE department_id = :dept_id;----考虑用户级别的验证被放在本地的中间层，减少了不同ID用户的数量，也减少了字典缓存的负载

使用PL/SQL包可以减少字典缓存的过量负载
避免在高使用率的段上执行DDL语句，由于sql所引用对象的重新编译导致sql解析失效，在下次执行时不得不重新解析
为经常更新的序列号分配足够的缓存空间将有效的降低字典缓存锁的频率，提高扩展性，使用CREATE SEQUENCE或ALTER SEQUENCE语句中的CACHE关键字为每个序列配置缓存条目的数量

游标的管理:
减少游标的解析调用通过使用Oracle Call Interface (OCI)
减少游标的解析调用通过使用Oracle precompilers(在预编译命令行或预编译程序中设置预编译子句来控制游标的使用HOLD_CURSOR = YES RELEASE_CURSOR = NO MAXOPENCURSORS = desired_value)
减少游标的解析调用通过使用SQLJ
减少游标的解析调用通过使用JDBC的setStmtCacheSize()方法
减少游标的解析调用通过使用Oracle Forms

3.共享池大小估计:
库缓存大小:
V$LIBRARYCACHE视图的RELOADS列确保其值接近于0--------表明没有相关超龄对象被reload
INVALIDATIONS列确保其值接近于0--------表明由于某些DDL的操作导致已经被共享的sql变为无效状态,在OLTP环境的高峰负载时，这个值一定要确保接近于0
V$SGASTAT视图的free memory for the shared pool这个值应该在高峰负载时尽可能的低，而不引起reload
SELECT NAMESPACE, PINS, PINHITS, RELOADS, INVALIDATIONS
FROM V$LIBRARYCACHE
ORDER BY NAMESPACE;

Library Cache Hit Ratio = sum(pinhits) / sum(pins)
-----------上面的查询是从实例启动到当前状态的统计数据，而不是某一段时间收集的统计信息
SELECT * FROM V$SGASTAT
WHERE NAME = 'free memory'
AND POOL = 'shared pool';
库缓存相关的几个视图:V$SHARED_POOL_ADVICE、V$LIBRARY_CACHE_MEMORY、V$JAVA_POOL_ADVICE、V$JAVA_LIBRARY_CACHE_MEMORY

字典缓存统计:
column parameter format a21
column pct_succ_gets format 999.9
column updates format 999,999,999
SELECT parameter
, sum(gets)
, sum(getmisses)
, 100*sum(gets - getmisses) / sum(gets) pct_succ_gets
, sum(modifications) updates
FROM V$ROWCACHE
WHERE gets > 0
GROUP BY parameter;
SELECT (SUM(GETS - GETMISSES - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE; -----字典缓存的命中率
结果缓存统计：
SQL> set serveroutput on
SQL> execute dbms_result_cache.memory_report
SQL>execute dbms_result_cache.flush -----清理结果缓存
column name format a20
select name, value from v$result_cache_statistics;
RESULT_CACHE_MAX_SIZE可以设置结果缓存的最大值，不能被动态修改
RESULT_CACHE_REMOTE_EXPIRATION可以用来设置远程数据库对象的查询结果缓存在本地，远程将不会缓存，默认值为0

4.大池:
在使用并行查询、RMAN执行备份恢复操作、共享服务器模式时需要为这些操作分配大池，大池不使用LRU算法
使用大池来存储与共享服务器相关的用户全局区UGA，减少了共享池碎片
LARGE_POOL_SIZE指定大池的大小，大池的最小值是300K
查看对象驻留在那个池中，可以想看V$SGASTAT视图的POOL列
大池的配置基于同时活动会话的数量(假设共享服务器模式需要200K到300K来存储每个活动会话的信息，如果预计同时有100个活动的会话，那么配置大池到30M)
对于使用共享服务模式的DB即使在配置了大池,oracle仍然会从共享池中为每个会话分配一个固定的内存(约10K)，
CIRCUITS参数指定oracle允许并发共享服务器进程的最大数量
共享服务器相比专业服务器使用更少的PGA内存------因为总进程数量减少
在共享服务器下为了获得更好的性能设置SORT_AREA_SIZE和SORT_AREA_RETAINED_SIZE为同样的值将保证了大池中排序结果不被写入磁盘
SELECT SUM(VALUE) || ' BYTES' "TOTAL MEMORY FOR ALL SESSIONS"
FROM V$SESSTAT, V$STATNAME
WHERE NAME = 'session uga memory'    --------如果会话连接到专用服务器进程，那么这个内存是用户进程所占内存的一部分，如果会话连接到共享服务器进程，那么这个内存是共享池的一部分
AND V$SESSTAT.STATISTIC# = V$STATNAME.STATISTIC#;
SELECT SUM(VALUE) || ' BYTES' "TOTAL MAX MEM FOR ALL SESSIONS"
FROM V$SESSTAT, V$STATNAME
WHERE NAME = 'session uga memory max'
AND V$SESSTAT.STATISTIC# = V$STATNAME.STATISTIC#;
CURSOR_SPACE_FOR_TIME参数参数默认为false，如果在库缓存命中率比较高的情况下，此参数被设置为true可以稍微改善性能，因为true表示直等到相关应用的语句关闭才释放游标，不验证游标是否在缓存中
两种情况下不能设置CURSOR_SPACE_FOR_TIME为true:如果执行调用过程中库缓存缺失+每个用户的私有sql区的可用内存不足时
缓存会话游标:-----对使用oracle Forms的应用特别有用
缓存会话游标通过SESSION_CACHED_CURSORS参数控制，可心动态修改ALTER SESSION SET SESSION_CACHED_CURSORS = value; -----表示保存在缓存的会话游标的最大数量
缓存会话游标使用LRU算法
确定缓存会话游标大小是否合适，查看V$SYSSTAT view的session cursor cache hits

5.保留池:
相关知识:
oracle内部在共享池内部保留的一个很小的内存区，可以缓解由于共享池中可用内存不足导致分配一个大的内存空间(大于_shared_pool_reserved_min_alloc)请求而引起的latch争用
保留池通常用于在加载java对象相关的PL/SQL或触发器编译的操作
一般情况下是不需要改变保留池的大小，因为oracle已经预留了一个足够大的内存空间给保留池(默认5%*SHARED_POOL_SIZE)，如果有需要可以通过SHARED_POOL_RESERVED_SIZE参数
对于一个大内存的需求，oracle会先在share pool的未保留部分进行分配，如果不足再去保留池分配，如果两者都无足够的空间，那么oracle试图通过释放足够空间来分配，先在未保留部分，再在保留池
V$SHARED_POOL_RESERVED视图可以诊断保留池相关的参数，在一个有充足可用内存的操作系统中应保证REQUEST_MISSES趋于0，如果在一个内存紧张的操作系统中应保证没有REQUEST_FAILURES或此值不再增加，如果这些指标不能达到那么需要同时增加SHARED_POOL_RESERVED_SIZE和SHARED_POOL_SIZE相同的倍数
为了减少响应时间将一些大的SQL或PL/SQL保留在share pool中可以使用DBMS_SHARED_POOL.KEEP，需要分三步:
a.确定那一个包或游标要被放到内存中
b.启动数据库   -------这一步很关键，减少了共享池的碎片的产生，提高性能
c.调用DBMS_SHARED_POOL.KEEP去固定对象到内部中
共享池的中的保留池大小不能超过共享池的50%，一般情况下建议为共享池的5%到10%
show parameter reserved
NAME                                TYPE                            VALUE
------------------------------------ -------------------------------- ------------------------------
_shared_pool_reserved_pct           integer                           10             -----阀值
shared_pool_reserved_size           biginteger                        512M    -----保留池的大小
descv$shared_pool_reserved;
Name                                     Null?   Type
------------------------------ -------- ----------------------------
--以下字段只有当参数SHARED_POOL_RESERVED_SIZE设置了才有效
FREE_SPACE                  NUMBER --&gt保留区的空闲空间数
AVG_FREE_SIZE               NUMBER --&gt保留区的空闲空间平均数
FREE_COUNT                  NUMBER --&gt保留区的空闲内存块数
MAX_FREE_SIZE               NUMBER --&gt最大的保留区空闲空间数
USED_SPACE                  NUMBER --&gt保留区使用空间数
AVG_USED_SIZE               NUMBER --&gt保留区使用空间平均数。
USED_COUNT                  NUMBER --&gt保留区使用内存块数
MAX_USED_SIZE               NUMBER --&gt最大保留区使用空间数
REQUESTS                    NUMBER --&gt请求在保留区查找空闲内存块的次数
REQUEST_MISSES              NUMBER --&gt无法满足查找保留区空闲内存块请求，需要从LRU列表中清出对象的次数
LAST_MISS_SIZE              NUMBER --&gt请求的内存大小，这次请求是最后一次需要从LRU列表清出对象来满足的请求
MAX_MISS_SIZE               NUMBER --&gt所有需要从LRU列表清出对象来满足的请求中的内存最大大小
--以下字段无论参数SHARED_POOL_RESERVED_SIZE是否设置了都有效
REQUEST_FAILURES            NUMBER --&gt没有内存能满足的请求次数（导致4031错误的请求）
LAST_FAILURE_SIZE           NUMBER --&gt没有内存能满足的请求所需的内存大小（导致4031错误的请求）
ABORTED_REQUEST_THRESHOLD   NUMBER --&gt不清出对象的情况下，导致4031错误的最小请求大小
ABORTED_REQUESTS            NUMBER --&gt不清出对象的情况下，导致4031错误的请求次数
LAST_ABORTED_SIZE           NUMBER --&gt不清出对象的情况下，最后一次导致4031错误的请求大小
--我们可以根据后面4个字段值来决定如何设置保留区的大小以避免4031错误的发生
select requests,request_misses,request_failures,
last_failure_size,aborted_request_threshold ab_re_th,
aborted_requests,last_aborted_size
from v$shared_pool_reserved;
保留池监视:
select free_space,free_count,used_space,avg_used_size from v$shared_pool_reserved; ----查看保留池是否够用
SELECT REQUEST_MISSES,REQUEST_FAILURES FROM V$SHARED_POOL_RESERVED; -----监控当前大小是否合理
使用包dbms_shared_pool的过程aborted_request_threshold来调整阙值
alter system set shared_pool_reserved_size=integer[K | M | G] -------修改大小
保留池过大:
request_misses列值始终为零，且没有持续增加
free_memory列值大于等于保留池的50%
影响保留池的几个重要参数:
col ksppinm format a54
col ksppstvl format a54
SELECT KSPPINM, KSPPSTVL
FROM X$KSPPI PI, X$KSPPCVCV
WHERE CV.INDX = PI.INDX
AND PI.KSPPINMLIKE'/_%'ESCAPE'/'
AND PI.KSPPINMLIKE'%&Para%';
show parameter reserved

6.游标共享:
好处:减少了内存的使用，闩锁的争用，能更快的解析
解析阶段:根据CURSOR_SHARING的设置看确定sql文本是否完全相同或相似+执行计划是否可以重用=sql可以共享
oracle不建议在DSS(仓库)环境中设置CURSOR_SHARING参数为force，此外星型转换不支持CURSOR_SHARING设置为similar或force
虽然游标共享设置为similar可以减少一部分硬解析，但在共享池中查找一个类似可以共享的语句时又需要一些额外的工作(增加了任何select语句中文字的长度，通过describe可以看出)，所以最好是在开发时编写一致的sql语句
在以下两种情况下可能需要设置:
a.共享池中的一些sql仅仅在文字的值不相同
b.存在大量的库缓存未命中导致响应时间降低

维护连接:
在大型的OLTP环境中，中间层应该保持与数据库的连接，而不是为每一个数据库请求建立连接和断开连接，这样做减少了CPU资源和闩锁

7.使用客户端查询结果缓存:
oracle调用接口的应用程序可以利用客户端OCI结果缓存，从而提高查询的响应时间
结果缓存的模式:
RESULT_CACHE_MODE参数决定，manual表示必须使用/*+ result_cache */ hint才能使用结果缓存，force表示所有的查询结果都进行结果缓存
在manual下使用类似语句:SELECT /*+ result_cache */ deptno, avg(sal) FROM emp GROUP BY deptno;
在force下可以使用类似语句:SELECT /*+ no_result_cache */ deptno, avg(sal) FROM emp GROUP BY deptno;
控制客户端结果缓存:CLIENT_RESULT_CACHE_SIZE当设置为0将禁用，CLIENT_RESULT_CACHE_LAG控制客户端缓存的延迟时间，设置一个较低的值将保证客户端与数据库端结果的一致性
通过CLIENT_RESULT_CACHE_STATS$视图查看结果缓存的相关信息

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

转载于:http://blog.itpub.net/16807927/viewspace-711598/