Long response time for a query

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
Olof
Posts: 8
Joined: Thu 09 Jun 2011 11:18

Long response time for a query

Post by Olof » Fri 24 Jun 2022 06:44

Hi, my setup :

Devart DocConnect for Oracle 9.10.909.0
Windows Server 2016
ASP.NET 4.7.2 (WebForms)
EF 6
Oracle client 11.2.0.1.0
Oracle Database 11.2.0.4.0

With eavy load on our App, a request who take an average of 300 ms to execute, sometimes take for example 50 s ! This is the same request (with others parameters but with aproximatively the same amount of data returned).

The App is monitored with AppDynamics, so I can see where the time is consumed :

Code: Select all

Cloee2.Domains.Evaluations.Business.Evaluations.NotesDetaillees.GetDataEpreuveForSaisieNotesDetailleesBusiness:Do
  System.Linq.Enumerable:ToList
    Constructor of System.Collections.Generic.List`1
      System.Data.Entity.Core.Common.Internal.Materialization.Shaper`1+SimpleEnumerator:MoveNext
        System.Data.Entity.Core.Common.Internal.Materialization.Shaper`1:StoreRead
          Devart.Data.Oracle.OracleDataReader:Read
            Devart.Data.Oracle.dw:b
              OciDynamicType:OCIStmtFetch
The first line is in our code. This method make some LINQ requests and this graph show one of them.

On database, the worse request take 219 ms, so the problem don't came from the database.

No extra load on the app server (memory, CPU, disks).

I know that I have to upgrade to a newer version of DotConnect, but I can't do it now because it's too risky.

Any idea why OciDynamicType.OCIStmtFetch take so long ?


Thank you

DmitryGm
Devart Team
Posts: 152
Joined: Fri 11 Dec 2020 10:27

Re: Long response time for a query

Post by DmitryGm » Wed 29 Jun 2022 11:35

Kindly be enifrmed that OCIStmtFetch() call fetches rows from a query. This can take a long time, especially if the amount of data returned is large.

Also, we would highly recommend updating dotConnect for Oracle to the latest version - 9.16.1434, which may improve performance. See the Revision History: https://www.devart.com/dotconnect/oracl ... story.html

Post Reply