Web app suffers slow performance after upgrading to Oracle 19c

Question

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<T> which I've used as an alternative way to get the data and eliminate the potential of a poor performing LINQ statement.

 _dbContext.ExecuteQuery<T>("sql here");

Instead the issue persists. For clarity the SQL statement is nothing more than:

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.

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. That's 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 am interested to learn about 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.

Update

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.

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"];
}

Answer 1

Ok, all sorted. The issue was actually the geographical location of the database vs the application server. Bearing in mind our business have offices in UK and Hong Kong, as such the database was hosted in HK with the app server in the UK. This distance between the two was the crux of the issue (I guess the database calls making their round trips with a higher latency compounded things). When we tested with another database instance held in the UK, the problem was solved. The moral of the story, for us at any rate, is to keep the database and app server in the same region.