问题在Oracle下10分钟内插入1000万条查询？

我正在处理文件加载程序。

此程序的目的是获取输入文件，对其数据进行一些转换，然后将数据上载到Oracle数据库中。

我面临的问题是我需要优化在Oracle上插入非常大的输入数据。

我正在将数据上传到表中，比方说ABC。

我在我的C ++程序中使用Oracle提供的OCI库。具体来说，我使用OCI连接池进行多线程并加载到ORACLE。（http://docs.oracle.com/cd/B28359_01/appdev.111/b28395/oci09adv.htm ）

以下是用于创建表ABC的DDL语句 -

CREATE TABLE ABC(
   seq_no         NUMBER NOT NULL,
   ssm_id         VARCHAR2(9)  NOT NULL,
   invocation_id  VARCHAR2(100)  NOT NULL,
   analytic_id    VARCHAR2(100) NOT NULL,
   analytic_value NUMBER NOT NULL,
   override       VARCHAR2(1)  DEFAULT  'N'   NOT NULL,
   update_source  VARCHAR2(255) NOT NULL,
   last_chg_user  CHAR(10)  DEFAULT  USER NOT NULL,
   last_chg_date  TIMESTAMP(3) DEFAULT  SYSTIMESTAMP NOT NULL
);

CREATE UNIQUE INDEX ABC_indx ON ABC(seq_no, ssm_id, invocation_id, analytic_id);
/
CREATE SEQUENCE ABC_seq;
/

CREATE OR REPLACE TRIGGER ABC_insert
BEFORE INSERT ON ABC
FOR EACH ROW
BEGIN
SELECT ABC_seq.nextval INTO :new.seq_no FROM DUAL;
END;

我目前正在使用以下查询模式将数据上载到数据库中。我通过各种OCI连接池线程分批发送500个查询数据。

使用SQL插入查询的示例 -

insert into ABC (SSM_ID, invocation_id , calc_id, analytic_id, analytic_value,
override, update_source)
select 'c','b',NULL, 'test', 123 , 'N', 'asdf' from dual
union all select 'a','b',NULL, 'test', 123 , 'N', 'asdf' from dual
union all select 'b','b',NULL, 'test', 123 , 'N', 'asdf' from dual
union all select 'c','g',NULL, 'test', 123 , 'N', 'asdf' from dual

Oracle针对上述查询的执行计划 -

-----------------------------------------------------------------------------
| Id  | Operation                | Name|Rows| Cost (%CPU) | Time     |
-----------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |     | 4  |     8   (0) | 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | ABC |    |             |          |
|   2 |   UNION-ALL              |     |    |             |          |
|   3 |    FAST DUAL             |     | 1  |     2   (0) | 00:00:01 |
|   4 |    FAST DUAL             |     | 1  |     2   (0) | 00:00:01 |
|   5 |    FAST DUAL             |     | 1  |     2   (0) | 00:00:01 |
|   6 |    FAST DUAL             |     | 1  |     2   (0) | 00:00:01 |

该程序的运行时间加载100万行 -

Batch Size = 500
Number of threads - Execution Time -
10                  4:19
20                  1:58
30                  1:17
40                  1:34
45                  2:06
50                  1:21
60                  1:24
70                  1:41
80                  1:43
90                  2:17
100                 2:06


Average Run Time = 1:57    (Roughly 2 minutes)

我需要进一步优化和减少这个时间。我面临的问题是我上传了1000万行。

平均运行时间为千万出来是= 21分钟

（我的目标是将这个时间减少到10分钟以下）

所以我也尝试了以下步骤 -

[1] 是否在表ABC的基础上进行了分区 seq_no。用过的 30个分区。经过测试 100万行 - 表现非常糟糕。几乎 比未分区的表多4倍。

[2] 另外在表ABC的基础上进行分区 last_chg_date。用过的 30个分区。

2.a）测试了100万行 - 性能几乎等于未分区的表。 差异很小，所以没有考虑到。

