Precise Ranks Tables and Indices By Activity | right indexes & right defragmentation schedule

Precise ranks the busiest objects (tables and indices) for Oracle, SQL Server, and DB2.  The right indexes and the right de-fragmentation schedule provide a healthy environment so that SQL statements can execute as fast a possible.  Users and applications experience performance as time.

This Precise screen ranks the busiest objects.  Here is how we do it.  Precise captures the SQL statements, their execution times, and their execution plans.  The plan connects the SQL statements to the tables and indexes accessed.  Precise adds up the total execution time (In Oracle, In SQL Server, In DB2) of all of the SQL statements that access the objects and then present the tables/indexes in descending order.  In the screen below, the Stock table is the the busiest object.

Defragment the busiest objects to save the most processing time.  This will have a nice ripple effect benefiting every thing running in Oracle, the server, or the VMware ESX host.  Use Precise’s Object Heat Matrix report to determine how often to rebuild the indexes.

These SQL statements access the Customer table.  A business application tends to access the tables in a similar way.  Reports may group activity by time period: day, week, month, quarter, year.  There will be similar filtering: division, department.  There will be similar sorting: division, department, time.  This implies that there is a set of indexes that address the way that these SQL statements access the data in the table.

Precise recommends index changes and models the benefit.  It shows the processing time savings.  The goal is the minimum number of highly selective, low-cluster factor, de-fragmented indexes that address the needs of the application.  Every index is maintained during inserts, deletes, and updates; thus, an additional index imposes additional processing and consumes space.  The time savings shown below is significant especially when you consider that the CPU runs at millions of instructions per second.  Those cycles will be available to other processes.

Please consult Benefits of Defragmentation for more information on why defragmentation is so effective.

Please schedule a demo if you’d like to discuss further Precise for Oracle.