为什么优化程序计划与实验性查询运行不相关？

Question

假设我们有以下问题：

• 给定一个表有一列'X'，含有某些行随机 整数从1到100：

  CREATE TABLE xtable(x) AS 
      SELECT ceil(dbms_random.value * 100) 
      FROM dual 
      CONNECT BY level <= 1000000; 
  

• 我们必须删除重复所以所有不同的整数都保留在表中。

---

让我们考虑三种解决方案（平均执行时间和优化计划）之下。

我必须补充，实验表明：

• 溶液1和2是可伸缩的，并具有与每个行量步骤（附表测试高达10万行）
• 溶液3具有线性时间的增长指数时间增长大约像3 * exp(0.6 * N)

我们看到，对于解决方案2优化计划给无关的实验结果的期望， 的甚至相反的对他们说：

• 成本和其他值都几乎是在计划2相同，3
• 执行时间实际上是解决办法1相同，2

而且在这个实验表 的收集统计信息的存在与否不会影响优化程序计划和执行时间。

---

请解释为什么我不能在案件2

是什么原因导致的优化忽略线性和指数的复杂性之间的明显差异信任优化计划？

---

解决方案：
1.

DELETE xtable WHERE rowid IN (
     SELECT ri from (
     SELECT rowid            AS ri, 
       row_number() OVER(PARTITION BY x ORDER BY null) AS rn 
      FROM xtable 
    ) 
     WHERE rn > 1 
) 


Exe time: 14 - 16 secs 

Plan: 
------------------------------------------------------------------------------------ 
| Id | Operation    | Name  | Rows | Bytes | Cost | Time  | 
------------------------------------------------------------------------------------ 
| 0 | DELETE STATEMENT   |   | 1000000 | 15000000 | 5119 | 00:00:01 | 
| 1 | DELETE     | XTABLE |   |   |  |   | 
| * 2 | HASH JOIN SEMI  |   | 1000000 | 15000000 | 5119 | 00:00:01 | 
| 3 |  TABLE ACCESS FULL | XTABLE | 1000000 | 3000000 | 280 | 00:00:01 | 
| 4 |  VIEW     | VW_NSO_1 | 1000000 | 12000000 | 2976 | 00:00:01 | 
| * 5 |  VIEW    |   | 1000000 | 25000000 | 2976 | 00:00:01 | 
| 6 |  WINDOW SORT  |   | 1000000 | 3000000 | 2976 | 00:00:01 | 
| 7 |  TABLE ACCESS FULL | XTABLE | 1000000 | 3000000 | 280 | 00:00:01 | 
------------------------------------------------------------------------------------ 
Predicate Information (identified by operation id): 
------------------------------------------ 
* 2 - access(ROWID="RI") 
* 5 - filter("RN">1) 

2.

DELETE xtable WHERE (x, rowid) NOT IN (SELECT x, min(rowid) FROM xtable GROUP BY x) 

Exe time: 15 - 17 secs 

Plan: 
-------------------------------------------------------------------------------------- 
| Id | Operation     | Name | Rows | Bytes | Cost  | Time  | 
-------------------------------------------------------------------------------------- 
| 0 | DELETE STATEMENT   |  | 50000 | 150000 | 278162850 | 03:01:06 | 
| 1 | DELETE     | XTABLE |   |   |   |   | 
| 2 | FILTER     |  |   |   |   |   | 
| 3 |  TABLE ACCESS FULL  | XTABLE | 1000000 | 3000000 |  281 | 00:00:01 | 
| 4 |  FILTER    |  |   |   |   |   | 
| 5 |  SORT GROUP BY NOSORT |  | 1000000 | 3000000 |  280 | 00:00:01 | 
| 6 |  TABLE ACCESS FULL | XTABLE | 1000000 | 3000000 |  280 | 00:00:01 | 
-------------------------------------------------------------------------------------- 
Predicate Information (identified by operation id): 
------------------------------------------ 
* 5 - access(INTERNAL_FUNCTION("X")=INTERNAL_FUNCTION("X") AND INTERNAL_FUNCTION(ROWID)=INTERNAL_FUNCTION("MIN(ROWID)")) 
* 5 - filter(INTERNAL_FUNCTION(ROWID)=INTERNAL_FUNCTION("MIN(ROWID)") AND INTERNAL_FUNCTION("X")=INTERNAL_FUNCTION("X")) 

3。

DELETE xtable a WHERE EXISTS(select 1 FROM xtable b WHERE a.x = b.x AND a.rowid < b.rowid) 

