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
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.
5 thoughts on “Find large Maximo tables and indexes on Oracle”
Does IBM recommend Table Partitioning for tables which has more data. What are the pros and cons of doing this?
I reached out to IBM with the same..they said Oracle database partitioning is not recommended..Any progress on your side?
I have also got the same reply. As of now we are looking into archiving methods to bring down the data size.
Table Partitioning requires different Oracle license… more $$$
I appreciate you expertise and the time you take to post. Several of your pages have helped me save time and headache.