Devart Forums

Posted: **Tue 06 Dec 2011 12:51**

Hi Guys,
in a particular case of our infrastructure we need to manage a DB containing all our cached reports in order to quickly retrieve the information for our customers.

Our cache db is a very stupid single table DB, one table with one PK (Guid / raw(16)) and one BLOB column (min 5 KB, max 2 MB, avg 200 KB).

Our access strategy against this DB is:
- read (24/7)
- ins/upd (3 hours per day)

Taking apart all the pure DB performance considerations such as Partitioning, SECUREFILES vs BASIC BLOB, IOT, LOB Chuncking, Dedicated Tablespace Block Size and so on, we noticed that while we are inserting/updating very frequently, with an high degree of parallelism, the performances goes down and investigating the causes we found that the 92% of the time used by DB is related to this wait event: "SQL*Net more data from client".
This means that the DB has already received the update/insert command but it is waiting for receive the whole LOB (because the lobs are sent from client to server and viceversa chunked).

I suppose that this should happen also during read phase but the read phase is spreaded during all the 24 hours so it is less impacted by this wait event.

In our previous projects, we already addressed this problem using the Oracle ODP.NET and setting a command parameter (InitialLOBFetchSize), this parameter sets the amount of byte sent for each chunck (or something like that).

My question is: Exists something similar that we can use in devart dotConnect for Oracle that works using EntityFramework?
If you need we can provide a solution that reproduce this Oracle wait event.

Thanks in advance,
Monducci Marco

Posted: **Thu 08 Dec 2011 10:36**

MonDeveloper wrote:we noticed that while we are inserting/updating very frequently, with an high degree of parallelism, the performances goes down

MonDeveloper wrote:In our previous projects, we already addressed this problem using the Oracle ODP.NET and setting a command parameter (InitialLOBFetchSize), this parameter sets the amount of byte sent for each chunck (or something like that).

Sending LOB to the database with one round trip should have a better performance than the one of multiple round trips which send the same LOB.

MonDeveloper wrote:If you need we can provide a solution that reproduce this Oracle wait event.

Please send us a small test project with your test data and corresponding DDL script so that we can compare the performance of dotConnect for Oracle and ODP.NET in this particular case.

Posted: **Wed 14 Dec 2011 22:17**

Hello,

I'm experiencing the same problem, reading and writing of CLOB values is 3 to 4 times slower than the same operation using VARCHAR values (in our 1-GBit-LAN).
In a 34-MBit-WAN of one of our customers it's 400 to 600 times slower.
ODP.NET is much faster, especially when InitialLOBFetchSize is set to -1 for the SELECT command.

Here's the sample code:

Code: Select all

using (var c = new Devart.Data.Oracle.OracleConnection("Data Source=***;User Id=***;Password=***"))
{
    c.Open();
    try
    {
        var cmddrop = c.CreateCommand();
        cmddrop.CommandText = "DROP TABLE TEMP_PERFTEST";
        cmddrop.ExecuteNonQuery();
    }
    catch
    {
    }

    var cmd = c.CreateCommand();
    cmd.CommandText = "CREATE TABLE TEMP_PERFTEST(data clob)"; //use varchar2(2000) instead
    cmd.ExecuteNonQuery();
                
    cmd = c.CreateCommand();
    cmd.CommandText = "INSERT INTO TEMP_PERFTEST(data) VALUES(:data)";
    var p = cmd.CreateParameter();
    p.ParameterName = "data";
    p.OracleDbType = Devart.Data.Oracle.OracleDbType.Clob; //use VarChar instead
    cmd.Parameters.Add(p);

    var sw = System.Diagnostics.Stopwatch.StartNew();
    for(int i = 0; i < 10000; i++)
    {
        p.Value = new string('x', 1000);
        cmd.ExecuteNonQuery();
    }
    Console.WriteLine(sw.Elapsed);

    cmd = c.CreateCommand();
    cmd.CommandText = "SELECT data FROM TEMP_PERFTEST";
    var r = cmd.ExecuteReader();
    while (r.Read())
    {
        string test = r.GetValue(0) as string;
    }
    Console.WriteLine(sw.Elapsed);
}

I hope there's a way to optimize this behavior.

Posted: **Mon 19 Dec 2011 14:27**

Thank you for your test code. We are investigating the difference in behaviour between dotConnect for Oracle and ODP.NET.

Posted: **Fri 25 Sep 2015 12:47**

We have added the InitialLobFetchSize property to the OracleCommand in dotConnect for Oracle 8.4.333.

Now you can assign, for example, 1024 to this property, and it will significantly improve reading CLOB data in the specified example.

Posted: **Thu 07 Jul 2016 11:59**

We have improved the performance of inserting CLOB data. We have decreased the performance difference with ODP.NET when writing CLOB data. For additional optimization, you may omit OracleDbType for the corresponding dotConnect parameter. In this case it will be determined automatically. If the size of the value is less than 4000 characters, it will be passed as varchar. Otherwise, it will be passed as CLOB.

We will post here when the corresponding build of dotConnect for Oracle is available for download.

Posted: **Thu 07 Jul 2016 13:51**

New build of dotConnect for Oracle 9.1.55 is available for download!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to viewtopic.php?t=33944

Devart Forums

Oracle LOB Fetch Size

Oracle LOB Fetch Size

Sample code

Re: Oracle LOB Fetch Size

Re: Oracle LOB Fetch Size

Re: Oracle LOB Fetch Size