oracle性能调优之--Share Pool 调整与优化

oracle性能调优之--Share Pool 调整与优化

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

-- 共享池的调整与优化(Sharedpool 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 sharedserver session --用于共享模式，可以将该模块移到laregpool来处理。专用模式不予考虑。

二、Library cache 作用与组成

Library Cache 由以下四个部件组成

Shared SQL areas

Private SQL areas

PL/SQL proceduresand packages

Various controlstructures

Library Cache 作用

存放用于共享的SQL命令或PL/SQL块

采用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

修改：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 biginteger 3M

shared_pool_size biginteger 0 --为0，表明由系统自动分配

sys@ORCL> show parameter sga_

NAME TYPE VALUE

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

sga_max_size biginteger 176M

sga_target biginteger 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 的自动化管理

六、Librarypool 共享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(由于Librarycache大小不足导致共享的SQL被LRU算法淘汰掉)

过多的负荷导致Librarycache 大小不足