Oracle查询仅在添加随机注释时运行一致

Question

编辑:已解决！感谢@kfinity。

AskTom 建议在查询开始时使用select /*+ opt_param('_optimizer_use_feedback‘'false') */禁用反馈使用。这为我解决了这个问题。

tldr:向查询添加随机化的注释使其运行一致，删除此注释会破坏它。

警告: long。

环境

我的首选工作方式是将源中的查询作为字符串处理，这样它们就处于版本控制中，我可以看到随着时间的推移而发生的变化。除此之外，我还使用了dapper和oracle.ManagedDataAccess NuGet包。有问题的应用程序是一个运行在.NET框架4.7.2上的WPF应用程序(在这两种情况下)。我使用的是Visual studio professional 2017 15.9.5。

问题所在

大约一年前，我在查询时遇到了这个问题。我不记得是谁了，我只知道我没有时间把它记录下来并张贴在这里。我现在知道了，我也遇到了同样的问题。那时，我不知何故发现，如果我重启电脑或更改查询文本，它就会再次正常运行。只是偶尔会出现问题的症状，我会在查询中添加注释，或者删除之前的注释，我会在每个版本中测试特定的函数。我每次都会测试它，因为如果它在我的机器上有故障，那么在目标用户机器上也会有故障。当时，我认为这是我的电脑的驱动程序/硬件问题。我想我可以通过更改查询文本来修复它，因为我会将整个查询从代码剪切并粘贴(ctrl-x ctrl-v)到Oracle developer中，然后在那里进行编辑。在某种程度上，我注意到，即使是一个额外的空格或回车也会让它再次工作。

回到现在，我又遇到了这个问题。这一次不同，因为它不再偶尔失败。这是非常一致的。回想一下我是如何判断这是一个驱动程序/硬件问题的，我在3台不同的机器上构建并运行了应用程序，结果都是一样的。我可以在Oracle developer中运行查询，使用ctrl + end来运行整个查询，而不仅仅是50行。它大约在10秒内运行。它一遍又一遍地一致运行，如果没有任何变化，这是有意义的。

如果我从我的应用程序运行它，它运行得很好-但只运行一次。这也需要大约10秒。如果我刷新数据，然后再次运行查询，它就会挂起。没有例外，如果我破坏了调试器，它只是在database.Query<>()调用上降温。如果我重新启动PC或更改查询，它将运行一次-恰好一次。

V$session_longops/全表扫描

当然，我去谷歌寻求帮助。我发现了一些有趣的文章，这些文章对我并没有真正的帮助：

https://mjsoracleblog.wordpress.com/2014/10/24/oracle-performance-mystery-wildly-varying-query-response-time/

Oracle inconsistent performance behaviour of query

直到我发现了这个：

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1191435335912

他们提到了v$session_longops，它理应让我们深入了解长期运行的操作。

select *
from v$session_longops
where time_remaining > 0

当我刚刚刷新了应用程序中的数据时，我运行了这段代码，导致查询再次运行。我看到了这个：

​

​

第一个查询运行得很好，索引也很好。第二次，它启动了全表扫描。它不需要这个，因为它在第一次运行时运行良好，在oracle developer中也是如此。正如预期的那样，如果我让它运行(耗时超过20分钟)，表扫描就会完成，并且我会得到与第一次相同的结果。

我发现您可以在不使用Oracle developer中的图形用户界面的情况下使用explain plan for来解释查询计划。这给了我两个截然不同的计划，Oracle developer中的那个总是有这样一个注释：- 'PLAN_TABLE' is old version。所以我不确定我能不能相信这些信息，我不知道该怎么处理它。

Plan from Oracle developer

Plan from code

修复方法

正如我之前所说的，添加或删除注释，或者更确切地说，更改查询文本可以修复问题，并且永远不会启动全表扫描。我在查询中添加了一个包含DateTime.Now的注释，所以它总是不同的。它始终有效。虽然从技术上讲，这解决了这个问题，但我认为对于一个更荒谬的问题来说，这是一个相当荒谬的解决方案。我宁愿知道为什么会发生这种情况，如果我做错了什么。所以问题是，为什么随机化的评论可以修复我的查询？

代码

