mysql共享池_共享池的调整与优化(Shared pool Tuning)

--=======================================

--共享池的调整与优化(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

修改：altersystem set shared_pool_size=120m;

sys@ORCL> select * from v$version where rownum 

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

sys@ORCL> show parameter shared_pool_

NAMETYPEVALUE

------------------------------------ ----------- ------------------------------

shared_pool_reserved_sizebig integer 3M

shared_pool_sizebig integer 0--为0，表明由系统自动分配

sys@ORCL> show parameter sga_

NAMETYPEVALUE

------------------------------------ ----------- ------------------------------

sga_max_sizebig integer 176M

sga_targetbig 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

六、Library pool共享SQL,PL/SQL代码标准

当发布一条SQL或PL/SQL命令时，Oracle会自动寻找该命令是否存在于共享池中来决定对当前的语句使用硬解析或软解析。

SQL语句的执行过程如下：

a.SQL代码的语法(语法的正确性)及语义检查(对象的存在性与权限)

b.将SQL代码的文本进行哈希得到哈希值

c.如果共享池中存在相同的哈希值，则对这个命令进一步判断是否进行软解析，否则到e步骤。

d.对于存在相同哈希值的新命令行，其文本将与已存在的命令行的文本逐个进行比较。这些比较包括大小写，字符串是否一致，空格，注释等，如果一致，则对其进行软解析，转到步骤f。否则到d步骤。

e.硬解析，生成执行计划。

f.执行SQL代码，返回结果。

有关硬解析与软解析请参考：

七、共享池中闩的竞争

共享池中闩的竞争或Library cache闩的竞争表明存在下列情形

非共享的SQL需要硬解析

重新解析共享的SQL(由于Library cache大小不足导致共享的SQL被LRU算法淘汰掉)

过多的负荷导致Library cache大小不足

八、v$librarycache视图

scott@ORCL > desc v$librarycache;

NameNull?Type

----------------------------- -------- --------------

NAMESPACEVARCHAR2(15)--存储在库缓存中的对象类型,值为SQL area,table/procedure,body,trigger

GETSNUMBER--显示请求库缓存中的条目的次数(或语句句柄数)

GETHITSNUMBER--显示被请求的条目存在于缓存中的次数(获得的句柄数)

GETHITRATIONUMBER--前两者之比

PINSNUMBER--位于execution阶段，显示库缓存中条目被执行的次数

PINHITSNUMBER--位于execution阶段，显示条目已经在库缓存中之后被执行的次数

PINHITRATIONUMBER--前两者之比

RELOADSNUMBER--显示条目因过时或无效时在库缓存中被重载的次数

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$librarycache视图可以用来监控library cache的活动情况。

重点关注字段

RELOADS列：表示对象被重新加载的次数，理论上该值应该接近于零。过大是由于对象无效或library pool过小被换出。

INVALIDATIONS：列表示对象失效的次数，对象失效后，需要被再次解析。

GETHITRATIO：该列值过低，表明过多的对象被换出内存。

GETPINRATIO：该列值过低，表明会话没有多次执行相同的游标，即使对象被不同的会话共享或会话没有找到共享的游标。

下面查询v$librarycache的性能状况：

sys@ASMDB > select * from v$version where rownum 

BANNER

----------------------------------------------------------------

Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production

SELECT namespace,gets,gethits,ROUND(GETHITRATIO*100,2) gethit_ratio,pins,pinhits,

ROUND(PINHITRATIO*100,2) pinhit_ratio,reloads,invalidations FROM v$librarycache;

NAMESPACEGETSGETHITS GETHIT_RATIOPINSPINHITS PINHIT_RATIORELOADS INVALIDATIONS

--------------- ---------- ---------- ------------ ---------- ---------- ------------ ---------- -------------

SQL AREA33682494732623718696.86 1137146337 111350965397.92120249238273

TABLE/PROCEDURE 1536310611 1536263944100 1591415343 159116614199.98855740

BODY14490614399099.3714496914247498.281280

TRIGGER4776537147765105100477653814776511310000

INDEX1104164110370699.961104133110346799.9400

CLUSTER423414203899.28428604226098.600

OBJECT001000010000

PIPE001000010000

JAVA SOURCE401947.5401947.500

JAVA RESOURCE401947.5401947.500

JAVA DATA116 7161.2123714762.0300

分析上面的查询，在此仅仅分析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;

PARAMETERSUM(GETS) SUM(GETMISSES) GETMISS_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

总原则尽可能使代码解析最小化

确保用户尽可能使用共享的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

FROMV$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, %

---------- ---------------------------- ---------------

27777176251288253.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, %

---------- ---------------------------- ------------

2777727542128825799.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';

POOLNAMEBYTES/1024/1024

----------- -------------------------- ---------------

shared pool free memory97.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$sgainfoWHERE 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

FROMv$sgastat a, v$parameter b

WHEREa.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.491,152.00186.5183.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

FROMv$shared_pool_advice;

EST_SIZE SIZE_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.查询视图获得相关信息

--查询执行次数小于5的SQL语句

scott@ORCL> select sql_text from v$sqlarea

2where executions 

--查询解析的次数

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;

十三、更多参考

有关闪回特性请参考

有关基于用户管理的备份和备份恢复的概念请参考：

有关RMAN的恢复与管理请参考：

有关Oracle体系结构请参考：