Parallel Tidbits

A couple of small things I noticed recently. The first was a mention of the presence of Bloom Filters in PX plans over on Greg Rahn's blog, another one that I picked up through OraNA which looks very interesting. Greg's looking for ideas for performance related blogs at the moment, so you might have some suggestions.

The other I noticed via the Oracle-L digest email. I don't always read this as well as I should because of all those damn email .sigs that clutter it up! Anyway, when I saw this comment from Jonathan Lewis, it prompted a minor concern.

To the best of my knowledge the /*+ parallel(alias, degree) */ hint does NOT tell the optimizer to use parallel execution, it merely tells the optimizer to divide the cost of a tablescan on 'alias' by 'degree' (allowing for the effect of the _optimizer_percent_parallel in general and a fixed 0.9 scaling factor in 10g specifically) and then follow the consequences.

It is perfectly feasible that the optimizer found a serial index access path that was cheaper than the 'tablescan cost / 4' dictated by the hint.

He makes a wider-ranging point in a later reply.

Under ANY circumstances, what you are trying to do with hints is to restrict the optimizer to having just ONE possible path through its own codebase. If you don't use enough hints, or don't use the hints properly, then the optimizer may find a way of doing something you didn't want, despite obeying all your hints at the appropriate points in its working.

So, if you use the PARALLEL(XXX) hint, think about ANY executions that might NOT do a parallel tablescan on table XXX and make it impossible for them to happen.

It's very similar to things I've heard Jonathan say on other occasions, so why just parrot his words here?

To provide a balance to my constant use of very simple hints in the various PX papers. In most cases, the queries and objects I'm using are so simple that the hints are almost certain to give me what I'm looking for but, for more complex statements, you might need to be more careful to include a full set of hints to persuade Oracle to follow your intentions.

My minor concern was that in keeping things simple, people might believe things are always that simple! Which, as usual, they're not