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

February 19, 2008

Interesting comments and questions of “Why Oracle?” seminar

Filed under: Oracle Other — H.Tonguç Yılmaz @ 10:33 am

There were several interesting comments and questions during last Saturday’s seminar.

1. Can we flashback a truncated or drop purged table?

Thanks to Ogan for showing that this can be done by flashback database feature. Undo and Flashback Logs are different concepts. I created an additional demo to show that NOLOGGING options can be problematic here;

Code Listing 201a – Flashback database and NOLOGGING operations problem demo

2. How multi-versioning can be able to return rows of a dropped table since drop does not produce undo for the rows it has, only undo for the metadata?

Thanks to Baran for raising this problem. Tom Kyte has a similar example in his book but he uses DELETE and COMMITs, this is an accepted behavior if you are in a single user environment because the undo that DELETE produced will still be available after even you commit. But how DROP is covered here is a question mark, and TRUNCATE raises an error, there is a related Asktom thread for this question.

Code Listing 201b – Multi-versioning returning rows of a dropped table demo

3. Committing in a loop after each N rows during a big update over ROWIDs is much more efficient than a single sql update.

This was not a question in deed, some fellow challenged me during my locking demo with this comment. He even cried out that he couldn’t believe me not knowing this since this was Tom Kyte’s own claim! Pufff, this was an interesting experience, after all those years reading Tom’s books and Asktom threads I just could smile for a while and promised him to demonstrate that this claim both is not Tom’s and wrong.

I guess there can be several reasons that people can not understand what they read;
– they are unable to understand since their native language is not English,
– some forum threads are so long for an average newbie who even is unable to find the topic on the documentation they can not follow,
– the information provided do not have simple demos to assist understanding,
– on purpose they are trying to provocate or mislead the crowd to gain attention etc.
– what else do you think?

But still this was a unique experience since I saw lots of Oracle myth believers before but never saw someone crying out that this kind of a myth is Kyte’s claim. Tom Kyte is so dedicated to not to be one “Question Authority” that his efforts inspired lots of us for years. And here are two related threads of him;

“How to Update millions or records in a table”

“snapshot too old error”

Still I feel an Oracle exorcism is needed within my community after this seminar, so I will be preparing a presentation on some popular myths like;
– dividing a bulk update into pieces and committing frequently will relax your system compared to a single sql update
– we need to rebuild indexes regularly(never ending story..)
– hit ratio based tuning is enough
– bind variables are like aspirin, use them and gain performance anywhere anytime
– what else do you think?

With our native language in my presentation I will try to be short and to the point, try to prepare demos which will assist me since testing is believing. I will use DBMS_SCHEDULER to simulate concurrent access since there are important critiques that a single user environment test can not produce outputs that can be used as a proof for loaded OLTP environments. DBMS_ALERT will help to control the scheduled jobs execution timing, here is an example of this technique. So please comment on the myth topics, your advices on the technique I mentioned in order to have the most efficient output for this exorcism session :)

4. We know that you are not the best but how come that you are the most famous?

This was another interesting comment, FAME is something google promoted me somehow after years of blogging I guess, ironic when I do a Google search even I find my own posts sometimes :) I always thought that this is all about SHARING, but not sharing anything, sharing a valuable and trustable information, after all who thinks that he or she can be the BEST on some software which is this wide ranged and closed source? Also I am not interested to be the BEST but simply I am interested in to understand how things are implemented with the software I use daily, that’s all, and this is a rational human’s default behavior right?

ps: check out this SQL, really incredible :)

Testing Information : the scripts mentioned are tested on Oracle Database 11g Enterprise Edition Release 11.1.0.6.0

References Used :

NA

Leave a Comment »

No comments yet.

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

Blog at WordPress.com.

%d bloggers like this: