My heart sinks when someone in support asks us to send over the datafiles involved in some SR or other we have running with support. People seem to forget that there are some large systems out there. In our case our problem was with a 9 way join in a starflake (a hybrid between star & snowflake) schema with query rewrite enabled and star transformations enabled. In reality the failing query could hit 95 % of the data files in the database and even with this small system we would be looking at 900GB of datafiles. Going back to the original query we were able to capture a plan that took us to the fact table actually used , we could then rewrite the original query in terms of the table used and reduce our join to 6 tables and more importantly just 1 tablespace worth of data. This is still somewhat large (50GB) and frankly not a candidate to send in as a test case.

But all is not lost, we can export the 6 tables with rows=no then using some of the DBMS_STATS functionality create a stats table and export the table stats for the 6 tables affected to it (not forgetting the system stats). We then export the stats table (and its data, of course). On the test box we import the six empty tables and their statistics. Run the DBMS_STATS.IMPORT_TABLE_STATS function for each table imported then prove the problem still occurs. In this case the problem is with plan creation so not having data makes no difference.

We have now moved a reproducible test case to a test server. This just need packaging up and lodging with support – less than 1 MB and no customer data exposed.