Oracle 日期范围SQL调优

嗨，

我有以下查询:

SELECT
  sum(SAL_DXC.CEXREALPROG + SAL_DXC.CEXNONPROG),
  CM_T_PERIODO_DI.CODPERIODO,
  CM_T_PERIODO_DI.ANO,
  CM_T_PERIODO_DI.NUMMES
FROM
  CM_T_PERIODO_DI,
  CM_T_DXC_HE  SAL_DXC,
  CM_T_HOSPITAL_DI  CM_T_HOSPITAL_DI_IDHOSP
WHERE
  ( CM_T_HOSPITAL_DI_IDHOSP.IDHOSPITAL=SAL_DXC.IDHOSPITAL  )
  AND  ( SAL_DXC.IDDATASALTROZO between CM_T_PERIODO_DI.IDDATADESDE and  CM_T_PERIODO_DI.IDDATAHASTA and SAL_DXC.TROZOFINMOV=1  )
  AND  ( CM_T_PERIODO_DI.TIPOPERIODO  =  'Meses'
  )
  AND  ( CM_T_PERIODO_DI.TIPOPERIODO  =  'Meses'  )
  AND  ( CM_T_PERIODO_DI.TIPOPERIODO  =  'Meses'  )
  AND  
  (
   CM_T_HOSPITAL_DI_IDHOSP.NOMHOSP  =  'C.H. DE OURENSE'
   AND
   CM_T_PERIODO_DI.CODPERIODO  BETWEEN  '2019/01'  AND  '2019/10'
  AND  (CM_T_PERIODO_DI.TIPOPERIODO  =  'Meses'
)
  )
GROUP BY
  CM_T_PERIODO_DI.CODPERIODO,
  CM_T_PERIODO_DI.ANO,
  CM_T_PERIODO_DI.NUMMES


----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                           |    13 |  1118 |       |   198   (1)| 00:00:03 |
|   1 |  HASH GROUP BY                   |                           |    13 |  1118 |  1104K|   198   (1)| 00:00:03 |
|   2 |   NESTED LOOPS                   |                           | 10922 |   917K|       |   197   (0)| 00:00:03 |
|   3 |    NESTED LOOPS                  |                           | 10922 |   917K|       |   197   (0)| 00:00:03 |
|   4 |     MERGE JOIN CARTESIAN         |                           |    14 |   966 |       |    10   (0)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID | CM_T_HOSPITAL_DI          |     1 |    35 |       |     2   (0)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN           | CM_T_HOSPITAL_DI_NOMHOSP  |     1 |       |       |     1   (0)| 00:00:01 |
|   7 |      BUFFER SORT                 |                           |    14 |   476 |       |     8   (0)| 00:00:01 |
|*  8 |       TABLE ACCESS BY INDEX ROWID| CM_T_PERIODO_DI           |    14 |   476 |       |     8   (0)| 00:00:01 |
|*  9 |        INDEX RANGE SCAN          | CM_IX_PERIODO_TIPOPERIODO |   456 |       |       |     1   (0)| 00:00:01 |
|* 10 |     INDEX RANGE SCAN             | CM_IX_DXC_IDDATATROZO     |   109 |       |       |     0   (0)| 00:00:01 |
|* 11 |    TABLE ACCESS BY INDEX ROWID   | CM_T_DXC_HE               |   760 | 12920 |       |    13   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - access("CM_T_HOSPITAL_DI_IDHOSP"."NOMHOSP"='C.H. DE OURENSE')
   8 - filter("CM_T_PERIODO_DI"."CODPERIODO">='2019/01' AND "CM_T_PERIODO_DI"."CODPERIODO"<='2019/10')
   9 - access("CM_T_PERIODO_DI"."TIPOPERIODO"='Meses')
  10 - access("SAL_DXC"."IDDATASALTROZO">="CM_T_PERIODO_DI"."IDDATADESDE" AND 
              "SAL_DXC"."IDDATASALTROZO"<="CM_T_PERIODO_DI"."IDDATAHASTA")
  11 - filter("SAL_DXC"."TROZOFINMOV"=1 AND "CM_T_HOSPITAL_DI_IDHOSP"."IDHOSPITAL"="SAL_DXC"."IDHOSPITAL
              ")
复制

该查询在较小的日期范围内运行良好，但超过一年，它运行非常缓慢，并且使用SAL_DXC全扫描更好。如何使优化器知道日期范围太大而无法使用索引？

非常感谢，
维克多

该查询有几个范围搜索:

SAL_DXC.IDDATASALTROZO between CM_T_PERIODO_DI.IDDATADESDE and  CM_T_PERIODO_DI.IDDATAHASTA 
AND
CM_T_PERIODO_DI.CODPERIODO  BETWEEN  '2019/01'  AND  '2019/10'
复制

正如昨天的SQL Office Hours中所讨论的那样，优化器很难对这些进行良好的估计。

当录音可用时，您可以在以下位置查看:

https://asktom.oracle.com/pls/apex/f?p=100:551:::NO:RP,551:P551_CLASS_ID:6623

为了TL; 同时博士...

您可以使用一些策略。从大致最简单/最小影响到最困难/最大影响开始:

Increase Dynamic Stats Level

添加

/*+ dynamic_sampling ( 11 ) */
复制

提示或设置会话中的OPTIMIZER_DYNAMIC_SAMPLING使优化器能够在选择计划时捕获有关表的额外统计信息。

仅此一项就足以使其获得范围谓词的更好估计。从而选择一个更好的计划。

Create Indexes

该查询使用索引来定位CM_T_PERIODO_DI和CM_T_DXC_HE的行。但是随后会进一步过滤表上的行。使用索引准确查找所需的行更有效。然后从表中只读取那些行。

所以我会尝试创建索引:

CM_T_PERIODO_DI ( TIPOPERIODO, CODPERIODO )
CM_T_DXC_HE ( TROZOFINMOV, IDHOSPITAL, IDDATASALTROZO )
复制

创建这些之后，您可能会得到相同的计划 “形状”。但是优化器将能够在索引中的步骤8和10进行过滤。所以它从表中访问更少的行。

因此，即使使用动态统计信息 “修复” 了您的计划，我仍然会尝试这些。

Check the Stats

第10行估计索引将定位109行。但是在步骤11，它估计760行。

所以不知何故，它认为它将使用索引来查找 ~ 100行。但是然后从表上返回大约7倍!

这是... 可疑。检查你的表和索引统计是最新的。

NB-增加dynamic_samping应该已经在您的查询中解决了这个问题。

Upgrade!

从12c起，优化器可以使用自适应计划。这些使查询能够根据第一个表返回的行数在嵌套循环和哈希联接之间进行选择。这可能会启动，使优化器能够完全扫描CM_T_DXC_HE并将其哈希连接到其他表。

当然，升级需要付出很多努力，还有其他影响，并且可能无法改善您的查询!但是11g是over a decade旧了。因此，无论如何，您应该在路线图上 “很快” 发生。