Hacker News Reader

Comment by dpb001 | Hacker News Reader

The phrase "tune it via their CBO" doesn't make sense.

Oracle DBA here who hates their licensing, practices, etc. as much as the next guy, but I have to hand it to their query optimization. I've seen their optimizer get better with every major release, and yes, regression testing will always turn up a few problems but Oracle gives you the best tools to analyze and override the decisions that the optimizer is making when necessary (and I'm not just talking about basic hints here)

View on HN

Until someone switches a table name from lowercase to uppercase and the hand rolled optimizer stops working?

You clearly have no idea what you are talking about.

This happened to me in prod with Oracle. Lost a critical optimization in prod because my case of a table changed from upper to lower.

If you changed the case of a table name, this means you had to change whatever was generating the SQL since mixed or lower case requires table or column names to be surrounded by double quotes.

So, you changed code and didn't test it. Yup, definitely Oracle's fault.

Having a poorly thought out query planner as the cornerstone of their product is a sign that the product is not that good.

In all fairness, I can't really blame Oracle on this one. That's a fairly well documented issue, because Oracle places the hash value of each query into the shared SQL pool, and if CURSOR_SHARING is set to exact and you've stopped the query from aging out then Oracle will see it as a different query.

That's not even really a CBO issue. But I hear your pain :-)

Except that's not what I'm referring to. As with any cost based optimiser, it's only as good as the statistics it has to work with, and if you've finally carefully tuned your database with an appropriate histogram on a critical table, and found that after an upgrade they "tweaked" the CBO to fix a statistics calculation then you are right back to looking at explain plans to fix your performance issue.

Sometimes you just aren't going to pick up this sort of thing in testing as you'll only know about it under full production load. Their CBO is pretty powerful, but by and large it's often difficult to know why it makes certain decisions. And that's even when you use their full suite of profiling tools!

Reply on news.ycombinator.com