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.物化视图

对于这个用例，我将它们排除在外，因为所讨论的临时数据集相当复杂，并且对更新实例化视图的影响将太大。

实际数据注意事项

以上是我正在尝试执行的示例。实际的数据集包括：

• 临时数据从大约15个联接表中反规范化。
• 它的产生速度约为2-20x /秒。
• 每个临时数据集的实际行数约为10-200（不如上面的示例大）。
• 系统的每个用户都有自己的临时数据集（总共1M个用户，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

但是在一天结束时，只有挂钟时间很重要。临时表的加载和查询步骤都快得多。

在PL / SQL版本可以通过更换得到改善BULK COLLECT与cast(collect(test_o(MOD(a, 10), '' || MOD(a, 12))) as test_t) INTO t。但是它仍然比临时表版本慢得多。

优化阅读

从小型临时表读取仅使用内存中的缓冲区高速缓存。仅多次运行查询部分，并观察consistent gets from cache（内存）在physical reads cache（磁盘）保持不变的情况下如何增加。

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可能会开始处理它，例如通过将数据从日志缓冲区移到磁盘上，但是在出现实际情况之前，不要急于进行COMMIT。

PL / SQL时间流向何方？

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

我们可能永远无法确切知道为什么PL /
SQL代码比较慢。我不用担心太多。普遍的答案是，无论如何，绝大多数数据库工作都必须使用SQL进行。如果甲骨文花了更多的时间优化数据库核心SQL而不是附加语言PL
/ SQL，这将很有意义。

附加条款

对于性能测试，将connect by逻辑删除到一个单独的步骤中可能会有所帮助。该SQL是加载数据的绝妙技巧，但它可能非常缓慢且占用大量资源。用这种技巧一次加载一个样本表，然后从该表中插入更为现实。

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

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

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