- 3,715,500 Users
- 2,242,776 Discussions
- 7,845,368 Comments
Forum Stats
Discussions
Categories
- 15 Data
- 362.2K Big Data Appliance
- 6 Data Science
- 1.5K Databases
- 461 General Database Discussions
- 3.7K Java and JavaScript in the Database
- 22 Multilingual Engine
- 487 MySQL Community Space
- 3 NoSQL Database
- 7.6K Oracle Database Express Edition (XE)
- 2.8K ORDS, SODA & JSON in the Database
- 416 SQLcl
- 42 SQL Developer Data Modeler
- 184.8K SQL & PL/SQL
- 21K SQL Developer
- 1.8K Development
- 3 Developer Projects
- 32 Programming Languages
- 135.1K Development Tools
- 8 DevOps
- 3K QA/Testing
- 246 Java
- 5 Java Learning Subscription
- 10 Database Connectivity
- 66 Java Community Process
- 1 Java 25
- 9 Java APIs
- 141.1K Java Development Tools
- 6 Java EE (Java Enterprise Edition)
- 153K Java Essentials
- 135 Java 8 Questions
- 86.2K Java Programming
- 270 Java Lambda MOOC
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 10 Java SE
- 13.8K Java Security
- 3 Java User Groups
- 22 JavaScript - Nashorn
- 18 Programs
- 124 LiveLabs
- 30 Workshops
- 9 Software
- 3 Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 3 Deutsche Oracle Community
- 10 Español
- 1.9K Japanese
- 2 Portuguese
Huge performance degradation when upgrading to efcore 3.1beta or efcore 2.19.80 when connecting to o
we have applications use efcore connecting to oracle exadata 11g. It's been working well before 2.19.80. when i upgrade efcore for oracle to the latest version (2.19.80) or to beta version 3.1, things were getting wrong. Just as the following log shows: it takes nearly 2 minutes to complete one simple query which is unacceptable. before this, it tooks only serveral milliseconds. Can anyone help me?
dbug: Microsoft.EntityFrameworkCore.Database.Command[0]
2020-07-19 16:25:54.008715 ThreadID:9 (SQL) OracleRelationalCommand.ExecuteReaderAsync() : SELECT "p"."PRODUCTID" "ProductId", UPPER(LTRIM(RTRIM("p"."M_ALTERNATENAME"))) "JdeProduct" FROM "MESWIPSMT"."CONTAINER" "c" INNER JOIN "MESWIPSMT"."PRODUCT" "p" ON ("c"."PRODUCTID" = "p"."PRODUCTID") WHERE ("c"."CONTAINERNAME" = :barcode_0) and rownum <= 1
dbug: Microsoft.EntityFrameworkCore.Database.Command[20100]
Executing DbCommand [Parameters=[:barcode_0='?' (Size = 2000)], CommandType='Text', CommandTimeout='0']
SELECT "p"."PRODUCTID" "ProductId", UPPER(LTRIM(RTRIM("p"."M_ALTERNATENAME"))) "JdeProduct"
FROM "MESWIPSMT"."CONTAINER" "c"
INNER JOIN "MESWIPSMT"."PRODUCT" "p" ON ("c"."PRODUCTID" = "p"."PRODUCTID")
WHERE ("c"."CONTAINERNAME" = :barcode_0)
and rownum <= 1
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (95,670ms) [Parameters=[:barcode_0='?' (Size = 2000)], CommandType='Text', CommandTimeout='0']
SELECT "p"."PRODUCTID" "ProductId", UPPER(LTRIM(RTRIM("p"."M_ALTERNATENAME"))) "JdeProduct"
FROM "MESWIPSMT"."CONTAINER" "c"
INNER JOIN "MESWIPSMT"."PRODUCT" "p" ON ("c"."PRODUCTID" = "p"."PRODUCTID")
WHERE ("c"."CONTAINERNAME" = :barcode_0)
and rownum <= 1
dbug: Microsoft.EntityFrameworkCore.Database.Command[0]
2020-07-19 16:27:29.682973 ThreadID:9 (EXIT) OracleRelationalCommand.ExecuteReaderAsync()
Best Answer
-
This performance issue now looks to be the same as the other customer's performance issue. At least, we suspect the same root cause.
If the EF Core model does not accurately indicate which string properties are Unicode and which are not, performance can then becomes significantly degraded, including undertaking full scans. If the provider scaffolds the model, this problem shouldn't happen. When the mapping is manually created or the scaffold is modified, this problem could occur. It generally will happen when IsUnicode is used or not used for the column configuration.
The beta 2 modified the .NET string binding/mapping scheme. .NET strings will be mapped to NVARCHAR2. Previously, it was to VARCHAR2.
Can you take a look at your EF Core model and ensure that any .NET strings have accurately mapped the Unicode setting?
Answers
-
Did these performance problems occur with EF Core 3.1 Beta 1? The current beta requires ODP.NET 2.19.80 and is the second beta.
Are your other queries affected by a performance slow down with the beta 2?
Would it be possible to send a test case to the Oracle .NET team (dotnet_us(at)oracle.com)?
-
Performance degradation happened after i have updated my oracle entity framework core to 2.19.80, and i have also tested the beta entity framework core 3.1 beta1 and beta2. All of them cause huge performance degradation. So i have to switch back to 2.19.70 and things became normal again. By the way, i am using exdata 11g. Is oracle 11g already deprecated and have no official support any more?
-
Oracle Database 11g remans supported until December 2020. It's possible to extend support beyond that date if needed.
EF Core 3 made significant changes to LINQ to SQL conversion from EF Core 2. Of course, the purpose of the beta is to identify these issues and fix them, if possible. If you can provide the data requested above, the Oracle team will take a look.
-
We've tried to approximate your query running against DB 11.2.04, but have been unable to reproduce the performance problem. Do you mind sending us a complete test case? There's likely something very specific in your LINQ perhaps in combination with something in the schema causing the issue. You can reach us at dotnet_us(at)oracle.com.
-
I got the problem. EFcore generated sql:
SELECT "c"."CONTAINERID", "c"."CONTAINERNAME"
FROM "MESWIPSMT"."CONTAINER" "c"
WHERE ("c"."CONTAINERNAME" = N'C470295354ELFW0A6');It uses INDEX STORAGE FAST FULL SCAN execution plan, and costs very long time to get one record in huge record sets.
If I remove the 'N' prefix before string literal:
SELECT "c"."CONTAINERID", "c"."CONTAINERNAME"
FROM "MESWIPSMT"."CONTAINER" "c"
WHERE ("c"."CONTAINERNAME" = 'C470295354ELFW0A6');It will run as quickly as before, and execution plain different: INDEX SKIP SCAN.
Can you investigate how could N prefix affect performance so badly?
-
I filed bug 31684179 to track this issue. Is the second SQL with the N prefix the one that gets executed when using EF Core 2.1? If not, do you mind sharing what the EF Core 2.1 SQL is?
-
EFCore always generates "N" prefix for string literal in lambda expression which impact performance badly.
same sql generated by oracle.efcore 2.19.70 and 2.19.80:
SELECT "item"."PRODUCTID", "UPPER"("LTRIM"("RTRIM"("item.Product"."M_ALTERNATENAME"))) "JdeProduct"
FROM "MESWIPSMT"."CONTAINER" "item"
LEFT JOIN "MESWIPSMT"."PRODUCT" "item.Product" ON ("item"."PRODUCTID" = "item.Product"."PRODUCTID")WHERE ("item"."CONTAINERNAME" = :barcode_0)
AND ROWNUM <= 1;
but 2.19.80 or above (3.19.0-beta1 and beta2) needs over 100 seconds to complete query, but 2.19.70 needs just 3 milliseconds!
Entity Framework Core 2.2.6-servicing-10079 initialized 'OracleDbContext' using provider 'Oracle.EntityFrameworkCore' with options: MaxPoolSize=100 OracleSQLCompatibility=11
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (115,774ms) [Parameters=[:barcode_0='?' (Size = 2000)], CommandType='Text', CommandTimeout='0']
SELECT "item"."PRODUCTID", "UPPER"("LTRIM"("RTRIM"("item.Product"."M_ALTERNATENAME"))) "JdeProduct"
FROM "MESWIPSMT"."CONTAINER" "item"
LEFT JOIN "MESWIPSMT"."PRODUCT" "item.Product" ON ("item"."PRODUCTID" = "item.Product"."PRODUCTID")
WHERE ("item"."CONTAINERNAME" = :barcode_0)
and rownum <= 1
It seems like this SQL can not use INDEX and causes long time query. But this SQL under 2.19.70 runs very quickly. That's strange.
-
In the beta 2, the generated select query will not always prefix an ‘N’. It will be prefixed if the HasColumnType() fluent API for that column uses an N type. For example, HasColumnType(NVARCHAR2(2000)). If it is something you can change easily, please try it out.
If not, what is the full table definition and the indexes definition created on the table? What is the fluent API (HasColumnType, IsUnicode) set for the ‘CONTAINERNAME’ column in .NET?
Also, does the performance issue happen every time you execute or just the first execution? With another customer that had a different performance problem with beta 2, we saw that the first run was slower, but subsequent runs were 10 times faster.
If you have the time, could you run the below scenarios and enable the EFCORE and ODP.NET traces? Execute the runs in the same sequence.
1. Test case using Oracle EFCORE 2.x with ODP.NET 2.19.70 provider at least 5 times.
2. Test case using Oracle EFCORE 3.1 with ODP.NET 2.19.80 provider at least 5 times.
3. Test case using Oracle EFCORE 3.19.beta2 with ODP.NET 2.19.80 provider at least 5 times.
Do NOT to bounce the DB after each run and use the same DB for each run.
Please note, to enable EFCORE traces, set the LogLevel to Trace in 2.x and Debug in 3.x.
You can send the logs to us (dotnet_us(at)oracle.com). Thanks!
-
This performance issue now looks to be the same as the other customer's performance issue. At least, we suspect the same root cause.
If the EF Core model does not accurately indicate which string properties are Unicode and which are not, performance can then becomes significantly degraded, including undertaking full scans. If the provider scaffolds the model, this problem shouldn't happen. When the mapping is manually created or the scaffold is modified, this problem could occur. It generally will happen when IsUnicode is used or not used for the column configuration.
The beta 2 modified the .NET string binding/mapping scheme. .NET strings will be mapped to NVARCHAR2. Previously, it was to VARCHAR2.
Can you take a look at your EF Core model and ensure that any .NET strings have accurately mapped the Unicode setting?
-
Hi Alex, thanks for Your clarification. I have tested the new EFCORE with explicit annotation of "VARCHAR2' data type. Things work well with the annotation. How could I set this annotation as default for all STRING column. Before these new versions, string columns are all modeled as VARCHAR2. By the way, I am using Code-First style.
-
There should be no need to make any changes when Oracle EF Core scaffolds the model. VARCHAR2 columns should be mapped accurately. It's only when the model is manually created or modified does this problem occur.
Does the mismatch occur when using scaffolding without Fluent API changes? If you are generating the classes manually, any reason why you don't use scaffolding or at least begin customizing from what scaffolding generates?