Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

September 10, 2007

Again testing, testing, testing..

Filed under: Oracle How To — H.Tonguç Yılmaz @ 12:36 pm

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..

3 Comments »

  1. All possible session based options are discussed here and given by examples.

    Oracle performance analysis – Tracing and performance evaluation by Bilal Hatipoğlu;
    http://www.bhatipoglu.com/entry/17/oracle-performance-analysis-tracing-and-performance-evaluation

    Comment by H.Tonguç Yılmaz — December 26, 2007 @ 3:08 pm | Reply

  2. […] getting into details I have mentioned importance of testing before, this time again the results proved that somethings even written in the documentation may […]

    Pingback by Oracle OLAP features performance testing Part 1 « H.Tonguç YILMAZ Oracle Blog — January 21, 2008 @ 10:26 am | Reply

  3. Oracle 11G Result Cache in the Real World by Alex Fatkulin
    http://www.pythian.com/blogs/1004/oracle-11g-result-cache-in-the-real-world

    Comment by H.Tonguç Yılmaz — May 14, 2008 @ 4:59 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: