sql - oracle提高查询性能

Question

我是 oracle 的新手，我必须解决这个问题。

我有一个里面大约有 5.2 亿行的表。我必须获取所有行并将它们导入(非规范化)到 NoSQL 数据库中。

该表有两个整数字段 C_ID 和 A_ID 和 3 个索引，一个在 C_ID 上，一个在 A_ID 上，一个在两个字段上。

我一开始就尝试过这种方式:

SELECT C_ID, A_ID FROM M_TABLE;

这在合理的时间内从未给我任何结果(我无法测量时间，因为它似乎永远不会完成)。

我以这种方式更改了查询:

SELECT /*+ ALL_ROWS */ C_ID, A_ID FROM (SELECT
    rownum rn, C_ID, A_ID
FROM
    M_TABLE WHERE rownum < ((:1 * :2 ) +1 )) WHERE rn >= (((:1 -1) * :2 ) +1 );

我使用 3 个线程并行运行此查询，并使用大小为 1000 的页面进行分页。

我试着介绍三个优化:

1)我在表格上创建了​​统计信息:

ANALYZE TABLE TABLE_M ESTIMATE STATISTICS SAMPLE 5 PERCENT;

2)我将表划分为 8 个分区。

3)我用并行选项创建了表。

现在我能够每秒获取 10000 行，因此整个过程大约需要 15 个小时才能完成(数据库在 4 核、8 GB 的机器上运行)。

问题是我需要在最多 5 小时内完成所有工作。

我没有想法，因此，在我要求购买新机器之前，您知道在这种情况下可以提高性能的任何方法。

Answer 1

你怎么处理你的结果？它是使用 PL/SQL 直接提取到文件中还是使用其他应用程序来处理数据？它是通过网络发送的吗？ (这可能是悬而未决的果实)。

我问的原因通常是 FULL SCAN (没有 ORDER BY)将 立即返回第一行 .如果您将结果输出到文件，您应该会看到它立即开始填满。如果不这样做，这意味着该段的开头有很多空白空间，这可以解释为什么查询永远不会返回(至少在合理的时间内)。

所以当你说你的查询没有返回时，我有点担心，你怎么知道？以下块是否返回？

DECLARE
  l NUMBER := 0;
BEGIN
  FOR cc IN (SELECT C_ID, A_ID FROM M_TABLE) LOOP
    l := l + 1;
    EXIT WHEN l >= 100000;
  END LOOP;
END;

如果是，则表示正在处理您的完整扫描。通过对上述查询进行计时，您应该能够计算完整的单个 SCAN 需要多少时间，假设该段是均匀密集的。

读取 500M 行的工作量很大，但行很小，所以如果表段压缩得很好，Oracle 应该在合理的时间内返回所有行。如果重复删除然后加载 INSERT /*+APPEND*/ 表段可能会具有低效的空间配置例如。重建表 ( ALTER TABLE MOVE ) 将删除段中所有空的无用空间。顺便说一句，当您对表进行分区时，您确实重建了它，所以这可能是您的查询现在返回的原因!

在任何情况下，我都会建议您重试全表扫描，可能是在重建表以重置任何空白空间和高水位线之后。迄今为止，单次全表扫描是访问大量数据的最可靠的方法(也是最有效的方法之一)。

如果你需要进一步提升性能，建议你看看ROWID分区( DIY parallel processing方案)或者内置包 DBMS_PARALLEL_EXECUTE .