Table fragmentation

Job details

Name:	Table fragmentation
Platform:	Oracle
Category:	Maintenance
Premium package:	Maintenance package
Description:	Checks the Table fragmentation in all database schemas.
Long description:	Checks the table fragmentation in all database schemas.
Version:	1.2
Default schedule:	40 4 5 *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & maj_version=‘10′]

Parameters

Name	Default value	Description
history threshold	90	The number of days the statistics (size, rows count, fragmentation, etc.) will be kept in the history table.
max objects	10	The number of tables for which the statistics will be collected into the history table.
size threshold	50	The minimum size (in MB) of the table to be analyzed for fragmentation.
max elapsed time	60	The maximum time (in minutes) the procedure is allowed to run.
warning threshold	50	Warning threshold value in percent. If the average blocks density (in percent) in the table get below the threshold value, the job will return a warning.
alarm threshold	30	Alarm threshold value in percent. If the average blocks density (in percent) in the table get below the threshold value, the job will return an alarm.
ignore schemas	SYS, SYSTEM	List of schemas (separated by comma) which will be ignored. You can use % (percent sign) to represent wild card characters.
enable warnings and alarms	NO	If set to “NO” (default), the job will only collect statistics without returning status warning or alarm. Value “YES“ will activate alerting.
continue on next run	YES	If set to “YES“ (default) the procedure will continue where it left off.
max objects per graph	5	The maximum number of objects visualized in the report graphs.
max objects per table	30	The maximum number of objects visualized in the report table.
check empty blocks	NO	Checks the number of empty (never used) data blocks in the table (column EMPTY_BLOCKS in DBA_TABLES). This column is populated only if you collect statistics on the table using the DBMS_STATS package. If set to “YES“ the procedure will execute DBMS_STATS.proc_name procedure on a table with missing statistics.
density threshold	80	Tables with higher density then this parameter value are not checked.

Job Summary

Purpose: The purpose of this job is to monitor and report on table fragmentation across all database schemas in an Oracle 10g environment.
Why: This job is critical to ensure that database tables do not suffer from excessive fragmentation, which can lead to degraded performance due to increased I/O operations. By maintaining a certain threshold for blocks density, the system can alert administrators to take necessary actions, possibly leading to table reorganization or rebuild, to maintain optimal database performance.
Manual checking: You can manually check table fragmentation in the database by issuing these SQL commands:

SELECT owner, table_name, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len, num_rows FROM dba_tables WHERE table_name = 'your_table_name';

Job Dependencies

The task has dependencies on several elements necessary for its operation:

Main procedure for monitoring table fragmentation, named “dbw_table_fragm_check_proc”.
Auxiliary tables such as “dbw_table_fragm_tab”, “dbw_table_fragm_tab_histr”, “dbw_table_fragm_exec_histr”, and “dbw_table_fragm_ignore_sch” for managing execution history, statistics, and configuration exceptions.
Dependence on its own iterative execution to continue monitoring from where it last left off, as specified by the parameter settings.

Operation Details

This job performs several functions as described below:

Collects data on table sizes, row counts, and fragmentation details from the “DBA_TABLES“ dictionary view.
Applies filters based on size thresholds to focus on significant tables that could affect performance.
Maintains historical data on table fragmentation to analyze trends over time.
Option to ignore certain schemas from the check to focus on relevant areas.
Reports generated can trigger warnings or alarms if the fragmentation crosses defined thresholds, providing immediate feedback on potential performance issues.
Provides execution history records to track the performance and efficiency of the job over time.

Automatic Execution Setup

This task is scheduled to run periodically with the default schedule set to run at a specific hour and minute, ensuring regular checks and updates on the state of table fragmentation across the database.

Reporting

Upon execution, the job provides detailed reports which include:

Schema Count	Table Count	Min Size Threshold (MB)	Density Threshold	Min Density	Schemas Checked	Tables Checked	Fragmentation Table Count

Objects Checked Size (MB)	Start	End	Elapsed Time (sec.)	Elapsed Time (min.)	Last Schema	Last Table	Errors

In addition, visual graphs and tabulated data present the fragmentation statistics and history in a way that’s easy to understand and analyze for database administrators.