Oracle：数据库之间执行计划的差异
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2684979/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle: Difference in execution plans between databases
提问by Will
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.
它们都是 Oracle 9i，但几乎每个查询都有完全不同的执行计划，具体取决于数据库。
All tables/indexes are the same, but the dev database has about 1/10th the rows for each table.
所有表/索引都相同，但开发数据库每个表的行数约为 1/10。
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).
在生产中，它为大多数查询选择的查询执行计划与开发不同，成本有时高出 1000 倍。在某些情况下（全表访问），生产查询似乎也没有使用正确的查询索引。
I have ran dbms_utility.analyze schema on both databases recently as well in the hopes the CBO would figure something out.
我最近也在这两个数据库上运行了 dbms_utility.analyze 模式，希望 CBO 能解决问题。
Is there some other underlying oracle configuration that could be causing this?
是否有其他一些潜在的 oracle 配置可能导致这种情况？
I am a developer mostly so this kind of DBA analysis is fairly confusing at first..
我主要是一名开发人员，所以这种 DBA 分析起初相当混乱。
回答by David Mann
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;
1) 我要检查的第一件事是数据库参数在 Prod 和 Dev 之间是否相同。如果影响基于成本的优化器决策的参数之一不同，则所有赌注都将关闭。可以在 v$parameter 视图中看到参数；
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.
2) 拥有最新的对象统计数据很好，但请记住您指出的巨大差异 - Dev 有 10% 的 Prod 行。该行数会影响 CBO 如何决定执行查询的最佳方式。鉴于行数的巨大差异，我不希望计划相同。
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).
根据情况，优化器可能会选择全表扫描具有 20,000 行 (Dev) 的表，其中它可能会决定在具有 200,000 行 (Prod) 的表上的索引成本较低。（数字仅用于演示，CBO 使用成本计算算法来确定 FTS 和索引扫描的内容，而不是绝对值）。
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 hereYou can view system stats via the sys.aux_stats$ view.
3) 系统统计数据也包含在解释计划中。这是一组代表 CPU 和磁盘 i/o 特性的统计数据。如果您在两个系统上的硬件不同，那么我希望您的系统统计数据不同，这可能会影响计划。Jonathan Lewis在这里的一些很好的讨论 您可以通过 sys.aux_stats$ 视图查看系统统计信息。
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.
但是可以从您的 Prod 系统导出统计信息并将其加载到您的 Dev 系统中。这使您的 Prod 统计信息可用于您的开发数据库。
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.
检查 DBMS_STATS 包的 Oracle 文档，特别是 EXPORT_SCHEMA_STATS、EXPORT_SYSTEM_STATS、IMPORT_SCHEMA_STATS、IMPORT_SYSTEM_STATS 过程。请记住，您可能需要在 10g/11g 上禁用晚上 10 点每晚的统计工作……或者您可以在导入后调查锁定统计信息，这样它们就不会被每晚的工作更新。
