学会 Shared Pool

原创 Oracle 作者：静以致远√团团时间：2014-07-16 18:22:13 0 删除编辑

学会 Shared Pool

Oracle通过Shared Pool来实现SQL共享，减少代码硬解析，从而提高数据库的性能。

Shared Pool主要由Libiary Cache（库缓存）和Data Dictionary Cache（数据字典缓存）组成，Oracle 11g开始在Shared Pool划分出一块内存用于存储SQL查询的结果集，称为Result Cache Memory。

Data Dictionary Cache

dictionary cache存放了在执行SQL语句过程中，所参照的数据字典的信息，包括SQL语句所涉及的表名、表的列、权限信息等。dictionary cache也叫做row cache，因为这里面的信息都是以数据行的形式存放的，而不是以数据块的形式存放的。对于dictionary cache来说，oracle倾向于将它们一直缓存在shared pool里，不会将它们交换出内存。

当一条SQL语句进入Library Cache时，oracle会到dictionary cache中找与操作对象有关的字典信息，比如表明、表的列、用户权限等信息，如果发现dictionary cache中没有这些信息，则会将system表空间里的数据字典信息调入buffer cache内存，读取内存数据块的数据字典内容，然后将这些读取出来的数据字典内容按照行的形式加入dictionary cache里，从而构造出dc_tables之类的对象，然后从dictionary cache中的行数据中读取有关的列信息放入library cache中。

Library Cache

libiary cache 最主要的功能就是存放用户提交的 SQL语句、SQL 语句相关的解析树（解析树也就是对 SQL 语句中所涉及到的所有对象的展现）、执行计划、用户提交的 PL/SQL 程序块（包括匿名程序块、存储过程、包、函数等）以及它们转换后能够被oracle 执行的代码等。为了对这些内存结构进行管理，还存放了很多控制结构，包括 lock、pin、dependency table 等。

library cache 还存放了很多的数据库对象的信息，包括表、索引等等。有关这些数据库对象的信息都是从 dictionary cache 中获得的。如果用户对 library cache 中的对象信息进行了修改，则这些修改会返回到 dictionary cache 中。

在 library cache 中存放的所有的信息单元都叫做对象，这些对象可以分成两类：一类叫存储对象，也就是上面所说的数据库对象。它们是通过显式的 SQL语句或 PL/SQL 程序创建出来的，如果要删除它们，也必须通过显示的 SQL 命令进行删除。这类对象包括表、视图、索引、包、函数等等；另一类叫做过渡对象，也就是上面所说的用户提的 SQL语句或者提交的 PL/SQL程序块等。这些过渡对象是在执行 SQL 语句或 PL/SQL 程序的过程中产生的，并缓存在内存里。如果实例关闭则删除，或者由于内存不足而被交换出去，从而被删除。

当用户提交 SQL 语句或PL/SQL程序块到oracle的shared pool 以后，在 library cache 中生成的一个可执行的对象，这个对象就叫做游标（cursor）。不要把这里的游标与标准SQL（ANSI SQL）的游标混淆起来了，标准 SQL 的游标是指返回多条记录的SQL形式，需要定义、打开、关闭。下面所说到的游标如无特别说明，都是指 library cache 中的可执行的对象。游标是可以被所有进程共享的，也就是说如果100个进程都执行相同的SQL 语句，那么这100个进程都可以同时使用该SQL 语句所产生的游标，从而节省了内存。每个游标都是由 library cache 中的两个或多个对象所体现的，至少两个对象。一个对象叫做父游标（parent cursor），包含游标的名称以及其他独立于提交用户的信息。从 v$sqlarea 视图里看到的都是有关父游标的信息；另外一个或多个对象叫做子游标（child cursors），如果 SQL 文本相同，但是可能提交 SQL 语句的用户不同，或者用户提交的 SQL 语句所涉及到的对象为同名词等，都有可能生成不同的子游标。因为这些 SQL 语句的文本虽然完全一样，但是上下文环境却不一样，因此这样的 SQL 语句不是一个可执行的对象，必须细化为多个子游标后才能够执行。子游标含有执行计划或者 PL/SQL 对象的程序代码块等。

