暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

如何提高Oracle数据提取吞吐率

ASKTOM 2020-09-21
443

问题描述

在过去的17年中,我一直专注于实时应用程序,其中从Oracle检索的行数通常为1,几乎总是少于10。我在能够编写非常高性能的实时应用程序方面享有很高的声誉,但突然之间,我面临着从一个大型批处理数据库服务器中读取数百万 (甚至可能数十亿) 行的问题,并正在寻找提高速度的方法。

所以这里有一个大问题: 在这些约束条件下,从oracle表或视图中提取数据的绝对最快的方法是什么:

1) 正在执行一个遍历整个表或视图的查询。
2) 从oracle中提取的行需要通过格式化例程进行预处理,该格式化例程实际上将整个行转换为固定长度的记录。固定长度记录的布局类似于Cobol数据布局,但是,没有打包的二进制文件,只是好的旧的普通ascii。
3) 固定长度的记录将被分组为捆绑包,并且捆绑包将被移交给后台线程,后台线程将进一步处理数据并生成输出文件和/或摘要信息。请注意,对于我们的基准速度测试,省略了最后一块。

我们在驱动程序页面上发现了这个blub,指出在IPC上使用OCI比网络连接更快,并且正在尝试使用基于Java的自制速度测试/基准测试程序来测试前提,该程序仅执行上述步骤1和2约束。

https://docs.oracle.com/cd/E11882_01/appdev.112/e13995/oracle/jdbc/OracleDriver.html
     An IPC connection is much faster than a network connection.
复制


我们的数据库硬件由裸机AIX数据库服务器组成。我们在同一个数据中心还有其他几个基于Linux的虚拟机。用于我们测试的远程客户端只是与数据库AIX服务器位于同一数据中心的Linux VM之一。

在java speed-test应用程序中,我们将语句fetch大小设置为4196行。结果让我很困惑,见下表。根据我们的结果,使用JDBC的瘦驱动程序 (通过网络) 比OCI通过IPC更快。注意,我们设置了一个max records值,将基准停止在固定点,而不是遍历整个表。

                                                                                                   Mib/
Library  Protocol  Connect String                               Client      Records    Seconds   Second
jdbc     ojdbc     jdbc:oracle:thin:@MyDbServer:1521/MySID      remote    3,629,265    453.569    9.195
oci      sql*net   jdbc:oracle:oci8:@MyDbServer:1521/MySID      remote    3,629,265    631.424    6.605
oci      ipc       jdbc:oracle:oci8:@                           local     3,629,265    667.554    6.248
复制


由于我们看到使用Java应用程序的Mib/Sec率很差,我决定编写一个OCCI程序,它的速度要快得多,但是可惜,数据库服务器是AIX,我不能为我的生命弄清楚如何在AIX上编译程序。如果我在数据库服务器上运行它,程序会更快吗?是否有另一种数据提取方法可能比我们编写的Java或C程序更快?

在C程序中,我将预取内存大小设置为1Gib,并使用不同的取行大小进行了播放。客户端是与数据库服务器本身位于同一数据中心的远程Linux服务器。

     Prefetch               Prefetch      Mib/
       Memory    Max Rows       Rows    Second
1,073,741,824     100,000         10     2.529
1,073,741,824     100,000        100    11.135
1,073,741,824     100,000      1,000    29.115
1,073,741,824     100,000     10,000    32.064
1,073,741,824     100,000    100,000    23.067
1,073,741,824   1,000,000    100,000    32.970
1,073,741,824  10,000,000     10,000    33.358
1,073,741,824   3,000,000     10,000    34.014
1,073,741,824   3,000,000     10,000    33.990  (101.990 Seconds)

复制



使用OCCI程序的远程连接字符串示例:

     (DESCRIPTION = (ADDRESS=(PROTOCOL = TCP)(HOST = MyHostName)(PORT = 1521)) (CONNECT_DATA= (SERVICE_NAME = MyDbSID) (SERVER = DEDICATED)))
复制


现在,在把所有这些都放在你的腿上之后,我有一个关于 “结果集” 的问题。我已经查看了Java,C和C # 的文档,并且这三个似乎都使用 “ResultSet”。令我不安的是,“ResultSet” 需要一次遍历结果恰好一行。更好的方法是将结果分解为微批次。我们知道,按照语句 “setFetchSize()” 方法,数据以微批方式进入驱动程序。因此,与其说 “resultSet.next()”,不如说 “resultSet.nextBatch()”,其中 “nextBatch()” 将返回一个唯一的ResultSet对象,该对象包含由 “setFetchSize()” 语句方法设置的相同行数,或驱动程序从数据库中最后一次提取检索到的剩余处理行数,以较小者为准。对ressutset.nextBatch() 的调用 (其中没有剩余行) 将引发一个异常,该异常清楚地指示查询结果的结束。然后,每个微批次可以独立地处理后台线程/任务。

