Concurrency and It's Cost

I posted something earlier in the week asking to understand how the fetch buffer works. I've since done work a) to figure out how it all works, and b) to assess the costs of alternatives. If you're interested and still reading, my summary follows. The fetch buffer is filled with a number of records at one shot. This is obviously a big performance booster. However, you must understand how this works with concurrency control. If you're using "committed read", OnLine checks to make sure that the records are committed WHEN THE BUFFER IS FILLED. If, between filling the buffer and your application executing the fetch, the record gets deleted, you will still retrieve that record (because it fetches from that buffer, not the DB itself). If that's not good enough, you can switch to "cursor stability". This makes sure that the record exists when you fetch it. There are 2 flavors to this: a) Within a transaction, every record you fetch is guaranteed to be good. b) If you're not within a transaction, fetching any record will be accurate UNLESS the next one to be fetched is deleted/updated. If so, you'll get the old one. Otherwise, your results are good. (Based on DB size, this caveat is quite rare.) Timing: We have a system which continually reads records at a extremely high rate (and then sends them up to a satellite). We need very high throughput here. The following are times to fetch 375,000 records on an HP-UX 10.20 workstation with 64Mb of RAM: Method Time(Sec) Committed Read, No Transactions 143 Repeatable Read, No Transactions 466 (226% longer) Repeatable Read, with Transactions 440 (208% longer) I hope somebody finds this useful. David David Samson, Senior Database Researcher dsamson@ndsisrael.com NDS Technologies Israel Ltd. +972 2-589-4529 PO Box 23012 Fax: +972 2-589-4578 Jerusalem, Israel