ITPub博客

首页 > IT职业 > IT生活 > 调整共享池(zt)

调整共享池(zt)

原创 IT生活 作者:jolly10 时间:2008-04-30 10:16:16 0 删除 编辑
本文对shared_pool各部分的调整讲的比较详细,特记录下来.[@more@]
1.共享池中的主要组件
/库缓存(library cache) 主要缓存共享SQL和PL/SQL相关信息
/数据词典缓存(data dictionary cache) 缓存Dictionary objects的信息,用于解析权限,表结构等
/UGA(User global are)
在shared server模式下如果没有配置大池(Larger_pool_size=0)时,UGA会占用shared pool的空间
SGA中除Shared Pool外还有:
大池 如果使用RMAN备份 并行查询 共享服务器时应该配置
另外还有 data buffer cache(ppt第五章), redo log buffer(ppt第七章)

2.调整shared pool size应该考虑什么
由于库缓存和数据词典缓存的miss引起的操作时间往往比SGA中其他组件的cache miss要多,因此在SGA中
首先应该考虑调整shared pool。而当调整shared pool时,则应该首先集中在库缓存上,因为库缓存的大小和数
据词典缓存的大小没有单独的设置,而是oracle自动按照一定的算法在shared pool中分配给他们,而按照oracle
的内存空间分配的算法,如果库缓存的命中率高的话,数据词典缓存的命中率也会较高的;库缓存的命中率高而数
据词典缓存的命中率却很低的话几乎是很少的。
如果共享池很小,这往往会消耗很多CPU资源并且容易引起竞争;但是如果你把共享池设到很大,也不一定
是好事,那为什么呢?当你把共享池配得很大,是不是消耗了很多内存资源那,那么其他得组件能使用得内存
资源是不是就少了,另外,在这个很大的大池中,缓冲的内容是不是很多啊,多了,在里面查找是不是就慢了。
所以说共享池虽然不能很小,但是也不是越大越好,还是要适当。

3. 共享池相关
/这个共享池是由初始化参数SHARED_POOL_SIZE来定义的,默认是8M,这个参数是动态的
/库缓存的内容
包含了Statement的文本, 分析后的Code, 以及相应的执行计划
/数据词典缓存主要是system中的数据词典表(对象的列定义信息,用户的权限信息等)

4. 库缓存
/用来缓存共享的SQL statements 和PL/SQL块,这些可以给所有的连接用户共享。
/由LRU(最小使用最先淘汰算法)来管理, 千万不要以为是FIFO(先进先出)算法管理的
/oracle server怎样知道你要执行的Statement等被缓存了呢?
--先通过一个hash算法将Statement text运算成一个hash数值
--然后通过这个hash值去找

5 几个重要的共享池相关的锁存器(latch)
/latch也是一种锁,但是是一中由oracle server自行管理的用来保护共享的内存结构的低级锁(low lock),
和后面章节讲的LOCK是由区别的,latch不用我们来维护。
/share pool latch: 用来保护共享池中的内存分配
/libray cache latch: 在shared pool中查找匹配的SQL等是起到保护作用,保护在查找的短暂过程中这个列表不会被改变
什么时候会改变,由于oracle server可以同时被很多用户连接使用,在这个过程中很由可能会由新的SQL进来,而把老的
很少用的SQL给恰巧挤出去了,而我要找的就是这个很少用的SQL时那可能不就出问题了吗,这个latch能够保证在查找的
这个过程中不会出现这个问题。
而在这些latches上如过由竞争的话,表示SQL、PL/SQL被重用的机率很小,可能是因为没有使用绑定变量或者设置的游标
缓存的数量不够多。那么要减少这种竞争,我们又该怎么做呢?我们可以考虑什么原因引起的,然后用什么对策。
引起竞争的原因:没有共享的SQL(没有使用绑定变量), 共享SQL重新硬分析了(可以查询V$sqlarea中的parse_calls和
excutions字段,如果某个SQL对应的parse_calls接近excutions,说明这条SQL经常被重新硬分析),库缓存的大小不够
根据上面的这些因素,我们可以考虑调整下面几种情况来减少竞争的发生,理想情况是减少到0啦。
优化您的SQL, 检查"热点块(hot blocks,同时访问的人多)", 调整CURSOR_SHARING参数的值,增大shared_pool_size
如果使用了需要大池的操作而没有配置大池,可以配置大池(比如共享模式,可以把UGA从shared pool移到large pool)