2.b）再次 测试相同的1000万行。表现几乎相同 到未分区的表。没有明显的区别。

以下是用于实现分区的DDL命令 -

CREATE TABLESPACE ts1 DATAFILE AUTOEXTEND ON;
CREATE TABLESPACE ts2 DATAFILE AUTOEXTEND ON;
CREATE TABLESPACE ts3 DATAFILE AUTOEXTEND ON;
CREATE TABLESPACE ts4 DATAFILE AUTOEXTEND ON;
CREATE TABLESPACE ts5 DATAFILE AUTOEXTEND ON;
CREATE TABLESPACE ts6 DATAFILE AUTOEXTEND ON;
CREATE TABLESPACE ts7 DATAFILE AUTOEXTEND ON;
CREATE TABLESPACE ts8 DATAFILE AUTOEXTEND ON;
CREATE TABLESPACE ts9 DATAFILE AUTOEXTEND ON;
CREATE TABLESPACE ts10 DATAFILE AUTOEXTEND ON;
CREATE TABLESPACE ts11 DATAFILE AUTOEXTEND ON;
CREATE TABLESPACE ts12 DATAFILE AUTOEXTEND ON;
CREATE TABLESPACE ts13 DATAFILE AUTOEXTEND ON;
CREATE TABLESPACE ts14 DATAFILE AUTOEXTEND ON;
CREATE TABLESPACE ts15 DATAFILE AUTOEXTEND ON;
CREATE TABLESPACE ts16 DATAFILE AUTOEXTEND ON;
CREATE TABLESPACE ts17 DATAFILE AUTOEXTEND ON;
CREATE TABLESPACE ts18 DATAFILE AUTOEXTEND ON;
CREATE TABLESPACE ts19 DATAFILE AUTOEXTEND ON;
CREATE TABLESPACE ts20 DATAFILE AUTOEXTEND ON;
CREATE TABLESPACE ts21 DATAFILE AUTOEXTEND ON;
CREATE TABLESPACE ts22 DATAFILE AUTOEXTEND ON;
CREATE TABLESPACE ts23 DATAFILE AUTOEXTEND ON;
CREATE TABLESPACE ts24 DATAFILE AUTOEXTEND ON;
CREATE TABLESPACE ts25 DATAFILE AUTOEXTEND ON;
CREATE TABLESPACE ts26 DATAFILE AUTOEXTEND ON;
CREATE TABLESPACE ts27 DATAFILE AUTOEXTEND ON;
CREATE TABLESPACE ts28 DATAFILE AUTOEXTEND ON;
CREATE TABLESPACE ts29 DATAFILE AUTOEXTEND ON;
CREATE TABLESPACE ts30 DATAFILE AUTOEXTEND ON;

CREATE TABLE ABC(
   seq_no           NUMBER NOT NULL,
   ssm_id           VARCHAR2(9)  NOT NULL,
   invocation_id    VARCHAR2(100)  NOT NULL,
   calc_id          VARCHAR2(100) NULL,
   analytic_id      VARCHAR2(100) NOT NULL,
   ANALYTIC_VALUE   NUMBER NOT NULL,
   override         VARCHAR2(1)  DEFAULT  'N'   NOT NULL,
   update_source    VARCHAR2(255) NOT NULL,
   last_chg_user    CHAR(10)  DEFAULT  USER NOT NULL,
   last_chg_date    TIMESTAMP(3) DEFAULT  SYSTIMESTAMP NOT NULL
)
PARTITION BY HASH(last_chg_date)
PARTITIONS 30
STORE IN (ts1, ts2, ts3, ts4, ts5, ts6, ts7, ts8, ts9, ts10, ts11, ts12, ts13,
ts14, ts15, ts16, ts17, ts18, ts19, ts20, ts21, ts22, ts23, ts24, ts25, ts26,
ts27, ts28, ts29, ts30);

我在线程函数中使用的代码（用C ++编写），使用OCI -

void OracleLoader::bulkInsertThread(std::vector<std::string> const & statements)
{

    try
    {
        INFO("ORACLE_LOADER_THREAD","Entered Thread = %1%", m_env);
        string useOraUsr = "some_user";
        string useOraPwd = "some_password";

        int user_name_len   = useOraUsr.length();
        int passwd_name_len = useOraPwd.length();

        text* username((text*)useOraUsr.c_str());
        text* password((text*)useOraPwd.c_str());


        if(! m_env)
        {
            CreateOraEnvAndConnect();
        }
        OCISvcCtx *m_svc = (OCISvcCtx *) 0;
        OCIStmt *m_stm = (OCIStmt *)0;

        checkerr(m_err,OCILogon2(m_env,
                                 m_err,
                                 &m_svc,
                                 (CONST OraText *)username,
                                 user_name_len,
                                 (CONST OraText *)password,
                                 passwd_name_len,
                                 (CONST OraText *)poolName,
                                 poolNameLen,
                                 OCI_CPOOL));

        OCIHandleAlloc(m_env, (dvoid **)&m_stm, OCI_HTYPE_STMT, (size_t)0, (dvoid **)0);

////////// Execution Queries in the format of - /////////////////
//        insert into pm_own.sec_analytics (SSM_ID, invocation_id , calc_id, analytic_id, analytic_value, override, update_source)
//        select 'c','b',NULL, 'test', 123 , 'N', 'asdf' from dual
//        union all select 'a','b',NULL, 'test', 123 , 'N', 'asdf' from dual
//        union all select 'b','b',NULL, 'test', 123 , 'N', 'asdf' from dual
//        union all select 'c','g',NULL, 'test', 123 , 'N', 'asdf' from dual
//////////////////////////////////////////////////////////////////

        size_t startOffset = 0;
        const int batch_size = PCSecAnalyticsContext::instance().getBatchCount();
        while (startOffset < statements.size())
        {
            int remaining = (startOffset + batch_size < statements.size() ) ? batch_size : (statements.size() - startOffset );
            // Break the query vector to meet the batch size
            std::vector<std::string> items(statements.begin() + startOffset,
                                           statements.begin() + startOffset + remaining);

            //! Preparing the Query
            std::string insert_query = "insert into ";
            insert_query += Context::instance().getUpdateTable();
            insert_query += " (SSM_ID, invocation_id , calc_id, analytic_id, analytic_value, override, update_source)\n";

            std::vector<std::string>::const_iterator i3 = items.begin();
            insert_query += *i3 ;

            for( i3 = items.begin() + 1; i3 != items.end(); ++i3)
                insert_query += "union " + *i3 ;
            // Preparing the Statement and Then Executing it in the next step
            text *txtQuery((text *)(insert_query).c_str());
            checkerr(m_err, OCIStmtPrepare (m_stm, m_err, txtQuery, strlen((char *)txtQuery), OCI_NTV_SYNTAX, OCI_DEFAULT));
            checkerr(m_err, OCIStmtExecute (m_svc, m_stm, m_err, (ub4)1, (ub4)0, (OCISnapshot *)0, (OCISnapshot *)0, OCI_DEFAULT ));

            startOffset += batch_size;
        }

        // Here is the commit statement. I am committing at the end of each thread.
        checkerr(m_err, OCITransCommit(m_svc,m_err,(ub4)0));

        checkerr(m_err, OCIHandleFree((dvoid *) m_stm, OCI_HTYPE_STMT));
        checkerr(m_err, OCILogoff(m_svc, m_err));

        INFO("ORACLE_LOADER_THREAD","Thread Complete. Leaving Thread.");
    }

    catch(AnException &ex)
    {
        ERROR("ORACLE_LOADER_THREAD", "Oracle query failed with : %1%", std::string(ex.what()));
        throw AnException(string("Oracle query failed with : ") + ex.what());
    }
}

在回复帖子的时候，我被提出了几种优化我的方法 插入查询。我选择并使用过 我有意思 在我的程序中出于以下原因，我在测试各种INSERT查询时发现了这些原因。在运行向我建议的SQL查询时 - 我是 - 我

insert into ABC (SSM_ID, invocation_id , calc_id, analytic_id, analytic_value,
override, update_source)
select 'c','b',NULL, 'test', 123 , 'N', 'asdf' from dual
union all select 'a','b',NULL, 'test', 123 , 'N', 'asdf' from dual
union all select 'b','b',NULL, 'test', 123 , 'N', 'asdf' from dual
union all select 'c','g',NULL, 'test', 123 , 'N', 'asdf' from dual

Oracle针对查询I的执行计划 -

--------------------------------------------------------------------------
| Id  | Operation                | Name| Rows | Cost (%CPU)   | Time     |
--------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |     |  4   | 8   (0)       | 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | ABC |      |               |          |
|   2 |   UNION-ALL              |     |      |               |          |
|   3 |    FAST DUAL             |     |  1   | 2   (0)       | 00:00:01 |
|   4 |    FAST DUAL             |     |  1   | 2   (0)       | 00:00:01 |
|   5 |    FAST DUAL             |     |  1   | 2   (0)       | 00:00:01 |
|   6 |    FAST DUAL             |     |  1   | 2   (0)       | 00:00:01 |

QUERY II -

insert all
into ABC (SSM_ID, invocation_id , calc_id, analytic_id, analytic_value,
override, update_source) values ('c','b',NULL, 'test', 123 , 'N', 'asdf')
into ABC (SSM_ID, invocation_id , calc_id, analytic_id, analytic_value,
override, update_source) values ('c','e',NULL, 'test', 123 , 'N', 'asdf')
into ABC (SSM_ID, invocation_id , calc_id, analytic_id, analytic_value,
override, update_source) values ('c','r',NULL, 'test', 123 , 'N', 'asdf')
into ABC (SSM_ID, invocation_id , calc_id, analytic_id, analytic_value,
override, update_source) values ('c','t',NULL, 'test', 123 , 'N', 'asdf')
select 1 from dual

Oracle针对查询II的执行计划 -

-----------------------------------------------------------------------------
| Id  | Operation           | Name| Rows  | Cost (%CPU)   | Time     |
-----------------------------------------------------------------------------
|   0 | INSERT STATEMENT    |     | 1     |     2   (0)   | 00:00:01 |
|   1 |  MULTI-TABLE INSERT |     |       |               |          |
|   2 |   FAST DUAL         |     | 1     |     2   (0)   | 00:00:01 |
|   3 |   INTO              | ABC |       |               |          |
|   4 |   INTO              | ABC |       |               |          |
|   5 |   INTO              | ABC |       |               |          |
|   6 |   INTO              | ABC |       |               |          |

根据实验而定 查询我更快。

在这里，我在Oracle SQL Developer上进行了测试，并且我也通过我的C ++程序（FILELOADER）发送了插入查询。

在进一步阅读它时，我发现执行计划显示的成本是查询用于处理自身的CPU数量。 这告诉Oracle将使用更多的CPU来处理第一个查询，这就是为什么它的成本继续为= 8。

即使通过我的应用程序使用相同的插入模式，我发现它的性能几乎要好1.5倍。

我需要深入了解如何进一步提高性能..？我尝试过的所有事情，都是在我的问题中总结出来的。如果我发现或发现任何相关内容，我将添加此问题。

我的目标是带来的 在10分钟内上传1000万次查询的时间。

10713

2017-07-14 10:10

起源

你的硬盘有多快写？它有多少IOPS？它可能会达到这些限制之一 - exussum

为每个分区创建一个表空间没有任何优势 - 特别是如果它们都在同一个硬盘上。 - a_horse_with_no_name

UNIQUE索引没用，因为seq_no已经是唯一的。我会首先测试原始导入速度，没有唯一索引和没有序列，以检查哪个部分是最慢的。 - dnoeth

另一句话：准备好的语句通常更快，你应该将它们与数组处理结合起来，以便向DBMS发送更少的请求。 - dnoeth

还有一个想法：尝试将所有数据存储到文件或一系列文件中，并使用SQL Loader加载它们。结果加载时间将概述它的速度。因此，了解您已经与最佳指标的接近程度会更容易。 - xacinay

答案:

我知道其他人已经提到这一点，你不想听，但使用使用SQL * Loader 要么外部表格。我大约相同宽度的表的平均加载时间是12.57 秒只有超过10米的行。这些实用程序已明确设计为快速将数据加载到数据库中并且非常擅长。这可能会导致一些额外的时间处罚，具体取决于输入文件的格式，但有很多选项，我很少在加载之前更改文件。

如果您不愿意这样做，那么您不必升级您的硬件;你需要消除所有可能阻碍快速加载的障碍。要枚举它们，请删除：

该指数
触发
序列
分区

有了所有这些，你就不得不让数据库执行更多工作，而且因为你在事务上这样做，所以你没有充分发挥数据库的潜力。

比如说，将数据加载到一个单独的表中 ABC_LOAD。数据完全加载后执行一次单 INSERT语句进入ABC。

insert into abc
select abc_seq.nextval, a.*
  from abc_load a

执行此操作时（即使您不这样做）确保序列缓存大小正确; 去引用：

当应用程序访问序列缓存中的序列时，序列号快速读取。但是，如果应用程序访问如果序列不在缓存中，则必须读取序列在使用序列号之前从磁盘到缓存。

如果你的应用程序同时使用许多序列，那么你的序列缓存可能不够大，无法容纳所有序列。在在这种情况下，访问序列号可能通常需要磁盘读取。要快速访问所有序列，请确保缓存足够用于保存您的所有序列同时使用的条目应用。

这意味着如果你有10个线程同时使用这个序列写500条记录，那么你需要一个5000的缓存大小。该更改顺序文档说明了如何改变这个：

alter sequence abc_seq cache 5000

如果按照我的建议，我将缓存大小调整到大约10.5米。

考虑使用 APPEND提示（另请参阅Oracle Base）;这指示Oracle使用直接路径插入，它直接将数据附加到表的末尾，而不是寻找空间来放置它。如果您的表有索引但您可以使用它，则无法使用此功能 ABC_LOAD

insert /*+ append */ into ABC (SSM_ID, invocation_id , calc_id, ... )
select 'c','b',NULL, 'test', 123 , 'N', 'asdf' from dual
union all select 'a','b',NULL, 'test', 123 , 'N', 'asdf' from dual
union all select 'b','b',NULL, 'test', 123 , 'N', 'asdf' from dual
union all select 'c','g',NULL, 'test', 123 , 'N', 'asdf' from dual

如果您使用APPEND提示;我补充一下截短 ABC_LOAD 插入之后 ABC 否则这张桌子会无限增长。这应该是安全的，因为那时您将完成使用该表。

您没有提到您正在使用的版本或版本或Oracle。你可以使用一些额外的小技巧：

Oracle 12c

此版本支持标识列;你可以完全摆脱序列。