一些上下文:这是一个ERP系统，查询获取具有层次结构的所有工作单，或者只包含它们自己的工作单，将它们组合在一起，然后添加它们所需的物料和一些其他信息，如它们的描述。它只对尚未关闭的工作单执行此操作。

SQL：

select
    --Hierarchic info, some aliases exceeded 30 chars and had to be shorted to current state
    hierarchic_workorders.ccn                   as HierarchicCcn ,
    hierarchic_workorders.mas_loc               as HierarchicMasLoc,
    hierarchic_workorders.wo_num                as HierarchicWoNum,
    hierarchic_workorders.wo_line               as HierarchicWoLine,
    wo.item                                     as HierarchicItem,
    wo.revision                                 as HierarchicRevision,    
    wo_item.description                         as HierarchicDescription,
    wo_rtg.wc                                   as HierarchicWorkCenter,
    hierarchic_workorders.startdate             as HierarchicStartDate,
    hierarchic_workorders.mfgclosedate          as HierarchicMfgClosedDate,
    hierarchic_workorders.chassisnumbers        as HierarchicChassisNumbers,
    hierarchic_workorders.wo_level              as HierarchicLevel,
    hierarchic_workorders.parent_wo_num         as HierarchicParentWoNum,
    hierarchic_workorders.parent_wo_line        as HierarchicParentWoLine,
    hierarchic_workorders.parent_wo_bom_useq    as HierarchicParentwobomuseq,

    --wo bom info
    wo_bom.ccn                  as WoRtgCcn,
    wo_bom.mas_loc              as WoRtgMasloc,
    wo_bom.wo_num               as WoRtgWoNum,
    wo_bom.wo_line              as WoRtgWoLine,
    wo_bom.wo_bom_useq          as WoRtgWobomUseq,
    wo_bom.item                 as WoRtgItem,
    wo_bom.revision             as WoRtgRevision,
    wo_bom_item.description     as WoRtgDescription,
    wo_bom.bom_comp_qty         as WoRtgBomCompQty,
    wo_bom.bom_commit           as WoRtgCommit,
    wo_bom.backflush            as WoRtgBackflush,
    wo_bom.issue_qty            as WoRtgIssueQty,
    wo_bom.commit_qty           as WoRtgCommitQty,
    wo_bom.reqd_qty             as WoRtgReqdQty

from live.wo_bom


--===========================================================================================================================================================================
-- Maybe it's possible to remove this or the other min operation join in hierarchic_workorders, to make it faster - not sure if possible ====================================
--===========================================================================================================================================================================
left join(
    select
        wo_rtg_min_operation.min_operation,
        wo_rtg.*
    from live.wo_rtg    
    left join(
        select
            ccn,
            mas_loc,
            wo_num,
            wo_line,
            lpad(to_char(min(to_number(trim(operation)))), 4, ' ')  as min_operation
        from live.wo_rtg
        group by ccn, mas_loc, wo_num, wo_line
    )wo_rtg_min_operation
        on  wo_rtg_min_operation.ccn     = wo_rtg.ccn
        and wo_rtg_min_operation.mas_loc = wo_rtg.mas_loc
        and wo_rtg_min_operation.wo_num  = wo_rtg.wo_num
        and wo_rtg_min_operation.wo_line = wo_rtg.wo_line
) wo_rtg
    on wo_rtg.ccn = wo_bom.ccn
    and wo_rtg.mas_loc = wo_bom.mas_loc
    and wo_rtg.wo_num = wo_bom.wo_num
    and wo_rtg.wo_line = wo_bom.wo_line
    --This case when is painfully slow but it can't really be cached or indexed
    and wo_rtg.operation = (
            case when wo_bom.operation = ' ' then
                wo_rtg.min_operation
            else 
               wo_bom.operation
            end
        )
