Last night I was called in on an unfortunate problem. A client has an update that they must run (unfortunately) which updates ~830,000 rows, setting one column equal to the other (two column table). On their development environment this was taking roughly twenty seconds to perform. However, on their soon-to-be production environment it was taking roughly 45 minutes.
Explain plans were checked, trace files examined, and not much popped up except that the production machine was attempting larger I/Os during the update and was consequently taking much longer. Comparing the initialization parameters between production and development showed the exact same parameters, except that the upcoming production box was using a 16k block size and development was using a 4k block size.
There was one other difference, which was that the upcoming production server was 64-bit and the development server was 32-bit. In order to make sure everything was the same, I created a fresh instance on the 64-bit production server with a block size of 4k, exported the objects from the instance with the 16k block size, and imported them.
The final result: When the update was run against the 16k blocksize DB, it took 45 minutes. Against the 4k blocksize DB on the same box with the same parameters and the same FS mounts, it took 2 minutes 20 seconds.
I even took it a step further to see if we could squeeze any more performance out of it. Setting FILESYSTEMIO_OPTIONS=’SETALL’ (instead of none) I was able to get the update down to 1.5 minutes. Turning off DB_BLOCK_CHECKING (not recommended) I was able to get it down to 10 seconds.
By going from a 16k blocksize to a 4k blocksize with all other things being equal, we experienced roughly a twenty times improvement. We shaved off even more time setting FILESYSTEMIO_OPTIONS = SETALL. And then we changed DB_BLOCK_CHECKING, a parameter Oracle documentation says only adds a 1 to 10% overhead depending on concurrency of DML, which made the update 6 times faster alone.
The final result was a 270 times improvement over the original.
To be fair, I also tried setting the FILESYSTEMIO_OPTIONS and DB_BLOCK_CHECKING the same on the 16k blocksize instance, which resulted in the update taking 30 minutes as opposed to 45. The results were better, but the 4k blocksize database still won by 180 times.
What’s more, all queries both large and small performed the same or better than in production, and a test insert of 100,000 rows went from 20 seconds on the 16k blocksize to 3 seconds on the 4k.
Stats
Oracle 9.2.0.8 on RHEL4 64-bit, 8CPU, 2+2 RAID10 for Data with a 128k segment size, 2+2 RAID10 for Redo, Hardware diagnostics report no issues
Conclusion
Honestly, there are no final conclusions yet. Like a weight loss commercial that shows a 400 pound man slimming up to 150 pounds, ‘results may not be typical’. While it definitely shows that changing only blocksize can have a profound effect, more thorough analysis will help get to the core of why it had such an effect. Whatever the case, it definitely shows us to keep an open mind in tuning. Leave no stone unturned!
NOTE: There is one interesting outcome that I’ve observed: on the 16k blocksize database, the UPDATE statement performs full table scans against FILE$. On the 4k blocksize database, it performs range scans using the FILE_I2 index. This only makes a difference of about 1 consistent get per execution, but it is worth noting.
NOTE: It is also worth reiterating, this is and Oracle 9.2.0.8 database.
Hi Steve,
Excellent write up!
How many times a month do you see systems where changing blocksize makes a profound impact on performance?
What about clients who have small rows in large blocks with high DML activity?
How about RAC, reducing overhead and increasing throughput with small blocksizes?
Did you try this with 8K block size?
What kind of latch waits are you seeing? I would guess, er, hypothesize you have something like this happening: first row updates, second row gotten, block needs to be copied and latched, repeat for entire block creating huge chains of latches. So you wind up with much larger latch chains with the larger block, plus more cpu usage to keep track of it. Is the cpu maxxed?
Your comment about all queries faster notwithstanding, I’d wonder about the wisdom of making major decisions about blocksize based on load windows. But of course, it depends.
and also consider this is RHEL4. Aka 2.6 kernel, with its IO optimizer full on.
One of the things it does is read-aheads at a much earlier threshold (tunable) then earlier RHEL releases. It might well be those are kicking in at 16k page size, while 4k is not enough to toggle them? Anyways, relationship of block size to filesystem IO tuning would be my first port of call in investigating why.
a: I’d wonder about the wisdom of making major decisions about blocksize based on load windows
I concur. I was merely pointing out an observation that is worthy of consideration.
Noons: Excellent point, this is part of my ongoing investigation as well for the sake of due diligence.
Hi Steven,
(Seems my attempts at leaving a comment hits 404, retrying… -Hopefully, with your last post, that should be fixed by now.)
Based on what evidence did you find that the prod system was “attempting larger I/Os during the update”?
On the dev system, were there other differences than 32-bit vs 64-bit? (OS, hardware setup, …)
To expand the field of view, could you consider doing a few re-runs, setup with:
“created a fresh instance on the … server with a block size of (N)k, exported the objects from the instance with the 16k block size, and imported them.”
Dev server, N=16 (for comparison)
Prod server, N=16 (to see if any difference between 16 and 16)
A nice little friday afternoon activity, right? 😉
(er… now, that was two days ago)
Fredrik, thanks for the persistence!
I hope to be able to do some re-runs. There were no other differences between the 32 and 64-bit environments, except of course the CPU types. However, most of the testing was not between pseudo-prod/dev, but on two different instances on pseudo-prod.
Time and consent permitting, I may try a couple re-runs just to make sure I’ve covered all bases.
Hi Steve,
>> I may try a couple re-runs just to make sure I’ve covered all bases.
How about controlling I/O? You can’t flush the buffer in 9i, and it’s a production instance . . . .
Can you put each table in the KEEP pool beforehand, and runthe tests during scheduled downtime to eliminate other factors?
So long as the data is identical (a fresh CTAS into identical tablespaces), and instance parms and I/O sub-system are identical you should be able to establish a statistically significant correlation.
There are various reasons why it’s questionable whether the dramatic change in performance has anything to do with the change in blocksize.
Rather than repeating some of the technical details previously posted on the OTN forum about this posting, here’s a pointer to where the discussion starts: http://forums.oracle.com/forums/message.jspa?messageID=2589212#2589212
You’ve probably thought of this, but I once had a large update problem which came down to the ‘select’ component (ie identifying the rows needing to be updated) having to apply a bunch of undo generated by its own ‘update’ component. Larger blocks would mean that there more records per block, which are all being updaetd and generating their own undo record. So when it is updating the 50th record on the block, it needs to apply the undo generated by the previous 49 updates on that block to get the consistent image of that 50th record. That may have had to do with access path (which I think used an index) and version (probably an early 9iR2). The ‘undo records applied’ stats were pretty horrifying when that statement ran.
>> There are various reasons why it’s questionable whether the dramatic change in performance has anything to do with the change in blocksize.
What a load of nonsense.
The blocksize was the only thing that he changed!
>> here’s a pointer to where the discussion starts
No, that’s just a bunch of Oracl;e wannabees, moaning because they have no access to a production database.
Artificial test cases are ALWAYS invalid when testing Oracle performance.
Steve did it 100% right, and there is no question that it was the change in blocksize that precipitated this huge performance boost.
Hi Gary,
>> Larger blocks would mean that there more records per block, which are all being updaetd and generating their own undo record.
Yes, excellent point.
Under heavy DML loads, small rows in a large blocksizes will frequently perform worse than large rows in a small blocksize. I havepersonally witnessed this, and using multiple blocksizes for a hybrid system can greatly improve performance:
http://www.dba-oracle.com/oracle_news/news_karam_tunes_blocksize_20x_improvement.htm
> The blocksize was the only thing that he changed!
Yes, but that one thing has many dependent variables. Jonathan has just demonstrated a possibility on the OTN thread – pctfree could be wrong for one blocksize and not the other. Steve needs to try his 16 block test with a more appropriate pctfree. A less likely possibility is that 9208 shows other untested aspects of “fixed” bugs like 4288876 or whatever. And of course, testing like Steve is doing can be affected by multiple Oracle and app problems.
> Steve needs to try his 16 block test with a more appropriate pctfree
You’re not the boss of me.
😉
> Steve needs to try his 16 block test with a more appropriate pctfree
That’s not right.
When testing the performance of different blocksizes, the only thing that should change is the blocksize . . . .
Despite what the Luddites may say, the performance differences between different blocksizes are well documented, in the Oracle Corporation documentation, on MetaLink and in Rich Niemiec’s bestselling Oracle Press tuning book.
Contention reduction – small rows in a large block perform worse under heavy DML than large rows in a small blocksize.
Faster updates – Heavy insert/update tables can see faster performance when segregated into another blocksize which is mapped to a small data buffer cache. Smaller data buffer caches often see faster throughput performance as dirty blocks are flushed more efficiently.
Reduced Pinging – RAC can perform far faster with smaller blocksizes, reducing cache fusion overhead and greatly improving throughput.
Less RAM waste – Moving random access small row tables to a smaller blocksize (with a corresponding small blocksize buffer) will reduce buffer waste and improve the chance that other data blocks will remain in the cache.
Faster scans – Tables and indexes that require full scans can see faster performance when placed in a large blocksize as long as the I/O does not exceed the capabilities of the disk subsystem.
> pctfree could be wrong for one blocksize and not the other. Steve needs to try his 16 block test with a more appropriate pctfree.
As Jonathan pointed out here, the pctfree isn’t wrong per se. While I agree that it may not be 100% optimal, His test shows the issue as being ASSM’s behavior with larger blocksizes (based on observations, 16k and up).
Steve,
I think it would be more accurate to say that the pctfree is wrong – but that the devastating performance impact is (or seems to be) due to a bug in ASSM when rows migrate. Moreover, the bug may only surface in 16KB (or larger) blocks.
a,
> Jonathan has just demonstrated a possibility on the OTN thread –
> pctfree could be wrong for one blocksize and not the other.
In fact the pctfree was wrong for both block sizes. You can see the error in the 4KB trace file data that Steve posted to the thread. The update took 2.2M current block gets when it should have taken about 830,000. It was this discrepancy that made me ask about indexing and nulls etc.
>> In fact the pctfree was wrong for both block sizes
A fact? Karam never said what his PCTFREE was set to, genius.
>> due to a bug in ASSM when rows migrate.
A 9i bug that’s still in 11g?
Oh brother.
Despite overwelming evidence to the contrary, your grasping at straws reminds me of the old addage when a husband is suspected of cheating:
“Deny till you Die”.
Jonathan:
I was using “wrong” to refer to it not being a noticeable performance problem in the 4K test. But you are right, there are excessive gets. This is a very interesting distinction, when you start thinking about compulsive tuning disorder – most people (I’m guessing) would consider worrying about it in the 4K test to be CTD, and yet here is an example where, if one knew to look, it would be a red flag.
Don:
OK, Steve, what was PCTFREE? For that matter, what about the waits I asked for in a previous post (not that anyone cares, now that we have more relevant things to look at)?
As far as bugs lasting three major versions, it’s happened before. How long was that “oops, you didn’t specify the schema, so update the wrong schema!” bug floating about? Oracle doesn’t fix bugs unless they both know about them and decide they are important enough to fix. They usually don’t “know about them” until someone replicably demonstrates them. I couldn’t guess how they determine the importance, though they say things about customer feedback. I suspect this particular bug is one that no one really noticed in a lot of cases, just like this one. They see something wrong, and no one thinks to look in the right place. I’m not saying Jonathan has nailed it for Steve’s case (is anyone? It’s just a demonstration of how to get similar results, and why it is seen), just that Steve should look and see and let us know.
oraclesponge has an example of an undocumented feature that would be useful for DW that’s been in 4 major releases. Stuff happens.
If Oracle should confirm the 16kb blocksize issue as a bug then that’s going to leave someone with egg on his face, and maybe even an apology to publish. Or a whole bunch of posts to go back and delete.
David Aldridge said… If Oracle should confirm the 16kb blocksize issue…
I’m wondering if bug 6918210 might be a good one to watch – it has the confirmed flag set to “Y” and a Dev priority of “2”. It is 32KB blocksize, but involves ASSM and row migration.
– Mark
Donald K. Burleson said…
June 15th, 2008 at 3:02 pm
You can’t flush the buffer in 9i, and it’s a production instance . . . .
Why not? Do you mean you shouldn’t flush the buffer cache because it is a production database or there is a technical limitation that prevents one from doing so in 9i?
– Mark
Wow, that bug really does look spot on. It specifically relates to updates resulting in row migration on a 32kb blocksize taking 20 hours instead of minutes on 8kb.
Verrrrry interesting … 😉
Hi David,
>> If Oracle should confirm the 16kb blocksize issue as a bug then that’s going to leave someone with egg on his face,
Huh? Oracle has bugs, it has always had bugs.
I think you miss-out on the cause-effect of this testing! If the changed in blocksize derailed a bug (who knows?), it DOES NOT diminish the fact that it was the db_block_size change that CAUSED the performance boost.
>> maybe even an apology to publish.
There is nothing to apologize for David. I don’t care if the change in blocksize caused a performance boost because of the cycles of the moon, so long as it’s repeatable. As a warehouse person, you should know this. The “root cause” of any correlation may be interesting, but it’s incidental. What counts is the predictive validity of the correlation, like beer and diapers:
http://www.dba-oracle.com/oracle_tips_beer_diapers_data_warehouse.htm
Are there intervening mechanisms when you change an Oracle parm? You bet! But that does not make what Steve did any less amazing. Taking a job from 45 minutes to 10 seconds is a remarkable achievement.
Tell me, David, about the “scientific” method that you used when you chose to use 16k blocks on your multi-billion dollar database.
David, on the OTN thread you made a comment acknowledging the issue of locking and throughout performance with larger blocksizes during heavy DML loads. Can you elaborate?
Finally, please explain why it’s customary for you guys to treat everyone who disagrees with you with disrespect, mocking and unprofessional comments?
It’s also weird how emotionally invested some people are about these purely technical issues, and how some sadists seem to get huge pleasure from unfairly denigrating people (so long as it’s done from a safe distance, of course, because, at their core, these people are cowards).
Instead of treating Karam and Prusinski with the respect that you would like to be treated, you called them inept, liars and twisted the facts in a bizarre attempt to discredit their honesty and integrity. That’s why some of the woodies get no respect. They don’t give anyone else respect!
Hi Mark,
>> Do you mean you shouldn’t flush the buffer cache because it is a production database or there is a technical limitation that prevents one from doing so in 9i?
Both actually. Am I mistaken that the “ALTER SYSTEM FLUSH BUFFER_CACHE;” syntax came out in 10g?
When it comes to testing performance, you must model the actual production environment as closely as possible, but pragmatic realities also come into play. In this case, the client has found Steve to do an excelent job without having a full-sized test environment.
David,
On OTN you asked about how to conduct workload-based testing prior to the 11g SPA.
As you might imagine, justifying a production change with a single-user contrived test case is suicide, and like many DBA’s, I’ve developed tools to capture and replay multiple transactions to accurately simulate a real production workload. It’s not hard.
Others use tools like the Quest Benchmark Factory to produce statistically significant performance tests. If you like, I’ll be glad to mail you a copy of Dr. Scalzo’s book “Database Benchmarking”.
http://www.rampant-books.com/book_2007_1_database_benchmarking.htm
IMHO, only a fool would discard a real-world benchmark to use an artificial test case, it’s malfeasance at the highest level.
Artificial test cases are ALWAYS non-generalizable, and non-reproducible, even in the same environment! Even a through bench is not perfect, but unlike a test case, the predictive value of a benchmark can be scientifically ascertained, and management can make a decision based upon the real probability of success.
>> maybe even an apology to publish.
I think that there are several people here who have treated Mr. Karam rudely and unprofessionally. For those of you, there is hope of redemption. It’s not easy learning science without real-world work experience:
http://www.dba-oracle.com/t_visual_guide_oracle_concepts.htm
Repent and you shall be forgiven, just fill-in this form and e-mail it to Steve:
http://www.dba-oracle.com/images/apology_form.jpg
Donald K. Burleson Says:
June 18th, 2008 at 8:18 am
Both actually. Am I mistaken that the “ALTER SYSTEM FLUSH BUFFER_CACHE;†syntax came out in 10g?
alter session set events ‘immediate trace name flush_cache’;
This works perfectly fine in 9i.
– Mark
Steve-
Could you confirm the presence of a bug or not? There are a couple options here. You could try:
1) using a MSSM tablespace in your 16k block size db
2) if using a 16k block ASSM tablespace: set event 10320 level 10 and monitoring the trace file for “kdt_bseg_srch:sbk: failed dba=…” (this trace is verbose so either have space or only run it until you have confirmation of the mentioned string).
If #1 produces near 4k block execution times and #2 is true, then I think the responsible thing to do is to have Donald K. Burleson retract his 20x posting and update yours accordingly. It would seem to me to be irresponsible and unprofessional to be promoting something that resulted because of a bug.
Anonymous,
1) I cannot reproduce the test in this way (at this point anyways), the client has moved forward with their own work
2) I would definitely agree that the problem Jonathan described on OTN sounds suspiciously like what I noticed in this example. The blocksize was 16k, ASSM was on, the rows were small, and while I can’t go back and find the PCTFREE (sorry a@b.c) I’m pretty sure it was low, so if this bug does apply to 16k blocks as well it’s a close match.
3) I am not promoting anything, first of all. Second of all, if it is this bug, then it is persistent across 9i, 10g, and 11g. Are they going to backport a fix for all three versions? How long will it be? Until they do (and even if they do), then moving to a smaller blocksize does remain a possible workaround (as does setting a proper PCTFREE or using MSSM).
4) The responsible thing to do is to post with your real name, not as “Anonymous.” Everyone else is (except ‘a‘), join the party.
Hi Steve,
I see that folks on OTN are still are having trouble understanding predictive analytics. Some fellow named Foote wrote this:
“It’s what differentiates a good Doctor from a bad Doctor, a Dr who knows “why” that medicine will fix that illness.”
That’s an odd comment since physicians don’t know how many of the drugs on the market today work!
For example, the drug Bupropion was originally designed as a anti-depressant, and they discovered a statistically significant correlation between people taking it, and people who quit smoking.
Anyway, root cause is not required in science, at least as far as the FDA is concerned.
Maybe it’s different in Austria, who knows?
[begin quote]
Some fellow named Foote wrote this:
…
Maybe it’s different in Austria, who knows?
[end quote]
hehe, that reminds me of a dialog from the movie “Dumb and Dumber”…
Jim Carrey’s Character: That’s a very nice accent you have. New Jersey?
Female Character: Austria!
Jim Carrey’s Character: G’day mate! Let’s put a shrimp on the barbie!
– Mark
Don,
Given an infinite amount of hardware, monkeys and time, every company has the resources to go blundering around in the dark testing their real-world loads in the hope of stumbling on the Magic Block Size, or combination of Magic Block Sizes and segment placements, that will make their systems run faster. Such companies have no interest in the root cause of the effect that Steve has detected.
However, those of us with finite resources are very interested in knowing the root cause because knowing that it is bug-related allows us to do the following:
i) Test for symptoms that match those expected when encountering the bug.
ii) Testing for workarounds that do not require an outage while they move to a completely new database with a different block size (like Steve suggests, maybe modifying PCTFREE).
iii) Pushing for high priority fixes and backports for the bug with Oracle.
iv) Applying a patch.
Sadly this focused approach leads to reduced peanut rations for the monkeys, who will have to find a less rational area in which to ply their trade. You, Don, have to decide whose side you’re on: the companies’ or the monkeys’?
> (sorry a@b.c)
No worries! I appreciate that you post as much as you do. A lesson to be learned here, need to record details no matter how small, you never know when you need them.
>join the party
Actually, I also feel posting with real names means more, but with Don involved, I was too intimidated. Appreciate you allowing it.
Hi David,
>> very interested in knowing the root cause because knowing that it is bug-related
A three release bug?
Nah, if it hasn’t been identified nor fixed in five years,, it’s a feature, not a bug. . . .
What’s it been since 9i, almost a decade?
For this client, there is a ZERO chance that Oracle would backport a bug fix to 9i.
********************************************************************************
>> combination of Magic Block Sizes and segment placements, that will make their systems run faster.
Yeah, 45 minutes , tuned down to 10 seconds . . . .
>> “Magic Block Sizes ”
No, I call that a “Silver Bullet”. The client is beyond thrilled . . .
*******************************************************************************
>> those of us with finite resources are very interested in knowing the root cause
Like Steve mentioned with this client, (without infinite resources), they don’t much care. I have NEVER, EVER has a boss who wanted to pay a DBA to investigate wether a huge performance boost.
*******************************************************************************
>> because knowing that it is bug-related allows us to do the following
Which is to help Oracle Corporation de-bug their software, right?
Most opf my clients are will willing to pay for this, especially after shelling out tens of thousands of dollars for support each year!
******************************************************************************
>> trade. You, Don, have to decide whose side you’re on: the companies’ or the monkeys’
No, David, you don’t get the support relationship.
I serve my clients, and I don’t make these decisions.
To an Oracle customer, there is ZERO benefit to these activities, in fact, it can be nagative, especially if a bug is discovered.
Believe me, I WOULD LOVE to be paid to investgate root causes.
Don,
For an 8CPU 2+2 RAID10 (data) system we naturally have much more latitude in moving to a new database — with small systems much options are often available. Wouldn’t you interested in presenting options to a client with a mission critical 4TB high availability system though? An option of just increasing PCTFREE, or migrating the affected tables to an MSSM tablespace?
David,
Not to start the debate again, but you DID forget “move the table to a tablespace with a blocksize not affected by this issue.”
As one of the consultants that worked on this issue, I will say that I did give them options. I actually recommended that they grant us time to figure out why the blocksize change had such a huge impact. Because of their deadline, they opted to take what they could get.
I am glad that my observation led to such a great discovery by Jonathon, that’s why I love the idea of sharing all observations no matter how much they buck the conventional wisdom. My client has already moved on at this point, but I always give my client as many options as I can; and if none exists, I recommend research. It is up to a client to take that or the ‘quick fix.’ usually they choose the latter.
I’ve been watching this thread with interest and learned quite a bit – and this angle of it that Steve identified is particularly interesting. I think the thread degenerated at times (as it often does – and even from some individuals that usually don’t do so…) I see a few things here:
– Steve, I think you’re doing the right thing here by laying out what you saw, but acknowldging the likelihood of the explanantion that Jonathan offered.
– It doesn’t sound like Steve did anything wrong. When faced with a problem that is solved accidentally, sometimes the customer’s decision is to simply move on rather than investigate further. (And sometimes that decision is even the correct one!)
– Don – I do think you’re wrong to continue to hold this up as a justification for changing the block size. It doesn’t mean Steve was wrong to do what he did – it just means that in light of more recent facts, it simply doesn’t stick to the wall to hold this one up as an example of your claim. In fact, I suspect that if Steve were to walk into a similar situation now, he would try some of the other techniques first (the ones that don’t require rebuilding the whole database…) If you’re going to use something as evidence, at least make it accurate and related. Given that you have a set of “personal debunkers” as you call them, your “cause” would be better served to offer no evidence at all, just citing your own experienced based opinion and leave it at that, than to offer evidence that is so clearly flawed.
Steve,
Yes, another option that’s much easier than moving the whole database, though it introduces the complexity of managing multiple block sizes — options are always good, and I think we both understand that knowledge of the root cause in this case expands the range of options available. I guess that the critical factor for the client though was that moving the complete system to a new database block size was a very feasible option. The level of client interest in root causes and alternative remediations is inversely proportional to the difficulty of implementing whatever remediation is easily at hand.
Nothing wrong with that. It makes perfect sense for a company in that situation for whom moving to a new database is a quick solution. I don’t understand Don’s position though, when his other clients cannot be obliging enough to work only with small systems. Well, that’s his business.
>> Given that you have a set of ‘personal debunkers’ as you call them, your ’cause’ would be better served to offer no evidence at all,
Yes, that’s a good idea, for me and all of my consultants. Nothing good comes from sharing details from real-world databases, especially in today’s competitive consulting environment.
I like to share what I’ve seen, but not in the face of these nasty, rude “Oracle Scientists” who make ridiculous accusations of ineptitude against non-anonymous experts like Steve all the while refusing to share their own experience and credentials.
The most telling part of this whole exercise is the official acknowledgement from Oracle that Oracle does not fully test their software for performance, and they only test with an 8k blocksize. I’ve notified many of my clients about this and they are outraged at this lack of fundamental QA testing.
Never mind that Oracle’s own TPC performance benchmarks use multiple blocksizes, and that I have many dozens of database who have experienced spectacular success with non-standard blocksizes.
As has been pointed out to me, it’s in my own best interest for the “Oracle Scientists” to keep this single blocksize myth alive.
It generates more business for us . . . .
>> … they only test with an 8k blocksize. I’ve notified many of my clients about this and they are outraged at this lack of fundamental QA testing.
I hope that they wouldn’t be outraged to find that Oracle doesn’t test all the possibilities of multi-blocksize databases.
2kb+4kb, 2kb+8kb, 2kb+16kb, 2kb+32kb, 4kb+8kb, 4kb+16kb, 4kb+32kb, 8kb+16kb, 8kb+32kb, 16kb+32kb … then all of the permutations with three different sizes … etc.
When you start using non-mainstream methods like multiple block sizes you are almost guaranteed to be in untested territory other than the most basic “is it functional” testing. Something your clients ought to bear in mind when deciding whether to go that route, I’d say. There’s nothing about the use of a feature in TPC tests that means, “ready for production”.
Personaly, I’m thinking of swearing off on non-8kb block sizes. I’ve had to deal with multiple block sizes on data warehouses where tablespaces were transported from OLTP systems, and it’s a PITA. Data warehouses are often pushing the boundaries of functional combinations that may not have been fully tested, and I don’t feel that I should be adding to the risks. And at least now I know that if I choose 8kb then that gives me the best shot at avoiding space management bugs.
Donald K. Burleson Says:
June 20th, 2008 at 12:40 pm
>> …the official acknowledgment from Greg Rahn of the Real-World Performance Group that Oracle does not fully test their software for performance, and they only test with an 8k blocksize.
>> I’ve notified many of my clients about this and they are outraged at this lack of fundamental QA testing.
Would you mind citing this reference? It does not appear to be a quotation, so I’d like to see exactly what Greg said.
I find it impossible to believe that they only test with 8k. I do believe that it is probable that they do not test every possible combination (as David mentions). To put it in perspective: Does Microsoft test every combination of hardware components with Windows? Of course not. It is just not practical.
From reading the OTN thread, I do see that Greg mentions “all our internal applications use an 8k block”.[1] I take this to mean that all the databases that Oracle runs to support its business (a.k.a. “production databases”) use an 8k block. Now that, I would completely believe.
And why you would go to some of your clients and spread such negativity is beyond me, but certainly speaks of your character. I would have thought that you would (like David) be recommending an 8k block since it would be well tested and Oracle’s production databases use it exclusively, so it can be all that bad, right?
[1] http://forums.oracle.com/forums/message.jspa?messageID=2583270#2583270
b.w. I would have thought that you would (like David) be recommending an 8k block
As one of the consultants that works with Burleson Consulting, I can say that for our new database projects/migrations I almost always work with an 8k database blocksize unless the client is currently on a different blocksize (we don’t want to change variables). However, I will not rule out blocksize changes if there seems to be a just cause for it (or if the client demands it).
>> Would you mind citing this reference?
http://forums.oracle.com/forums/thread.jspa?messageID=2595805&tstart=0
After 30 pages of comments, it’s hard to find things!
It was not Greg, it was Oracle ACE Diretor Daniel Morgan, quoting Brynn Llewellyn:
“Which brings us full circle to the statement Brynn made to me and that I have repeated several times in this thread. Oracle only tests 8K blocks.”
http://forums.oracle.com/forums/thread.jspa?messageID=2445936&
“The right size is 8K because that is the only size Oracle tests.”
“Oracle only tests 8K blocks”
>> And why you would go to some of your clients and spread such negativity is beyond me, but certainly speaks of your character.
Thank you. We pride ourselves in finding the truth and telling it like it is. When customers pay hundreds of thousands of dollars for an EE license, they expect that it’s been properly tested, and so do I.
However, in this case, we must remember that the cited person was not Greg Rahn (my bad), but Oracle ACE Director Daniel Morgan, the same fellow who uses his elected position at PSOUG to line his pockets with cash.
Until I confirm this, I’m going to call these clients and tell them that because this quoute from Brynn came from an Oracle ACE direstor, it could very well be a complete fabrication.
On the other had, how could Oracle miss something as obvious as this 16k mess? Even fiundamental testing would have found it, almost 10 years ago.
Hi David,
>> I hope that they wouldn’t be outraged to find that Oracle doesn’t test all the possibilities of multi-blocksize databases.
Yes, I would.
With Oracle’s massive data centers, they could (and should) conduct thousands of tests, all fully automated. DB2 does at Fishkill, I know this . . .
*********************************************************************************************
>> There’s nothing about the use of a feature in TPC tests that means, “ready for productionâ€.
No, I disagree. Oracle Corporation (and the hardware vendor) spends a huge amount of time testing the TPC benchmarks for optimal performance (the braging rights are worth millions of dollars in server sales). I have no doubt that they deployed multiple blocksizes because they performed faster, not just on a whim! When I;m tuning a new server, TPC is the first place I go. It’s a goldmine of hidden kernel parms, tips and tricks for seqeezing out optimal performance. Achieveling optimal performance is a proper goal, and to do otherwise is misfeasence, IMHO.
************************************************************************************************
>> When you start using non-mainstream methods like multiple block sizes
You mean like you did, when you did your own tests and chose 16k blocks for the mission-critical Sirius data warehouse?
************************************************************************************************
>> Data warehouses are often pushing the boundaries of functional combinations
What boundaries? There is nothing wrong with using any features that Oracle provides (unless you are asserting that Oracle does not test properly).
C’mon, Oracle would not release 32k blocksizes without at least some fundamantal testing. . .
BTW, Just because they are new to you does not mean that they are new! Large corporations with mainframes running non-relational databases have been using multiple blocksizes since the 1980’s, with proven success. . . . .
After 20 years of success, the benefits of multiple blocksizes are well proven. Check out this IBM benchmatk with Oracle:
http://www.dba-oracle.com/t_tpc_ibm_oracle_benchmark_terabyte.htm
db_cache_size = 176000M
db_2k_cache_size = 2048M
db_16k_cache_size = 99000M
db_keep_cache_size = 600000M
db_recycle_cache_size = 64000M
This Oracle Corporation benchmark is 100% reproduceable and repeatible. If you think that it would run faster without the multiple blocksizes, all you need to do is re-run the tests! That’s why TPC is so great, you can see for yourself!
Hi David,
>> I’m wondering if bug 6918210 might be a good one to watch – it has the confirmed flag set to “Y†and a Dev priority of “2″. It is 32KB blocksize, but involves ASSM and row migration.
Interesting point. It makes me wonder if the real bug is inside ASSM. We have had to turn it off for clients with high DML loads, and I’ve noted several “features” of ASSM:
– ASSM can be slow for full-table scans: Several studies have shown that large-table full-table scans (FTS) will run longer with ASSM than standard bitmaps. ASSM FTS tablespaces are consistently slower than freelist FTS operations. This implies that ASSM may not be appropriate for decision support systems and warehouse applications unless partitioning is used with Oracle Parallel Query.
– ASSM is slower for high-volume concurrent inserts: Numerous experts have conducted studies that show that tables with high volume bulk loads perform faster with traditional multiple freelists.
– ASSM will influence index clustering: For row ordered tables, ASSM can adversely affect the clustering_factor for indexes. Bitmap freelists are less likely to place adjacent tows on physically adjacent data blocks, and this can lower the clustering_factor and the cost-based optimizer’s propensity to favor an index range scan.
I’m a bit confused by the sequence of events… apologies for formatting here if it gets munged.
****************
Jonathan Lewis:
…but that the devastating performance impact is (or seems to be) due to a bug in ASSM when rows migrate.
****************
Donald K. Burleson:
>> due to a bug in ASSM when rows migrate.
A 9i bug that’s still in 11g?
Oh brother.
Despite overwelming evidence to the contrary, your grasping at straws reminds me of the old addage when a husband is suspected of cheating…
****************
Mark A. Williams:
I’m wondering if bug 6918210 might be a good one to watch – it has the confirmed flag set to “Y†and a Dev priority of “2″. It is 32KB blocksize, but involves ASSM and row migration.
****************
Donald K. Burleson:
Interesting point. It makes me wonder if the real bug is inside ASSM.
****************
So, Jonathan says the impact seems to be due to an ASSM bug and row migration. Don responds with “Oh brother” and “Despite overwelming evidence to the contrary…”. I then cite a bug (6918210) which looks to be related and has the confirmed flag set to “Y”. Don then responds (to David Aldridge) with “Interesting point. It makes me wonder if the real bug is inside ASSM.”
Did I misread this? How did things go from “Oh brother” and “Despite overwelming evidence to the contrary…” to “It makes me wonder if the real bug is inside ASSM”?
Also, and I know the reference is to original statements by Daniel Morgan, but “Brynn” spells his name “Bryn”. A minor point I realize.
– Mark
Don,
You’d better decide whether this is a bug or not … you started with, “A 9i bug that’s still in 11g? … Oh brother. “, then “Huh? Oracle has bugs, it has always had bugs.”, now it’s “Interesting point. It makes me wonder if the real bug is inside ASSM”, the latter of which I was Jonathan’s orginial point. It’s good to see you coming around about the importance of root causes 😀
>> >> I hope that they wouldn’t be outraged to find that Oracle doesn’t test all the possibilities of multi-blocksize databases.
>> Yes, I would.
Of course you would, because now whenever you recommend using multiple block sizes for performance to a client you have to add the clause “although I know for sure that this is an untested configuration except for the purpose of an artificial pure-performance benchmark, and you may therefore uncover bugs that have lain undetected across three major releases”. In fact you ought now to go and add that clause to all of the articles you’ve written recommending it, shouldn’t you?
>> >> When you start using non-mainstream methods like multiple block sizes
>> You mean like you did, when you did your own tests and chose 16k blocks for the mission-critical Sirius data warehouse?
Sorry Don, three mistakes in one sentence … “multiple” means “more than one”… I don’t work for Sirius … I didn’t say anything about a mission critical data warehouse.
I don’t think you can impugn my reputation Don, not with all of those glowing references to me on your websites .. “Oracle expert “, “Oracle guru”, “…a well-respected data warehouse consultant”, “Respected Oracle expert …”, “Oracle data warehouse guru …”
😀
Hi David,
>> you have to add the clause “although I know for sure that this is an untested configuration except for the purpose of an artificial pure-performance benchmark, and you may therefore uncover bugs that have lain undetected across three major releasesâ€.
No, not atall. Just like Steve, we throughly test using real-world workloads under representative conditions. Why guess?
****************************************************************************************************
>> In fact you ought now to go and add that clause to all of the articles you’ve written recommending it, shouldn’t you?
No, because Steve’s case is just one of at least a half-dozen permutations of huge performance differences between blocksizes.
****************************************************************************************
>> It makes me wonder if the real bug is inside ASSMâ€, the latter of which I was Jonathan’s orginial point.
I think you misunderstand. Lewis pointed to an ASSM & 16k blocksize “bug” which may or may not be fixed, ever (at least after more than 5 years).
The ASSM issues that I’ve noticed are not exclusive to 16k blocksizes, that was my point.
***************************************************************************************************
>> I don’t work for Sirius
XM/Sirius, you published that they are merging. You wrote in March 2008 “So it turns out that the proposed merger between XM Satellite Radio (my employer) and Sirius Satellite Radio was just approved by the Department of Justice. ”
http://oraclesponge.wordpress.com/2008/03/24/big-news-round-here-anyway/
Why do you feel it necessary to split hairs like this?
It’s distracting, rude and unprofessional.
***************************************************************************************************
>> I didn’t say anything about a mission critical data warehouse.
As I recall, you said that you were a now a DBA manager, driving a team doing a mission critical warehouse for a major satellite radio company. . . . .
But you did state that you chose a 16k blocksize for your warehouse, correct?
********************************************************************************************
>> I don’t think you can impugn my reputation Don, not with all of those glowing references to me on your websites
David, I never intended to, but being an expert and being a nice person are two different things. You seemed like a nice fellow when Janet and I met you, but I guess you never know . . . .
Since you want to be deliberately obtuse and nitpicky, I guess I have to go back and point out what you said on the OTN thread:
*******************************************************************************************
> David, do you agree that, all else being equal, small
> rows in a large blocksize can perform worse than
> large rows in a small blocksize under heavy DML
> load?
>
David wrote: “Of course they can — and the reverse is equally true.”
Yup, we agree again. . . .
*******************************************************************************************
David wrote: “That’s why there’s no “one-size-fits-all” solution.”
Sere, that sounds like you agree with me, a one-sized-fits-all blocksize is not recommended!
> Does your production warehouse use an 8k blocksize?
> Be Sirius now!
David wrote: “It uses 16kb.”
And there we see it. You are not going to make me believe that you chose a 16k blocksize for an important warehouse without first testing the performance.
No way.
Hi Mark,
>> Did I misread this?
Yes, you did. Lewis struggled to fine something, anything, to discredit Steve Karam’s amazing work. he’s just a mean-spirited, negative person.
That sorta-kinda bug is exclusive to using 16k blocks and ASSM, and it has existed for over half a decade. And Oracle wonders why nobody wants to report bugs. Oracle takes years to fix them, and even when they do, they will not backport them to your release. . . . .
My experiences are that ASSM falls apart under heavly DML load, regardless of blocksize, and it has other flakey issues, TOTALLY UNRELATED 16k blocksizes:
http://www.dba-oracle.com/oracle10g_tuning/t_assm_performance.htm
Don,
I explained the reason why I chose 16kb, I believe, and it was mostly to do with efficiency in packing long rows data into blocks. I also believe that I have explained that with direct path reads and writes, which tend to dominate large data warehouses, block size is much less important as a performance consideration. You may remember that that was the reason why I was previoulsy sceptical of an announcement you made that Mike Ault was soon to produce a benchmark showing enormous performance improvements when temp tablesaces were on very large block sizes quite some time ago.
You might ask why I don’t use a 32kb block size instead of 16kb,and that would be a good question — it’s because I have previously encountered bugs on bitmap index corruption on a 32kb data segment blocksize that made me cautious of the risks of using extreme values for limited benefits. Now I think that I’m edging towards preferring 8kb instead of 16kb because it appears that it is more thoroughly tested, and it also makes data warehouse more compatible with OLTP systems for the purpose of transporting tablespaces (I do like that technique — opening the same readonly data files across multiple databases is very useful).
So you see Don, when new information becomes available I feel very free to reconsider my position and modify my advice, even if it directly conflicts with my previously held beliefs. I don’t go trying to impugn the character of the messenger or imply that people may be fabricating quotes, for example.
Don, I’m sorry to see you getting all bent out of shape and in pubic disagreement yet again with just about every Oracle professional who has no commercial links with you. I don’t believe it is at all a personal thing — people honestly believe that you are incorrect, and if your philosophy is that you need produce nothing other than anecdotal evidence then that’s just your own business. We have no common ground to work with here.
>> I think that I’m edging towards preferring 8kb instead of 16kb because it appears that it is more thoroughly tested
Yes, that was a revelation alright! But I can’t imaging telling anyone not to follow Oracle’s own blocksize recommendations because they did not do proper testing.
But what about LOB segments? It would be a nasty trade-off between huge fragmentation and the “safe” blocksize.
************************************************************************************
>> with just about every Oracle professional who has no commercial links with you.
EVERY ORACLE PROFESSIONAL? Are you serious, David? First, I recognize that this “scientist clique” is just a highly-vocal minority, and not, as you claim “every Oracle professional”. That’s not only a gross exaggeration, it’s ridiculous! 99.9% of working professionals don’t have the free time to respond to these anonymous “expertsâ€., and I don’t blame them!
Look what happened to Steve.
All he did was share what he did, and these semi-anonymous “Oracle Scientists†insinuated that he was either inept or a liar. This is very rude, very unprofessional, and ensures that nobody shares there experiences with these mean and nasty people.
*****************************************************************************************
>> if your philosophy is that you need produce nothing other than anecdotal evidence then that’s just your own business. We have no common ground to work with here.
No, we have common ground, now that you answered my original question!
You FINALLY shared your decision process on blocksizes, which is what I asked you about last week, on one of the early pages of the thread. Instead of a direct answer, you used subterfuge, which I thought was very unlike you.
Whatever, thanks for finally sharing your experiences.
I did not ask you for any evidence, just what you have seen, there is nothing wrong with that, and I was surprised by your uncooperative and defensive posture. . . .
I only wanted to know what you thought and why you chose the blocksize that you did . .
>> EVERY ORACLE PROFESSIONAL? Are you serious, David?
“… just about every …” … I don’t know that Steve had a particularly bad experience though. He found a very interesting effect, and everyone understands commercial pressures to just get things done.
>> You FINALLY shared your decision process on blocksizes, which is what I asked you about last week, on one of the early pages of the thread. Instead of a direct answer, you used subterfuge, which I thought was very unlike you.
http://forums.oracle.com/forums/thread.jspa?messageID=2576827�
… where I said, “I’ve not noted very big differences, if any. I generally use large block sizes to reduce space wastage when dealing with very long average row lengths (more of a data mart thing than a data warehouse thing), and since you never know whether someone is going to come along with a requirement in the future that will lead to a very long average row length then I’ll start off with a high block size initially.”
Where’s the subterfuge?
>> All [Steve] did was share what he did, and these semi-anonymous “Oracle Scientists†insinuated that he was either inept or a liar. This is very rude, very unprofessional, and ensures that nobody shares there experiences with these mean and nasty people.
This is such an over exaggeration. Why must everything be filled with so much drama, Don? In the thread, Jonathan offers the possibility that Steve may have inadvertently made a mistake, which is no way calling him inept or a liar. I think he was merely suggesting that “to err is human.” If there was any ambiguity, Jonathan further clarifies his comment by saying, “One silly mistake doesn’t make you incompetent.” It seems perfectly reasonable to suggest that a 20x improvement by “only†changing the block size, is due to more than just the block size alone changing (as Steve suggests: ‘results may not be typical’) – something else has drastically changed and needs to be understood.
I do believe that people are very interested in the “why†of this 20x claim and it probably did not help matters at all that you, Don, posted your own shameless promotion of this on your website, especially before there was any explanation. To Steve’s credit, he wrote “more thorough analysis will help get to the core of why it had such an effectâ€. Now it was Jonathan Lewis who performed more thorough analysis and discovered what looked to be a bug, and Mark A. Williams who found what looks to be the match for the bug. And now, it all makes sense: The performance of the 16k block was 20x worse that it should have been, due to a bug, and changing to the 4k block avoided hitting the bug. Now all the world is a bit smarter because the “why†is understood. I think this is a perfect demonstration of why it is so important to share details and have an explanation of an observation. Without details and an explanation, it’s all about as useful as an ashtray on a motorbike.
Hello anonymous troll:
>> This is such an over exaggeration. Why must everything be filled with so much drama, Don? In the thread, Jonathan offers the possibility that Steve may have inadvertently made a mistake, which is no way calling him inept or a liar.
No, the words “inept and liar” were Steve’s words about Lewis, not mine:
“Before you were talking anomalies, now I’m either a liar or inept?”
http://forums.oracle.com/forums/thread.jspa?threadID=663687&tstart=0&messageID=2592009
*********************************************************************
>> Now it was Jonathan Lewis who performed more thorough analysis
Right. Using nothing but a vague description of the issue, Lewis declares that Karam used the “wrong pctfree”.
What a Yutz.
Hi David,
>> Where’s the subterfuge?
– Why did your tests lead your company to choose a 16k blocksize?
– If you are “profoundly against non-standard blocksizes”, why did you choose one for the XM Satelitte data warehouse? You mentioned somethoing about saving disk, can you elaborate, please?
– Both 16k and 32k are non-standard blocksizes, why do you think that 32k is buggier?
Don,
I understand where you’re going with this — if I now believe that 8kb block sizes are safer because they are more thoroughly tested, why did I not previously use that knowledge to use only 8kb block sizes. Hence all the hinting about “why are you doing something that you believe to be risky on your MISSION CRITICAL!!! XM SATELLITE RADIO!!!!!!!!! data warehouses?!?!?!?”
The answer is simple, Don — it’s because time is linear, and learning something at time X doesn’t allow you to go back and use that knowledge prior to that time. One can’t just go back and change decisions as easily as one could (hypothetically) go back and delete forum postings, or edit technical articles, to try and hide errors that one might be ashamed of having made in public.
Anyhoo …
>> Why did your tests lead your company to choose a 16k blocksize?
There is often a modest improvement in the amount of data that can be fitted in them, in particular with very long rows. I’ve built fact tables with average row lengths in excess of 1000 bytes (with no LONG’s or LOBs etc) to support rapid analysis of denormailsed data sets (general ledger data, for example), and you never know when you’re going to be asked to do so again. Also, with a row-ordered direct path load there is high likelihood that an adjacent block would have to be read anyway, and that compression ratios will be improved.
>> If you are “profoundly against non-standard blocksizesâ€, why did you choose one for the XM Satelitte data warehouse? You mentioned somethoing about saving disk, can you elaborate, please?
http://forums.oracle.com/forums/click.jspa?searchID=10397116&messageID=2575729
I believe I said that I profoundly disagree with your advice to use multiple block sizes.
See above for an explanation of the disk saving — much more so with very long column lengths, of course.
>> – Both 16k and 32k are non-standard blocksizes, why do you think that 32k is buggier?
As I said above, “.. I have previously encountered bugs on bitmap index corruption on a 32kb data segment blocksize that made me cautious of the risks of using extreme values for limited benefits”. It was a 32kb bug, not a 16kb bug.
>> .. these semi-anonymous “Oracle Scientists†insinuated that [Steve] was either inept or a liar. This is very rude, very unprofessional,
>> No, the words “inept and liar†were Steve’s words about Lewis, not mine
So you’re saying that Steve is very rude and very unprofessional? Got to disagree with you there again, Don. He seems pretty decent to me.
😉
Hi David,
>> Hence all the hinting about “why are you doing something that you believe to be risky on your MISSION CRITICAL!!! XM SATELLITE RADIO!!!!!!!!! data warehouses?!?!?!?â€
I was just trying to make my point, not to bash you, and I’m sorry if appeared to be an attack.
Besides me and Steve, you were the only other expert with verifiable experience and credentials, and I wanted to know your thoughts, the voice of experience.
As you know, I value real-world experiences far more then test cases, and I was frustrated that you were not willing to share your experiences in the OTN thread.
BTW, David, I’ve noticed that your demeanor changes radically, depending on who you are speaking with . . .
And I can understand that. In the “my-way-or-hit-the-highway†environment of those “scientists”, speaking solely from your experiences without producing reams of evidence would result in flaming. It’s a shame that they are so intolerant . . . .
******************************************************************************************************
>> I profoundly disagree with your advice to use multiple block sizes.
Yes, but the thread was about non-standard blocksizes, not multiple blocksizes, that’s where the wires got crossed.
Fair enough. BTW, did you see this?
http://forums.oracle.com/forums/thread.jspa?threadID=664435&tstart=0
To be more specific, David, are you against using “non 8k blocksizes”?
Assuming that Oracle fixes the 16k and 32k bugs, would your opinion change?
******************************************************************************************************
>> Also, with a row-ordered direct path load there is high likelihood that an adjacent block would have to be read anyway, and that compression ratios will be improved.
Interesting. I wonder if blocksize be less important in row-ordered tables like sorted hash clusters?
******************************************************************************************************
>> and learning something at time X doesn’t allow you to go back and use that knowledge prior to that time.
So, would you do it different today?
*******************************************************************************************************
>> Don. He seems pretty decent to me.
You just LOVE playing games with words, don’t you?
I’ve seen examples of this in books, and they call it “droll humourâ€.
First off, I like to thank you for the experience. Watching the best of the best go at it. I’ve read postings, tips, and even bought your books and you guys are the elite. I’ve learned that from this point on, my dbs will be 8k block size. Thanks again
Hi burleson,
Reading your comments, I doubt who is unprofessional!
What is wrong in proving things with test cases and it’s not fair to laugh at those who prove them beyond “words” and links to your site.
Infact when many brains come together a healthy discussion will come-up but when comebody come up with tested (with real results), why to get uneasy.
Steve, forgive me for off-topic though. It is nice to have these kind of posts. It do inspire me to test and research things again and again. If you are a senior DBA / consultant, it doesnt mean that “researching” is a sin and “time doesnt allow” :). And there is no hard and fast rule that the research should be done only in Multi Million Dollar Production. If you can replicate an enviornment and test cases, ” a database is a database”.
Thanks
-Sethu
Hi Sethu,
>> What is wrong in proving things with test cases
Because it’s impossibe.
You cannot prove anything about Oracle performance with a test case.
They are `100% invalid, they will not generalize, and the results are not reproduceable, even on the same hardware!
The only way to approach performance testing is with a full multiuser TPC benchmark, and even those have limited value.
Re: alter system flush buffer_cache;
This was accidentally done on a production 3-node RAC system (10.2.0.3 on Linux 86-64) and the results were that the database hung – so long that we had to bounce it.
Obviously, it was an error to flush, but I was surprised at the hanging: would it be that the cache fusion was wrecked? Might Oracle have eventually recovered? Any suggestions welcome.
>> alter system flush buffer_cache was was accidentally done on a production 3-node RAC system
Wow. I would guess that all buffers on that node was marked as “invalid” and the “hang” was the cache fusion layer upadting the buffer sharing information.
>> Might Oracle have eventually recovered?
Sure, eventually. . . .
>> Any suggestions welcome.
Don’t do that, especially on a production server.
@Don:
Thanks! Pretty much as I thought – I guess it’s a push too far to do that sort of thing and expect Oracle to magically “recover its composure” in a few minutes!
Very interesting discussion indeed. I’ve been warning against using ASSM for years: the blessed thing is so untested and buggy it begs disbelief!
Someone mentioned LOBs in that context: yeah, try to use LOBs in ASSM tablespaces and no matter what block size you use, get ready for some fireworks. ALL releases too.
Of course: 12g will fix all that, so will 10.2.0.29…
Sorry folks, but when it comes to QA, Oracle is a disgrace. Well known and it won’t change any time soon until it starts to hit them wherre it hurts.
That’s why many folks get so defensive in their approach to being an Oracle dba: it’s only too easy to try some of the latest features, get royally burned and then have half the world point the finger at you claiming you’re a “bad dba” because you “failed” to test what Oracle doesn’t give a hoot about testing in the first place!
And then they wonder why folks don’t install CPUs, don’t want to migrate their apps to the latest and so on. What the heck for, to buy a bucket load of performance and other bugs? No thanks, I already pay royally to use the product, why should I also pay to test it?
But the show must go on…
@Noons:
I remember as a junior DBA excitedly installing 9i for Windows (initial release)… and then it became clear that Oracle’s testing was, frankly, just useless. Anyone with Windows 9i initial release will see that setting pause on will end your SQL*Plus session! Lovely! I am with Don on this: Oracle’s got the dollars to do real testing, in various set-ups, so they should do so. As for “Oh, how can they possibly do that, what with there being so many variations?” – rubbish. It’s their product, and there is a finite number of platforms and versions to test.
Richard and Noons:
I agree with you for the most part on the testing bit. When you pay $40,000 per CPU for licensing of software, you would hope that comprehensive testing has been performed on every aspect of the system. At the very least, you would hope that the documentation can be considered Law, so you can form your hypotheses and theories based upon something indesputable.
When different variations of database use are not tested thoroughly, then they are passing the testing on to the consumer. Could you imagine if the makers of your vehicle decided not to test your vehicle under every possible driving condition and instead sold it, knowing that some people might suffer calamity because they failed to test properly?
What about drug companies? There have been several drugs released in the US that caused casualties or other issues as a result of imperfect testing. Those companies are severely chastised for their ignorance.
Now some may think these situations are far too extreme; no one dies from Oracle database crashes. I would respond that it is completely possible for people to die, as Oracle powers some extremely critical bits of information worldwide. I would also add that a DBA, trusting in Oracle’s testing, may suggest their company perform something that results in utter mayhem. And then what? An otherwise budding DBA may lose their job, and the future of their career.
Naysayers! Always with their saying of nay!
Okay, maybe it is a bit dramatic, but I still believe that company wishing to excel in any field must do so by proving its stability. To prove that, testing of all eventualities must be accomplished at the source, not at the target.
Hi Noons,
>> the blessed thing is so untested and buggy it begs disbelief!
I don’t use it. There is nothing wrong with multiple freelists . . . .
*****************************************************************************************
>> when it comes to QA, Oracle is a disgrace
Amen! And it;s hard to convince management that the software that they just spent a million dollars on was not properly tested:
Oracle ACE Director Daniel Morgan, says that “The right size is 8K because that is the only size Oracle tests”, a serious allegation, given that the Oracle documentation, Oracle University and MetaLink all recommend non-standard blocksizes under special circumstances.
Hi Steve,
>> no one dies from Oracle database crashes.
We have several clients with life-or-death databases:
http://searchoracle.techtarget.com/tip/0,289483,sid41_gci1141542,00.html
@Donald K. Burleson
>> the [Oracle] software … was not properly tested
I guess that depends on one’s definition of “properly”. There are very few software products that really are 100% tested. Given the number of features and options that are available in a product like Oracle database, it is quite likely they all do not get tested. Obviously when bugs are worked around, (as in this case) and not diagnosed, they do not get resolved as quickly either.
>> [re only 8k block testing]
It is doubtful that only an 8k block is tested, but since 8k is the default I think it probably gets tested more than a non-default value. I would assume that to be the case with every option though: the default value is tested more frequently than non-defaults.
>> given that the Oracle documentation, Oracle University and MetaLink all recommend non-standard blocksizes under special circumstances
That is a bold statement to make with out any references. I believe you may be reading too much into the Oracle documentation, etc. I have yet to see a recommendation on a non-default block size. I believe the documentation mentions there may be certain scenarios that a non-default block size may alter performance, but I do not believe I have ever seen a specific recommendation. Do you have links to the official documentation you are paraphrasing to support this observation? What is the exact verbiage? What are Donald K. Burleson’s recommendation on block sizes? I’ve seen one page of a few with some points, but these comments do not state much other that the simple points or comments from other people. Just for one example, one remark you have is “Large blocks gives more data transfer per I/O call”. True statement, but what about when multiblock read is used to read more than one block. Then what? If I may, this is like saying a larger petro tank holds more fuel than a smaller one. Completely true, but what does that do for fuel efficiency? I hope that you can elaborate on your position on block sizes. It’s interesting but I have not seen much details to persuade me to consider a non 8k block size. Also, the points that you have on your block size page (faster updates, etc): are those your findings or are they a collection from others? Thank you much for helping to understand more.
Can you please change the web page color to something like white or so . Its hard for eyes to read on blue base.
Hi JP,
>> There are very few software products that really are 100% tested.
Yeah, that’s true, and it;s not Oracle’s fault. It would take decades to test each and every nuance.
However, blocksizes are fundamental . . .
>> That is a bold statement to make with out any references.
No problem, here are references that recommend multiple blocksizes:
http://www.dba-oracle.com/t_multiple_blocksizes_summary.htm
Metalink Note:46757.1 titled “Notes on Choosing an Optimal DB BLOCK SIZE” says that there are some benefits from having larger blocksizes, but only under specific criteria (paraphrased from Metalink):
– Large blocks gives more data transfer per I/O call.
– Larger blocksizes provides less fragmentation (row chaining and row migration) of large objects (LOB, BLOB, CLOB)
– Indexes like big blocks because index height can be lower and more space exists within the index branch nodes.
– Moving indexes to a larger blocksize saves disk space. Oracle says “you will conserve about 4% of data storage (4GB on every 100GB) for every large index in your database by moving from a 2KB database block size to an 8KB database block size.”
what kind of waits did you experience in your situation only i/o waits or others?
This is the best of the best.
Don at his best..
Free entertainment.I wish I have comments like this on my blog.:)
Miladin
Thanks for the very good discussion aside from few personal attacks.
If I may suggest ,
1. If you provide a test case ( with the create …. exec plans ) , that will be great .
2. This is not a place for personal attacks ( like .. mean sprited ,,,, ) .
3. In a scientic field , we would learn to agree on disagreeing .
Let us treat the inner biomedical issues, not just the outer behavioral issues, and maybe these kids will simply act better. ,