Shared Pool

Hey Folks,

Here we shall review about Shared Pool :

Shared Pool : Shared pool is a part  of SGA and it is used to store :

• Most recently executed SQL statement
• Most recently used data definitions 

It consist of three key performance - related memory structures :

• Library Cache
• Data Discretionary cache
• Result cache

Shared pool sized by the Parameter " SHARED_POOL_SIZE "

           ALTER SYSTEM SET SHARED_POOL_SIZE = 64M;

The shared pool environment contains both fixed and variable structure. The fixed structures remains relatively the same size whereas the variable structure grows and shrink based on user and program requirements. the actual sizing for the fixed and variable structure is based on an initialization parameter and the work of an Oracle Internal algorithms.

The size of shared pool as total size determined by the initialization parameter SHARED_POOL_SIZE. The Default value of the parameter is 8 M on 32 - bit platforms and 64 M on 64 - bit platforms. Increasing the value of this parameter is increases the amount of memory reserved   for the shared pool.

In the shared pool have many caches including the Library Cache and the Data Dictionary Cache automatically increase and decrease in size as needed. Old entries are aged out to accommodate new entries when shared pool runs out of space.

A Cache Miss on the library cache or Data Dictionary Cache is more more expensive than a miss on database buffer cache. For this reason the shared pool should be sized to ensure that frequently used data is cached.

Large memory allocation required in shared pool for several features, such as shared server, Parallel Query OR  Recovery Manager. Oracle Recommends  to use a separate memory area, that is called  "large pool" to protect the System Global Area (SGA) memory used by  these features. 

Sometime  make memory allocation out of the shared pool for  JAVA, PL/SQL, Or SQL cursors larger than 5 KB. To Enable these allocations to occur more efficiently Oracle Database preserve a small amount of shared pool memory and that preserved memory is called  Reserved Pool. It is Used if the shared pool Runs out of Space.

Sizing  the Shared Pool :

Shared Pool consist of three important memory structure:

1. Library Cache : Library Cache is part of shared pool in the SGA and it Plays the important role and special functionality as below 

• Stores information about the most recently used SQL and PL/SQL Statements
• Enables the sharing of commonly used statements
• It is managed by least Recently used (LRU) algorithm
• Consist of two structures

            --> Shared SQL area

            --> Shared PL/SQL  area

• Size determined by shared pool sizing

The Library cache stores executable forms of SQL  cursors, PL/SQL programs, and JAVA Classes, Which are collectively referred to as the application code.

When the application code is executed and parsed or program unit is called the memory is allocated. If the size is too small, Statements are continually reloaded into the library cache and which affect performance. The library cache is managed by an LRU algorithm.

Oracle database attempts to reuse existing code if it has been executed previously and can be shared. If the parsed representation of the SQL statement exists in library cache and it can be shared, then the database reuses the existing code. This is Known as a Soft Parse, Or a Library Cache Hit.

If Oracle Database cannot use the existing code, Then database must build a new executable version of the application code. This is known as Hard Parse or, a Library Cache Miss. 

In Order to Perform a Hard Parse Oracle Database uses more resources than during a Soft Parse. Resources used for a Soft Parse include CPU and Library Cache Latch Gets. Resources required for Hard Parse Include additional CPU, Library Cache Latch Gets and the Shared Pool Latch Gets. A Hard Parse may occur on either the parse step or the execute step When processing a SQL Statement.

When the Library Cache fills  then the Less/least used execution paths and Parse tress are removed from the Library Cache to make rooms for the new Entries. If the SQL or PL/SQL statements are not reused then they are eventually aged out, Then Oracle Database implicitly re-parses the statement to create a new Shared SQL area for it, and execute the statement. This also results in a Hard Parse.  To reduce Library Cache Misses on execution calls, allocate more memory to the Library Cache.

When an application makes a parse call for a SQL statement, if the parsed representation of the statement does not exist in the Library Cache, then Oracle Database Parse the statement and stores the Parsed form in the Shared Pool. To Reduce the Library Cache Misses on Parse calls, ensure that all the shareable SQL statements are stored in the Shared Pool whenever Possible.

The Library Cache consist of two structures:

• Shared SQL : The shared SQL area stores and shares the Execution Plan and Parse Tree for SQL Statements runs against the Database. If the Second time, Same SQL statements executes, then it is able to take advantage of the Parse information available in the shared SQL area to faster its executions. To make an assurance that the SQL statements  use a  shared SQL area whenever possible and for that  The Text, Schema and bind variables must be exactly the same.
• Shared PL/SQL : The Shared PL/SQL area stores the shares the most recently executed PL/SQL Statements. Parsed and Complied Program Unit and Procedures ( Functions, Packages, Triggers) are stored in this area.

   2.  Data Dictionary Cache :

The Second major Cache area within the Shared Pool is the Data Dictionary Cache. The Data Dictionary Cache holds the internal data Structure of the Oracle Database.

The Data Dictionary is accessed very often by various Oracle Processes. Dictionary Cache is the memory area that is designed to hold the  Database Data dictionary data. It is also known as the ROW Cache Because it holds the data in ROWs format instead of buffers.