6 调整库缓存目标
/保持最小次数的分析,尽可能减少丢失,如何做:
--确保用户能够共享statement
--分配足够的空间保证statement尽可能的不被赶出去
经常要用的statement要保证他们能够呆在share pool里被找到
--避免invalidations(由于数据字典发生变法而引起的Statement失效叫invalidations,比如改变了数据结构,重新收集了统计等)
/避免碎片,这样会浪费空间
--为大内存要求的对象(比如大于1万byte的SQL) 保留空间,由对应的参数设置
--栓住那些场用的大对象,最好是在instance一移动,马上就把他们keep到shared pool中
--尽可能的不要使用匿名PL/SQL块(把他们写成一些小的包中的存储过程或函数)
--为shared server连接配置大池

7. 术语 V$libaraycache
/命名空间:SQL AREA, TABLE/PROCEDURE, BODY, TRIGGER 等
/gets: (parse)每当一条语句被分析一次时,该语句对应的名称空间的gets加1
/gethits : 分析时在对应的名称空间找到已经存在时加1,其分析后的代码和执行计划在内存中找到了,不再执行硬分析,直接使用
/pins: (excution)每当一条语句执行一次时,该语句对应的名称空间的的pins加1
/reloads: (parse)因为找到的分析代码版本已经过期或作废而被重新硬分析的次数
/invalidations: 因为数据词典发生变化,该语句被标记成失效,被迫重新做硬分析的次数
对于每一个命名空间,在
v@librarycache中会有一行与其相对应,可以分析每一个命名空间的get命中率和reloads(丢失率),
这两个比例对监控优化library cache非常有用。


8. 库缓存相关的诊断工具
/v$sgastat 可以显示所有SGA组件的大小
/v$librarycache 关于library中对象的统计信息,非常重要
/v$sql 可以查看cursor,parent SQL text, child of the origial SQL
这里介绍以下child cursor
user A: select * from tblA
user B: select * from tblA
大家认为这两条语句是不是一样的啊,可能会有很多人会说是一样的,但我告诉你不一定,那为什么呢?
这个tblA看起来是一样的,但是不一定哦,一个是A用户的, 一个是B用户的,这时他们的执行计划分析代码
差别可能就大了哦,哈哈,如果前面加了限定大家就明白了: select * from A.tblA,select * from B.tblA
这是不一样,当然前面不加限定的也可能是一样的。
/v$sqlarea: 这个数据表相当与对v$sql做group by 了,并且和v$sql一样每条只可以看到1000个字符
/v$sqltest: 这个试图可以查看Sql等的全部内容啦,分行的哦
/v$db_object_cache: 显示缓存的对象,包括对应的namespace,type和gets,pins等统计信息
/相关初始化参数:shared_pool_size, open_cursors, session_cached_cursors, cursor_space_for_time
cursor_sharing, shared_pool_reserved_size
/statspack等工具

9. 是否共享了Cursor
/查询V$librarycache,检查SQL AREA的gethitratio
select gethitratio from v$libararycache where namespace='SQL AREA';
这个命中率 = gethits/gets, 表示软分析次数与分析次数的比值
OLTP系统应该>=90%,如果<90%,你可能需要
--改写你的应用程序中的Statement了,提高效率。但往往你可能无法去调整应用程序的代码,因为你没有。
--增加共享池的大小
--调查那些SQL正在执行
select sql_text, users_excuting,executions, loads from v$sqlarea;
--users_excuting查询的时候,正在执行的次数
--excutions 总的执行次数
--loads 这个数值越低越好
select * from v$sqltext where sql_text like 'select * from hr.employees where %';

10. 共享游标
/初始化参数 CURSOR_SHARING,有三个取值:exact, similar, force
--exact 默认值,2个sql语句必须完全一样才共享缓存中的已分析代码
--similar 当2个sql仅只有参数变量值不一样而结构完全一样时,而且执行计划一样就共享
--force 当2个sql仅只有参数变量值不一样而结构完全一样时也共享,不考虑执行计划的影响,比如
select * from employees where employee_id=1000;
select * from employees where employee_id=1234;
11. 准则:库缓存的丢失率(reloads)
/reload应该小于PINS的1%
select sum(pins) "executions", sum(reloads) "cache misses",
sum(reloads)/sum(pins) from v$libararycache;
/当其>1%时,有两种可能的原因
--因为没有空间了,曾经分析执行过的代码被赶出去了,对策:增加shared pool大小,当然SGA要有空闲空间
--共享的代码 invalidated了
对策:避免在高负荷时修改数据结构和收集数据统计,创建索引等,把这些工作调整到负荷低的时候去做

