Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

Possible Duplicate:
RAM Impact on Oracle database operations

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?

share|improve this question
Im going to guess because with smaller space, the data in memory isnt laid out contiguous, so it has to find empty blocks. However with very large amounts of memory, there are always large blocks of free space. – Austin Henley Nov 16 '11 at 6:55
1  
@Austin this not how oracle works – steve Nov 16 '11 at 7:09

migrated from stackoverflow.com Nov 16 '11 at 10:19

marked as duplicate by Gaius, Leigh Riffel, jcolebrand♦ Nov 17 '11 at 5:43

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1 Answer

up vote 3 down vote

The information you provided is not sufficient to analyze the problem, so people will start guessing (BTW how have you established your theory that the problem is memory pressure?). To analyze the problem you need to enable sql_trace for both sessions, even better set event 10046 for both sessions and then compare the data.

this can be done by executing the following sql once the session has been established

alter session set events '10046 trace name context forever, level 12';

This will generate trace files. These trace files need to be analyzed either manually or with tkprof. You need to pay attention to the wait events.

Are there any other differences between the machines in terms of

  • Processor
  • Disk speeds and disk types
  • Network infrastructure
  • Other load on the machine
  • Number of database sessions

As these will affect the performance measurements

share|improve this answer
No difference is there in terms of processor (both are 64 bit machines) or disk speeds. No extra load is there on the machine other than the one application that runs those queries. So the number of sessions too are same. – praveen Nov 16 '11 at 9:16
i've added the trace at system level for both db's. Once the job finishes i will have more data to add – praveen Nov 16 '11 at 9:24

Not the answer you're looking for? Browse other questions tagged or ask your own question.