Internals of Shared Pool

Internals of Shared Pool

Purpose and Scope:

This article is intended to provide a inside view and working of Shared Pool and its solution since Oracle 7 version thru Oracle 10g Release 2.

Introduction to Shared Pool

It is fundamentally to serve the metadata cache. Shared pool helps execute SQL and PLSQL.

Efficient metadata caching required to support creation of objects and to be a repository of many sharable components for efficient running of RDBMS engine and PL/SQL engine

Contents of Shared Pool

SQL and PLSQL Objects

SQL Area

V$SQLAREA table contains all the data of this sql area and also provides a list of statistics on shared SQL area and contains one row per SQL string. It provides statistics on SQL statements that are in memory, parsed, and ready for execution.

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.

sql area:PLSQL

sql area:KOKA related to Pseudo code cursor – No document defines or talks about this piece of sql area.

Library cache:

It contains the objects, grants on those objects, dependencies, sequences and synonyms. It also contains the complex metadata that helps to compile the shared SQL and PLSQL

Please query v$librarycache and under namespace column all the following are listed.

BODY

CLUSTER

INDEX

JAVA DATA

JAVA RESOURCE

JAVA SOURCE

OBJECT

PIPE

SQL AREA

TABLE/PROCEDURE

TRIGGER

Row cache:

Please query ‘parameter’ from v$rowcache and the following is listed.

dc_awr_control

dc_constraints

dc_database_links

dc_files

dc_free_extents

dc_global_oids

dc_hintsets

dc_histogram_data

dc_histogram_defs

dc_object_grants

dc_object_ids

dc_objects

dc_outlines

dc_partition_scns

dc_profiles

dc_qmc_cache_entries

dc_qmc_ldap_cache_entries

dc_rollback_segments

dc_segments

dc_sequences

dc_table_scns

dc_tablespace_quotas

dc_tablespaces

dc_used_extents

dc_usernames

dc_users

global database name

kqlsubheap_object

outstanding_alerts

qmtmrcin_cache_entries

qmtmrcip_cache_entries

qmtmrciq_cache_entries

qmtmrctn_cache_entries

qmtmrctp_cache_entries

qmtmrctq_cache_entries

rule_fast_operators

rule_info

rule_or_piece

Active Session History (ASH) buffers

There are new background processes introduced by Oracle in Oracle 10g in R1 and R2. They are

The Memory Monitor Light (MMNL) process is a new process in 10g which works with the Automatic Workload Repository new feature (AWR) to write out full statistics buffers to disk as needed.

The memory monitor (MMON) process was introduced in 10g and is associated with the Automatic Workload Repository new features used for automatic problem detection and self-tuning. MMON writes out the required statistics for AWR on a scheduled basis.

MMON background slave (m000) processes.

MMAN – SGA Background Process: The Automatic Shared Memory Management feature uses a new background process named Memory Manager (MMAN). MMAN serves as the SGA Memory Broker and coordinates the sizing of the memory components. The SGA Memory Broker keeps track of the sizes of the components and pending resize operations

ASH is a circular buffer and is an integral part of shared pool. Oracle Metalink Doc ID Note: 243132.1

Size of ASH Circular Buffer = Max [Min [ #CPUs * 2 MB, 5% of Shared Pool Size, 30MB ], 1MB ]

An ACTIVE session means

01. Present inside a user call

02. Not a recursive session

03. Not waiting for the ‘IDLE’ wait-event

04. If it is a background process, not waiting for its usual timer-event

05. If it is a parallel slave, not waiting for the PX_IDLE wait event.

So will not see any info if a process is waitting for “SQL*Net message from client”.

Get that SQL with this SQL

select SESSION_ID,

NAME,

P1,

P2,

P3,

WAIT_TIME,

CURRENT_OBJ#,

CURRENT_FILE#,

CURRENT_BLOCK#

from v$active_session_history ash,

v$event_name enm

where ash.event#=enm.event#

and SESSION_ID=&SID and SAMPLE_TIME>=(sysdate-&minute/(24*60));

Oracle has provide a utility under $ORACLE_HOME/rdbms/demo (Location may change), by which you can upload the ASH trace dump to a database table and do the analysis.

And many more stuff is stored in the Shared Pool of every Oracle Instance.

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.

How the cache in the shared pool is protected from being over written?

The following listing is to identify the latches listed from v$latchname   LATCH#	NAME
213	shared pool
214	library cache
215	library cache lock
216	library cache pin
217	library cache pin allocation
218	library cache lock allocation
219	library cache load lock
220	library cache hash chains
240	shared pool simulator
241	shared pool sim alloc