Mike Ault's thoughts on various topics, Oracle related and not. Note: I reserve the right to delete comments that are not contributing to the overall theme of the BLOG or are insulting or demeaning to anyone. The posts on this blog are provided “as is” with no warranties and confer no rights. The opinions expressed on this site are mine and mine alone, and do not necessarily represent those of my employer.

Wednesday, June 01, 2005

Are READ-ONLY Tablespaces Faster?

In a recent online article it was claimed that read-only tablespaces performed better than read-write tablespaces for SELECT type transactions. As could be predicted many folks jumped up and down about this saying this was not true. I decided rather than do a knee jerk reaction as these folks have done I would do something radical, I would test the premise using an actual database.

For my test I selected a 2,545,000 row table in a tablespace that has been placed on a 7X1 disk RAID-5 array attached to a 3 Ghtz CPU Linux Redhat 3.4 based server running Oracle Enterprise Version 10.1.0.3. The query was chosen to force a full table scan against the table by calculating an average of a non-indexed numeric field.

The table structure was:

SQL> desc merged_patient_counts_ru
Name--------------------------------------Null?----Type
----------------------------------------- -------- ----------------
SOURCE---------------------------------------------VARCHAR2(10)
PATIENT_NAME---------------------------------------VARCHAR2(128)
DMIS-----------------------------------------------VARCHAR2(4)
MEPRS----------------------------------------------VARCHAR2(32)
DIVISION_NAME--------------------------------------VARCHAR2(30)
MONTH----------------------------------------------VARCHAR2(6)
ICD9-----------------------------------------------VARCHAR2(9)
ICD9_CODE_DIAGNOSIS--------------------------------VARCHAR2(30)
ENCOUNTER_ADMISSION_DATETIME-----------------------DATE
ENCOUNTER_DISCHARGE_DATETIME-----------------------DATE
APP_COUNT------------------------------------------NUMBER
ADMIT_COUNT----------------------------------------NUMBER
ER_COUNT-------------------------------------------NUMBER


The SELECT statement used for the test was:

SQL> SELECT avg(app_count) FROM MERGED_PATIENT_COUNTS_RU;

AVG(APP_COUNT)
--------------
86.4054172

The explain plan and statistics (identical for both READ WRITE and READ ONLY executions) was:

Elapsed: 00:00:01.52

Execution Plan
----------------------------------------------------------
0---SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1102 Card=1 Bytes=3)
1 0--SORT (AGGREGATE)
2 1--PX COORDINATOR
3 2--PX SEND* (QC (RANDOM)) OF ':TQ10000' :Q1000
4 3--SORT* (AGGREGATE) :Q1000
5 4--PX BLOCK* (ITERATOR) (Cost=1102 Card=2545000 Bytes :Q1000 =7635000)
6 5--TABLE ACCESS* (FULL) OF 'MERGED_PATIENT_COUNTS_R :Q1000
U' (TABLE) (Cost=1102 Card=2545000 Bytes=7635000)


3 PARALLEL_TO_SERIAL
4 PARALLEL_COMBINED_WITH_PARENT
5 PARALLEL_COMBINED_WITH_CHILD
6 PARALLEL_COMBINED_WITH_PARENT


Statistics
----------------------------------------------------------
12----recursive calls
0-----db block gets
21024-consistent gets
19312-physical reads
0-----redo size
418---bytes sent via SQL*Net to client
512---bytes received via SQL*Net from client
2-----SQL*Net roundtrips to/from client
53----sorts (memory)
0-----sorts (disk)
1-----rows processed

The process used for testing was:

1. Set timing on
2. Set autotrace on
3. Gather explain plans and statistics for both READ WRITE and READ ONLY runs, allowing statistics to stabilize.
4. Set autotrace off
5. Set tablespace status to READ WRITE
6. Execute SELECT 15 times
7. Set tablespace status to READ ONLY
8. Execute SELECT 15 times

The results are shown in table 1.