12. 调整库缓存
/当gethitratio<90%或丢失率>1%时,就需要优化
/对于频繁要用的PL/SQL包,触发器,sequence,永久将他们缓存在内存中
/为常用的SQL定义足够的内存
/为大内存需求的对象保留空间,这样可以避免碎片(碎片会导致这些内存不可用而浪费)
/栓住那些常用的对象
/将大的匿名PL/SQL块改写成小的包函数
将匿名PL/SQL块改写成存储过程,不管他们是大还是小的匿名块

13. 共享池建议
可以通过OEM来查看oracle server建议的共享池大小,也可以查询视图
select shared_pool_size_for_estimate as pool_size, estd_lc_size,estd_lc_time_saved from v$shared_pool_advice;
shared_pool_size_for_estimate:表示估计的共享池大小,取值范围,当前库缓存大小的50---200%,每个估计值一行
estd_lc_size: 对应分配的library cache大小,
estd_lc_time_saved: 估计的能保留的时长
怎么决定库缓存的大小呢,当estd_ls_size增大时,estd_lc_time不再发生变化时,我们取第一个这样的share_pool_size_for_estimate作为我们的共享池大小。

14. 执行计划
/oracle server把SQL解析执行过的执行计划缓存在内存中,供下次共享使用,而不必再执行一次硬分析,直接找到相应的内存中的执行计划来执行SQL
/当SQL在内存中被淘汰,相对应的执行计划也会被请出libarary cache
/这个特性最主要的好处就是能更好的诊断查询执行效率
/怎样查询SQL的执行计划
select operation,object_owner,object_name,cost from v$sql_plan order by hash_value
/在v$sql视图中有一列hash_value(书上错:不应该是书上说的plan_hash_value)和v$sql_plan的列hash_value相对应
/hash_value还可以用来比较Cursor SQL TEXT是否相似

15 计算Global Space
/计算存储对象需要的空间大小,比如包,函数,存储过程
select sum(sharable_mem) from v$db_object_view
[where type in ('PACKAGE','PACKAGE BODY','PROCEDURE','FUNCTION')]
/计算经常执行的SQl需要的空间大小
select sum(sharable_meme) from v$sql_are where execution>5
/计算高峰时所有打开游标需要的空间,假如每个用户每个游标的最大允许大小是250B
select sumI(250*user_opening) from v$sqlarea
/理想状态下,您的生产环境的library cache应该是这些数字之和,并且加上一定的缓冲大小
/在测试环境中,你也可以用一个典型用户来计算可能需要需要的平均游标空间,
select 250*value bytes_per_user from v$sesstat s, v$statname n where s.statistic$=n.statistics#
and n.name='opened cursors curent' and s.sid=15;

16. 大内存需求(oracle自身运行的大程序段,package等,超过1万byte的程序段)
/有大的连续内存满足需求
/在共享池中为他们保留一定的连续空间
/V$shared_pool_reserved视图中的列说明
--free_space: 保留空间列表中所有的空闲空间
--Avg_Free_Size: 保留空间列表空闲内存的平均值
--Max_Free_Size: 保留空间列表空闲内存的最大值
--Request_misses:因为没有一个满足需求的连续空闲内存,LRU列表不得不flush对象来满足请求的次数
--request_failuers: 没有空闲空间导致请求失败的次数
--last_failures_size:最后一次请求失败需要的内存大小
--其他
/初始化参数:shared_pool_size
shared_pool_reserved_size 一般为share_pool_size的10%, 是shared_pool_size的一部分,不是另外开辟一块内存
最大值是share_pool_size的50%, 大于50%会报错

17. 调整共享池保留空间
/诊断工具 V$shared_pool_reserved
--在一个有足够空闲内存的系统,调整的目标是 request_misses=0
/通过dbms_shared_pool_pachage.aborted_request_threshold过程来设置一个阀值
比如有个包在reserver中请求2M空间,reserver会去找一个2M的连续空间出来,一般情况下,如果没有则会请出一些对象以获得这2M内存,但是你可以设置一个阀值比2M小一点,那么不管有没有2M或者以上的空闲内存,就都不会让2M及其以上的对象进来
/设置初始化参数shared_pool_reserved_size
---当shared_pool_reserved_size太小,可以适当增大他的大小
--当他太大,以下任何一种情况满足应该适当减小shared_pool_reserved_size的值
/request_miss=0或者保持一个值而不再增大是,可以减小shared_pool_reserved_size到一个适合这个情况的最小值,
释放出不必要的内存给其他的人用
/shared_pool_reserved中如果Free_space 大于其50%时
/shared_pool_size太小时
使用V$shared_pool_reserved也可以诊断出shared_pool_size太小,这是request_failuer>0并且持续增大的一个原因,这个时候,如果
你已经启用了the reserved list,那么减小shared_pool_reserved_size的大小,如果没有,则要增加shared_pool_size的大小。

