-1

I am loading multiple batches of data (~40 MB) in parallel to the Oracle DB. I am noticing parallel Oracle commits to DB are very slow in that case. But single commit is much faster. Looks like this problem is related to the Oracle redo log files or log file syncs. Could you help to solve this problem.

Pavel Petkun
  • 21
  • 7
  • What does "parallel Oracle commits" mean to you? Are you certain that the problem isn't just that your parallel processing isn't scaling the way you would like? What causes you to believe that the commit is the operation that is slowing you down? Do you have some AWR/ Statspack reports that lead you to believe that? Are you saying that each thread loads a 40 MB batch of data and then issues a single commit? How many simultaneous threads do you have, how much data are you loading, and how many times are you committing? – Justin Cave May 16 '14 at 20:45
  • I have 5 Java processes. Each of them performs data load concluding loading operation into a transaction. There is one transaction per process. I was debugging java class that performs data load and there was much time spent in commit method invocation (~5 min in that case) – Pavel Petkun May 16 '14 at 20:59
  • What wait event(s) is the database waiting on? Is each Java process issuing a single commit? So you're describing going from a situation where if there is 1 total commit things are fast but when you go to just 5 total commits now your process is slow? – Justin Cave May 16 '14 at 21:01
  • Yes. Each Java process issuing a single commit. Yes. 100MB single commit in one process is faster than 20MB per 5 processes commits for me. – Pavel Petkun May 16 '14 at 21:08
  • What is the database waiting on? – Justin Cave May 16 '14 at 21:09
  • Are you using direct load? – vav May 16 '14 at 21:15
  • The database is waiting on: net message from client, rdbms ipc message, DIAG idle wait, log file switch (checkpoint incomplete), asynch descriptor resize, Streams AQ: qmn coordinator idle wait, smon timer, db file async I/O submit, Space Manager: slave idle wait, pmon timer, VKRM Idle, Streams AQ: waiting for time management or cleanup tasks, Streams AQ: qmn slave idle wait, VKTM Logical Idle Wait – Pavel Petkun May 16 '14 at 21:41
  • Sorry, I'm not sure what that is... Are you getting that by querying v$session and just showing what each session is currently waiting on? We'd need to know what the database session(s) your application is using is waiting on during the particular Java method invocation you've identified. If that is the only activity going on in the database, that could come from an AWR/ Statspack report. Otherwise, it would probably need to come from an ASH report or, if you're not licensed for AWR, from something like a session trace. – Justin Cave May 16 '14 at 21:45
  • Yes. I was querying v$session_wait. Trying to get needed info you specified... – Pavel Petkun May 16 '14 at 21:52
  • My sessions during the 'commit' Java method invocation wait on the **log file switch (checkpoint incomplete)** event then **free buffer waits** event (nearly 50/50 of commit time) – Pavel Petkun May 16 '14 at 22:43

1 Answers1

0

If you have log file switch (checkpoint incomplete) events, it's means that your sizing your redo-logs to small. What is the size of them? Could you add more with biggest size(it's easy to do it)?

eliatou
  • 744
  • 5
  • 12
  • Thank you. I have increased the size of the redo logs. For now I have 6 groups of the redo logs per 400M. As a result my sessions don't wait on the **log file switch (checkpoint incomplete)** event but still wait on the **free buffer waits** event (~2 min). I think this is a problem. Isn't it? – Pavel Petkun May 17 '14 at 11:21
  • Even more than 5 mins... So is there any way to increase the buffer cache size? – Pavel Petkun May 17 '14 at 12:47
  • I'm not sure to well understand your problem, but it seems to me that you have an I/O pb. This could be the bottleneck. So parrallelize in that case is maybe not a good solution. A solution to speed up could be the batch in nologging mode. – eliatou May 18 '14 at 02:04