In the Reorg Manager, statistics collection options are specified on the Scripting Options page (Analysis section). These options allow you to automatically collect or restore statistics when a reorganization script is run.
See Collect Statistics for an overview of statistics collection in Space Manager.
The Reorg Manager provides the following options for statistics collection:
Restore original statistics in data dictionary for the CBO—This option lets you preserve existing statistics in the Oracle data dictionary instead of collecting statistics. It is designed for use when data-dictionary statistics are pre-set for Oracle’s cost-based optimizer.
Note: Restore functionality requires the DBMS_STATS package.
For information on how to specify statistics collection options in the Reorg Manager, see Select Scripting Options.
When you restore statistics during statistics collection, data dictionary statistics are copied before objects are reorganized. The statistics are restored to the data dictionary afterward. The backup and restore are performed using procedures from the DBMS_STATS package. Before a reorganization begins, the export_table_stats procedure is used to export existing statistics from the data dictionary to a backup table. This is called QUEST_SPC_REORGANIZATION_STATTAB. After statistics are collected, statistics in the backup table are imported into the data dictionary with import_table_stats. They are then deleted from the backup table with delete_table_stats.
Important: The DBMS_STATS procedures used by the restore option processes statistics needed by Oracle’s cost-based optimizer. They do not process statistics such as number of chained rows, average free space in data blocks, number of empty blocks, and average space in freelist blocks. As a result, these statistics are not preserved by the restore option. If they exist in the data dictionary before collection is performed with this option, they will not exist there afterward.
After you specify storage options for individual objects in the Customize Object Allocations page, the Reorg Manager performs a space audit and displays the results in the Space Usage Summary tab. Reorg Manager checks for the following:
LiveReorg tablespace—If you are performing a live reorganization, Reorg Manager checks to see if the LiveReorg tablespace has enough space for LiveReorg objects. The LiveReorg tablespace and LiveReorg objects are used by QSA. The LiveReorg tablespace is specified with the agent’s LW_TABLESPACE parameter. Space checks by Reorg Manager consider the space needed for creating LiveReorg objects. They do not consider additional space that might be needed for object growth. If a live reorganization fails because a LiveReorg object cannot grow, a collection trigger error is displayed in the script log.
Note: Another space audit is performed when a script is run to make sure sufficient space is still available.
The Space Usage Summary tab displays information for segments and tablespaces involved in a reorganization. This includes original tablespaces, tablespaces being used on an interim basis, tablespaces to which objects are being relocated, and the LiveReorg tablespace.
For each segment, the information displayed includes current and ending space allocation values.
For each tablespace, the information displayed includes starting free space and ending free space.
Space Manager may alert you to space availability with various messages. If a message indicates that a target tablespace or the LiveReorg tablespace does not have enough free space, open Tablespace Properties for the tablespace and add datafiles or increase datafile size. Leave Reorg Manager open while you do this. You can also return to previous windows and select different target tablespaces or adjust storage values for the objects being reorganized.
If Space Manager displays a message after estimating space usage (in the Reorg Manager or other areas of the application), review the message description and suggested guidelines below.
Message |
Description |
---|---|
Insufficient Free Space |
When one or more target tablespaces do not have enough free space, the “insufficient free space” message is displayed at the bottom of the Review Space Usage window. Values for target tablespaces without enough free space are displayed in red. A negative value is displayed in the Lowest Usable Freespace column. |
Insufficient Contiguous Free Space |
When one or more target tablespaces do not have enough contiguous free space for more objects, a message with a list of these tablespaces and objects displays when you proceed to the Review Space Usage window.
To provide the contiguous space needed in a target tablespace or the LiveReorg tablespace, use one of the methods under “Space Usage Messages”. If your actions do not result in sufficient contiguous free space, one of the following messages is displayed in the Review Space Usage window:
|
Current Allocation Notes |
The Review Space Usage window sometimes displays a note indicating that some objects were sized based on current allocation instead of current usage. Current allocation and current usage are two sizing options for the Minimize Number of Extents global allocation strategy. You can select either one. However, the current usage option requires current statistics in the Space Manager repository. If statistics are not current for an object, current allocation is used instead of current usage.
The note gives one of the following reasons for use of current allocation:
|
Underestimates and Overestimates |
Space checks can sometimes underestimate or overestimate the space required by an object. This happens when statistics in the Space Manager repository are not current. The results of incorrect estimates are as follows:
Even when space checks are accurate, “Freespace not available” errors can occur during script execution. This happens when extents are allocated in a different order than anticipated. For example, a space check might anticipate that larger extents will be allocated to larger objects first, but Oracle might allocate these extents to smaller objects first. As a result, they are not available for larger objects. |
The wizard checks for indexes that can be used as posting indexes. A posting index is needed when live transactions are copied to a reorganized copy table. The index guarantees uniqueness of rows in the copy and speeds up the posting process. Each table in a script has its own posting index.
In most cases, Space Manager automatically chooses a posting index. Normally, the most selective index available for a table is selected. However, in some cases, Space Manager prompts you to make choices regarding the posting index. This happens when the only candidate for a table’s posting index has a PRIMARY KEY constraint or UNIQUE KEY constraint in the ENABLE NOVALIDATE state.
The message prompts you to validate constraints so that they can be used for posting. If you do not want to do this, you can exclude a table from a reorganization or select one of the two posting methods that are designed for tables that do not have selective indexes. These are the “all columns” posting method and the ROWID posting index. See Posting Indexes for more information.
Important: If you choose to validate a UNIQUE KEY constraint, posting may be slow if the leading column of the constraint is nullable.
To respond to checks for a posting index
From the Unsupported Objects message with two options, select one option:
From the Unsupported Objects message with four options, select one option:
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. 이용 약관 개인정보 보호정책 Cookie Preference Center