Panorama: Determining candidates for storage reorganization in Oracle-DB
Knowing that Oracle-DB does not automatically release storage below the high water mark it would be necessary to do it yourself. Usually you may scan your database from time to time to identify tables, indexes or lobs where reorganization of these objects can make significant amount of storage in tablespace available for other objects. The PL/SQL package DBMS_SPACE allows consideration of free space in DB blocks for an object. This information can be used to forecast the effect of object reorganization. Unfortunately DBMS_SPACE.Space_Usage takes longer time for evaluation of larger objects, so it is quite expensive to scan a whole schema or system using DBMS_SPACE. Following I'll show how the performance analysis tool Panorama handles determining candidates for reorganization. To determine candidates for deeper inspection Panorama uses a simple calculation based on avg. row-length, Pct_Free and Ini_Trans to list suspected objects that are worth to reorganize. Sorting th