Wrong results
August 3rd, 2005 by Peter Scott
One of our users raised a support call with my team – a query returned the wrong results. My guys check it out and capture the explain plan… yes wrong results, and because the lower bound of a BETWEEN prediacte was being omitted. So a TAR is raised with support – the captured plan is filed with the TAR, support can see there is a problem
Back comes the phrase most DW managers hate to hear from support: “Can you prepare a simple test case?”
Test cases are not trivial on a 2.5TB DW. This query joins to a half a dozen dimension tables, the fact table has 116 partitions and tens of millions of rows. Looking at the query plan we are using star transforms into temporary tables, bitmap indexes and parallel query. To further complicate the set-up the fact table is also a materialized view and registered for query re-write. It’ll take a while to reduce that to something portable and still showing the problem.
UPDATE
Following Tom Kyte’s suggestion to create an empty test case + exported stats I got a working test case out to support in just a few hours, I included the minimum data to show the problem (three rows!)
If you do export stats watch out for the user id in the exported table - the DBMS_STATS.IMPORTxx procedure tries to use the same user as the original export.
By the way, the problem is not a dropped predicate but a missing bound in the partition iterator
August 3rd, 2005 at 9:40 pm
Someone has to do it…
Without a test case, how can a problem be solved, or proved that it is solved?
Since you found the error, it would probably be easiest for you to create the test case (as difficult as it may be) than it would be for Oracle to try to reproduce it.
Sure, it’s no fun, but you’re doing your bit for all of us.
August 3rd, 2005 at 9:55 pm
I know… it just takes a few days - removing the factors that are not involved, cleaning the data to protect commercial content, exporting the test cast, loading it on another machine to see that the problem is still there…
Of course we will do it, but it is bigger task that those three words simple test case
August 4th, 2005 at 1:28 am
Pete –
what you might try doing is
a) create all of the empty structures in a test db
b) export the stats from prod into a stats table
c) export that stats table using good old exp
d) import that into test
e) verify you have the same plan as test (you should, if using system stats - you’ll want to move them too)
f) plug in say “one row per table” that you know should/should not appear because of the bad predicate filter
that should reproduce, rather than hit or miss - and you can set support the
o creates
o dmp file(s)
o inserts
o the query
August 4th, 2005 at 7:33 am
Thanks Tom
You make it sound so simple
We might have missed the system stats export, thanks
To your list perhaps we should add the output from show parameters