Oracle Scratchpad

January 23, 2007

Searching Metalink

Filed under: Troubleshooting — Jonathan Lewis @ 11:33 am GMT Jan 23,2007

An incoming link this morning took me to a thread on the Oracle forums. The problem related to the issue of a query returning the wrong results. The query in question was as follows:

SELECT  ap_prod,ca_altcod,clm,pn_id 
FROM    sa_codalt,sa_prodnat,sa_prod,usrtraco.traco_sa_prod,sa_natura 
WHERE   ap_prod = pn_prod(+) 
AND     pn_id IS NULL 
AND     ca_prod = ap_prod 
AND     ca_tipo = 90000000013 
AND     oldcod = ca_altcod 
AND     nt_tiponat = 'CLM' 
AND     nt_cod = clm 
;       

The problem is that this query seems to be working, returning 10 rows with a null pn_id as specified in the where clause. But when you add one more column to the select list, you get more rows back, including rows where pn_id is not null.

Now, the original poster does not make it easy for us to help him – he hasn’t used aliases on the tables and columns, hasn’t included execution plans, and (initially) didn’t point out that one of the tables is in the local database, two in one remote, and two in a second remote database.

This behaviour, however, is clearly a bug – the query is returning non-null results against a predicate that demands nulls. So let’s turn to metalink. How do you try to find the bug ?

First, identify the special features of this query. It’s got an outer join (they can be a source of great “entertainment”), it’s a distributed query (and it turned out that the OP couldn’t emulate the problem with a local-only version of the query) with a couple of database links, it’s got a null predicate (another cause of endless entertainment) which is being ignored. So let’s use that information to search.

First choose the advanced search, and tick just the option for the Bug database. Then type the phrase wrong results into the Exact Phrase text box.

After that I tried the following in the match all words text box: distributed outer join null.  This returned just a few results (but if I had got more than 100 results, I would have used the product list to restrict the problem to the server). None of the results matched the symptoms, so I changed wrong results to wrong result (note the singular) just in case – again I got a few returns but no match.

So I changed the distributed to dblink. The problem looks like bug 4527815.

Total search time – 5 minutes (I’m not always that lucky – but a Metalink search is always worth a few minutes of anyone’s time).

Big Clue from the bug description – look at the execution plan for predicates disappearing when you do a remote join. In other words, you have to have at least two tables at the end of a database link before you could see this bug.

As I’ve said before – always do a proper investigation with explain plan using dbms_xplan properly to get the best possible output.

10 Comments »

  1. I think that the biggest problem is Metalink! :)

    We don’t have Metalink support where I work…or so they say! :D

    Comment by Antonio — January 23, 2007 @ 1:00 pm GMT Jan 23,2007 | Reply

  2. Just out of interest, how do you as an independent consultant get a Metalink account? I thought those came with an Oracle licence? Is there a way to search without an account that I’m missing, or did you buy something? Obviously I’m too tight to do that…

    Comment by William Robertson — January 23, 2007 @ 3:05 pm GMT Jan 23,2007 | Reply

  3. William, I’ve had a server licence for the last 18 years or so.

    Comment by Jonathan Lewis — January 23, 2007 @ 7:01 pm GMT Jan 23,2007 | Reply

  4. Jonathan Lewis said:
    …always do a proper investigation with explain plan…

    All well and good when you get a plan – the “feature” I’m tracking causes the parse to fail. Still we are nearly there, we know the type of optimisation that collapses in a heap, is now a matter of making a reduced test case for support.

    Comment by Peter Scott — January 23, 2007 @ 7:53 pm GMT Jan 23,2007 | Reply

  5. Peter, I haven’t seen one of those since 7.3.2. If explain plan fails, then how about a 10053 with 10046 after flushing the shared pool.

    Comment by Jonathan Lewis — January 23, 2007 @ 11:31 pm GMT Jan 23,2007 | Reply

  6. Regarding Metalink access, sounds like I should buy an Oracle licence in 1989 and if possible backdate it for tax purposes ;)

    I’ve often thought a Tardis would pay for itself in no time.

    Comment by William Robertson — January 24, 2007 @ 12:56 am GMT Jan 24,2007 | Reply

  7. Hello Jonathan,
    What might have been instructive and informative here, would be to rewrite using ANSI joins and OUTER joins, where the outer join condition would (have to) contain all the outer-joined-table predicates, i.e.,

    … tableA a
    LEFT OUTER JOIN tableB b ON (a.colA = b.colA AND b.colB IS NULL)

    Seán.

    Comment by SeanMacGC — January 24, 2007 @ 7:42 pm GMT Jan 24,2007 | Reply

  8. Sean – in what way would it have been instructive and informative ? It seems to be a bug which exposes itself when a certain type of execution plan is generated. Anything which changes the plan away from the problem structure would probably solve the problem, so choosing ANSI syntax is not (per se) a particularly relevant experiment.

    Comment by Jonathan Lewis — January 25, 2007 @ 12:07 am GMT Jan 25,2007 | Reply

  9. I agree Jonathan, that this definitely seems to be a bug, exposed by OUTER JOINs and DB Links, however, I have found that using ANSI syntax, where we can very clearly delineate the join conditions and associated predicates per se, there can be reduced latitude for the query transformation to mangle the predicates.

    It’s a simple and quick test, to rewrite the query as per ANSI syntax, though it’s not possible for the non-initiated to do such from the submitted query.

    Comment by SeanMacGC — January 25, 2007 @ 8:36 am GMT Jan 25,2007 | Reply

  10. Just to expand a little here Jonathan: the point of the ANSI exercise would be to determine whether the result sets differed, and if they did, to investigate with event 10053, dbms_xplan, etc. (Sorry, didn’t make that too clear.)

    Comment by SeanMacGC — January 25, 2007 @ 9:03 am GMT Jan 25,2007 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.