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:
- 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!!
