Full Hinting

This question came up some time ago on the comp.database.oracle.server newsgroup. It’s an interesting question, and I’m not sure I know the answer.

Hints and Guesses

If you go to this URL, you will find a discussion about the index_ss() hint. It’s an interesting example of how (a) you could argue that Oracle is ignoring hints until (b) you realise that we really can’t tell what’s going on because we don’t know what a hint is supposed to do.

ORA-01722: upgrade error

I received an email recently from someone who had just upgraded the Oracle 11i Business Suite (11.5.9) from 9.2.0.6 to 10.2.0.2.  After the upgrade, the following SQL statement (shown here with its original format – not according to my standards) started failing with Oracle error: ORA-01722: invalid number.

How many ways ?

Have you ever wondered how hard the optimizer has to work to produce an execution plan. Consider a simple query with four tables in a straight join, where each table has a few B-tree indexes. How many different executions plans could the optimizer produce.

I won’t guarantee that I’ve covered all the options in the following list (and I’ve deliberately ignored some special cases), even so it’s an extraordinary result.

Pushing Predicates

Tom Kyte once said he wanted me to start a blog so that I could at least warn people about new articles on my website. So that’s what this posting is for. 

In response to a question from a person attending the “Optimal SQL” class I was running for Oracle University, I have written a short article about the push_pred and no_push_pred hints.

Thinking it through

In a posting on table order in the from clause, I explain how the optimizer doesn’t always have time to investigate every possible join order for a query, and point out that this means it is possible for a change in the order of the tables in your from clause to result in a change in the execution plan for the query.

What is a hint ?

It’s a great shame that Oracle Corp. decided to use the name “hints” for its optimizer directive mechanism.  “Hints” are not hints, they are interception points in the optimizer code path, and must be obeyed.

Why do people think that Oracle “ignores” hints ? There are two main reasons.

1. the available hints are not properly documented
2. the hints are rarely used properly – because they are not documented properly.
3. (there are a few bugs that make things go really wrong anyway) – and yes, I know that’s the third reason of two

dbms_xplan in 10g

[More on dbms_xplan.display_cursor()]

If you’re using 9i and haven’t learned about the dbms_xplan package, then you should take a good look at it right away. It’s (usually) a much better way of getting execution plans from your system than writing your own queries against the plan_table.

If you’ve been using dbms_xplan, and upgraded from 9i to 10g, make sure that you look at the new features – there are some things which are really useful, and this note is about just one of them.

dbms_xplan.display_cursor()

Subquery Selectivity

The optimizer in 10g (possibly release 2 only) has an important fix that may be enough to make some people decide to accelerate their migration programs. It’s to do with filter subqueries.

To get things started, here’s a little query with its execution path:

Filter Subqueries

Here’s a little demonstration of a feature that can cause random fluctuations in performance because of an unlucky data item. It starts with an emp table holding 20.000 employees spread across six departments, and then moves one employee to a new (carefully chosen) department. You will have to run this in version 9i or later, as it makes use of subquery factoring to generate the emp table.

But it’s in the manual!

I’ve just seen a note on the news group comp.databases.server.oracle advising someone to check the online manual for a piece of code to report which objects are using how much space in the buffer cache. This is the reference and this is the code:

 
SELECT o.object_name, COUNT(1) number_of_blocks 
  FROM DBA_OBJECTS o, V$BH bh 
 WHERE o.object_id  = bh.objd 
   AND o.owner     != 'SYS' 
 GROUP BY o.object_name 
 ORDER BY count(1); 

There are two flaws with this code – it gets the wrong results, and it’s inefficient.

Taking a Hint

It seems the word is gradually getting around the Oracle world that “hints” are really absolute directives to the optimizer that cannot be ignored – if they are correctly used.

Unfortunately the documentation on hints is far from complete, so it is very easy to produce examples that suggest that a hint has been ignored. Here’s one I came across recently in a note about 10g.