Testing is believing, but your test quality must service your need. With my past experiences I may guarantee that;
- isolated environment tests will cheat you,
- response time based tests will cheat you
Once upon a time I told one of friends that if she saw less logical i/o within a performance comparison, even it is Mr.Lewis whom we perceive as a god of Oracle :), don’t believe if you are told the opposite. This statement is a little missing, I always prefer to check these outputs during an important performance comparison for each scenerio;
- logical i/o amounts and the access paths,
- latching amounts and locking activity is critical for scalability on oltp applications,
- waits in the response time
So in my opinion the most accurate path is;
- to create a sufficient and appropriate test case parallel to your need,
- load it parallel to your production needs on your test environment(for load purposes you may use dbms_scheduler or dbms_job prior to 10g for example)
- and do 10046 level 8 + tkprof analysis of each session for comparison(but be careful about tkprof traps)
Here is an example also Mr.Lewis at the end included :) This article Myths on bitmap indexes by Patrick Sinke took a lot of attention some time ago. There were two interesting claims about Bitmap Indexes;
- Myth 2 – Bitmap indexes are only suitable for data warehouses
- Myth 3 – Bitmap indexes have slow performance on DML actions
But these hypothesis were not tested under load, just a single connection isolated environment sql*plus session, so if your production is similar to this than no problem :) There were lots of comments warning the author, so he re-published the article.
Here is another example sharing the comparison results of index organized table(IOT) versus heap organized table under load. So after these results do you still believe IOT or Hash Clusters that Oracle use with world record breaking tpc benchmarks are performing worser than heap organized tables as it is usually advised referencing idiot looping test cases? My answer is, you again have to implement a test case for your own need, load it and analyze 10046 outputs before you end up with any conclusion.
After years my conclusion is that you may never believe easily all you read or hear. Everybody is sharing something they believe useful or “TRUE” to others also. But just because something is printed or someone is more experienced doesn’t mean it is “TRUE”, at least for you. OS and Oracle versions matter, parameters and statistics change everything.
And I advice when you are using a search engine nowadays, add “oracle.com” at the end of your search words, this way at least you will be searching first the official information. There are always some hunters waiting for their sheep..