When an Oracle Database is first started, No data exists in  Data Dictionary Cache. The Data Dictionary Cache started to holds the dictionary data after the Database Startup  and  it is up and running for a period of time. It is used for storing the most recently used data definition in the Oracle Database.  These Data Definitions may include information about, Data Files, tables, Indexes, Privileges, Users etc, and any DML operation performed.

Caching these information in memory improves the performance especially for queries and updates using DML. During the parsing phase, the server process scans this memory structure to resolve the database object names and validate access.

Data dictionary Cache hold the information about Oracle Database is as below:

• A Collection of the most recently used definitions in the database
• Include information about database files, tables, indexes, columns, users, privileges, and other database objects
• During the parse phase, the server process looks at the data dictionary for information to resolve object names and validate access
• Caching data dictionary information into memory improves response time on queries on DML
• Size determined by the Shared Pool Sizing

The data dictionary cache is also referred to as the dictionary cache or Row cache. Information about the database (User Account Data, Data File names, Segment names, Extent Locations, Table descriptions and user privileges) is stored in the data dictionary tables. When this Information is needed by the server, the data dictionary tables are read, and the data that is returned is stored in the data dictionary cache.

We can reduce the contention of Data dictionary cache by using locally managed tablespace (LMT)  instead of using dictionary managed tablespace (DMT). 

The data dictionary cache misses is the ratio of the number of data dictionary request resulting in cache misses (Requested Data dictionaries information that's are not found in data dictionary cache). The data dictionary cache holds the data dictionary data that is used and heavily referenced during SQL pre-execution processing and it improves the SQL query performance.

If you are having difficulties regarding the data dictionary cache misses, note:

"If the Miss Percentage above is greater than 10-15, you may need to tune your library cache usage by adding additional memory to the data dictionary cache. Increasing memory is done by increasing the SHARED_POOL_SIZE parameter in the INITsid.ora file.

Sizing of the data Dictionary :

The overall size is dependent on the size of the shared pool size and is Managed internally by the database. If the Data Dictionary Cache is too Small, then the database has to query the data dictionary tables repeatedly for information is  needed by the server. These queries are called recursive calls and are slower than the direct query on the data dictionary cache because direct queries do not use SQL.

3. Result Cache :

This Features is introduced and included in Shared pool of an SGA from oracle 11G Release. The result cache has received an attention due the capability to cached  SQL query and PL/SQL functions results in memory.

When a query is executed for the very first time, the user’s process searches for the data in the database buffer cache. If data is there (because someone else had retrieved this data before), it uses it; otherwise, it performs an I/O operation to retrieve data from the datafile on disk into the buffer cache, and from this data, the final result set is built.

Subsequently, if another query requires the same data set, the process uses the data from the buffer cache to build the result set required by the user. Well, here the result cache could be called a cache area, in this case, the shared pool. So, the Result Cache is an area in the shared pool and contains the end results of a query execution.

E.g. : Suppose one user executed a query and get the final result of 300 rows.  If another user executed the same query and then The user session will have to traverse through all these rows in the buffer cache once again to get the final result of 300 rows. So the result cache is introduced and using this result cache to get directly to those 300 rows the second time and every time going forward.

E.g. : Suppose one user executed a query and get the final result of 300 rows.  If another user executed the same query and then The user session will have to traverse through all these rows in the buffer cache once again to get the final result of 300 rows. So the result cache is introduced and using this result cache to get directly to those 300 rows the second time and every time going forward.

Now Question Comes that how the result cache will use when same query executed again and again. So for that use of result cache and get the faster result of the same query, We have to use OR pass RESULT_CACHE hint in the query.

SELECT /*+ RESULT CACHE */ SUM(Sal) FROM HR.employees WHERE DEPTNO=45;

When a query with  RESULT_CACHE  hint is run. Oracle Will  see if the Results of the query have already been Executed, Computed, and Cached and If so, Retrieve the data from the cache instead of querying the data blocks and computing the results again.  Take the fallowing important points into consideration.

• The result Cache feature is useful  only for SQL queries that are executed over and over again frequently.
• the underlying data doesn't change very often. When The data changes, the result set is removed from the cache

If you are executing the same queries over and over, using the RESULT_CACHE hint often makes subsequent queries run faster.

By default, the result_cache_max_size parameter is about 0.25% of the memory_target parameter or 1% of the shared_pool parameter. 

This parameter can be modified to control how much is stored in the Result Cache. The result_cache_max_result parameter specifies what percentage of result_cache_max_size a single Result Cache can use.  The default value is 5%.

Each result set is identified in the cache using a CACHE_ID, which is a 90-character long string. The CACHE_ID for a query does not match the SQL_ID used to identify the query in the library cache and contained in V$SQL. Unlike the SQL_ID, which is generated for every SQL query executed against an Oracle database, the CACHE_ID is for an area or bucket in the Result Cache section of the shared pool that stores the end result of the query.

Thank You!!