【深入解析--eygle】学习笔记 1.2 shared pool原理 Shared Pool是Oracle SGA设置中最复杂也是最重要的一部分内容，Oracle通过Shared Pool来实现SQL共享、减少代码硬解析等，从而提高数据库的性能。在某些版本中，如果设置不当，Shared Pool可能会极大影响数

【深入解析--eygle】学习笔记

1.2 shared pool原理

Shared Pool是Oracle SGA设置中最复杂也是最重要的一部分内容，Oracle通过Shared Pool来实现SQL共享、减少代码硬解析等，从而提高数据库的性能。在某些版本中，如果设置不当，Shared Pool可能会极大影响数据库的正常运行。

在Oracle 7之前，Shared Pool并不存在，每个Oracle连接都有一个独立的Server进程与之相关联，Server进程负责解析和优化所有SQL和PL/SQL代码。典型的，在OLTP环境中，很多代码具有相同或类似的结构，反复的独立解析浪费了大量的时间以及资源，Oracle最终认识到这个问题，并且从PL/SQL开始尝试把这部分可共享的内容进行独立存储和管理，于是Shared Pool作为一个独立的SGA组件开始被引入，并且其功能和作用被逐渐完善和发展起来。

在这里注意到，Shared Pool最初被引入的目的，也就是它的本质功能在于实现共享。如果用户的系统代码是完全异构的（假设代码从不绑定变量，从不反复执行），那么就会发现，这时候Shared Pool完全就成为了一个负担，它在徒劳无功地进行无谓的努力：保存代码、执行计划等期待重用，并且客户端要不停的获取Latch，试图寻找共享代码，却始终一无所获。如果真是如此，那这是我们最不愿看到的情况，Shared Pool变得有害无益。当然这是极端，可是在性能优化中我们发现，大多数性能低下的系统都存在这样的通病：代码极少共享，缺乏或从不实行变量绑定。优化这些系统的根本方法就是优化代码，使代码（在保证性能的前提下）可以充分共享，减少无谓的反复硬/软解析。

实际上，Oracle引入Shared Pool就是为了帮助我们实现代码的共享和重用。了解了这一点之后，我们在应用开发的过程中，也应该有意识地?高自己的代码水平，以期减少数据库的压力。这应该是对开发人员最基本的要求。

Shared Pool主要由两部分组成，一部分是库缓存（Library Cahce），另一部分是数据字典缓存（Data Dictionary Cache）。Library Cache主要用于存储SQL语句、SQL语句相关的解析树、执行计划、PL/SQL程序块（包括匿名程序块、存储过程、包、函数等）以及它们转换后能够被Oracle执行的代码等，这部分信息可以通过v$librarycache视图查询；至于Data Dictionary Cache主要用于存放数据字典信息，包括表、视图等对象的结构信息，用户以及对象权限信息，这部分信息相对稳定，在Shared Pool中通过字典缓存单独存放，字典缓存的内容是按行（Row）存储的（其他数据通常按Buffer存储），所以又被称为Row Cache，其信息可以通过V$ROWCACHE查询。

17:44:15 sys@felix SQL>desc v$librarycache;

Name Null? Type

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

NAMESPACE VARCHAR2(64)

GETS NUMBER

GETHITS NUMBER

GETHITRATIO NUMBER

PINS NUMBER

PINHITS NUMBER

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

17:44:40 sys@felix SQL>

17:44:40 sys@felix SQL>desc v$rowcache;

Name Null? Type

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

CACHE# NUMBER

TYPE VARCHAR2(11)

SUBORDINATE# NUMBER

PARAMETER VARCHAR2(32)

COUNT NUMBER

USAGE NUMBER

FIXED NUMBER

GETS NUMBER

GETMISSES NUMBER

SCANS NUMBER

SCANMISSES NUMBER

SCANCOMPLETES NUMBER

MODIFICATIONS NUMBER

FLUSHES NUMBER

DLM_REQUESTS NUMBER

DLM_CONFLICTS NUMBER

DLM_RELEASES NUMBER

17:50:55 sys@felix SQL>

下图说明了Shared Pool各个部分协同工作以及与Buffer Cache的配合。

从Oracle Database 11g开始，在Shared Pool中划出了另外一块内存用于存储SQL查询的结果集，称为ResultCache Memory。以前Shared Pool的主要功能是共享SQL，减少硬解析，从而?高性能，但是SQL共享之后，执行查询同样可能消耗大量的时间和资源，现在Oracle尝试将查询的结果集缓存起来，如果同一SQL或PL/SQL函数多次执行（特别是包含复杂运算的SQL），那么缓存的查询结果可以直接返回给用户，不需要真正去执行运算，这样就又为性能带来了极大的提升。

1.2.1 Oracle 11g 新特性：Result Cache

结果集缓存（Result Cache）是Oracle Database 11g新引入的功能，除了可以在服务器端缓存结果集（ServerResult Cache）之外，还可以在客户端缓存结果集（Client Result Cache）。

服务器端的Result Cache Memory由两部分组成:

(1) SQL Query Result Cache：存储SQL查询的结果集。

(2) PL/SQL Function Result Cache：用于存储PL/SQL函数的结果集。

Oracle通过一个新引入初始化参数result_cache_max_size 来控制该Cache的大小。如果result_cache_max_size=0 则表示禁用该特性。参数result_cache_max_result 则控制单个缓存结果可以占总的ServerResult Cache大小的百分比。

09:47:20 sys@felix SQL>show parameter result_

NAME TYPE VALUE

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

client_result_cache_lag big integer 3000

client_result_cache_size big integer 0

result_cache_max_result integer 5

result_cache_max_size big integer 1M

result_cache_mode string MANUAL

result_cache_remote_expiration integer 0

09:48:09 sys@felix SQL>

上面显示的参数中result_cache_mode用于控制Server result cache的模式，该参数有3个可选设置。

(1) 设置auto：则优化器会自动判断是否将查询结果缓存。

(2) 设置manual：则需要通过查询提示result_cache来告诉优化器是否缓存结果。

(3) 设置force ：则尽可能地缓存查询结果（通过提示no_result_cache可以拒绝缓存）

09:52:31 scott@felix SQL>create table felix asselect * from dba_objects;

Table created.

09:53:28 scott@felix SQL>alter systemflush SHARED_POOL;

System altered.

09:53:42 scott@felix SQL>alter system flushBUFFER_CACHE;

System altered.

09:54:06 scott@felix SQL>set autot on;

09:54:25 scott@felix SQL>select count(*) fromfelix;

COUNT(*)

----------

75613

Execution Plan

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

Plan hash value: 2587295606

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

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

| 0 |SELECT STATEMENT | | 1 | 301 (1)| 00:00:04 |

| 1 | SORTAGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| FELIX | 63221 | 301 (1)| 00:00:04 |

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

Note

-----

- dynamicsampling used for this statement (level=2)

Statistics

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

70 recursive calls

0 db block gets

1167 consistent gets

1351 physical reads

0 redo size

528 bytes sent via SQL*Net to client

523 bytes received via SQL*Netfrom client

2 SQL*Net roundtrips to/fromclient

5 sorts (memory)

0 sorts (disk)

1 rows processed

09:54:44 scott@felix SQL>

现在再来看看在Server Result Cache下Oracle的行为，首先在result_cache_mode参数设置为MANUAL时：

09:56:02 scott@felix SQL>show parameterresult_cache_mode

NAME TYPE VALUE

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

result_cache_mode string MANUAL

09:56:50 scott@felix SQL>

需要在SQL语句中手工指定Cache，这需要通过加入一个hints来实现，这个hints是result_cache：

09:56:50 scott@felix SQL>select /*+result_cache */ count(*) from felix;

