On histograms

In his paper "Using DBMS_STATS in Access Path Optimization", Wolfgang Breitling writes:

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.

I'm Kind of a Big Deal Around Here

Yesterday Justin Kestelyn over at the OTN blog announced a re-org of the blogs.oracle.com homepage. I was pleasantly surprised to see my own name listed among the likes of Werner Puschitz, Jonathan Lewis, Howard Rogers and my new locker buddy Laurent Schneider. Jeremy Schneider is conspicuously absent, hopefully that gets remedied too.

And, yes, I've probably used that title before.

A Brief Update

I've been busting ass lining up sponsors for an Ultimate tournament, so blogging has taken a big backseat. I have added a tumblr blog to my collective which you might find diverting.

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.

What Is NULL?

Today in #oracle, Burk0 asked:

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

...

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!

Music. Sweet music

Oracle Licensing (or: WTF Partitioning?!)

I came across this post about Oracle partitioning licensing a couple weeks ago (yes I'm catching up with all the blogging). In it, Mathias basically doesn't think that partitioning should be separately licensed at all, and included in ALL versions of Oracle.

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?

Perils and Pitfalls in Oracle Partitioning: Bind Variables?

Recently one of my developers found some articles by Arup Nanda while researching a query performance problem. The articles were title "Perils and Pitfalls in Partitioning," parts 1 and 2.

In particular he was curious about this statement in part 2:

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

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.

Joining on substrings

Jonathan Lewis shared an example of joining two tables where the joining field on table is comprised of two fields on the other table. The original poster thought he'd need to re-design his table, but Jonathan proved otherwise with the grace and efficiency of a true Englishman.

Definitely good to know!

LOL/SQL?

It started with the LOLCATS. Then we had the ORLY thing and the "in ur base" thing. It's all one big conglomeration now, and it has belched forth LOLCODE. I'm told there is a parser/compiler for LOLCODE, but my head hurts just thinking about it.

But it does beg the question of when we'll see the Oracle extension of LOL/SQL and who will write it. Steven?

CSALTER requires a clean CSSCAN

Following up to my last post, it turns out that CSALTER requires you to have clean data and produce a clean CSSCAN within 7 days prior to running the character set migration script.

If the CSSCAN isn't clean, you'll get this:

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.

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.