22
Introduction to the Reorg Wizard

The Reorg Wizard can help you maintain a well performing database by correcting space usage problems. Over time, database performance can be affected by problems such as row chaining and/or migration and index stagnation. The Reorg Wizard can eliminate space problems by reorganizing database space usage. The Reorg Wizard also allows you to change an object's storage settings and location.

The Reorg Wizard gives you two reorganization options:

• Reorganize specific schema objects

• Reorganize an entire tablespace

The wizard performs reorganizations within the Oracle database and does not use external data handling operations. Reorganizations can be performed within the target tablespace or by temporarily using space in another tablespace.

The Reorg Wizard evaluates the reorganization operations and generates an Impact Report and reorganization script, which can be reviewed prior to execution. Reorganizations can be performed immediately or can be scheduled at a later time, perhaps during off-peak hours. In either case, the reorganization will be run by an Enterprise Manager job using the Oracle Agent.

Note: The Enterprise Manager Console, Oracle Management Server and Agent are required to perform reorganizations with the Reorg Wizard. The Agent must be running on the same node as the database being reorganized.

Certain database reorganization operations can be time-consuming and will impact the availability of objects being reorganized. Use database object reorganization selectively. For example, tablespace reorganizations can often be avoided by reorganizing only selected tables and indexes.

When to use the Reorg Wizard

Over time, your database's space usage can become fragmented and inefficient. The Reorg Wizard can be used for efficient space management.

You can use the Tablespace Map (also available with the Oracle Tuning Pack) to perform a Segment Analysis on the object you are interested in reorganizing prior to launching the Reorg Wizard. A Segment Analysis report is generated showing you exactly where storage problems exist.

Specifically, you may want to use the Reorg Wizard when one or more of the following situations occur:

• Excessive row migration--If an UPDATE statement increases the amount of data in a row so that the row no longer fits in its data blocks, the Oracle Server tries to find another block with enough free space to hold the entire row. If such a block is available, the Oracle Server migrates the data for the entire row to the new data. A forwarding address is left at the original row's block. This is called migrating a row; the rowid of a migrated row does not change.

  The effect of row fragmentation on performance is that each time a migrated row is needed for a query, an additional I/O call is required for the row piece that is not stored in the original block.

  It is possible to minimize unnecessary row fragmentation for a table by correctly setting the table's PCTFREE value. Being too pessimistic on PCTFREE can waste some space but will more or less guarantee no row migration while being too optimistic can result in row migration and reduced performance. Instead of trying to save every byte in a block, Oracle recommends erring on the side of setting PCTFREE high rather than low. In most cases this will be a difference of only 5%. For tables that do not have varying width columns and/or do not get updated, this parameter can be safely ignored or set to a low value.

  Reorganizing the table or table partition will fix migrated rows. Furthermore when reorganizing an object which suffers from excessive row migration, you may wish to increase the current PCTFREE setting in order to reduce the potential for rows which would have to be migrated in the future.

• Disorganized indexes--As indexed values are inserted or deleted, the amount of unusable space within the index increases. Because the index contains a mixture of data and empty areas, scans of the index are less efficient. Rebuilding the index will eliminate unused index space.

• Inefficient extent sizes-- In order to avoid unnecessary I/O for objects that are fully scanned, a segment's extent size should be a multiple of the multi-block I/O size which is defined as DB_FILE_MULTIBLOCK_READ_COUNT * DB_BLOCK_SIZE. If unnecessary I/O is being performed, consider rebuilding the object specifying an INITIAL and NEXT value that is a multiple of the multi-block I/O size.

• Overextended object--The MAXEXTENTS storage parameter specifies the maximum number of extents that can be allocated to the segment. Once a segment has filled the maximum number of extents, any row insertion will fail. If an object is approaching its maximum extents setting, consider increasing the value of the segment's MAXEXTENTS storage parameter, or rebuilding the segment with a larger extent size. Ensure that the extents within the segment are the same size by specifying storage parameters where the INITIAL and NEXT values are the same and PCTINCREASE=0.

Any of these factors may impact the overall performance of the database.

Accessing the Reorg Wizard

