SHARED POOL ARCHITECTURE

                 
  In this first article in this series, I will discuss the purpose of shared pool, its architecture and hash buckets in the shared pool. In the next article(s), I will discuss about latches,  locks , pins and mutexes and tuning of shared pool.                    
WHAT IS SHARED POOL?
  The Shared Pool is a RAM area within the RAM heap that is created at startup time, a component of the System Global Area (the SGA).  The shared pool is the most important area of the SGA, except for the data buffer caches. The shared pool is usually the second-largest SGA memory area.                 
  PURPOSE OF SHARED POOL
 An important purpose of the shared pool is to cache the executable versions of SQL and PL/SQL statements. This allows multiple executions of the same SQL or PL/SQL code to be performed without the resources required for a hard parse, which results in significant reductions in CPU, memory, and latch usage.

                   WHAT DOES SHARED POOL CONTAIN?

  The shared pool is primarily a repository for shared SQL and PL/SQL so that users can share the statements thus reducing the need to parse a similar statement. Much of the shared pool usage is to support the execution of shared SQL and PL/SQL packages; but in order to build a cursor or compile a PL/SQL procedure, we need to know all about the database objects referenced by the SQL or PL/SQL being compiled e.g. to execute a query on a table, we need to know metadata about table including column names, data types, indexes and optimizer statistics. All of this additional metadata is also cached in the shared pool, independent of the cursors or program unit. Since this metadata is stored independently, it can be used to build any no. of cursors.  Besides, several features like the shared server, parallel query, or RMAN make large memory allocations in the shared pool.

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:

 Library cache
   Oracle’s library cache is responsible for collecting, parsing, interpreting, and executing all of the SQL statements that go against the Oracle database. It contains :   1. SQL(cursors): Executable representation of a SQL statement that may be used repeatedly by many sessions.

   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

 The dictionary cache stores environmental information, which includes referential integrity, table definitions, indexing information, and other metadata stored within Oracle’s internal tables.It’s also known as the row cache. It is used to cache data dictionary related information in RAM for quick access. The dictionary cache is like the buffer cache, except it’s for Oracle data dictionary information instead of user information.  Oracle Database uses this metadata when parsing SQL cursors or during the compilation of PL/SQL programs

 Result cache

  It holds query result sets and query fragments, so that subsequent queries can retrieve the result directly from cache.

              HOW IS MEMORY ALLOCATED IN SHARED POOL?

 The shared pool has many hundreds of objects in it , where an object  can be for example, a cursor, a table, a view or a PL/SQL package.

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?

    The library cache stores text of the SQL statements and their execution plans in cursors. Whenever a SQL statement is issued, library cache is searched to find an existing cursor for the issued SQL statement.If whole of the library cache is searched for cursor, it will take very long. If we think of library cache as a library of books and cursor as a book, it will obviously take very long to locate a book in a huge library. Hence, we asign a code to every book and have a catalog card which contains book’s code and it’s rack number in the library. We search the catalog looking for the desired card and just go the listed rack to get the book.

  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.

For example,

      Hash buckets               Hash chains

         H1             S1<–>  S2 <–>  S5 <–> S9

                           ^                                    ^

                           |_____________________|

 

         H2          S3 <–> S8

                        ^           ^

                        |_______|

 

         H3           S4 <–> S6
                        ^            ^           
                        |_______|

 

         H4             S7
 Note that multiple sql statements can hash to the same value. In above diagram, H1 can contain statements which hash to certain values only. Statements S1, S2, S5 and S9 hash to one of those values. Hence, pointers to their cursors are in the same hash bucket H1. So, if statement S5 is issued, only the hash chain corresponding to hash bucket H1 is searched instead of searching whole of library c  If the corresponding cursor is not found, statement is hashed to get the hash bucket number, its cursor is constructed and pointer to the cursor is linked to hash chain for the bucket (hard parse).

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.

 

Related links :

Home

Database Index
Tuning Index
Cursor Sharing Demystified 
Library Cache Lock And Pin Demonstrated
Latches, Locks, Pins And Mutexes 
Parent And Child Cursors In Oracle
Shared Pool Architecture 

                                    ———————

13 thoughts on “SHARED POOL ARCHITECTURE

  1. Dear Anju,

    As you refer “multiple sql statements can hash to the same value”. This means these sql statements use bind variables. So they have the same hash value. So hash bucket contains all sql with the same hash value. Is it right ?

    Thanks for your time

    V.T

    1. Hi V.T

      Multiple SQL statements can hash to the same value irrespective of whether they use bind variables or not. Just like modulus of many numbers can be same. I hope this answers your query. In case you have any doubts, Pls feel free to post them.

      Regards
      Anju

      1. Thanks for your reply soon. But really, I do not clear in this case. The hash value is the same when the input value (SQL statement text) of hash function is the same. Is it right ? Please correct me if I mistake.

        Thanks,
        V.T

        1. Hash value will be same when the input value (SQL statement text) is same. Hash value can also be same if input value is different.

          For example mod(10,2) = mod (4,2) = mod(6,2) = 0

          Just as modulus of various numbers can be same, Hash value of multiple statements can also be same.

          Regards
          Anju

Leave a Reply to Abdul Cancel reply