Web app suffers slow performance after upgrading to Oracle 19c
Posted: 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.
Instead the issue persists. For clarity the SQL statement is nothing more than:
The exact same SQL statement run directly in TOAD is super quick with results returned in less than a second.
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.
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:
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");
Code: Select all
SELECT * FROM tableA WHERE condition1='Y' and condition2 = 'Y'
- 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.
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)
}
}
}
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?