commits and sql trace

From: Scott Gamble <zifnab_at_islandsofmyth.org>
Date: Fri, 20 Sep 2002 14:17:26 GMT
Message-ID: <W5Gi9.111$0I3.6302@petpeeve.ziplink.net>

Oracle 8.1.7.3
Tru64 5.1

This is a third party application and we have no visibility to the source. We have been back and forth with the vendor for a while now trying to get some performance improvements made to the code. The general flow of the code goes like this...

      select products
      insert customer and organization info into a temp table
      select adjustments using the temp table
      delete all rows from the temp table
      commit.

The use of that temp table has become optional however we have not stopped using it at this point though that is the plan. A note also even though we made it into a temp table they were still deleting the rows, this has been addressed in the new release as well (supposedly).

My question is about commits in the trace files. One of the issues we had had with their code over the last 2 years or so is that rather than one commit it had an entire series of commits. For example a typical trace would look like this..

delete rows..

commit;
commit;
commit;

...

With the new release of the code we just received we no longer get the 'commit' statement itself in the trace files, however there are still numerous 'XCTEND' in the trace file, which as far as I understand is still a commit

  1898 delete from TRIL_JOINS_WRK where TRIL_JOINS_WRK.local_id = :1   1899 END OF STMT
  1900 PARSE #4:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=0,tim=173520301   1901 EXEC 4:c=1,e=5,p=0,cr=2,cu=412,mis=0,r=393,dep=0,og=4,tim=173520306

  1902  XCTEND rlbk=0, rd_only=0
  1903  XCTEND rlbk=0, rd_only=1
  1904  XCTEND rlbk=0, rd_only=1
  1905  XCTEND rlbk=0, rd_only=1

What I wanted to know is, is that XCTEND caused by an actual commit in their code or is it an implicit commit of some kind. Trying to understand the lack of the 'commit' statment.

Hope that made sense

Scott Gamble             Received on Fri Sep 20 2002 - 09:17:26 CDT