Several OraDirect.NET problems

[Alladin]

Hi there,

I want to make selects with dynamic amount of elements in IN construct in my multithreading WebService environment and consume the data.

Something like this:

select * from mytable where id in (1,2,3,4,5)

I can do this by parameterising set content like this:

1) define UDT MyValues
CREATE TYPE MYVALUES AS TABLE OF NUMBER;

2) convert my select in form of:
select * from my table where id in (select * from table(:param))

3) using Ora.NET create an OracleArray (f.e. myParam) of type MyValues.

4) Fill it with data like myParam.Add(value);

5) Set OracleParameter :param to myParam object.

6) Execute query.

7) while (reader.Read()) { consume data; }

8) Repeat from step 3 in different threads at least 300 times using built-in connection pooling.

So far tasks are clear. Now the problems:

1) Why does this operation work in combination with Oracle 10g (10.2.0.1) and outdated (4.30.25) OraDirect.NET, but hangs with latest (4.35.28) version of OraDirect.NET? Using Oracle 11g client, however, latest version of OraDirect.NET works too. Is this instability caused by Oracle team or by Corelab one?

2) Ok, if combination of latest OraDirect.NET with different Oracle clients is not reliable and simply unpredictable, logical answer would be not to rely on Oracle client at all, minimizing possible error field. However, there is another problem - UDT somehow do not work in Direct mode.

3) Multithreading tests of different providers (OraDirect, MS, Oracle) show that OraDirect is far from the best.

For example, pool of 100 Oracle connections is not possible because of Out Of Memory problem (MS & Oracle however don't have this problem).

In default configuration OraDirect.NET requires (at least) twice more memory than other providers in reading data using DataReader with blobs, looks like OracleDataReader tries to read all provided data into memory, instead of just feeding it to DataReader consumers.

Using Oracle client 9.2.0.6, in multithreading configuration OraDirect.NET sporadically crashes with various exceptions starting from Corrupted memory and ending with Cannot close sessions with active operations...

So, my worries now - how to make things work stable and memory inaggressive?

P.S. ODP.NET supports UDT now too, but UDT implementation is buggy as well.

[Alexey.mdr]

1) We regularly carry out performance tests on different databases in different environments. The tests show that OraDirect .NET is much better than ODBC, OLEDB, and ODP.Net. Besides that OraDirect .NET has got the biggest number of features among all data providers, including ODP.Net.
I understand that catching a problem could be difficult, but try to localize the problem, please. We need to know where exactly the error showed up. Have you tried waiting for a while? Probably there is wrong connection settings configuration. Please provide us with a sample.
2) UDT are not supported in Direct mode. They are only accessed with OCI i.e. via client.
3) Our multithreading tests of OraDirect .NET show approximately the same level of performance comparing to other providers. The testing process is quite complicated and it depends on various factors (computer hardware/software configurations, network type, network load, using of OracleObject types, proper thread synchronization etc.) Please, provide some code, where you use BLOB and OracleObjects.

We didn't encounter the problem with connection pooling. Even if you have 100 connection in the pool, make sure you have closed all readers and writers before closing the connection and it size will be as small as possible in the ConnectionPool, otherwise the connection will keep inside additional information.

Try to decrease DataReader.FetchSize property in order to minimize memory usage in multithreading application.

It is strongly recommended to use only one connection per thread. Also be aware of the proper synchronization of your application.

[Alladin]

Here is a simple example (you need to adjust connection settings & table name to succeed, out table has about 700 records, 1 blob column with average 2kb content):

Code: Select all

  public partial class Form1 : Form
  {
    //    private static string driver = "System.Data.OracleClient";
    //    private static string driver = "Oracle.DataAccess.Client";
    private static string driver = "CoreLab.Oracle";
    private static string connectionString = "Data Source=playarea;User Id=testuser;Password=testuser;max pool size=100";

    private static DbProviderFactory dbProv = DbProviderFactories.GetFactory(driver);

    public Form1()
    {
      InitializeComponent();
    }

    private void btnMultipleReports_Click(object sender, EventArgs e)
    {
      Trace.WriteLine(string.Format("+++++++++++++++++ {0} +++++++++++++++++++", driver));

      int cnt = 300;

      var tl = new List();

      for (int i = 0; i  t.Join());
    }

    private int _started;
    private int _finished;
    private int _running;
    private long _total;
    private long _min = long.MaxValue;
    private long _max = long.MinValue;

    public void ThreadProc1()
    {
      int started;
      int finished;
      int running;
      long total;

      lock (this)
      {
        _started++;
        _running++;

        started = _started;
      }

      Stopwatch swatch = new Stopwatch();

      using (var connection = dbProv.CreateConnection())
      {
        connection.ConnectionString = connectionString;
        connection.Open();

        swatch.Start();

        using (var cmd = connection.CreateCommand())
        {
          cmd.CommandText = "select * from testtable";

          using (var reader = cmd.ExecuteReader())
            while (reader.Read())
              reader.GetValue(0);
        }

        swatch.Stop();
      }

      long time = swatch.ElapsedMilliseconds;

      lock (this)
      {
        _total += time;
        _finished++;
        _running--;

        _min = Math.Min(_min, time);
        _max = Math.Max(_max, time);

        running = _running;
        finished = _finished;
        total = _total;
      }

      Trace.WriteLine(string.Format("Thread stopped in {3} ms ({5}/{4}/{6} ms). Started: {0}, Finished: {1}, Running: {2}", started, finished, running, time, total / finished, _min, _max));
    }
}

As you see, nothing really special... But try to run this program with different providers, you'll see what I mean

[Alexey.mdr]

Could you please post your database table definition?

[Alladin]

create table TESTTABLE
(
TESTOBJECTIDENT NUMBER(9) not null,
RULEIDENT NUMBER(9) not null,
VIOLATIONIDENT NUMBER(9) not null,
CHECKIDENT NUMBER(9) not null,
EXPOSURE FLOAT,
KEYINFO VARCHAR2(600),
RULEVERSION NUMBER(9) not null,
REMARK VARCHAR2(4000),
NUMBER_OF_DAYS NUMBER(9),
STARTDATE DATE,
ENDDATE DATE,
MAXIMUM_EXPOSURE FLOAT,
WAS_SEEN_BY VARCHAR2(30),
IS_CLOSED NUMBER(1),
WAS_SEEN_BY2 VARCHAR2(30),
TO_BE_REPORTED NUMBER(1),
WAS_SEEN_AT2 DATE,
WAS_SEEN_AT DATE,
WKNLIST VARCHAR2(4000),
VIOLATIONFACTOR FLOAT,
MAXVF FLOAT,
MAXEXPDATE DATE,
FROMLASTQUARTAL NUMBER(1),
REPORTVIOLATIONID NUMBER(9),
NAVINEURO FLOAT,
FOREACHATTRIBUTE VARCHAR2(80),
FOREACHEXPOSURES VARCHAR2(4000),
RULEFROMTYPE NUMBER(9),
ALLFOREACHEXPOSURES VARCHAR2(4000),
PATH VARCHAR2(255),
PARAMS VARCHAR2(4000),
EXPLANATION CLOB,
CHECKDATE DATE default SYSDATE not null,
SEVERITYIDENT NUMBER(9) not null,
OLDSEVERITYIDENT NUMBER(9),
EXPLANATION_BLOB BLOB,
DATAIDENT NUMBER(9),
POSCHECKDETAILIDENT NUMBER(9),
FLAG_DELETION NUMBER(1),
REPLACEDVIOLATION NUMBER(9),
CORRECTED NUMBER(9),
NUMBER_OF_NAVDATES NUMBER(9),
TECHREM VARCHAR2(4000),
PRIORVIOLATION NUMBER(9)
)

[Alladin]

I can send you also the content of this table, if this is needed

[Alladin]

http://jetasoft.de/testtable.pde

[Alexey.mdr]

We will test your example.

[Alexey.mdr]

What tool do you use to import/export data?
Could you please make a dump file (*.dmp) and send it to our support team?

[Alladin]

PL/SQL Developer export file...

[Alexey.mdr]

We are investigating your example right now.
I'll let you know about our progress in several days.

[Alladin]

Looking forward for your reply...

