一. Shared Pool 概述

在之前的blog对Oracle 的内存架构也做了一个概述，参考：

Oracle 内存 架构 详解

在网上搜到一篇介绍shared pool 非常详细的pdf资料。 原文链接以找不到，但还是要感谢作者Kamus的辛勤劳动。

重新整理了一下，链接如下：

IINTRODUCT IION

            What is shared pool? This first query that comes,let us have a brief introduction regarding shared pool here first.Most of the people knows that shared pool is the part of System Global Area (SGA) it’s true but little else, What exactly the shared pool?

            Shared pool are contain lots of key memory areas of Oracle and in Instance tuning the major area that we have to tune is shared pool if shared pool defined improperly the overall database performance will suffer.

            Majority shared pool related to the two part of SGA one is fixed are which is relatively constant to a oracle instance for a particular version and the second part is Variable area which gradually shrink and grow for user and application requirement.

Now we should do a close look of various component of Shared Pool

Basically Shared Pool could be divided in three major parts:

            1. Library Cache

            2. Dictionary Cache

            3. Control Structure

二.  Library Cache

            Memory Part where all the SQL and PL/SQL statement executed, all execution plan reside here for SQL statement stored here.

We can further subdivide this Library Chache into:

            1. Shared and Private SQL Area

            2. PL/SQL Procedure Part

2.1 Shared and Private SQL Area

            A shared SQL area contains the parse tree and execution plan for a single SQL statement, or for similar SQL statements. Oracle saves memory by using one shared SQL area for multiple similar DML statements, particularly when many users execute the same application.

            A shared SQL area is always in the shared pool. Oracle allocates memory from the shared pool when a SQL statement is parsed; the size of this memory depends on the complexity of the statement. If a SQL statement requires a new shared SQL area and the entire shared pool has already been allocated, Oracle can deallocate items from the pool using a modified least Recently used algorithm until there is enough free space for the new statement's shared SQL area.

            A private SQL area contains data such as bind information and runtime buffers. Each session that issues a SQL statement has a private SQL area. Each user that submits an identical SQL statement has his or her own private SQL area that uses a single shared SQL area; many private SQL areas can be associated with the same shared SQL area.

A private SQL area has a persistent area and a runtime area:

            （1）The persistent area contains bind information that persists across executions, code for datatype conversion (in case the defined datatype is not the same as the datatype of the selected column), and other state information (like recursive or remote

cursor numbers or the state of a parallel query). The size of the persistent area depends on the number of binds and columns specified in the statement. For example, the persistent area is larger if many columns are specified in a query.

            （2）The runtime area contains information used while the SQL statement is being executed. The size of the runtime area depends on the type and complexity of the SQL statement being executed and on the sizes of the rows that are processed by the statement. In general, the runtime area is somewhat smaller for INSERT, UPDATE and DELETE statements than it is for SELECT statements, particularly when the SELECT statement requires a sort.

            Oracle creates the runtime area as the first step of an execute request. For INSERT, UPDATE, and DELETE statements, Oracle frees the runtime area after the statement has been executed.  For queries, Oracle frees the runtime area only after all rows are fetched or the query is cancelled.

            The location of a private SQL area depends on the type of connection established for a session. If a session is connected via a dedicated server, private SQL areas are located in the user's PGA. However, if a session is connected via the multithreaded server, the persistent areas and, for SELECT statements, the runtime areas, are kept in the SGA (x$ksmms) table provide the runtime information regarding SQL area in Library Cache which is suppose to be allocated to a particular Oracle Instance。

E.g.

/* Formatted on 2011/6/21 10:18:48 (QP5 v5.163.1008.3004) */

SELECT *

  FROM X$KSMSS

 WHERE KSMSSNAM = 'sql_area' AND KSMSSLEN 0;

2.2 PL/SQL Procedure Part

            Oracle processes PL/SQL program units (procedures, functions, packages, anonymous blocks, and database triggers) much the same way it processes individual SQL statements.

            Oracle allocates a shared area to hold the parsed, compiled form of a program unit. Oracle allocates a private area to hold values specific to the session that executes the program unit, including local, global, and package variables (also known as package instantiation) and buffers for executing SQL. If more than one user executes the same program unit, then a single, shared area is used by all users, while each user maintains a separate copy of his or her private SQL area, holding values specific to his or her session.

            Individual SQL statements contained within a PL/SQL program unit are processed as described in the previous sections. Despite their origins within a PL/SQL program unit, these SQL statements use a shared area to hold their parsed representations and a private area for each session that executes the statement.

            (x$ksmms) table provide the runtime information regarding PL/SQL area in Library Cache which is suppose to be allocated to a particular Oracle Instance 。

E.g.

/* Formatted on 2011/6/21 10:39:11 (QP5 v5.163.1008.3004) */

SELECT *

  FROM X$KSMSS

 WHERE KSMSSNAM LIKE 'PL/SQL%' AND KSMSSLEN 0;

PL/SQL MPCODE stands for machine dependent pseudocode.

PL/SQL DIANA stands for the PL/SQL code size in the shared pool at runtime.

2.3  Library Cache Manager

            The main purpose of the library cache is to provide a mechanism to locate and store any library cache object quickly. A hashing mechanism is used to locate a handle, which contains the identity (name) of the object. The library cache handle then points us to one or more the library cache objects and their contents.

            The library cache caches different types of library objects (e.g. packages, procedures, functions, shared cursors, anonymous PL/SQL blocks, table definitions, view definitions, form definitions).

            Library cache memory is allocated out of the top most heap or the generic SGA heap. When the library cache, KGL, needs more memory, it will call the heap manager (KGH) to allocate it. The library cache consists of a hash table, which consists of an array of hash buckets. Each hash bucket is a doubly linked list of library cache object handles. Each library cache object handle points to a library cache object and has a reference list. The library cache object is further broken down into other components such as a dependency table, a child table, and an authorisation table (to name a few).

KGH Heap Manager 说明：

            Shared pool和PGA都是由一个Oracle的内存管理器来管理，我们称之为KGH heap manager。Heap Manager不是一个进程，而是一串代码。Heap Manager主要目的就是满足server 进程请求memory 的时候分配内存或者释放内存。        Heap Manager在管理PGA的时候，Heap Manager需要和操作系统来打交道来分配或者回收内存。但是呢，在shared pool中，内存是预先分配的，Heap Manager管理所有的空闲内存。

            当某个进程需要分配shared pool的内存的时候，Heap Manager就满足该请求，Heap Manager也和其他ORACLE模块一起工作来回收shared pool的空闲内存。

您可能感兴趣的文章: