Oppolzer - Informatik / Blog

I would like to add:

I don't think that COMMITs on application with only SELECT processing do
help a lot.

You should make sure that the SELECT processing sets no locks (that is,
use the proper ISOLATION LEVEL and CURRENTDATA bind option), and you
should CLOSE your cursors as soon as possible. Of course, you should specify
FOR FETCH ONLY on all your FETCH ONLY cursors, and your SQLs should be
tuned using EXPLAIN etc., so that they pick the best access path.

Please consider that COMMIT processing can be expensive, too. We once had an
application that did over 1000 COMMITs per second - this was a real nightware
and did'nt make any sense. Even in an application with high update activity, a
COMMIT every two or three seconds is sufficient.

Kind regards

Bernd



Am 05.04.2012 21:48, schrieb Bernd Oppolzer:
> Look at the CURRENTDATA bind option, if it is at the default (YES), there may be
> locks with SELECT processing, if the cursors are not read only due to ORDER BY,
> JOIN or FOR FETCH ONLY clauses (this may not be the case with WITH UR). I always
> recommend to change the default to CURRENTDATA NO.
>
> And: I don't think that buffer pool pages get externalized at commit. They stay
> in memory. The externalization of buffer pool pages is independent of logical
> transactions and depends of log activity etc. (number of log buffers written and
> maybe time since last externalization). It is possible that data gets
> externalized that has not yet been committed, and vice versa. The commit
> activity is recorded on the log, and in the case of a DB failure, the consistent
> state of the DB is reconstructed during DB recovery (undo and redo processing).
>
> I hope my explanations are correct; maybe others could comment on this.
>
> Kind regards
>
> Bernd
>
>
>
> Am 05.04.2012 21:05, schrieb R.C.:
>>
>> Hello Team,
>>
>> We have a bunch of application programs running in parallel and doing
>> huge amount of processing.
>>
>> They are using onlu SELECTS that too with UR clause.
>>
>> I know a commit can release locks and claims.And also any page in
>> buffer pool after a commit, gets externalized, there by making room
>> for a new page..reducing chances of page thrashing.
>>
>> Usually people use commits in inserts and updates, but i've a feeling
>> that it will help even in simple SELECT query also.
>>
>> Please share your thougths and experiences.
>>
>> Thanks!!!
>>
>>
>> -----End Original Message-----
>
>
> -----End Original Message-----