library cache使用多个 hash bucket 来管理，每个 hash bucket后面都串连着多个句柄（该句柄叫做 library cache object handle），这些句柄描述了 library cache 里的对象的一些属性，包括名称、标记、指向对象所处的内存地址的指针等。

library cache的整体结构：

当一条 SQL 语句进入library cache 的时候，先将 SQL 文本转化为对应 ASCII 数值，然后对该这些 ASCII 数值进行 hash 函数的运算，传入函数的是 SQL 语句的名称（name，对于 SQL 语句来说其 name 就是 SQL 语句的文本）以及命名空间（namespace，对于 SQL语句来说是“SQL AREA”，表示共享游标。可以从视图 v$librarycache 里找到所有的 namespace）。运用hash 函数后得到一个值，该值就是 hash bucket 的号码，从而该 SQL 语句被分配到该号的 hash bucket 里去。实际上，hash bucket 就是通过串连起来的对象句柄才体现出来的，它本身是一个逻辑上的概念，是一个逻辑组，而不像对象是一个具体的实体。oracle 根据 shared_pool_size 所指定的 shared pool 尺寸自动计算 hash buckets 的个数，shared pool越大，则可以挂载的对象句柄就越多。当某个进程需要处理某个对象时，比如处理一条新进入的 SQL 语句时，它会对该 SQL 语句应用 hash 函数算法，以决定其所在的 hash bucket 的编号，然后进入该 hash bucket 进行扫描并比较。有可能会发生该对象的句柄存在，但是句柄所指向的对象已经被交换出内存的情况出现。这时对应的对象必须被再次装载（reload）。也可能该对象的句柄都不存在，这时进程必须重新构建一个对象句柄挂到 hash bucket 上，然后再重新装载对象。SQL 语句相关的对象有很多（最直观的就是 SQL 语句的文本），这些对象都存放在 library cache 里，它们都通过句柄来访问。可以把 library cache理解为一本书，而SQL 语句的对象就是书中的页，而句柄就是目录，通过目录可以快速定位到指定内容的页。

对象句柄存放了对象的名称（name）、对象所属的命名空间（namespace）、有关对象的一些标记（比如对象是否为只读、为本地对象还是远程对象、是否被 pin 在内存中等等）以及有关对象的一些统计信息等。而且，对象句柄中还存放了当前正在 lock 住和pin 住该对象的用户列表、以及当前正在等待 lock 和pin 该对象的用户列表。对象句柄中存放的最重要的内容就是指向 Heap 0 对象的指针了。Heap 0 用来存放与对象有直接关系的一些信息，比如对象类型、对象相关的表（比如依赖表、子表等）、指向对象的其他数据块的指针（这些数据块指向了实际存放 SQL 文本、PL/SQL 代码、错误信息等的大内存块，这些大内存块依次叫做 Heap 1、2、3、4 等）等信息。

Heap 是通过调用服务器进程进行分配的，任何对象都具有 heap 0，至于还应该分配哪些其他的 heap 则是由对象的类型决定的，比如 SQL 游标具有heap 1 和 6，而 PL/SQL程序包则具有 heap 1、2、3 和 4.按照 heap 的使用情况，oracle会在 SGA（library cache）、PGA 或UGA 中分配heap，但是 heap 0 始终都是在 library cache 中进行分配的。如果所请求的 heap 已经在SGA 中分配了，则不会在 PGA中再次分配 heap.Heap 是由一个或多个 chunk 组成的，这些 chunk 可以是分散的分布在 library cache 中的，不需要连续分布。

Heap 0中包含的对象包括：

1）object type：library cache 中的对象类型包括：表、视图、索引、同名词等等。每个对象只能有一个 object type，根据 object type 将对象归类到不同的 namespace 里。一个object type 对应一个 namespace，但是一个 namespace 可能对应多个 object type.这样的话，查找一个对象时，只要在该对象所属的 namespace 中去找就可以了。比较常见的 namespace 包括：

a）SQL AREA：也可以叫做 CRSR，表示 shared cursor，存放共享的 SQL 语句。

b）TABLE/PROCEDURE：存放的 object type 包括：table、view、sequence、synonym、 procedure 的定义、function 的定义以及 package 的定义。

c）BODY：存放 procedure 的实际代码、function 的实际代码以及 package 的实际代码。

