1 2 3 Previous Next 40 Replies Latest reply on Jan 10, 2012 10:51 AM by 894085 Go to original post
      • 30. Re: How to verify the data got committed
        Billy~Verreynne
        Adrian wrote:

        Autocommit, in a client-server context is just a performance optimisation that simply removes the need for an additional server round-trip.

        In very high performance systems it's usual to execute your transaction and switch on autocommit for the final action so that you don't incur an extra server round-trip and the obvious network waits.
        You are confused as to what autocommit means for developers from environments like Sybase and SQL-Server and others.

        It means that each DML is automatically committed. And the reasons for that are because of concurrency and because of potential lock escalation.

        This approach has no valid or sound reason in Oracle.

        That's the point I was making.

        Now if the OP was talking about the intricacies of the OCI instead (as you are alluding to), then he not only neglected to clearly state his problem, this is also not the {forum:id=67} forum.

        As for saving a round trip by using the OCI feature you referred to - in 99% of client implementations that would be irrelevant as far as performance optimisation goes. The typical client will make a bunch of DML calls and then finalise that using a COMMIT call. For argument sake, let's say there are 9 DMLs and then a commit. Eliminating that commit (reducing 10 round trips to 9 round trips) is a tiny reduction in calls and will not have any significant performance impact. Most clients will fall into this category.

        Also keep in mind that the OCI was designed decades ago when network performance was not that great (like running over 64Kb leased lines where the link is shared with other applications). Back then such an approach (sending an implied commit with a DML call) made a lot of sense. We use to get a network sniffer expert in to fully analyse client-server traffic generated by a client for traffic optimisation.

        In today's network environment - that OCI option has little relevance.
        • 31. Re: How to verify the data got committed
          894085
          Toon Koppelaars wrote:
          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
          Here's a bit of trace file retrieved from submitting a transaction with autocommit switched on from a JDBC driver using an OCI connection:

          The code does this:

          * Opens a connection with autocommit turned on or off:
          * Turns on 10046 trace by executing a prepared statement
          * Inserts a record into a noddy test table.
          * (Optionally manually commits)
          * Turns off 10046 trace by executing a prepared statement
          * Closes the connection.

          =====================
          PARSING IN CURSOR #3 len=75 dep=0 uid=202 oct=2 lid=202 tim=747705330452 hv=4284602770 ad='4b1151e0' sqlid='8k17m8bzq3qck'
          INSERT INTO /* test with auto commit */ test (some_value) VALUES ( :value )
          END OF STMT
          PARSE #3:c=0,e=318,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=747705330451
          BINDS #3:
           Bind#0
            oacdty=01 mxl=32(20) mxlc=00 mal=00 scl=00 pre=00
            oacflg=03 fl2=1000000 frm=01 csi=178 siz=32 off=0
            kxsbbbfp=611c2bd8  bln=32  avl=10  flg=05
            value="Some Value"
          EXEC #3:c=0,e=469,p=0,cr=1,cu=4,mis=1,r=1,dep=0,og=1,plh=0,tim=747705330974
          STAT #3 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL  (cr=1 pr=0 pw=0 time=0 us)'
          XCTEND rlbk=0, rd_only=0, tim=747705331038
          WAIT #3: nam='log file sync' ela= 816 buffer#=7478 sync scn=3667346697 p3=0 obj#=-1 tim=747705331963
          WAIT #3: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=747705332038
          WAIT #3: nam='SQL*Net message from client' ela= 1260 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=747705333324
          =====================
          PARSING IN CURSOR #4 len=55 dep=0 uid=202 oct=42 lid=202 tim=747705333389 hv=1692168403 ad='0' sqlid='5zwkyu5kdsw6m'
          ALTER SESSION SET EVENTS='10046 trace name context off'
          The interesting thing for me is that directly following the execution there is a transaction end (which is not a rollback) and a log file sync, next you see a network trip and me turning off the trace event.

          Now check what happens if I use a prepared commit:
          PARSING IN CURSOR #3 len=75 dep=0 uid=202 oct=2 lid=202 tim=748981197510 hv=4284602770 ad='4b1151e0' sqlid='8k17m8bzq3qck'
          INSERT INTO /* test with prepared commit */ test (some_value) VALUES ( :value )
          END OF STMT
          PARSE #3:c=0,e=25,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=748981197509
          BINDS #3:
           Bind#0
            oacdty=01 mxl=32(20) mxlc=00 mal=00 scl=00 pre=00
            oacflg=03 fl2=1000000 frm=01 csi=178 siz=32 off=0
            kxsbbbfp=61fe2bd8  bln=32  avl=10  flg=05
            value="Some Value"
          EXEC #3:c=0,e=128,p=0,cr=1,cu=4,mis=0,r=1,dep=0,og=1,plh=0,tim=748981197716
          STAT #3 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL  (cr=1 pr=0 pw=0 time=0 us)'
          WAIT #3: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=748981197779
          WAIT #3: nam='SQL*Net message from client' ela= 1544 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=748981199338
          =====================
          PARSING IN CURSOR #4 len=6 dep=0 uid=202 oct=44 lid=202 tim=748981199400 hv=255718823 ad='0' sqlid='8ggw94h7mvxd7'
          COMMIT
          END OF STMT
          PARSE #4:c=0,e=12,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=748981199399
          XCTEND rlbk=0, rd_only=0, tim=748981199475
          EXEC #4:c=0,e=246,p=0,cr=0,cu=1,mis=0,r=0,dep=0,og=0,plh=0,tim=748981199712
          WAIT #4: nam='log file sync' ela= 411 buffer#=2148 sync scn=3667351800 p3=0 obj#=-1 tim=748981200171
          WAIT #4: nam='SQL*Net message to client' ela= 0 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=748981200235
          WAIT #4: nam='SQL*Net message from client' ela= 1406 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=748981201657
          I see a network round trip followed by a parse on COMMIT and a log file sync.

          I disagree entirely with Billy about "today's network environment" being such that we don't need to worry about these things. Optimising is optimising, and it makes stuff scale. What's the point of installing super fast network and then bombarding it with unnecessary round trips? next you'll be suggesting we don't need to set an optimal fetch size, or prefetch rows, or use a client side cache because the networks are so good.

          When performance is not set as a design axiom, we end up with disasters like XML and SOA.

          Also the "OCI was designed decades ago" doesn't make a lot of sense - Oracle was designed decades ago, as was PL/SQL, but all three have evolved.

          I'm not disputing the fact that this is a fringe optimisation, or that using autocommit as a default is a Bad Idea. All I'm saying is that your initial attack on the concept as merely a product of inferior databases is not entirely true.

          I realise this is not the OCI forum, but it isn't the JDBC forum either - I would tend to argue the question is in the wrong forum. Auto commit is a concept, not a technology. It is available through a host of client interfaces.
          • 32. Re: How to verify the data got committed
            Toon Koppelaars
            Thanks for posting this.
            It clearly demonstrates indeed that using the autocommit feature of the OCI layer will prevent the extra roundtrip.
            • 33. Re: How to verify the data got committed
              894085
              FYI - identical behaviour with an jdbc:oracle:thin:@ connection.
              XCTEND rlbk=0, rd_only=1, tim=750574015547
              WAIT #3: nam='SQL*Net message to client' ela= 1 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=750574018149
              WAIT #3: nam='SQL*Net message from client' ela= 1013 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=750574019190
              =====================
              PARSING IN CURSOR #2 len=75 dep=0 uid=202 oct=2 lid=202 tim=750574019252 hv=4284602770 ad='4b1151e0' sqlid='8k17m8bzq3qck'
              INSERT INTO /* test with auto commit */ test (some_value) VALUES ( :value )
              END OF STMT
              PARSE #2:c=0,e=32,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=750574019251
              BINDS #2:
               Bind#0
                oacdty=01 mxl=32(10) mxlc=00 mal=00 scl=00 pre=00
                oacflg=03 fl2=1000010 frm=01 csi=178 siz=32 off=0
                kxsbbbfp=62052bd8  bln=32  avl=10  flg=05
                value="Some Value"
              EXEC #2:c=0,e=127,p=0,cr=1,cu=4,mis=0,r=1,dep=0,og=1,plh=0,tim=750574019430
              STAT #2 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL  (cr=1 pr=0 pw=0 time=0 us)'
              XCTEND rlbk=0, rd_only=0, tim=750574019500
              WAIT #2: nam='log file sync' ela= 337 buffer#=6723 sync scn=3667392170 p3=0 obj#=-1 tim=750574019989
              WAIT #2: nam='SQL*Net message to client' ela= 1 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=750574020059
              WAIT #2: nam='SQL*Net message from client' ela= 1584 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=750574021666
              =====================
              PARSING IN CURSOR #4 len=55 dep=0 uid=202 oct=42 lid=202 tim=750574021704 hv=1692168403 ad='0' sqlid='5zwkyu5kdsw6m'
              ALTER SESSION SET EVENTS='10046 trace name context off'
              END OF STMT
              PARSE #4:c=0,e=8,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=750574021704
              EXEC #4:c=0,e=755,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=750574022507
              And finally using as connection.commit on thin JDBC rather than a prepared commit seems to simply show a roundtrip and XCTEND commit.
              =====================
              PARSING IN CURSOR #2 len=75 dep=0 uid=202 oct=2 lid=202 tim=750859349637 hv=4284602770 ad='4b1151e0' sqlid='8k17m8bzq3qck'
              INSERT INTO /* test with auto commit */ test (some_value) VALUES ( :value )
              END OF STMT
              PARSE #2:c=0,e=28,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=750859349636
              BINDS #2:
               Bind#0
                oacdty=01 mxl=32(13) mxlc=00 mal=00 scl=00 pre=00
                oacflg=03 fl2=1000010 frm=01 csi=178 siz=32 off=0
                kxsbbbfp=63022bd8  bln=32  avl=13  flg=05
                value="Some Value 99"
              EXEC #2:c=0,e=133,p=0,cr=1,cu=4,mis=0,r=1,dep=0,og=1,plh=0,tim=750859349847
              STAT #2 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL  (cr=1 pr=0 pw=0 time=0 us)'
              WAIT #2: nam='SQL*Net message to client' ela= 1 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=750859349907
              WAIT #2: nam='SQL*Net message from client' ela= 533 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=750859350458
              XCTEND rlbk=0, rd_only=0, tim=750859350508
              WAIT #0: nam='log file sync' ela= 482 buffer#=7415 sync scn=3667423989 p3=0 obj#=-1 tim=750859351127
              WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=750859351196
              WAIT #0: nam='SQL*Net message from client' ela= 727 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=750859351946
              =====================
              PARSING IN CURSOR #4 len=55 dep=0 uid=202 oct=42 lid=202 tim=750859352001 hv=1692168403 ad='0' sqlid='5zwkyu5kdsw6m'
              ALTER SESSION SET EVENTS='10046 trace name context off'
              • 34. Re: How to verify the data got committed
                Billy~Verreynne
                Adrian wrote:

                I disagree entirely with Billy about "today's network environment" being such that we don't need to worry about these things. Optimising is optimising, and it makes stuff scale. What's the point of installing super fast network and then bombarding it with unnecessary round trips?
                I said it is "+of little relevance+" and not that it is of "+no relevance+" on the modern network.

                If you need to optimise at that level.. well, it means exhausting all means and method of optimisation higher up the s/w stack. And that is a very tall order to simply tick off as done and done correctly.

                So I'm not disputing that it does not address a niche area in performance tuning. Simply that this niche is pretty much insignificant in using something like DSCP bits to mark traffic as a higher priority, ensuring full duplex all the way, ensuring optimal routing tables and so on. In other words, when it comes to network performance, there are a number of other options that need to be addressed before grasping at the straw offered by the OCI for adding an implicit commit to a server call.
                next you'll be suggesting we don't need to set an optimal fetch size, or prefetch rows, or use a client side cache because the networks are so good.
                Using the full MTU size is a fundamental for this type of client-server interaction. As for caching on the client side - that runs into issues such as ensuring that the client cache correctly reflects the truth in the database. And can cause lost updates and other data integrity issues of not done sensible.

                Also keep in mind that autocommit in the other databases have no bearing on minimising round trips. It has everything to do with performance and enabling concurrency.
                • 35. Re: How to verify the data got committed
                  894085
                  Interesting that this largely irrelevant optimisation is included in so many of the client drivers. Almost like the guys who put those together thought it was pretty useful, actually.

                  I did original say (before 3360 et al. jumped down my throat) that it applied to high performance environments.

                  "Race to zero" class applications aside, If I know that all transactions involve a single operation and commit (for example updating a market data ticker, say), then it makes sense to ensure that there are not two network roundtrips to write a record and commit it.

                  If optimised batching is used because throughput is more important than latency then it makes no sense to allow one network trip for the batch, and another just to commit it.

                  It just seems like common sense then to automatically commit on success of the last statement in a transaction as a default position, just like you wouldn't think of not using bind variables (in most cases), or not writing row-by-slow loops just because you think that modern storage and CPUs are fast.
                  • 36. Re: How to verify the data got committed
                    Peter Gjelstrup
                    Adrian wrote:
                    If optimised batching is used because throughput is more important than latency then it makes no sense to allow one network trip for the batch, and another just to commit it.
                    Not sure, but you seem to be suggesting the use of e.g a JDBC update batch, having autocommit=true.

                    Interesting.


                    Regards
                    Peter

                    P.S: I know that I by now have participated in the ongoing thread-jacking. Poor OP, maybe some good-hearted forum member could produce some sort of attempt to answer his question?
                    • 37. Re: How to verify the data got committed
                      Toon Koppelaars
                      In answering the OP:

                      You can use the V$SESSION and V$TRANSACTION dba views to find out if your transaction has indeed committed.

                      In v$session locate your row.
                      Then use the TADDR column value in your v$session row to check whether a row exists in v$transaction where the ADDR column in v$transaction equals the TADDR column of v$session.

                      If no v$transaction row can be found, then your session has committed its transaction.
                      • 38. Re: How to verify the data got committed
                        894085
                        Toon Koppelaars wrote:
                        In answering the OP:

                        You can use the V$SESSION and V$TRANSACTION dba views to find out if your transaction has indeed committed.

                        In v$session locate your row.
                        Then use the TADDR column value in your v$session row to check whether a row exists in v$transaction where the ADDR column in v$transaction equals the TADDR column of v$session.

                        If no v$transaction row can be found, then your session has committed its transaction.
                        To be fair, I did say that all the way back on page one. Along with an innocent comment about the performance benefit of setting autocommit to avoid a network round-trip on the final statement of a transaction.

                        Unfortunately in the rush to try to prove me wrong about something I was right about, the comment appears to have been missed.

                        And to the previous poster - I don't see this as thread-jacking at all. When people say things like "autocommit is a hack" it needs to be challenged, or people believe it.
                        • 39. Re: How to verify the data got committed
                          Peter Gjelstrup
                          And to the previous poster - I don't see this as thread-jacking at all. When people say things like "autocommit is a hack" it needs to be challenged, or people believe it.
                          That would be me :)

                          Surely it is thread-jacking, bringing forth a pseudo-discussion on performance.

                          And that autocommit should have it's place in "sub-zero applications", is something to remember. Wonder how the LGWR (And your throughput) feels about that.

                          But fine, you did show that a round trip can be saved, let's leave at that and speak no more of the consequences.

                          Don't know if autocommit is a hack, it is just "one of those things".
                          Oddly enough, is it the default behaviour in JDBC. For what's it's worth, my guess is that the reason for this is pretty close to what Billy Verreynne started out with.


                          Regards
                          Peter

                          To be fair, I did say that all the way back on page one.
                          Must have been lost along the road ;)
                          • 40. Re: How to verify the data got committed
                            894085
                            Peter Gjelstrup wrote:
                            Oddly enough, is it the default behaviour in JDBC. For what's it's worth, my guess is that the reason for this is pretty close to what Billy Verreynne started out with.


                            Regards
                            Peter
                            Yeah, I can't deny that. The myth of "needing to commit regularly" is one of those cringeworthy bits of dogma from the database dodgers.

                            >
                            >
                            To be fair, I did say that all the way back on page one.
                            Must have been lost along the road ;)
                            Aha. It got out of hand quickly.

                            Luckily I didn't point out the 3360's spurious observation:

                            >
                            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.
                            ...Is completely wrong as well - or this might have gone on for days...

                            >>
                            And that autocommit should have it's place in "sub-zero applications...
                            C'mon that wasn't nearly as creative as old lead-pipe trying to re-engineer the phrase "of little consequence" to retrospectively mean "actually useful" in a linguistic retreat and flanking manoeuvre of remarkable dexterity.

                            edit: Actually just came across the documentation that recommends this approach today, so thought it was worth updating the thread for a kind of completeness:

                            There reference comes from the "Building High Performance Drivers For Oracle 11g" Oracle whitepaper, on page 21.

                            "Oracle recommends that you use OCI_COMMIT_ON_SUCCESS, on the last OCISttmtExecute() in a transaction so that the commit gets piggybacked with the same call to the Database."

                            http://www.oracle.com/technetwork/topics/php/whatsnew/building-best-drivers-131920.pdf
                            1 2 3 Previous Next