Time Critical DB calls on the AWA WP logs
search cancel

Time Critical DB calls on the AWA WP logs

book

Article ID: 94188

calendar_today

Updated On:

Products

CA Automic Workload Automation - Automation Engine CA Automic One Automation

Issue/Introduction

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.

Environment

Component:Automation Engine 

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.