Objects View (Oracle Performance Analyst)

From DBArtisan
Jump to: navigation, search

Go Up to Oracle Performance Analyst Statistics

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

Home

The Objects 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 examine 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 ratios are used on the Performance Analyst Objects home page to succinctly communicate the general session-based performance levels of the monitored database:

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

Active Rollback Ratio

To undo changes made to the Oracle database from within a transaction, Oracle writes data to individual rollback segments. You can also use these to maintain read consistency for multiple users of modified data. Because Rollback Segments read and write data, they can become very hot areas for I/O. This statistic is a ratio that displays the percentage of rollback segments currently involved in work.

Seeing consistent ratios of 80% or higher could indicate the need for more rollback segments on the database.

Location

Oracle Performance Analyst Statistics > Objects View (DBArtisan - Oracle Performance Analyst) > Home > Key Ratio Analysis Pane

Correction

Consider the use of UNDO management, which is where Oracle itself automatically manages rollback segments in special UNDO tablespaces.

Chained Row Fetch Ratio

In normal circumstances, a row of data should fit completely inside one Oracle block. Sometimes, however, this is not the case and the table suddenly contains chained or migrated rows (rows that span more than one data block).

Chaining occurs when a row is initially too large to fit inside one block. Two or more blocks are used by Oracle to hold the row. Migration deals with rows that have grown so much that they can no longer be contained within their original block. When this occurs, Oracle relocates the row out of its original block into another block, but leaves a pointer behind to indicate the relocation.

Both chaining and migration force Oracle to perform more than one I/O to retrieve data that could normally be obtained with a single I/O operation, with the end result being degraded performance.

The chain row fetch ratio statistic represents the percentage of rows fetched that were of either the chained or migrated row type.

Location

Oracle Performance Analyst Statistics > Objects View (DBArtisan - Oracle Performance Analyst) > Home > Key Ratio Analysis Pane

Metrics

If the amount of chained rows in your tables exceeds 25-30%, you should take steps to eliminate the problem. Further, if the amount of chained rows accessed in your system vs. total rows accessed approaches 20-30%, you can start the process of eliminating the chained and migrated rows.

Correction

You can locate tables that contain chained rows. Once found, there are a couple of ways to reorganize tables to remove the chaining/migration problem. However, the best way to deal with chained and migrated rows is to prevent them from occurring. The table below describes two methods:

Method Description

Use a large block size

Because chaining and migrations occur when the Oracle block is too small to hold the rows in question, make sure you are using a large enough block size for each database you create. An 8KB block size or higher is normally recommended to help stave off chained and migrated rows. If you are using Oracle9i or later, you can create special tablespaces that have larger block sizes (16-32KB) than the overall database block size and place any table that is a candidate for chained/migrated rows into them.

Use proper values of PCTFREE for tables

The necessary amount of percent free in a table helps prevent row migrations from occurring. If you have a database that houses rows with the potential to grow substantially over their initially inserted size, provide a liberal amount of PCTFREE for each of your tables.

Bottleneck Analysis Pane

When using bottleneck analysis to troubleshoot a database, you cannot rely only on the information contained in the wait event views that Oracle provides. For example, an object attempts to extend into another extent of space in a tablespace and yet be denied if no such free space exists. Such a failure is not reflected in any wait event, but still represents a very real bottleneck to the database. In the same way that you cannot depend on only a few ratios to properly carry out ratio-based performance analysis, an administrator must include other statistical metrics in their overall bottleneck analysis framework to obtain an accurate performance risk assessment. Performance Analyst works to identify bottlenecks in your database that fall outside of pure wait events so you can get a total picture of all stoppages in your system. The Performance Analyst Objects home page displays object-related bottlenecks decreases the overall performance of your system.

The following bottleneck indicators are used on the Performance Analyst Objects home page to communicate the general overall conditions of objects in the monitored database:

The following statistics, available on this pane, duplicate statistics available on the Top Session Bottlenecks of the Home View (Oracle Performance Analyst) page:

Chained Tables

A row of data should fit completely inside one Oracle block. Sometimes, a table suddenly contains chained or migrated rows, which are rows that span more than one data block.

Chaining occurs when a row is initially too large to fit inside one block. Two or more blocks are used by Oracle to hold the row. Migration deals with rows that have grown so much that they can no longer be contained within their original block. Oracle relocates the row out of its original block into another block, but leaves a pointer behind to indicate the relocation.

Both chaining and migration force Oracle to perform more than one I/O to retrieve data that could normally be obtained with a single I/O operation, with the end result being degraded performance.

The Chained Tables statistic identifies the number of tables in the database that contain chained or migrated rows.

Location

Oracle Performance Analyst Statistics > Objects View (DBArtisan - Oracle Performance Analyst) > Home > Bottleneck Analysis Pane

Metrics

