Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

Oracle Concepts - Shared Pool

Oracle Tips by Burleson Consulting

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

Answer: 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. There are a number of sub-areas within the SGA, each with its own important purpose.

Unfortunately, all of the sub-areas are controlled by the single shared_pool_size parameter. A shortage of shared pool RAM may result in high library 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 shared pool contains Oracle's library cache, which is responsible for collecting, parsing, interpreting, and executing all of the SQL statements that go against the Oracle database. Hence, the shared pool is a key component, so it's necessary for the Oracle database administrator to check for shared pool contention.

The shared pool is like a buffer for SQL statements.  Oracle's parsing algorithm ensures that identical SQL statements do not have to be parsed each time they're executed.  The shared pool  is used to store SQL statements, and it includes the following components:

Since it is not possible to dedicate separate regions of memory for the shared pool components, the shared pool is usually the second-largest SGA memory area (depending on the size of the db_cache_size parameter). The shared pool contains RAM memory regions that serve the following purposes:

? Library cache ? The library cache contains the current SQL execution plan information. It also holds stored procedures and trigger code.

? 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.

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

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

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

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

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

* Control Structures - Common control structure information, for example, lock information

The dictionary cache stores ?metadata? (data about your tables and indexes) and 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. We will discuss the data dictionary later in this book.

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

Thus far we have discussed Oracle?s in-memory storage of data, SQL and control structures but there is one other very important SGA structure to be mentioned, the redo log buffer.

Adjusting the Oracle10g Database Shared Pool

Many DBAs know that there are several queries for determining when the Oracle shared pool is too small. The library cache miss ratio tells the DBA whether to add space to the shared pool, and it represents the ratio of the sum of library cache reloads to the sum of pins.

In general, if the library cache ratio is over 1, you should consider adding to the shared_pool_size. Library cache misses occur during the parsing and preparation of the execution plans for SQL statements.

The compilation of a 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 the parsed representation of the statement already exists in the library cache. If not, Oracle will allocate a shared SQL area within the library cache and then parse the SQL statement. At execution time, Oracle checks to see if a parsed representation of the SQL statement already exists in the library cache. If not, Oracle will reparse and execute the statement.

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

rpt_lib_miss.sql

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

                               Cache Misses   Library Cache

Yr.  Mo Dy  Hr.       execs While Executing      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 report identifies a time period where there may be a problem, STATSPACK provides the ability to run detailed reports to show the behavior of the objects within the library cache.

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

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

 


Here is a script to measure object usage inside the shared pool:

 set pagesize 132

column owner format a16
column name  format a36
column sharable_mem format 999,999,999
column executions   format 999,999,999
prompt
prompt  Memory Usage of Shared Pool Order - Biggest First
prompt
column name format 45

select  owner, name||' - '||type name, sharable_mem from v$db_object_cache
where sharable_mem > 10000
  and type in ('PACKAGE', 'PACKAGE BODY', '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 from v$db_object_cache
where loads > 3
  and type in ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')
order by loads desc
/

prompt
prompt  Executions of Objects in the  Shared Pool  - Most Executions First
prompt
select  owner, name||' - '||type name, executions from v$db_object_cache
where executions  > 100
  and type in ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')
order by executions  desc
/
 

 

See my related notes on shared_pool tips:

 

 

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


 

 

  
 

 
 
 
 
 

 
 
 
 
 
 
Oracle training Excel
 
Oracle performance tuning software 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.