oracle 相同的 SQL 但不同的解释计划
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4953315/
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
same SQL but different explain plan
提问by iwan
I am running same SQL (below), at 2 different environments with same index set and table size. But they gave me 2 different explain plans (attached)
我在具有相同索引集和表大小的 2 个不同环境中运行相同的 SQL（如下）。但是他们给了我两个不同的解释计划（附后）
- uses a Merge Join Cartesian -- very slow
- uses PX Coordinator / PX Send / PX RECEIVE -- very fast
- 使用 Merge Join Cartesian —— 很慢
- 使用 PX Coordinator / PX Send / PX RECEIVE -- 非常快
Query:
询问：
SELECT *
FROM SIEBEL.S_PARTY PRTY, SIEBEL.S_CONTACT CONT, HPQ_IF_ENTERPRISE_DIRECTORY ED,SIEBEL.S_BU BU
WHERE PRTY.ROW_ID = CONT.PAR_ROW_ID
AND BU.ROW_ID(+)=CONT.BU_ID
AND CONT.EMP_NUM IS NOT NULL
AND ED.HPSTATUS NOT IN ('Terminated', 'Retired', 'Deceased')
AND ED.EMPLOYEENUMBER = UPPER (LPAD (CONT.EMP_NUM, 8, '0'))
AND (SUBSTR(ED.MODIFYTIMESTAMP,1,14) >= '19800101' OR ED.MODIFYTIMESTAMP IS NULL)
Any idea what is the possible things to cause this difference? And what does 2nd explain plan with (PX things) mean?
知道导致这种差异的可能原因是什么吗？2nd 用（PX 东西）解释计划是什么意思？
Note that I am not looking for changing the SQL query (freeze in production).
请注意，我不是在寻找更改 SQL 查询（在生产中冻结）。
Thanks a lot.
非常感谢。
回答by Gary Myers
PX indicates parallel processing. That may not be available on the other database due to session settings (or if the other database is a different edition or version).
PX 表示并行处理。由于会话设置（或者如果另一个数据库是不同的版本或版本），这可能在另一个数据库上不可用。
回答by Jeffrey Kemp
The plan for a query is not just dependant on the table size or indexes, but also on many other factors, mainly the statistics for the table, its columns, and its indexes. These statistics include such things as the clustering factor, which can make a big difference to the calculated cost.
查询计划不仅取决于表大小或索引，还取决于许多其他因素，主要是表、列和索引的统计信息。这些统计信息包括聚类因子等，这会对计算的成本产生很大的影响。
In addition, different system statistics, optimizer parameters, table structure (e.g. partitioned vs. non-partitioned), and database block size, all come into play and the slightest difference between environments can cause a different plan to be favoured.
此外，不同的系统统计信息、优化器参数、表结构（例如分区与非分区）和数据库块大小都会起作用，环境之间最细微的差异都会导致不同的计划受到青睐。
回答by Jon Heller
Do the tables have a different DEGREE? Check this query in both environments:
表有不同的度数吗？在两种环境中检查此查询：
select table_name, degree from all_tables where table_name in ('S_PARTY', 'S_CONTACT', 'HPQ_IF_ENTERPRISE_DIRECTORY','S_BU');
