January 16, 2007
January 3, 2007
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.
December 15, 2006
I received an email recently from someone who had just upgraded the Oracle 11i Business Suite (11.5.9) from 184.108.40.206 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.
December 9, 2006
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.
December 7, 2006
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.
December 4, 2006
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.
November 11, 2006
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.
- the available hints are not properly documented
- the hints are rarely used properly – because they are not documented properly.
- (there are a few bugs that make things go really wrong anyway) – and yes, I know that’s the third reason of two
November 9, 2006
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.
November 8, 2006
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:
November 6, 2006
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.
November 2, 2006
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.
October 28, 2006
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.