Index status check
Job details
| Name: |
Index status check |
| Platform: |
Oracle |
| Category: |
Maintenance |
| Description: |
Detecting indexes in status UNUSABLE |
| Long description: |
Detecting indexes in status UNUSABLE |
| Version: |
1.5 |
| Default schedule: |
5,15,25,35,45,55 * * * |
| Requires engine install: |
Yes |
| Compatibility tag: |
.[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & _priv_read_all_indexes = ‘1′ & maj_version = ‘8′] |
Parameters
| Name |
Default value |
Description |
| ignore index |
|
Indexes we don’t give a warning for. Values for this parameter must be separated with commas (,). |
Job Summary
- Purpose: The purpose of this job is to monitor and identify any Oracle database indexes that are in an “UNUSABLE“ status.
- Why: This job is important because indexes in an “UNUSABLE“ status can lead to degraded query performance and potentially impact the overall functionality of applications relying on the database. Detecting and resolving such issues promptly helps in maintaining the efficiency and reliability of database operations.
- Manual checking: You can check this manually in the database by issuing the following SQL command:
SELECT owner, index_name, index_type, status FROM all_indexes WHERE status = 'UNUSABLE';
Job Configuration and Details
- Group:
com.dbwatch.job
- Artifact ID:
oracle_index_status_check_8i
- Job Name: Index status check
- Description: Detecting indexes in status “UNUSABLE“
- Version: 1.5
- Company: dbwatch.com
- Default Schedule: Executes every 10 minutes (5, 15, 25, 35, 45, 55 of each hour)
Dependencies
- This job depends on successful execution of an SQL procedure that checks index status, failing which will not clean up past failures.
Report Generation
- Report Title: Index Status
- Description: Report for index_status_check check
- Report Schedule: Generates on the hour every hour
- Presentation Content: Summary of indexes not in “VALID“ status
Database Interaction
- SQL Implementation: A procedure named “index_status_check” is created or replaced, which audits all indexes and logs those in “UNUSABLE“ status unless explicitly ignored.
- Exception Handling: Updates the check values and logs any exceptions into DBW_ERRORS with a timestamp and error details.
Index Status Detection Table
| Owner |
Index Name |
Index Type |
Status |
| Owner Data |
Index Name Data |
Index Type Data |
UNUSABLE |
- Note: This table lists all indexes not in status VALID. If empty, no indexes are in status UNUSABLE.
Upgrade from Version 1.4
- The same SQL procedure is reiterated for installation during upgrades, ensuring continuity and up-to-date functionality.
Miscellaneous
- Compatibility: The task is compatible with Oracle instances (as specified by a compatibility query).
- Category: Maintenance
- Installation Settings: The job necessitates forceful installation irrespective of previous versions or existing installations.