| Name: |
Index fragmentation |
| Platform: |
Oracle |
| Category: |
Maintenance |
| Premium package: |
Maintenance package |
| Description: |
Checks the index fragmentation in all database schemas. If DELETED LEAF ROWS is high, you will get low block density. |
| Long description: |
Checks the index fragmentation in all database schemas. |
| Version: |
1.3 |
| Default schedule: |
40 4 4 * |
| Requires engine install: |
Yes |
| Compatibility tag: |
[CDATA[.[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 objects (indexes and tables) for which the statistics will be checked and collected into the history table. |
| size threshold |
50 |
The minimum size (in MB) of the indexes to be analyzed for fragmentation. Only type NORMAL indexes are analyzed (not BITMAP, LOB, etc.) |
| with clause |
VALIDATE STRUCTURE |
Clause which should be specifies when running ANALYZE INDEX command. Valid inputs are “VALIDATE STRUCTURE” or “COMPUTE STATISTICS”. |
| 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 leaf blocks density in the B-Tree (in percent) in the index get below the threshold value, the job will return a warning. |
| alarm threshold |
30 |
Alarm threshold value in percent. If the average leaf blocks density in the B-Tree (in percent) in the index 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. |
| max size threshold |
5000 |
The maximum size (in MB) of the objects (indexes) to be analyzed for fragmentation. |
| 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. |
← Index status check (8i) / Table fragmentation (10g) →
Post your comment on this topic.