小心隐藏的PLSQL到SQL上下文开关

最近，我偶然发现了一个关于客户高效的Oracle数据库的奇怪的查询：

SELECT USER FROM SYS.DUAL

有两件事引起了我的注意：

• 该查询每月执行数十亿次，约占该系统负载的0.3%。这是0.3%的东西非常愚蠢！
• 我不认为顾客有资格DUALSYS.DUAL，这暗示了一些系统功能。

 

SELECT 

  sql_id, 

  executions, 

  elapsed_time, 

  ratio_to_report(elapsed_time) over() p, 

  sql_text

FROM v$sql

ORDER BY p DESC;

为什么这个查询经常运行？STANDARD.USER(我还不知道在哪里可以手动在字典视图中找到这些信息)。

天真地，我一直以为USER伪列或伪常量是上下文中的一些值，但与许多其他函数一样，它实际上只是包中的一个函数。

 

现在，我还不能百分之百确定这些源代码是否允许从法律的角度进行复制，这就是甲骨文等等。但是，如果您在这里运行这个查询，我可以自由地允许您：

WITH s AS (

  SELECT s.*,

    MIN(CASE 

      WHEN upper(text) LIKE '%FUNCTION USER%' 

      THEN line END

    ) OVER () s

  FROM all_source s

  WHERE owner = 'SYS' 

  AND name = 'STANDARD'

  AND type = 'PACKAGE BODY'

)

SELECT text

FROM s

WHERE line >= s AND line < s + 6;

然后你可能会看到这样的东西：

  function USER return varchar2 is

  c varchar2(255);

  begin

        select user into c from sys.dual;

        return c;

  end;

这只是我向您展示的一些SQL查询的结果。任何与实际源代码的对应仅仅是巧合。

让我们假设曾.STANDARD.USER()功能。想核实一下吗？

让我们来做基准

和往常一样，我使用的是所描述的基准技术这里。这，这个，那，那个全基准逻辑在文章的末尾。

 

FOR i IN 1 .. v_repeat LOOP

  v := USER;

END LOOP;

用这个：

FOR i IN 1 .. v_repeat LOOP

  SELECT USER INTO v FROM dual;

END LOOP;

而这个：

FOR i IN 1 .. v_repeat LOOP

  v := sys_context('USERENV', 'CURRENT_USER');

END LOOP;

这一基准的结果是：

Run 1, Statement 1 : 2.40509 (avg : 2.43158)

Run 1, Statement 2 : 2.13208 (avg : 2.11816)

Run 1, Statement 3 : 1.01452 (avg : 1.02081)

Run 2, Statement 1 : 2.41889 (avg : 2.43158)

Run 2, Statement 2 : 2.09753 (avg : 2.11816)

Run 2, Statement 3 : 1.00203 (avg : 1.02081)

Run 3, Statement 1 : 2.45384 (avg : 2.43158)

Run 3, Statement 2 : 2.09060 (avg : 2.11816)

Run 3, Statement 3 : 1.02239 (avg : 1.02081)

Run 4, Statement 1 : 2.39516 (avg : 2.43158)

Run 4, Statement 2 : 2.14140 (avg : 2.11816)

Run 4, Statement 3 : 1.06512 (avg : 1.02081)

Run 5, Statement 1 : 2.48493 (avg : 2.43158)

Run 5, Statement 2 : 2.12922 (avg : 2.11816)

Run 5, Statement 3 : 1.00000 (avg : 1.02081)

如何读取这个基准测试结果？这些不是实际的时间，也不是有趣的时间，而是相对于最快的运行时间(运行5，语句3=1)。显式SELECT USER FROM DUALSYS_CONTEXT呼叫，和USER打电话也有点慢。

当重新运行此查询时：

SELECT 

  sql_id, 

  executions, 

  ratio_to_report(elapsed_time) over() p, 

  sql_text

FROM v$sql

ORDER BY p DESC;

我们可以看到：

SQL_ID          EXECUTIONS  P     SQL_TEXT

6r9s58qfu339c   1           0.26  DECLARE ...

1v717nvrhgbn9   2500000     0.14  SELECT USER FROM SYS.DUAL

...

因此，这个查询肯定运行了太多次，包括涉及的PL/SQL到SQL上下文切换。

我正在Windows机器上的Docker中运行Oracle 18.0.0.0.0中的这个基准测试。更接近金属和较少虚拟化的设置可能会取得更激烈的结果。看，康纳·麦克唐纳从使用中得到了更好的改善SYS_CONTEXT:

在这种情况下，STANDARD.USER()引用经常在触发器中使用，以填充许多表的审计列。修起来很容易。只管用sys_context('USERENV', 'CURRENT_USER')相反。

全基准逻辑

SET SERVEROUTPUT ON

ALTER SYSTEM FLUSH SHARED_POOL;

ALTER SYSTEM FLUSH BUFFER_CACHE;

CREATE TABLE results (

  run     NUMBER(2),

  stmt    NUMBER(2),

  elapsed NUMBER

);

DECLARE

  v_ts TIMESTAMP WITH TIME ZONE;

  v_repeat CONSTANT NUMBER := 500000;

  v NUMBER;

BEGIN

  -- Repeat the whole benchmark several times to 

  -- avoid warmup penalty

  FOR r IN 1..5 LOOP

    v_ts := SYSTIMESTAMP;

    FOR i IN 1 .. v_repeat LOOP

      v := v + length(USER);

    END LOOP;

    INSERT INTO results VALUES (r, 1, 

      SYSDATE + ((SYSTIMESTAMP - v_ts) * 86400) - SYSDATE);

    v_ts := SYSTIMESTAMP;

    FOR i IN 1 .. v_repeat LOOP

      SELECT v + length(USER) INTO v FROM dual;

    END LOOP;

    INSERT INTO results VALUES (r, 2, 

      SYSDATE + ((SYSTIMESTAMP - v_ts) * 86400) - SYSDATE);

    v_ts := SYSTIMESTAMP;

    FOR i IN 1 .. v_repeat LOOP

      v := v + length(sys_context('USERENV', 'CURRENT_USER'));

    END LOOP;

    INSERT INTO results VALUES (r, 3, 

      SYSDATE + ((SYSTIMESTAMP - v_ts) * 86400) - SYSDATE);

  END LOOP;

  FOR rec IN (

    SELECT 

      run, stmt, 

      CAST(elapsed / MIN(elapsed) OVER() AS NUMBER(10, 5)) ratio,

      CAST(AVG(elapsed) OVER (PARTITION BY stmt) / 

           MIN(elapsed) OVER() AS NUMBER(10, 5)) avg_ratio

    FROM results

    ORDER BY run, stmt

  )

  LOOP

    dbms_output.put_line('Run ' || rec.run || 

      ', Statement ' || rec.stmt || 

      ' : ' || rec.ratio || ' (avg : ' || rec.avg_ratio || ')');

  END LOOP;

  dbms_output.put_line('');

  dbms_output.put_line('Copyright Data Geekery GmbH');

  dbms_output.put_line('https://www.jooq.org/benchmark');

END;

/

DROP TABLE results;

谢谢你的阅读，并让我知道在评论部分的任何问题或想法。