执行计划中是否考虑了功能？

2024-06-26

当查询在 SELECT 或 WHERE 子句中包含 PL/SQL 函数（用户定义函数）时，如何生成执行计划？它是否也计算这些函数的成本并将其显示在执行计划中，或者这些函数只是被忽略？

在此先感谢您的帮助。

用户生成的函数在 SELECT 或 WHERE 子句中使用时直接贡献很少或没有成本。如果我们希望优化器根据函数的成本做出决策，我们必须手动设置成本相关统计数据 https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ASSOCIATE-STATISTICS.html#GUID-BD02BA6A-32A7-4093-A6B6-BAE860C0F834命令。

示例架构

对于此示例，创建以下中等大小的表和两个简单的函数 - 一个明显快，另一个明显慢。

create table test1 as
select mod(level, 10) a, mod(level, 10) b
from dual
connect by level <= 100000;

begin
    dbms_stats.gather_table_stats(user, 'test1');
end;
/

create or replace function fast_function(p_number number) return number is
begin
    return p_number;
end;
/

create or replace function slow_function(p_number number) return number is
    v_count number;
begin
    select count(*)
    into v_count
    from all_tables;
    return v_count;
end;
/

SELECT 子句中的函数 - 无成本

在 SELECT 子句中调用该函数根本不会改变成本。下面的三个查询选择一个文字、快速函数和慢速函数：

explain plan for select a from test1;
select * from table(dbms_xplan.display);

explain plan for select fast_function(a) from test1;
select * from table(dbms_xplan.display);

explain plan for select slow_function(a) from test1;
select * from table(dbms_xplan.display);

但所有查询都会生成相同的执行计划：

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |   100K|   292K|    47   (3)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEST1 |   100K|   292K|    47   (3)| 00:00:01 |
---------------------------------------------------------------------------

WHERE 子句中的函数 - 成本很低

当调用 WHERE 子句中的函数而不是文字时，成本从 48 略微增加到 70。但是快速函数和慢速函数之间没有成本差异。

explain plan for select * from test1 where a = b;
select * from table(dbms_xplan.display);

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       | 10000 | 60000 |    48   (5)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST1 | 10000 | 60000 |    48   (5)| 00:00:01 |
---------------------------------------------------------------------------

explain plan for select * from test1 where fast_function(a) = b;
select * from table(dbms_xplan.display);

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       | 10000 | 60000 |    70  (35)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST1 | 10000 | 60000 |    70  (35)| 00:00:01 |
---------------------------------------------------------------------------

explain plan for select * from test1 where slow_function(a) = b;
select * from table(dbms_xplan.display);

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       | 10000 | 60000 |    70  (35)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST1 | 10000 | 60000 |    70  (35)| 00:00:01 |
---------------------------------------------------------------------------

选择性

尽管名称为“基于成本的优化器”，但我们可能应该更担心基数而不是成本。谓词返回的行数决定了大多数执行计划的选择。 Oracle 对用户定义的函数做出一些默认猜测。例如，在下面的查询中，Oracle 假设该函数仅满足 1% 的行 - 这就是执行计划中的“行”显示 1000 而不是 100000 的原因。

explain plan for select * from test1 where fast_function(a) = 1;
select * from table(dbms_xplan.display);

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |  1000 |  6000 |    70  (35)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST1 |  1000 |  6000 |    70  (35)| 00:00:01 |
---------------------------------------------------------------------------

如果我们知道该函数更具选择性，例如，如果我们知道该函数更有可能只匹配所有行的 0.1%，我们还可以使用ASSOCIATE STATISTICS设置默认选择性。以下命令设置选择性，然后行数从 1000 下降到 100。

associate statistics with functions fast_function default selectivity 0.1;
explain plan for select * from test1 where fast_function(a) = 1;
select * from table(dbms_xplan.display);

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |   100 |   600 |    70  (35)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST1 |   100 |   600 |    70  (35)| 00:00:01 |
---------------------------------------------------------------------------

在我们的简单计划中，基数并不重要。但在实际查询中，糟糕的基数估计会导致错误决策的连锁反应，从而导致查询速度变慢。帮助优化器做出良好的基数估计通常是性能调优中最重要的部分。

其他类型的函数和统计

这个已经很长的答案仍然只触及函数如何影响执行计划的表面。表函数（返回数据行的函数）完全是另一个主题。我敢打赌，较新的 Oracle 版本中存在动态重新优化功能，在优化器从错误中吸取教训后，这将有助于改进第二次或第三次执行。

我希望我没有阻止您使用自定义函数。绝大多数时候，Oracle会毫不费力地做出正确的决策。如果没有，有一些机制可以帮助纠正这些错误。

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)