|Oracle Enterprise Manager Database Tuning with the Oracle Tuning Pack
Part Number A86647-01
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 three reorganization options:
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.
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.
Over time, your database's space usage can become fragmented and inefficient. The Reorg Wizard can be used for efficient space management.
Specifically, you may want to use the Reorg Wizard when one or more of the following situations occur:
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. In some cases, however, you may want to focus on correcting problematic rows only. The Reorg Wizard allows you to reorganize an entire tablespace or choose selected rows to repair. In either case, 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.
Any of these factors may impact the overall performance of the database.
You can start the Reorg Wizard in context of an object that you want to reorganize or in standalone mode. You can start the Reorg wizard in context to an object by selecting an entire tablespace or by selecting a specific database object. You can also start the Reorg Wizard in standalone mode from the Oracle Enterprise Manager console Tools=>Tuning Pack menu or from the Oracle Enterprise Manager console's Tuning Pack toolbar.
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:
The reorganization option allows you to chose the type of reorganization to perform. There are three reorganization options: reorganize specific schema objects, reorganize an entire tablespace, or repair migrated rows. (Note that this step will only appear when you launch the Reorg Wizard without first selecting an object to reorganize.)
A list of available objects is displayed. The wizard allows you to select the objects you wish to reorganize. (Note that this step will only appear when you launch the Reorg Wizard without first selecting an object to reorganize.)
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.
One way to enhance availability and manageability is to allow users full access to the database during a data reorganization operation. Depending on the server version, Oracle supports online reorganizations for various types of objects. For example, starting with Oracle 8i, B*-tree indexes can be created and rebuilt online. Oracle 9i improves on this with new features that support online creation and rebuilding of even more types of indexes as well as support for online reorganization for some types of tables.
Even though online reorganizations provide higher availability, they are slower. If you have a maintenance window that allows for a scheduled "after hours" reorganization, then a faster, offline reorganization would be better.
The Reorg Wizard allows you to indicate whether the generated script should favor availability or speed. If availability is chosen, the generated script will take full advantage of online capabilities when supported by the server. If speed is chosen, the generated script will always use an offline reorganization approach.
To take advantage of the Oracle 9i online table reorganization, you must have the following privileges: SELECT ANY TABLE, ALTER ANY TABLE, LOCK ANY TABLE, DROP ANY TABLE, CREATE ANY TABLE and the ability to execute the dbms_redefinition package either by explicitly granting EXECUTE privilege for this package or by granting the EXECUTE_CATALOG_ROLE.
For more information on granting user privileges, see the Oracle Enterprise Manager Administrator's Guide.
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.
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 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 Summary contains a summary of the database commands that will be used to perform the reorganization.
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.
Should a job fail; for example, due to insufficient free space in a tablespace to carry out the reorganization, there are two ways of dealing with the failure: