Memory View (Oracle Performance Analyst)

From DBArtisan
Jump to: navigation, search

Go Up to Oracle Performance Analyst Statistics

In addition to a Home page, the Memory category of Oracle Performance Analyst includes the following tabbed pages:

Home

The Memory home page includes the following sections:

Key Ratio Analysis pane

Object-related database activity can be examined using both ratio-based and wait/bottleneck-based analysis. Ratio-based analysis involves examining a number of key database ratios and statistical readings that can be used to indicate how active certain object types are. Performance ratios serve as roll-up mechanisms for busy DBAs to use for at-a-glance performance analysis.

When using ratio-based analysis, there are some standards to adhere to. To start with, many of the formulas that make up ratio-based analysis must be derived from delta measurements instead of cumulative statistics. Many of the global ratios that you examines come from the v$sysstat performance view. The performance view maintains a count of all the occurrences (in the VALUE column) of a particular database incident (in the NAME column) since the database was brought up. For databases that are kept up for long periods of time, these values can grow quite large and impacts how a particular ratio is interpreted. However, if delta statistics are used (taking, for a specified sampling period, the before and after counts of each statistic that make up a ratio), then an accurate and current portrayal of the various ratios can be had.

A final thing to remember about using ratio-based analysis is that, while there are a number of rules of thumb that can be used as starting points to begin the evaluation of database performance, DBAs must determine each database’s individual ‘personality’ with respect to the key performance ratios. Some hard and fast rules simply do not apply to every database. The danger in using blanket ratio standards is that they can lead you to haphazardly take action, which can at times contribute nothing to the situation, and sometimes even degrade performance.

The following memory ratios are used on the Performance Analyst Memory home page to succinctly communicate the general overall memory performance levels of the monitored database. They duplicate statistics shown on the Home page. For detailed descriptions of those statistics, see Key Ratio Analysis Pane.

Bottleneck Analysis pane

When an Oracle database is up and running, every connected process is either busy doing work or waiting to perform work. A process that is waiting may mean nothing in the overall scheme of things or it can be an indicator that a database bottleneck exists. You can use Bottleneck Analysis to determine if perceived bottlenecks in a database are contributing to a performance problem.

Memory bottlenecks can definitely cause performance degradation in an otherwise well-running database. Typically, these bottlenecks center around Oracle’s buffer/data cache, library cache, and occasionally log buffer memory regions. To help you identify such problems, the following statistics are presented on the Memory home page:

The following statistic, available on this pane, duplicates a statistic available on the Key Ratio Analysis Pane of the Home View (Oracle Performance Analyst) page:

Free Buffer Wait Average

There are times when a user must wait to obtain a clean buffer in the buffer/data cache. The free buffer wait average provides a metric that communicates the average number of tree buffer waits..

Location

Oracle Performance Analyst Statistics > Memory View (Oracle Performance Analyst) > Home > Bottleneck Analysis pane

Metrics

Seeing consistent non-zero numbers for the free buffer wait average might indicate an undersized data/buffer cache.

Correction

If the Object Reloads statistic, along with other shared pool metrics, indicates an undersized shared pool, you can do the following depending on the version of Oracle you are running: If a problem is found: Increase the size of the shared_pool_size parameter through use of the ALTER SYSTEM SET shared_pool_size command value. Take caution to not over-allocate; ensure enough free memory exists on server before increasing value. Monitor the new value to see if performance improves. If using an SPFILE, save the new configuration values so Oracle reuses them each time the database is stopped and re-started. If you suspect that there are hot code objects that would benefit from being pinned into the shared pool, then you can go into shared pool details using Performance Analyst’s monitors. You can easily spot the general code object types or specific objects that should be pinned in the pool from these monitors. Once identified, you can use the dbms_shared_pool package to pin the hot code objects into RAM.

Object Reloads

When a SQL statement or code object (stored procedure, trigger, etc.) is executed for the first time, Oracle loads the code definition into the shared pool. If the statement or code object is executed again, Oracle can reuse the object definition in memory, and thereby shorten execution time.

If a code object is aged out of the shared pool, and then re-requested, Oracle must load the object back into the pool. Such activity can lead to reduced response times. The Object Reloads statistic provides you with a count of how many objects have been reloaded back into the shared pool.

Location

Oracle Performance Analyst Statistics > Memory View (Oracle Performance Analyst) > Home > Bottleneck Analysis pane

Metrics

Seeing consistent non-zero numbers for object reloads could indicate a too small shared pool or that help identify that there are hot code objects that should be pinned in the shared pool so they cannot be removed.

Correction

If the Object Reloads statistic, along with other shared pool metrics, indicates an undersized shared pool, you can do the following depending on the version of Oracle you are running: If a problem is found: Increase the size of the shared_pool_size parameter through use of the ALTER SYSTEM SET shared_pool_size command value. Take caution to not over-allocate; ensure enough free memory exists on server before increasing value. Monitor the new value to see if performance improves. If using an SPFILE, save the new configuration values so Oracle reuses them each time the database is stopped and re-started. If you suspect that there are hot code objects that would benefit from being pinned into the shared pool, then you can go into shared pool details using Performance Analyst’s monitors. You can easily spot the general code object types or specific objects that should be pinned in the pool from these monitors. Once identified, you can use the dbms_shared_pool package to pin the hot code objects into RAM.

