EF core with Devart provider, Cursor sharing problem

Question

We are live about 2 years with our WEB APIs and suddenly some high load made our one API work very slow and DBA says that it's because of Cursor sharing problem which itself is because of NLS parameters are changing.

API just uses EF Core 2.1 with Devart provider (9.6.621) for oracle to make simple query :

public IQueryable<NotificationEndUserViewEntity> InboxQuerable(string userId, string clientId)
    {
        var client = _dbContext.Clients.FirstOrDefault(x => x.ClientId == clientId);
        var inboxQuerable = _dbContext
                                 .NotificationsUserView
                                    .Where(nots =>
                                        !nots.IsCancelled &&
                                        !nots.IsDeleted &&
                                        nots.IsOutGoing &&
                                        nots.ActiveFrom < DateTime.Now &&
                                        (nots.ActiveTill == null || nots.ActiveTill > DateTime.Now) &&
                                        nots.ClientId == client.Id &&
                                        nots.RecipientId == userId);
        return inboxQuerable.AsNoTracking();
    }

 var firstMastRead = await InboxQuerable(userId, clientId)
                                    .OrderByDescending(x => x.ActiveFrom)
                                    .FirstOrDefaultAsync(x => x.IsMustRead && !x.IsRead);

What can be the problem here? DBA says that there more then 300-500 cursors for this query. Is it prameter binding problem, EF or Devart (We use old versions but before update we hav to be sure about it), how can I control NLS settings?

UPDATE: which DBA sees.

    SELECT nots.Id,
        nots.ActiveFrom,
        nots.ActiveTill,
        nots.BodyEn,
        nots.BodyKa,
        nots.ClientId,
        nots.HasAttachment,
        nots.IsCancelled,
        nots.IsDeleted,
        nots.IsMustRead,
        nots.IsNoReplay,
        nots.IsOutGoing,
        nots.IsRead,
        nots.RecipientId,
        nots.SubjectEn,
        nots.SubjectKa
   FROM VW_USER_NOTIFICATIONS nots
  WHERE     (    (    (    (    (    (    (nots.IsCancelled = :"SYS_B_0")
                                      AND (nots.IsDeleted = :"SYS_B_1"))
                                 AND (nots.IsOutGoing = :"SYS_B_2"))
                            AND (nots.ActiveFrom < CURRENT_DATE))
                       AND (   nots.ActiveTill IS NULL
                            OR (nots.ActiveTill > CURRENT_DATE)))
                  AND (nots.ClientId = :p__client_Id_0))
             AND (nots.RecipientId = :p__userId_1))
        AND ((nots.IsMustRead = :"SYS_B_3") AND (nots.IsRead = :"SYS_B_4")) 
ORDER BY nots.ActiveFrom DESC FETCH FIRST :"SYS_B_5" ROWS ONLY

Which is generated by Linq :

SELECT 
nots.Id, 
nots.ActiveFrom, 
nots.ActiveTill, 
nots.BodyEn, 
nots.BodyKa, 
nots.ClientId, 
nots.HasAttachment, 
nots.IsCancelled, 
nots.IsDeleted, 
nots.IsMustRead, 
nots.IsNoReplay, 
nots.IsOutGoing, 
nots.IsRead, 
nots.RecipientId,
nots.SubjectEn, 
nots.SubjectKa  FROM VW_USER_NOTIFICATIONS nots WHERE (((((((nots.IsCancelled = 0)
    AND (nots.IsDeleted = 0)) 
    AND (nots.IsOutGoing = 1)) 
    AND (nots.ActiveFrom < CURRENT_DATE))
    AND (nots.ActiveTill IS NULL OR (nots.ActiveTill > CURRENT_DATE))) 
    AND (nots.ClientId = :p__client_Id_0)) 
    AND (nots.RecipientId = :p__userId_1)) 
    AND ((nots.IsMustRead = 1) AND (nots.IsRead = 0)) ORDER BY nots.ActiveFrom DESC FETCH FIRST 1 ROWS ONLY