Reading Read Only Read Write
1-------1.49------1.56
2-------1.45------1.49
3-------1.39------1.37
4-------1.45------1.5
5-------1.5-------1.55
6-------1.3-------1.46
7-------1.43------1.5
8-------1.42------1.46
9-------1.35------1.38
10------1.4-------1.43
11------1.47------1.5
12------1.49------1.46
13------1.44------1.33
14------1.43------1.47
15------1.45------1.56
Avg-----1.430667--1.468

Table 1: Results from Performance Test of READ-ONLY Tablespace

As can be seen from the table, the performance of the READ-ONLY tablespace was marginally better (1.4307 verses 1.468 seconds on the average) than the READ-WRITE tablespace.

Based on these results, the article was correct in the assertion that the performance of READ-ONLY tablespaces is better than that of READ-WRITE tablespaces for SELECT transactions. While the performance difference is only a couple of percent it is still a measurable affect.

32 comments:

DaPi said...

Mike,

The standard deviations of the two samples (RO and RW) are aprox 0.06 so I don't think you can claim a statistically significant effect.

Mike said...

Maybe so. However, I repeated the test a couple of times afterwards with repeatible results. In each the read-only was marginally faster. I would love to see others repeat the tests on different releases and platforms.

Mike

Mike said...

Repeated the test using 50 measurements each, again, the average time for the Read Only (1.44) was fractionally better than the average time (1.46) for the Read Write. The SD for the Read Only was 0.067 while for the Read Write it was 0.137 so the error bars still overlap and the read-write shows less consistent times as evidenced by the greater SD.

Again, in each test I run (I have done them with 5, 10, 15 and now 50 data pair sets) the read-only comes out, on the average, just a few hundreths of a second better than the read-write.

DaPi said...

Frustrating isn't it!

My back-of-an-envelope calculation makes that a 1-sigma effect - so about a 1-in-3 chance of getting the observed effect (i.e. the 50 pairs) from a null hypothesis of no difference between RO and RW.

Peter K said...

There were no knee jerk reactions. Again, the article authored by a well-known author did not back up his claims with data.

One of the things the article identified was the fact that READ-ONLY tablespaces bypass the read-consistency feature of Oracle which was not true as Tom Kyte correctly and nicely pointed out.

Mike said...

Peter,

The folks saying it trwern't so also didn't provide any data to back up their claims, so therefore it falls into the "This is not how I think it works, therefore it must be wrong" camp of thinking which is a kissin cousin to knee jerk.

Mike

David Aldridge said...

The reaction was not to the performance claim per se, it was because the author claimed "great improvements" (if memory serves) based on Oracle "bypassing read consistency mechanisms". The latter was pretty obvious nonsense, and your test has demonstrated no great improvements.

I'd be interested to see this without parallel query -- I would make a guess that the difference could be due to the checkpoint process that the query coordinator requires before running direct i/o against a table. Possibly the checkpoint is not carried out if the tablespace is known to be read-only, but it's just a guess.

Perhaps if you also broke out the wait time with a 10046 trace you could get a more specific idea of what is causing any difference.

By the way Mike, the problem with providing a test to disprove some people's bogus claims is that they'll immediately shriek "Can't prove anything with a script!!!" (despite using simple scripts themselves for their own purposes).

Ask me how I know. ;)

David Aldridge said...

You might also try it with and without flushing the buffer cache before each run, if you're not using parallel query. You might also try setting _serial_direct_read to true to get direct reads without the Px overhead

Mike said...

Interesting ideas...when I get back to the lab I will do more detailed tests. I am doing these between client crises right now so can't do thinkgs that are too involved.

Jeff Hunter said...

Hmm, interesting. Did a test on Solaris 2.8 with 9.2.0.5. Had a 1+ million row table in a readonly tablespace (ro1) and the same table in a read/write tablespace (rw1). Ran the same query on both tables, and the results showed a read only tablespace marginally faster.

SELECT COUNT(*)
FROM
( SELECT /*+ parallel (t, 2) */ * FROM RW1 T)


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10 0.01 0.48 0 30 0 0
Fetch 10 0.09 97.74 0 0 0 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 21 0.10 98.22 0 30 0 10

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 41 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
10 SORT AGGREGATE
0 SORT AGGREGATE
0 TABLE ACCESS FULL RW1


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
PX Deq: Join ACK 19 0.02 0.15
PX Deq: Parse Reply 11 0.00 0.01
PX Deq: Execute Reply 282 1.01 96.29
PX Deq: Signal ACK 38 0.10 1.12
enqueue 14 0.06 0.47
********************************************************************************

SELECT COUNT(*)
FROM
( SELECT /*+ parallel (t, 2) */ * FROM RO1 T)


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10 0.03 0.47 0 30 0 0
Fetch 10 0.17 92.25 0 0 0 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 21 0.20 92.72 0 30 0 10

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 41 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
10 SORT AGGREGATE
0 SORT AGGREGATE
0 TABLE ACCESS FULL RO1


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
PX Deq: Join ACK 20 0.04 0.26
PX Deq: Parse Reply 12 0.00 0.01
PX Deq: Execute Reply 286 0.90 90.85
PX Deq: Signal ACK 39 0.10 1.15
enqueue 13 0.07 0.48
********************************************************************************

Mike said...

Jeff,

Make sure to run the selects multiple times and do an average, my timings ran from a high of 2 seconds to a low of 1.25 seconds.

Mike

Mike said...

dapi,

Wonder if a paired value t-test would show anything? I think I called that correctly, it has been several years since I dealt with such things.

Mike

Jeff Hunter said...

Make sure to run the selects multiple times and do an average, my timings ran from a high of 2 seconds to a low of 1.25 seconds.
The tkprof output shows I ran it 10 times. ;)

Mike said...

Here are the t-test results:

Paired Student's t-Test: Results
The results of a paired t-test performed at 07:31 on 2-JUN-2005
t= 0.963
degrees of freedom = 49
The probability of this result, assuming the null hypothesis, is 0.340

Group A: Number of items= 50
1.29 1.31 1.31 1.32 1.32 1.34 1.35 1.35 1.36 1.36 1.39 1.39 1.40 1.40 1.40 1.41 1.41 1.41 1.41 1.42 1.42 1.43 1.43 1.45 1.45 1.45 1.45 1.46 1.46 1.46 1.47 1.47 1.47 1.48 1.48 1.49 1.50 1.50 1.51 1.51 1.51 1.52 1.52 1.52 1.52 1.54 1.54 1.77 1.80 2.14
Mean = 1.46
95% confidence interval for Mean: 1.422 thru 1.500
Standard Deviation = 0.137
Hi = 2.14 Low = 1.29
Median = 1.45
Average Absolute Deviation from Median = 8.020E-02

Group B: Number of items= 50
1.29 1.29 1.32 1.34 1.35 1.35 1.37 1.37 1.37 1.38 1.39 1.39 1.39 1.39 1.40 1.40 1.41 1.42 1.42 1.43 1.43 1.44 1.44 1.44 1.45 1.45 1.45 1.45 1.45 1.46 1.46 1.46 1.47 1.47 1.48 1.48 1.49 1.49 1.49 1.50 1.50 1.50 1.50 1.53 1.53 1.53 1.53 1.55 1.55 1.57
Mean = 1.44
95% confidence interval for Mean: 1.421 thru 1.459
Standard Deviation = 6.717E-02
Hi = 1.57 Low = 1.29
Median = 1.45
Average Absolute Deviation from Median = 5.340E-02