d）TRIGGER：存放的 object type为 trigger.

e）INDEX：存放的object type为 index.

2）object name：对象名称由三部分组成：

a）Schema 的名称，对于共享游标（SQL 语句或 PL/SQL 程序块）来说为空。

b）对象名称。分为两种情况：对于共享游标（SQL 语句或 PL/SQL程序块）来说，其对象名称就是 SQL 的语句本身；而对于其他对象（比如表、视图、索引等）就是其在数据字典中的名称。

c） Database link的名称。这是可选的，如果是本地对象，则为空。

这样，对象的名称的格式为：SCHEMA.NAME@DBLINK.比如，可以为 hr.employees@apac.com，也可以为 hr.employees 等。

3） flags：flags 主要用来描述对象是否已经被锁定。对象具有三种类型的 flag：

a）public flag：表示对象上没有锁定（pin）或者latch.

b）status flag：表示对象上存在锁定（pin），说明对象正在被创建或删除或修改等。

c）specitial flag：表示对象上存在 library cache latch.

4）tables：对每个对象，都会维护以下一串 tables 中的若干个：

a）dependency table：含有当前对象所依赖的其他对象。比如一个视图可能会依赖其组成的多个表、一个存储过程可能依赖其中所调用的其他存储过程、一个游标可能依赖其中所涉及到的多个表等。Dependency table 中的每个条目都指向一块物理内存，该物理内存中含有当前对象所依赖的对象的句柄。

b）child table：含有当前对象的子对象，只有游标具有 child table.Child table 中的每个条目都指向一个可执行的 SQL 命令所对应的句柄。

c）translation table：包含当前对象所引用的名称是如何解释为 oracle 底层对象的名称，只有游标具有 translation table.

d）authorization table：包含该对象上所对应的权限，一个条目对应一个权限。

e）access table：对于 dependency table 中的每一个条目，都会在 access table中存在对应的一个或多个条目。比如，假设对象A 依赖对象B，那么在 A的 dependency table 和access table中都会存在一个条目指向 B.位于 access table 中的指向 B 的条目说明了对 B具有什么样的访问类型，从而也就说明了用户要执行 A 则必须具有对 B 的权限。

f）read-only dependency table：类似于 dependency table，但是存放只读的对象。

g）schema name table：包含 authorization table 中的条目所属的 schema.

5）data blocks：对象的其他信息会存放在不同的 heap 中，为了找到这些 heap，会在 heap 0 中存放多个（最多 16 个，但是这 16 个data block不会都用到）data blocks 结构，每个 data block 含有指向这些实际 heap 内存块的指针。

除了 heap 0 以外，还有 11 个 heap，根据对象的不同进行分配，并存放了不同的内容：

1）Heap 1：存放 PL/SQL 对象的源代码。

2）Heap 2：存放 PL/SQL 对象的解析树，这有个好听的名字： DIANA.

3）Heap 3：存放 PL/SQL 对象的伪代码。

4）Heap 4：存放 PL/SQL 对象的基于硬件的伪代码。

5）Heap 5：存放了编译时的错误信息。

6）Heap 6：存放了共享游标对象的 SQL 文本。

7）Heap 7：可用空间。

8）Heaps 8–11：根据对象的不同而使用的子 heap.

可以通过查询 v$db_object_cache 来显示 library cache 中有哪些对象被缓存，以及这些对象的大小：

1 select *

2 from (select row_number() over(partition by namespace order by sharable_mem desc) size_rank,

3 namespace,

4 sharable_mem,

5 substr(name, 1, 50) name

6 from v$db_object_cache

7 order by sharable_mem desc)

8 where size_rank <= 3

9* order by namespace, size_rank

SQL> /

SIZE_RANK NAMESPACE SHARABLE_MEM NAME

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

1 BODY 375759 DBMS_RCVMAN

2 BODY 218914 MGMT_JOB_ENGINE

3 BODY 145151 DBMS_ISCHED

1 CLUSTER 356 SMON_SCN_TO_TIME

2 CLUSTER 355 C_TOID_VERSION#

3 CLUSTER 354 C_OBJ#_INTCOL#

SHARED POOL 的内存结构：

1. shared pool从物理结构上来看，是由许多内存块组成。这些内存块称之为chunk。

2. Chunk是shared pool中内存的最小单位。

3. Chunk中所有的内存都是连续的。

CHUNK的可属性：

1. 当chunk属于可用类型的时候，它既不属于library cache，也不属于dictionary cache.

2. 这些chunk可以分为四类：

free(可以不受限制地被分配)。

recr（需要时被移走或者重建，共享sql语句的chunk的类型就是recr）

freeabl(曾经被session使用过，随后会被完全或部分释放。这样的chunk不能临时被移走)

perm（这样的chunk包含永久的对象，这样的chunk中的可用空间在需要的时候可以被移走）。

CHUNK链表：

1. 在shared pool中可用的chunk会串起来成为可用链（free list）,也称之为bucket.

2. 可用的bucket,被分为254个。

3. 当一个进程需要shared pool里的一个chunk时，该进程首先会在符合所需空间的bucket上扫描，找到一个合适的chunk。

如果找的chunk尺寸比需要的尺寸要大，该chunk就会被拆分成2个chunk，一个chunk用来存放数据,一个则成为free类型的chunk并被挂在当前的该bucket。

如果当前bucket上没有符合chunk,就会从下一个非空bucket里找，所里的bucket都不符合，就会扫描已经使用的recreatable类型的chunk的链表。从该链表上释放一部分的chunk.

4. 当shared pool找不到足够大小的所需内存的时候，就会发出ora-4031的错误信息。当我们查询v$sgastat里的可用空间时，可能发现name 为free memory可用空间还足够大，但是还是报错，是因为在发出ora-4031错误的时候，已经释放不少内存。但是在这些内存当中的chunk，没有一个chunk可以提供连续的的物理内存满足所需要的空间。

5. Shared pool latch的作用：

对bucket的扫描、管理、分配chunk等操作都是在shared pool latch 的保护进行。

如果shared pool含有数量巨大的非常小的free类型的chunk，则在扫描bucket的时候，shared pool latch会被锁定很长时间。这是ORACLE 8i以前shared pool latch争夺的主要原因。加大shared pool 只能延缓，以后会越来越严重。Oracle 9i以后，减少chunk的尺寸递增的幅度，加大了可用chunk链表的数量。效率大大提高了。

6. latch的作用：

所谓latch,就是轻量级的锁。每个进程往内存块写入的数据之前，必须获得一个latch,写完后释放latch.

7. Shared pool 组件之间的关系：

当某条SQL语句进入library cache的时候，会在library cache找相关的数据字典信息。（eg:表名、表的列）。如果没有，会将system表空间里的数据字典内容按照行的形式放入dictionary cache里，从了构造出dc_table之类的对象。再从dictionary cache的行数据取出相关的信息放入librarary cache.

8. Shared_pool_reserved_size的作用：

1. Shared_pool_reserved_size是为非常大的对象单独保留一块区域空间。而不是从这个可用的chunk链表中来分配空间的。这个块保留区域与正常chunk管理完全分开。

2. 一般Shared_pool_reserved_size的大小为shared pool 的5%。

3. Shared_pool_reserved_size的这快区域的可用chunk不会挂在bucket上。这块保留区域的使用情况从视图V$shared_pool_reserved中看出。该视图中的request _misses字段显示出无法从保留区域里获得足够大的chunk的总次数。该字段应该尽量为0。

Result Cache

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

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

SQL Query Result Cache：存储SQL查询的结果集

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

该Cache的大小由参数result_cache_max_size控制，如果该参数的值为0则禁用该功能；参数resul_cache_max_result用来控制单个缓存结果占用总的Server Result Cache大小的百分比。

SQL> select * from v$version where rownum=1;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

SQL> show parameter result_cache;

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

其中result_cache_mode用来控制Server result cache的模式：

auto：优化器自动判断是否将查询结果缓存

manual：需要通过查询提示result_cache来告诉优化器是否缓存结果

force：尽可能的缓存查询结果（通过提升no_result_cache可以拒绝缓存）

来开一下使用和不适用Result Cache的区别：

SQL> set autotrace on;

SQL> select count(*) from scott.emp;

COUNT(*)

----------

统计信息如下：

Statistics

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

1542 recursive calls