CREATE TABLE ABC(
   seq_no         NUMBER GENERATED AS IDENTITY (increment by 5000)

Oracle 11g r2

如果你保持扳机;您可以直接指定序列值。
```
:new.seq_no := ABC_seq.nextval;
```
Oracle企业版

如果您使用的是Oracle Enterprise，则可以加快INSERT的速度 ABC_LOAD 通过使用并行提示：
```
insert /*+ parallel */ into abc
select abc_seq.nextval, a.*
  from abc_load a
```
这可能导致它自己的问题（太多的并行进程等），所以测试。它威力帮助较小的批量插入，但它不太可能，因为你将浪费时间计算什么线程应该处理什么。

TL;博士

使用数据库附带的实用程序。

如果你不能使用它们，那么就可以摆脱所有可能减慢插入速度并放大批量的东西，因为这是数据库擅长的。

2017-07-15 12:38

如果你有一个文本文件，你应该尝试 SQL LOADER 有直接路径。它非常快，专为这种大规模数据负载而设计。看看这个选项这可以改善性能。

作为ETL的第二个优势，明文文件比10 ^ 7插入文件更小，更容易审核。

如果你需要进行一些转换，你可以在事后用oracle完成。

2017-07-14 11:50

您应该尝试批量插入数据。为此，您可以使用 OCI * ML。讨论它在这儿。值得注意的文章在这儿。或者您可以尝试使用Oracle SQL Bulk Loader SQLLDR 本身可以提高你的上传速度。为此，将数据序列化为csv文件并调用SQLLDR作为参数传递csv。

另一种可能的优化是交易策略。尝试在每个线程/连接的1个事务中插入所有数据。

另一种方法是使用多插入：

INSERT ALL
   INTO ABC (SSM_ID, invocation_id , calc_id, analytic_id, analytic_value, 
   override, update_source ) VALUES ('c','b',NULL, 'test', 123 , 'N', 'asdf')
   INTO ABC (SSM_ID, invocation_id , calc_id, analytic_id, analytic_value, 
   override, update_source ) VALUES ('a','b',NULL, 'test', 123 , 'N', 'asdf')
   INTO ABC (SSM_ID, invocation_id , calc_id, analytic_id, analytic_value, 
   override, update_source ) VALUES ('b','b',NULL, 'test', 123 , 'N', 'asdf')

SELECT 1 FROM DUAL;

代替 insert .. union all。

您的示例数据看起来是相互依赖的，这会导致插入1个重要行，然后使用插入后的SQL查询将其扩展为4行。

此外，在插入批处理之前关闭所有索引（或删除它们并在批量完成时重新创建）。表索引会降低插入性能，而您当时并未实际使用它（它会在每个插入的行上计算一些id并执行相应的操作）。

使用预准备语句语法应该加快上传例程，因为服务器将具有已经解析的缓存语句。

然后，优化您的C ++代码：将ops移出周期：

 //! Preparing the Query
   std::string insert_query = "insert into ";
   insert_query += Context::instance().getUpdateTable();
   insert_query += " (SSM_ID, invocation_id , calc_id, 
        analytic_id, analytic_value, override, update_source)\n";
   while (startOffset < statements.size())
   { ... }

2017-07-14 10:23

另外，有包裹 DBMS_DATAPUMP expdp / impdp使用此API执行其任务。 - zaratustra

@xacinay - 我已经使用我在问题中提到的SQL查询将所有数据合并到一个事务中。我想避免SQLLDR，因为它将控制我的CPP程序。我更喜欢可以在我的C ++应用程序中完成的事情，比如OCI库。 - badola

好吧，它不清楚你实际插入的每个事务有多少条记录。如果500rcs /交易 - 金额非常小。尝试将其增加到20000rcs / transaction。 - xacinay

@xacinay当我发送1000万行时，每个线程约为33334行。然后根据用户输入，线程将其分解为500或1000的批次。我测试了不同数量的批量大小，范围高达5000，但性能的变化并不显着。 - badola

修改了我的答案（参见OCI ML参考） - xacinay

顺便说一句，你是否尝试增加物理客户端的数量，而不仅仅是线程？通过在多个VM或多个物理计算机上运行云。我最近读过Aerospike开发人员的评论，他们解释说许多人无法重现他们的结果只是因为他们不理解它并不容易使客户端实际每秒发送那么多查询（他们的每秒大于1M）案件）。例如，对于他们的基准测试，他们必须并行运行4个客户端。也许这个特殊的oracle驱动程序速度不够快，无法在单台机器上支持每秒7-8千多个请求？

2017-10-04 09:02

嗨@Maskim，我没时间测试你建议的方法。最后，我最终使用了与Oracle捆绑在一起的sqlldr程序。 - badola

问题 在Oracle下10分钟内插入1000万条查询？

答案:

TL;博士

热门问题

问题在Oracle下10分钟内插入1000万条查询？