18. 在共享池中栓住大对象
/调查库缓存中那些大对象没有被栓住, 比如大于1万字节的
select * from v$db_object_cache where shareable_mem>10000
and kept='NO' [and type in ('PACKAGE', 'PACKAGE BODY', 'FUNCTION','PROCEDURE')]
/用什么工具来栓住他们到共享池中,用dbms_shared_pool.keep(package_nem)这个包过程
现在我们应该知道为什么要栓住这些大对象吧,如果不栓住这些大对象,在加载到库池时极容易引起内存碎片,为了能够获得足够的连续空间,甚至需要将大量的小对象从共享池中赶出去,这样将会导致命中率低,响应用户的时间增加,对不对啊!为了避免这种糟糕的情况发生,我们就有必要以启动instance,就先栓住这些常用的大对象,这样他们可以一直保存在内存中,也不再去和那些小对象去抢空间了。
/那些对象应该被栓住
--经常需要的大的存储过程,比如standard包,diutil包以及那些超过定义的阀值的常用的大对象
--使用频率很高的那些表对应的触发器
--序列sequence, 缓存起来可以增加响应的速度,(创建sequence有一个cache N从句,可以一次生成多少个值缓存起来)
/什么时候栓住他们最好
在前面我已经提到过好几次了,大家想起来了没有。在instance一起来就把他们栓住。
/凡是被栓住的对象,即使你执行alter system flush shared_pool;时也不会被赶出去,还在里面,是不是很有好处啊?

19. 匿名PL/SQL块
/怎样找出匿名块,找到后该怎么优化
--查询视图v$sqlarea中command_type=47的SQL,这个就是匿名块,比如
select sql_text from v$sqlarea where Command_type=47 and length(sql_text)>500
--一般来说找到他们后尽可能的把他们转换成小的包函数,但是如果不能转化又怎么办,那就把他们栓住啊,
比如,步骤如下:
1) declare x number declare /* KEEP_ME */ x number
begin x:=5 -------------> begin x:=5;
end; end;
2) select address, hash_value from v$sqlarea where command_type=47 and sql_text like '%KEEP_ME%';
3) execute dbms_shared_pool_keep('address,hash_value');这里'address,hash_value'是上面查出来的值啊

20. 影响库缓存的其他一些参数
/open_cursors 50(default)|n
这个参数定义了在用户进程的私有(不共享)SQL区域最多能打开的游标数量,对于中型应用一般设为300
/Cursor_space_for_time false(default)|true
表示是否缓存cursor, true能够提高命中率,但增加负荷,慎重,那什么时候可以改为true呢?
因为设置为true, 你是那空间来换时间,如果你有足够的内存,能使V$librarycache中reloads等于0或者趋近0的时候,你当然可以设置为true.
如果你的应用使用了oracle forms和任何动态SQL, oracle建议保留默认值false
/session_cached_cursors 0(default,不缓存)
如果应用程序是form型的,将这个值设为true,可以改进库缓存的命中率,因为窗体中所包含的SQL语句将被缓存,当窗体来回切换时可以减少重分析的次数。
/Cursor_sharing exact|similar|force
这个前面已经详细介绍过,这里不再介绍了,大家如果不清楚可以翻看前面。

21. 调整数据词典缓存
前面我们一直在讨论的是库缓存,大家知道,共享池中还有一个很重要的组件,那就是数据词典缓存。这讲开始我们开始讨论这个问题。
/存放的内容:数据对象的定义
/使用v$rowcache视图可以得到数据词典缓存中的信息,主要有了解三个列
--paramter: 缓存中数据词典的名称
--gets: 请求的总次数
--getmisses: 请求时没有在缓存中找到的次数,丢失
/优化目标:避免丢失,提高命中率, 丢失率:getmisses/gets
/要调整数据词典缓存大小,只能通过改变shared_pool_size参数来调整
/在实例刚起来的时候,由于数据词典缓存中没有数据,所以任何执行的SQL第一次都需要从磁盘文件加载数据词典,产生缓冲丢失。但是,随着数据库稳定持续的运行,常用的数据词典对象就应该缓存到了内存中,在这个时候,缓存丢失发生的几率就应该很小。为了调优数据词典缓存,应该实在应用已经稳定持续运行了一段时间,而不是instance启动的时候。

