sql - Oracle 中临时数据的性能注意事项

Question

我正在评估各种选项，以针对 Oracle 中的单个临时数据集运行一堆高性能查询。在 T-SQL 中，我可能会使用内存临时表，但 Oracle 没有与此功能完全相同的功能。

我目前看到以下选项:

1。全局临时表

CREATE GLOBAL TEMPORARY TABLE test_temp_t (
  n NUMBER(10), 
  s VARCHAR2(10)
) ON COMMIT DELETE ROWS; -- Other configurations are possible, too

DECLARE
  t test_t;
  n NUMBER(10);
BEGIN

  -- Replace this with the actual temporary data set generation
  INSERT INTO test_temp_t
  SELECT MOD(level, 10), '' || MOD(level, 12)
  FROM dual
  CONNECT BY level < 1000000;

  -- Replace this example query with more interesting statistics
  SELECT COUNT(DISTINCT t.n)
  INTO n 
  FROM test_temp_t t;

  DBMS_OUTPUT.PUT_LINE(n);
END;

计划:

----------------------------------------------------
| Id  | Operation            | A-Rows |   A-Time   |
----------------------------------------------------
|   0 | SELECT STATEMENT     |      1 |00:00:00.27 |
|   1 |  SORT AGGREGATE      |      1 |00:00:00.27 |
|   2 |   VIEW               |     10 |00:00:00.27 |
|   3 |    HASH GROUP BY     |     10 |00:00:00.27 |
|   4 |     TABLE ACCESS FULL|    999K|00:00:00.11 |
----------------------------------------------------

2。 PL/SQL 表类型变量的取消嵌套

CREATE TYPE test_o AS OBJECT (n NUMBER(10), s VARCHAR2(10));
CREATE TYPE test_t AS TABLE OF test_o;

DECLARE
  t test_t;
  n NUMBER(10);
BEGIN

  -- Replace this with the actual temporary data set generation
  SELECT test_o(MOD(level, 10), '' || MOD(level, 12))
  BULK COLLECT INTO t
  FROM dual
  CONNECT BY level < 1000000;

  -- Replace this example query with more interesting statistics
  SELECT COUNT(DISTINCT n)
  INTO n 
  FROM TABLE(t) t;

  DBMS_OUTPUT.PUT_LINE(n);
END;

计划:

------------------------------------------------------------------
| Id  | Operation                          | A-Rows |   A-Time   |
------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |      1 |00:00:00.68 |
|   1 |  SORT GROUP BY                     |      1 |00:00:00.68 |
|   2 |   COLLECTION ITERATOR PICKLER FETCH|    999K|00:00:00.22 |
------------------------------------------------------------------

3。物化 View

我在这个用例中排除了它们，因为所讨论的临时数据集相当复杂，并且对更新物化 View 的影响太大。

真实数据考虑因素

以上是我正在尝试做的事情的示例。真实数据集包括:

• 临时数据是来自大约 15 个连接表的非规范化数据。
• 生成速度约为 2-20x/秒。
• 每个临时数据集的实际行数约为 10-200(没有上面示例中的那么大)。
• 系统的每个用户都有自己的临时数据集(总共 100 万用户，10k 并发用户)。
• 建立数据集后，应对其运行大约 10-50 个分析查询。
• 这些分析必须在线运行，即不能推迟到批处理作业。

问题

根据我的直觉，临时表查询“应该”会更慢，因为它(可能)涉及 I/O 和磁盘访问，而 PL/SQL 集合查询只是一个内存中解决方案。但在我的简单基准测试中，情况并非如此，因为临时表查询比 PL/SQL 集合查询高出 3 倍。为什么会这样呢？是否发生了一些 PL/SQL <-> SQL 上下文切换？

我是否有其他选项可以对明确定义的临时数据集进行快速(但广泛)“内存中”数据分析？是否有任何重要的公开基准可以比较各种选项？

Answer 1

由于缓存和异步 I/O，临时表实际上与内存表相同，并且临时表解决方案不需要任何 SQL 和 PL/SQL 之间转换的开销。

