--=======================================
--共享池的调整与优化(Sharedpool Tuning)
--=======================================
共享池(Shared pool)是SGA中最关键的内存片段,共享池主要由库缓存(共享sql区和PL/sql区)和数据字典缓存组成。其中库缓存的作用是存
放频繁使用的sql，pl/代码以及执行计划。数据字段缓存用于缓存数据字典。在内存空间有限的容量下，数据库系统根据一定的算法决定何
时释放共享池中的代码以及数据字典信息。下面逐一解释各个部件并给出调整方案。
一、共享池的组成
Library cache(库缓存)--存放sql,PL/sql代码,命令块,解析代码，执行计划
Data dictionary cache(数据字典缓存)--存放数据对象的数据字典信息
User global area(UGA) for sharedserver session--用于共享模式，可以将该模块移到laregpool来处理。专用模式不予考虑。
二、Library cache作用与组成
Library Cache由以下四个部件组成
Shared sql areas
Private sql areas
PL/sql proceduresand packages
VarIoUs controlstructures
Library Cache作用
采用LRU算法(最近最少使用算法)
用于避免相同代码的再度解析
ORA-04031则表明共享池不够用
三、Data dictionary cache组成与作用
组成
Row cache
Library cache
作用
存储数据库中数据文件、表、索引、列、用户和其它数据对象的定义和权限信息
四、Shared pool的大小
Library cache与Data dictionarycache两者共同组成了shared pool的大小，由参数shared_pool_size来决定
查看：show parametershared_pool_size
修改：altersystemsetshared_pool_size=120m;
sys@ORCL>select*fromv$versionwhererownum<2;
BANNER
----------------------------------------------------------------
OracleDatabase10g Enterprise Edition Release 10.2.0.1.0-Prod
show parameter shared_pool_
NAMETYPEVALUE
----------------------------------------------- ------------------------------
shared_pool_reserved_sizebiginteger 3M
shared_pool_sizebiginteger 0--为0，表明由系统自动分配
show parameter sga_
sga_max_sizebiginteger 176M
sga_targetbiginteger 176M--非零值，表示由系统自动调整sga
五、SGA_MAX_SIZE与SGA_TARGET
sga_max_size决定了为Oracle分配内存的最大值
sga_target决定了基于sga_max_size的大小来自动分配内存，sga_target<=sga_max_size
sga_target会为下列组件自动分配内存
Buffer cache
Shared pool
Larege pool
Jave pool
Streams pool
当设定sga_target参数为非零值，且又单独设定sga_target的五个组件为非零值，在这种情形下，这几个组件设定的值则为该组件所必须要
分配的最小值。
下列sga组件不受sga_target的管理和影响，即需要单独为以下几个组件分配大小
Log buffer(日志缓冲)
Other buffer caches,such as KEEP,RECYCLE,and other block sizes(保留池，回收池，nK池)
Fixed SGA and otherinternal allocations
有关SGA的自动管理，更详细请参考：Oracle10g SGA的自动化管理
当发布一条sql或PL/sql命令时，Oracle会自动寻找该命令是否存在于共享池中来决定对当前的语句使用硬解析或软解析。
sql语句的执行过程如下：
a.sql代码的语法(语法的正确性)及语义检查(对象的存在性与权限)
c.如果共享池中存在相同的哈希值，则对这个命令进一步判断是否进行软解析，否则到e步骤。
d.对于存在相同哈希值的新命令行，其文本将与已存在的命令行的文本逐个进行比较。这些比较包括大小写，字符串是否一致，空格，注释等，如果一致，则对其进行软解析，转到步骤f。否则到d步骤。
e.硬解析，生成执行计划。
有关硬解析与软解析请参考：Oracle硬解析与软解析
七、共享池中闩的竞争
共享池中闩的竞争或Library cache闩的竞争表明存在下列情形
非共享的sql需要硬解析
重新解析共享的sql(由于Librarycache大小不足导致共享的sql被LRU算法淘汰掉)
过多的负荷导致Librarycache大小不足
八、v$librarycache视图
scott@ORCLdescv$librarycache;
NameNull?Type
----------------------------- ----------------------
NAMESPACEVARCHAR2(15)--存储在库缓存中的对象类型,值为sqlarea,table/procedure,body,trigger
GETSNUMBER--显示请求库缓存中的条目的次数(或语句句柄数)
GETHITSNUMBER--显示被请求的条目存在于缓存中的次数(获得的句柄数)
GETHITRATIONUMBER--前两者之比
PINSNUMBER--位于execution阶段，显示库缓存中条目被执行的次数
PINHITSNUMBER--位于execution阶段，显示条目已经在库缓存中之后被执行的次数
PINHITRATIONUMBERRELOADSNUMBER--显示条目因过时或无效时在库缓存中被重载的次数
INVALIDATIONSNUMBER--由于对象被修改导致所有参照该对象的执行计划无效的次数，需要被再次解析
DLM_LOCK_REQUESTSNUMBER
DLM_PIN_REQUESTSNUMBER
DLM_PIN_RELEASESNUMBER
DLM_INVALIDATION_REQUESTSNUMBER
DLM_INVALIDATIONSNUMBER
get表示请求条目或对象、获得对象句柄；
pin根据句柄找到实际对象并执行，但对象内容可能因为老化而pin不到所以出现reload；
一个session需要使用一个object时，如果是初次使用，则必然是先get然后pin并维护这个object的句柄。下次再使用这个object时，因为
已经维护该句柄，所以直接pin而没有了get过程。如果对象老化则移除共享池，再次请求则会出现reload。
有关Library cache的详细说明：V$LIBRARY
由上面所列出的字段可知，v$librarycache视图可以用来监控librarycache的活动情况。
重点关注字段
RELOADS列：表示对象被重新加载的次数，理论上该值应该接近于零。过大是由于对象无效或librarypool过小被换出。
INVALIDATIONS：列表示对象失效的次数，对象失效后，需要被再次解析。
GETHITRATIO：该列值过低，表明过多的对象被换出内存。
GETPINRATIO：该列值过低，表明会话没有多次执行相同的游标，即使对象被不同的会话共享或会话没有找到共享的游标。
sys@ASMDBOracle9iEnterprise Edition Release 9.2.0.1.064bitProduction
SELECTnamespace,getsgethitsROUNDGETHITRATIO100gethit_ratiopinspinhits PINHITRATIOpinhit_ratioreloadsinvalidationsFROMNAMESPACEGETSGETHITSGETHIT_RATIOPINSPINHITSPINHIT_RATIORELOADS INVALIDATIONS
--------------- ---------- ---------------------- ---------- ---------- ------------ ---------- -------------
sqlAREA33682494732623718696.861137146337 111350965397.92120249238273
TABLE/PROCEDURE153631061115362639441001591415343159116614199.98855740
BODY14490614399099.3714496914247498.281280
TRIGGER4776537147765105100477653814776511310000
INDEX1104164110370699.961104133110346799.9400
CLUSTER423414203899.28428604226098.600
OBJECT001000010000
PIPE001000010000
JAVASOURCE401947.5401947.500
JAVARESOURCE401947.5401947.500
JAVADATA1167161.2123714762.0300
分析上面的查询，在此仅仅分析sql AREA对象，其余的类似分析
在sql AREA中，执行的次数为次1137146337 (PINS列)。
重载(RELOADS)的次数为1202492，表明一些对象无效或因librarycache过小被agedout，则这些对象被执行了重载。
无效的对象(INVALIDATIONS)为38273次。
基于查询的结果，可以用于判断shared_pool_size的reloads,68)">invalidations的情况，是否调整share_pool_size请参考后面十,68)">十一,68)">十二点
九、数据字典缓存(data dictionary cache)
使用视图v$rowcache获取数据字典缓存的信息
该视图中包含字典对象的定义信息
gets:请求对象的次数
getmisses:在data dictionarycache中请求对象失败的次数
调整目标：避免请求失败
也可根据statspack来调整data dictionary cache
通常情况下，应保证数据字典缓存命中率为95%或高于95%
--下面查询数据字典缓存的命中率与缺失率
(((1-SUMgetmisses)/()+))))*3"Hit Ratio"
()/sum)*"Misses Ratio"
v$rowcache
WHERE+<>0;
HitRatio Misses Ratio
--------- ------------
99.865.135
缺失率应当低于以下百分比
<2%对于常用的数据字典对象
15%整个数据字典缓冲对象
整个数据字典的缺失率
((*)/decode),230)">))),230)">Getmiss_ratio
v$rowcacheGETMISS_RATIO
-------------
.14
不同的组件对象检查组件的缺失率及命中率的情况
parameter
)
)),230)">Hit_Ratio
modificationsupdates
0
GROUPBYORDERGetmiss_ratioDESCHit_RatioPARAMETERGETSGETMISSESGETMISS_RATIOHIT_RATIoUPDATES
------------------------------------------ -------------- ------------- ---------- ----------
dc_qmc_cache_entries1110000
dc_constraints543157.4142.5954
dc_tablespace_quotas97619820.2979.71976
dc_files539325.9494.063
dc_global_oids5640582459.4499.560
dc_histogram_defs185645793223703.1299.880
dc_objects7347032630375.0499.962228
dc_segments11254425150126.0499.962198
dc_sequences78142951453.0299.987814291
关于dc_qmc_cache_entries为100%还不清楚，请大家指正。
十、优化Library cache
总原则尽可能使代码解析最小化
为Librarycache分配更多的空间以避免淘汰最老的代码与执行计划
避免无效的再度解析(如Librarycache已经存在某个对象的解析，而该对象结构发生了变化)
避免Library cache中过多的碎片
为Library cache使用保留空间
锁定一些频繁使用的对象到Librarycache中，以避免LRU算法淘汰掉
排除较大的PL/sql匿名块或对其进行拆分
对于共享服务器模式可以分配largepool给UGA，避免对共享池的争用
十一、调整shared_pool_size
1.监控对象的重载情况
NAMESPACEGETHITSroundPINSPINHITSRELOADSINVALIDATIONS
V$LIBRARYCACHE;--考虑是否存在过多的reloads和invalidations
2.当库缓存的重载率大于零，应考虑增大shared_pool_size
"Executions""CacheMisses while Executing"AS"Reload Ratio,%"ExecutionsCache MisseswhileExecuting Reload Ratio%
---------- -------------------------------------------
27777176251288253.05
3.库缓存的命中率应保持在95%，否则应考虑增大shared_pool_size
(()))*"HitRatio,230)">Executing Hit Ratio---------- ----------------------------------------
2777727542128825799.95
4.估算Library cache占用大小，shared pool的可用空间，总大小
--查看共享池可用空间，当sharedpool有过多的可用空间，再调大shared pool则意义不大
poolnamebytes/10241024v$sgastatnameLIKE'%free memory%'AND='sharedpool'POOLBYTES1024
----------- -----------------------------------------
sharedpool freememory97.6241302
--查询已使用的Library cache大小总和
WITHcteAS(
sharable_memsharable_mem_count--查询非sql语句(包，视图)占用的Library cache大小
v$db_object_cache
UNIONALL
v$sqlarea
sharable_mem_count1024cte--实际上还有一部分为用户游标使用占用的空间，此处略去
SHARABLE_MEM_COUNT---------------------------------
820.59599971771
--查询分配的shared_pool_size的大小
'%shar%'--------------------
1216
v$sgainfo'Shared%' 5.查看shared pool的分配大小，已使用空间，可用空间，已用空间的百分比
columnshared_pool_used format 9999.99
shared_pool_size format 9shared_pool_avail format 9shared_pool_pct format 999.99
a.shared_pool_usedMAXb.valueshared_pool_size))shared_pool_avail100 Shared_pool_per
v$sgastat av$parameterb
IN('table definiti''dictionary cache''library cache''sql area''PL/sql DIANA''shared_pool_size'SHARED_POOL_USEDSHARED_POOL_SIZE SHARED_POOL_AVAIL SHARED_POOL_PER
---------------- --------------------------------- ---------------
965.491152.00186.5183.809699
6.根据上述的各个情况的判断，检查v$shared_pool_advice来判断增加shared_pool_size
shared_pool_size_for_estimate est_sizeshared_pool_size_factorsize_factorestd_lc_sizeestd_lc_memory_objectsobj_cntestd_lc_time_saved_factorsav_factor
v$shared_pool_adviceEST_SIZESIZE_FACTOR ESTD_LC_SIZEOBJ_CNT SAV_FACTOR
--------- ----------- ---------------------- ----------
640.5556642549471
768.6667769807361
896.77788961018601
1024.888910231355361
1152111501679271
12801.111112772004231
14081.222214042341441
15361.333315352570421
16641.444416622708001
17921.555617892822021
19201.666719142941381
20481.777820403065701
21761.888921693171041
2304222993276591
十二、共享池调优工具
1.几个重要的性能视图
v$sgastat
v$librarycache
v$sql
v$sqlarea
v$sqltext
v$db_object_cache
2.几个重要参数
shared_pool_size
open_cursors
session_cached_cursors
cursor_space_for_time
cursor_sharing
shared_pool_reserved_size
3.查询视图获得相关信息
sql_text2executions5orderbyuppersql_text);--查询解析的次数
parse_callsexecutionsv$sqlarea 对于那些相同的sql语句，但不存在于Librarypool，可以查询视图v$sql_shared_cursor来判断v$sql_shared_cursor
为什么没有被共享，以及绑定变量的错误匹配等。
--查询特定对象获得句柄的命中率
gethitratio
v$librarycache
='sql AREA'--查询当前用户正在运行哪些sql语句
users_executingloads
v$sqlarea
v$sqltext
like'select * from scott.emp where %'--收集表的统计信息
executedbms_statsgather_table_stats(---注意此处-表示转义
'SCOTT''EMP');
PLsqlproceduresuccessfully completed.
--通过动态性能视图获得有关share pool size的建议
Shared_Pool_size_for_estimatepool_size
shared_pool_size_factorfactor
estd_lc_size
estd_lc_time_saved
--通过视图v$sql_plan查看执行计划
operation
object_owner
object_name
COST
v$sql_plan
hash_value--sql语句与执行计划的对照
--v$sql中有一列为plan_hash_value与v$sql_plan相互参照
sql_text
v$sql_plan a
JOINv$sql b
ONplan_hash_valueplan_hash_value
object_owner'SCOTT';
原文链接：https://www.f2er.com/oracle/212977.html