Database optimization job is scheduled everyday for CA UIM database by Our DB team.Earlier the job used to take 3 hours to complete.But recently it takes more than a day to complete and result of which data engine queue is getting increased. Our DB team is suspecting that there is a change in the database structure and No.of indexes got increased.But we have not done any change in the application level.
This is the observation from DB end.
Total Indexes in the database is 60,913.
Index fragmentation above 90% is 1009. These indexes highly fragmented.
Index fragmentation between 50% to 90% is 52,949.
There are 26 heap tables with huge fragmentation.
So they have come up with a solution and they want the below details
Please check with the OEM and get us the indexes that are used frequently for daily operations and for reporting purpose, so that we can create a customized job to perform the maintenance activity. =============================================
Any idea which are the indexes(i mean the tables)are used frequently.