Oracle 19c性能问题-长期操作

Question

我有一个应用程序，在非常定期的基础上针对数据库运行自动化进程。不幸的是，语句是在ORM中构建的，不能重写。

声明如下：

SELECT t0.id FROM SCHEMA.CMTS t0, SCHEMA.CMNTSRCH t3, SCHEMA.CONT t1, SCHEMA.YU t2 WHERE (
  (
    t0.YUtypeyn = 0
    OR
    t0.YUtypeyn = 1  
    )
  AND
  (
    (
      t1.contcd IN ('FD')
      OR
      t1.contcd IS NULL
    )
    AND
    (
      t2.TRNSMDCD = 'RD'
      AND
      t2.spid = 1
      AND
      t0.compyn = NULL
      AND
      (
        NOT
        (
          upper(t3.curryuloc) LIKE upper('ABC%') ESCAPE '\'
        )
        AND
        NOT
        (
          upper(t3.curryuloc) LIKE upper('DEF%') ESCAPE '\'
        )
        AND
        NOT
        (
          upper(t3.curryuloc) LIKE upper('GHI%') ESCAPE '\'
        )
        AND
        NOT
        (
          upper(t3.curryuloc) LIKE upper('%JKL%') ESCAPE '\'
        )
        AND
        NOT
        (
          upper(t3.curryuloc) LIKE upper('%MNO%') ESCAPE '\'
        )
        AND
        NOT
        (
          upper(t3.curryuloc) LIKE upper('%PQR%') ESCAPE '\'
        )
        AND
        NOT
        (
          upper(t3.curryuloc) LIKE upper('%STU%') ESCAPE '\'
        )
        AND
        NOT
        (
          upper(t3.curryuloc) LIKE upper('%VWX%') ESCAPE '\'
        )
        AND
        NOT
        (
          upper(t3.curryuloc) LIKE upper('%YZ%') ESCAPE '\'
        )
      )
      AND
      t0.cancelledyn = NULL
    )
  )
)
AND
t0.CMNTSRCHid = t3.id
AND
t0.contcd = t1.contcd
AND
t0.YUid = t2.id(+) 

在运行此语句时，已注意到性能问题。在监视v$session_longops时，我注意到对CMNTSRCH进行了全表扫描。

​

​

到目前为止，一个完整的表格扫描已经过去了2640秒--这引起了人们的关注。

解释计划如下：

------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                      |     1 |    59 |     0   (0)|          |
|*  1 |  FILTER                       |                      |       |       |            |          |
|   2 |   NESTED LOOPS                |                      |     1 |    59 | 26677   (1)| 00:00:02 |
|   3 |    NESTED LOOPS               |                      |     1 |    59 | 26677   (1)| 00:00:02 |
|   4 |     MERGE JOIN CARTESIAN      |                      |     1 |    27 | 26674   (1)| 00:00:02 |
|   5 |      NESTED LOOPS             |                      |     1 |    13 | 24369   (1)| 00:00:01 |
|*  6 |       INDEX UNIQUE SCAN       | CONT_PK              |     1 |     5 |     0   (0)| 00:00:01 |
|*  7 |       TABLE ACCESS FULL       | CMNTSRCH             |     1 |     8 | 24369   (1)| 00:00:01 |
|   8 |      BUFFER SORT              |                      | 36824 |   503K|  2305   (1)| 00:00:01 |
|*  9 |       INDEX FAST FULL SCAN    | IDX_YU20220408       | 36824 |   503K|  2305   (1)| 00:00:01 |
|* 10 |     INDEX RANGE SCAN          | CMNT_013             |     1 |       |     2   (0)| 00:00:01 |
|* 11 |    TABLE ACCESS BY INDEX ROWID| CMNT                 |     1 |    32 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

在查看语句时，我决定基于t3.id和t3.curryuloc创建一个基于函数的索引，并检查了解释计划--它似乎提高了CPU成本，现在执行索引快速全扫描，而不是表扫描。

新的解释计划(在基于函数的索引创建之后)：

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                                |     1 |    59 |     0   (0)|          |
|*  1 |  FILTER                       |                                |       |       |            |          |
|   2 |   NESTED LOOPS                |                                |     1 |    59 |  3975   (1)| 00:00:01 |
|   3 |    NESTED LOOPS               |                                |     1 |    59 |  3975   (1)| 00:00:01 |
|   4 |     MERGE JOIN CARTESIAN      |                                |     1 |    27 |  3972   (1)| 00:00:01 |
|   5 |      NESTED LOOPS             |                                |     1 |    13 |  1667   (2)| 00:00:01 |
|*  6 |       INDEX UNIQUE SCAN       | CONT_PK                        |     1 |     5 |     0   (0)| 00:00:01 |
|*  7 |       INDEX FAST FULL SCAN    | IDX_CMNTSRCH_20220407          |     1 |     8 |  1667   (2)| 00:00:01 |
|   8 |      BUFFER SORT              |                                | 36824 |   503K|  2305   (1)| 00:00:01 |
|*  9 |       INDEX FAST FULL SCAN    | IDX_YU20220408                 | 36824 |   503K|  2305   (1)| 00:00:01 |
|* 10 |     INDEX RANGE SCAN          | CMNT_013                       |     1 |       |     2   (0)| 00:00:01 |
|* 11 |    TABLE ACCESS BY INDEX ROWID| CMNT                           |     1 |    32 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------

然而，这次手术也花费了相当长的时间。

​

​

监视v$session_longops，我可以看到新会话何时运行该语句。SOFAR从大约4000块开始，速度越来越慢，当它达到5500块时，它几乎停止了运转。强制使用基于函数的索引似乎对操作完成所需的时间没有任何影响。

不过，奇怪的是，如果我使用应用程序绑定到参数的相同参数手动运行该语句，则几乎不需要时间执行(没有返回任何行)。

接下来我应该采取什么步骤来解决这个问题呢？

Answer 1

你有一大堆像这样的地方条款：

  AND (NOT(upper(t3.curryuloc) LIKE upper('ABC%') ESCAPE '\')

看到列upper()函数CMNTSRCH.curryuloc了吗？它不能随机访问一个普通的索引。

但甲骨文拥有功能指标。如果创建这样的索引，您可能会获得更好的性能：

CREATE INDEX whatever_1 ON CMNTSRCH (UPPER(curryuloc), id);

或者，颠倒列顺序，如下所示：

CREATE INDEX whatever_2 ON CMNTSRCH (id, UPPER(curryuloc));

值得一试。

但是，必须指出的是，AND...NOT...LIKE子句的级联并不是为了效率而编写的。