Take the 2-minute tour ×
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.

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
    
duplicating question from stack overflow - as it was suggested this is better suited. –  praveen Nov 16 '11 at 6:47
    
if you'll register your account you'll get better notification, etc. –  jcolebrand Nov 17 '11 at 5:44
add comment

3 Answers 3

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.

share|improve this answer
    
Yup even i guessed the indices were causing an issue and removed them before running the inserts - the difference was still huge. –  praveen Nov 16 '11 at 9:13
add comment

Since it is not using all the memory, the possibilities are:

  1. The inserts do not need more memory and the performance difference is caused by another difference between the systems. This could be anything from database initialization parameters, to other database activity, to storage differences, to any number of other differences.

  2. The inserts benefit from additional memory, but the 4GB machine is preventing increased use due to Oracle or OS restrictions. For example, the SGA size may be more restricted on the 4GB system.

If more memory is allocated to Oracle processes on 16GB machine than the 4GB machine then you should investigate the later. If similar amounts of memory (not percentages) are allocated on each, then you should investigate the former.

share|improve this answer
add comment

As SYS run @?/rdbms/admin/awrrpt. That will allow you to compare where machines A and B are spending the most time. There's too little to go on in your question. Are the machines, disks, Oracle versions, other workloads etc identical apart from the memory?

Or for a specific SQL_ID, use AWR SQL report.

share|improve this answer
    
awr won't help a lot as it only shows the system performance and not the performance of individual queries. It makes more sense to trace the individual session. –  steve Nov 16 '11 at 7:19
    
the machines are same in terms of processor capacity and disk and oracle version. The workload is same across both the databases - one application runs against the database that inserts these rows. These inserts are made across multiple sessions - so tracing sessions was not helping. –  praveen Nov 16 '11 at 9:12
    
i've added the trace 10046 at system level for both db's. Once the job finishes i will have more data to add –  praveen Nov 16 '11 at 9:25
    
@steve correct, but there is too little to go on in the question, so that is where to start. I have updated my answer with the next step. –  Gaius Nov 16 '11 at 9:35
    
@praveen - multiple sessions running the same SQL? Because you can report on a SQL_ID with AWR too. –  Gaius Nov 16 '11 at 9:36
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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