如何导出Oracle统计数据
我正在编写一些新的 SQL 查询，并希望检查 Oracle 查询优化器在生产中提出的查询计划。
我的开发数据库没有像生产数据库那样的数据量。
如何从生产数据库导出数据库统计信息并将其重新导入到开发数据库中？我无权访问生产数据库，因此我无法在不通过第三方托管组织的情况下简单地生成生产解释计划。这很痛苦。所以我想要一个本地数据库，它在某种程度上代表了生产，我可以在上面尝试不同的事情。
此外，这适用于遗留应用程序。我想通过添加适当的索引来“改进”架构。约束等。
我需要首先在我的开发数据库中执行此操作，然后再进行测试和生产。
如果我在开发中添加索引并重新生成统计数据，那么统计数据将围绕开发数据量生成，这使得很难评估我的更改对生产的影响。
有人对如何处理这个问题有任何建议吗？或者这只是我们在生产中发现意外行为后修复它的情况？我确实有一个具有生产量的临时数据库，但我再次必须通过第三方来对此运行查询，这很痛苦。所以我正在寻找尽可能消除中间人的方法。
这一切都是使用Oracle 9i。
谢谢。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
请参阅 DBMS_STATS.EXPORT_SCHEMA_STATS 和 DBMS_STATS.IMPORT_SCHEMA_STATS 包的文档。如果您没有访问权限，则必须让具有必要权限的人员为您在生产数据库中进行导出。如果您的开发硬件与生产硬件显着不同，您还应该使用 EXPORT/IMPORT_SYSTEM_STATS 过程导出/导入系统统计信息。
执行此操作后，请记住关闭开发数据库中重新计算统计信息的所有作业。
See the documentation for the DBMS_STATS.EXPORT_SCHEMA_STATS and DBMS_STATS.IMPORT_SCHEMA_STATS packages. You'll have to have someone with the necessary privileges do the export in the production database for you if you don't have access. If your development hardware is significantly different than your production hardware, you should also export/import the system statistics with the EXPORT/IMPORT_SYSTEM_STATS procedures.
Remember to turn off any jobs in the development database that recalculate statistics after you do this.