seq搜索引擎优化至少包括那几步?(如何演示优化器可能产生不好的基数估计的几种方式)
优采云 发布时间: 2021-11-03 15:23seq搜索引擎优化至少包括那几步?(如何演示优化器可能产生不好的基数估计的几种方式)
原创来源:... d-optimizer-part-6/
在本系列的第 5 部分中,我开始梳理查询列表,旨在展示优化器可能产生不良基数估计的几种方式。在本文中,我将整理列表的其余部分。剩下的查询如下:
select count(*) from t1 where trunc_5> (
选择 max(trunc_5) from t1 where mod_200 = 0
);
select count(*) from t1 where Five_per_sec> sysdate-60/86400;
执行 dbms_lock.sleep(10)
select count(*) from t1 where Five_per_sec> sysdate-10/86400;
变量 b1 数
执行:b1:= 10
select count(*) from t1 where skewed = :b1;
执行:b1:= 100
select count(*) from t1 where skewed = :b1;
第一个查询表明优化器最喜欢的猜测之一又出现了——5%——这通常用于估计子查询的影响。下一对(中间有 10 秒的睡眠时间)展示了即将到期的统计数据的潜在灾难性副作用。最后一对向我们介绍了绑定变量和偏斜数据分布的一些问题。
请记住,我所有的演示示例都在 11.2.0.4 上运行,到目前为止,查询引用的任何列都没有生成直方图。
子查询效果
这是我们清单中第一个查询的默认执行计划:
-------------------------------------------------- ---------------------------
| 编号| 操作 | 姓名 | 行| 字节 | 成本 (%CPU)| 时间 |
-------------------------------------------------- ---------------------------
| 0 | 选择语句 | | | |1308 (100)| |
| 1 |排序聚合 | | 1 | 5 | | |
|*2 | 表访问已满| T1 | 50000 | 244K| 658 (9)| 00:00:04 |
| 3 | 排序聚合 | | 1 | 9 | | |
|*4 | 表访问已满| T1 |5000 | 45000 | 650 (8)| 00:00:04 |
-------------------------------------------------- ---------------------------
谓词信息(由操作 id 标识):
-------------------------------------------------- ——
2-过滤器("TRUNC_5">)
4-filter("MOD_200"=0)
查询确定了 195 行,但预测为 50,000,这是原创 1,000,000 行的 5%。
在这个简单的例子中,这个错误实际上是无关紧要的,但是你可以想象在一个更复杂的查询中——一个将第二行连接到另一个表的查询——估计 50,000 会鼓励优化器做一个散列连接到下一个表,并且更接近实际值 195 的估计可能会鼓励优化器执行嵌套循环连接。
当然,你也可以简单的先制定出最好的计划,然后创建一套完整的提示来强制执行计划(得到你想要的计划后,你可以通过生成一个等效的SQL计划基线来提高提示的健壮性)。如果查询足够简单,您可以通过在主查询块中填写 /*+leading() */ 提示和在子查询中填写 /*+ no_unnest push_subq */ 来逃避。
另外,如果您有性能包的许可证,您可以使用 DBMS_SQLTUNE 包(可能通过 OEM 的图形界面)创建 SQL 配置文件以进行查询。这将执行查询并发现默认的 5% 是一个错误的估计,并且可以创建一组“opt_estimate()”提示来帮助优化器计算更好的估计,这应该会导致选择更好的计划。
最后,您可能很幸运地向 SQL 添加了一个“简单”的 cardinality() 提示,允许优化器在开始时有一个正确的估计,并允许它为计划的其余部分生成合理的估计。不幸的是,cardinality() 提示不是很受支持,没有真正记录下来,并且比 Internet 上的一些评论所建议的更微妙(因此更难使用)。
在我的小例子中,我可以简单地将提示 /*+ cardinality(t1 195) */ 添加到 SQL:
选择
/*+ 基数(t1 195) */
数数(*)
从 t1
其中 trunc_5> (
选择 max(trunc_5) from t1 where mod_200 = 0
)
;
-------------------------------------------------- ---------------------------
| 编号| 操作 | 姓名 | 行| 字节 | 成本 (%CPU)| 时间 |
-------------------------------------------------- ---------------------------
| 0 | 选择语句 | | | |1308 (100)| |
| 1 |排序聚合 | | 1 | 5 | | |
|*2 | 表访问已满| T1 | 195 | 975 | 658 (9)| 00:00:04 |
| 3 | 排序聚合 | | 1 | 9 | | |
|*4 | 表访问已满| T1 |5000 | 45000 | 650 (8)| 00:00:04 |
-------------------------------------------------- ---------------------------
这不会改变查询的成本,但它已根据需要修改了第二行中的基数估计。Oracle内部并没有使用cardinality()——如果你查看优化器的trace文件(10053事件),你会看到提示已经翻译成如下形式的opt_estimate()提示:
opt_estimate(table t1@sel$1 行=195.000000)
但是,与所有“微调”提示一样,您通常需要比原先想象的更多的基数提示。因为它表示这个提示是“单表访问”提示,但是在更复杂的情况下,你可能希望收录大量的提示来表达这个意思:“如果你从T1开始,它会给你195行,但是如果你从T2开始,它会给你5000行”,然后你可能需要使用“连接基数”版本的提示来表达这样的东西:“如果你从T1开始 -> T2,这个连接会给你 800 行,但 T1 -> T3 会给你 650 行”。一般来说,尝试找到所有您需要的基数提示以确保您需要的计划会出现是有点痛苦的。
随着时间的推移
为了运行我的测试,我创建了数据,采集了统计信息,然后执行了几个不同的查询,并从内存中获取了它们的执行计划。当我执行将 Five_per_sec 列与“SYSDATE-60 秒”进行比较的查询时,在创建数据和运行查询之间已经过了几秒钟。Five_per_sec时间部分的最高值是17:15:17,我执行查询的时间已经移动到了17:15:31,有14秒的差距——这意味着表中有46秒的数据匹配谓词“five_per_sec> sysdate – 60/86400”。这是我运行查询时得到的执行计划:
-------------------------------------------------- -------------------------
| 编号| 操作 | 姓名 | 行| 字节 | 成本 (%CPU)| 时间 |
-------------------------------------------------- -------------------------
| 0 | 选择语句 | | | | 752 (100)| |
| 1 |排序聚合 | | 1 | 8 | | |
|*2 | 表访问已满| T1 | 230 |1840 | 752(21)| 00:00:04 |
-------------------------------------------------- -------------------------
如您所见,优化器的估计是 230 行——46 秒的完美匹配,每秒 5 行。
但是如果我再等10秒(现在时间是17:15:41),然后查询sysdate – 10/86400,会发生什么?我查询的数据晚于17:15:31,没有与表中谓词匹配的数据,但是优化器期望找到什么:
-------------------------------------------------- -------------------------
| 编号| 操作 | 姓名 | 行| 字节 | 成本 (%CPU)| 时间 |
-------------------------------------------------- -------------------------
| 0 | 选择语句 | | | | 752 (100)| |
| 1 |排序聚合 | | 1 | 8 | | |
|*2 | 表访问已满| T1 | 5 | 40 | 752(21)| 00:00:04 |
-------------------------------------------------- -------------------------
优化器的估计是 5 行。一些额*敏*感*词*的谓词执行查询,优化器可以看到所需的数据完全落在一个已知的低值/高值值范围,则公式将返回“列=常量”。
时间的流逝案例研究
我的模型中的错误还不算太糟糕——如果我测试时数据继续以同样的速度到达,那么最后10秒会有50行,所以5不会是(不一定是)一个糟糕的估计可能不会引起问题。但是这种基本行为可能会产生可怕的影响,如以下产品示例所示。
数据到达率为每分钟 300 行(每秒 5 行)。有一个非常重要的轮询作业,它每隔几分钟执行一次查询,以确定最近15分钟(并且仍处于某种状态)接收到的数据——通常识别出大约2000到4000行数据。查询相当简单,包括关键时间谓词:
其中到达时间> sysdate – 1/96
由于数据变化的速度,自动统计采集作业每晚都会在这张表上运行,因此其统计的准确性与简单的默认配置一样准确。
每隔几天——似乎完全是随机的——这个轮询作业就会遇到一个可怕的性能问题,执行一次需要几分钟而不是几秒钟。毫不奇怪,这已经对业务运营产生了影响,用户对系统的看法会大不相同。这是怎么回事?
仅在采集统计信息 15 分钟后,优化器对查询的估计将从几千个下降到仅 5 个(因为任何给定的到达时间平均有 5 行数据)。如果发生这种情况,计划将发生灾难性的变化,因为优化器确定可以找到一个好的策略来处理这 5 行,而这对于真正存在的数千行数据来说是一个非常糟糕的策略。
查询每隔几分钟执行一次,因此通常会在采集统计信息后不久重新优化 - 生成良好的执行计划。大多数日子里,游标将保留在内存中,并在一天的剩余时间里不断重复使用;所以优化器永远不需要再次优化语句,也没有机会产生一个糟糕的计划。不幸的是,这种情况偶尔会发生,并且共享池中的内存需求量很大。把这个好的计划从内存中洗掉,在采集到统计信息几个小时后重新优化查询。生成了“只有 5 行”糟糕的计划——然后查询在第二天之前表现不佳,或者他们采集了这个表的统计信息(客户有一天发现了)。
基于时间(或顺序)的列可能会导致各种奇怪的问题——尽管后果是你可能只有在你不走运的情况下才会受苦;但是如果你有这样的代码,它总是在寻找“最后几行数据”,这是一个需要注意的问题。如果你看到这个问题,可以遵循三个基本策略:
通过(或 SQL 计划基线/存储大纲)修复计划。
设置一个可能经常(在这种情况下每 5 分钟一次)采集此键列的统计信息并使所有相关游标无效的作业。
编写一些代码并调用 dbms_stats.set_column_stats() 将列统计信息中的高值设置为未来值。这个值必须足够远,以避免计划中的灾难性变化(但不能太远,以防止优化器在相反方向上出现错误)。这实际上只是第二种方法的一个变种,但是 (一) 执行起来更便宜,而且 (二) 你可能能够以比采集统计工作低得多的频率执行它。
绑定变量
列表中的最后两个查询引入了绑定变量的问题。我们将在本系列的后续章节中更详细地访问这个主题,我在这里唯一想说的是,倾斜数据模式和绑定变量不能合并——你必须帮助优化器。
看数据的生成方式,我们可以计算出这两个查询的第一个应该返回21行,第二个应该返回201行(“skewed = N”数据一共有2N + 1),但在这两种情况下,执行计划预计都是 1000 行:
-------------------------------------------------- -------------------------
| 编号| 操作 | 姓名 | 行| 字节 | 成本 (%CPU)| 时间 |
-------------------------------------------------- -------------------------
| 0 | 选择语句 | | | | 666 (100)| |
| 1 |排序聚合 | | 1 | 4 | | |
|*2 | 表访问已满| T1 |1000 |4000 | 666(11)| 00:00:04 |
-------------------------------------------------- -------------------------
谓词信息(由操作 id 标识):
-------------------------------------------------- ——
2-过滤器(“倾斜”=:B1)
这是因为我们没有做任何事情让优化器知道数据值的分布是极其不均匀的;所以现有的统计数据只是告诉它总共有 1,000,000 行和 1000 个不同的值,所以任何值都将返回 1,000,000/1000=1000 行。即使我们在查询中使用字面常量值 10 和 100 而不是使用绑定变量,我们也会得到相同的结果。
为了帮助 Oracle 理解这种倾斜的数据,我们必须在列上创建一个直方图。不幸的是我在列中有 1000 个不同的值,而在 11g 中一个直方图最多可以收录 255 行(相当于 254 个桶),因此 Oracle 被迫在列上生成轮廓直方图。事实上,虽然我们可以看到,最稀有和最常见的数据之间的变化是巨大的(skewed=0 有一行,skewed=999 有 1999 行),但如果允许“自动检测”是否需要,Oracle甚至没有创建直方图。如果我们强制Oracle创建254个桶的直方图,它不会捕获任何公共值(1M行数据只有254个桶,一个值必须出现在4000行左右才能注册为公共值,如果你很倒霉,即使出现在大约 8000 行上也不会被注意到)。
为了继续演示,我切换到 12c。如果我再次告诉 Oracle 使用“自动大小”来采集列上的直方图,它会决定使用 254 个桶,然后决定不值得。但是,在 12c 中,直方图中最多可以有 2048 行。当我告诉 Oracle 使用最大值时,它将为列中的 1000 个值中的每个值创建一个频率直方图,其中一行。然后我得到了两个查询的计划如下:
-------------------------------------------------- -------------------------
| 编号| 操作 | 姓名 | 行| 字节 | 成本 (%CPU)| 时间 |
-------------------------------------------------- -------------------------
| 0 | 选择语句 | | | | 665 (100)| |
| 1 |排序聚合 | | 1 | 4 | | |
|*2 | 表访问已满| T1 | 21 | 84 | 665(11)| 00:00:01 |
-------------------------------------------------- -------------------------
谓词信息(由操作 id 标识):
-------------------------------------------------- ——
2-过滤器(“倾斜”=:B1)
这两个查询的计划保持不变,但它至少在第一次执行时预测正确的行数。优化器有办法做到这一点,因为查询必须在第一次执行时进行优化,而优化器“偷窥”绑定变量的实际值来帮助它选择一个计划,因此将查询优化为谓词看起来像“skewed = 10”,直方图中的信息显示有21行的值为10。
不幸的是,当我们在绑定变量中使用一个新值来执行查询时,优化器识别出它最近看到的查询文本完全相同,并发现它在内存中具有该查询的合法执行计划。只是简单的计划被重用。
绑定变量窥视和执行计划的重用是有益的,因为我们不必在每次执行查询时争用资源并用完资源来优化它;但是,当数据分布如此不均匀时,生成不同输入值的结果集大小会有显着差异,计划的重用成为一个弊端。我们可能需要重新优化看起来相同但本质上非常不同的语句。
绑定变量和直方图之间的冲突是一个长期存在的问题,Oracle 在过去 20 年中推出了许多策略来限制这种冲突的破坏性副作用;然而,应用程序开发者最终需要意识到这种威胁,并确定在什么特殊情况下,需要让数据库和客户端代码协同工作,以应对极度倾斜的数据分布。
总结
我介绍了优化的三个“难”问题。第一个是子查询引入的 5%“固定猜测值”。这并不容易解决,除非您准备提供实际执行路径的提示,或使用未记录的 cardinality() 提示,或使用优化工具生成 SQL 摘要(一系列 opt_estimate() 提示)。
第二个问题是基于时间或序列值的列可能会引入随机不稳定,如果最重要的查询是找到“最新数据”。当查询使用“超出范围”值时,用于优化基于范围的谓词的优化器算法可能会导致基数估计非常差,从而导致执行计划不佳。要么使用提示(或创建 SQL 计划基线)来获取所需的执行计划,要么采取一些措施将键列的 HIGH_VALUE 保持在适当的最近(或未来)值,以便优化器重新优化查询 正确的计划始终可用。
最后,我们必须意识到绑定变量和倾斜数据模式不能合并——即使存在直方图也是如此。我们将在以后的章节中更多地讨论这个,但是目前我们已经注意到,如果相同的查询文本可能会产生极其不同的数据量,那么您可能需要在关键列上创建直方图,但是您需要确保您的代码与数据库一起工作(例如在关键谓词中使用文字常量)以最有效地利用直方图。