Find Offending SQL Bottlenecks!

After the tune-able parameters are changed for optimal performance, your system still fails miserably with a poor response time.  The most likely step you should take is to do a deep diagnostics on the system.  Break the system up into different components such as application server where diagnostics using probes is required, database servers where SQL statements and stored procedures become the next to be scrutinized, etc… Read the rest of this entry »

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 »