1 2 3 Previous Next 40 Replies Latest reply on Jan 10, 2012 10:51 AM by 894085 Go to original post
      • 15. Re: How to verify the data got committed
        894085
        Here we go, it's my old buddies.
        BluShadow wrote:
        Adrian wrote:
        3360 - why do you follow me around trying to flame me?
        I don't see that 3360 was doing any flaming. Your the only person who's mentioned flaming in these threads.
        I never claimed that the JDBC thin driver uses OCI. The OP never stated whether he used JDBC at all, OCI or thin.
        It was your post above that brought OCI into the discussion to justify what you were saying that had been challenged. Yet the OP made no mention of OCI.
        The OP made no mention of JDBC either.

        "Auto Commit" is a concept. It is available through most client side libraries. JDBC, OCCI, OCI, ODP .net and so on. BV was dismissing the idea as a product of inferior lock-resource managed databases.

        I merely pointed to a use of auto commit as a concept, ratified by the documentation, where it is useful.
        I was merely responding, mostly to BV's "always manually commit" comment. Which is technology agnostic.
        "autocommit is a hack used by some other databases because of their inferior design in dealing with locks and concurrency"
        I merely commented that autocommit is an important high performance optimisation when used correctly, and as such we shouldn't discredit it as merely a tool for inferior lock-resource databases.
        Which still hasn't been shown to be proven, hence why 3360 challenged it.
        I posted a link to the documentation. Which I feel is a more valid contribution that 3360's post of a google search on "Autocommit".
        Nothing irrelevant there at all. Clearly you are still upset about a discussion on a previous thread, and I encourage you to let it lie. It is now you disrupting this thread.
        No, I don't think it was anything to do with a previous thread, more to do with generalized statements that 3360 believed were not correct. Nothing wrong with challenging such statements, especially if they could mislead the OP.

        It's fair enough 3360 has asked for clarification, so why not give clarification as to what is meant by your original response and in what context it fits with the OP's question.
        It should be obvious that I presented a valid use of the auto commit technique. I can't see why you are chosing to be so defensive against new ideas yet again.

        Of course it was everything to do with the other thread, which is why 3360 brought it up again by saying:
        "why you are taking yet another thread down an irrelevant detour."
        If you want to be a little closed club that doesn't allow new members and disregard everything I say, then feel free to ignore it. It just seems precious for 3360 to be leaping in and behaving like such a know-it-all brat when he's clearly wrong on this one.

        Under JDBC documentation Oracle say this:
        "In auto-commit mode, the COMMIT operation occurs either when the statement completes or the next execute occurs".
        Now that heavily implies to me that there is no additional roundtrip in the JDBC implementation. But I am verifying this entirely now (although my point stands despite this), at which point I'll accept both of your apologies.
        • 16. Re: How to verify the data got committed
          BluShadow
          Adrian wrote:
          Here we go, it's my old buddies.
          Let's not jump to conclusions eh. ;)
          BluShadow wrote:
          Adrian wrote:
          3360 - why do you follow me around trying to flame me?
          I don't see that 3360 was doing any flaming. Your the only person who's mentioned flaming in these threads.
          I never claimed that the JDBC thin driver uses OCI. The OP never stated whether he used JDBC at all, OCI or thin.
          It was your post above that brought OCI into the discussion to justify what you were saying that had been challenged. Yet the OP made no mention of OCI.
          The OP made no mention of JDBC either.
          And neither did anyone else before your first post, so not sure what your point is.
          "Auto Commit" is a concept. It is available through most client side libraries. JDBC, OCCI, OCI, ODP .net and so on. BV was dismissing the idea as a product of inferior lock-resource managed databases.
          So, clarify why it's not a product of inferior lock-resource managed databases. Just because various things do provide an auto-commit ability doesn't prove that it is performance enhancing as you suggested. Please clarify, that's all that's been asked. :)
          I merely pointed to a use of auto commit as a concept, ratified by the documentation, where it is useful.
          You said it was a performance improvement. So can you clarify why that is? You said it was less network round trips, but others have suggested that autocommit in many interfaces just causes the interface to issue a seperate commit after the statement anyway so doesn't make a difference. Do you have any tracing evidence to show otherwise? And does your 'performance improvement' apply to the OP's case?

          It should be obvious that I presented a valid use of the auto commit technique. I can't see why you are chosing to be so defensive against new ideas yet again.
          Not being defensive, just trying to understand. I guess it's how you choose to read it.
          Reading this thread, I've not seen how it definitely relates to the OP's question (as he didn't mention a particular interface), but assuming it was a valid use of auto commit, it's not been explained clearly and shown that it applies to all or just some interfaces.
          Of course it was everything to do with the other thread, which is why 3360 brought it up again by saying:
          "why you are taking yet another thread down an irrelevant detour."
          The point was that 3360 didn't initially respond to you because of the other thread, but to simply challenge what you were saying because there's no evidence to back it up. Only after you haven't clarified but have instead chosen to feel the need to back-challenge (or whatever you want to call it) has it been suggested you're taking this thread away from the initial point.
          If you want to be a little closed club that doesn't allow new members and disregard everything I say, then feel free to ignore it. It just seems precious for 3360 to be leaping in and behaving like such a know-it-all brat when he's clearly wrong on this one.
          It's possible to discuss things without name calling. That's not acceptable on the forums. Just because you've been challenged from what you've said doesn't mean that anyone is attacking you, but that you need to clarify what you're saying. There is no closed club and it's ok for you to question what someone else has written, but at least if you're going to do that, back up what you're saying or clarify it when someone challenges what you say, rather than getting all defensive. Is that too much to ask? :|

          Under JDBC documentation Oracle say this:
          "In auto-commit mode, the COMMIT operation occurs either when the statement completes or the next execute occurs".
          Now that heavily implies to me that there is no additional roundtrip in the JDBC implementation. But I am verifying this entirely now (although my point stands despite this), at which point I'll accept both of your apologies.
          Maybe it's just me, but that doesn't seem clear. It's not stated whether the commit is an additional operation performed afterwards or whether it somehow magically sends the statement with a "commit" flag tagged onto it. I'm not familiar with JDBC, but I know with ODBC when I've traced things, the commit from an autocommit is sent as a subsequent executed statement afterwards.

          We don't have anything to apologise for as we've not done anything wrong. I'll be happy if you can show it one way or the other.
          • 17. Re: How to verify the data got committed
            894085
            You said it was a performance improvement. So can you clarify why that is? You said it was less network round trips, but others have suggested that autocommit in >> many interfaces just causes the interface to issue a seperate commit after the statement anyway so doesn't make a difference.
            Where is your proof?

            I provided a reference to the documentation. I'm not sure why you seem to hold my posts out in need of more scrutiny than anyone else's.

            What you've done is taken other people's assumptions or questions as law without any proof (yet again) and yet you expect proof from me.

            What about this?

            If you think that both myself and the Oracle documentation is wrong, and that the OCI API does not do avoid the network roundtrip and that JDBC/OCI does not set the OCI commit_on_execute flag, and that thin JDBC similarly submits it's own explicit network roundtrip, and that the numerous other references to doing just this across the internet are incorrect, then where is your proof?

            You accuse me of making unsubstantiated references, despite referencing the actual documentation, and yet you make a counter claim with no proof! Do you suppose your large number of posts makes you exempt from rigorous examination of your statements?? You apparently do.
            • 18. Re: How to verify the data got committed
              BluShadow
              * sigh * and I was trying to be friendly and helpful (perhaps you missed the smilies?) in getting things back on track and help you understand where others were coming from. Seems you just want to argue though. If that's the case, then you won't find you get on well with others on this forum. * double sigh *
              • 19. Re: How to verify the data got committed
                894085
                If I "won't get on" with people like 3360 who want to leap in and flame everything I say as "nonsense" and "making things up", whilst having no valid counter argument, I can live with that. :)

                If I "won't get on" with patronising people like yourself trying to explain the laws of deductive reasoning to me, whilst ignoring them entirely themselves, I can live with that. :)

                If you want to take a set against everything I say, whilst backing up your little friends like 3360 who you believe can blithely go around calling the demonstrated and referenced facts of others "nonsense" whilst providing no counter proof of your own, then be my guest. :)

                It's sad that this attitude has destroyed what could have been a constructive thread with some interesting analysis, however :)

                I didn't want to argue. I had set last weeks set-to over the order by order_id debacle aside. However 3360 was clearly intent on another 3 or 4 rounds. Unfortunately he just jumped in and obfuscated what could have been an interesting discussion whilst unfortunately he was completely wrong about how auto commit is implemented in most contemporary client side drivers.
                • 20. Re: How to verify the data got committed
                  6363
                  We are talking about the autocommit setting.
                  Jeevanand K wrote:
                  i am having a prepared statement and i am executing a DML through that also i made the autocommit as true in that.
                  Which is obviously bad for transactions and bad for performance.

                  If as you claim, autocommit can actually be an optimization if it uses the underlying OCI_COMMIT_ON_SUCCESS (I assume commit_on_execute was a mistake of yours but maybe you have introduced another fork in the discussion) then I would assume you actually have to know that it uses it, which we know for JDBC thin it does not since it does not use OCI.
                  Adrian wrote:
                  If you think that both myself and the Oracle documentation is wrong, and that the OCI API does not do avoid the network roundtrip
                  No one has said that.
                  that JDBC/OCI does not set the OCI commit_on_execute flag, and that thin JDBC similarly submits it's own explicit network roundtrip, and that the numerous other references to doing just this across the internet are incorrect
                  I don't see anywhere, any evidence, that any of the various implementations of autocommit use OCI_COMMIT_ON_SUCCESS (again apologies if this was supposed to be commit_on_execute), if as you say there are many references please provide one.

                  In fact the difference in documented behavior of JDBC that in autocommit, it always commits whether the preceding statement succeeds or fails, strongly suggests it does not.
                  • 21. Re: How to verify the data got committed
                    6363
                    Adrian wrote:

                    unfortunately he was completely wrong about how auto commit is implemented in most contemporary client side drivers.
                    I would be interested if you could provide one reference showing an autocommit implementation uses OCI_COMMIT_ON_SUCCESS. We already know JDBC thin does not. Since you can say without doubt that the majority of autocommit implementations use OCI_COMMIT_ON_SUCCESS it should be no problem.
                    • 22. Re: How to verify the data got committed
                      894085
                      3360 wrote:
                      We are talking about the autocommit setting.
                      Jeevanand K wrote:
                      i am having a prepared statement and i am executing a DML through that also i made the autocommit as true in that.
                      Which is obviously bad for transactions and bad for performance.

                      If as you claim, autocommit can actually be an optimization if it uses the underlying OCI_COMMIT_ON_SUCCESS (I assume commit_on_execute was a mistake of yours but maybe you have introduced another fork in the discussion) then I would assume you actually have to know that it uses it, which we know for JDBC thin it does not since it does not use OCI.
                      Adrian wrote:
                      If you think that both myself and the Oracle documentation is wrong, and that the OCI API does not do avoid the network roundtrip
                      No one has said that.
                      that JDBC/OCI does not set the OCI commit_on_execute flag, and that thin JDBC similarly submits it's own explicit network roundtrip, and that the numerous other references to doing just this across the internet are incorrect
                      I don't see anywhere, any evidence, that any of the various implementations of autocommit use OCI_COMMIT_ON_SUCCESS (again apologies if this was supposed to be commit_on_execute), if as you say there are many references please provide one.

                      In fact the difference in documented behavior of JDBC that in autocommit, it always commits whether the preceding statement succeeds or fails, strongly suggests it does not.
                      Why are you still harping on about JDBC? At no point did I make any claim about JDBC.

                      We all agree that using auto commit with no consideration is simply bad for performance.

                      We all agree that a lot of resource-managed locking systems suggest autocommit to "free up resources" and other nonsense ideas.

                      All I simply did was bring some perspective in pointing out that OCI_COMMIT_ON_SUCCESS (which is what I said in the first place - so stop attempting to claim some pumpkin points because I referenced a private macro later) avoids the additional network round trip. As documented.

                      This is clearly a valid use of the concept of auto commit. Which clearly you didn't know about.

                      You've seen my post, and clearly leapt in because you fancied another few rounds over the locked thread on "order by order_id" from last week. Hence your reference to "another thread".

                      There is certainly no explicit "COMMIT" trip executed in any of the thin or OCI JDBC driver versions I've tested here today under auto commit as far as I can trace. In fact there appear to be further optimisations in JDBC, but I am still verifying that. I know for a fact there isn't another round trip in OCI. I'm verifying several versions of OCCI and ODP .net as I go. It's very difficult to verify what happens in the JDBC thin driver because the network trace is obviously not available.
                      "We already know JDBC thin does not"
                      No we don't. You just claimed that. It does not use OCI - but it seems very reasonable to me that if the server is capable of servicing a request to commit on success, then it must be possible for the thin driver to leverage the server side capability also - and any decent performant driver would leverage that capability as available.

                      It seems very unlikely that Oracle would widely document a performance advantage of doing so if they were blatantly lying and under the covers more network round trips were occurring.

                      Is it possible that there is a giant conspiracy at Oracle to not instrument these round trips so they could claim this benefit? Is that what you are suggesting?

                      JDBC is pretty clever at minimising roundtrips. If you've used the batch update facility in a loop you can get some confusing occurrances if you don't know what is going on. I'd be deeply surprised if this wasn't similarly well thought through.
                      • 23. Re: How to verify the data got committed
                        Toon Koppelaars
                        Adrian wrote:
                        Toon Koppelaars wrote:
                        The question is, does the JDBC driver (assuming that's what the OP is using), make use of this OCI setting?

                        The proof is in the pudding, by doing a sql-trace and establishing that JDBC does not send and automatic seperate COMMIT, but instead uses that setting.

                        Many other 'middleware/client' layers do the automatic separate commit.

                        Edited by: Toon Koppelaars on Nov 21, 2011 3:35 PM
                        The OP unfortunately doesn't say. I was more replying to the sentiment that "autocommit" as a concept had no value and was merely for those "other inferior" databases. As a concept, it's useful for this optimisation.

                        However, that doesn't make a great deal of sense to me (at least in all cases). How would JDBC know implcitly that it is your last statement before you were about to COMMIT? What if you have a conditional commit?

                        I'd love to see your trace file proving that though, along with a code example. Could you post it up here?

                        If they have found an optimising path where this is possible it would certainly prove my point.

                        EDIT: Wait, I missed your point. I thought you were saying you had done a sql-trace and shown that JDBC would automatically switch on commit on success on the final call where an execute() commitrans() could be optimised. Yes, you make a good point. However if JDBC/OCI does not use OCI effectively, I'd be pretty disappointed. Let me check and get back on that.
                        Adrian,

                        I'm no Java programmer, far from it, so cannot do this test myself. If you can, I'd be very interested in seeing the snippet of raw tracefile that shows the PARSE/EXEC of a dml-statement (that was prepared with the "OCI autocommit" flag) + some of the following lines in that tracefile.

                        Thanks,
                        Toon
                        • 24. Re: How to verify the data got committed
                          Solomon Yakobson
                          >
                          I'd be very interested in seeing the snippet of raw tracefile that shows the PARSE/EXEC of a dml-statement (that was prepared with the "OCI autocommit" flag) + some of the following lines in that tracefile.
                          I doubted you'll see anything like that in trace file. AFAIK Oracle does not have database level autocommit like, for example, SQL Server. Client tools do. And it is purely client's responsibility when to commit (I am talking DML here). So all I expect to see in such trace is client issued commits.

                          SY.
                          • 25. Re: How to verify the data got committed
                            894085
                            Solomon Yakobson wrote:
                            >
                            I'd be very interested in seeing the snippet of raw tracefile that shows the PARSE/EXEC of a dml-statement (that was prepared with the "OCI autocommit" flag) + some of the following lines in that tracefile.
                            I doubted you'll see anything like that in trace file. AFAIK Oracle does not have database level autocommit like, for example, SQL Server. Client tools do. And it is purely client's responsibility when to commit (I am talking DML here). So all I expect to see in such trace is client issued commits.

                            SY.
                            You can pretty easily see a round trip to execute a commit. Or rather, in the majority of cases, you don't see one.
                            • 26. Re: How to verify the data got committed
                              BluShadow
                              Adrian wrote:
                              If I "won't get on" with patronising people like yourself trying to explain the laws of deductive reasoning to me, whilst ignoring them entirely themselves, I can live with that. :)
                              I never had any intention of being patronising, just of offering some advice and doing what I could to help alleviate the potential issue that was clearly arising, as any moderator should do.

                              As is common on forums (without meaning to sound patronising :) ), I know people can read things with a different intonation than that posted by the person who wrote it, so it's not unusual for people to read the wrong emotions into things. I know 3360 and many of the other posters here from experience and I've never known them to be aggressive or flaming in any manner, just professional and good at showing their knowledge and expertise.

                              One thing that people will always confront on these forums (which you'd know if you were a regular) is anyone who makes claims of performance improvements without showing evidence. The classic is (and I'm confident you are beyond this category) those who come here claiming that count(1) is faster than count(*).

                              So let's just get back to clarifying and confirming with evidence what has been stated and we can perhaps agree to agree or disagree as necessary. ;)
                              • 27. Re: How to verify the data got committed
                                Solomon Yakobson
                                Adrian wrote:

                                You can pretty easily see a round trip to execute a commit. Or rather, in the majority of cases, you don't see one.
                                SQL> set autocommit off
                                SQL> connect scott@orcl
                                Enter password: *****
                                Connected.
                                SQL> select  name,
                                  2          sid,
                                  3          value
                                  4    from  v$sesstat,
                                  5          v$statname
                                  6    where v$sesstat.statistic# = v$statname.statistic#
                                  7      and v$statname.name in (
                                  8                              'SQL*Net roundtrips to/from client',
                                  9                              'user commits'
                                 10                             )
                                 11      and sid in (select sid from v$mystat)
                                 12  /
                                
                                NAME                                       SID      VALUE
                                ----------------------------------- ---------- ----------
                                user commits                                27          0
                                SQL*Net roundtrips to/from client           27         12
                                
                                SQL> insert into emp1 select * from emp
                                  2  /
                                
                                14 rows created.
                                
                                SQL> select  name,
                                  2          sid,
                                  3          value
                                  4    from  v$sesstat,
                                  5          v$statname
                                  6    where v$sesstat.statistic# = v$statname.statistic#
                                  7      and v$statname.name in (
                                  8                              'SQL*Net roundtrips to/from client',
                                  9                              'user commits'
                                 10                             )
                                 11      and sid in (select sid from v$mystat)
                                 12  /
                                
                                NAME                                       SID      VALUE
                                ----------------------------------- ---------- ----------
                                user commits                                27          0
                                SQL*Net roundtrips to/from client           27         15 <-- As you can see with autocommitt off INSERT incremented roundtrips by 1 (2 roundtript takes select itself)
                                SQL> set autocommit on
                                SQL> select  name,
                                  2          sid,
                                  3          value
                                  4    from  v$sesstat,
                                  5          v$statname
                                  6    where v$sesstat.statistic# = v$statname.statistic#
                                  7      and v$statname.name in (
                                  8                              'SQL*Net roundtrips to/from client',
                                  9                              'user commits'
                                 10                             )
                                 11      and sid in (select sid from v$mystat)
                                 12  /
                                
                                NAME                                       SID      VALUE
                                ----------------------------------- ---------- ----------
                                user commits                                27          0
                                SQL*Net roundtrips to/from client           27         17 <-- As you can see setting autocommitt on did not change roundtrips - it wasn't even communicated to database 
                                
                                SQL> insert into emp1 select * from emp
                                  2  /
                                
                                14 rows created.
                                
                                Commit complete.
                                SQL> select  name,
                                  2          sid,
                                  3          value
                                  4    from  v$sesstat,
                                  5          v$statname
                                  6    where v$sesstat.statistic# = v$statname.statistic#
                                  7      and v$statname.name in (
                                  8                              'SQL*Net roundtrips to/from client',
                                  9                              'user commits'
                                 10                             )
                                 11      and sid in (select sid from v$mystat)
                                 12  /
                                
                                NAME                                       SID      VALUE
                                ----------------------------------- ---------- ----------
                                user commits                                27          1
                                SQL*Net roundtrips to/from client           27         21 <-- Now there were 2 roundtrips: one for insert and one for commit.
                                
                                SQL> 
                                SY.
                                • 28. Re: How to verify the data got committed
                                  6363
                                  Adrian wrote:
                                  >
                                  "We already know JDBC thin does not"
                                  No we don't. You just claimed that.
                                  Full quote.
                                  I would be interested if you could provide one reference showing an autocommit implementation uses OCI_COMMIT_ON_SUCCESS. We already know JDBC thin does not.
                                  We already know JDBC thin does not use OCI_COMMIT_ON_SUCCESS since it does not use OCI.

                                  Seems a reasonable claim to me.
                                  • 29. Re: How to verify the data got committed
                                    Billy~Verreynne
                                    Toon Koppelaars wrote:
                                    Billy  Verreynne  wrote:
                                    ... And one writer will only block another writer when both are writing the very same rows...
                                    Not fully true...

                                    Scenario: parent-table and child-table and enabled FK between them. Only one row in parent-table, no rows in child table.

                                    Writer one deletes parent-row, but doesn't commit yet.
                                    Writer two inserts child for that parent-row and will block...
                                    That scenario still means 2 processes/sessions wanting to change the same data in my view - granted, more complex as the definition of "+sameness+" is via a foreign key.

                                    What is not fully true wrt the statement is where the only sameness is the data block the rows reside in and contention for that block (across cache fusion, only a single TL, etc).