Oracle SQL执行计划-访问谓词确定

你好，汤姆，

我知道汤姆已经退休了。但很高兴Oracle的一组专业知识有助于保持AskTom的运作。这确实很有帮助。

我在SAP/Oracle环境中面临SQL计划问题。SQL是:

SELECT
  DISTINCT "MATNR","VKORG","VTWEG","MTPOS"
FROM
  "MVKE"
WHERE
  "MANDT"=:A0 AND (("MATNR" IN (:A1,:A2,:A3) AND "VKORG"=:A4 AND "VTWEG"=:A5) OR ("MATNR" IN
  (:A1,:A2,:A3) AND "VKORG"=:A9 AND "VTWEG"=:A10) OR ("MATNR" IN (:A1,:A2,:A3) AND "VKORG"=:A14 AND
  "VTWEG"=:A15) OR ("MATNR" IN (:A1,:A2,:A3) AND "VKORG"=:A19 AND "VTWEG"=:A20) OR ("MATNR" IN
  (:A1,:A2,:A3) AND "VKORG"=:A24 AND "VTWEG"=:A25))

MVKE有一个主索引-MVKE ~ 0，它由MANDT、MATNR、VKORG、VTWEG等表字段组成。oracle执行计划是Oracle按预期使用MVKE〜0索引范围扫描，where-子句中的所有其他字段都用作 “过滤器谓词”。但是访问谓词只有一个字段的MANDT。问题是，mandt是SAP世界中只有1个不同值的 “客户端” 字段，但mantnr字段具有成百上千个值，这是该索引中最具选择性的字段。这导致了我们的性能问题。现在，ERP Oracle团队对某些Oracle静态参数进行了更改，例如SGA大小，已解决了该问题。是什么导致了我们最初的问题？哪些Oracle静态参数对 “访问谓词” 的权重更高？我用谷歌搜索了互联网，但我的问题仍然存在。

非常感谢。

以下是使用适当的访问谓词的新计划 (使用 “mandt” 作为访问谓词的旧计划只有两个步骤)

SQL Statement
----------------------------------------------------------------------------------------------------------------------
SELECT
  DISTINCT "MATNR","VKORG","VTWEG","MTPOS"
FROM
  "MVKE"
WHERE
  "MANDT"=:A0 AND (("MATNR" IN (:A1,:A2,:A3) AND "VKORG"=:A4 AND "VTWEG"=:A5) OR ("MATNR" IN
  (:A1,:A2,:A3) AND "VKORG"=:A9 AND "VTWEG"=:A10) OR ("MATNR" IN (:A1,:A2,:A3) AND "VKORG"=:A14 AND
  "VTWEG"=:A15) OR ("MATNR" IN (:A1,:A2,:A3) AND "VKORG"=:A19 AND "VTWEG"=:A20) OR ("MATNR" IN
  (:A1,:A2,:A3) AND "VKORG"=:A24 AND "VTWEG"=:A25))


Execution Plan

----------------------------------------------------------------------------------------------------------------------
Explain from v$sql_plan not possible -> Explain from PLAN_TABLE is displayed !
No values in v$sql_plan for Address: 00000006860DF8F0 Hash_value:  1957305630 Child_number:  0 Instance_ID: 1
Sql_id:

System: F6R
Plan hash value: 1827367495

