OCP认证033之自制讲稿—调整共享池(zt)

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 -------------&gt 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，随着持续稳定的运行，我们希望他趋近于0
/oem工具：performace manager--&gtshared 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/35489/viewspace-85088/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/35489/viewspace-85088/