About the Repository
The Space Manager Repository collects and stores statistics. The statistics stored in the Repository are used by Space Manager to calculate reorg need and wasted space. Reorg need and wasted space are displayed in the Explorer, used in generated reports, and used by the Reorganization Wizards.
In addition, statistics from the Repository are displayed in the Explorer and in Object Properties.
There is one repository for each database where Space Manager is installed. An unlimited number of collections can be accumulated in the repository for each object. When multiple collections are made in one day, only the last collection is kept.
Repository Update Job
Statistics are collected by the Repository Update job that is scheduled to run daily, by default. You can modify the frequency at which this job runs, if necessary. See Change Repository Update Schedule for more information.
This job collects statistics from the Oracle data dictionary and from running Oracle procedures (like those in the DBMS_SPACE package). These statistics are then used by Space Manager to display the list of objects in the Explorer, to estimate table sizes, to generate trend reports, to calculate reorg need and wasted space, and to provide a variety of storage and space use information throughout the Space Manager interface.
Accumulated statistics provide the historic information needed to track growth rates over time and project future space needs. If statistics have never been collected for an object, zero growth is assumed for that object.
Considerations
- If the statistics copied from the data dictionary are pre-set statistics for the cost-based optimizer, be aware that they may affect calculations by Space Manager modules. Space Manager’s reorganization modules and reports provide the best results when current storage statistics are available in the repository.
- If an object does not have statistics in the Oracle data dictionary, statistics are not copied to the Space Manager repository for that object.
Repository-Related Tasks
See the following topics for more information about Repository-related tasks:
- Change Repository Update Schedule—Learn how to change the frequency at which the Repository is updated.
- Explorer Object Lists—You can select the method Space Manager uses to gather and display the list of objects in the Explorer.
- Repository Schedule Options—You can specify the number of segments updated during each Repository Job run.
What Is Supported for Statistics Collection
Supported
- Non-partitioned tables and indexes
- Partitioned tables and indexes
- Table partitions and subpartitions
- Index partitions and subpartitions
- Clusters, clustered tables, cluster indexes, indexes on clustered tables
- Most user-defined datatypes
Not Supported
- IOT overflows
- IOT overflow partitions
- LOBs
- LOB partitions and subpartitions
- LOB indexes
- LOB index partitions and subpartitions
- Nested tables
- Object tables
- Rollback segments
- Cache segments
- Temporary segments
- Unknown segment types
Profile DML Activity
Smart Start for DML Profiler
You can use the DML Profiler to profile SQL going against a table you want to reorganize so that you can plan when to start and switch a table reorg. This Smart Start provides an overview of all the major steps involved in profiling DML activity.
To create a DML Profile
- In the Explorer, right-click a table and select DML Profile | Create/Modify Collection.
-
The Create DML Activity Collection dialog opens to the Parameters tab.
If you want to add objects, select the Select Objects tab. Select additional objects to include and click Add to List.
-
In the Parameters tab, specify collection parameters. Review the following for additional information:
Duration Enter then number of days, hours, minutes, and seconds that you want to collect activity. Cycle Length (Advanced) Enter the number of seconds that elapse between two queries on the collection table.
Cycles per Collection (Advanced) Enter the number of cycles between two updates on the collection statistics table. Long Running Transaction Cycles (Advanced) Enter the number of cycles that can elapse before a transaction is considered long running. Schedule Select whether to run activity collection instantly, save the script to run at a later time, or schedule the collection script to run on a specific date and time. - Click Save.
To stop a currently-running DML Profile script
- In the Job Monitor window, right-click the script and select Cancel.
To modify the script's collection duration
- In the Explorer tree-list, select the database that contains the object.
- Expand the DML Collection Profile node for that database.
- Select the owner of the object. The DML Profile Collections window opens and lists all the profiles for that owner.
- Right-click the profile and select Modify Collection.
To reschedule a script
- In the Job Monitor window, right-click the script and select Schedule or click
.
To unschedule a scheduled script
- In the Job Monitor window, right-click the scheduled script and select Unschedule or click
.
To generate a DML Profile report
- In the Explorer window, right-click an object and select DML Profile | DML Profile Report.
Tips:
- You can modify or delete a collection if the collection script is not running.
- An object can have only one active DML collection at a time. An active collection is a running or scheduled collection. An object can have multiple completed collections.