Space View (Oracle Performance Analyst)

From RapidSQL
Jump to: navigation, search

Go Up to Oracle Performance Analyst Statistics

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

Home

The Space Home page includes the following sections:

Space Analysis Pane

The Oracle tablespace is the logical container for the physical storage needs of a database. The Space Analysis section displays details for all the tablespaces for a particular database, including their total, used, free space, and percent free, as well as if each can automatically extend to support incoming space requests.

Metrics

If any tablespace's free space percent amount goes below 10%, and at least one tablespace’s datafiles does not have AUTOEXTEND enabled (or the datafile has reached its extend limit), you should take action to ensure that the tablespace does not run out of available free space.

Correction

There are two things you can do to ensure that a tablespace does not run out of available free space:

  1. First, you should look into the use of Oracle's AUTOEXTEND feature. AUTOEXTEND lets you give an Oracle tablespace the ability to auto-grow when it has exhausted the free space contained within. You can let a tablespace grow in an unlimited fashion or put constraints on it to stop at a certain point. You can also dictate how much more free space the tablespace gets each time it needs more space than is available. However, AUTOEXTEND enabled for a tablespace does not mean that you cannot run out of space. Remember you still have the physical server limitations to contend with. Make sure you (or your sysadmin) keep a careful eye on the server drives that house your Oracle database files for available free space.
  2. If the free space on a server drive nears its limit, disable AUTOEXTEND for the datafile(s) that are on the target drive, and use the ALTER TABLESPACE … ADD DATAFILE command to place a new datafile for the tablespace on another drive that has more free space to offer.
    Tip: AUTOEXTEND is not a replacement for proactive space planning. When extra space is needed by the database, and AUTOEXTEND is activated by Oracle, performance slows as Oracle allocates more space for the tablespace. Avoiding AUTOEXTEND aids performance, albeit in a small way.

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 may attempt 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 Space home page displays space-related bottlenecks that can decrease overall performance of your system.

The following bottleneck indicators are used on the Performance Analyst Space home page to succinctly communicate the general overall space situation of the monitored database:

Autoextend Datafiles at Extend Limit

You can specify that one or more tablespace datafiles can automatically grow by enabling the datafile’s AUTOEXTEND property. you can also specify that the datafile not be permitted to grow beyond a certain limit, and once that limit is reached, the datafile and/or the tablespace may be permanently out of space.

Location

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

Metrics

You should not see any positive indicator that any datafile has reached its extend limit. While it might be okay from the standpoint that a datafile cannot simply grow anymore (if the tablespace has other datafiles that can grow or that no more space is added to the tablespace), disable AUTOEXTEND on the datafile.

Correction

If a datafile has reached its extend limit, you can take a number of actions: Eliminate the extend limit for the datafile either by increasing the limit size or allowing to grow in an unlimited fashion. If the datafile is on a disk or file system that has no free space left, you can add another datafile to the tablespace to allow for space expansion and alter the problem datafile to have AUTOEXTEND disabled. If the datafile is on a disk or file system that has no free space left, the datafile can be physically relocated to another drive or file system with more free space.

Objects at Maximum Extents

When an object in Oracle (table, index, table partition, etc.) needs to expand, Oracle automatically allocates another extent of space to accommodate the incoming data. However, a table or index can reach its maximum extent limit, which is the maximum number of extents for an object.

Location

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

Metrics

Unless an object is static in nature, and is not expected to grow in size, no object should ever get to its maximum extent limit.

Correction

If a maximum extent limit is reached for an object, you can take a number of actions: The object can be reorganized into a locally-managed tablespace where no maximum extent limit is applied to any object. The object can be altered to have unlimited extents. The object can be reorganized into one or a few extents.

The good news concerning locally-managed tablespaces is that unlimited extents are the rule, so if you choose to use these storage structures in your database, you will not have to worry about an object ever reaching a maximum extent limit. Some DBAs have expressed concerns over whether an object having hundreds or thousands of extents experiences performance problems when full table scans or similar operations are performed against them. While most Oracle experts agree that such I/O degradation might have been experienced in earlier Oracle versions, most feel that such is not the case any longer, especially when locally-managed tablespaces are used.

Objects with No Room to Expand

When an object in Oracle (table, index, table partition, etc.) needs to expand, Oracle automatically allocates another extent of space to accommodate the incoming data. However, an object may be prohibited from expanding for a number of reasons:

  • The tablespace does not have enough free space to accommodate the extent request, and cannot automatically extend.
  • The tablespace has enough total free space, but the space is not contiguous in nature (and the tablespace cannot automatically extend) and therefore the object cannot extend.

