--=======================================
-- 共享池的调整与优化 (Shared pool Tuning)
--=======================================
共享池 ( Shared pool ) 是 SGA 中最关键的内存片段 , 共享池主要由库缓存 ( 共享 SQL 区和 PL / SQL 区 ) 和数据字典缓存组成。其中库缓存的作用是存
放频繁使用的 sql ， pl / sql 代码以及执行计划。数据字段缓存用于缓存数据字典。在内存空间有限的容量下，数据库系统根据一定的算法决定何
时释放共享池中的 sql ， pl / sql 代码以及数据字典信息。下面逐一解释各个部件并给出调整方案。
一、共享池的组成
Library cache ( 库缓存 ) -- 存放 SQL ,PL/SQL 代码 , 命令块 , 解析代码，执行计划
Data dictionary cache ( 数据字典缓存 ) -- 存放数据对象的数据字典信息
User global area(UGA) for shared server session -- 用于共享模式，可以将该模块移到 lareg pool 来处理。专用模式不予考虑。
二、 Library cache 作用与组成
Library Cache 由以下四个部件组成
Shared SQL areas
Private SQL areas
PL/SQL procedures and packages
Various control structures
Library Cache 作用
存放用于共享的 SQL 命令或 PL / SQL 块
采用 LRU 算法 ( 最近最少使用算法 )
用于避免相同代码的再度解析
ORA - 04031 则表明共享池不够用
三、 Data dictionary cache 组成与作用
组成
Row cache
Library cache
作用
存储数据库中数据文件、表、索引、列、用户和其它数据对象的定义和权限信息
四、 Shared pool 的大小
Library cache 与 Data dictionary cache 两者共同组成了 shared pool 的大小，由参数 shared_pool_size 来决定
查看： show parameter shared_pool_size
修改： alter system set shared_pool_size = 120m ;
sys@ORCL > select * from v$version where rownum < 2 ;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
sys@ORCL > show parameter shared_pool_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size big integer 3M
shared_pool_size big integer 0 -- 为 0 ，表明由系统自动分配
sys@ORCL > show parameter sga_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 176M
sga_target big integer 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 other internal allocations
有关 SGA 的自动管理，更详细请参考： Oracle 10g SGA 的自动化管理
六、 Library pool 共享 SQL , PL / SQL 代码标准
当发布一条 SQL 或 PL / SQL 命令时， Oracle 会自动寻找该命令是否存在于共享池中来决定对当前的语句使用硬解析或软解析。
SQL 语句的执行过程如下：
a . SQL 代码的语法 ( 语法的正确性 ) 及语义检查 ( 对象的存在性与权限 )
b . 将 SQL 代码的文本进行哈希得到哈希值
c . 如果共享池中存在相同的哈希值，则对这个命令进一步判断是否进行软解析，否则到 e 步骤。
d . 对于存在相同哈希值的新命令行，其文本将与已存在的命令行的文本逐个进行比较。这些比较包括大小写，字符串是否一致，空格， 注释等，如果一致，则对其进行软解析，转到步骤 f 。否则到 d 步骤。
e . 硬解析，生成执行计划。
f . 执行 SQL 代码，返回结果。
有关硬解析与软解析请参考： Oracle 硬解析与软解析
七、共享池中闩的竞争
共享池中闩的竞争或 Library cache 闩的竞争表明存在下列情形
非共享的 SQL 需要硬解析
重新解析共享的 SQL ( 由于 Library cache 大小不足导致共享的 SQL 被 LRU 算法淘汰掉 )
过多的负荷导致 Library cache 大小不足
八、 v$librarycache 视图
scott@ORCL > desc v$librarycache ;
Name Null ? Type
----------------------------- -------- --------------
NAMESPACE VARCHAR2 ( 15 ) -- 存储在库缓存中的对象类型 , 值为 SQL area,table/procedure,body,trigger
GETS NUMBER -- 显示请求库缓存中的条目的次数 ( 或语句句柄数 )
GETHITS NUMBER -- 显示被请求的条目存在于缓存中的次数 ( 获得的句柄数 )
GETHITRATIO NUMBER -- 前两者之比
PINS NUMBER -- 位于 execution 阶段，显示库缓存中条目被执行的次数
PINHITS NUMBER -- 位于 execution 阶段，显示条目已经在库缓存中之后被执行的次数
PINHITRATIO NUMBER -- 前两者之比
RELOADS NUMBER -- 显示条目因过时或无效时在库缓存中被重载的次数
INVALIDATIONS NUMBER -- 由于对象被修改导致所有参照该对象的执行计划无效的次数，需要被再次解析
DLM_LOCK_REQUESTS NUMBER
DLM_PIN_REQUESTS NUMBER
DLM_PIN_RELEASES NUMBER
DLM_INVALIDATION_REQUESTS NUMBER
DLM_INVALIDATIONS NUMBER
get 表示请求条目或对象、获得对象句柄；
pin 根据句柄找到实际对象并执行，但对象内容可能因为老化而 pin 不到所以出现 reload ；
一个 session 需要使用一个 object 时，如果是初次使用，则必然是先 get 然后 pin 并维护这个 object 的句柄。下次再使用这个 object 时，因为
已经维护该句柄，所以直接 pin 而没有了 get 过程。如果对象老化则移除共享池，再次请求则会出现 reload 。
有关 Library cache 的详细说明： V$LIBRARY
由上面所列出的字段可知， v$librarycache 视图可以用来监控 library cache 的活动情况。
重点关注字段
RELOADS 列：表示对象被重新加载的次数，理论上该值应该接近于零。过大是由于对象无效或 library pool 过小被换出。
INVALIDATIONS ：列表示对象失效的次数，对象失效后，需要被再次解析。
GETHITRATIO ：该列值过低，表明过多的对象被换出内存。
GETPINRATIO ：该列值过低，表明会话没有多次执行相同的游标，即使对象被不同的会话共享或会话没有找到共享的游标。
下面查询 v$librarycache 的性能状况：
sys@ASMDB > select * from v$version where rownum < 2 ;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64 bit Production
SELECT namespace , gets , gethits , ROUND ( GETHITRATIO * 100 , 2 ) gethit_ratio , pins , pinhits ,
ROUND ( PINHITRATIO * 100 , 2 ) pinhit_ratio , reloads , invalidations FROM v$librarycache ;
NAMESPACE GETS GETHITS GETHIT_RATIO PINS PINHITS PINHIT_RATIO RELOADS INVALIDATIONS
--------------- ---------- ---------- ------------ ---------- ---------- ------------ ---------- -------------
SQL AREA 336824947 326237186 96.86 1137146337 1113509653 97.92 1202492 38273
TABLE / PROCEDURE 1536310611 1536263944 100 1591415343 1591166141 99.98 85574 0
BODY 144906 143990 99.37 144969 142474 98.28 128 0
TRIGGER 47765371 47765105 100 47765381 47765113 100 0 0
INDEX 1104164 1103706 99.96 1104133 1103467 99.94 0 0
CLUSTER 42341 42038 99.28 42860 42260 98.6 0 0
OBJECT 0 0 100 0 0 100 0 0
PIPE 0 0 100 0 0 100 0 0
JAVA SOURCE 40 19 47.5 40 19 47.5 0 0
JAVA RESOURCE 40 19 47.5 40 19 47.5 0 0
JAVA DATA 116 71 61.21 237 147 62.03 0 0
分析上面的查询，在此仅仅分析 SQL AREA 对象，其余的类似分析
a . 在 SQL AREA 中，执行的次数为次 1137146337 (PINS 列 ) 。
b . 重载 ( RELOADS ) 的次数为 1202492 ，表明一些对象无效或因 librarycache 过小被 aged out ，则这些对象被执行了重载。
c . 无效的对象 ( INVALIDATIONS ) 为 38273 次。
d . 基于查询的结果，可以用于判断 shared_pool_size 的 reloads , invalidations 的情况，是否调整 share_pool_size 请参考后面十 , 十一 , 十二点
九、数据字典缓存 ( data dictionary cache )
使用视图 v$rowcache 获取数据字典缓存的信息
该视图中包含字典对象的定义信息
gets : 请求对象的次数
getmisses : 在 data dictionary cache 中请求对象失败的次数
调整目标：避免请求失败
也可根据 statspack 来调整 data dictionary cache
通常情况下，应保证数据字典缓存命中率为 95 % 或高于 95 %
-- 下面查询数据字典缓存的命中率与缺失率
SELECT ROUND ((( 1 - SUM ( getmisses )/( SUM ( gets )+ SUM ( getmisses ))))* 100 , 3 ) "Hit Ratio"
, ROUND ( SUM ( getmisses )/ sum ( gets )* 100 , 3 ) "Misses Ratio"
FROM v$rowcache
WHERE gets + getmisses <> 0 ;
Hit Ratio Misses Ratio
--------- ------------
99.865 .135
缺失率应当低于以下百分比
< 2 % 对于常用的数据字典对象
< 15 % 整个数据字典缓冲对象
整个数据字典的缺失率
SELECT ROUND (( 100 * SUM ( getmisses )/ decode ( SUM ( gets ), 0 , 1 , SUM ( gets ))), 2 ) Getmiss_ratio
FROM v$rowcache ;
GETMISS_RATIO
-------------
.14
不同的组件对象检查组件的缺失率及命中率的情况
SELECT parameter
, SUM ( gets )
, SUM ( getmisses )
, ROUND (( 100 * SUM ( getmisses )/ decode ( SUM ( gets ), 0 , 1 , SUM ( gets ))), 2 ) Getmiss_ratio
, ROUND (( 100 * SUM ( gets - getmisses )/ SUM ( gets )), 2 ) Hit_Ratio
, SUM ( modifications ) updates
FROM v$rowcache
WHERE gets > 0
GROUP BY parameter
ORDER BY Getmiss_ratio DESC , Hit_Ratio DESC ;
PARAMETER SUM ( GETS ) SUM ( GETMISSES ) GETMISS_RATIO HIT_RATIO UPDATES
-------------------------------- ---------- -------------- ------------- ---------- ----------
dc_qmc_cache_entries 1 1 100 0 0
dc_constraints 54 31 57.41 42.59 54
dc_tablespace_quotas 976 198 20.29 79.71 976
dc_files 539 32 5.94 94.06 3
dc_global_oids 564058 2459 .44 99.56 0
dc_histogram_defs 185645793 223703 .12 99.88 0
dc_objects 73470326 30375 .04 99.96 2228
dc_segments 112544251 50126 .04 99.96 2198
dc_sequences 7814295 1453 .02 99.98 7814291
关于 dc_qmc_cache_entries 为 100% 还不清楚，请大家指正。
十、优化 Library cache
总原则尽可能使代码解析最小化
确保用户尽可能使用共享的 SQL 执行计划
为 Library cache 分配更多的空间以避免淘汰最老的代码与执行计划
避免无效的再度解析 ( 如 Library cache 已经存在某个对象的解析，而该对象结构发生了变化 )
避免 Library cache 中过多的碎片
为 Library cache 使用保留空间
锁定一些频繁使用的对象到 Library cache 中，以避免 LRU 算法淘汰掉
排除较大的 PL / SQL 匿名块或对其进行拆分
对于共享服务器模式可以分配 large pool 给 UGA ，避免对共享池的争用
十一、调整 shared_pool_size
1. 监控对象的重载情况
SELECT NAMESPACE ,
GETS ,
GETHITS ,
round ( GETHITRATIO * 100 , 2 ) gethit_ratio ,
PINS ,
PINHITS ,
round ( PINHITRATIO * 100 , 2 ) pinhit_ratio ,
RELOADS ,
INVALIDATIONS
FROM V$LIBRARYCACHE ; -- 考虑是否存在过多的 reloads 和 invalidations
2. 当库缓存的重载率大于零，应考虑增大 shared_pool_size
SELECT SUM ( pins ) "Executions" , SUM ( reloads ) "Cache Misses while Executing" ,
ROUND ( SUM ( reloads )/ SUM ( pins )* 100 , 2 ) AS "Reload Ratio, %" FROM V$LIBRARYCACHE ;
Executions Cache Misses while Executing Reload Ratio , %
---------- ---------------------------- ---------------
2777717625 1288253 .05
3. 库缓存的命中率应保持在 95 % ，否则应考虑增大 shared_pool_size
SELECT SUM ( pins ) "Executions" , SUM ( reloads ) "Cache Misses while Executing" ,
ROUND (( SUM ( pins )/( SUM ( reloads )+ SUM ( pins )))* 100 , 2 )
"Hit Ratio, %" FROM V$LIBRARYCACHE ;
Executions Cache Misses while Executing Hit Ratio , %
---------- ---------------------------- ------------
2777727542 1288257 99.95
4. 估算 Library cache 占用大小， shared pool 的可用空间，总大小
-- 查看共享池可用空间，当 shared pool 有过多的可用空间，再调大 shared pool 则意义不大
SELECT pool , name , bytes / 1024 / 1024 FROM v$sgastat WHERE name LIKE '%free memory%' AND pool = 'shared pool' ;
POOL NAME BYTES / 1024 / 1024
----------- -------------------------- ---------------
shared pool free memory 97.6241302
-- 查询已使用的 Library cache 大小总和
WITH cte AS (
SELECT SUM ( sharable_mem ) sharable_mem_count -- 查询非 SQL 语句 ( 包，视图 ) 占用的 Library cache 大小
FROM v$db_object_cache
UNION ALL
SELECT SUM ( sharable_mem ) -- 查询 SQL 语句占用的 Library cache 大小
FROM v$sqlarea
)
SELECT SUM ( sharable_mem_count )/ 1024 / 1024 -- 查询已使用的 Library cache 大小总和
FROM cte ; -- 实际上还有一部分为用户游标使用占用的空间，此处略去
SUM ( SHARABLE_MEM_COUNT )/ 1024 / 1024
---------------------------------
820.59599971771
-- 查询分配的 shared_pool_size 的大小
SELECT SUM ( bytes )/ 1024 / 1024 FROM v$sgastat WHERE pool LIKE '%shar%' ;
SUM ( BYTES )/ 1024 / 1024
--------------------
1216
SELECT * FROM v$sgainfo WHERE name LIKE 'Shared%' ;
5. 查看 shared pool 的分配大小，已使用空间，可用空间，已用空间的百分比
column shared_pool_used format 9 , 999.99
column shared_pool_size format 9 , 999.99
column shared_pool_avail format 9 , 999.99
column shared_pool_pct format 999.99
SELECT SUM ( a . bytes ) / ( 1024 * 1024 ) shared_pool_used ,
MAX ( b . value ) / ( 1024 * 1024 ) shared_pool_size ,
( MAX ( b . value ) - SUM ( a . bytes )) / ( 1024 * 1024 ) shared_pool_avail ,
( SUM ( a . bytes ) / MAX ( b . value )) * 100 Shared_pool_per
FROM v$sgastat a , v$parameter b
WHERE a . name IN ( 'table definiti' ,
'dictionary cache' ,
'library cache' ,
'sql area' ,
'PL/SQL DIANA' )
AND b . name = 'shared_pool_size' ;
SHARED_POOL_USED SHARED_POOL_SIZE SHARED_POOL_AVAIL SHARED_POOL_PER
---------------- ---------------- ----------------- ---------------
965.49 1 , 152.00 186.51 83.809699
6 . 根据上述的各个情况的判断，检查 v$shared_pool_advice 来判断增加 shared_pool_size
SELECT shared_pool_size_for_estimate est_size ,
shared_pool_size_factor size_factor ,
estd_lc_size ,
estd_lc_memory_objects obj_cnt ,
estd_lc_time_saved_factor sav_factor
FROM v$shared_pool_advice ;
EST_SIZE SIZE_FACTOR ESTD_LC_SIZE OBJ_CNT SAV_FACTOR
--------- ----------- ------------ ---------- ----------
640 .5556 642 54947 1
768 .6667 769 80736 1
896 .7778 896 101860 1
1024 .8889 1023 135536 1
1152 1 1150 167927 1
1280 1.1111 1277 200423 1
1408 1.2222 1404 234144 1
1536 1.3333 1535 257042 1
1664 1.4444 1662 270800 1
1792 1.5556 1789 282202 1
1920 1.6667 1914 294138 1
2048 1.7778 2040 306570 1
2176 1.8889 2169 317104 1
2304 2 2299 327659 1
十二、共享池调优工具
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. 查询视图获得相关信息
-- 查询执行次数小于 5 的 SQL 语句
scott@ORCL > select sql_text from v$sqlarea
2 where executions < 5 order by upper ( sql_text );
-- 查询解析的次数
scott@ORCL > select sql_text , parse_calls , executions from v$sqlarea order by parse_calls ;
对于那些相同的 SQL 语句，但不存在于 Library pool ，可以查询视图 v$sql_shared_cursor 来判断 v$sql_shared_cursor
为什么没有被共享，以及绑定变量的错误匹配等。
-- 查询特定对象获得句柄的命中率
select gethitratio
from v$librarycache
where namespace = 'SQL AREA' ;
-- 查询当前用户正在运行哪些 SQL 语句
select sql_text , users_executing ,
executions , loads
from v$sqlarea
select * from v$sqltext
where sql_text like 'select * from scott.emp where %' ;
-- 收集表的统计信息
scott@ORCL > execute dbms_stats . gather_table_stats (- -- 注意此处 - 表示转义
> 'SCOTT' , 'EMP' );
PL / SQL procedure successfully completed .
-- 通过动态性能视图获得有关 share pool size 的建议
SELECT Shared_Pool_size_for_estimate AS pool_size
, shared_pool_size_factor AS factor
, estd_lc_size
, estd_lc_time_saved
FROM v$shared_pool_advice ;
-- 通过视图 v$sql_plan 查看执行计划
SELECT operation
, object_owner
, object_name
, COST
FROM v$sql_plan
ORDER BY hash_value ;
--SQL 语句与执行计划的对照
--v$sql 中有一列为 plan_hash_value 与 v$sql_plan 相互参照
SELECT a . operation
, object_owner
, object_name
, COST
, b . sql_text
FROM v$sql_plan a
JOIN v$sql b
ON a . plan_hash_value = b . plan_hash_value
WHERE a . object_owner = 'SCOTT'
ORDER BY a . hash_value ;
十三、更多参考
有关闪回特性请参考
Oracle 闪回特性(FLASHBACK DATABASE)
Oracle 闪回特性(FLASHBACK DROP & RECYCLEBIN)
Oracle 闪回特性(Flashback Query 、Flashback Table)
Oracle 闪回特性(Flashback Version 、Flashback Transaction)
有关基于用户管理的备份和备份恢复的概念请参考：
Oracle 基于用户管理恢复的处理 ( 详细描述了介质恢复及其处理 )
有关 RMAN 的恢复与管理请参考：
有关 Oracle 体系结构请参考：
Oracle 实例和Oracle 数据库(Oracle 体系结构)
Oracle 联机重做日志文件(ONLINE LOG FILE)