2 db block gets

301 consistent gets

39 physical reads

0 redo size

422 bytes sent via SQL*Net to client

419 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

40 sorts (memory)

0 sorts (disk)

1 rows processed

再一次执行相同SQL看一下统计信息：

SQL> select count(*) from scott.emp;

COUNT(*)

----------

Statistics

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

0 recursive calls

0 db block gets

1 consistent gets

0 physical reads

0 redo size

422 bytes sent via SQL*Net to client

419 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

看看使用Server Result Cache后的统计：

SQL> alter system flush buffer_cache; //测试环境，生成环境中小心执行这两条命令

System altered.

SQL> alter system flush shared_pool;

System altered.

SQL> show parameter result_cache_mode;

NAME TYPE VALUE

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

result_cache_mode string MANUAL

由于mode是manual，在使用Result Cache时需要手工执行Cache

SQL> select /*+ result_cache */count(*) from scott.emp;

COUNT(*)

----------

Execution Plan

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

Plan hash value: 2937609675

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

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

| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |

| 2 | SORT AGGREGATE | | 1 | | |

| 3 | INDEX FULL SCAN| PK_EMP | 14 | 1 (0)| 00:00:01 |

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

Result Cache Information (identified by operation id):

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

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

Statistics

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

1106 recursive calls

0 db block gets

225 consistent gets

28 physical reads

0 redo size

422 bytes sent via SQL*Net to client

419 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

34 sorts (memory)

0 sorts (disk)

1 rows processed

此处注意创建了名为1ts4m7fpf7cu6amj9m94np2d7s的RESULT CACHE，再次查询

SQL> select /*+ result_cache */count(*) from scott.emp;

COUNT(*)

----------

Execution Plan

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

Plan hash value: 2937609675

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

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

| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |

| 2 | SORT AGGREGATE | | 1 | | |

| 3 | INDEX FULL SCAN| PK_EMP | 14 | 1 (0)| 00:00:01 |

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

Result Cache Information (identified by operation id):

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

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

Statistics

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

0 recursive calls

0 db block gets

0 consistent gets

0 physical reads

0 redo size

422 bytes sent via SQL*Net to client

419 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

会发现在使用Result Cache的情况下第二次执行相同的SQL时consistent gets（数据请求总数在回滚段Buffer中的数据一致性读所需要的数据块）也变成了0，直接访问结果集，不再执行SQL查询。此次用的emp表仅有14条数据，如果表中的数据多一些收益会更大。

使用v$result_cache_memory视图查看Cache的使用情况

SQL> select * from v$result_cache_memory;

ID CHUNK OFFSET FRE OBJECT_ID POSITION

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

0 0 0 NO 0 0

1 0 1 NO 1 0

2 0 2 YES

3 0 3 YES

4 0 4 YES

5 0 5 YES

6 0 6 YES

7 0 7 YES

8 0 8 YES

9 0 9 YES

10 0 10 YES

通过v$result_cache_statistics查询Result Cache的统计信息

SQL> select * from v$result_cache_statistics;

ID NAME VALUE

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

1 Block Size (Bytes) 1024

2 Block Count Maximum 1024

3 Block Count Current 32

4 Result Size Maximum 51

(Blocks)

5 Create Count Success 1

6 Create Count Failure 0

7 Find Count 4

8 Invalidation Count 0

9 Delete Count Invalid 0

v$result_cache_objects视图查看Cache对象信息

SQL> select id,type,name,block_count,row_count,cache_id from v$result_cache_objects;

ID TYPE NAME BLOCK_COUNT ROW_COUNT CACHE_ID

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

0 Dependency SCOTT.EMP 1 0 SCOTT.EMP

1 Result select /*+ result_cache */ 1 1 1ts4m7fpf7cu6amj9m94np2d7s

count(*) from scott.emp

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29320885/viewspace-1220670/，如需转载，请注明出处，否则将追究法律责任。

上一篇：搞懂buffer cache

下一篇：使用AWR生成报表

请登录后发表评论登录

全部评论

静以致远√团团

专家姓名：薛团团

每个人都有梦想，去实现吧！

注册时间：2013-11-14

博文量
164
访问量

2103642

ITPub博客

学会 Shared Pool