Exe time: 970 - 990 sec 

Plan: 
---------------------------------------------------------------------------------------------- 
| Id | Operation      | Name | Rows | Bytes | Cost  | Time  | 
---------------------------------------------------------------------------------------------- 
| 0 | DELETE STATEMENT     |  | 50000 | 300000 | 278208956 | 03:01:08 | 
| 1 | DELETE       | XTABLE |   |   |   |   | 
| * 2 | FILTER      |  |   |   |   |   | 
| 3 |  NESTED LOOPS SEMI   |  | 50000 | 300000 | 278208956 | 03:01:08 | 
| 4 |  TABLE ACCESS FULL   | XTABLE | 1000000 | 3000000 |  280 | 00:00:01 | 
| * 5 |  TABLE ACCESS BY ROWID RANGE | XTABLE | 50000 | 150000 |  278 | 00:00:01 | 
----------------------------------------------------------------------------------------------  
Predicate Information (identified by operation id): 
------------------------------------------ 
* 2 - filter(:VAR2=:VAR1) 
* 5 - access("B".ROWID>"A".ROWID) 

计划被上Oracle 12.1.0.2.0

Answer 1

请解释为什么我不能在案件2

你不应该相信优化信任优化计划。 CBO是95％，但你不知道哪5％是错的。

典型问题是使用EXPLAIN PLAN显示的执行计划不等于执行使用的计划。 （你不说你如何获得这个计划）。

有疑问请使用DBMS_SQLTUNE.REPORT_SQL_MONITOR来查看实际计划和有问题的零件。

是什么导致优化器忽略线性和指数复杂度之间的明显差异？

看到上面，忘了计划的成本比较。在处理整个表时要避免的是处理NESTED LOOP。 这是情况3

| 3 |  NESTED LOOPS SEMI   |  | 50000| 300000 | 278208956 | 03:01:08| 
| 4 |  TABLE ACCESS FULL   |XTABLE | 1000000| 3000000 |  280 | 00:00:01| 
| 5 |  TABLE ACCESS BY ROWID RANGE |XTABLE | 50000| 150000 |  278 | 00:00:01| 

你想看排序和哈希加入这个是啥子计划1所示究竟发生了什么。

在我看来，计划2将与重复记录的数量不成比例（简单地尝试一个表，每行两次，看看你是否获得与案例3相同的经过时间）。 优化程序无法估计重复记录的数量，因此可以防御性地估计出高数量，因此成本高。

最后一个备注 - 理论说，你不应该观察线性特性但充其量O(n * log(n))。

最后的评论 - 您的测试数据对于删除dups不现实。典型的你有一个大的桌子，只有少数几个人。在你的设置中，除100以外的所有记录都是dups。

删除成本占主导地位找到dups的成本，所以你观察线性行为。

与

CREATE TABLE xtable(x) AS 
    SELECT ceil(dbms_random.value * 100000000) 
    FROM dual 
    CONNECT BY level <= 1000000; 

select count(*) total, count(*)- count(distinct x) to_be_deleted from xtable; 
    TOTAL TO_BE_DELETED 
---------- ------------- 
    1000000   5083 

努力让您将删除的记录0.5％。现在规模化，你会完全观察其他模式。

Answer 2

得到无法重现的第二个计划。这里说到：

-------------------------------------------------------------------------------------------          
| Id | Operation    | Name  | Rows | Bytes |TempSpc| Cost (%CPU)| Time  |          
-------------------------------------------------------------------------------------------          
| 0 | DELETE STATEMENT  |   |  |  |  | 3648 (100)|   |          
| 1 | DELETE    | XTABLE |  |  |  |   |   |          
| 2 | MERGE JOIN ANTI NA |   | 999K| 26M|  | 3648 (5)| 00:00:01 |          
| 3 | SORT JOIN   |   | 1000K| 2929K| 22M| 3147 (3)| 00:00:01 |          
| 4 |  TABLE ACCESS FULL | XTABLE | 1000K| 2929K|  | 434 (3)| 00:00:01 |          
|* 5 | SORT UNIQUE   |   | 100 | 2500 |  | 500 (16)| 00:00:01 |          
| 6 |  VIEW    | VW_NSO_1 | 100 | 2500 |  | 499 (16)| 00:00:01 |          
| 7 |  SORT GROUP BY  |   | 100 | 300 |  | 499 (16)| 00:00:01 |          
| 8 |  TABLE ACCESS FULL| XTABLE | 1000K| 2929K|  | 434 (3)| 00:00:01 |          
-------------------------------------------------------------------------------------------