Location

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

Metrics

Unless an object is static in nature, and is not expected to grow in size, no object should ever be prohibited from acquiring its next extent of space.

Correction

If an object cannot extend, you can take a number of actions including: One or more of the tablespace’s datafiles can have its AUTOEXTEND property enabled, which lets the object to grow even though there is no space currently available in the tablespace. The object can be reorganized into a locally-managed tablespace all extents are uniform in nature. The object can be altered to have a reduced next extent size.

Offline Tablespaces

A tablespace ONLINE status indicates that the tablespace is in normal read/write mode. Tablespaces marked read only displays a status of READ ONLY, while tablespaces engaged in backup mode indicates such in their status. An OFFLINE tablespace during normal processing hours is a cause for concern.

Location

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

Metrics

No tablespace should have a status of OFFLINE during normal business hours, unless you are performing maintenance on it.

Correction

If a tablespace has a status of OFFLINE, you can make it ONLINE using the ALTER TABLESPACE command.

Top Fragmented Datafiles

This section shows the tablespaces datafiles suffering from the highest levels of free space fragmentation in the database. Tablespaces are made up of object segments and space extents. Extents are either allocated to object segments or are free. When a tablespace is initially populated, all objects are neatly packed together in the front of the tablespace and all remaining free space is in one free chunk at the end. As objects grow (or extend) they are given new extents of space in the tablespace/datafile. If they are dropped, pockets of free space begin to appear throughout the tablespace. These pockets of space take one of two forms. The table below describes these forms:

Free Space Description

Honeycombs

Pockets of free space that are adjacent to one another.

Bubbles

Pockets of free space that are trapped between object extents in the tablespace.


Location

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

Metrics

If you see a datafile that has many chunks of free space, determine if the tablespace is experiencing honeycomb or bubble fragmentation. You can handle honeycomb fragmentation quite easily, whereas bubble fragmentation is more difficult to solve.

Correction

You can eliminate honeycomb fragmentation with the ALTER TABLESPACE…COALESCE command. Issuing this command combines all pockets of adjacent free space into one extent. Each database maintenance plan should include a job that coalesces all the free honeycombs in a tablespace into one free chunk. Although Oracle is supposed to perform this operation automatically through the SMON process, it requires you to have the PCTINCREASE parameter of the tablespace set to a nonzero value. Having PCTINCREASE set to a value greater than zero encourages tablespace fragmentation through disparately sized extents. Plus, using SMON in this way is not efficient or entirely reliable.

You can temporarily solve bubble fragmentation by performing a total tablespace reorganization. A better long-term solution for Oracle databases 8.1.5 or later is to convert tablespaces over to locally managed tablespaces. With locally managed tablespaces you either specify the initial extent size and let Oracle automatically size all other extents, or specify a uniform extent size for everything. Problems caused by fragmentation then become a thing of the past.

To help stave off fragmentation problems:

  • Set PCTINCREASE to zero for all tablespaces and objects to promote same-sized extents.
  • Specify equal-sized allotments for your INITIAL and NEXT object storage parameters.
  • Group objects with like growth and storage needs together in their own tablespaces.
  • Considering converting any dictionary-managed tablespace to locally-managed.

You should also avoid fragmentation in the SYSTEM tablespaces. The best ways to do this include:

  • Ensure no user has a DEFAULT or TEMPORARY tablespace assignment of SYSTEM.
  • Ensure no user has a quota set for SYSTEM.
  • Ensure no user has been granted the UNLIMITED TABLESPACE privilege.

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.

The SQL Analysis section of the Space home page shows a count of all SQL statements that have executed INSERT, UPDATE, DELETE, and TRUNCATE commands. Seeing these statistics should help you determine how much space-related SQL activity is occurring on your system.

Metrics

The statistics for this section can be used in many different ways. For example, large numbers of inserts could indicate a growing database and that you should keep a watchful eye on free space. Heavy update numbers could mean trouble for objects containing many indexes (indexes typically undergo splits and fragmentation when large volumes of updates are thrown their way). Many deletes might signal potential table high water mark problems.

Redo Logs

Oracle's redo logs are hotbeds of I/O activity in databases that store heavily modified data. Redo log files are used to perform database recovery in the event of a system crash. Redo logs are written to in a cyclical fashion - each log file is filled up before Oracle moves on to the next file. The redo bytes statistic reflects the total amount of redo generated in bytes since the last refresh. The redo wastage bytes indicate how many bytes were wasted by redo log buffers being written before they were full.

