Several OraDirect.NET problems

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Alexey.mdr
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Post by Alexey.mdr » Thu 21 Feb 2008 10:53

We found the reason for the different timing (when using several Oracle clients).
Yes, your test program shows that the time may differ up to 10-15 times.
The source of such dispersion lies in the incorrect measuring.
You shouldn't use stopwatch inside the thread procedure that uses shared resources.
This will cause thread interference, resulting in the corruption of measured time.
Instead it's better to measure time of execution between all threads started and all threads stopped.

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

Post by Alladin » Thu 21 Feb 2008 12:26

As far as I know, shared resources in this case are oracle sessions, which are taken from the session pool. When session is obtained, it is not a shared resource anymore, until it is closed, right?

However, as you may notice, I didn't measure the time, db drivers need to provide a session from the pool. What is measured is just a plain data retrieval, which could be roughly compared among all providers.

BTW, the changes you've made in my test proggie have not really changed measurement logic. Moreover, adding items in ArrayList from multiple threads without synchronization is dangerously wrong.

Alexey.mdr
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Post by Alexey.mdr » Thu 21 Feb 2008 14:54

Moreover, adding items in ArrayList from multiple threads without synchronization is dangerously wrong.
Your remark is absolutely correct.

In the original program measured time depends on
synchronization types inside components and Oracle clients.
This way, it's not possible to estimate correctly the time spent by all threads.

We didn't reproduce the error with hanging Oracle Client.

I've changed the code. Now the program measures all the time spent by all threads
(including all operations with connections).

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;data source=ora1110;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) {

            int cnt = 200;
            Trace.WriteLine(string.Format("+++++++++++++++++ {0} +++++++++++++++++++", driver));            
            var tl = new List();
            Stopwatch swatch = new Stopwatch();            
            swatch.Start();
            for (int i = 0; i  t.Join());
            swatch.Stop();
            long time = swatch.ElapsedMilliseconds;
            Trace.WriteLine("+++++++++++++++++++++++++");
            Trace.WriteLine("Average time: " + time);
            Trace.WriteLine("+++++++++++++++++++++++++");
        }

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

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

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

        private void Form1_Load(object sender, EventArgs e) { }
    }
}
I made 3 tests for each data provider.
This is what I got:
OraDirect .NET
12767+10219+10460 | average 11148
ODP.NET
20290+20297+20191 | average 20259
System.Data.OracleClient
23958+23898+24238 | average 24031

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

Post by Alladin » Thu 21 Feb 2008 18:56

Could you please try it with different Oracle client/Oracle server combinations.

I'm interested in Oracle 9i & Oracle 10i servers atm.

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

Post by Alladin » Thu 21 Feb 2008 19:44

Still I can't explain why MS & Oracle drivers work, but CoreLab fails with OutOfMemory exception...

Alexey.mdr
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Post by Alexey.mdr » Fri 22 Feb 2008 10:16

OraDirect .NET dynamically allocates 26 037 bytes for each row (for the posted table only).
If we read one row in each thread we'll have:
25390 * 300 = 7617000 = 7Mb
OraDirect .NET dynamically allocates memory for 20 – 640 rows.
So, we'll have 4874700 Kb used.
The algorithm of memory allocation computed for high-speed performance.

Please use command FetchSize property to lower memory usage.
Try the program posted above with 9 and 10 Oracle clients.

Post Reply