September 18, 2012

Find large Maximo tables and indexes on Oracle

When you have to assess the system performance of a Maximo server it may be useful to have a quickly see what are the larger database tables and indexes. This can quickly spot some performance issues in your system.
Large tables generates heavy database I/O workloads when are nor accesses through an index. Large indexes are also inefficient. Creating indexes with too many columns is a very common mistake. Dropping complex indexes and creating two or three smaller ones can improve query times in many cases.

Here is a useful SQL query (for Oracle) to list the larger database tables and indexes together with their size in MBytes.

SELECT segment_name, segment_type, tablespace_name, SUM(bytes)/1048576 megs
FROM user_extents
GROUP BY segment_name, segment_type, tablespace_name

Maximo Health Check report

There is now a better and easier way to have the same information and much more details about potential performance issues in Maximo just by running a BIRT report.
Check out the Maximo Health Check report on developerWorks.


  1. Hi Bruno,
    Does IBM recommend Table Partitioning for tables which has more data. What are the pros and cons of doing this?

  2. Hi Srikar,

    I reached out to IBM with the same..they said Oracle database partitioning is not recommended..Any progress on your side?

    1. Table Partitioning requires different Oracle license... more $$$

  3. I have also got the same reply. As of now we are looking into archiving methods to bring down the data size.