Location

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

Metrics

Viewing a high percentage of wasted bytes to overall redo bytes can help you identify if redo wastage is a problem on your system.

Correction

Sometimes heavy redo wastage occurs when the log_checkpoint_interval parameter is set too high. To fix this: Edit the Init.ora file for the database. Change the amount of log_checkpoint_interval to a lower value. Cycle the Oracle server when possible to allow the new value to take effect. Monitor new value to see if performance improves. For Oracle9i and later, you can dynamically alter the log_checkpoint_interval parameter without stopping/starting the database.

User Space Analysis Pane

The Top Space Hogs section details the schema in the database that own the most space. In databases where object owners can be many, it is oftentimes a good idea to take a quick look at which schema are leading the way in terms of space usage. This is not restricted to production environments only, but can be extended to dynamic development systems as well, especially if there are many developers in a database that have their own sets of objects. Developers sometimes have a bad habit of creating objects (or copies of objects) that they leave in a database even though they are not being used.

Correction

If a user owns many objects, or copies of objects, you can reclaim space in your database by contacting the user to see if those objects can be dropped.

Tablespace Detail Tab

The Tablespace Detail tab includes the following sections:

Tablespace Object Summary

Tablespaces are the logical containers for database objects inside Oracle. The Tablespace Object Summary section provides an overview of how much space is consumed in each tablespace by object type. The table below lists the information available in this section:

Column Description

Tablespace Name

The name of the tablespace.

Status

Indicates the status of the tablespace.

Autoextend

Indicates whether one or more of the tablespace’s datafiles has the ability to automatically grow.

Total Space (MB)

The total allocated space for the tablespace.

Table (MB)

The amount of used table and table partition space, in MB.

Index (MB)

The amount of used index and index partition space, in MB.

Rollback (MB)

The amount of used rollback and undo segment space, in MB.

Cluster (MB)

The amount of used cluster space, in MB.

LOB (MB)

The amount of used large object space, in MB.


Location

Oracle Performance Analyst Statistics > Space View (DBArtisan - Oracle Performance Analyst) > Tablespace Detail Tab

Datafile Detail

Tablespaces are the logical containers for database objects inside Oracle, and datafiles are the actual physical files that make up a tablespace. The Datafile Detail section provides an overview of the size of each datafile and other demographic information. The table below lists the information available in this section:

Column Description

File Name

The name of the datafile.

Status

Indicates the status of the tablespace.

Autoextend

Indicates whether the datafile has the ability to automatically grow.

Tablespace Name

The name of the tablespace.

Size (MB)

The total allocated space for the datafile.

Max Size (MB)

The maximum size the datafile can automatically grow to.


Location

Oracle Performance Analyst Statistics > Space View (DBArtisan - Oracle Performance Analyst) > Tablespace Detail Tab

Object Space Tab

The Object Space tab displays a summary of space and extent information for all or selected objects in the database. The table below lists the information available on this tab:

Column Description

Owner

The owner of the object.

Object Name

The name of the object.

Object Type

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

Space (KB)

The size of the object in KB.

Extents

The number of extents used by the object.

Max Extents

The maximum number of extents allowed for the object.

Initial Extent (KB)

The initial extent size for the object.

Next Extent (KB)

The next extent size for the object.

Tablespace

The tablespace where the object resides.

Metrics

One thing to keep an eye on are objects that are close to their maximum extent limit. Such objects can be altered to have more or unlimited extents if need be. The use of locally-managed tablespace eliminates the threat of an object ever encountering a maximum extent limit. For more information, see the Home page under Space View (DBArtisan - Oracle Performance Analyst).

Tablespace Growth Tab

Growing tablespaces do not have to spell problems for a DBA. Of course, up front planning is the key to sizing tablespaces correctly. Unfortunately, DBAs may not have all the information they need at tablespace creation time (or they may have the wrong information), so a tablespace can approach the end of its free space from time to time. The DBA can allow a tablespace to grow automatically (AUTOEXTEND) to prevent an out-of-space condition. Enabling AUTOEXTEND for a tablespace is quite reassuring for a DBA, but it introduces a new concept for the tablespace: monitoring data file growth. You should monitor your tablespaces that have AUTOEXTEND set so you can get an idea of the growth that is occurring in your database. Monitoring them lets you perform some mini-capacity planning and helps you get a jump-start on server upgrades.

The Tablespace Growth tab displays growth statistics for the database currently being monitored. The table below lists the information available on this tab:

Column Description

Tablespace Name

The name of the tablespace.

Datafile Name

Thee name of the tablespace’s datafile.

Autoextend

Indicates whether the tablespace has one or more datafiles that have the AUTOEXTEND feature set (allowing the datafile to automatically grow).

Created Size

The starting size for the physical datafile.

Current Size

The current size of the physical datafile.

Growth

The percentage of growth experienced for the datafile (if any).

Metrics

Using AUTOEXTEND can be very important for tablespaces devoted to disk sorting. Large data warehouses often must endure large disk sort operations. Having AUTOEXTEND enabled for tablespaces used for temporary segments (sort activity) helps large sort queries to complete when they might otherwise fail due to running out of sort space.

Just having AUTOEXTEND enabled for a tablespace does not mean you cannot run out of space. Remember, you still have to contend with the physical server limitations. Make sure you (or your sysadmin) keep a careful eye on the server drives that house your Oracle database files for available free space. If the free space on a server drive nears its limit, disable AUTOEXTEND for the datafile(s) that are on that drive, and use the traditional ALTER TABLESPACE…ADD DATAFILE command to place a new datafile for the tablespace on another drive that has more free space to offer.

Fragmentation Tab

Tablespaces are made up of object segments and space extents. Extents are either allocated to object segments or are free. When a tablespace is initially populated, all objects are neatly packed together in the front of the tablespace and all remaining free space are in one free chunk at the end. Unfortunately, this is not how things continue to be in a tablespace. As objects grow (or extend) they are given new extents of space in the tablespace. If you drop objects, pockets of free space begins to appear throughout the tablespace. These pockets of space are either honeycombs or bubbles. Honeycombs are not so difficult to deal with, but bubbles are another story.

The Fragmentation tab displays two different views of tablespace fragmentation. The first grid displays fragmentation at the tablespace level and the second grid displays fragmentation at the datafile level. The table below describes the information available in the Tablespace Fragmentation grid:

Column Description

Tablespace Name

The tablespace name.

Free Space (MB)

The total amount of free space in MB for the tablespace.

Free Chunks

The total number of free chunks in the tablespace.

Largest Chunk (MB)

The largest free chunk (in MB) for the tablespace.

The table below describes the information available in the Datafile Fragmentation grid on the Fragmentation tab:

Column Description

Datafile

The name of the datafile.

Autoextend

Indicates whether the datafile can automatically grow in size.

Tablespace

The tablespace name.

Free Chunks

The number of free chunks in the datafile.

Largest Chunk

The largest free chunk (in MB) for the datafile.

Metrics

To spot and correct fragmentation in your tablespaces, you should periodically monitor the fragmentation levels of your tablespaces at a global level. Doing so helps you quickly spot tablespaces that are experiencing fragmentation issues. Seeing a tablespace with only one chunk of free space is a sign that a tablespace is not having fragmentation problems. Seeing a tablespace with a couple of free chunks may not be a big deal either, because the tablespace could be made up of more than one datafile. Each datafile has its own chunk or chunks of free space.

If you see a tablespace that has many chunks of free space, the next thing to do is drill down into it and find out if the tablespace is experiencing honeycomb or bubble fragmentation. Honeycomb fragmentation occurs when pockets of free space exist that are adjacent to one another. Bubbles are pockets of free space that are trapped between object segment extents.

You can eliminate honeycomb fragmentation with the ALTER TABLESPACE...COALESCE command. This command combines all pockets of adjacent free space into one extent. It is important to do this because when an object is newly introduced to a tablespace (or an existing object needs to extend), and a contiguous chunk of free space does not exist to accommodate an object's INITIAL or NEXT size allocation, Oracle must manually coalesce all available honeycombs to try and form a large enough free chunk. This is a performance hit. If possible, you should try to minimize performance hits.

Bubble fragmentation is a more serious matter and is normally only corrected through tablespace or database reorganization. The standard technique is to perform an export of all objects in the tablespace, drop the tablespace, and then import all the objects back into the tablespace. However, this technique may just treat the symptom and not the cause of bubble fragmentation. The real issue is to address the reuse of space extents within a tablespace so that bubble fragmentation does not occur in the first place.

Oracle8 and later offers the concept of locally-managed tablespaces, which can all but eliminate tablespace fragmentation. It totally does away with the storage parameters of MINEXTENTS, MAXEXENTS, PCTINCREASE, and NEXT. With locally managed tablespaces you either specify the initial extent size and let Oracle automatically size all other extents, or specify a uniform extent size for everything. Problems caused by fragmentation then become a thing of the past.

