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:
, 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.
, particularly when the SELECT statement requires a sort.
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 *
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.
E.g.
/* Formatted on 2011/6/21 10:39:11 (QP5 v5.163.1008.3004) */
SELECT *
;
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).
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).
。Heap Manager
，