Several OraDirect.NET problems

Several OraDirect.NET problems

Postby Alladin » Tue 05 Feb 2008 16:05

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.
Alladin
 
Posts: 149
Joined: Mon 27 Nov 2006 16:18

Postby Alexey.mdr » Wed 06 Feb 2008 11:09

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.
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Here is a simple example

Postby Alladin » Wed 06 Feb 2008 12:31

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 <= cnt; i++)
      {
        Thread t = new Thread(new ThreadStart(ThreadProc1));
        tl.Add(t);
        t.Start();
      }

      tl.ForEach(t => 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
Alladin
 
Posts: 149
Joined: Mon 27 Nov 2006 16:18

Postby Alexey.mdr » Wed 06 Feb 2008 15:29

Could you please post your database table definition?
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Postby Alladin » Wed 06 Feb 2008 15:47

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
 
Posts: 149
Joined: Mon 27 Nov 2006 16:18

Postby Alladin » Wed 06 Feb 2008 15:47

I can send you also the content of this table, if this is needed
Alladin
 
Posts: 149
Joined: Mon 27 Nov 2006 16:18

Postby Alladin » Wed 06 Feb 2008 15:54

Alladin
 
Posts: 149
Joined: Mon 27 Nov 2006 16:18

Postby Alexey.mdr » Thu 07 Feb 2008 10:04

We will test your example.
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Postby Alexey.mdr » Thu 07 Feb 2008 14:02

What tool do you use to import/export data?
Could you please make a dump file (*.dmp) and send it to our support team?
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Postby Alladin » Thu 07 Feb 2008 14:07

PL/SQL Developer export file...
Alladin
 
Posts: 149
Joined: Mon 27 Nov 2006 16:18

Postby Alexey.mdr » Mon 11 Feb 2008 10:28

We are investigating your example right now.
I'll let you know about our progress in several days.
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Postby Alladin » Mon 11 Feb 2008 14:59

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...
Alladin
 
Posts: 149
Joined: Mon 27 Nov 2006 16:18

Postby Alexey.mdr » Mon 11 Feb 2008 15:10

Your remarks will be carefully examined.
We will come back with a summarization soon.
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Postby Alladin » Sun 17 Feb 2008 15:02

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...
Alladin
 
Posts: 149
Joined: Mon 27 Nov 2006 16:18

Postby Alexey.mdr » Wed 20 Feb 2008 17:05

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 <= cnt; i++) {

                Thread t = new Thread(new ThreadStart(ThreadProc1));
                tl.Add(t);
                t.Start();

            }

            tl.ForEach(t => 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;
...
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Next

Return to dotConnect for Oracle