Correction

What can you do manually to help stave off fragmentation problems? A few suggestions include:

  • Set PCTINCREASE to zero for all tablespaces and objects to promote same-sized extents.
  • Specify equal-sized allotments for your INITIAL and NEXT object storage parameters.
  • Group objects with like growth and storage needs together in their own tablespaces.

Of all your tablespaces, you want to avoid fragmentation problems in your SYSTEM tablespace the most as this is the major hotbed tablespace for Oracle activities. The easiest way to avoid this is to not allow any user (even the default DBA ID's SYS and SYSTEM) to have access to it. There are three ways to do this:

  1. Ensure no user has a DEFAULT or TEMPORARY tablespace assignment of SYSTEM.
  2. Ensure no user has a quota set for SYSTEM.
  3. Ensure no user has been granted the UNLIMITED TABLESPACE privilege.

Extent Analysis Tab

Object fragmentation results when objects consume multiple extents of space in a tablespace rather than a single block of space. Although performance problems with respect to object fragmentation are not as severe as they were in previous versions of Oracle, some DBAs believe that response-time penalties can still be chalked up to this situation. When multiple extents exist for an object, the amount of time it takes Oracle to scan it can be longer than if the object was made up of only one extent. This typically holds true when extents are scattered on different parts of the physical server disk. In addition, a performance hit is taken each time an object must extend into another extent of space.

The Extent Analysis tab displays objects whose extent count has exceeded a user-suggested numerical limit. The table below lists the information available on this tab if any objects are found:

Column Description

Owner

The user account that owns the object.

Object Name

The name of the object.

Object Type

The type of object (TABLE, INDEX, etc.).

Next Extent

The next extent amount of space the object will consume.

Total Extents

The number of extents the object is currently consuming.

Max Extents

The MAXEXTENTS limit imposed on the object by the DBA.

Largest Free Chunk (KB)

The size of the largest chunk.

Tablespace Name

The name of the tablespace.

Metrics

When is object fragmentation a problem for an object? Opinions on this vary widely. As a general rule of thumb, if an object is exhibiting response time degradations, and no other factor can be attributed to the slowdown, examine the object to see how badly fragmented it is. If extent numbers are seen exceeding 100 or more, than you might want to think about reorganizing the object or using locally managed tablespaces. This rule of thumb does not apply to objects assigned to locally-managed tablespaces as extent fragmentation is expected and not a performance issue.

Correction

The best preventative for this problem is specifying the correct allocation of space for the object in the first place, but what can you do if you have objects with high numbers of extents? For tables and indexes you have three options:

  1. Use Oracle's export/import utility to export, drop, and import the fragmented objects back into the database with the export parameter COMPRESS=Y. This will bring the objects back into the database with one large extent. Make sure that large enough chunks of available free space exist to accept the object back in, or you may experience space allocation errors.
  2. With Oracle8, you can use the ALTER TABLE … MOVE command to reorganize a table back into one extent in a very safe and efficient manner.
  3. Use ALTER INDEX … REBUILD to reorganize indexes that have moved into multiple extents.

Another thing to keep an eye on is the next extent size of an object vs. the largest free chunk of space in the tablespace. Seeing objects with larger next extents than free space may signal a problem unless the tablespace can automatically extend to accommodate the object’s next extent.

User Space Tab

The User Space tab includes the following sections:

Top Space User Accounts

It is vital that a DBA know how the various accounts assigned to a database consume space. This is true in either production or development databases, because even in development databases, space can become an issue that causes major headaches. If development accounts are creating and cloning many objects without removing them, the result can be a lot of wasted space.

The Top Space User Accounts statistic shows how much total allocated space the top twenty accounts possess in the database.

Location

Oracle Performance Analyst Statistics > Space View (DBArtisan - Oracle Performance Analyst) > User Space Tab

Metrics

Seeing owner account with larger than expected data volumes could warrant further investigation. In addition, seeing accounts that have objects in more tablespaces than they have quota's for should also cause you to examine their account more closely to ensure the correct space privileges are in place.

User Space Detail

The User Space Detail section allows you to drill-down and see the specifics of the Top Space User accounts.

Location

Oracle Performance Analyst Statistics > Space View (DBArtisan - Oracle Performance Analyst) > User Space Tab

Retrieved from "https://docwiki.embarcadero.com/RapidSQL/171/e/index.php?title=Space_View_(Oracle_Performance_Analyst)&oldid=10481"