gpt4 book ai didi

oracle - 为什么优化器计划与实验性查询运行不相关?

转载 作者:行者123 更新时间:2023-12-02 01:24:00 26 4
gpt4 key购买 nike

假设我们有以下问题:

  • 给定一个包含一列 'X' 的表,其中包含一些具有随机值的行从 1 到 100 的整数:

    CREATE TABLE xtable(x) AS 
    SELECT ceil(dbms_random.value * 100)
    FROM dual
    CONNECT BY level <= 1000000;
  • 我们必须删除重复的行,以便所有不同的整数保留在表中。


让我们考虑下面的三个解决方案(平均执行时间和优化器计划)。

我必须补充一点,实验表明:

  • 解决方案 1 和 2 是可扩展的,并且随着每个行数量的增加呈线性时间增长(针对多达 1000 万行的表进行了测试)
  • 解决方案 3 的时间呈指数增长,大约像 3 * exp(0.6 * N)

我们看到对于solution 2 optimizer plan给出了与实验结果无关的期望,甚至与他们相反:

  • 成本和其他值(value)在计划 2 和 3 中几乎相同
  • 解决方案 1 和 2 的执行时间几乎相同

And in this experiments the presence or absence of gathered statistics for the table doesn't affect optimizer plans and execution times.


请解释为什么我不能相信情况 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 上获得的

最佳答案

Please, explain why I can't trust the optimizer plan in case 2.

你永远不应该相信优化器。 CBO 95% 正确,但你不知道哪 5% 是错误的。

典型问题是使用EXPLAIN PLAN 显示的执行计划与执行使用的计划不相同。 (你没有说你是如何获得计划的)。

有疑问使用DBMS_SQLTUNE.REPORT_SQL_MONITOR对于长期运行的查询,以查看实际计划和有问题的部分。

What causes the optimizer to ignore the obvious difference between linear and exponential complexity?

看到上面并忘记计划的成本比较。在处理整个表 时要避免的是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|

您想查看 SORT 和 HASH JOIN,这是计划 1 显示的内容。

在我看来,计划 2 不会随着重复记录的数量缩放(简单地尝试每行两次的表,看看你是否得到与案例 3 相同的耗时) .优化器无法估计重复记录的数量,因此防御性地估计了很多,因此成本也很高。

最后但要注意一点 - 该理论说您不应该观察到线性行为,但最好是 O(n * log(n))

最后的评论 - 您的测试数据对于去除重复数据是不现实的。通常,您有一张大 table ,上面有少量重复项。在您的设置中,除 100 条以外的所有记录都是重复的。

删除的成本主导了查找重复项的成本,因此您可以观察到线性行为。

试试

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% 的记录。现在缩放,您将观察到完全不同的模式。

关于oracle - 为什么优化器计划与实验性查询运行不相关?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/38198324/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com