This section describes the factors that are used by Space Manager to calculate reorg need.
The Wasted Blocks factor indicates what percentage of an object’s data blocks are wasted. Wasted blocks are the number used beyond the number currently needed for data. These blocks once contained data but are now empty because data has been deleted. The number of data blocks used is the high water mark for an object. This is the point up to which Oracle reads during full object scans. The high water mark rises when data is inserted. It does not go down when data is deleted. (Wasted Blocks is a factor used for all objects.)
Why it contributes to reorg need:
Wasted data blocks degrade database performance because they require Oracle to read more blocks than necessary during full object scans. Reorganizing objects with wasted data blocks resets (drops) the high water mark to the number of data blocks actually needed for data. Oracle then needs to scan fewer blocks for those objects.
Values used:
The following fields display the values used in calculating the Wasted Blocks factor:
The Chained Rows factor indicates what percentage of a table’s rows are chained. Chained rows are rows that have been migrated from their original data block due to data growth. They can also be rows that span multiple data blocks because they are too large to fit into a single data block. The Chained Rows factor takes both types of chaining into account. However, only migrated rows are repaired by Space Manager. (Chained Rows is a factor used for tables only.)
Why it contributes to reorg need:
Excessive chaining degrades performance during queries. This is because chaining forces Oracle to read multiple data blocks to retrieve a single row. First, Oracle retrieves the row’s original data block. Then, it uses the pointer in the original block to retrieve the row’s new data block. Reorganizing (or repairing) tables with migrated rows reduces the number of data blocks used by these rows. This in turn reduces the data blocks Oracle needs to scan.
Values used:
The following fields display the values used in calculating the Chained Rows factor:
Reorg Need After Reorganization
Reorg Need calculations include two factors, wasted space and chained rows. Wasted Space in a segment is calculated using DBMS_SPACE.SPACE_USAGE and is calculated for segments in a locally managed tablespace only.
When DBMS_STATS or FND_STATS is used to collect statistics, you can compensate for missing chained-row statistics by decreasing the weight of the Chained Rows factor in reorg need calculations. The default weight is 30%.
To decrease the weight for the Chained Rows factor, update the value for Chained Row Weight in Tools | Options | ReorgNeed. Also, increase the weight for the Wasted Space Weight so that the total of all weights is 100.
Normally, values for reorg need and need ratio drop to zero after an object is reorganized and its statistics are updated.
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center