--===========================================================================================================================================================================
-- Find all open MPS orders and highest hierarchic PRP orders. Having these be a subquery instead of the starting data drastically increases performance ========================
--===========================================================================================================================================================================
join(

    select
        ccn,
        mas_loc,
        wo_num,
        wo_line,
        startdate,
        mfgclosedate,
        chassisnumbers,
        wo_level,
        parent_wo_num,
        parent_wo_line,
        parent_wo_bom_useq
    from (
        --===========================================================================================================================================================================
        -- PRP ======================================================================================================================================================================
        --===========================================================================================================================================================================

        select
            'PRP' as type,

            wowob.ccn,
            wowob.mas_loc,
            wowob.wo_num,
            wowob.wo_line,

            apssplit_min_operation.operation_start_date as startdate,
            wo.mfg_close_date as mfgclosedate,
            trim(
                trim(wo.user_alpha2) || ' ' ||
                trim(wo.user_alpha3) || ' ' ||
                trim(wo.chassis3)    || ' ' ||
                trim(wo.chassis4)    || ' ' ||
                trim(wo.chassis5)
            ) as chassisnumbers,        
            level as wo_level,
            wowob.parent_wo_num,
            wowob.parent_wo_line,
            wowob.parent_wo_bom_useq
        from live.wowob

        join live.wo
            on wo.ccn = wowob.ccn
            and wo.mas_loc = wowob.mas_loc
            and wo.wo_num = wowob.wo_num
            and wo.wo_line = wowob.wo_line
        left join(
             select
                ccn,
                mas_loc,
                orderident,
                order_line,
                lpad(to_char(min(to_number(trim(operation)))), 4, ' ')  as min_operation,
                operation_start_date
            from live.apssplit
            where schedule = 'SHOP' and order_type = 'W'
            group by ccn, mas_loc, orderident, order_line, operation_start_date
        ) apssplit_min_operation
            on  apssplit_min_operation.ccn = wowob.ccn
            and apssplit_min_operation.mas_loc = wowob.mas_loc
            and apssplit_min_operation.orderident = wowob.wo_num
            and apssplit_min_operation.order_line = wowob.wo_line

        --Only select open wo's
        --Underlying wo's obviously have to start BEFORE their parents, we don't have to check them all for this reason.
        where apssplit_min_operation.operation_start_date is not null
        and   apssplit_min_operation.operation_start_date < sysdate + :days_ahead
            --wo.mfg_close_date is null and 
            --wo.fin_close_date is null and
            --wo.ord_qty - wo.scrap_qty - wo.complete_qty > 0
            --and wo.start_date < sysdate + :days_ahead    
            --and wowob.wo_num = '              334594'        

        --Grab the childs of only the highest parents.
        connect by prior wowob.ccn      = wowob.ccn
               and prior wowob.mas_loc  = wowob.mas_loc
               and prior wowob.wo_num   = wowob.parent_wo_num
               and prior wowob.wo_line  = wowob.parent_wo_line
        start with wowob.ccn || wowob.mas_loc || wowob.wo_num || wowob.wo_line in (
            --Subquery to select all the highest hierarchic wowob's that are still open in wo.
            --Performance:
                --all: 21253 in ?
                --Open only: 174 in 0.155 seconds
            select
                 wowob.ccn || wowob.mas_loc || wowob.wo_num || wowob.wo_line as wowob_key
            from live.wowob

            --Parent join
            left join live.wowob parent_wowob
                on wowob.ccn               = parent_wowob.ccn
                and wowob.mas_loc           = parent_wowob.mas_loc
                and wowob.parent_wo_num     = parent_wowob.wo_num
                and wowob.parent_wo_line    = parent_wowob.wo_line
            --end parent join

            where   wowob.ccn =       :ccn
                and wowob.mas_loc = :mas_loc
                and parent_wowob.ccn is null
        )

        union all


        --===========================================================================================================================================================================
        -- MPS ======================================================================================================================================================================
        --===========================================================================================================================================================================

        select 
            'MPS' as type,
            wo.ccn,
            wo.mas_loc,
            wo.wo_num,
            wo.wo_line,

            apssplit_min_operation.operation_start_date as startdate,
            wo.mfg_close_date as mfgclosedate,
            trim(
                trim(wo.user_alpha2) || ' ' ||
                trim(wo.user_alpha3) || ' ' ||
                trim(wo.chassis3)    || ' ' ||
                trim(wo.chassis4)    || ' ' ||
                trim(wo.chassis5)
            ) as chassisnumbers,    
            1 as wo_level,
            '' as parent_wo_num,
            '' as parent_wo_line,
            '' as parent_wo_bom_useq    
        from live.wo
        join live.item_ccn
            on  item_ccn.ccn        = wo.ccn
            and item_ccn.item       = wo.item
            and item_ccn.revision   = wo.revision
            and item_ccn.mastsched = 'Y' --mps
            and item_ccn.planned = ' '   --mrp
            and item_ccn.prp = ' '       --NOT prp...

        left join(
             select
                ccn,
                mas_loc,
                orderident,
                order_line,
                lpad(to_char(min(to_number(trim(operation)))), 4, ' ')  as min_operation,
                operation_start_date
            from live.apssplit
            where schedule = 'SHOP' and order_type = 'W'
            group by ccn, mas_loc, orderident, order_line, operation_start_date
        ) apssplit_min_operation
            on  apssplit_min_operation.ccn = wo.ccn
            and apssplit_min_operation.mas_loc = wo.mas_loc
            and apssplit_min_operation.orderident = wo.wo_num
            and apssplit_min_operation.order_line = wo.wo_line

        --Only select open wo's
        --Underlying wo's obviously have to start BEFORE their parents, we don't have to check them all for this reason.
        where apssplit_min_operation.operation_start_date is not null
        and   apssplit_min_operation.operation_start_date < sysdate + :days_ahead
    )
    order by startdate

) hierarchic_workorders
    on  hierarchic_workorders.ccn       = wo_bom.ccn
    and hierarchic_workorders.mas_loc   = wo_bom.mas_loc
    and hierarchic_workorders.wo_num    = wo_bom.wo_num
    and hierarchic_workorders.wo_line   = wo_bom.wo_line


