Web app suffers slow performance after upgrading to Oracle 19c

Discussion of open issues, suggestions and bugs regarding LinqConnect – Devart's LINQ to SQL compatible ORM
Post Reply
loganfox
Posts: 5
Joined: Thu 17 Mar 2022 12:02

Web app suffers slow performance after upgrading to Oracle 19c

Post by loganfox » Thu 24 Mar 2022 09:27

Where to start with this one! Ok, I have a c# MVC web application (.NET 4.52) using an older version of Devart LinqConnect to scaffold the database etc. The app has been around for 10 years with an Oracle 11g backend database without issue.

Our DBA team exported the schema/database and imported it over to an Oracle 19c environment. Since repointing the web app to use the new schema on 19c we've seen a huge hit to performance.

After a ton of debugging I've targeted a table which is small enough to test out some ideas as it drives a view on a page which is performing badly. When I say badly, its render time was on average 2 secs on 11g and now taking up to 25 seconds on 19c.

I isolated a line of code using a LINQ statement to get a filtered dataset from one of the entities (which returns 13 records, so really nothing much).

In Devart there is a feature .ExecuteQuery which I've used as an alternative way to get the data and eliminate the potential of a poor performing LINQ statement.

Code: Select all

 _dbContext.ExecuteQuery<T>("sql here");
Instead the issue persists. For clarity the SQL statement is nothing more than:

Code: Select all

SELECT * FROM tableA WHERE condition1='Y' and condition2 = 'Y'
The exact same SQL statement run directly in TOAD is super quick with results returned in less than a second.
  • I've since upgraded Devart LinqConnect to their latest version (as of yesterday).
  • I've recreated the model with the assistance from LinqConnect's ORM (this was to eliminate any legacy settings that perhaps wasn't working well on 19c.
Despite the above steps I still didn't see any positive results. I went as far as creating a brand new test application in .NET Core 3.1 using Oracles EF packages from Nuget. I scaffolded the database, did some data retrieval on the same table and got the same performance issue. It seems to be related to the two CLOB fields in this table. There isn't really much content in them as they're just test records but I guess the nature of the data type is causing something to go awry since the migration to 19c.

In the test app using the option 'Direct=true;' in the connection string actually improved the response times on the table down to something like 3 seconds. However, this setting doesn't have any noticable impact on our main app that uses Devarts own drivers.

I did find however in the Devart ORM a setting to 'delay loading' for fields of the table/model. I gave that a go and enabled delayed loading for both CLOB fields and voila, response times return back to normal. I guess this works because the data from the CLOB field isn't being accessed in realtime, but probably more like lazy loading at the field level maybe?

Unfortunately this doesn't solve the issue as the results are passed back up the chain where some code puts the results into another data model which is enriched with more data from related entity tables.

Code: Select all

public statifc Func<CustomerTable, CustomerViewModel> CustomerViewModelProjection
{
    get 
    {
         return cust => new CustomerViewModel 
         {
             Name = cust.Name,
             Surname = cust.Surname
             Age = (int?) cust.Age
             Rating = cust.Billing.Max(x => x.BillingStatic.RatingName)
         }
    }
}
Even though there are no other CLOB fields in the other entity tables (that I can find) the performance during this next block of code is also performing really badly. It's basically taking the results from the first call and making a more friendly/enriched version that can pull in linked data via lazy loading. The above example isn't reflective of the actual code but gives you an idea. The new model thats being created doesn't even reference the CLOB fields from the first call. So despite having 'delayed loading' enable this bit of code behaves as if it were accessing these fields in the background for some reason. Thats just a guess of course.

In summary:
  • The issue started by repointing the backend database from Oracle 11g to 19c.
  • No code changes were done.
  • LINQ to SQL as far as I can tell, isn't the issue.
  • Recreating the entity model completely using the latest LinqConnect software from Devart made no tangible impact.
  • CLOBs do seem to be a playing a direct role in the performance hit, but why would this happen?
I'd really appreciate some ideas from the community as to possible causes. My gut feel is that it's around the prefetching of the CLOBs. I just can't identify why a change from 11g to 19c would cause such a problem. Again, running these statements in TOAD are fine and responsive, it's just between the web app and the database. The database is hosted in the cloud with a response time of around 350ms.

loganfox
Posts: 5
Joined: Thu 17 Mar 2022 12:02

Re: Web app suffers slow performance after upgrading to Oracle 19c

Post by loganfox » Thu 24 Mar 2022 10:06

Update since my original post In case it helps, I've also just tried using the OracleCommand approach to negate anything to do with EF/Devart. I'm still seeing the same performance hit when it comes to those clobs. In the below rough example, 13 records take around 14 seconds to be iterated. If I comment out the two clob fields, iteration is fast/near instant. In other test runs using 'Direct=true' in the connection string didn't help much/do anything noticeable to improve performance.

Code: Select all

var con = new OracleConnection("connstringhere");
con.Open();
var cmd = new OracleCommand();
cmd.Connection = con;
cmd.CommandText = "SELECT * FROM tableA WHERE condition1='Y' and condition2 = 'Y'";
OracleDataReader dr = cmd.ExecuteReader();

while (dr.Read())) {
  var d1 = dr["ID"];
  var d2 = dr["CLOBFIELD1"];
  VAR d3 = dr["CLOBFIELD2"];
}

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Web app suffers slow performance after upgrading to Oracle 19c

Post by Shalex » Thu 05 May 2022 08:35

loganfox wrote: Thu 24 Mar 2022 10:06 Update since my original post In case it helps, I've also just tried using the OracleCommand approach to negate anything to do with EF/Devart. I'm still seeing the same performance hit when it comes to those clobs. In the below rough example, 13 records take around 14 seconds to be iterated. If I comment out the two clob fields, iteration is fast/near instant. In other test runs using 'Direct=true' in the connection string didn't help much/do anything noticeable to improve performance.

Code: Select all

var con = new OracleConnection("connstringhere");
con.Open();
var cmd = new OracleCommand();
cmd.Connection = con;
cmd.CommandText = "SELECT * FROM tableA WHERE condition1='Y' and condition2 = 'Y'";
OracleDataReader dr = cmd.ExecuteReader();

while (dr.Read())) {
  var d1 = dr["ID"];
  var d2 = dr["CLOBFIELD1"];
  VAR d3 = dr["CLOBFIELD2"];
}
Sorry for the delayed response. Try setting cmd.InitialLobFetchSize to different values to check if it makes any difference. The value is the number of bytes of LOB data to prefetch and cache when querying data containing LOB columns. Try 100, 1000, 10000, etc.

Post Reply