Re: Query that returns 2 different results in 9iRAC and 10gRac

Joel Garry wrote:
> Seemed clear enough to me. ACID only applies to a stable set of data.
> The "performance tool" is looking at transient data. Any time you
> start getting down into Oracle internals you will have to deal with
> this. You can't stop the clay pigeon at your favorite spot and then
> shoot it.

There is definately some heisenberg effects going on here.

I spent some time with the developer that reviewed the bug today, and he managed to convince me that it wasn't a bug - an explanation as below. But now, after a whisky or two, I note that the bug as logged against the tar doesn't quite reflect what was reported in the tar, and the explanation he gave me doesn't quit reflect what was actually logged in the bug.

So I will continue to follow up. But in a nutshell, here's the explanation as to what we thought was reported.

1. In a RAC environment, any query against a gv$session view will spawn one parallel query slave per instance to do the scan. (i.e Session count = 1 PQ slave/node)
2. If you union all two of these queries together without using the WITH clause, you will get one parallel query slave per 'branch' of the union all, again per instance (i.e Session count = 2 PQ slaves/node)
3. Adding the WITH clause changes the execution plan so that the second (and any subsequent) branch of the query trys to use the intermediate results produced by the first, which are materialized to a temp table. As such, a query slave for the second branch (the second query in the UNION ALL) is now not required, so is not spawned. So with the addition of the WITH, session count goes back to 1 PQ slave/node

None of this above is a bug. In fact this is what is supposed to happen. Which is why the bug was closed.

Problem is I now have trouble relating this back to what was acutally reported in the TAR, and then in the newsgroup, and now my head hurts and I think the cat just disappeared :-( Received on Thu Mar 10 2005 - 22:26:54 CST