Time Critical DB calls on the AWA WP logs

Document ID : KB000094188
Last Modified Date : 04/01/2019
Show Technical Document Details
Issue:
In some instances, when you notice strange WP spikes and quite a few 'time critical DB' calls, which some are normal, but there are a lot more than normal, then bad indexes could be the issue. You cannot generalize to say that index rebuilding for performance is rare, or even medium rare, it depends on many factors, most importantly the characteristics of the application.
  • In scientific applications (clinical, laboratory) where large datasets are added and removed, the need to rebuild indexes is "common".
  • Conversely, in system that never update or delete rows, index rebuilding rarely improves performance.
  • In systems that do batch DML jobs, index rebuilding "often" improves SQL performance.
Resolution:
While it may be rare to rebuild an Oracle index for performance reasons, there are some databases that will get a measurable performance boost from rebuilding indexes.  These workloads have these characteristics:
  • High index fragmentation:  The SQL workload has lots of table DML causing lots of deleted leaf blocks.
  • High index scan access plans:  The SQL workload is rich with index scans (index fast-full scans and index range scans)
The rules for identification of candidates for index coalescing/rebuilding depend on your specific index state.