Re: benchmark statspack or any very high transaction system (>4000 tps) statspack report?

From: Jonathan Lewis <>
Date: Fri, 16 May 2008 13:27:23 +0100
Message-ID: <059401c8b750$31ec5e20$0200a8c0@Primary>

The first counter-argument to your colleague's suggestion is the "piggyback" commit.

Assume you can do a 2 m/s turnaround on a call to log writer, limiting a SINGLE SESSION to 500 transactions per second - I've picked 2 m/s because even with 4 Gigabit links to a SAN cache you won't often get a much faster turnaround than that in "real" systems.

You only need about 30 concurrent sessions to keep on issuing commits at about half this rate to hit the target you want. Assume about 15 commit at once, the log writer can respond by clearing all 15 calls at once; but while it's doing this, the other 15 commit - so that their synch calls have to wait for
the current write to complete. But after the log writer has acknowledged the batch of commits, it can handle the second batch seven simultaneously - during with the first 15 sessions generate more redo and commit .. and so on.

It's the sort of case where your statspack might say:

    time spent in log file parallel write 1,000 seconds     time spent in log file sync 45,000 seconds

then when you look at the average times, the writes average about 2 m/s, and the log file syncs about 3 m/s.

The numbers don't quite add up - but I hope you get the idea. Log file sync TOTALS often overstate the impact of waiting for log writes in highly concurrent system when viewed at the system level.


Jonathan Lewis

Author: Cost Based Oracle: Fundamentals

The Co-operative Oracle Users' FAQ

  • Original Message ----- From: "Zhu,Chao" <> To: <> Sent: Friday, May 16, 2008 12:57 AM Subject: benchmark statspack or any very high transaction system (>4000 tps) statspack report?

> hi,
> Anyone has information about tpc-c benchmark statspack?
> I am very interested in their statspack report, see whether with such
> kind of huge transaction (it is something like 72000 transaction per
> second). As one business transaction involves quite a few DMLs, actual DML
> is far more than that.
> One of my colleagu has a theory that with redo write response time of
> 0.5ms (redo sync time in statspack), you can go at most 3600 "user commit"
> with reasonable(say, less than 2ms for redo write) response time even your
> SAN still have enough IO capacity; I disagree but I don't have such data
> about detailed performance data.
> Anyone has information where I can get the statspack for tpcc
> benchmark, or can share your statspack report with that part abstraction?
> (just system load profile part, and redo performance part)?
> --
> Regards
> Zhu Chao

Received on Fri May 16 2008 - 07:27:23 CDT

Original text of this message