BTW. There is a new issue found. Queries returning columns of some OracleObject (create type as object) roughly 2-3 times slower with CoreLab in context with Oracle Client 10 than with Oracle Client 9...

However retrieving flattened columns from object types is blazing fast... No idea what happens behind the scene...

[Alexey.mdr]

Your remarks will be carefully examined.
We will come back with a summarization soon.

[Alladin]

Sorry, forgot to mention that PLSQL Developer export tables are locale sensitive.

Please alter session to GERMAN NLS_LANG before import, otherwise lots of number fields will not be imported...

[Alexey.mdr]

I have made subtle changes to your application, so I can get the average time at the end of the test.

Code: Select all

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Common;
using CoreLab.Data;
using CoreLab.Oracle;
using System.Diagnostics;
using System.Threading;
using System.Collections;

namespace UserPerfomanceTestOracle {

    public partial class Form1 : Form {
            //private static string driver = "System.Data.OracleClient";
            //private static string driver = "Oracle.DataAccess.Client";
            private static string driver = "CoreLab.Oracle";
            private static string connectionString = "user id=scott;password=tiger;server=ora1110;home=OraClient11g_home1;max pool size=100";            
            private ArrayList arList = new ArrayList();
        private static DbProviderFactory dbProv = DbProviderFactories.GetFactory(driver);

        public Form1() {
            InitializeComponent();          
          
        }

        private void btnMultipleReports_Click(object sender, EventArgs e) {

            Trace.WriteLine(string.Format("+++++++++++++++++ {0} +++++++++++++++++++", driver));

            int cnt = 200;

            var tl = new List();

            for (int i = 0; i  t.Join());
            long avTime = 0;
            foreach (Object o in arList) {
                avTime += (long)o;
            }
            Trace.WriteLine("+++++++++++++++++++++++++");
            Trace.WriteLine("Average time: "+avTime/cnt);
            Trace.WriteLine("+++++++++++++++++++++++++");

        }

        private int _started;
        private int _finished;
        private int _running;
        private long _total;
        private long _min = long.MaxValue;
        private long _max = long.MinValue;

        public void ThreadProc1() {
            int started;
            int finished;
            int running;
            long total;

            lock (this) {
                _started++;
                _running++;

                started = _started;
            }

            Stopwatch swatch = new Stopwatch();

            using (var connection = dbProv.CreateConnection()) {
                connection.ConnectionString = connectionString;
                connection.Open();

                swatch.Start();

                using (var cmd = connection.CreateCommand()) {
                    cmd.CommandText = "select * from testtable";
                    ((CoreLab.Oracle.OracleCommand)cmd).FetchSize = 10;

                    using (var reader = cmd.ExecuteReader())
                        while (reader.Read())
                            reader.GetValue(0);
                }

                swatch.Stop();
            }

            long time = swatch.ElapsedMilliseconds;

            lock (this) {
                _total += time;
                _finished++;
                _running--;

                _min = Math.Min(_min, time);
                _max = Math.Max(_max, time);

                running = _running;
                finished = _finished;
                total = _total;
            }

            arList.Add(time);
            Trace.WriteLine(string.Format("Thread stopped in {3} ms ({5}/{4}/{6} ms). Started: {0}, Finished: {1}, Running: {2}", started, finished, running, time, total / finished, _min, _max));
        }
        
        private void Form1_Load(object sender, EventArgs e) {
            
           
        }
    }
}

Here are my results in milliseconds.
I made 10 tests (for each provider):

Code: Select all

-->OraDirect .NET
2992 1989 2023 2743 1855 1936 2331 2586 2198 2480 
---------
average 2313

-->ODP 
3307 6592 6098 3747 4531 3565 4538 4368 12051 3256
------
average 5205

-->System.Data.OracleClient
3770 2809 2372 2780 2087 2986 2556 2142 2304 3122
-----
average 2692

But as to memory usage:
OraDirect .NET used more memory compared to other providers.
We are investigating this issue.
I reckon that is why you have got different results.
Please try limiting OracleCommand.FetchSize to 100.

Code: Select all

...
cmd.CommandText = "select * from testtable";
                    ((CoreLab.Oracle.OracleCommand)cmd).FetchSize = 100;
...