Forum Stats

  • 3,722,461 Users
  • 2,244,316 Discussions
  • 7,849,849 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Huge performance degradation when upgrading to efcore 3.1beta or efcore 2.19.80 when connecting to o

User_51LBJ
User_51LBJ Member Posts: 5
edited August 2020 in ODP.NET

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

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,772 Employee
    edited August 2020 Accepted 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

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,772 Employee
    edited July 2020

    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)?

  • User_51LBJ
    User_51LBJ Member Posts: 5
    edited July 2020

    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?

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,772 Employee
    edited July 2020

    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.

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,772 Employee
    edited July 2020

    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.

  • User_51LBJ
    User_51LBJ Member Posts: 5
    edited July 2020

    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?

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,772 Employee
    edited July 2020

    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?

  • User_51LBJ
    User_51LBJ Member Posts: 5
    edited July 2020

    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.

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,772 Employee
    edited July 2020

    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!

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,772 Employee
    edited August 2020 Accepted 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?

  • User_51LBJ
    User_51LBJ Member Posts: 5
    edited August 2020

    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.

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,772 Employee
    edited August 2020

    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?

Sign In or Register to comment.