OracleLoader hangs posting lots (60 millions) of records with a batch size of 50000 in DIRECT mode

[motuzko]

OracleLoader hangs posting lots (60 millions) of records with a batch size of 50000 in DIRECT mode.

The process hangs at random.

Table

Code: Select all

CREATE TABLE CRAW.LIMIT_USAGE_FACT
(
  SLICE_DATE    DATE                            NOT NULL,
  LIMIT_DEF_ID  NUMBER(9)                       NOT NULL,
  LIMIT_VER     NUMBER(9)                       NOT NULL,
  PL_PK         NUMBER(15)                      NOT NULL
)

Loading

Code: Select all

        String tmpSliceDateColumnName = 'SLICE_DATE';
        String tmpLimitDefIdColumnName = 'LIMIT_DEF_ID';
        String tmpLimitVerColumnName = 'LIMIT_VER';
        String tmpPlPkColumnName = 'PL_PK';

                #region Bulk store PlPks
                using (var tmpLoader = new OracleLoader(
                  $"CRAW.{tmpTempTableName}",
                  tmpOracleConnection))
                {
                  tmpLoader.CreateColumns();
                  tmpLoader.BatchSize = 50 * 1000;
                  tmpLoader.NotifyAfter = 50 * 1000;

                  Int32 tmpRowsPersisted = 0;

                  tmpLoader.RowsCopied += (inSender, inArgs) =>
                  {
                    tmpRowsPersisted += inArgs.RowsCopied;
                    tmpCurrentClassLogger.Debug($"Persist PlPks: {inArgs.RowsCopied:N0} rows copied ({tmpRowsPersisted:N0} so far)");
                  };

                  tmpLoader.Error += (inSender, inArgs) =>
                  {
                    if (Debugger.IsAttached)
                      Debugger.Break();

                    inArgs.Ignore = true;
                  };

                  foreach (OracleLoaderColumn tmpColumn in tmpLoader.Columns.Cast<OracleLoaderColumn>()
                    .ToList()
                    .Where(
                      inColumn => !new[]
                      {
                        tmpSliceDateColumnName,
                        tmpLimitDefIdColumnName,
                        tmpLimitVerColumnName,
                        tmpPlPkColumnName,
                      }.Contains(inColumn.Name)))
                    tmpLoader.Columns.Remove(tmpColumn);

                  tmpLoader.Open();
                  try
                  {
                    List<ProcessItem> tmpProcessItems;
                    lock (m_EnqueueLocker)
                    {
                      tmpProcessItems = m_ProcessItems.ToList();
                      m_ProcessItems.Clear();
                    }

                    List<LimitUsageFact> tmpLimitUsageFacts = tmpProcessItems.SelectMany(
                        inItem => inItem.PlPks,
                        (inItem, inPlPk) => new LimitUsageFact
                        {
                          SliceDate = SliceDate,
                          LimitDefId = inItem.LimitDefKey.LimitDefId,
                          LimitVer = inItem.LimitDefKey.LimitVer,
                          PlPk = inPlPk,
                        })
                      .ToList();

                    tmpCurrentClassLogger.Debug($"Persist PlPks: writing {tmpLimitUsageFacts.Count:N0} rows");

                    foreach (LimitUsageFact tmpLimitUsageFact in tmpLimitUsageFacts)
                    {
                      tmpLoader.SetValue(
                        tmpSliceDateColumnName,
                        tmpLimitUsageFact.SliceDate);
                      tmpLoader.SetValue(
                        tmpLimitDefIdColumnName,
                        tmpLimitUsageFact.LimitDefId);
                      tmpLoader.SetValue(
                        tmpLimitVerColumnName,
                        tmpLimitUsageFact.LimitVer);
                      tmpLoader.SetValue(
                        tmpPlPkColumnName,
                        tmpLimitUsageFact.PlPk);

                      tmpLoader.NextRow();
                    }
                  }
                  finally
                  {
                    tmpLoader.Close();
                    tmpCurrentClassLogger.Debug("Persist PlPks: done");
                  }
                }
                #endregion

[Pinturiccio]