----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |     5 |   180 |     8  (13)| 00:00:01 |
|   1 |  CONCATENATION                |        |       |       |            |          |
|   2 |   INLIST ITERATOR             |        |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| MVKE   |     1 |    36 |     1   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN         | MVKE~0 |     1 |       |     1   (0)| 00:00:01 |
|   5 |   INLIST ITERATOR             |        |       |       |            |          |
|   6 |    TABLE ACCESS BY INDEX ROWID| MVKE   |     1 |    36 |     1   (0)| 00:00:01 |
|*  7 |     INDEX UNIQUE SCAN         | MVKE~0 |     1 |       |     1   (0)| 00:00:01 |
|   8 |   INLIST ITERATOR             |        |       |       |            |          |
|   9 |    TABLE ACCESS BY INDEX ROWID| MVKE   |     1 |    36 |     1   (0)| 00:00:01 |
|* 10 |     INDEX UNIQUE SCAN         | MVKE~0 |     1 |       |     1   (0)| 00:00:01 |
|  11 |   INLIST ITERATOR             |        |       |       |            |          |
|  12 |    TABLE ACCESS BY INDEX ROWID| MVKE   |     1 |    36 |     1   (0)| 00:00:01 |
|* 13 |     INDEX UNIQUE SCAN         | MVKE~0 |     1 |       |     1   (0)| 00:00:01 |
|  14 |   INLIST ITERATOR             |        |       |       |            |          |
|  15 |    TABLE ACCESS BY INDEX ROWID| MVKE   |     1 |    36 |     1   (0)| 00:00:01 |
|* 16 |     INDEX UNIQUE SCAN         | MVKE~0 |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   3 - SEL$1_1 / MVKE@SEL$1
   4 - SEL$1_1 / MVKE@SEL$1
   6 - SEL$1_2 / MVKE@SEL$1_2
   7 - SEL$1_2 / MVKE@SEL$1_2
   9 - SEL$1_3 / MVKE@SEL$1_3
  10 - SEL$1_3 / MVKE@SEL$1_3
  12 - SEL$1_4 / MVKE@SEL$1_4
  13 - SEL$1_4 / MVKE@SEL$1_4
  15 - SEL$1_5 / MVKE@SEL$1_5
  16 - SEL$1_5 / MVKE@SEL$1_5

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("MANDT"=:A0 AND ("MATNR"=:A1 OR "MATNR"=:A2 OR "MATNR"=:A3) AND
              "VKORG"=:A24 AND "VTWEG"=:A25)
   7 - access("MANDT"=:A0 AND ("MATNR"=:A1 OR "MATNR"=:A2 OR "MATNR"=:A3) AND
              "VKORG"=:A19 AND "VTWEG"=:A20)
       filter(LNNVL("VKORG"=:A24) OR LNNVL("VTWEG"=:A25) OR LNNVL("MATNR"=:A1)
              AND LNNVL("MATNR"=:A2) AND LNNVL("MATNR"=:A3))
  10 - access("MANDT"=:A0 AND ("MATNR"=:A1 OR "MATNR"=:A2 OR "MATNR"=:A3) AND
              "VKORG"=:A14 AND "VTWEG"=:A15)
       filter((LNNVL("VKORG"=:A19) OR LNNVL("VTWEG"=:A20) OR LNNVL("MATNR"=:A1)
              AND LNNVL("MATNR"=:A2) AND LNNVL("MATNR"=:A3)) AND (LNNVL("VKORG"=:A24) OR
              LNNVL("VTWEG"=:A25) OR LNNVL("MATNR"=:A1) AND LNNVL("MATNR"=:A2) AND
              LNNVL("MATNR"=:A3)))
  13 - access("MANDT"=:A0 AND ("MATNR"=:A1 OR "MATNR"=:A2 OR "MATNR"=:A3) AND
              "VKORG"=:A9 AND "VTWEG"=:A10)
       filter((LNNVL("VKORG"=:A14) OR LNNVL("VTWEG"=:A15) OR LNNVL("MATNR"=:A1)
              AND LNNVL("MATNR"=:A2) AND LNNVL("MATNR"=:A3)) AND (LNNVL("VKORG"=:A19) OR
              LNNVL("VTWEG"=:A20) OR LNNVL("MATNR"=:A1) AND LNNVL("MATNR"=:A2) AND
              LNNVL("MATNR"=:A3)) AND (LNNVL("VKORG"=:A24) OR LNNVL("VTWEG"=:A25) OR
              LNNVL("MATNR"=:A1) AND LNNVL("MATNR"=:A2) AND LNNVL("MATNR"=:A3)))
  16 - access("MANDT"=:A0 AND ("MATNR"=:A1 OR "MATNR"=:A2 OR "MATNR"=:A3) AND
              "VKORG"=:A4 AND "VTWEG"=:A5)
       filter((LNNVL("VKORG"=:A9) OR LNNVL("VTWEG"=:A10) OR LNNVL("MATNR"=:A1)
              AND LNNVL("MATNR"=:A2) AND LNNVL("MATNR"=:A3)) AND (LNNVL("VKORG"=:A14) OR
              LNNVL("VTWEG"=:A15) OR LNNVL("MATNR"=:A1) AND LNNVL("MATNR"=:A2) AND
              LNNVL("MATNR"=:A3)) AND (LNNVL("VKORG"=:A19) OR LNNVL("VTWEG"=:A20) OR
              LNNVL("MATNR"=:A1) AND LNNVL("MATNR"=:A2) AND LNNVL("MATNR"=:A3)) AND
              (LNNVL("VKORG"=:A24) OR LNNVL("VTWEG"=:A25) OR LNNVL("MATNR"=:A1) AND
              LNNVL("MATNR"=:A2) AND LNNVL("MATNR"=:A3)))

嗨，埃里克，

你能分享更多关于你的问题的信息吗？

您可以发送原始执行计划和更改的初始化参数列表吗？