Hi,

I am writing some new SQL queries and want to check the query plans that the Oracle query optimiser would come up with in production.

My development database doesn't have anything like the data volumes of the production database.

How can I export database statistics from a production database and re-import them into a development database? I don't have access to the production database, so I can't simply generate explain plans on production without going through a third party hosting organisation. This is painful. So I want a local database which is in some way representative of production on which I can try out different things.

Also, this is for a legacy application. I'd like to "improve" the schema, by adding appropriate indexes. constraints, etc.

I need to do this in my development database first, before rolling out to test and production.

If I add an index and re-generate statistics in development, then the statistics will be generated around the development data volumes, which makes it difficult to assess the impact my changes on production.

Does anyone have any tips on how to deal with this? Or is it just a case of fixing unexpected behaviour once we've discovered it on production? I do have a staging database with production volumes, but again I have to go through a third party to run queries against this, which is painful. So I'm looking for ways to cut out the middle man as much as possible.

All this is using Oracle 9i.

Thanks.