一篇不错的介绍Shared Pool的文章(转载).

一篇简单的介绍shared pool 的文章..
http://narashimreddy.wordpress.com/2009/10/30/internals-of-shared-pool/

评论:
比较深入的介绍了PLSQL MCODE 与 PLSQL DIANA
Shared pool 内部内存分配的简要描述.
SQL/PLSQL 游标的使用与解析.以及其工作原理.

PL/SQL MPCODE

The “compiled” form a PL/SQL is called PCODE, which stands for pseudo code, and is interpreted at runtime by the PL/SQL engine. That is, it is not really compiled; it is just pre-compiled. There is also a machine dependent form of pseudo code used in some cases called MPCODE. (Source Steve Adams – 1999 – Questions and Answers)

PL/SQL DIANA

Source: Steve Adams – 1999 Q & A

IDL stands for Interface Definition Language. It is an intermediate language in which the structure of database tables and the logic of PL/SQL program units can be consistently represented as attributed trees. Oracle uses the DIANA IDL, which comes from compilers for the Ada programming language. DIANA stands for Descriptive Intermediate Attributed Notation for Ada. Anyway, this is one of four tables in the data dictionary used to store the DIANA for PL/SQL program units, and the database objects that they reference.

To convert from a human-readable source language (such as PL/SQL) to a machine-readable language (such as m-code) that is efficiently executed. In Oracle, compiling converts PL/SQL into two internal forms, m-code and DIANA, to assist with both efficient execution and dependency management.

Memory Allocation and Release

The SHARED_POOL has at the highest level has 2 kinds of structures. The first structure is PERMANENT structure which is not alterable as has been spawned by Oracle Instance Startup and its stay in the SHARED_POOL is not negotiable.

The second structure is re-creatable and hence is negotiable. The negotiating mechanism is Least Recently Used (LRU) and thus the contents can be aged out and re-loadable. But every re-load has its price to pay.

01. When new objects are referenced they need to be brought into memory and they need memory allocation

02. So re-creatable objects are aged out and pushed out of memory

03. Objects are made up of chunks of memory and when they are created the process checks for contiguous required space

04. The chunks of memory are in 1 and 4 K

05. If the available free memory is not sufficient to create a contiguous required chunk, Oracle throws up error ORA-04031

SQL and PL/SQL objects and cursor usage and parsing

There are two kinds of cursors.

01. One is explicit cursor – created by PL/SQL explicitly in the declarative section and then managed and closed in the executable/run section of the PL/SQL object. For queries that return more than one row, one can explicitly declare a cursor to process the rows individually.

02. PL/SQL implicitly declares a cursor for all SQL data manipulation statements, including queries that return only one row.

03. All the SELECT statements issued are managed by the implicit cursors.

When a cursor is closed, the cursor information is moved into session’s closed cursor cache in User Global Area (UGA). The Shared pool maintains the handles in hashed chains. SESSION_CACHED_CURSORS is the parameter that controls the number. The default value for this in Oracle 10.2.0.2.0 is 0 (zero) and in the later versions, it was said that the same is set to 50.

How it works?

When a cursor is opened, the session process hashes pthe SQL statement and performs a hash lookup in the closed cursor cache in the session memory (UGA) and if found the same is moved to open cursors and then no parsing is required.

If the cursor is not found in the session, the hash value is used to search the hash chains in the shared pool for the cursor handle. This search is registered as hard parse.

If the cursor handle is found and the cursor has not aged out, the cursor is executed. This is a soft parse.

If some part of the cursor has aged out of the shared pool

Or

If the cursor does not exist in the shared pool, then the cursor is reconstructed.

This is called hard parse. The cursor reconstruction requires a lookup of the metadata for the dependent objects such as tables, indexes, extents and sequences.

If the metadata for these objects does not already cached in the shared pool, recursive SQL is generated to fetch the information from the data dictionary.

No related posts.