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.
Several OraDirect.NET problems
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.
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.
-
- Posts: 729
- Joined: Thu 13 Dec 2007 10:24
Your remark is absolutely correct.Moreover, adding items in ArrayList from multiple threads without synchronization is dangerously wrong.
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) { }
}
}
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
-
- Posts: 729
- Joined: Thu 13 Dec 2007 10:24
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.
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.