Redo Log Space Waits

The Oracle database is able to manage recovery by recording all changes made to a database through the use of redo log files. Oracle writes modifications made to a database to these files that have the capability to be archived off to another medium for disaster recovery. The background process that performs these operations is Oracle's Log Writer (LGWR). There is a buffer area in Oracle's System Global Area (SGA) that is used to reduce redo log file I/O, whose size, or lack thereof, can affect performance in a busy system. Sometimes a user process must wait for space in this redo log buffer. Oracle uses the log buffer area to cache redo entries prior to writing them to disk, and if the buffer is not large enough for the redo entry load, waits can occur..

Location

Oracle Performance Analyst Statistics > Memory View (Oracle Performance Analyst) > Home > Bottleneck Analysis pane

Metrics

The two main numbers to watch are the redo log space requests and the redo log wait time. If either statistic strays too far from zero, increase the log_buffer parameter and add more memory to the redo log buffer.

Correction

To fix, do the following: Edit the Init.ora file or SPFILE for the database. Increase the amount of log_buffer to a higher value (take caution to not over-allocate; ensure enough free memory exists on server before increasing value. Cycle the Oracle server when possible to allow the new value to take effect. Monitor new value to see if performance improves. On some Oracle platforms, when adjusting the log_buffer parameter, make sure you make the amount a multiple of the block size. Otherwise, on database startup, Oracle returns an error stating that you have entered an invalid amount for the redo log buffer. NOTE: Oracle increases the log_buffer parameter if you make it smaller than its default size for a given platform.

Redo Log Space Wait Time

The Oracle database is able to manage recovery by recording all changes made to a database through the use of redo log files. Oracle writes modifications made to a database to these files that have the capability to be archived off to another medium for disaster recovery. The background process that performs these operations is Oracle's Log Writer (LGWR). There is a buffer area in Oracle's System Global Area (SGA) that is used to reduce redo log file I/O, whose size, or lack thereof, can affect performance in a busy system. Sometimes a user process must wait for space in this redo log buffer. Oracle uses the log buffer area to cache redo entries prior to writing them to disk, and if the buffer is not large enough for the redo entry load, waits can occur.

Location

Oracle Performance Analyst Statistics > Memory View (Oracle Performance Analyst) > Home > Bottleneck Analysis pane

Metrics

The two main numbers to watch are the redo log space requests and the redo log wait time. If either statistic strays too far from zero, increase the log_buffer parameter and add more memory to the redo log buffer.

Correction

To fix, do the following: Edit the Init.ora file or SPFILE for the database. Increase the amount of log_buffer to a higher value (take caution to not over-allocate; ensure enough free memory exists on server before increasing value. Cycle the Oracle server when possible to allow the new value to take effect. Monitor new value to see if performance improves. On some Oracle platforms, when adjusting the log_buffer parameter, make sure you make the amount a multiple of the block size. Otherwise, on database startup, Oracle returns an error stating that you have entered an invalid amount for the redo log buffer. NOTE: Oracle increases the log_buffer parameter if you make it smaller than its default size for a given platform.

Top Latch Misses

Latches protect the many memory structures in Oracle's SGA. They ensure that one and only one process at a time can run or modify any memory structure at the same instant. More restrictive than locks (which at least allow for some collective user interaction), latches have no queuing mechanism.

The Top Latch Misses chart indicates the top latch waits as ranked by total misses.

Location

Oracle Performance Analyst Statistics > Memory View (Oracle Performance Analyst) > Home > Bottleneck Analysis pane

Metrics

Seeing increasing numbers for the same latch miss can require further accessing session latch details, which can be accomplished inside Performance Analyst.

Correction

The remedy for latch misses depends on the latch itself. Buffer chain latches, for example, are indicative of high I/O rates in databases. Increasing the buffer/data cache can help as might adding more buffer LRU chain latches by increasing the db_block_lru_latches parameter. Library cache latch misses are oftentimes produced by non-reused SQL in the shared pool. Increasing the shared_pool_size can help, but a better remedy might be changing SQL to use bind variables or setting the cursor_sharing parameter to FORCE.

SQL Analysis Pane

Much of a database's overall performance can be attributed to SQL statement execution. Poorly optimized SQL statements can drag an otherwise well-configured database down in terms of end user response times. SQL statements that use much memory can also cause a problem in a database. The SQL Analysis for memory shows what SQL statements have consumed the largest percentages of shareable, persistent, and runtime memory

Before you can identify problem SQL in your database, you have to ask the question, “What is bad SQL?” What criteria do you use when you begin the hunt for problem SQL in your critical systems? Understand that even the seasoned experts disagree on what constitutes efficient and inefficient SQL; so there is no way to sufficiently answer this question to every Oracle professional's satisfaction. The table lists some general criteria you can use when evaluating the output from various database monitors or personal diagnostic scripts:

Criteria Description

Overall Response (Elapsed) Time

The time the query took to parse, execute, and fetch the data needed to satisfy the query. It should not include the network time needed to make the round trip from the requesting client workstation to the database server. This statistic is available in Oracle9i or later.

CPU Time

The CPU time the query took to parse, execute, and fetch the data needed to satisfy the query.

Physical I/O

This is often used as the major statistic in terms of identifying good vs. bad SQL, this is a measure of how many disk reads the query caused to satisfy the user's request. While you certainly want to control disk I/O where possible, it is important that you not focus solely on physical I/O as the single benchmark of inefficient SQL. Make no mistake, disk access is slower than memory access and also consumes processing time making the physical to logical transition, but you need to look at the entire I/O picture of a SQL statement, which includes looking at a statements' logical I/O as well.

Logical I/O

The number of memory reads the query took to satisfy the user's request. The goal of tuning I/O for a query should be to examine both logical and physical I/O, and use appropriate mechanisms to keep both to a minimum.

Repetition

The number of times the query has been executed. A problem in this area is not as easy to spot as the others unless you know your application well. A query that takes a fraction of a second to execute can be a headache on your system if it has executed erroneously (for example, a query that executes in a runaway PL/SQL loop) over and over again.

There are other criteria that you can examine like sort activity or access plan statistics (that show items like Cartesian joins and the like), but more often than not, these measures are reflected in the criteria listed above. Fortunately, Oracle records all the above measures (some only in 9i), which makes tracking the SQL that has been submitted against an Oracle database much easier.

Metrics

When you begin to look for inefficient SQL in a database, there are two primary questions you want answered:

  1. What HAS been the worst SQL that has historically been run in my database?
  2. What IS the worst SQL that is running right now in my database?

When troubleshooting a slow system, you should be on the lookout for any query that shows an execution count that is significantly larger than any other query on the system. It could be that the query is in an inefficient PL/SQL loop, or other problematic programming construct. Only by bringing the query to the attention of the application developers will you know if the query is being mishandled from a programming standpoint.

There is the possibility that the SQL statement just is not tuned well. You can view Performance Analyst's Top SQL view and, if you have IDERA SQL Tuner installed, you can port the SQL over to SQL Tuner to better optimize the statement.

SGA Analysis Pane

Most DBAs know all about the Oracle System Global Area (SGA). The SGA is Oracle's memory structural area devoted to facilitating the transfer of data and information between clients and the Oracle database. The table below describes Oracle memory structures:

Memory Structure Description

Default buffer cache

Maintains data blocks when they are read from the database. If you do not specifically place objects in another data cache, then any data requested by clients from the database is placed into this cache. The memory area is controlled by the db_cache_size parameter.

Keep buffer cache

You can assign various objects to a special cache that retains those object’s requested blocks in RAM for as long as the database remains up. The keep cache's main is for often-referenced lookup tables that should be kept in memory at all times for fast access. The buffer_pool_keep parameter controls the size of this cache in Oracle8, while the db_keep_cache_size parameter handles the cache in Oracle9i or later. The keep pool is a sub-pool of the default buffer cache.

Recycle buffer cache

The opposite of the keep cache. When large table scans occur, the data that fills a memory cache will likely not be needed again and should be quickly discarded from RAM so that they do not occupy memory space and prevent needed blocks from assuming their place. Objects containing such data can be assigned to the recycle pool to ensure that such a thing does indeed occur. The buffer_pool_recycle parameter controls the size of this cache in Oracle8 and earlier, while the db_recycle_cache_size parameter handles the cache in Oracle9i or later.

Specific block size caches

You can create tablespaces whose blocksize differs from the overall database blocksize. When data is read into the SGA from these tablespaces, their data has to be placed into memory regions that can accommodate their special block size. Oracle9i or later has memory settings for 2K, 4K, 8K, 16K, and 32K caches. The configuration parameter names are in the pattern of db_nk_cache_size.

Shared pool

Holds object structure as well as code definitions, and other metadata. Setting the proper amount of memory in the shared pool assists a great deal in improving overall performance with respect to code execution and object references. The shared_pool_size parameter controls the memory region.

Large pool

You can configure an optional, specialized memory region called the large pool that holds items for shared server operations, backup and restore tasks, and other miscellaneous things. The large_pool_size parameter controls the memory region. The large pool is also used for sorting when the multi-threaded server (MTS) is implemented.

Java pool

Handles the memory for Java methods, class definitions, etc. The java_pool_size parameter controls the amount of memory for this area.

Redo log buffer

Buffers modifications that are made to the database before they are physically written to the redo log files. The log_buffer configuration parameter controls the memory area.

Oracle also maintains a fixed area in the SGA that contains a number of atomic variables, pointers, and other miscellaneous structures that reference areas of the SGA.

Workload Analysis Pane

It is not uncommon for one or two users to cause the majority of runtime problems that plague a database. The problem could be a runaway process, an untuned batch procedure, or other user-initiated operation. Oftentimes, user connections can get out of hand with memory consumption, and extreme cases have caused headaches at both the database and operating system level (ORA-4030 errors).

If your server does not have an overabundance of memory, periodically check to see who your heavy memory users are along with the total percentage of memory each takes up. If you see one or two users who have more than 25-50% of the total memory usage, investigate the sessions further to see the activities they are performing.

Buffer Cache Tab

The Buffer Cache tab includes the following sections:

Cached Blocks by Tablespace

Oracle’s buffer/data cache contains blocks read in from disk at the user’s request. The Cached Blocks by Tablespace section displays the amount of data held currently in the cache by tablespace.

Location

Oracle Performance Analyst Statistics > Memory View (Oracle Performance Analyst) > Buffer Cache Tab

Cached Tables

One of the ways you can help data from a table stay in the buffer/data cache as long as possible is to enable the table’s CACHE property. CACHE specifies that blocks retrieved for a table be placed at the most recently used end of the LRU list in the buffer cache when a full table scan is performed. This is particularly useful for small lookup tables.

The Cached Tables section displays the data for all tables that have their CACHE property enabled. The table below lists the information available in this section:

Column Description

Owner

The owner of the table.

Table Name

The name of the table.

Table Size

The size of the table in bytes.


Location

Oracle Performance Analyst Statistics > Memory View (Oracle Performance Analyst) > Buffer Cache Tab

Metrics

CACHE is best suited for small lookup tables. Therefore, if you see a large table that has been cached, you may want to reevaluate whether it is smart to cache the contents of that table in Oracle’s buffer/data cache.

Correction

If you wish to disable the CACHE property of a table, you can alter the table and set the property to false.

Buffer Cache Utilization

The Buffer Cache Utilization section shows the state and use of the buffers currently in the buffer cache along with a count of how many there are for each state. Typically, the statuses of the buffers are: being read, free, read and modified, and read and not modified.

Location

Oracle Performance Analyst Statistics > Memory View (Oracle Performance Analyst) > Buffer Cache Tab

Metrics

This grouping of statistics is quite helpful in determining if you have an overabundance of block buffers allocated. Consistently seeing large number of free buffers will clue you in to the fact that you can reduce the amount of block buffers in the cache and give memory back to the operating system. If, however, you see no free buffers within the first hour of bringing your Oracle database up, then you may consider adding more buffers to the cache.

Correction

If a problem is found, then: Edit the Init.ora file or spfile for the database. Increase the amount of db_cache_size to a higher value if free buffers are not found (take caution to not over-allocate; ensure enough free memory exists on server before increasing value). Reduce the number of an overabundance of FREE buffers are present. Monitor new value to see if more numbers of free buffers show up for the FREE status.

Dictionary Cache Tab

Oracle's data dictionary is a component of the shared pool that contains system elements necessary for the parsing of SQL statements, security resolution, object definitions, and more.

The overall data dictionary cache hit ratio provides a key indicator in determining the performance of the shared pool, and shows how often object definitions are found in memory vs. having to read them in from disk. Because Oracle references the data dictionary many times when an SQL statement is processed, it is imperative that as much as possible of this vital reference information be kept in RAM.

The Dictionary Cache tab shows the individual elements of the data dictionary along with their associated hit ratios. In versions 6.x of Oracle, these individual elements could be tuned, but in versions 7.x and later, the only main method for tuning them involves the adjustment of the entire shared pool setting. Although not tunable from an individual parameter level, each displayed element gives insight into which area of the data dictionary is either adding to or detracting from overall performance.

The table below describes the information available on this tab:

Column Description

Parameter

The name of the individual data dictionary element.

Usage

The number of cache entries that contain valid data.

Gets

The number of requests for this element.

Get Misses

The number of requests resulting in a cache miss.

Hit Ratio

The ratio of cache hits versus misses of total requests. The maximum is 100%.

Scans

The number of scan requests.

Scan Misses

The number of times that a scan failed to find the needed data in the cache.

Scan Completes

The number of times the list was scanned completely.

Modifications

The number of insert, update, and delete actions.

Flushes

The number of disk flushes.

Metrics

An overall high data dictionary cache hit ratio is desirable, as are high hit ratios in each individual parameter. You should strive for a hit ratio between 90-100%, with 95% being a good performance benchmark.

Note: When a database is first started, the data dictionary cache hit ratio is not at an optimal level because all references to object definitions are relatively new, and as such, must be placed into the shared pool. Look for hit ratios between 80-90% for new database startups. If, however, after an hour or two of steady database time, the data dictionary cache hit ratio and individual hit ratios, have not increased to desirable levels, you should look into the possibility of increasing the shared_pool_size parameter.
Note: Databases supporting applications that involve large number of objects (such as an Oracle Financials installation) should have larger than normal shared pools to support the required object definitions.

Buffer Pools Tab

The Buffer Pools tab includes the following sections:

Buffer Pool Hit Ratio

Because data objects can be referenced with different usage patterns, Oracle8 and later offers the option to intelligently place objects into one of three buffer caches. The table below describes the types of buffer caches:

Cache Description

KEEP

Designed to minimize misses in the buffer cache. Small objects that are frequently referenced are candidates for the KEEP buffer pool.

RECYCLE

Designed to avoid having large numbers of infrequently accessed blocks of data crowd out objects that need to be referenced in RAM, the RECYCLE pool is good for large objects that are scanned from beginning to end without the need for keeping all their data in RAM.

DEFAULT

The traditional cache for all other data objects.

Note: Unless you specify the KEEP or RECYCLE buffer cache, Oracle automatically places objects into the DEFAULT buffer cache.

The Buffer Pool Hit Ratio section the hit ratios for each of the Oracle8 and later buffer caches so you can easily see how often the objects placed into the various caches are being referenced in memory. Examining how often data is satisfied from memory vs. disk will help you determine if the caches are large enough and if they are being used in an optimal manner. The table below describes the information available in this section:

Column Description

Buffer pool name

The name of the Oracle buffer pool.

Buffer pool hit ratio

The overall hit ratio for the particular cache.

Note: The DEFAULT buffer cache will only be shown for those installations not using the specialized caches available in Oracle8 and later.

Location

Oracle Performance Analyst Statistics > Memory View (Oracle Performance Analyst) > Buffer Pools Tab

Metrics

The KEEP buffer pool should maintain a hit ratio as close to 100% as possible. However, the buffer pool hit ratio is not near 100% until the database has been up and running in a typical state for a short time. A poor hit ratio for the RECYCLE buffer pool may not be a bad thing since there is little chance of reusing a block stored in the buffer pool before it is aged out. NOTE: If you place objects into the KEEP buffer pool, you should periodically reexamine their object sizes to ensure that they are not growing to a physical state that will jeopardize the performance of the KEEP pool.

Buffer Pool Object Assignments

Because data objects can be referenced with different types of usage patterns, Oracle8 and later offers the option to intelligently place objects into one of three buffer caches. The table below describes these three buffer caches:

Cache Description

KEEP

Designed to minimize misses in the buffer cache. Small objects that are frequently referenced are candidates for the KEEP buffer pool.

RECYCLE

Designed to avoid having large numbers of infrequently accessed blocks of data crowd out objects that need to be referenced in RAM, the RECYCLE pool is good for large objects that are scanned from beginning to end without the need for keeping all their data in RAM.

DEFAULT

The traditional cache for all other data objects.

Objects not specified to go into either the KEEP or RECYCLE caches will automatically be placed into the DEFAULT buffer cache.

The Buffer Pool Object Assignments section provides a summary with respect to which types of objects have been assigned to the various buffer caches. The table below describes the information available in this section:

Column Description

Object Type

The type of object (table, index, etc.).

Buffer Pool

The buffer cache name (KEEP, etc.).

Total Objects Assigned

The number of objects that have been assigned to the particular cache.


Location

Oracle Performance Analyst Statistics > Memory View (Oracle Performance Analyst) > Buffer Pools Tab

Metrics

When looking at overall database I/O activity, you should keep an eye out for objects that may lend themselves to being placed into a particular buffer cache. Consider using a KEEP cache for relatively small, frequently accessed tables that require fast response times. Large tables with random I/O activity and are scanned from beginning to end a lot are good candidates for a RECYCLE cache.

Buffer Pool Memory Configuration

Because data objects can be referenced with different types of usage patterns, Oracle8 and later offers the option to intelligently place objects into one of three buffer caches. The table below describes these three buffer caches:

Cache Description

KEEP

Designed to minimize misses in the buffer cache. Small objects that are frequently referenced are candidates for the KEEP buffer pool.

RECYCLE

Designed to avoid having large numbers of infrequently accessed blocks of data crowd out objects that need to be referenced in RAM, the RECYCLE pool is good for large objects that are scanned from beginning to end without the need for keeping all their data in RAM.

DEFAULT

The traditional cache for all other data objects.

Objects not specified to go into either the KEEP or RECYCLE caches will automatically be placed into the DEFAULT buffer cache.

Location

Oracle Performance Analyst Statistics > Memory View (Oracle Performance Analyst) > Buffer Pools Tab

Metrics

When looking at overall database I/O activity, you should keep an eye out for objects that may lend themselves to being placed into a particular buffer cache. Consider using a KEEP cache for relatively small, frequently accessed tables that require fast response times. Large tables with random I/O activity and are scanned from beginning to end a lot are good candidates for a RECYCLE cache.

Correction

Objects can be placed into different buffer pools at object creation time (using the STORAGE...BUFFER_POOL option) or existing objects can be set into a different pool with the ALTER command. Note that tables, partitions, and indexes may be placed into the different caches. If you just want to use the DEFAULT buffer pool and not enable any special caches, you can still encourage Oracle to keep certain objects in the buffer cache as long as possible using the CACHE parameter. For example, issuing the command ALTER TABLE … CACHE specifies that the blocks retrieved for this table be placed at the most recently used end of the LRU list in the DEFAULT buffer cache when a full table scan is performed. The CACHE hint may also be used in SQL statements to cache a table, but used in this form, the blocks will only be cached until the next time the database is shut down. Once the database comes back up, the CACHE hint would have to be issued in a SQL statement again to cache the needed blocks of data.

Latch Activity Tab

The Latch Activity tab includes the following sections:

Latch Hit Ratio

Protecting the many memory structures in Oracle's SGA are latches. They ensure that one and only one process at a time can run or modify any memory structure at the same instant. Much more restrictive than locks (which at least allow for some collective user interaction), latches have no queuing mechanism so either you get it or you do not and are forced to continually retry. The latch hit ratio defines the number of times a process obtained a willing-to-wait.

Location

Oracle Performance Analyst Statistics > Memory View (Oracle Performance Analyst) > Latch Activity Tab

Metrics

If the latch hit ratio falls below 99%, then action should be taken to resolve the amount of latch contention occurring.

Correction

The details regarding the latch contention should be examined. Increasing the shared_pool_size can assist in latch problems also. There are a few specialized cases of latch contention that can be rectified pretty easily. The table below describes them:

Contention Description

Cache buffer chain latch

This latch is responsible for protecting paths to database block buffers in the buffer cache. Very high I/O loads tend to cause contention for this latch. You can alleviate contention somewhat by adding more buffers to the cache (through the db_block_buffers/db_cache_size parameter) or by adding more LRU latch chain latches with the db_block_lru_latches parameter.

Library cache latches

Protects cached SQL statements in the library cache area of the Oracle shared pool. Contention for this latch is the usual result of literals being used for SQL statements instead of bind variables.

Immediate Latch Hit Ratio

Protecting the many memory structures in Oracle's SGA are latches. They ensure that one and only one process at a time can run or modify any memory structure at the same instant. Much more restrictive than locks (which at least allow for some collective user interaction), latches have no queuing mechanism so either you get it or you do not and are forced to continually retry. The latch immediate miss ratio defines the number of times a process obtained a not-willing-to-wait latch.

Location

Oracle Performance Analyst Statistics > Memory View (Oracle Performance Analyst) > Latch Activity Tab

Metrics

If the value falls below 99%, then action should be taken to resolve the amount of latch contention occurring.

Correction

The details regarding the latch contention should be examined. Increasing the shared_pool_size can assist in latch problems also. There are a few specialized cases of latch contention that can be rectified pretty easily. The table below describes them:

Contention Description

Cache buffer chain latch

This latch is responsible for protecting paths to database block buffers in the buffer cache. Very high I/O loads tend to cause contention for this latch. You can alleviate contention somewhat by adding more buffers to the cache (through the db_block_buffers/db_cache_size parameter) or by adding more LRU latch chain latches with the db_block_lru_latches parameter.

Library cache latches

Protects cached SQL statements in the library cache area of the Oracle shared pool. Contention for this latch is the usual result of literals being used for SQL statements instead of bind variables.

Latch Sleep Ratio

Protecting the many memory structures in Oracle's SGA are latches. They ensure that one and only one process at a time can run or modify any memory structure at the same instant. Much more restrictive than locks (which at least allow for some collective user interaction), latches have no queuing mechanism so either you get it or you do not and are forced to continually retry.

A sleep indicates that a latch could not be obtained for a process, and that the attempt will be retried. A low ratio indicates many processes that had to sleep multiple times before obtaining a requested latch.

Location

Oracle Performance Analyst Statistics > Memory View (Oracle Performance Analyst) > Latch Activity Tab

Metrics

Sleeps should be kept as low as possible. If the overall sleep ratio falls below 99%, then action should be taken to resolve the amount of latch contention that is occurring.

Correction

The details regarding the latch contention should be examined. Increasing the shared_pool_size can assist in latch problems also. There are a few specialized cases of latch contention that can be rectified pretty easily. The table below describes them:

Contention Description

Cache buffer chain latch

This latch is responsible for protecting paths to database block buffers in the buffer cache. Very high I/O loads tend to cause contention for this latch. You can alleviate contention somewhat by adding more buffers to the cache (through the db_block_buffers/db_cache_size parameter) or by adding more LRU latch chain latches with the db_block_lru_latches parameter.

Library cache latches

Protects cached SQL statements in the library cache area of the Oracle shared pool. Contention for this latch is the usual result of literals being used for SQL statements instead of bind variables.

Latch Detail

Protecting the many memory structures in Oracle's SGA are latches. They ensure that one and only one process at a time can run or modify any memory structure at the same instant. Much more restrictive than locks (which at least allow for some collective user interaction), latches have no queuing mechanism so either you get it or you do not and are forced to continually retry.

The Latch Detail section presents a detailed view of latch activity. The table below lists the information available:

Column Description

Latch Name

The name of the latch.

Gets

The number of times the latch was requested by a process.

Misses

The number of failed attempts to acquire the latch on the first attempt.

Immediate Gets

The number of nowait requests for a latch.

Immediate Misses

The number of failed nowait attempts to acquire the latch on the first attempt.

Sleeps

The number of requests that paused while waiting for a latch.


Location

Oracle Performance Analyst Statistics > Memory View (Oracle Performance Analyst) > Latch Activity Tab

Metrics

Common indicators of latch contention are a latch miss ratio (which records willing-to-wait mode latch requests) and latch immediate miss ratio (which records no-wait mode latch requests). These statistics reflect the overall health of how often latch requests were made and satisfied without waiting. If either of these exceed 1%, then latch contention may be causing system slowdowns.

There are a few latch contention situations that you can recognize and get to the bottom of quickly. The table below describes them:

Contention Situation Description

Cache buffer chain latch

This latch is responsible for protecting paths to database block buffers in the buffer cache. Very high I/O loads tend to cause contention for this latch. You can alleviate contention somewhat by adding more buffers to the cache (through the db_block_buffers parameter) or by adding more LRU latch chain latches with the db_block_lru_latches parameter.

Library cache latches

This latch protects cached SQL statements in the library cache area of the Oracle shared pool. Contention for this latch is the usual result of literals being used for SQL statements instead of bind variables.

Library Cache Tab

The Library Cache tab includes the following sections:

Library Cache Performance

The library cache holds commonly used SQL statements - basically database code objects. A method for improving performance in Oracle is to encourage the reuse of SQL statements so expensive parse operations may be avoided. The library cache assists this tuning effort.

The Library Performance sectioned tab provides insight into how efficiently the library cache is operating. The table below describes the information available in this section:

Column Description

Namespace

The region of the library cache.

Gets

The number of times a lock was requested for objects in the particular namespace.

Get Hit Ratio

The percentage of times (with 100% being the maximum) that the object was found in the cache.

Pins

The number of times a pin was requested for objects of this namespace.

Pin Hit Ratio

The percentage of times (with 100% being the maximum) that pin requests were successful.

Reloads

The number of times a piece of the object had to be brought back in from disk to the cache, most likely because it was flushed from the shared pool.

Invalidations

The number of times objects in this namespace were marked invalid because a dependent object was modified.


Location

Oracle Performance Analyst Statistics > Memory View (Oracle Performance Analyst) > Library Cache Tab

Metrics

NOTE: An overall high library cache hit ratio is a desirable thing. You should strive for a hit ratio between 95-100%, with 99% being a good performance benchmark for code reuse. When a database is first started, the library cache hit ratio, along with the individual region hit ratios, will not be at an optimal level because all code being used will be relatively new, and as such, must be parsed and placed into the shared pool. If, however, after a solid hour or two of steady database time, the library cache hit ratio has not increased to desirable levels, you should look into the possibility of increasing the shared_pool_size parameter. To keep important code objects from being aged out of the library cache, you can use the DBMS_SHARED_POOL package to pin frequently used code objects in memory so they will always be there when needed.

Pinned Objects

To keep important code objects from being aged out of the library cache, you can use the DBMS_SHARED_POOL package to pin frequently used code objects in memory so they will always be there when needed. The Pinned Objects section displays all objects in the library cache that have pinned. The table below lists the information available in this section:

Column Description

Owner

The user account that owns the object.

Name

The name of the object.

Type

The type of the object: INDEX, TABLE, CLUSTER, VIEW, SET, SYNONYM, SEQUENCE, PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TRIGGER, CLASS, OBJECT, USER, DBLINK.

Shareable Memory

The amount of memory consumed by the object in the shared pool.

Loads

The number of times the object has been loaded into the cache. Note that this count also increases when an object has been invalidated.

Executions

The number of times the object has been executed by a session thread.

Locks

The number of users actively locking the object.

Pins

The number of user actively pinning the object.

Pinned?

Indicates (YES or NO) if this object has been pinned in memory with the DBMS_SHARED_POOL package.

Shared Pool Tab

The main components of the shared pool memory are the library and dictionary caches. Many of the caches in the shared pool increase or decrease in size automatically as necessary. The Shared Pool tab of the Memory Detail view presents the following sections:

Shared Space Pool Utilization - a graphical representation of the amount of used and free memory.

Shared Pool Fragmentation - displays the following statistics:

  • Bucket: A work area defined by its optimal memory requirement.
  • Free Space: The amount of free space in the bucket.
  • Free Chunks: The number of free chunks that are available to be allocated from the shared pool.
  • Average Size: The average size of a chunk.
  • Largest: The size of the largest chunk.

Shared Pool Usage - The Shared Pool Usage section shows the following statistics:

  • Shared Pool Component Name
  • Amount Used (bytes)
  • Percent Used

PGA Tab

The PGA tab displays statistical measures and techniques that let you manage a session's Program Global Area.

Note: This information is available for Oracle9i and later.

The PGA tab includes the following sections:

For more information, see Memory View (Oracle Performance Analyst).

PGA Overview

Oracle's Program Global Area (PGA) is a private memory region containing data and control information for every connected process. Examples of PGA information are the runtime area of a cursor and memory sort space. If shared servers are used, then part of the runtime area can be located in the SGA.

Intensive queries cause a big portion of the PGA to be dedicated to work areas supporting memory intensive operators, such as the following:

  • Sort-based operators (for example, ORDER BY, GROUP BY, ROLLUP, window functions)
  • Hash-join
  • Bitmap merge
  • Bitmap create
  • Write buffers used by bulk load operations

A sort operator uses a sort area to perform the in-memory sort of a set of rows. Similarly, a hash-join operator uses a hash area to build a hash table.

Note: This information is available for Oracle9i and later.

Location

Oracle Performance Analyst Statistics > Memory View (Oracle Performance Analyst) > PGA Tab

Metrics

The size of a PGA area can be controlled and tuned. Larger work areas can significantly improve performance of session work, with the trade off being higher memory consumption. The size of a session's PGA should be big enough so that it can accommodate the input data and auxiliary memory structures allocated by its requested SQL actions. This is known as the optimal size of a PGA work area. When the size of a PGA work area is smaller than optimal, the response time increases, because an extra pass is performed over part of the input data. This is known as the one-pass size of the PGA work area. Under the one-pass threshold, when the size of a work area is smaller than its input data size, multiple passes over the input data are needed. This could drastically increase the response time of the session. This is known as the multi-pass size of the work area. The goal is to have most work areas running with an optimal size (more than 90% or more for OLTP systems), while a smaller amount are running with a one-pass size (less than 10%). Multi-pass execution should be completely avoided. Prior to Oracle9i, the PGA was controlled using the SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE and CREATE_BITMAP_AREA_SIZE parameters. Setting these parameters is difficult, because the maximum PGA size is ideally selected based on data input size and total number of work areas active in the system. These two factors vary from one work area to another and from one point in time to another.

Correction

With Oracle9i, you can simplify and improve the way PGA memory is allocated, by enabling automatic PGA memory management. In this mode, Oracle dynamically adjusts the size of the portion of the PGA memory dedicated to work areas, based on an overall PGA memory target explicitly set by the DBA. To enable automatic PGA memory management, you have to set the initialization parameter PGA_AGGREGATE_TARGET.

The table below describes two metrics you should to watch:

Metric Description

Over allocation count

Over-allocating PGA memory can happen if the value of PGA_AGGREGATE_TARGET is too small to accommodate the PGA other component in the previous equation plus the minimum memory required to execute the work area workload. When this happens, Oracle cannot honor the initialization parameter PGA_AGGREGATE_TARGET, and extra PGA memory needs to be allocated. If over-allocation occurs, you should increase the value of PGA_AGGREGATE_TARGET.

Cache hit percentage ORACLE 9.2 AND LATER

This metric is computed by Oracle to reflect the performance of the PGA memory component. A value of 100% means that all work areas executed by the system since instance start-up have used an optimal amount of PGA memory. When a work area cannot run optimally, one or more extra passes is performed over the input data. This reduces the cache hit percentage in proportion to the size of the input data and the number of extra passes performed.

PGA Workarea Summary

Oracle's Program Global Area (PGA) is a private memory region containing data and control information for every connected process. Examples of PGA information are the runtime area of a cursor and memory sort space. If shared servers are used, then part of the runtime area can be located in the SGA.

Intensive queries cause a big portion of the PGA to be dedicated to work areas supporting memory intensive operators, such as the following:

  • Sort-based operators (for example, ORDER BY, GROUP BY, ROLLUP, window functions)
  • Hash-join
  • Bitmap merge
  • Bitmap create
  • Write buffers used by bulk load operations

A sort operator uses a sort area to perform the in-memory sort of a set of rows. Similarly, a hash-join operator uses a hash area to build a hash table.

Note: This information is available for Oracle9i and later.

Location

Oracle Performance Analyst Statistics > Memory View (Oracle Performance Analyst) > PGA Tab

Metrics

The size of a PGA area can be controlled and tuned. Larger work areas can significantly improve performance of session work, with the trade off being higher memory consumption. The size of a session's PGA should be big enough so that it can accommodate the input data and auxiliary memory structures allocated by its requested SQL actions. This is known as the optimal size of a PGA work area. When the size of a PGA work area is smaller than optimal, the response time increases, because an extra pass is performed over part of the input data. This is known as the one-pass size of the PGA work area. Under the one-pass threshold, when the size of a work area is smaller than its input data size, multiple passes over the input data are needed. This could drastically increase the response time of the session. This is known as the multi-pass size of the work area. The goal is to have most work areas running with an optimal size (more than 90% or more for OLTP systems), while a smaller amount are running with a one-pass size (less than 10%). Multi-pass execution should be completely avoided. Prior to Oracle9i, the PGA was controlled using the SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE and CREATE_BITMAP_AREA_SIZE parameters. Setting these parameters is difficult, because the maximum PGA size is ideally selected based on data input size and total number of work areas active in the system. These two factors vary a lot from one work area to another and from one point in time to another.

Correction

With Oracle9i, you can simplify and improve the way PGA memory is allocated, by enabling automatic PGA memory management. In this mode, Oracle dynamically adjusts the size of the portion of the PGA memory dedicated to work areas, based on an overall PGA memory target explicitly set by the DBA. To enable automatic PGA memory management, you have to set the initialization parameter PGA_AGGREGATE_TARGET. If multi-pass percentages are consistently non-zero, you should increase the size of PGA_AGGREGATE_TARGET.

PGA Session Detail

Oracle's Program Global Area (PGA) is a private memory region containing data and control information for every connected process. Examples of PGA information are the runtime area of a cursor and memory sort space. If shared servers are used, then part of the runtime area can be located in the SGA.

Intensive queries cause a big portion of the PGA to be dedicated to work areas supporting memory intensive operators, such as the following:

  • Sort-based operators (for example, ORDER BY, GROUP BY, ROLLUP, window functions)
  • Hash-join
  • Bitmap merge
  • Bitmap create
  • Write buffers used by bulk load operations

A sort operator uses a sort area to perform the in-memory sort of a set of rows. Similarly, a hash-join operator uses a hash area to build a hash table.

The PGA Session Detail section displays PGA usage for all connected sessions. The table below describes the information in this section:

Data Description

SID

The unique identified given the session by Oracle.

Username

The user account a session is using.

O/S ID

The operating system ID of the process.

Logon Time

The time the session logged into Oracle.

Machine Name

The name of the machine running the process.

PGA Alloc Memory

The amount of allocated PGA memory for the process.

PGA Used Memory

The amount of freeable or usable memory for the process.

PGA Max Memory

The maximum amount of PGA memory used by the process.

Note: This information is available for Oracle9i and later.

Location

Oracle Performance Analyst Statistics > Memory View (Oracle Performance Analyst) > PGA Tab