That looks pretty clean. So I am at a loss. Does "VW" mean view? Any voodoo in the view? — granadaCoder, Jul 23 '20 at 16:10
Yes vw is view and nothing special in it, simple joins. They say that it does not matter what is in view, when I make select on it, Oracle tries to parse my query, while some NLS parameters are changing and Cursor sharing not working. After some googling and searching, found some hint about parameter binding, but can't understand exacly what. Ah and it's happening on every view and table, I thinks it's some devart bug but... — Dadroid, Jul 23 '20 at 16:18
"FETCH FIRST 1 ROWS ONLY" not sure where that is coming from. I would fork your code and try a 3.1 driver.........and see if you get same behavior. The 2.1 ef drivers (esp oracle related) were pretty weak. except sql-server.which is ef's tightest buddy of course. — granadaCoder, Jul 23 '20 at 16:29

granadaCoder · Answer 1 · 2020-07-23T13:29:59.930

//DBA says that there more then 300-500 cursors for this query.//

You, as the developer, (IMHO) ..... should always look at the generated SQL, especially when doing non trivial query creation.

So here is a breadcrumb to get that wired up.

https://github.com/granadacoder/oracle-ef-issues-demo/blob/master/src/DataLayer.EntityFramework/Contexts/EfPlaygroundDbContext.cs#L66

This will allow you to share the SQL that is generated.

If you log and research your logs, you want to be very watchful of comments like

"could not be translated and will be evaluated locally."

My guess is that you may be getting the super-number-of-cursors because of this issue.

In my sample, you can see those warnings in the below breadcrumb.

https://github.com/granadacoder/oracle-ef-issues-demo/blob/master/src/ConsoleOne/Program.cs#L160

===========================

Also, I noticed this:

nots.ActiveTill > DateTime.Now

Try to create a local variable and use it.......vs having the DateTime.Now "inline".

Example:

DateTime myNow = DateTime.Now;

and later

nots.ActiveTill > myNow

I don't remember exactly, but that "inline" thing is ringing my inner voice.

Slightly offtopic, consider using an IDateTimeProvider vs using DateTime directly.

https://www.nuget.org/packages/Chronos.Abstractions/1.0.2

===========================

Also, I have not used the Devart provider, but the Oracle.EF driver suffers from a huge issue with what I call the "column alias bug".

My example here:

https://github.com/granadacoder/oracle-ef-issues-demo

Bug report(s) here:

https://support.oracle.com/rs?type=bug&id=31234903

https://support.oracle.com/rs?type=bug&id=31240915

(the "discussion" is here : https://community.oracle.com/thread/4327311 )

PS

The Oracle 3.1 EF driver looks a lot better than the 2.1 version. I think the certification process must have been alot more rigorous for 3.1. And maybe a reason (a guess on my part) why the Oracle 3.1 EF driver took so long (I guess since it is in beta, it is actually "taking so long").. to get out the door.

https://www.nuget.org/packages/Oracle.EntityFrameworkCore/

Of course, that may be why devart is a popular option.

In summary.

Know how to see the generated SQL.

Know how to Debug/Trace the logs for warnings about the generated SQL.

Adjust your C# code to avoid the warnings.

ORM's are "fickle" sometimes. At the end of the day, they create SQL, but that SQL needs to be viewed critically.

Thank you for your hints. We know about sql generation and "could not be translated and will be evaluated locally." thing and this is not a case for us. About DateTime.now, I will check it, As we look in to the logs, generated sql gives ActiveTill > CURRENT_DATE, seems right but maybe the reason for NLS settings change. — Dadroid, Jul 23 '20 at 13:53
Do you get a single generated SQL statement? If so, post that complete single SQL statement in your original question. — granadaCoder, Jul 23 '20 at 14:04

EF core with Devart provider, Cursor sharing problem

1 Answers1