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
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.


3 comments:

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

    ReplyDelete
  2. Hi Srikar,

    I reached out to IBM with the same..they said Oracle database partitioning is not recommended..Any progress on your side?
    https://urldefense.proofpoint.com/v2/url?u=http-3A__www-2D01.ibm.com_support_docview.wss-3Fuid-3Dswg21403417&d=DwIFAg&c=xJPf3uN2gg7foA7dEwyrhg&r=e4HJM8ZHJWEiSO-eYn9X9oMavHxqnfjUrpxZ8Iwe2V4&m=Kz6fL_5aES7PyqPIZT5GYy0SjiY36P5HWpFjAKl0Xkg&s=BLdXot3VPpmzXMmQryV-7YnlnTR-dZcMaLeTPuXFtb8&e=

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

    ReplyDelete