Test cases revisited

Back in January I wrote about a bug we found in the query optimiser that caused some of our complex temporary table star_transformation query candidates to crash the plan evaluation phase of optimisation with an ORA 604 error.

Well, the effort of filing the test case was worthwhile - the guys and gals at Oracle have created a patch for the problem. Our next stage is to validate that the patch resolves the problem with production queries rather than the simple test case. And there is the challenge, our test system is about 1/6th the size of the production system in terms of fact date range but equi-sized in all other dimensions. Simply running example code (grabbed from production) on the test system is unable to reproduce the problem - remember the hoops we needed to go through to get a zero-data test case.

So what we need to do is to convince Oracle that the two systems contain the same data and distributed in the same way. So again DBMS_STATS export and import functionality comes into its own. But now defaults conspire against us - exporting table stats also includes index stats, column stats and partition stats; it is the partitions that will cause a problem on import because some (most) of the partitions do not exist on test system and there is no simple dbms_stats procedure to only move global stats.

If only there was an import global table stats option - I only need global stats as all of my test cases access multiple partitions or use KEY values for PSTART and PSTOP in the query plan. It seems that the most pragmatic approach is to delete from the transferred stats table all of the rows that relate to partitions (or at least the non-existent partitions) before doing the stats import. But this may not be quite as simple as it sounds. I'll leave it to one of my DBAs to find out :-)