Group A-B: Number of items= 50
-0.210 -0.210 -0.180 -0.170 -0.150 -0.130 -0.130 -0.120 -0.120 -0.110 -0.110 -0.100 -9.000E-02 -6.000E-02 -6.000E-02 -5.000E-02 -5.000E-02 -5.000E-02 -4.000E-02 -3.000E-02 -2.000E-02 -1.000E-02 1.000E-02 1.000E-02 1.000E-02 2.000E-02 2.000E-02 2.000E-02 4.000E-02 5.000E-02 6.000E-02 7.000E-02 7.000E-02 7.000E-02 8.000E-02 8.000E-02 9.000E-02 9.000E-02 9.000E-02 9.000E-02 1.000E-01 0.100 0.110 0.120 0.130 0.160 0.170 0.340 0.370 0.690
Mean = 2.120E-02
95% confidence interval for Mean: -2.3047E-02 thru 6.5447E-02
Standard Deviation = 0.156
Hi = 0.690 Low = -0.210
Median = 1.500E-02
Average Absolute Deviation from Median = 0.108

usually for a "significant" difference the probability should be less than 0.05, so while this is not a significant difference, it none the less shows (0.34) that there is only a 34% probability that the two sets of measurements are looking at the "same" process.

Thanks to http://www.physics.csbsju.edu/stats/t-test_NROW_form.html for the t-test program.

Mike

Mike

Thomas Kyte said...

Mike,


did you actually read the blog where "people" (hey, I'm a plural) took a look at the comment?

The comment was (paraphrasing here) "read only tablespaces, they are good, because they bypass read consistency"


Did you actually read it? One wonders, Because you say The folks saying it trwern't so also didn't provide any data to back up their claims, but I distinctly recall having a link there to an article that shows a query getting an ora-1555 on a query against a read only tablespace.

NOW, if read consistency where bypassed (the crux of the comment), one wonders - how exactly did the 1555 get raised? Indeed.

So, what more "proof" did you need exactly?


So, where is the knee jerk reaction?

Hey everyone, look:

ops$tkyte@ORA10GR1> select sysdate from dual;
Elapsed: 00:00:00.03
Elapsed: 00:00:00.09
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01
Elapsed: 00:00:00.01
Elapsed: 00:00:00.01
Elapsed: 00:00:00.03
Elapsed: 00:00:00.01

might set timing on in sqlplus, with a meager 1/100ths of second resolution (so each observation is off by +-0.009, make sure to roll that into your statistics computation), that times the amount of time it took sqlplus to push pixels on your latop, transmit data over the network, coordinate PQ slaves, do physical IO, computing an average, etc etc etc (remember, these are all things NOT affected by the consistent gets - what? it did consistent gets, are those not -- yes, they are - read consistent gets....) not be the 'best' way to time things?.

And why sometimes a read only tablespace is really slow compared to a read write?

So, if you claim "readonly tables provide greatly improved performance and this single user test script! (gee, where have we read about them?) proves it -- well, so be it. We'll just adjust our definitions of greatly to match. (greatly now means "not statistically relevant")


But, question for the author of the quote: do read only tablespaces really bypass read consistency? Do they? And wasn't that the context of the original quote?


But then again, you must mean some other people -- because if you read the blog, it wasn't about performance, it was about read consistency. So, who are these people?

"many folks"
"jumped up and down"
"saying this was not true (re: peformance)"

I think we need a dictionary, many? performance? jumped up and down?

I've got a great word.

hyperbole.

Someone is charging windmills again, but have fun everyone.

DaPi said...

Mike,

You're right that it should be a t-test (we are estimating the SD's from the sample). But with a sample size of 50 and effects less than 2-sigma, the critical point of the t- and normal-distributions differ much less that the approximations I made in the back-of-an-envelope stuff.
(i.e. sqrt(50) = 7
RO = 1.44 +- 0.01
RW = 1.46 +- 0.02
delta = 0.02 +- 0.02 => 1-sigma)

DaPi said...

. . . it none the less shows (0.34) that there is only a 34% probability that the two sets of measurements are looking at the "same" process.
So we agree on the size of the effect - I estimated 1-in-3 in my second post.

Though I must disagree on your wording, the program you used states it better: The probability of this result, assuming the null hypothesis, is 0.340

i.e. IF there is really NO difference between RO and RW (null hypothesis), then you have a 1-in-3 chance of getting results that deviate this much (or more)from "equality".

Most journals in the physical sciences require 3-sigma effects (99.7% confidence) - even then such effects have been know to go away!

Peter K said...

Mike,
I just wanted to thank you for the time and effort that you have put to publish the results. I noticed that the original article that started the whole discussion has been edited where the statement (claim) that Tom referred to is no longer in the article but instead now make a reference to the article that you and Robert Freeman published as a result of this discussion. I think this is what Jonathan Lewis would refer to as "Circular References" although your article did not refer to Don's original article.

David Aldridge said...

... but on the other hand, there're such issues as ...

Multiple Blocksizes — All data warehouse indexes that are accessed via range scans and Oracle objects that must be accessed via full-table, or full-index, scans should be in a 32k blocksize.

Maybe this is not the forum for a full discussion of these issues ... but if not then I don't know where is a suitable place. I have a long list of comments that I'd like to make to improve that article.

DaPi said...

Mike, like Peter_K, I do appreciate the fact that you've made your data so accessible.

But I can't agree with your conclusion (now published in two places):
Based on these results, the article was correct in the assertion that the performance of READ-ONLY tablespaces is better than that of READ-WRITE tablespaces for SELECT transactions.

With the t-test you made, that could/should be rewritten:
Based on these results, the article has a 1-in-3 chance of being wrong in the assertion that the performance of READ-ONLY tablespaces is better than that of READ-WRITE tablespaces for SELECT transactions.

I guess it's up to you what odds you like bet on!

Thomas Kyte said...

[quote from a book, not an online article]
Read only Tablespaces - If a DBA has a time series warehouse in which information eventually becomes statis, using tablespace partitions and marking older tablespaces as read-only can greatly improve performance. When a tablespace is marked as read only, Oracle can bypass this read consistency mechanism, reducing overhead and resulting in faster throughput
[/quote]

If we are too actually take the "greatly improve" (no numbers, no tests) at face value, I guess most of us must re-adjust our interpretation of "greatly improve". The book does not say "better performance", "marginally better performance", "hard to see the difference but a little better", it says "greatly improve"

Mike -- just your opinion -- are your findings evidence of "greatly improving"? Are your findings evidence of "read consistency is bypassed"? Would you yourself seriously recomend someone architect their data layout in order to make things read only for performance reasons? (don't get me wrong here, many reasons for read only, but I wouldn't put "greatly improve performance" or "bypass read consistency" into that list).


I did some measured tests myself, I did not use timing in sqlplus. I did use v$ tables, captured statistics, latches and elapsed times. Did it on a 10,000,000 row table (about 146,000 8k blocks).

You can peek at the results at:

system1.lst single cpu p4
system 2 dual xeon poweredge
big_table script to generate data


Looking at cpu consumed (the speed up must be cpu related) shows sometime A is better than B and B sometimes better than A -- but but less than a percent either way.

The elapsed times flucuate widely from run to run (running the same script over and over). Fractions of a percent are the norm.

So, if you want to advise people to put things read only for performance, so be it.

Because -- and this is very important, that was never the point, performance was never the point. An "anonymous" poster brought it (and a totally wild documentation reference that had nothing to do with anything, not really sure what that persons point was) up.

I was always pointing at read consistency.

Don Burleson said...

Hi Tom,

Yes, I changed that page because the evidence contradicted my statement!

Yes, Tom, I was WRONG!

I WAS WRONG, I said, WRONG.

Just like you were wrong when you made that misleading comment that the optimizer parameters could not influence LIO and PIO.

Get over it. . . .

BTW I've added an errata e-mail on over 8,000 pages on BC domains. Oracle changes, and I'm undertaking to be version-specific and correct any myths that might be influencing beginners.

For example, books in Oracle7 correctly talked about LRU buffer insertion (which changed to midpoint insertion) and confused some people and caused some Oaken dude to resign.

Also, lets not forget the change in 9i where the KEEP and RECYCLE buffers were split-out from the DEFAULT buffer, &c, &c.

Oracle changes, and BC is comitted to providing constant errata and release change information.

How about asktom? Did you publish Bipul Kumar's test showing that changes to the optimnizer parms had a direct impact on I/O?

As to this read-only test, I've comissioned a full-sized multi-user TPC-C and TPC-H test, complete with 10046 traces and conclusive evidence.

I'm also going to do a multi-usder TPC-H on parallelism for RAC v. monolithic servers to show differences in response time and throughput, postulating that consolidated CPU resources will provide faster servicing for large-table full-table scans than RAC where additional processes and additional interconnect services are required.

I think that Mike is onto something in his small test, and that a full bench (with cross-tablespace SQL into updatable tablespaces) will show a consistent, statistically significant improvement for RO tablespaces.

The reason? Ah, that's the scientific method, right?

You form a hypothesis, and mine is that the code path has overhead for read consistency.

Is my hypothesis wrong? Maybe, maybe not, but the bench will enlighten us.

BTW, If I recall correctly, your hypothesis said that there would be NO DIFFERENCE because the read mechanism was exactly the same on RO v. updatable tablespaces.

Do you have one of your legendary "proofs" for this?

A valid, reproducable TPC bench is coming, and perhaps we could compare our "proofs" afterward. . .

Thomas Kyte said...

Don,

Question: shouldn't the tests about performance you are doing now (to which I reiterate -- it was never about that, someone is making it about that but this was about read consistency) be done before making claims? Isn't that how it normally proceeds? (wonder how you'll do the tpc-c's with read only tablespaces, that should be interesting)

Did you actually read the page in context about optimizer index cost adj and caching?

Or any of the of the followups in your own forum that pointed this out to you over and over by many people (I believe Niall was the first to make mention of it, but unfortunately it seems the thread no longer exists, but what Niall said there was:

...[quote, good old file save]
Its false as you present it, but the way in which you present it does seem rather out of context. That discussion starts with Tom stating that the way to tune is to reduce LIOs. So clearly Tom knows the benefit of concentrating on LIO.

This particular sentence comes as a small part of a reply to a poster who wants to know if changing the OPTIMIZER_ parameters will affect consistent gets for a given statement with a given (hinted) execution plan. In the particular case that is being referred to, it won't. I guess this is one of the problems of this sort of online discussion, that quotes when taken out of context can mislead - rather like your dba who read something in a book, took it out of context and ran into trouble.
.......[/quote]

Get over what? You guys seem to be making the big thing out of this -- with a knee jerk reaction paper that was pulled, rewritten, pulled, rewritten (the original two or three knee jerk versions of http://www.dba-oracle.com/oracle_tips_read_only_tablespace_performance.htm is what I'm referring to)

I'd be more than willing to let this die, it just keeps popping its proverbial head up and not by my doing.

You are the ones making it sound like I published a knee jerk, incorrect statement without testing (when in fact -- I pointed to a test, and when in fact I made not a single comment about performance)

If Bipul published a test on my site -- I did not remove it, in fact it is really only in response to you that i've removed much of anything on the site. I didn't receive one in email from him, I didn't remove anything from him on the site?

Where did you read that hypothesis you quote and incorrectly attribute to me?

I stated accurately that read consistency is not bypassed and yes, "legendary" example was in fact sitting right there (but both you and Mike seem to be missing the "click" on that link).

David Aldridge said...

Hmmm, it seems as if "greatly improved performance" has been downgraded to "statistically significant improvement". Quite the volte-face, I'd say.

A search of a couple of TPC-H full disclosure statements, including this one, doesn't show any statements that include the text "read only" except as part of the specification text. Apparantly Oracle and HP don't think it worthwhile, and you know of course that if it meant a difference of even a few percent then they'd be all over it.

I believe that there was another benchmark promised to prove that a 32kb block size for temporary tablespaces yielded a 30% disk sort improvement -- how's that one going? I mention it because it seems to be another case of great performance improvements being promised before the facts are in, and counter to knowledge on Oracle internals.

DaPi said...
This comment has been removed by a blog administrator.
Mike said...

The article was corrected for the improper statements, that seems to be what waqs wanted by everyone. Now everyone wants it back the way it was so they can continue to point to it and say "See we told this is bad!" get over it.

By running the test 50 times I hoped to eliminate a bit of the random timing differences to see if the values would level out. They didn't, it still showed a bias towards the read only side.

Unfortunately the top two links that were posted to show more detailed tests would not resolve, please repost proper ones. The third link only shows the script used to populate the table.

The other posts are not confrontational, if any further posts are, they will be deleted (as I had to do with one post already.)

Mike

Thomas Kyte said...

Mike,

As could be predicted many folks jumped up and down about this saying this was not true. I decided rather than do a knee jerk reaction as these folks have done...

In the vein of getting over it, could you point us to any cases of that? I mean, you seem to have a list of links that prompted this article at hand. Could you share them with us? I am not aware of any.

Until you brought it up, I don't think anyone was really questioning the performance aspect, questioning the magnitude perhaps (sure, somewhere between 0.5% and 2% as far as CPU utilization goes apparently, as others have said "hard to measure" -- I'd put it into inperceptable -- but anyway). The only thing I've seen pointed out was the read consistency issue. Where there others?

Who is asking for the article to be put back??? Where did you see that? I don't think anyone wants that.

The links are

http://asktom.oracle.com/~tkyte/system1.lst
http://asktom.oracle.com/~tkyte/system2.lst
http://asktom.oracle.com/~tkyte/big_table2.sql

Looks like IE doesn't want to show the ".lst" files in the browser, save them to disk and read using "write" or "word" (notepad doesn't get the big line)

Mike said...

Tom,

Everyone has agreed the read consistency part was incorrect and it was changed in the article. So what is the point of bringing it up? This isn't blog about Don. It is amazing that your entire life seems to be revolving around exposing Don. Also, unfortunately the technology doesn't exist to go back into books and correct problems there other than errata postings which most won't go look for anyway. So what was the point of bringing in a book that was printed prior to the article and its correction?

I wasn't addressing the read consistency issue because it was already dealt with. The performance questions interested me so I cheched it out and that is what the disussion is about.

DaPi said...

I don't know what else might have been going on here. The "This post has been removed by the author", after slimdave Aldridge's, was mine. I asked a question and made a comment, which I realised, after re-reading what had gone before, were STUPID. To save my blushes, I deleted the post about 15 minutes after making it.

Mike said...

Again, as is on the front page of the blog, if a post doesn't add to the thread it will be removed. I removed a post that did not contribute, was inflamatory in nature and was of no value.

Some others here are borderline.

As long as the post contributes and applies to the blog/thread then it will remain.

Peter K said...

D published an article which incorrectly specified that Oracle bypasses read-consistency for READ-ONLY tablespace which a number of folks pointed out including T. M blogged about knee-jerking reactions and published a set of results proving that performance is better for READ-ONLY tablespaces than READ-WRITE tablespaces. D finally revised his article removing that incorrect statement and blame T and cited some stuff about T & H which H tried to respond but was removed by M who then told T that since D removed the incorrect statement (after this blog posting), that has been dealt with.

Man, I'm confused as hell now since the "knee-jerk" reactions were actually about the incorrect read-consistency statement of D.

The good news is that D has corrected his online article BUT the bottom line seems to be READ-ONLY tablespace performance are marginally (< a couple of % points) faster (statistically speaking) than READ-WRITE tablespaces (for FULL TABLE scans).

Mike said...

Peter,

Good summary. Looks like time to close...

Mike