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

不念不忘少年蓝@ 2022-05-15 05:04 279阅读 0赞

\--=======================================

\-- 共享池的调整与优化(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 的自动化管理][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 硬解析与软解析][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_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 \- 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;

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 DATABASE]

[Oracle 闪回特性(FLASHBACK DROP & RECYCLEBIN)][Oracle _FLASHBACK DROP _ RECYCLEBIN]

[Oracle 闪回特性(Flashback Query、Flashback Table)][Oracle _Flashback Query_Flashback Table]

[Oracle 闪回特性(Flashback Version、Flashback Transaction)][Oracle _Flashback Version_Flashback Transaction]

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

[Oracle 冷备份][Oracle 1]

[Oracle 热备份][Oracle 2]

[Oracle 备份恢复概念][Oracle 3]

[Oracle 实例恢复][Oracle 4]

[Oracle 基于用户管理恢复的处理][Oracle 5](详细描述了介质恢复及其处理)

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

[RMAN 概述及其体系结构][RMAN]

[RMAN 配置、监控与管理][RMAN 1]

[RMAN 备份详解][RMAN 2]

[RMAN 还原与恢复][RMAN 3]

有关Oracle体系结构请参考：

[Oracle 实例和Oracle数据库(Oracle体系结构)][Oracle _Oracle_Oracle]

[Oracle 表空间与数据文件][Oracle 6]

[Oracle 密码文件][Oracle 7]

[Oracle 参数文件][Oracle 8]

[Oracle 数据库实例启动关闭过程][Oracle 9]

[Oracle 联机重做日志文件(ONLINE LOG FILE)][Oracle _ONLINE LOG FILE]

[Oracle 控制文件(CONTROLFILE)][Oracle _CONTROLFILE]

[Oracle 归档日志][Oracle 10]

[Oracle 10g SGA]: http://blog.csdn.net/robinson_0612/archive/2010/04/27/5534832.aspx
[Oracle]: http://blog.csdn.net/robinson_0612/archive/2011/02/19/6195483.aspx
[V_LIBRARY]: http://hi.baidu.com/bystander1983/blog/item/f701ce9a4d6363bfc9eaf4fe.html
[Oracle _FLASHBACK DATABASE]: http://blog.csdn.net/robinson_0612/archive/2010/12/27/6100429.aspx
[Oracle _FLASHBACK DROP _ RECYCLEBIN]: http://blog.csdn.net/robinson_0612/archive/2010/12/29/6105327.aspx
[Oracle _Flashback Query_Flashback Table]: http://blog.csdn.net/robinson_0612/archive/2011/01/02/6112607.aspx
[Oracle _Flashback Version_Flashback Transaction]: http://blog.csdn.net/robinson_0612/archive/2011/01/02/6112981.aspx
[Oracle 1]: http://blog.csdn.net/robinson_0612/archive/2010/08/05/5791597.aspx
[Oracle 2]: http://blog.csdn.net/robinson_0612/archive/2010/08/16/5816012.aspx
[Oracle 3]: http://blog.csdn.net/robinson_0612/archive/2010/08/05/5791585.aspx
[Oracle 4]: http://blog.csdn.net/robinson_0612/archive/2010/07/27/5768233.aspx
[Oracle 5]: http://blog.csdn.net/robinson_0612/archive/2010/11/28/6041217.aspx
[RMAN]: http://blog.csdn.net/robinson_0612/archive/2010/11/24/6032525.aspx
[RMAN 1]: http://blog.csdn.net/robinson_0612/archive/2010/11/24/6032597.aspx
[RMAN 2]: http://blog.csdn.net/robinson_0612/archive/2010/11/24/6032739.aspx
[RMAN 3]: http://blog.csdn.net/robinson_0612/archive/2010/11/28/6041247.aspx
[Oracle _Oracle_Oracle]: http://blog.csdn.net/robinson_0612/archive/2010/04/26/5529239.aspx
[Oracle 6]: http://blog.csdn.net/robinson_0612/archive/2010/05/20/5611738.aspx
[Oracle 7]: http://blog.csdn.net/robinson_0612/archive/2010/05/20/5611672.aspx
[Oracle 8]: http://blog.csdn.net/robinson_0612/archive/2010/05/05/5559174.aspx
[Oracle 9]: http://blog.csdn.net/robinson_0612/archive/2010/04/29/5542983.aspx
[Oracle _ONLINE LOG FILE]: http://blog.csdn.net/robinson_0612/archive/2010/07/20/5749556.aspx
[Oracle _CONTROLFILE]: http://blog.csdn.net/robinson_0612/archive/2010/07/15/5737527.aspx
[Oracle 10]: http://blog.csdn.net/robinson_0612/archive/2010/08/03/5784713.aspx