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
ORDER BY megs DESC;

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.