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.3 |
| Default schedule: | 40 4 5 * |
| Requires engine install: | Yes |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & nine_and_above=‘1′] |
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.unused_space procedure on each table. |
| density threshold | 80 | Tables with higher density then this parameter value are not checked. |
Job Summary
- Purpose: The purpose of this dbWatch Control Center job is to monitor and report on table fragmentation within all database schemas. It checks the density of the blocks in the tables, determining how compact the data storage is, which can affect the database performance significantly.
- Why:
- This job is important as fragmented tables can lead to inefficient data retrieval, resulting in slower database responses and potential performance issues.
SELECT owner, table_name, blocks, empty_blocks, avg_space, chain_cnt, pct_free FROM DBA_TABLES WHERE owner NOT IN ('SYS', 'SYSTEM');Dependencies
- The job relies on several procedural and tabular components which ensure the monitoring and logging of fragmentation across tables:
- dbw_table_fragm_check_proc – Main stored procedure handling the check, leveraging information from the DBA_TABLES dictionary view.
Operational Details
The operational execution is handled by the stored procedure which contains complex logic to assess each table for fragmentation based on thresholds such as size, density, and population of the DBA_TABLES. It also handles the continuation of the monitoring process across multiple runs, conditional checking for empty blocks, and the logging of statistical data in associated tables and historical records.updates the implementation to adapt to potential upgrades.
Reporting
The job produces various reports including:
- Fragmentation statistics providing details on schema and table counts, object sizes, start and end times of the job’s execution, and any errors encountered.
- Tabular and graphical representations summarizing the highest fragmented tables facilitating easy visualization and further analysis.
Implementation Notes
- The job implementation includes creating necessary tables and procedures upon installation with features including error handling, transaction control to maintain data integrity and procedural logging for troubleshooting and audit purposes.
- The upgrade code segment ensures that procedures and schemas are up to date with changes introduced in new versions of this job script.