sql - 如何在不更改查询的情况下改进此 oracle 11g 计划选择？

问题描述

我有一组 oracle 11g 数据库表的数据重新填充方案，它需要：

删除并重新创建所有表。滴就像DROP TABLE TCA_SECURITY_DAY_BAR CASCADE CONSTRAINTS PURGE
加载静态数据 - 即查找表，然后加载一些与日期相关的数据

在加载过程的不同时间，我穿插收集统计命令，我发现这些命令大大加快了加载过程本身的各种查询。但是，我对我的选择没有信心，因为它们是通过猜测选择的。

目标表收集调用。例子：

dbms_stats.gather_table_stats(                            
    ownname => 'TCA',                                     
    tabname => 'TCA_SECURITY_DAY_BAR',               
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,      
    block_sample => TRUE,                                 
    method_opt => 'FOR ALL INDEXED COLUMNS SIZE 1',       
    cascade => TRUE                                       
);

架构调用：示例：

dbms_stats.gather_schema_stats(
    ownname => 'TCA',
    estimate_percent => NULL,
    block_sample => TRUE,
    method_opt => 'FOR ALL INDEXED COLUMNS SIZE 1',
    cascade => TRUE,
    granularity => 'ALL',
    options => 'GATHER'                                                                                                                     
)

在重新加载过程之后，我运行了一些报告，并且我有一个问题查询类，当它可以/应该是亚秒级时需要 7 秒。我已经对查询进行了改进，例如使用参数并在 where 子句中更具体。带着这个问题，我不是试图改进查询，而是试图改进引擎选择的计划，或者至少了解它的神秘行为。

查询是从列表达式列表（未分组的、聚合的）生成的，其中我chunk将列表分为若干列（例如 3、4、5），以查看报告中的最佳视觉效果。对于给定的一组连接子句，选择的列表达式的数量和其他一些因素（一天中的时间、缓存、温度，谁知道）影响优化器是选择快速计划还是慢速计划。

这是具有 1 个分组列multiplier和 5 个非分组聚合表达式的示例查询：

SELECT
    TO_.multiplier
    -- multiplier.bracket
    ,   min(TCA.TCA_SECURITY.symbol)
    -- symbol.min
    ,   min(TO_.px_limit)
    -- px_limit.min
    ,   min(TCA.TCA_SECURITY_DAY_BAR.px_open_day)
    -- px_open_day.min
    ,   min(TCA.TCA_SECURITY_DAY_BAR.px_high_day)
    -- px_high_day.min
    ,   min(TCA.TCA_SECURITY_DAY_BAR.px_low_day)
    -- px_low_day.min
FROM TCA_ORDER TO_
    LEFT JOIN TCA_ORDER_SECURITY ON (TO_.ID = TCA_ORDER_SECURITY.ORDER_ID)
    LEFT JOIN TCA.TCA_SECURITY TCA_SECURITY ON (TCA_ORDER_SECURITY.SECURITY_ID = TCA_SECURITY.ID)
    LEFT JOIN TCA.TCA_SECURITY_DAY_BAR ON (
        TCA_SECURITY.ID = TCA_SECURITY_DAY_BAR.SECURITY_ID
        AND TO_.TRADE_DATE = TCA_SECURITY_DAY_BAR.TRADE_DATE
    )

WHERE TRADING_UNIT_ID IN (621)
GROUP BY TO_.multiplier

观察：

在将查询与不同数量的列进行比较时，我没有在两者之间运行统计信息，并且我相信它们没有运行（因为设置是每晚进行）。
一旦一个慢查询模式就位，它会在一段时间内保持一致。
最初，我发现 5 列的查询总是很慢（> 7 秒）
如果我只有 4 个，那就快了。
第二天5突然快了。但是选择 4 列很慢。
当我将记录的慢速运行查询复制/粘贴到 SQL Developer 时，它们很快
截至今天早上 - 3 快，4 慢，5 快

我知道架构设置为每晚运行统计信息，但我真的不知道用于此的确切命令。如果那些夜间统计数据收集电话不是特别的，我可以自己按需调用它们，这可能会有所帮助——我只需要知道如何准确地模仿它。

为了尝试排除故障，在我的 kotlin/jdbc 代码中，我使用了解释上次计划运行的调用。这是一个 diff，显示了一个 5 列快速和 4 列慢的查询。很明显，由于某种原因，它在慢速情况下选择了“NESTED LOOPS OUTER”，我不知道为什么。

因此，我正在寻找对可能发生的事情的解释以及在不更改查询的情况下影响优化器选择的建议。

标签： sqloracleoptimizationjdbcsql-execution-plan

sql - 如何在不更改查询的情况下改进此 oracle 11g 计划选择？

问题描述

解决方案

推荐阅读