The Reorg Wizard can be started in standalone mode from the Enterprise Manager Console tools menu -> Tuning Pack menu item, or from the Tuning Pack tool bar. It can also be started in standalone mode from DBA Studio, Storage Manager, Schema Manager and from Tablespace Map's Tools menu.

The Reorg Wizard can be started in context of the object that you want to reorganize from several Enterprise Manager launch points such as DBA Studio, Storage Manager, Schema Manager, and Tablespace Map.

Note: When the Reorg Wizard is launched in context to a database (from any of the DBA studio applications), the database and node preferred credentials must be set.

Reorg Wizard Interface

The Reorg Wizard interface varies depending on how the wizard is launched. When launched in context to a selected database object, the wizard displays screens that are appropriate for that object. You can get specific information for any of the pages from the Reorg Wizard's online help. See Figure 22-1 for a sample Reorg Wizard page.

Figure 22-1 Object Selection Page of the Reorg Wizard

Reorganization Process

The Reorg Wizard leads you through the process of reorganizing objects within a database. As part of the process you will be able to select the objects targeted for reorganization, optionally change the storage setting and location of these selected objects, and then perform the reorganization immediately or schedule it to be performed at later time.

If you need assistance while using the wizard, select the Help button from any of the pages to detailed information.

The Reorg Wizard guides you through the following steps:

• Selecting a Reorganization Option

  The reorganization option allows you to chose the type of reorganization to perform. There are two reorganization options: reorganize specific schema objects or reorganize an entire tablespace.

• Selecting Objects

  A list of available objects is displayed. The wizard allows you to select the objects you wish to reorganize.

• Setting object attributes

  When reorganizing schema objects, it is possible to change the object location and sizing information for the individual selected objects not only to correct existing space usage problems, but also to prevent future problems.

• Identifying Sufficient Temporary Free Space

  Generally reorganizations are performed by moving data to temporary objects that the wizard creates. When reorganizing specific schema objects, you can choose to have these temporary objects created in their current tablespace or you can use an alternate scratch tablespace. Using the current tablespace is a faster method because the objects are only moved once. However, you may want to create and use a scratch tablespace to avoid the space impact of reorganizing within the current tablespace.

  When reorganizing an entire tablespace, the use of a scratch tablespace is required.

  In addition to the current tablespace, the wizard displays a list of permanent, online, non-SYSTEM tablespaces for defining where temporary space should be acquired.

• Viewing the impact summary report and job script

  Using the selected objects and any changes to the individual objects, the wizard generates an Impact Report and reorganization script. The wizard allows you to review the Impact Report and a Job Script Summary prior to the execution of the reorganization.

  The Impact Report provides a list of errors or other problems discovered for the reorganization job; for example, the report would include resource warnings should there be insufficient space for the reorganization. This allows you to make necessary changes to help ensure the reorganization job will run successfully.

  The Job Script Summary contains a summary of the database commands that will be used to perform the reorganization.

• Scheduling the reorganization job

  The wizard allows you to specify when you want to run the reorganization job. Reorganizations can be performed immediately or can be scheduled at a later time perhaps during off-peak hours.

  After submitting a reorganization job, you can check its status by using the Enterprise Manager Console - Jobs window. Jobs will be listed in the Active or History tab views depending on their status. Double-click on the job listing to obtain information about the job properties and operations.

  Note: If you are using a Microsoft NT workstation, be sure that you NT user account has the right to 'Log on as a batch job.' Use the Microsoft NT User Manager to ensure this right is enabled.

Should a job fail; for example, if there is insufficient free space in a tablespace to carry out the reorganization, there are two ways of dealing with the failure:

1. Correct the error at the destination database (for example, by increasing the size of a tablespace) and proceed with the reorganization. From the Enterprise Manager Console create a new job. To do this, right-click on the failed job and select the Create Like... menu item. Then from the Create Job dialog, click Submit. The job will be resubmitted.

2. Undo the changes from the reorganization job. From the Enterprise Manager Console create a new job. To do this, right-click on the failed job and select the Create Like... menu item. From the Create Job dialog select the Parameters tab. Change the parameters from 'agent false proceed_and_cleanup' to 'agent false revert' and submit the modified job.

---