Oracle性能：查询执行多个相同的函数调用-Java 学习之路

在不使用函数结果缓存的情况下，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;
/

3 回答

添加内联视图和 ROWNUM 以防止Oracle将查询重写为单个查询块并多次执行这些函数 .

Sample function and demonstration of the problem

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;

Simple query changes that do NOT work

这两种方法都需要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;

Forcing Oracle to execute in a specific order

很难告诉Oracle "execute this code by itself, don't do any predicate pushing, merging, or other transformations on it" . 每个优化都有提示，但它们很难使用 . 有几种方法可以禁用这些转换，添加额外的 ROWNUM 通常是最简单的方法 .

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

很难确切地看到函数的评估位置 . 但是这些解释计划显示 ROWNUM 如何导致内联视图单独运行 .

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     |      |
---------------------------------

回复于 2024-04-28T12:16:09+08:00

4
您可以尝试 deterministic 关键字将函数标记为纯 . 然而，这是否真的改善了性能是另一个问题 .

Update:

我不知道上面的例子是多么逼真，但理论上你总是可以尝试重新构造你的SQL，以便它知道重复的函数调用（实际重复的值） . 有一些像
```
select x,x from (
    SELECT test_package.testSpeed('STANDARD', 'REGEXP_COUNT') x
      FROM dual
)
```
回复于 2024-04-28T12:16:09+08:00

使用内嵌视图 .

with get_functions as(
SELECT my_package.my_function(my_id) as func_val,
   my_package.function_also_calling_my_function(my_id) func_val_2
  FROM my_table
 WHERE my_table.id = my_id
)
select func_val,
   func_val / 24 as func_val_adj,
   func_val_2
from get_functions;

如果要取消对项目3的调用，请将func_val的结果传递给第三个函数 .

回复于 2024-04-28T12:16:09+08:00

Oracle性能：查询执行多个相同的函数调用

3 回答

相关问题