Seeing many chained/migrated tables could indicate a database whose block size is undersized. However, even one chained/migrated row table can be a problem if that table is an often-referenced or scanned table.

Correction

You can locate the actual tables that contain chained/migrated rows. Once found, there are a couple of ways to reorganize tables to remove the chaining/migration problem. However, the best way to deal with chained and migrated rows is to prevent them from occurring. The table below describes two methods:

Method Description

Use a large block size

Because chaining and migrations occur when the Oracle block is too small to hold the rows in question, make sure you are using a large enough block size for each database you create. An 8KB block size or higher is normally recommended to help stave off chained and migrated rows. If you are using Oracle9i or later, you can create special tablespaces that have larger block sizes (16-32KB) than the overall database block size and place any table that is a candidate for chained/migrated rows into them.

Use proper values of PCTFREE for tables

The necessary amount of percent free in a table helps prevent row migrations from occurring. If you have a database that houses rows with the potential to grow substantially over their initially inserted size, provide a liberal amount of PCTFREE for each of your tables.

For more information, see Bottleneck Analysis Pane.

Session Blocks

A single blocking user has the potential to stop work for nearly all other processes on a small system, and can cause major headaches even on large systems. Although Oracle supports unlimited row-level locking, blocking lock situations do crop up. Blocks are most often caused by user processes holding exclusive locks and not releasing them via a proper COMMIT frequency.

Note: This statistic is also called Current Object Blocks on the Home View (Oracle Performance Analyst) page, and Sessions Blocked on the Users View (Oracle Performance Analyst) Home page.

Location

Oracle Performance Analyst Statistics > Objects View (DBArtisan - Oracle Performance Analyst) > Home > Bottleneck Analysis Pane

Metrics

Investigate any indicator above zero immediately before the situation has a chance to mushroom.

Correction

Once discovered, a blocking lock situation can normally be quickly remedied. You can issue a KILL against the offending process, which eliminates the user's hold on the objects they were accessing. Other user processes then almost always complete in an instant. Discovering the blocked lock situation is eased by using tools like Performance Analyst, but preventing the blocking lock situation in the first place is more difficult. You can look at the Users View (Oracle Performance Analyst) detail and view all current blocking locks to see exactly which sessions are holding the currently restrictive locks. The culprit of blocking lock scenarios is usually the application design, or the SQL being used within the application itself. Properly coding an application to reference database objects in an efficient order, and then using the right SQL to get the job done, is an art. Most DBAs who have had to face Oracle Forms applications have suffered through the dreaded SELECT … FOR UPDATE statements that place unnecessary restrictive locks on nearly every read operation, and know all too well that good coding practice is important. The key to avoiding lock contention is to process user transactions in the quickest and most efficient manner possible - something not always easy to do.

Invalid/ Objects

Objects like procedures, packages, functions, triggers, and views can become invalidated for a variety of reasons. The main cause is generally a dependent object that has been altered or removed from the system. However, other objects, like indexes, can become invalid also due to scenarios like SQL*Loader problems. If an object that has become invalid is still referenced (through an application or SQL query tool), a variety of problems can result. Sometimes Oracle reports a clear error stating the problem, while other times seemingly odd behavior is exhibited by the database.

Location

Oracle Performance Analyst Statistics > Objects View (DBArtisan - Oracle Performance Analyst) > Home > Bottleneck Analysis Pane

Metrics

There is no reason to have invalid objects in a production database. If your production databases have invalid objects that are no longer needed, promptly remove them from each system. Any needed objects that are indicating an invalid status should quickly be fixed before access problems develop. It is very normal for development databases to have invalid objects because developers create, modify, and compile objects all the time. The only invalid object that really should not be present in either a development or production database is an invalid index.

Correction

If code objects have become invalidated, you can issue an ALTER … COMPILE command to see if they compile properly and become valid once again. If they do not, then check the USER_ERRORS view for any resulting error messages. Indexes can be validated once more by using the ALTER INDEX … REBUILD command.

Locked Objects

Locked Objects is a count of all objects on the system that currently have some form of lock against them.

Note: Drilling down into the count of locked objects displays detail on each object that is locked, along with the user process holding the lock and the type of lock held.

Location

Oracle Performance Analyst Statistics > Objects View (DBArtisan - Oracle Performance Analyst) > Home > Bottleneck Analysis Pane

Table High Water Mark Problems

One form of object fragmentation revolves around what is called a table's high water mark. Tables that are the victim of much insert-delete activity can develop performance problems due to wasted space being present. A table's high-water mark equals the last block in the table that was used to hold data. The problem in Oracle is that this high-water mark is not reset by DELETE activity, so it is possible for a table to have absolutely no data in it but contain a high-water mark that is many blocks high. When such a table is scanned, Oracle will read up to the high-water mark even if no rows exist in the table at all. This can make for some unnecessarily large scan times. For a real world example, see Bottleneck Analysis Pane.

