Histograms are like drugs -- An overdose [of histograms] can kill [performance].I've gotten a crash course on this recently. More on that in a bit.
29 June 2007
27 June 2007
And, yes, I've probably used that title before.
20 June 2007
However I've had some blog-worthy Oracle tales in recent days which are still unfolding. As soon as the dust has settled I'll be sure to write it up so that others may learn from my misfortunes.
And I'm almost ready to think about scheduling that OCA exam.
06 June 2007
<Burk0> mmm... got another problem... I'm using a "insert or update" trigger for my previous task. When I'm inserting a new row, I'd like the varchar field "oldvalue" to report "NA". I put an IF inside the trigger, checking if :old.id=null but that doesn't seem to work..any hint?
A fortuitous situation to be sure, for I had just minutes before finished reading this 2005 blog post from Robert Vollman. Although this situation was easily answered by informing the reader that comparisons to NULL require the use of "IS" and "IS NOT", as opposed to the equals operator "=", I went a step further and explained that nothing is equal to NULL, not even NULL itself.
The comment by HJR is particularly helpful:
When you insert a row that explicitly includes a NULL, you are explicitly stating you don't know what value should be included for that column. That is again very different from just missing it out. The one is a deliberate action and acknowledgement of the limits to knowledge. The other could be just a mere oversight.Music. Sweet music
Incidentally, because NULL means 'I don't know', you can never equate anything to NULL... even itself. Two unknowns are two unknowns, not two bits of the same unknown... for to be able to say that would require knowing something about them!
For those of you who haven't had the pleasure:
To use partitioning, you have to pay for the Enterprise Edition of the database at $40,000 per CPU and then pay an additional $10,000 for the partitioning option. That is a lot of money if all I really need is SE One (at $5,000 per CPU) with Partitioning. Unfortunately, that is not an option.As we used to shout at band camp, "$10,000? WHAT A SCREW!" Same thing goes for the Diagnostic Pack, one of the greatest inventions of mankind.
I'd at least settle for making partitioning included in EE and a pay-for-play option in SE. Of course, then who would pay for all of this?
In particular he was curious about this statement in part 2:
I had come across this explanation before, about how Oracle will peek at the bind variable when it hard-parses a query, and then generate a potentially horrendous query plan geared specifically towards that first bound value. By this token, the general sentiment is that use of bind variables for queries against large, partitioned (warehouse-y) tables was probably not in the best interest. It was also given that such statements would be run infrequently compared to your normal set of OLTP tables where bind variables will save your bacon.
While using partitioning, should you use bind variables?
This is an interesting question. As we all know, use of bind variables eliminates the need to parse the cursors and makes it easier to reuse the cursors.
In case of partitions, however, using bind variables poses a problematic situation. Partition elimination and joins can occur only if the optimizer knows the filtering predicate in advance. The value of bind variables are not known until it's time to execute, making the process of partition elimination or joins impossible. Therefore, to take advantage of these options, you should not use bind variables.
In Oracle 9i, the first parse of the statement, called hard parse, peeks into the value of the bind variable, and can effect these optimization options. But this occurs only with the hard parse; subsequent parses still go around the bind variable values.
I did google more about it and found something indicating that it shouldn't be an issue in our version of 10.2.0.2. Unfortunately, this event occurred 3-4 weeks ago, and I neglected to bookmark that source. I'll follow-up in this space if I do get a final answer. Perhaps a loyal reader can steer me straight.
This December post from Jonathan Lewis doesn't suggest that the issue was ever "fixed" (if you can call it a bug). Perhaps I was daydreaming or going through a chewing gum withdrawal delirium.
Definitely good to know!
02 June 2007
But it does beg the question of when we'll see the Oracle extension of LOL/SQL and who will write it. Steven?
01 June 2007
If the CSSCAN isn't clean, you'll get this:
So that question has been answered. Now to scrub the application data, as well as the histograms for that data that are in the data dictionary.Checking data validility...
Exceptional data found in scanner result
PL/SQL procedure successfully completed.
Checking or Converting phrase did not finish successfully
No database (national) character set will be altered
CSALTER finished unsuccessfully.