--===========================================================================================================================================================================
-- Descriptions from wo. wowob and wo_bom are different items and they have different descriptions. =========================================================================
--===========================================================================================================================================================================
left join live.wo
    on  wo.ccn      = hierarchic_workorders.ccn
    and wo.mas_loc  = hierarchic_workorders.mas_loc
    and wo.wo_num   = hierarchic_workorders.wo_num
    and wo.wo_line  = hierarchic_workorders.wo_line

left join live.item wo_item
    on  wo_item.item     = wo.item
    and wo_item.revision = wo.revision

left join live.item wo_bom_item
    on  wo_bom_item.item     = wo_bom.item
    and wo_bom_item.revision = wo_bom.revision

C# (不工作)：

using (IDbConnection database = new OracleConnection(_applicationSettings.OracleConnectionString))
{
    DynamicParameters parameters = new DynamicParameters();
    parameters.Add("ccn", ccn);
    parameters.Add("mas_loc", masLoc);
    parameters.Add("days_ahead", daysAhead);

    return database.Query<HierarchicWoWoBom>(Query, parameters).ToList();
}

C# (工作稳定)：

using (IDbConnection database = new OracleConnection(_applicationSettings.OracleConnectionString))
{
    DynamicParameters parameters = new DynamicParameters();
    parameters.Add("ccn", ccn);
    parameters.Add("mas_loc", masLoc);
    parameters.Add("days_ahead", daysAhead);

    return database.Query<HierarchicWoWoBom>($"-- {DateTime.Now}: randomized comment so that this query keeps working.\n" 
                                      + Query, parameters).ToList();
}

Answer 1

我不确定这是否真的是一个答案，但评论太长了。

我认为快速的第一个查询和缓慢的第二个查询通常表示a statistics/cardinality feedback issue。

基本上，在第一次运行查询时，优化器可能会检测到当前表/索引统计信息中估计的基数(行数)非常不准确，因此它会尝试缓存更准确的统计信息，以便下次运行相同的查询。但有时这实际上会让事情变得更糟。

作为一种快速修复方法，您可以尝试使用/*+ opt_param('_optimizer_use_feedback' 'false') */提示禁用该功能，或者使用AskTom suggests计划管理来保存好的计划，如上所述。

从长远来看，我认为这可能表明你的一些统计数据可能已经过时了？您可以通过执行cardinality tuning并查找计划the actual rows are a lot higher than the expected rows中的位置来缩小问题统计范围。基本过程是使用/*+ GATHER_PLAN_STATISTICS */提示运行查询，然后执行SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));以查看结果。