Fragmentation can be defined many different ways, but for the sake of this alert, it is based on the total size allocated to a table vs the space the actual data consumes. A highly fragmented table can take up extra space that it doesn't need, and when table scans are required to access the data, performance can suffer.
This query accesses all tables but excludes SYSTEM and SYS tables. It only returns tables that are larger than 100 MB, so adjust the threshold as needed. It returns a data set that has 2 columns:
- Column 1 - includes the table owner, name, total size and actual size
- Column 2 - the percent the actual size is related to total size. Higher numbers indicates wasted space.
To create this alert, create a Custom SQL Alert of type Multiple Numeric and make it look similar to this example:
