WHAT DOES SHARED POOL CONTAIN?
A number of components are fixed in size and space is allocated for them at instance startup. There is usually very little that the DBA can do with regard to these permanent allocations.
- Session arrays
- Process arrays
- Segmented arrays
- Other static structures
Generally what is of far greater ineterst to the DBA, are the allocations of memory that can be aged in and out of the cache since building new objects in the shared pool is expensive and impacts scalability and performance. Memory allocations for objects that can be rebuilt are referred to as ‘recreatable’.
Various recreatable components in shared pool are:
2. PL/SQL : Executable representation of PL/SQL packages ,procedures and functions that may be used repeatedly by many sessions.It also holds stored trigger code.
3. Objects of various types required to parse and execute SQL statements including tables, ,indexes, types, methods etc. and java classes.
The LC will maintain the relationships between tables and SQL statements (more specifically child cursors). This way if the table is altered, Oracle knows which child cursors and cursors to invalidate.
The LC handles point to memory that contains information about procedures, functions, tables, views, synonyms, cursors and child cursors.
The LC references and contains meta data about Oracle objects. Not all objects defined in Oracle’s data dictionary, but only those that have been recently referenced ) and have some cached information. V$DB_OBJECT_CACHE Reports(Namespace, name, sharable_mem) all objects currently in the library cache.
– Row Cache or The dictionary cache
– Result cache
HOW IS MEMORY ALLOCATED IN SHARED POOL?
Usually these individual objects are not large but they can grow to be several MB based on the object type and its defining attributes for example , a package body containing many procedures is likely to consume more space than a simple select statement.
Each individual object is not comprised of single large allocation but is further partitioned into independent memory allocations called ‘heaps’. The no. of heaps for an object depends on the object type. For example, a SQL cursor has 2 heaps : a smaller heap for the library cache metadata and a larger heap containing the executable representation of the cursor (usually called sqlarea). Although heaps themselves may be pretty large, each heap is itself comprised of one or more chunks of memory of standard allocation sizes . The standard allocation sizes reduce the number of problems arising from memory fragmentation.
When an object is allocated memory, the memory is not allocated as a single contiguous area . Rather it is in the form of one or more chunks of memory. The chunks comprising an object may not be contiguously located, but the memory within each chunk must be contiguous. This chunking enables large objects (over 5KB) to be loaded into the cache without requiring a single contiguous area. In this way, the database reduces the possibility of running out of enough contiguous memory due to fragmentation.
The most prevalent chunk sizes in use are 1K and 4K creating more uniform memory allocations. As a result, at a steady state the same kinds of objects and chunk sizes are allocated and aged out. This allocation method reduces fragmentation and total memory usage. Note that smaller units of allocation may be used for components like row cache.
Large allocations (over 5KB) are usually made by the shared server, parallel query, or RMAN. Oracle recommends segregating the SGA memory used by these features by configuring a distinct memory area, called the large pool.
Infrequently, Java, PL/SQL or SQL cursors may make allocations out of the shared pool that are larger than 4KB. If a large memory allocation exceeding 5K is requested , Oracle might have to age out a large no. of objects to satisfy that request and this operation could hold the latch resource for detectable periods of time, causing minor disruption to other concurrent attempts at memory allocation. To allow these allocations to occur most efficiently, an area of shared pool is set aside and only used for allocations larger than 4K .This is known as reserved pool and by default it occupies 5% of the shared pool size. Reserved pool will be used only if insufficient space is available in shared pool. Allocations made from the reserved pool are made as a large contiguous space of the size of the objects unlike shared pool where allocations are made in chunks of 1K or 4K. Thus, reserved pool makes allocation of large chunks more efficient. . Small allocations are not allowed in reserved area. Hence, smaller objects will not fragment the reserved list, helping to ensure the reserved list will have large contiguous chunks of memory. The database can use this memory for operations such as PL/SQL and trigger compilation or for temporary space while loading Java objects. After the memory allocated from the reserved pool is freed, it returns to the reserved pool.
Whenever an object is to be allocated memory in the shared pool, memory to be allocated for chunks has to be contiguous. If requested amount of contiguous memory is not available, the heap manager iterates through the shared pool LRU list attempting to free space (by aging out the objects) to create a contiguous chunk of the requested size. It may be possible that ORA-4031 may be raised even though we have a large amount of free space available simply because the available free memory will not yield a contiguous extent large enough to satisfy the current request. If ASMM/AMM is enabled, an additional granule of memory is requested if sufficient contiguous memory can’t be found.
HOW ARE OBJECTS ORGANIZED IN SHARED POOL?
In the similar manner, pointers to the cursors are placed in the doubly linked lists called hash chains. We scan the hash chain to get the pointer to the cursor of our sql statement, once found (soft parse), information in the cursor is used to execute the statement. In order to reduce the length of the hash chain to be scanned, hash chains are grouped together into hash buckets (Books in library grouped by subject) i.e. one hash bucket contains the hash chain containinig pointers to the cursors of sql statements hashing to a set of values.
Hash buckets Hash chains
H1 S1<–> S2 <–> S5 <–> S9
That’s was an overview of shared pool’s architecture. In my next post in this series, I will talk about various serialization structures used in library cache to protect memory allocations i.e. latches, locks, pins and mutexes.
Dataguard 11g release 2 simplifies configuration significantly by eliminating need for user written triggers to automate client failover. Role transitions managed by dataguard broker can automatically
– failover the database ,
– start the appropriate services on the new primary database,
– disconnect clients from the failed database and
– redirect them to the new primary database — no manual intervention is required.
service_name => ‘orcls’,-
network_name => ‘orcls’,-
failover_method => ‘BASIC’, –
failover_type => ‘SELECT’,-
failover_retries => 180,-
failover_delay => 1);
select database_role into role from v$database;
IF role = ‘PRIMARY’ THEN