Oracle Concepts - Shared Pool

Oracle Concepts - Shared Pool Oracle Tips by Burleson Consulting

Question:  What is the Oracle shared_pool_size parameter and whatis contained in this shared pool?

Answer: the Shared Pool is a RAM area within the RAM heap that is created at startup time, acomponent of the System Global Area (the SGA).  The shared pool is the most important area of the SGA, except for the databuffer caches. There are a number of sub-areas within the SGA, each with itsown important purpose. 

Unfortunately, all of the sub-areas are controlled by the singleshared_pool_size parameter. A shortage of shared pool RAM may result in highlibrary cache reloads, high row cache reloads, and shared pool latch contention.You may also see the error: "ORA-04031: Out of shared pool memory".

The Oracle sharedpool contains Oracle's library cache, which is responsible for collecting,parsing, interpreting, and executing all of the SQL statements that go againstthe Oracle database. Hence, the shared pool is a key component, so it'snecessary for the Oracle database administrator to check for shared poolcontention.

The shared pool islike a buffer for SQL statements.  Oracle's parsing algorithm ensures thatidentical SQL statements do not have to be parsed each time they'reexecuted.  The shared pool  is used to store SQL statements, and itincludes the following components:

Since it is notpossible to dedicate separate regions of memory for the shared pool components,the shared pool is usually the second-largest SGA memory area (depending on thesize of thedb_cache_size parameter). Theshared pool contains RAM memory regions that serve the following purposes:

? Library cache ? The library cache contains the currentSQL execution plan information. It also holds stored procedures and triggercode. 

? Dictionary cache - The dictionarycache stores environmental information, which includes referential integrity,table definitions, indexing information, and other metadata stored withinOracle's internal tables.

? Session information ? Systems that useSQL*Net version 2 with a multi-threaded server need this area to store sessioninformation. Beginning with Oracle, the v$session view contains informationrelated to Oracle*Net users.

The followingtable lists the different areas stored in the shared pool and their purpose:

* Shared SQL Area - The shared SQLarea stores each SQL statement executed in the database. This area allows SQLexecution plans to be reused by many users.

* Private SQL Area - Private SQLareas are non-shared memory areas assigned to unique user sessions.

* PL/SQL Area - Used to hold parsed and compiled PL/SQLprogram units, allowing the execution plans to be shared by many users.

* Control Structures - Common controlstructure information, for example, lock information

The dictionarycache stores ?metadata? (data about your tables and indexes) and it?s alsoknown as the row cache. It is used to cache data dictionary related informationin RAM for quick access. The dictionary cache is like the buffer cache, exceptit?s for Oracle data dictionary information instead of user information. Wewill discuss the data dictionary later in this book.

As with thedatabase buffer cache, the shared pool is critical to performance. Later inthis book we will discuss the concept of Oracle SQL statement reuse.Reusability is a concept that is very important when it comes to performancerelating to the shared pool!

Thus far we havediscussed Oracle?s in-memory storage of data, SQL and control structures butthere is one other very important SGA structure to be mentioned, the redo logbuffer.

Adjusting theOracle10g Database Shared Pool

Many DBAs knowthat there are several queries for determining when the Oracle shared pool istoo small. The library cache miss ratio tells the DBA whether to add space tothe shared pool, and it represents the ratio of the sum of library cachereloads to the sum of pins.

In general, if thelibrary cache ratio is over 1, you should consider adding to theshared_pool_size. Library cache misses occur during the parsing and preparationof the execution plans for SQL statements.

The compilation ofa SQL statement consists of two phases: the parse phase and the execute phase.When the time comes to parse an SQL statement, Oracle checks to see if theparsed representation of the statement already exists in the library cache. Ifnot, Oracle will allocate a shared SQL area within the library cache and thenparse the SQL statement. At execution time, Oracle checks to see if a parsedrepresentation of the SQL statement already exists in the library cache. Ifnot, Oracle will reparse and execute the statement.

The followingSTATSPACK script will compute the library cache miss ratio. Note that thescript sums all of the values for the individual components within the librarycache and provides an instance-wide view of the health of the library cache.

Here is theoutput. This report can easily be customized to alert the DBA when there areexcessive executions or library cache misses.

                              Cache Misses   Library Cache

Yr.  Mo Dy  Hr.       execs WhileExecuting      Miss Ratio

---------------- ---------- --------------- ---------------

2001-12-11 10       10,338         3              .00029

2001-12-12 10      182,477       134              .00073

2001-12-14 10      190,707       202              .00106

2001-12-16 10        2,803        11              .00392

Once this reportidentifies a time period where there may be a problem, STATSPACK provides theability to run detailed reports to show the behavior of the objects within thelibrary cache.

In the precedingexample, you see a clear RAM shortage in the shared pool between 10:00 A.M. and11:00 A.M. each day. In this case, you could dynamically reconfigure the sharedpool with additional RAM memory from the db_cache_size during this period.However, when Oracle Database 10g has the sga_target parameter set, it willautomatically re-allocate space as needed, up to the value of sga_target, bydynamically removing space for other components who aren't experiencing stressand reallocating to the stressed shared pool.

The OracleDatabase 10g workload manager promises to do this type of monitoring andanticipates upcoming RAM shortages, allocating additional RAM just in time forthe anticipated event. Let?s take a look at how Oracle Database 10g monitorsthe shared pool.

 

---

Here is a script to measure objectusage inside the shared pool:

 set pagesize 132

column owner format a16

column name  format a36

column sharable_mem format 999,999,999

column executions   format999,999,999

prompt

prompt  Memory Usage of Shared PoolOrder - Biggest First

prompt

column name format 45

select  owner, name||' - '||typename, sharable_mem from v$db_object_cache

where sharable_mem > 10000

  and type in ('PACKAGE', 'PACKAGEBODY', 'FUNCTION', 'PROCEDURE')

order by sharable_mem desc

/

prompt

prompt  Loads into Shared Pool - Most Loads First

prompt

select  owner, name||' - '||type name, loads , sharable_mem fromv$db_object_cache

where loads > 3

  and type in ('PACKAGE', 'PACKAGEBODY', 'FUNCTION', 'PROCEDURE')

order by loads desc

/

prompt

prompt  Executions of Objects inthe  Shared Pool  - Most Executions First

prompt

select  owner, name||' - '||typename, executions from v$db_object_cache

where executions  > 100

  and type in ('PACKAGE', 'PACKAGEBODY', 'FUNCTION', 'PROCEDURE')

order by executions  desc

/