c# - EF 核心与 Devart 提供程序，光标共享问题

问题描述

我们的 WEB API 使用了大约 2 年，突然一些高负载使我们的一个 API 工作非常缓慢，DBA 说这是因为游标共享问题，这本身就是因为 NLS 参数正在改变。

API 仅使用 EF Core 2.1 和 Devart 提供程序 (9.6.621) 供 oracle 进行简单查询：

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

这里有什么问题？DBA 说这个查询有超过 300-500 个游标。是参数绑定问题，EF 还是 Devart（我们使用旧版本，但在更新之前我们必须确定），如何控制 NLS 设置？

更新：DBA 看到的。

    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

这是由 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

标签： c#oracleentity-frameworkentity-framework-coredevart