在10分钟内在Oracle中插入1千万个查询？

暨嘉

2023-03-14

问题内容：

我正在研究文件加载器程序。

该程序的目的是获取一个输入文件，对其数据进行一些转换，然后将数据上传到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]在 seq_no 的基础上对表ABC进行了分区。使用了 30个分区 。经过 一百万行 测试-性能非常差。几乎
是未分区表的4倍。

[2]根据 last_chg_date 对表ABC的另一分区。使用了 30个分区 。

2.a）经过一百万行测试- 性能几乎等于未分区表。 差异很小，因此没有考虑。

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);

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

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 QUERY 。由于在测试各种INSERT查询时发现以下原因，因此我
在程序中选择并使用了 QUERYI 。在运行向我建议的SQL查询 -QUERY I-

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 |

查询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 |       |               |          |

根据实验， 查询I更快 。

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

在进一步了解它的过程中，我发现执行计划显示的成本是查询将用于处理自身的CPU数量。
这表明Oracle将使用更多的CPU来处理第一个查询，这就是为什么其成本继续为= 8的原因。

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

我需要一些关于如何进一步提高性能的见解。我尝试过的所有事情，都在我的问题中进行了总结。如果我发现或发现任何相关的内容，我将添加这个问题。

我的目标是 在10分钟内 将 上千万个查询 的 上传时间缩短 。

问题答案：

我知道其他人已经提到了这一点，但是您不想听到它，而是使用SQL*Loader或外部表。对于略多于10m的行，我对大约相同宽度的表的平均加载时间为12.57
秒
。这些实用程序经过明确设计，可以将数据快速加载到数据库中，并且非常擅长。根据输入文件的格式，这可能会导致一些额外的时间损失，但是有很多选择，而且在加载之前，我很少需要更改文件。

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

指标
触发
序列
隔断

有了所有这些，您就必须让数据库执行更多的工作，并且由于要以事务方式进行此操作，因此您并未充分利用数据库的潜力。

将数据加载到一个单独的表中，例如ABC_LOAD。该数据已被完全加载后进行 单一的 INSERT语句为ABC。

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

当您执行此操作时（即使您不这样做），请确保序列缓存大小正确；否则，请执行以下步骤。引用：

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

如果您的应用程序同时使用许多序列，则序列缓存可能不足以容纳所有序列。在这种情况下，访问序列号通常可能需要读取磁盘。为了快速访问所有序列，请确保您的缓存中有足够的条目来保存应用程序并发使用的所有序列。

这意味着，如果您有10个线程使用此序列同时写入500条记录，那么您需要的缓存大小为5,000。在ALTER
SEQUENCE文件指出如何改变这样的：

alter sequence abc_seq cache 5000

如果您按照我的建议，我会将缓存大小提高到10.5m左右。

查看使用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提示；插入后，我将添加TRUNCATE
ABC_LOAD，ABC否则此表将无限期增长。这将是安全的，因为届时您将完成使用该表的操作。

您没有提到您使用的是哪个版本或Oracle。您可以使用许多额外的小技巧：

甲骨文12c

这个版本支持身份列;
您可以完全摆脱顺序。

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

甲骨文11g r2

如果保持触发；您可以直接分配序列值。

    :new.seq_no := ABC_seq.nextval;

Oracle企业版

如果您使用的是Oracle
Enterprise，则可以ABC_LOAD使用PARALLEL提示来加快INSERT的速度：

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

这可能会导致自身的问题（太多并行进程等），因此请进行测试。这对于较小的批处理插入 可能会
有所帮助，但不太可能，因为您将浪费时间计算哪个线程应该处理什么。

tl; dr

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

如果您不能使用它们，那么请摆脱所有可能减慢插入速度并批量进行插入操作的原因，因为这正是数据库的优势所在。

在10分钟内在Oracle中插入1千万个查询？

tl; dr

相关阅读

相关文章

相关问答

相关工具

相关文档