sql - Oracle 性能 : query executing multiple identical function calls

Question

Oracle 是否可以在不使用函数结果缓存的情况下在同一查询(事务？)中调用函数时重用该函数的结果？

我正在使用的应用程序严重依赖 Oracle 函数。许多查询最终会多次执行完全相同的功能。

一个典型的例子是:

SELECT my_package.my_function(my_id),
       my_package.my_function(my_id) / 24,
       my_package.function_also_calling_my_function(my_id)
  FROM my_table
 WHERE my_table.id = my_id;

我注意到 Oracle 总是执行这些函数中的每一个，而没有意识到在一秒钟前在同一个查询中调用了同一个函数。函数中的某些元素可能会被缓存，从而导致返回速度稍快。这与我的问题无关，因为我想避免整个第二次或第三次执行。

假设这些函数相当消耗资源，并且这些函数可能会调用更多函数，结果基于相当大且更新频繁的表(一百万条记录，例如每小时更新 1000 次)。因此，无法使用 Oracle 的函数结果缓存。

尽管数据经常变化，但我希望这些函数在从同一个查询中调用时的结果是相同的。

Oracle 是否可以重用这些函数的结果以及如何重用？我正在使用 Oracle11g 和 Oracle12c。

下面是一个例子(只是一个随机的无意义函数来说明问题):

-- Takes 200 ms
SELECT test_package.testSpeed('STANDARD', 'REGEXP_COUNT')
  FROM dual;

-- Takes 400ms
SELECT test_package.testSpeed('STANDARD', 'REGEXP_COUNT')
     , test_package.testSpeed('STANDARD', 'REGEXP_COUNT')
  FROM dual;

使用的功能:

CREATE OR REPLACE PACKAGE test_package IS

FUNCTION testSpeed (p_package_name VARCHAR2, p_object_name VARCHAR2)
RETURN NUMBER;
END;
/

CREATE OR REPLACE PACKAGE BODY test_package IS

FUNCTION testSpeed (p_package_name VARCHAR2, p_object_name VARCHAR2)
RETURN NUMBER
IS

    ln_total NUMBER;

BEGIN

    SELECT SUM(position) INTO ln_total 
      FROM all_arguments 
     WHERE package_name = 'STANDARD' 
       AND object_name = 'REGEXP_COUNT';

    RETURN ln_total;

END testSpeed;

END;
/

Answer 1

添加内联 View 和 ROWNUM以防止 Oracle 将查询重新写入单个查询块并多次执行该功能。

问题示例函数及演示

create or replace function wait_1_second return number is
begin
    execute immediate 'begin dbms_lock.sleep(1); end;';
    -- ...
    -- Do something here to make caching impossible.
    -- ...
    return 1;
end;
/

--1 second
select wait_1_second() from dual;

--2 seconds
select wait_1_second(), wait_1_second() from dual;

--3 seconds
select wait_1_second(), wait_1_second() , wait_1_second() from dual;

不起作用的简单查询更改

这两种方法仍然需要 2 秒，而不是 1 秒。

select x, x
from
(
    select wait_1_second() x from dual
);

with execute_function as (select wait_1_second() x from dual)
select x, x from execute_function;

强制 Oracle 按特定顺序执行

很难告诉 Oracle“自行执行此代码，不要对其进行任何谓词推送、合并或其他转换”。这些优化中的每一个都有提示，但它们很难使用。有几种方法可以禁用这些转换，添加一个额外的 ROWNUM通常是最简单的。

--Only takes 1 second
select x, x
from
(
    select wait_1_second() x, rownum
    from dual
);

很难确切地看到函数在哪里被评估。但这些解释计划显示了 ROWNUM导致内联 View 单独运行。

explain plan for select x, x from (select wait_1_second() x from dual);
select * from table(dbms_xplan.display(format=>'basic'));

Plan hash value: 1388734953

---------------------------------
| Id  | Operation        | Name |
---------------------------------
|   0 | SELECT STATEMENT |      |
|   1 |  FAST DUAL       |      |
---------------------------------

explain plan for select x, x from (select wait_1_second() x, rownum from dual);
select * from table(dbms_xplan.display(format=>'basic'));

Plan hash value: 1143117158

---------------------------------
| Id  | Operation        | Name |
---------------------------------
|   0 | SELECT STATEMENT |      |
|   1 |  VIEW            |      |
|   2 |   COUNT          |      |
|   3 |    FAST DUAL     |      |
---------------------------------