Analyzing Oracle SGA Large Pool

Oracle provides the ability to create a optional area in the SGA (System Global Area), called the Large Pool to provide large memory allocations for the following. By allocating session memory from the large pool for shared server, Oracle XA, or parallel query buffers, Oracle can use the shared pool primarily for caching shared SQL and avoid the performance overhead caused by shrinking the shared SQL cache. Read the rest of this entry »

Analyzing Oracle SGA Java Pool

Oracle includes several application program interfaces and Java class libraries in order to facilitate the interaction of Java-based applications with Oracle databases.  Oracle also allows you to dedicate a portion of the SGA, called Java Pool, as the location where session-specified Java code and application variables reside during program execution. Read the rest of this entry »

Analyzing Oracle SGA Database Buffer Cache

Like the Shared Pool (Library Cache and Data Dictionary Cache), the performance of the Database Buffer Cache is determine with the cache hit-ratio.  Cache hits occur whenever a user process finds that a data buffer needed by their SQL statement is already cached in memory;  Consequently, cache misses occur when the user process does not find the requested data to already be cached in memory – causing the data to be read form disk instead.  High cache hit ratios indicate that your application users are frequently finding that the data buffers they need are already in memory thus reducing the need (and delay) to read from disk (remember that disk reads are much slower than memory).

Read the rest of this entry »

Analyzing Oracle SGA Shared Pool (Library Cache)

Starting with the Oracle System Global Area, we are going to touch on the basics of Shared Pool – Library Cache followed by Data Dictionary Cache (which will be in another article). The Shared Pools’ Library Cache is the area Oracle caches the SQL and PL/SQL statements that have been recently issued by application users. PL/SQL statements can be in the form of procedures, functions, packages, triggers, anonymous PL/SQL blocks, or Java classes. Read the rest of this entry »

Understanding Oracle’s underlying architecture

Before we are able to analyze and determine tuning areas, we need to understand Oracle Server Architecture and it’s underlying mechanism. Oracle Server Architecture consist of two main categories: logical (memory) structures and physical (or file) structures. The Oracle memory structure is collectively known as Oracle Instance. While the physical file structures are collectively known as Oracle database. Together, they are call an Oracle Server. Read the rest of this entry »