确认结果

将两个版本与 RunStats 进行比较，临时表版本看起来要差得多。 Run1 中临时表版本的所有垃圾，以及 Run2 中 PL/SQL 版本的一点额外内存。乍一看，PL/SQL 似乎应该是明显的赢家。

Type  Name                              Run1 (temp) Run2 (PLSQL)         Diff
----- -------------------------------- ------------ ------------ ------------
...
STAT  physical read bytes                    81,920            0      -81,920
STAT  physical read total bytes              81,920            0      -81,920
LATCH cache buffers chains                  104,663          462     -104,201
STAT  session uga memory                    445,488      681,016      235,528
STAT  KTFB alloc space (block)            2,097,152            0   -2,097,152
STAT  undo change vector size             2,350,188            0   -2,350,188
STAT  redo size                           2,804,516            0   -2,804,516
STAT  temp space allocated (bytes)       12,582,912            0  -12,582,912
STAT  table scan rows gotten             15,499,845            0  -15,499,845
STAT  session pga memory                    196,608   19,857,408   19,660,800
STAT  logical read bytes from cache     299,958,272            0 -299,958,272

但归根结底，只有挂钟时间才重要。使用临时表，加载和查询步骤都运行得更快。

可以通过将 BULK COLLECT 替换为 cast(collect(test_o(MOD(a, 10), '' || MOD(a, 12)) 来改进 PL/SQL 版本)) 作为 test_t) INTO t。但它仍然比临时表版本慢得多。

优化读取

从小临时表中读取数据仅使用缓冲区高速缓存，缓冲区高速缓存位于内存中。仅多次运行查询部分，并观察从缓存中获取的一致性(内存)如何增加，而物理读取缓存(磁盘)保持不变。

select name, value
from v$sysstat
where name in ('db block gets from cache', 'consistent gets from cache', 
'physical reads cache');

优化写入

理想情况下不会有物理 I/O，特别是因为临时表是ON COMMIT DELETE ROWS。而且听起来Oracle的下一个版本可能会引入这样的机制。但在这种情况下并不重要，磁盘 I/O 似乎并没有减慢速度。

多次运行加载步骤，然后运行select * from v$active_session_history order by sample_time desc;。大多数 I/O 是 BACKGROUND，这意味着没有任何东西在等待。我假设临时表内部逻辑只是常规 DML 机制的副本。一般来说，新表数据可能需要写入磁盘(如果已提交)。 Oracle 可能会开始处理它，例如将数据从日志缓冲区移动到磁盘，但在实际提交之前并不着急。

PL/SQL 时间去哪儿了？

我不知道。 SQL 和 PL/SQL 引擎之间是否存在多个上下文切换或单个转换？据我所知，没有任何可用指标显示在 SQL 和 PL/SQL 之间切换所花费的时间。

我们可能永远不知道为什么 PL/SQL 代码速度较慢。我不太担心它。一般的答案是，无论如何，绝大多数数据库工作都必须使用 SQL 来完成。如果 Oracle 花更多的时间来优化其数据库的核心 SQL，而不是附加语言 PL/SQL，那就更有意义了。

附加说明

对于性能测试，将connect by 逻辑删除到单独的步骤中会很有帮助。该 SQL 是加载数据的一个很好的技巧，但它可能非常慢并且占用资源。使用该技巧加载一次示例表，然后从该表中插入是更现实的。

我尝试使用新的 Oracle 12c 功能(临时撤消)和新的 18c 功能(私有(private)临时表)。与常规临时表相比，两者都没有提高性能。

我不会打赌，但我可以看到一种方法，随着数据变大，结果会完全改变。日志缓冲区和缓冲区高速缓存只能变得这么大。最终，后台 I/O 可能会增加并淹没某些进程，从而将 BACKGROUND 等待变成 FOREGROUND 等待。另一方面，PL/SQL 解决方案只有这么多 PGA 内存，然后就会崩溃。

最后，这部分证实了我对“内存数据库”的怀疑。缓存并不是什么新鲜事，数据库已经这样做了几十年。