We are seeing drastic change in the amount of time taken for db operations depending on ram size.
The same content when inserted into a db running on a 4gb ram machine takes about 600% more than into a db running on a 16gb ram machine. The inserts are made through jdbc. Both the db machines were 64 bit machines and both were enterprise editions of db.
Interesting thing is that the memory consumption for the db running on 4gb machine never goes above 50-60% of the total capacity – so we do not understand why this difference. Is there any explanation for this behavior or some more pointers to look into this?
Best Answer
Praveen,
Oracle provides a lot of functionality to enable you to trace individual sessions (with waits and binds) or statements. You don't list the version in use, but most probably you can trace the inserts using DBMS_MONITOR package introduced in 10g. In 11g and if you have the relevant management pack licensed you could also use Real Time SQL Monitoring. http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/instance_tune.htm#CACGEEIF If you take this approach, of tracing the problematic SQL then you can know for certain where the time is going. The most common cause by the way for slow inserts is nothing to do with RAM and everything to do with the number of indexes on the table.