COUNT(*)

----------

75613

Execution Plan

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

Plan hash value: 2587295606

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

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

| 0 |SELECT STATEMENT | | 1 | 301 (1)| 00:00:04 |

| 2 | SORT AGGREGATE | | 1 | | |

| 3 | TABLE ACCESS FULL| FELIX | 63221 | 301 (1)| 00:00:04 |

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

Result Cache Information (identified by operationid):

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

1 -column-count=1; dependencies=(SCOTT.FELIX); attributes=(single-row);name="select /*+ result_cache */ count(*) from felix"

Note

-----

- dynamicsampling used for this statement (level=2)

Statistics

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

4 recursive calls

0 db block gets

1137 consistent gets

1077 physical reads

0 redo size

528 bytes sent via SQL*Net toclient

523 bytes received via SQL*Netfrom client

2 SQL*Net roundtrips to/fromclient

0 sorts (memory)

0 sorts (disk)

1 rows processed

09:58:49 scott@felix SQL>

注意到这个执行计划已经和以往的不同，RESULTCACHE以1hnnwscv2aj3631n497zczt04j名称创建。那么在接下来的查询中，这个Result Cache就可以被利用：

09:58:49 scott@felix SQL>select /*+result_cache */ count(*) from felix;

COUNT(*)

----------

75613

Execution Plan

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

Plan hash value: 2587295606

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

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

| 0 |SELECT STATEMENT | | 1| 301 (1)| 00:00:04 |

| 2 | SORT AGGREGATE | | 1 | | |

| 3 | TABLE ACCESS FULL| FELIX | 63221 | 301 (1)| 00:00:04 |

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

Result Cache Information (identified by operationid):

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

1 - column-count=1;dependencies=(SCOTT.FELIX); attributes=(single-row); name="select /*+result_cache */ count(*) from felix"

Note

-----

- dynamicsampling used for this statement (level=2)

Statistics

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

0 recursive calls

0 db block gets

0 consistent gets

0 physical reads

0 redo size

528 bytes sent via SQL*Net toclient

523 bytes received via SQL*Netfrom client

2 SQL*Net roundtrips to/fromclient

0 sorts (memory)

0 sorts (disk)

1 rows processed

10:01:08 scott@felix SQL>

在这个利用到Result Cache的查询中，consistent gets减少到0，直接访问结果集，不再需要执行SQL查询。这就是Result Cache的强大之处。

在以上测试中，当result_cache_mode设置为MANUAL时，只有使用hints的情况下，Oracle才会利用缓存结果集；而如果将result_cache_mode设置为AUTO，Oracle如果发现缓冲结果集已经存在，那么就会自动使用。但是如果缓冲结果集不存在，Oracle并不会自动进行缓冲，只有使用HINT的情况下，Oracle才会将执行的结果集缓存。

可以通过查询v$result_cache_memory视图来看Cache的使用情况：

10:05:07 scott@felix SQL>select * fromV$RESULT_CACHE_MEMORY where free='NO';

ID CHUNK OFFSET FREE OBJECT_ID POSITION

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

0 0 0 NO 0 0

1 0 1 NO 1 0

10:05:12 scott@felix SQL>

V$RESULT_CACHE_MEMORY

V$RESULT_CACHE_MEMORY displays all the memory blocks and their status.

Column	Datatype	Description
ID	NUMBER	Unique block identifier (that is, the block number)
CHUNK	NUMBER	Chunk to which the block belongs (the upper 27 bits of the ID)
OFFSET	NUMBER	Offset of the block within its chunk (the lower 5 bits of the ID)
FREE	VARCHAR2(3)	Indicates whether the block is free (YES) or not (NO)
OBJECT_ID	NUMBER	Cache object to which the memory block belongs; NULL if the memory block is not allocated to a cache object (FREE = YES)
POSITION	NUMBER	Position of the block in the cached object; NULL if the memory block is not allocated to a cache object (FREE = YES)

