oracle-11g和12c中相同的查询但不同的计划 [英] oracle - same query but different plan in 11g and 12c
问题描述
此问题与此问题有关. 这是我尝试在12c中使用的代码
This Question is relative to this question. This is code i try to use in 12c
SELECT * FROM DMProgDate_00001
WHERE 1=1
AND ProgressOID IN (
SELECT P.OID FROM (
SELECT OID FROM (
SELECT A.OID, ROWNUM as seqNum FROM (
SELECT OID FROM DMProgress_00001
WHERE 1=1
AND Project = 'Moho'
AND Phase = 'Procurement'
AND Displine = 'Q340'
ORDER BY actCode
) A
WHERE ROWNUM <= 20
) WHERE seqNum > 0
) P
);
-
结果
result
11g:不到1秒
12c:超过8秒
这是11g中的查询计划
This is the query plan in 11g
这是12c中的查询计划
This is the query plan in 12c
当我取出所有分页代码时(如下所示). 12c中的查询足够快，因为11g但是需要分页查询.
When I take out all pagination code (like below). query in 12c is fast enough as 11g BUT need pagination query.
SELECT * FROM DMProgDate_00001 WHERE 1=1 AND ProgressOID IN ( SELECT P.OID FROM ( SELECT OID FROM DMProgress_00001 WHERE 1=1 AND Project = 'Moho' AND Phase = 'Procurement' AND Displine = 'Q340' ORDER BY actCode ) P );这是12c中的查询(无分页)计划
This is the query (without pagination) plan in 12c
我尝试了OFFSET ..关键字(仅支持12c)和optimizer_features_enable('11 .2.0.4')，但结果与上述相同(超过8秒).
I tried OFFSET .. key word ( only 12c support) and optimizer_features_enable('11.2.0.4') but same result as above ( over 8 sec).
我们需要同时支持11g和12c，我知道有些绕道可以解决此问题(在
We need to support both 11g and 12c and I know some detour to fix this problem ( in my pre-question) BUT don't want to keep it as same query code. Is there any option or setting that can fix this qeustion?
将查询计划添加为文本 (它们是不同的表名，但具有相同的表结构和内容)
added query plan as text (they are different table name but are same table structure and contents)
12c - over 3 sec Plan hash value: 3742986389 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 153 | 204 (0)| 00:00:01 | |* 1 | FILTER | | | | | | | 2 | TABLE ACCESS FULL | DMPROGDATE_00001 | 1 | 153 | 102 (0)| 00:00:01 | |* 3 | FILTER | | | | | | |* 4 | COUNT STOPKEY | | | | | | |* 5 | TABLE ACCESS FULL| DMPROGRESS_00001 | 26 | 2288 | 102 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( EXISTS (<not feasible>) 3 - filter("OID"=:B1) 4 - filter(ROWNUM<=20) 5 - filter("PROJECT"='Moho' AND "PHASE"='Procurement' AND "DISPLINE"='Q340') Note - dynamic statistics used: dynamic sampling (level=2) - 1 Sql Plan Directive used for this statement 11g - 0.01 sec Plan hash value: 833434956 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 13 | 1157 | 57 (2)| 00:00:01 | |* 1 | HASH JOIN RIGHT SEMI| | 13 | 1157 | 57 (2)| 00:00:01 | | 2 | VIEW | VW_NSO_1 | 3 | 81 | 34 (0)| 00:00:01 | |* 3 | COUNT STOPKEY | | | | | | |* 4 | TABLE ACCESS FULL| DMPROGRESS_00037 | 3 | 99 | 34 (0)| 00:00:01 | | 5 | TABLE ACCESS FULL | DMPROGDATE_00037 | 7388 | 447K| 22 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("PROGRESSOID"="OID") 3 - filter(ROWNUM<=20) 4 - filter("DISPLINE"='Q340' AND "PHASE"='Procurement' AND "PROJECT"='Moho')推荐答案
即使在相同版本的两个环境之间(不仅是11g和12c)，所选优化程序选择的执行计划也会有很大差异.它取决于许多因素，但主要是:
The execution plan chosen by the optimizer chosen can vary greatly even between two environments of the same version, not just 11g and 12c. It depends on many factors, but mostly:
- 表中的行数(env之间是否相似?)
- 存在哪些索引(两个数据库是否具有完全相同的索引?)
- 表和索引是否有统计信息，它们的最新状态如何?如果不是最新的，请收集表和索引的统计信息.
- Number of rows in the table (are they similar between envs?)
- What indexes are present (do both dbs have the exact same indexes?)
- Are there stats for the table and the indexes, and how up to date are they? If not up-to-date, please gather stats for the tables and the the indexes.
如果您可以发布这些详细信息，我可以提供更有用的答案.
If you can post these details I can provide a more helpful answer.
此外，代码看起来像是自动生成的，如果您可以随意修改它们，则可以根据详细信息建议重写的查询和/或提示.
Also, the code looks like auto generated, if you are at liberty to modify them, based on the details, we can suggest a rewritten query and/or hints.
如果无法修改代码，则可以使用SQL计划管理(SPM)强制执行计划-从运行速度更快的数据库中导出，然后导入另一个数据库中.
If you cannot modify the code, you can force a plan with SQL Plan Management (SPM) - export from the db where it runs faster and import into the other db.
更新
使用此简化的SQL版本生成计划:
Use this simplified version of the SQL to generate the plan:
SELECT * FROM DMProgDate_00001 WHERE ProgressOID IN ( SELECT OID FROM DMProgress_00001 WHERE Project = 'Moho' AND Phase = 'Procurement' AND Displine = 'Q340' AND ROWNUM <= 20 ORDER BY actCode );这篇关于oracle-11g和12c中相同的查询但不同的计划的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
