The story starts almost one year ago ,Customer is building their WMS and ERP application . Customer IT insist WMS and ERP should share the same Oracle Database ( it is a concern to save the development cost for Interface and license cost to buy another oracle ) . I caution that the potential performance issue would be occuried because of this kind of db sharing . It is hard for both vendors ( I am developing ERP and another vendor develop WMS) to distinguish the reason of performance problem.

Now , one year passed , the situation is :
A. Our ERP application is running just perfect.
B. WMS application could run just so so when ERP is offline
C. WMS application became extremely slow if ERP is working .

In ERP application , since it is same database , we don't interface any data but just directly visiting the inventory table under WMS schema ( wms.tb_inventory).

Customer regret and now they are thinking whether to separate those 2 system on 2 different database , it is a kind of lesson to customer for they making such kind of decision one year ago.

From technical point of view , If ERP application is ported on another database , There are 2 options:
1. Still re-use the current code , which impact on development cost is little , just change wms.tb_inventory to wms.tb_inventory@wmsdblink. But I think the performance would not be resolved ( and it may slow down ERP too)
2. Change the technical architecure , Use ASCII file , Middle table to interface information between both systems. Well the development cost is ++.

I am asking my developer to check V$session_id , and it appears the hit radio of WMS application session is extremely low , the ratio of some session shows 10% or less . Is that a clue to tell actually WMS itself , the poor SQL coding is the final reason of the performance issue?