Hitachi Vantara Pentaho Community Forums
Results 1 to 13 of 13

Thread: Batch insert and commit size

  1. #1
    Join Date
    Oct 2008
    Posts
    22

    Default Batch insert and commit size

    When we have the batch insert mode on and we set a very high commit size (say - 1000000000)
    1)
    for a record set of 100000, all the records do not get transferred.
    2)If the number of recrods is only 1000, it works without any issue.

    The source and target tables are in Oracle. The transformations do not show any error.

    How to repeat:
    Create tablet1 as select * from all_objects where 1 = 2 ;
    Create tablet12 as select * from all_objects where 1 = 2 ;
    insert into t1 select * from all_objects; (repeat till you have about 100000 records)
    Create a simple transformation that loads from t1 to t12. Truncate table option is kept on. I am setting max size for Java in spoon as 512M (set OPT=-Xmx512m ).

  2. #2
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    The oracle batch size is limited to something like 64000 or thereabouts.

  3. #3
    Join Date
    Oct 2008
    Posts
    22

    Default Batch insert and commit size

    Thanks for the quick response. How can I change this? Is this something at Pentaho side or an Oracle parameter?
    Regards,
    Jayadevan

  4. #4
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    What? Google is down in India?

    Oracle of course!

  5. #5
    Join Date
    Oct 2008
    Posts
    22

    Default Batch insert and commit size

    Hi,
    Google is very much up and running.
    It is just that after working with Oracle for a few years, I never came across such a parameter and so did not bother googling. Oracle would throw and error about rollback segment or undo tbs in such a scenario. Pentaho is saying everything is successful. I check the count and find that only partial load has happened.
    Any hints about how to fix this?
    Regards,
    Jay

  6. #6
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    There is absolutely no performance benefit in going for a 1M rows batch size.
    It's one of these "WTF are you doing anyway" cases.

    Sure, in this case, there is probably another Oracle bug at work here since it doesn't throw an error, but why do you even set it that high? Shees folks!
    A few thousand rows tops would be optimal or just leave the default and you would be fine.

    Matt

  7. #7
    Join Date
    Oct 2008
    Posts
    22

    Default Batch insert and commit size

    I wanted to make sure that you would be able to reproduce the situation if necessary, that is why I suggested that number. I am not keeping that number in a real life scenario.
    Let me check at what point I start getting this error and update.
    Thanks.

  8. #8
    Join Date
    Oct 2008
    Posts
    22

    Default

    OK. I start getting this issue when the commit size goes above 60000. Anyway, since it looks like that can not be resolved, here is something I am trying to do. The entire transformation should roll back or commit. If I am trying to insert, say 1000 records and there is an error in the 999th record, everything needs to be rolled back. How can that be done? I was trying to do that by keeping a high commit size.

  9. #9
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Apparently there was a bug in the Oracle 10g driver that prevents you from setting batch sizes > 1632 (don't ask!).

    "Unique connections" in the transformation settings makes the whole transformation transactional.
    That has nothing to do with the issue.

  10. #10
    Join Date
    Oct 2008
    Posts
    22

    Default

    Thanks. I was trying to achieve that by keeping a high commit size.

  11. #11
    Join Date
    Oct 2008
    Posts
    22

    Default

    hmm. With unique connection checked, even an unreasonably high commit size works.

  12. #12
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Because it is ignored obviously. Otherwise you can't make the transformation transactional can you?

  13. #13
    Join Date
    Oct 2008
    Posts
    22

    Default

    Obviously can't. Thanks a bunch for those quick replies.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.