Oracle：数据库之间执行计划的差异
我正在比较我的开发和生产数据库的查询。
它们都是 Oracle 9i，但几乎每个查询都有完全不同的执行计划，具体取决于数据库。
所有表/索引都是相同的，但开发数据库每个表的行数约为 1/10。
在生产中，它为大多数查询选择的查询执行计划与开发不同，并且成本有时高出 1000 倍。在某些情况下（全表访问），生产查询似乎也没有使用正确的索引进行查询。
我最近也在两个数据库上运行了 dbms_utility.analyze 模式，希望 CBO 能找出答案。
是否有其他一些底层 oracle 配置可能导致此问题？
我主要是一名开发人员，所以这种 DBA 分析一开始相当令人困惑。
I am comparing queries my development and production database.
They are both Oracle 9i, but almost every single query has a completely different execution plan depending on the database.
All tables/indexes are the same, but the dev database has about 1/10th the rows for each table.
On production, the query execution plan it picks for most queries is different from development, and the cost is somtimes 1000x higher. Queries on production also seem to be not using the correct indexes for queries in some cases (full table access).
I have ran dbms_utility.analyze schema on both databases recently as well in the hopes the CBO would figure something out.
Is there some other underlying oracle configuration that could be causing this?
I am a developer mostly so this kind of DBA analysis is fairly confusing at first..
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
1）我要检查的第一件事是数据库参数在 Prod 和 Dev 中是否相同。如果影响基于成本的优化器决策的参数之一不同，那么所有的赌注都会被取消。可以在v$参数视图中看到该参数；
2) 拥有最新的对象统计数据固然很好，但请记住您指出的巨大差异 - Dev 拥有 Prod 的 10% 行。 CBO 如何决定执行查询的最佳方式时会考虑此行计数。鉴于行数存在巨大差异，我预计计划不会相同。
根据具体情况，优化器可能会选择全表扫描具有 20,000 行的表 (Dev)，其中它可能会决定索引对于具有 200,000 行的表 (Prod) 的成本较低。 （数字仅用于演示，CBO 使用成本计算算法来确定要进行 FTS 的内容以及要扫描的内容，而不是绝对值）。
3) 系统统计数据也纳入解释计划中。这是一组代表 CPU 和磁盘 I/O 特征的统计数据。如果两个系统上的硬件不同，那么我预计您的系统统计数据会有所不同，这可能会影响计划。 Jonathan Lewis 此处进行了一些很好的讨论
您可以通过 sys.aux_stats$ 视图查看系统统计信息。
现在我不确定为什么不同的计划对您来说是一件坏事...如果统计数据是最新的并且参数设置正确，那么无论大小差异有多大，您都应该从任一系统中获得不错的性能...
但它是可以从您的 Prod 系统导出统计数据并将其加载到您的 Dev 系统中。这使得您的产品统计数据可用于您的开发数据库。
检查 DBMS_STATS 包的 Oracle 文档，特别是 EXPORT_SCHEMA_STATS、EXPORT_SYSTEM_STATS、IMPORT_SCHEMA_STATS、IMPORT_SYSTEM_STATS 过程。请记住，您可能需要禁用 10g/11g 上的晚上 10 点夜间统计作业...或者您可以在导入后调查锁定统计数据，以便夜间作业不会更新它们。
1) The first thing I would check is if the database parameters are equivalent across Prod and Dev. If one of the parameters that affects the decisions of the Cost Based Optimizer is different then all bets are off. You can see the parameter in v$parameter view;
2) Having up to date object statistics is great but keep in mind the large difference you pointed out - Dev has 10% of the rows of Prod. This rowcount is factored into how the CBO decides the best way to execute a query. Given the large difference in row counts I would not expect plans to be the same.
Depending on the circumstance the optimizer may choose to Full Table Scan a table with 20,000 rows (Dev)where it may decide an index is lower cost on the table that has 200,000 rows (Prod). (Numbers just for demonstration, the CBO uses costing algorighms for determining what to FTS and what to Index scan, not absolute values).
3) System statistics also factor into the explain plans. This is a set of statistics that represent CPU and disk i/o characteristics. If your hardware on both systems is different then I would expect your System Statistics to be different and this can affect the plans. Some good discussion from Jonathan Lewis here
You can view system stats via the sys.aux_stats$ view.
Now I'm not sure why different plans are a bad thing for you... if stats are up to date and parameters set correctly you should be getting decent performance from either system no matter what the difference in size...
but it is possible to export statistics from your Prod system and load them into your Dev system. This make your Prod statistics available to your Dev database.
Check the Oracle documentation for the DBMS_STATS package, specifically the EXPORT_SCHEMA_STATS, EXPORT_SYSTEM_STATS, IMPORT_SCHEMA_STATS, IMPORT_SYSTEM_STATS procedures. Keep in mind you may need to disable the 10pm nightly statistics jobs on 10g/11g... or you can investigate Locking statistics after import so they are not updated by nightly jobs.