一. Shared Pool 概述
在网上搜到一篇介绍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 reques