Sorry for the late response.

Your code is not complete, some type declarations are missing. We have used the following code instead:

Code: Select all

static void Main(string[] args)
{

    String tmpSliceDateColumnName = "SLICE_DATE";
    String tmpLimitDefIdColumnName = "LIMIT_DEF_ID";
    String tmpLimitVerColumnName = "LIMIT_VER";
    String tmpPlPkColumnName = "PL_PK";

    #region Bulk store PlPks

    OracleConnection conn = new OracleConnection("Host=***;sid=***;direct=true;uid=***;pwd=***");
    conn.Open();
    using (var tmpLoader = new OracleLoader($"LIMIT_USAGE_FACT", conn))
    {
        tmpLoader.CreateColumns();
        tmpLoader.BatchSize = 50 * 1000;
        tmpLoader.NotifyAfter = 50 * 1000;

        Int32 tmpRowsPersisted = 0;

        tmpLoader.RowsCopied += (inSender, inArgs) =>
        {
            tmpRowsPersisted += inArgs.RowsCopied;
            //tmpCurrentClassLogger.Debug($"Persist PlPks: {inArgs.RowsCopied:N0} rows copied ({tmpRowsPersisted:N0} so far)");
            Console.WriteLine($"Persist PlPks: {inArgs.RowsCopied:N0} rows copied ({tmpRowsPersisted:N0} so far)");
        };

        tmpLoader.Error += (inSender, inArgs) =>
        {
            if (Debugger.IsAttached)
                Debugger.Break();

            inArgs.Ignore = true;
        };

        foreach (OracleLoaderColumn tmpColumn in tmpLoader.Columns.Cast<OracleLoaderColumn>()
            .ToList()
            .Where(
            inColumn => !new[]
            {
                tmpSliceDateColumnName,
                tmpLimitDefIdColumnName,
                tmpLimitVerColumnName,
                tmpPlPkColumnName,
            }.Contains(inColumn.Name)))
            tmpLoader.Columns.Remove(tmpColumn);

        tmpLoader.Open();
        try
        {
            List<LimitUsageFact> tmpLimitUsageFacts = Enumerable.Range(1, 60000000).Select(i => new LimitUsageFact() { SliceDate = new DateTime(new DateTime(1900, 01, 01).Ticks + i), LimitDefId = i, LimitVer = i, PlPk = i }).ToList();

            //tmpCurrentClassLogger.Debug($"Persist PlPks: writing {tmpLimitUsageFacts.Count:N0} rows");
            Console.WriteLine($"Persist PlPks: writing {tmpLimitUsageFacts.Count:N0} rows");

            foreach (LimitUsageFact tmpLimitUsageFact in tmpLimitUsageFacts)
            {
                tmpLoader.SetValue(
                    tmpSliceDateColumnName,
                    tmpLimitUsageFact.SliceDate);
                tmpLoader.SetValue(
                    tmpLimitDefIdColumnName,
                    tmpLimitUsageFact.LimitDefId);
                tmpLoader.SetValue(
                    tmpLimitVerColumnName,
                    tmpLimitUsageFact.LimitVer);
                tmpLoader.SetValue(
                    tmpPlPkColumnName,
                    tmpLimitUsageFact.PlPk);

                tmpLoader.NextRow();
            }
        }
        finally
        {
            tmpLoader.Close();
            //tmpCurrentClassLogger.Debug("Persist PlPks: done");
            Console.WriteLine("Persist PlPks: done");
            conn.Close();
        }
    }
    #endregion
}

private class LimitUsageFact
{
    public LimitUsageFact()
    {
    }

    public DateTime SliceDate { get; set; }
    public int LimitDefId { get; set; }
    public int LimitVer { get; set; }
    public int PlPk { get; set; }
}

We cannot reproduce the issue with this code. As the result, 60m rows are inserted into Oracle database. Please tell us how we should modify this code for reproducing the issue.

[motuzko]

what's the oracle DB version you used?

[Pinturiccio]

We did the tests with Oracle 11.2. Please tell us your Oracle server version and dotConnect for Oracle version.