Oracle 中临时数据的性能注意事项 [英] Performance considerations for temporary data in Oracle

问题描述

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

I'm evaluating various options to run a bunch of high-performing queries against a single temporary data set in Oracle. In T-SQL, I'd probably use in-memory temporary tables, but Oracle doesn't have an exact equivalent of this feature.

我目前看到这些选项:

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

对于这个用例，我将它们排除在外，因为有问题的临时数据集相当复杂，更新物化视图的影响太大.

3. Materialised views

I'm ruling them out for this use-case, because the temporary data set in question is rather complex and the implications on updating the materialised view would be too significant.

以上是我正在尝试做的示例.真实数据集涉及:

The above are examples of what I'm trying to do. The real data sets involve:

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

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

From my intuition, the temp table query "should" be slower because it (probably) involves I/O and disk access, whereas the PL/SQL collection query is a mere in-memory solution. But in my trivial benchmark, this is not the case as the temp table query beats the PL/SQL collection query by factor 3x. Why is this the case? Is there some PL/SQL <-> SQL context switch happening?

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

Do I have other options for fast (yet extensive) "in-memory" data analysis on a well-defined temporary data set? Are there any significant publicly available benchmarks comparing the various options?

推荐答案

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

Temporary tables are effectively the same as in-memory tables thanks to caching and asynchronous I/O, and the temporary table solution does not require any overhead for converting between SQL and PL/SQL.

确认结果

用 RunStats 比较两个版本，临时表版本看起来差很多.Run1 中临时表版本的所有这些垃圾，而 Run2 中的 PL/SQL 版本只有一点额外的内存.起初，似乎 PL/SQL 应该是明显的赢家.

Comparing the two versions with RunStats, the temporary table version looks much worse. All that junk for the temporary table version in Run1, and only a little extra memory for the PL/SQL version in Run2. At first it seems like PL/SQL should be the clear winner.

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

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

But at the end of the day only the wall clock time matters. Both the loading and the querying steps run much faster with temporary tables.

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

The PL/SQL version can be improved by replacing the BULK COLLECT with cast(collect(test_o(MOD(a, 10), '' || MOD(a, 12))) as test_t) INTO t. But it's still significantly slower than the temporary table version.

优化读取

从小临时表中读取只使用缓冲区缓存，它在内存中.多次只运行查询部分，观察consistent 从缓存中获取(内存)如何增加，而物理读取缓存(磁盘)保持不变.

Reading from the small temporary table only uses the buffer cache, which is in memory. Run only the query part many times, and watch how the consistent gets from cache (memory) increase while the physical reads cache (disk) stay the same.

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 似乎不会减慢速度.

Ideally there would be no physical I/O, especially since the temporary table is ON COMMIT DELETE ROWS. And it sounds like the next version of Oracle may introduce such a mechanism. But it doesn't matter much in this case, the disk I/O does not seem to slow things down.

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

Run the load step multiple times, and then run select * from v$active_session_history order by sample_time desc;. Most of the I/O is BACKGROUND, which means nothing is waiting on it. I assume the temporary table internal logic is just a copy of regular DML mechanisms. In general, new table data may need to be written to disk, if it's committed. Oracle may start working on it, for example by moving data from the log buffer to disk, but there is no rush until there is an actual COMMIT.

PL/SQL 的时间都去哪儿了?

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

I have no clue. Are there multiple context switches, or a single conversion between the SQL and PL/SQL engines? As far as I know none of the available metrics show the time spent on switching between SQL and PL/SQL.

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

We may never know exactly why PL/SQL code is slower. I don't worry about it too much. The general answer is, the vast majority of database work has to be done in SQL anyway. It would make a lot of sense if Oracle spent more time optimizing the core of their database, SQL, than the add-on language, PL/SQL.

附加说明

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

For performance testing it can be helpful to remove the connect by logic into a separate step. That SQL is a great trick for loading data, but it can be very slow and resource intensive. It's more realistic to load a sample table once with that trick, and then insert from that table.

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

I tried using the new Oracle 12c feature, temporary undo, and the new 18c feature, private temporary tables. Neither one improved performance over regular temporary tables.

我不会赌它，但我可以看到结果会随着数据变大而完全改变的方式.日志缓冲区和缓冲区缓存只能变得如此之大.最终，后台 I/O 可能会加起来并压倒一些进程，将 BACKGROUND 等待变成 FOREGROUND 等待.另一方面，PL/SQL 解决方案只有这么多的 PGA 内存，然后事情就会崩溃.

I wouldn't bet on it, but I can see a way that the results would completely change as the data gets larger. The log buffer and the buffer cache can only get so large. And eventually that background I/O could add up and overwhelm some processes, turning the BACKGROUND wait into a FOREGROUND wait. On the other hand, there's only so much PGA memory for the PL/SQL solution, and then things crash.

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

Finally, this partially confirms my skepticism of "in-memory databases". Caching is nothing new, databases have been doing it for decades.

这篇关于Oracle 中临时数据的性能注意事项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！