0

Looking at Oracle Statspack report, which has snap of exactly at before and after of the batch program, is it possible to say how much commits has been done on database? How many transaction has been done?

dowonderatwill
  • 109
  • 1
  • 15

1 Answers1

0

In the Load Profile section you have number of transactions per second and number of rollbacks per second:

   Load Profile              Per Second    Per Transaction    Per Exec    Per Call
~~~~~~~~~~~~      ------------------  ----------------- ----------- -----------
      DB time(s):                0.4                0.0        0.01        0.06
       DB CPU(s):                0.2                0.0        0.00        0.03
       Redo size:           20,766.2            2,092.1
   Logical reads:              548.5               55.3
   Block changes:              138.3               13.9
  Physical reads:               22.9                2.3
 Physical writes:               16.5                1.7
      User calls:                7.0                0.7
          Parses:                9.2                0.9
     Hard parses:                0.0                0.0
W/A MB processed:                1.8                0.2
          Logons:                0.1                0.0
        Executes:               62.6                6.3
       Rollbacks:                0.0                0.0
    Transactions:                9.9
pifor
  • 7,419
  • 2
  • 8
  • 16
  • So, the total seconds elapsed between the two snapshot multiplied to Transactions will give me total commit happened on database. Right? We are doing commit every 100 chunk, but it is showing so may commits. I was expecting 1 commit for one chunk (we used addBatch and then execute). Hibernate is the ORM so, I doubt batching is not occurring. Thank you for your reply. – dowonderatwill May 09 '20 at 11:20
  • The total seconds elapsed between the two snapshot multiplied by Transactions will give total COMMIT + ROLLBACK that happened on database. If there are no ROLLBACK yes it is the total number of COMMIT. – pifor May 09 '20 at 11:48
  • This count of will give different result as it depends on time between the two snaps . Say, actual transactions are N, time between two snap with time diff as T, then in one test the result it will be R = N/T. Next test, same N transactions done but time between snap is T+delta=T2 then R2=N/T2, R2 may not be equal to R1. We will not get right value, provided we ensure delta of T2 (T+delta) be close to 0. Other thing observed that if there is no transaction, at some later snaps then this values does remain zero. Is there any other parameter where it stores the actual count of transactions? – dowonderatwill May 10 '20 at 06:23