通过V$RESULT_CACHE_STATISTICS可以查询Result Cache的统计信息：

10:15:27 scott@felix SQL>select * fromV$RESULT_CACHE_STATISTICS;

IDNAME VALUE

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

1 Block Size (Bytes) 1024

2Block Count Maximum 1024

3Block Count Current 32

4Result Size Maximum (Blocks) 51

5Create Count Success 1

6Create Count Failure 0

7Find Count 1

8Invalidation Count 0

9Delete Count Invalid 0

10Delete Count Valid 0

11Hash Chain Length 1

12Find Copy Count 1

12 rows selected.

10:15:34 scott@felix SQL>

V$RESULT_CACHE_OBJECTS记录了Cache的对象：

10:20:54 scott@felix SQL>SELECT ID,TYPE,NAME,BLOCK_COUNT,ROW_COUNTFROM V$RESULT_CACHE_OBJECTS;

IDTYPE NAME BLOCK_COUNT ROW_COUNT

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

0Dependency SCOTT.FELIX 1 0

1Result select /*+result_cache */ count(*) from 1 1

felix

10:21:19 scott@felix SQL>

V$RESULT_CACHE_OBJECTS displays all theobjects (both cached results and dependencies) and their attributes.

Column	Datatype	Description
ID	NUMBER	Identifier for the cache object (also the ID of the first block)
TYPE	VARCHAR2(10)	Type of the cache object: Result Dependency
STATUS	VARCHAR2(9)	Status of the object: New - Result is still under construction Published - Result is available for use Bypass - Result will be bypassed from use Expired - Result has exceeded expiration time Invalid - Result is no longer available for use
BUCKET_NO	NUMBER	Internal hash bucket for the object
HASH	NUMBER	Hash value for the object
NAME	VARCHAR2(128)	Name (for example, SQL prefix or PL/SQL function name)
NAMESPACE	VARCHAR2(5)	Namespace: SQL PLSQL
CREATION_TIMESTAMP	DATE	Time when the object was created
CREATOR_UID	NUMBER	UID that created the object
DEPEND_COUNT	NUMBER	Number of dependencies (TYPE = Result) or dependents (TYPE = Dependency)
BLOCK_COUNT	NUMBER	Total number of blocks in the cached object
SCN	NUMBER	Build SCN (TYPE = Result) or invalidation SCN (TYPE = Dependency)
COLUMN_COUNT	NUMBER	Number of columns in the cached result^{Foot 1}
PIN_COUNT	NUMBER	Number of active scans on this result^{Footref 1}
SCAN_COUNT	NUMBER	Total number of scans initiated on the cached result^{Footref 1}
ROW_COUNT	NUMBER	Total number of rows in the cached result^{Footref 1}
ROW_SIZE_MAX	NUMBER	Size of the largest row (in bytes)^{Footref 1}
ROW_SIZE_MIN	NUMBER	Size of the smallest row (in bytes)^{Footref 1}
ROW_SIZE_AVG	NUMBER	Average size of a row (in bytes)^{Footref 1}
BUILD_TIME	NUMBER	Amount of time (in hundredths of a second) it took to build the cached result^{Footref 1}
LRU_NUMBER	NUMBER	LRU list position (the smaller the value, the more recent the usage)^{Footref 1}
OBJECT_NO	NUMBER	Dictionary object number of the dependency object^{Foot 2}
INVALIDATIONS	NUMBER	Number of times the object has invalidated its dependents^{Footref 2}
SPACE_OVERHEAD	NUMBER	Overhead (in bytes) for the result^{Footref 1}
SPACE_UNUSED	NUMBER	Unused space (in bytes) for the result^{Footref 1}
CACHE_ID	VARCHAR2(93)	CacheId for the result (object name if it's a dependency)
CACHE_KEY	VARCHAR2(93)	CacheKey for the result (object name if it's a dependency)
DB_LINK^{Foot 3}	VARCHAR2(3)	Possible values: YES: If the result cache object references a remote database object NO: If the result cache object does not reference a remote database object
CHECKSUM^{Footref 3}	NUMBER	Checksum for the result object. The checksum is computed over all the blocks in the result cache object minus the object header.

^{Footnote 1}These columns are only valid for TYPE = Result; otherwise, they are NULL.

^{Footnote 2}These columns are only valid for TYPE = Dependency; otherwise, they are NULL.

^{Footnote 3}This column is available starting with Oracle Database11g Release 2 (11.2.0.4)

Table 7-6 Views and TablesRelated to the Server and Client Result Caches

View/Table	Description
V$RESULT_CACHE_STATISTICS	Lists various server result cache settings and memory usage statistics.
V$RESULT_CACHE_MEMORY	Lists all the memory blocks in the server result cache and their corresponding statistics.
V$RESULT_CACHE_OBJECTS	Lists all the objects whose results are in the server result cache along with their attributes.
V$RESULT_CACHE_DEPENDENCY	Lists the dependency details between the results in the server cache and dependencies among these results.
CLIENT_RESULT_CACHE_STATS$	Stores cache settings and memory usage statistics for the client result caches obtained from the OCI client processes. This statistics table has entries for each client process that is using result caching. After the client processes terminate, the database removes their entries from this table. The client table lists information similar to V$RESULT_CACHE_STATISTICS. See Also: Oracle Database Reference for details about CLIENT_RESULT_CACHE_STATS$
DBA_TABLES, USER_TABLES, ALL_TABLES	Includes a RESULT_CACHE column that shows the result cache mode annotation for the table. If the table has not been annotated, then this column shows DEFAULT. This column applies to both server and client result caching.

一个新的系统包被引入，DBMS_RESULT_CACHE可以用于执行关于Result Cache的管理：

10:21:19scott@felix SQL>set serveroutput on

10:25:30 scott@felix SQL>execdbms_result_cache.memory_report

R e s u l t C a c h e M e m o r y R e p o r t

[Parameters]

Block Size = 1K bytes

Maximum Cache Size = 1M bytes (1K blocks)

Maximum Result Size = 51K bytes (51 blocks)

[Memory]

Total Memory = 165032 bytes [0.096% of the SharedPool]

... Fixed Memory = 5352 bytes [0.003% of theShared Pool]

... Dynamic Memory = 159680 bytes [0.093% of theShared Pool]

....... Overhead = 126912 bytes

....... Cache Memory = 32K bytes (32 blocks)

........... Unused Memory = 30 blocks

........... Used Memory = 2 blocks

............... Dependencies = 1 blocks (1 count)

............... Results = 1 blocks

................... SQL = 1blocks (1 count)

PL/SQL procedure successfully completed.

10:25:49 scott@felix SQL>

1.2.2 Shared Pool 的设置说明

Shared Pool的大小可以通过初始化参数shared_pool_size设置。在Oracle 10g之前在共享池的设置上存在很多不同声音，一方面很多人建议可以把Shared Pool设置得稍大，以充分Cache代码和避免ORA-04031错误的出现；另一方面又有很多人建议不能把Shared Pool设置得过大，因为过大可能会带来管理上的额外负担，从而会影响数据库的性能。

在下面的测试中用到了Shared Pool的转储，所以首先需要了解一下相关的命令。可以通过如下命令转储Shared Pool共享内存的内容：

注意alter session setevents 'immediate trace name heapdump level 2'是一条内部命令，指定Oracle把Shared Pool的内存结构在Level 2级转储出来

Get_trc_scripts.sql

SELECT a.VALUE || b.symbol || c.instance_name ||'_ora_' || d.spid ||

'.trc' trace_file_name

FROM(SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') a,

(SELECT SUBSTR(VALUE, -6, 1) symbol

FROM v$parameter

WHERE NAME = 'user_dump_dest') b,

(SELECT instance_name FROM v$instance) c,

(SELECT spid

FROM v$session s, v$process p, v$mystat m

WHERE s.paddr = p.addr

AND s.SID = m.SID

AND m.statistic# = 0) d;

TRACE_FILE_NAME

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

/u01/app/oracle/diag/rdbms/felix/felix/trace/felix_ora_2751.trc

10:36:03 scott@felix SQL>

Shared Pool通过Free Lists管理free内存块（Chunk），Free的内存块（Chunk）按不同size被划分到不同的部分（Bucket）进行管理；

可以通过下图对Shared Pool的Free List管理进行说明

不同bucket管理的内存块的size范围如下所示（size显示的是下边界）：

[oracle@felix~]$ cat /u01/app/oracle/diag/rdbms/felix/felix/trace/felix_ora_2751.trc | grepbucket

Reservedbucket 0 size=32

Reservedbucket 1 size=4400

Reservedbucket 2 size=8216

Reservedbucket 3 size=8696

Reservedbucket 4 size=8704

Reservedbucket 5 size=8712

Reservedbucket 6 size=8720

Reservedbucket 7 size=9368

Reservedbucket 8 size=9376

Reservedbucket 9 size=12352

Reservedbucket 10 size=12360

Reservedbucket 11 size=16408

Reservedbucket 12 size=32792

Reservedbucket 13 size=65560

Reservedbucket 14 size=1990644

Chunk 0788db800 sz= 968 freeable "vproblem_bucket"

Chunk 0788dbde8 sz= 872 freeable "vproblem_bucket"

Chunk 0788dc370 sz= 872 freeable "vproblem_bucket"

Reservedbucket 0 size=32

Reservedbucket 1 size=4400

Reservedbucket 2 size=8216

Reservedbucket 3 size=8696

Reservedbucket 4 size=8704

Reservedbucket 5 size=8712

Reservedbucket 6 size=8720

Reservedbucket 7 size=9368

Reservedbucket 8 size=9376

Reservedbucket 9 size=12352

Reservedbucket 10 size=12360

Reservedbucket 11 size=16408

Reservedbucket 12 size=32792

Reservedbucket 13 size=65560

Reservedbucket 14 size=1990644

Reservedbucket 0 size=32

Reservedbucket 1 size=4400

Reservedbucket 2 size=8216

Reservedbucket 3 size=8696

Reservedbucket 4 size=8704

Reservedbucket 5 size=8712

Reservedbucket 6 size=8720

Reservedbucket 7 size=9368

Reservedbucket 8 size=9376

Reservedbucket 9 size=12352

Reservedbucket 10 size=12360

Reservedbucket 11 size=16408

Reservedbucket 12 size=32792

Reservedbucket 13 size=65560

Reservedbucket 14 size=1990644

Reservedbucket 0 size=32

Reservedbucket 1 size=4400

Reservedbucket 2 size=8216

Reservedbucket 3 size=8696

Reservedbucket 4 size=8704

Reservedbucket 5 size=8712

Reservedbucket 6 size=8720

Reservedbucket 7 size=9368

Reservedbucket 8 size=9376

Reservedbucket 9 size=12352

Reservedbucket 10 size=12360

Reservedbucket 11 size=16408

Reservedbucket 12 size=32792

Reservedbucket 13 size=65560

Reservedbucket 14 size=1990644

[oracle@felix ~]$

初始地，数据库启动以后，Shared Pool多数是连续内存块。但是当空间分配使用以后,内存块开始被分割，碎片开始出现，Bucket列表开始变长。

Oracle请求Shared Pool空间时，首先进入相应的Bucket进行查找。如果找不到，则转向下一个非空的Bucket，获取第一个Chunk。分割这个Chunk，剩余部分会进入相应的Bucket，进一步增加碎片

最终的结果是，由于不停分割，每个Bucket上的内存块会越来越多，越来越碎小。通常Bucket 0的问题会最为显著，在这个测试数据库上，Bucket 0上的碎片已经达到9030个，而shared_pool_size设置仅为150MB。

通常如果每个Bucket上的Chunk多于2000个，就被认为是Shared Pool碎片过多。Shared Pool的碎片过多，是Shared Pool产生性能问题的主要原因。

碎片过多会导致搜索Free Lists的时间过长，而我们知道，Free Lists的管理和搜索都需要获得和持有一个非常重要的Latch，就是Shared Pool Latch。Latch是Oracle数据库内部提供的一种低级锁，通过串行机制保护共享内存不被并发更新/修改所损坏。Latch的持有通常都非常短暂（通常微秒级），但是对于一个繁忙的数据库，这个串行机制往往会成为极大的性能瓶颈。

如果Free Lists链表过长，搜索这个Free Lists的时间就会变长，从而可能导致Shared Pool Latch被长时间持有，在一个繁忙的系统中，这会引起严重的Shared PoolLatch的竞争。在Oracle 9i之前，这个重要的Shared Pool Latch只有一个，所以长时间持有将会导致严重的性能问题。

1.2.3 Oracle 9i 子缓冲池的增强

从Oracle 9i开始，Shared Pool 可以被分割为多个子缓冲池（SubPool）进行管理，每个SubPool可以被看作是一个Mini Shared Pool，拥有自己独立的Free List、内存结构以及LRU List。同时Oracle?供多个Latch对各个子缓冲池进行管理，从而避免单个Latch的竞争（Shared Pool Reserved Area同样进行分割管理）。SubPool最多可以有7个，Shared Pool Latch也从原来的一个增加到现在的7个。如果系统有4个或4个以上的CPU，并且SHARED_POOL_SIZE大于250MB，Oracle可以把Shared Pool分割为多个子缓冲池（SubPool）进行管理，在Oracle 9i中，每个SubPool至少为128MB。

Oracle 9i中多个子缓冲池的结构示意如图所示：

以下查询显示的是为管理SubPool而新增的子Latch：

select addr, name, gets, misses, spin_gets

fromv$latch_children

where name = 'shared pool';

ADDR NAME GETS MISSES SPIN_GETS

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

00000000601072A0 shared pool 24 0 0

0000000060107200 shared pool 24 0 0

0000000060107160 shared pool 24 0 0

00000000601070C0 shared pool 24 0 0

0000000060107020 shared pool 24 0 0

0000000060106F80 shared pool 24 0 0

0000000060106EE0 shared pool 325942 10 1

7 rows selected.

11:02:08 scott@felix SQL>

但是需要注意的是，虽然多缓冲池技术使Oracle可以管理更大的共享池，但是SubPool的划分可能也会导致各分区之间的协调问题，甚至可能因为内存分散而出现ORA-04031错误。最常见的问题是某个子缓冲池（SubPool）可能出现过度使用，当新的进程仍然被分配到这个SubPool时，可能会导致内存请求失败（而此时其他SubPool可能还有很多内存空间）。

select KSMCHIDX "SubPool",

'sgaheap(' || KSMCHIDX || ',0)' sga_heap,

ksmchcom ChunkComment,

decode(round(ksmchsiz / 1000),

'0-1K',

'1-2K',

'2-3K',

'3-4K',

'4-5K',

'5-6k',

'6-7k',

'7-8k',

'8-9k',

'9-10k',

'> 10K') "size",

count(*),

ksmchcls Status,

sum(ksmchsiz) Bytes

fromx$ksmsp

whereKSMCHCOM = 'free memory'

group byksmchidx,

ksmchcls,

'sga heap(' || KSMCHIDX || ',0)',

ksmchcom,

ksmchcls,

decode(round(ksmchsiz / 1000),

'0-1K',

'1-2K',

'2-3K',

'3-4K',

'4-5K',

'5-6k',

'6-7k',

'7-8k',

'8-9k',

'9-10k',

'> 10K');

因为子缓冲池存在的种种问题，从Oracle 10g开始，Oracle允许内存请求在不同SubPool之间进行切换（Switch），从而?高了请求成功的可能（但是显然切换不可能是无限制的，所以问题仍然可能存在）。

8个子池都被使用，其Latch使用情况如下：

select child#, gets

fromv$latch_children

where name= 'shared pool'

order bychild#;

CHILD# GETS

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

1 343101

2 24

3 24

4 24

5 24

6 24

7 24

7 rows selected.

11:42:25 sys@felix SQL>

1.2.4 Oracle 10g 共享池管理的增强

子缓冲池的分配的算法很简单：

（1）每个子缓冲池必须满足一定的内存约束；

（2）每4颗CPU可以分配一个子缓冲池，最多7个。

在Oracle 9i中，每个SubPool至少128MB，在Oracle10g中，每个子缓冲池至少为256MB。如前所述，SubPool的数量可以通过_kghdsidx_count参数来控制，但是没有参数可以显示地控制SubPool的大小。

不管Oracle 9i中的128MB以及Oracle10g中的256MB，某些情况下，可能需要增加SubPool的大小。可以通过控制Shared Pool大小以及SubPool的数量来改变SubPool的大小。一些Bug以及内部测试表明500MB的SubPool可能会带来更好的性能，所以从Oracle 11g开始，每个SubPool至少为512MB。

除大小控制之外，在Oracle 10g中，Oracle仍然对共享池的管理做出了进一步改进，那就是对单个子缓冲池进行进一步的细分。现在缺省地，Oracle 10g会将单个缓冲池分割为会4个子分区进行管理（这可能是因为通常4颗CPU才分配一个SubPool），使用类似如上的方法在Oracle 10gR2中进行测试：

分析得到的日志，当仅有一个子缓冲时，SharedPool被划分为sga heap(1,0)～sgaheap(1,3)共4个子分区：

[root@felix~]# cat /u01/app/oracle/diag/rdbms/felix/felix/trace/felix_ora_4324.trc | grep"sga heap"

HEAP DUMP heap name="sga heap" desc=0x60001190

HEAP DUMP heap name="sga heap(1,0)" desc=0x60053f70

HEAP DUMP heap name="sga heap(1,1)" desc=0x600557c8

HEAP DUMP heap name="sga heap(1,2)" desc=0x60057020

HEAP DUMP heap name="sga heap(1,3)" desc=0x60058878

[root@felix ~]#

当使用两个子缓冲时，Shared Pool则被划分为8个子分区进行管理;

Oracle 10g中多缓冲池结构示意图如下图所示

通过一个内部表X$KGHLU（[K]ernel [G]eneric memory [H]eap manager State of [L]R[U] OfUnpinned Recreatable chunks）可以查询这些子缓冲池的分配：

11:59:29 sys@felix SQL>selectaddr,indx,kghluidx,kghludur,kghluops,kghlurcr from x$kghlu;

ADDR INDX KGHLUIDX KGHLUDUR KGHLUOPS KGHLURCR

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

00007FA372851098 0 1 0 119290 4425

12:03:45 sys@felix SQL>

通过这一系列的算法改进，Oracle中Shared Pool管理得以不断增强，较好的解决了大Shared Pool的性能问题；Oracle 8i中，过大Shared Pool设置可能带来的栓锁争用等性能问题在某种程度上得以解决。从Oracle10g开始，Oracle开始?供自动共享内存管理，使用该特性，用户可以不必显示设置共享内存参数，Oracle会自动进行分配和调整，虽然Oracle给我们提供了极大的便利，但是了解自动化后面的原理对于理解Oracle的运行机制仍然是十分重要的。