Location

Oracle Performance Analyst Statistics > Objects View (DBArtisan - Oracle Performance Analyst) > Home > Bottleneck Analysis Pane

Metrics

Seeing non-zero numbers for this metric could indicate that table scans are taking longer than they should.

Correction

Performance Analyst does not currently provide diagnostics that identify the actual tables that contain artificially inflated high water marks. IDERA Space Analyst provides all the levels of detail regarding table high water marks plus a reorganization wizard that can reset the high water mark and thereby improve performance of table scans against tables suffering from this problem.

Tables/Indexes in Same Tablespace

One common Oracle practice is to physically separate tables and their child indexes onto distinct physical drives or file systems. Doing so reduces the potential for I/O contention when each object is accessed. The Tables/Indexes in Same Tablespace statistic displays a count of all tables whose index(es) reside in the same tablespace as they do.

Location

Oracle Performance Analyst Statistics > Objects View (DBArtisan - Oracle Performance Analyst) > Home > Bottleneck Analysis Pane

Metrics

For development databases where performance is not an issue, seeing high counts of this metric is not an issue. Even on production systems, high counts of this metric may not be a problem, or cannot be avoided, if the server is configured with one large RAID5 disk configuration, each distinct drive or file system does not have its own controller, or there simply are not enough disks on the server to perform adequate object striping.

Correction

If the server is configured to allow for intelligent object striping, and you see a large count of tables and indexes that are housed in the same tablespace, you can: Create a separate tablespace for indexes on a drive different than that used for tables, and perform an ALTER INDEX … REBUILD operation on all indexes to move them to the new tablespace. Certain versions of Oracle even allow the rebuild operation to occur online (using the ONLINE keyword of the REBUILD command). Use the export/import utility to move indexes to a new tablespace. Use the ALTER TABLE … MOVE command to move tables to a new tablespace. Index-organized tables can be moved online.

SQL Analysis Pane

Certain objects in an Oracle database are accessed more than others. These objects can become a source of contention given certain conditions. The SQL Analysis section of the Objects home page identifies the most accessed tables on the database.

Metrics

DML activity against tables can cause contention for space management objects like free lists. Oracle9i or later provides automatic segment management, which can remove problems with free lists and the like.

Object/Buffer Pool Placement Pane

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

Buffer 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 Object/Buffer Pool Placement section identifies how many objects are currently assigned to the various caches.

Location

Oracle Performance Analyst Statistics > Objects View (DBArtisan - Oracle Performance Analyst) > Home > SQL Analysis Pane

Metrics

When looking at overall database I/O activity, you should keep an eye out for objects that you can place 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 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 in Oracle8 or later) or existing objects can be set into a different pool with the ALTER command. NOTE: Tables, partitions, and indexes can 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 cache as long as possible using the CACHE parameter. For example, issuing the command ALTER TABLE…CACHE specifies that the blocks retrieved for a 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. You can also use the CACHE hint in SQL statements to cache a table, but used in this form, the blocks are only cached until the next time the database is shut down.

User Object Analysis Pane

All objects are created inside of user accounts called schema. The User Object Analysis section of the Performance Analyst Objects home page shows schema that are ranked the worst in the several categories. The table below lists the categories:

Worst Schema for Description

Chained Row Tables

The schema containing the most tables with chained rows is identified.

Invalid Objects

The schema containing the most invalid objects is identified.

Extent Problems

The schema containing the most objects with maximum extent limit or extent space deficit problems is identified.


Correction

To determine the degree and severity of each type of problem, go into detailed views that present specific information for each type of object problem.

Hot Objects Tab

The Hot Objects tab includes the following sections:

Hot Tables

Certain objects in an Oracle database are accessed more than others. These objects can become a source of contention given certain conditions. The Hot Tables section identifies tables that are being frequently accessed through various SQL statements. The table below describes the information available in this section:

Column Description

Table Owner

The owner of the table.

Table Name

The name of the table.

Issued Command

The SQL statement command issued against the table.

Executions

The number of SQL executions the object has experienced.

Disk Reads

The number of estimated disk reads from the object.

Buffer Gets

The number of estimated logical I/Os from the object.

Rows Processed

The number of estimated rows processed from the object.


Location

Oracle Performance Analyst Statistics > Objects View (DBArtisan - Oracle Performance Analyst) > Hot Objects Tab

Metrics

DML activity against tables can cause contention for space management objects like free lists. Oracle9i and later provides automatic segment management, which can remove problems with free lists and the like.

Hot Code

Certain objects in an Oracle database are accessed more than others. Data objects can become a source of contention given certain conditions, while code objects rarely cause contention issues. The Hot Code section identifies code objects (procedure, functions, etc.) that are being frequently accessed through various SQL statements. The table below describes the information available in this section:

Column Description

Object Owner

The owner of the object.

Object Name

The name of the objects.

Object Type

The type of object (package, etc.).

Executions

The number of estimated executions for the object.

Loads

The number of times the object was loaded into the shared pool.

Locks

The number of locks the object has experienced.

Pins

The number of times the object was pinned in the shared pool.


Location

Oracle Performance Analyst Statistics > Objects View (DBArtisan - Oracle Performance Analyst) > Hot Objects Tab

Metrics

Often referenced code objects should be pinned in the shared pool using the Oracle DBMS_SHARED_POOL package. This can also be done through DBArtisan’s pinned code utility. Objects with many executions and loads should be considered candidates for pinning.

Invalid Objects Tab

The Invalid Objects tab includes the following sections:

Invalid Objects Summary

Objects like procedures, packages, functions, triggers, and views can become invalidated for a variety of reasons, with the main cause being a dependent object that has been altered or removed from the system. However, other objects, like indexes, can become invalid also due to scenarios like SQL*Loader problems. If an object that has become invalid is still referenced (through an application or SQL query tool), a variety of problems can result. Sometimes Oracle will report a clear error stating the problem, while other times seemingly quirky behavior will be exhibited by the database. In any event, as a DBA you should be on the lookout for objects in your database that have suddenly become invalid.

The Invalid Objects Summary section displays a count of the various objects in the database that are invalid.

Location

Oracle Performance Analyst Statistics > Objects View (DBArtisan - Oracle Performance Analyst) > Invalid Objects Tab

Metrics

There is no reason to have invalid objects in a production database. If your production databases have invalid/unusable objects that are no longer needed, you should promptly remove them from each system. Any needed objects that are indicating an invalid status should quickly be fixed before access problems develop. Correcting invalid objects like procedures and views often involves performing an ALTER…COMPILE operation. If the object status does not return to VALID, then further examination is warranted. It is very normal for development databases to have invalid objects because developers will no doubt be creating, modifying, and compiling objects all the time. The only invalid object that really should not be present in either a development or production database is an invalid index.

Invalid Objects Detail

Objects like procedures, packages, functions, triggers, and views can become invalidated for a variety of reasons, with the main cause being a dependent object that has been altered or removed from the system. However, other objects, like indexes, can become invalid also due to scenarios like SQL*Loader problems. If an object that has become invalid is still referenced (through an application or SQL query tool), a variety of problems can result. Sometimes Oracle will report a clear error stating the problem, while other times seemingly quirky behavior will be exhibited by the database. In any event, as a DBA you should be on the lookout for objects in your database that have suddenly become invalid.

The Invalid Objects Detail section displays information concerning invalid objects. The table below describes the information available in this section:

Column Description

User Account

The user account that owns the objects.

Object Name

The name of the invalid object.

Object Type

The type of object (procedure, view, etc.).

Created On

The timestamp of when the objects was created.

Last DDL Date

The last structural modification date for the object.


Location

Oracle Performance Analyst Statistics > Objects View (DBArtisan - Oracle Performance Analyst) > Invalid Objects Tab

Metrics

There is no reason to have invalid objects in a production database. If your production databases have invalid objects that are no longer needed, you should promptly remove them from each system. Any needed objects that are indicating an invalid status should quickly be fixed before access problems develop. Correcting invalid objects like procedures and views often involves performing an ALTER…COMPILE operation. If the object status does not return to VALID, then further examination is warranted. It is very normal for development databases to have invalid objects because developers will no doubt be creating, modifying, and compiling objects all the time. The only invalid object that really should not be present in either a development or production database is an invalid index.

Objects Summary Tab

The Objects Summary tab includes the following sections:

Section Description

Global Summary

This section displays a count of all user objects in the database (minus SYS and SYSTEM objects).

User Summary

This section displays a count across user accounts of all the major object types in the database. NOTE SYS and SYSTEM accounts are not included.

Tables/Indexes in Same Tablespace Tab

To help avoid physical I/O contention, it is wise to separate a table and its indexes onto separate physical drives. This is normally accomplished by putting tables into one tablespace (whose datafiles exist on one drive) and their indexes into a different tablespace (whose datafiles exist on another drive).

The Tables/Indexes in Same Tablespace tab identifies indexes whose tables reside in the same tablespace as they do. The table below describes the information available in this section:

Column Description

Table Owner

The owner of the table.

Table Name

The name of the table.

Index Name

The name of the index that resides in the same tablespace as the table.

Tablespace Name

The name of the tablespace.


Metrics

If tables and indexes are found in the same tablespace, then either the table or index should be moved to a different tablespace if the server is set up in such a way that different drives and controllers are available that make the relocation worthwhile. Indexes are traditionally easier to move than tables. They can be relocated through many reorganization tools (like IDERA Space Analyst) or through the manual use of the ALTER INDEX … REBUILD command.