22. 怎样测量数据词典性能
/V$rowcache
/比较重要的每行的丢失率应该小于2%, 整体丢失率应该小于15%
--单个/每行
select parameter, getmisses, gets, (getmisses/gets)*100 "miss ratio %" from v$rowcache;
--整体丢失率和命中率
select sum(getmisses)/sum(gets)*100 "miss ratio %",
(1-sum(getmisses)/sum(gets))*100 "Hit ratio %"
from v$rowcache;
--如果丢失率高,说明共享池小,需要调整共享池大小
/statspack报告中的 dictinary cache stats 部分,丢失率应该比较低,高说明有性能瓶颈
例 cache get Pct ........ Pct
requests Miss SGA
-------------------------------------------------------------
dc_free_extent 4 0.0 88
.............
dc_usernames 28 3.6 81
dc_users 45 0.0 86
Pct Miss表示丢失率,pct SGA表示为对应cache分配的内存空间的使用率
如果pct SGA和pct Miss都大的话,说明请求数高,并且丢失率也高,也就是暗示共享池太小。
/调整,如果丢失率高的话,我们就需要考虑增加共享池的大小了
alter system set shared_pool_size = 更大的值 scope=both|memeory|spfile;
/由于实例刚起来的焙蚧撼逯忻挥惺?荩??远??什豢赡艿扔?,随着持续稳定的运行,我们希望他趋近于0
/oem工具:performace manager-->shared pool statistic
--memory: SGA Overview
--Database Instance: libary cache Hit %
--top sessions: top session

23. UGA和ORACLE共享服务器
前面我们讨论了库缓存和数据词典缓存,下面我们来了解一下UGA和oracle共享服务器
/专用服务器下,我门知道每个用户进程都有一个PGA, PGA用于存放:
用户会话信息,游标State, 私有SQL区域/Sort Area,堆栈
/共享服务器,PGA依然存在,但只有堆栈数据了,其他的几个内容都被放到UGA区域了,
UGA对server process来说是共享的,不是为那个单独服务的,而PGA是一个server process一个单独的
--UGA到底放在那里呢,如果配置了大池则放在大池里,如果没有配置大池,就会在共享池中抢一块空间出来
/使用共享服务器时,能够节省内存空间,UGA+所有PGA的大小要小于专用服务器下所有PGA的大小

24. 计算UGA使用的大小
/计算你的连接的UGA大小
SELECT SUM(value) ||'bytes' "Total session memory"
FROM v$mystat, v$statname
WHERE name = 'session uga memory'
AND v$mystat.statistic# = v$statname.statistic#;
--mystat 仅只有你自己当前会话的信息
/计算共享服务下所有用户使用的UGA大小
SELECT SUM(value) ||'bytes' "Total session memory"
FROM v$sesstat, v$statname
WHERE name = 'session uga memory'
AND v$sesstat.statistic# = v$statname.statistic#;
/计算UGA曾经用到的最大空间
SELECT SUM(value) ||'bytes' "Total max memory"
FROM v$sesstat, v$statname
WHERE name = 'session uga memory max'
AND v$sesstat.statistic# = v$statname.statistic#;

25. 大池 large pool
这一讲的最后我们来说说大池,其实我们前面已经有很多地方提到过了他,相信大家还记得。
/什么时候用大池呢
--OS不支持异步I/O,为了提高DBWn的速度,使用IO奴隶(DBWR_IO_SLAVES),不过现在的操作系统基本上都支持异步I/O,所以很少使用
--使用RMAN备份和恢复操作
--共享服务器的UGA(session memory)
--并行查询
/由参数large_pool_size参数来调整大小,也是动态参数
/在共享服务器模式下,设置大池,UGA会从Shared pool中移到大池中,这样可以把共享池的内存留给库池和数据词典用,这样可以避免
共享的SQL由于空间不够而被赶出去和额外的空间争用。

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

请登录后发表评论 登录
全部评论

注册时间:2008-02-20

  • 博文量
    263
  • 访问量
    761325