是否有Oracle API或产品以这种方式工作?

感谢您的宝贵时间,我期待您的回音!

_ 韦德

专家解答

我想说的是这个Pro * C程序 (它基于多年前asktom的程序)。它对数据执行数组提取,以使其尽可能快。

https://github.com/gregrahn/oracle-ascii-unload

我将其弹出到我的虚拟盒子 (在笔记本电脑上运行) 中,因此我们在这里不是在谈论高功率服务器规范 :-)

我在服务器上运行了以下 * 本地 *,其中表 “LOTS_DATA” 是SCOTT.EMP的10000亿个副本,因此总共有1400万行。

[oracle@db19 flat]$ time ./oracle-ascii-unload userid=system/admin sqlstmt='select * from lots_data' arraysize=1000 > /dev/null
Unloading 'select * from mcdonac.lots_data'
Array size = 1000
EMPNO|ENAME|JOB|MGR|HIREDATE|SAL|COMM|DEPTNO
14000000 rows extracted
Elapsed: 00:01:13


real 1m13.319s
user 1m6.283s
sys 0m0.484s
复制


因此,每分钟不到1400万行,然后我被VM增加到2个内核,并同时运行2个,第二个运行:

[oracle@db19 flat]$ time ./oracle-ascii-unload userid=system/admin sqlstmt='select * from mcdonac.lots_data' arraysize=1000 > /dev/null
Unloading 'select * from mcdonac.lots_data'
Array size = 1000
EMPNO|ENAME|JOB|MGR|HIREDATE|SAL|COMM|DEPTNO
14000000 rows extracted
Elapsed: 00:01:14


real 1m13.705s
user 1m6.398s
sys 0m0.644s
复制


因此,在缩放方面,您可以运行多个,每个卸载不同的表,或者同一表的不同分区,或者数据的不同子集等

请注意,该程序将SQL作为参数,因此将SQL语句刻录到代码中的程序可能会运行得更快,因为您事先知道如何构建/格式化输出记录。

但最终,这里快速卸载的限制因素将是:

a) 读取源数据的速度有多快

在我的虚拟机上,我运行了这个

SQL> begin
  2  for i in ( select * from lots_data ) loop
  3    null;
  4  end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:20.56
复制


因此,仅在我的VM上获取14m行的操作将花费至少20秒。

b) 尽可能接近数据运行 (即,不通过网络卸载服务器上的数据)。如果您 * 必须 * 在网络上运行,那么您将看到巨大的阵列大小,以最大程度地减少行程以及网络基础设施 (带宽,延迟,巨型帧等)

c) 最小化系统调用。在上面的Pro * C中,我们为每行的每一列调用 'printf'。我可以通过在较大的块中发送输出来优化 * lot * (例如为 'n' 行构建字符串,然后转储)。

例如,连接每列的数据,每行只有一个printf调用给我:

[oracle@db19 flat]$ time ./oracle-ascii-unload userid=system/admin sqlstmt='select * from mcdonac.lots_data' arraysize=1000 > /dev/null
Unloading 'select * from mcdonac.lots_data'
Array size = 1000
EMPNO|ENAME|JOB|MGR|HIREDATE|SAL|COMM|DEPTNO
14000000 rows extracted
Elapsed: 00:00:52


real  0m52.400s
user  0m45.527s
sys 0m0.444s
复制


如果我取出 * 所有 * printf元素,则大约需要46秒... 因此,我的VM几乎可以做到。

顺便说一句,如果您不需要动态SQL的灵活性,那么如果您安装pro * c演示,则可以使用sample3.pc作为使用数组提取的静态SQL版本的基础。将查询,数组大小 (到500),列从我的LOTS_DATA硬编码为该查询,没有printf调用给出

[oracle@db19 flat]$ time ./sample3

Connected to ORACLE as user: system

real 0m20.506s
user 0m17.191s
sys 0m0.171s

复制


这表明我可以像PL/SQL一样快地读取Pro * C中的数据。
文章转载自ASKTOM,如果涉嫌侵权,请发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论

暂无图片
获得了26次点赞
暂无图片
内容获得14次评论
暂无图片
获得了95次收藏