Thursday, June 02, 2005

READ ONLY tablespace performance discussion.

Mike Ault yesterday published a set of results on his blog showing the differences in performance between READ_ONLY tablespaces and your normal everyday READ_WRITE tablespaces in Oracle, Are READ_ONLY tablespaces faster as well an article on it with Robert Freeman. This came about as a result of a posting by Tom Kyte on his blog which was a result of an article written by Don Burleson. One of the claims in the article was that read-only tablespace is faster because it bypasses the read-consistency that Oracle uses. The article has since been edited and the claim no longer appear in the article so the discussion surrounding all this might be a moot point. What's frustrating is that there were no indication on the article itself that it has been edited and 'corrections' made. But then DKB is well known for this tactic where he would post and re-edit/correct without documenting that so that his posting/article changes all the time except for those published in hard copy. The irony is that the edited article now refers to Mike's and Robert's article (a matter of "Circular References").

From the results published by Mike and some of the others, I think it is safe to conclude that any performance gains are so minute that it would not make a difference. Where would you use READ ONLY tablespace. I would suggest that if you have static data that are never going to change, locating them on a READ ONLY tablespace reduces the maintenance and support required (both by the database and the DBA).

2 comments:

Bill S. said...

Peter,
In case you have been otherwise engaged, Mike has now closed comments on that blog entry. I guess the difference between "greatly improve" and "statistically negligible" was too great a gulf for even him to span.
I liked the summary at the end - too bad it was the end, though. I was really interested there.

Peter K said...

Bill,
I noticed and yeah, I was hoping that the discussion will carry on further between Mike, Howard, Tom & Don but unfortunately that wasn't to be.