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

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
motuzko
Posts: 54
Joined: Tue 08 Sep 2009 18:02
Contact:

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

Post by motuzko » Thu 18 Jan 2018 20:19

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
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

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

Post by Pinturiccio » Fri 26 Jan 2018 13:46

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.


Pinturiccio
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

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

Post by Pinturiccio » Mon 29 Jan 2018 16:07

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

Post Reply