Hi Mohammed,

One thing is to make sure you keep the statistics updated. Work with your DBA to find the best parameters to use when running statistics on them. When tables become very large, it takes longer and longer to hit the defaults, so sometimes you are running stats but not getting the impact you could. For example, if the default is to update statistics when a table has grown 10%, but the table has 500M records already, it wouldn't update the stats until there are 50M new rows. When the table grows to 700M rows, it won't update stats until there are 70M new rows. And so on.

Try this to get some basic information: select table_name, num_rows, last_analyzed from all_tables where table_name in (<list of tables>)

Interface tables can become fragmented because data is inserted then deleted. Your DBA will be able to check that for you. Depending on your business processes, there could be a couple of actions. If you can confirm that the interface table is completely empty, you can truncate it to quickly free up space and eliminate all fragmentation. The tables are shared by all organizations, though, and if there is always data in there, the DBA should have some other methods of defragmenting.