sql - 查询语句在 oracle 上执行时间很长-6ren

sql - 查询语句在 oracle 上执行时间很长

转载作者：行者123 更新时间：2023-12-04 20:09:36

我的任务是改进我公司的现有代码/查询，

数据库版本

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
"CORE   10.2.0.4.0  Production"
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production

这就是问题所在——当执行下面的代码时，完成这项工作所需的时间超过 4 个小时，大约 7 到 8 个小时。

enter image description here

3小时37分钟内395行数据

  SELECT DISTINCT GROUP_DIST_NUMBER, BEGIN_DATE, PRICE_DROP_DATE
    FROM (SELECT DISTINCT
                 G.GROUP_DIST_NUMBER,
                 TO_DATE (:B2, 'DD-MON-YYYY') BEGIN_DATE,
                 TO_DATE (:B2, 'DD-MON-YYYY') PRICE_DROP_DATE
            FROM POS_DISTI_GROUP G,
                 POS_CUST_XREF M,
                 S_CPT_SEQ_NO C,
                 PP_STD_PRICE P,
                 S_CPT_AUDIT A,
                 RPT_PRODUCT_VALUE_LEVEL L
           WHERE     G.END_DATE > TO_DATE (:B2, 'DD-MON-YYYY')
                 AND G.GROUP_DIST_NUMBER = M.DIST_NUMBER
                 AND M.SG_BILL_TO_CUST_NO = A.BILL_TO_CUST_NO
                 AND A.START_DATE <= TO_DATE (:B2, 'DD-MON-YYYY')
                 AND A.END_DATE >= TO_DATE (:B2, 'DD-MON-YYYY')
                 AND L.PROD_VALUE = P.PROD_VALUE
                 AND L.PROD_LEVEL = P.PROD_LEVEL
                 AND C.CPT_PRICE_CODE IN
                        (SELECT /*+ PRECOMPUTE_SUBQUERY */
                                DISTINCT C1.CPT_PRICE_CODE
                           FROM PP_STD_PRICE P1,
                                S_CPT_PRICE_CODE C1,
                                S_CPT_SEQ_NO S1
                          WHERE     P1.STDP_ID = :B1
                                AND C1.CPT_PRICE_CAT LIKE 'NB%'
                                AND C1.CPT_PRICE_CODE = S1.CPT_PRICE_CODE
                                AND S1.PRICE_PROTECTABLE = 'Y')
                 AND C.CPT_PRICE_CODE = P.CUST_PRICE_TYPE
                 AND P.STDP_ID = :B1
                 AND A.CUST_PRICE_TYPE = C.CPT_BILL_CODE
                 AND M.ACTIVE_IND != 'N'
                 AND (M.CATEGORY_TYPE LIKE 'DIRECT%' OR M.INDIRECT_DISTI = 'Y')
                 AND TRUNC (M.ARCHIVE_DATE) > TRUNC (SYSDATE)
          UNION
            SELECT G.GROUP_DIST_NUMBER,
                   P.BEGIN_DATE,
                   MIN (INVT.PRICE_DROP_DATE) PRICE_DROP_DATE
              FROM POS_DISTI_GROUP G,
                   POS_CUST_XREF M,
                   PP_DEBIT_AUTHORIZATION P,
                   RPT_PRODUCT_VALUE_LEVEL L,
                   POS_PP_INVENTORY INVT
             WHERE     G.END_DATE > TO_DATE (:B2, 'DD-MON-YYYY')
                   AND G.GROUP_DIST_NUMBER = M.DIST_NUMBER
                   AND M.ACTIVE_IND != 'N'
                   AND (M.CATEGORY_TYPE LIKE 'DIRECT%' OR M.INDIRECT_DISTI = 'Y')
                   AND G.DIST_NUMBER = P.DIST_NUMBER
                   AND L.PROD_VALUE = P.PROD_VALUE
                   AND L.PROD_LEVEL = P.PROD_LEVEL
                   AND P.BEGIN_DATE >= TO_DATE (:B2, 'DD-MON-YYYY') - 6
                   AND P.BEGIN_DATE <= TO_DATE (:B2, 'DD-MON-YYYY')
                   AND INVT.DIST_NUMBER = G.GROUP_DIST_NUMBER
                   AND INVT.STMODEL = L.MOD_DESC
                   AND INVT.PPCF_SHOW_DATE = P.BEGIN_DATE
                   AND P.PRICE_TYPE = 'I'
                   AND (   P.POS_PROCESSED_FLAG IS NULL
                        OR P.POS_PROCESSED_FLAG != 'C')
                   AND P.POS_PP_FLAG = 'Y'
                   AND TRUNC (M.ARCHIVE_DATE) > TRUNC (SYSDATE)
          GROUP BY G.GROUP_DIST_NUMBER, P.BEGIN_DATE)
ORDER BY GROUP_DIST_NUMBER;

我不知道如何调整此查询语句以提高性能并使其执行得更快

这里是解释计划

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                             |   101 |  2525 |       | 24156  (10)|       |       |
|   1 |  SORT ORDER BY                            |                             |   101 |  2525 |       | 24156  (10)|       |       |
|   2 |   VIEW                                    |                             |   101 |  2525 |       | 24155  (10)|       |       |
|   3 |    SORT UNIQUE                            |                             |   101 | 17691 |       | 24155  (75)|       |       |
|   4 |     UNION-ALL                             |                             |       |       |       |            |       |       |
|*  5 |      HASH JOIN                            |                             |    10M|  1680M|       |  6446   (5)|       |       |
|*  6 |       TABLE ACCESS FULL                   | S_CPT_SEQ_NO                |   651 |  5208 |       |     5   (0)|       |       |
|*  7 |       HASH JOIN                           |                             |  2383K|   379M|       |  6318   (3)|       |       |
|*  8 |        TABLE ACCESS FULL                  | POS_DISTI_GROUP             |   100 |  1800 |       |     5   (0)|       |       |
|*  9 |        HASH JOIN                          |                             |  2396K|   340M|  4320K|  6283   (3)|       |       |
|  10 |         VIEW                              | RPT_PRODUCT_VALUE_LEVEL     |   138K|  2697K|       |  1905   (3)|       |       |
|  11 |          UNION-ALL                        |                             |       |       |       |            |       |       |
|* 12 |           HASH JOIN RIGHT OUTER           |                             | 13965 |   627K|       |    91   (5)|       |       |
|  13 |            INDEX FULL SCAN                | PK_SEAEGO_PRODUCT_HIERARCHY |   298 |  4172 |       |     1   (0)|       |       |
|* 14 |            HASH JOIN RIGHT OUTER          |                             | 13965 |   436K|       |    89   (4)|       |       |
|  15 |             INDEX FULL SCAN               | PK_S_CAP_GROUP              |     2 |     8 |       |     1   (0)|       |       |
|  16 |             TABLE ACCESS FULL             | SMA_STMODEL                 | 13965 |   381K|       |    87   (3)|       |       |
|* 17 |           HASH JOIN RIGHT OUTER           |                             | 14175 |  1065K|       |   158   (5)|       |       |
|  18 |            INDEX FAST FULL SCAN           | PK_S_FAMILY                 |  1366 |  5464 |       |     2   (0)|       |       |
|* 19 |            HASH JOIN RIGHT OUTER          |                             | 14175 |  1010K|       |   156   (5)|       |       |
|  20 |             INDEX FULL SCAN               | PK_F_MODPRODMGR             |    22 |    88 |       |     1   (0)|       |       |
|* 21 |             HASH JOIN                     |                             | 14175 |   955K|       |   154   (4)|       |       |
|  22 |              TABLE ACCESS FULL            | SMA_PRODUCTMODEL            | 14132 |   317K|       |    62   (2)|       |       |
|* 23 |              HASH JOIN RIGHT OUTER        |                             | 13965 |   627K|       |    91   (5)|       |       |
|  24 |               INDEX FULL SCAN             | PK_SEAEGO_PRODUCT_HIERARCHY |   298 |  4172 |       |     1   (0)|       |       |
|* 25 |               HASH JOIN RIGHT OUTER       |                             | 13965 |   436K|       |    89   (4)|       |       |
|  26 |                INDEX FULL SCAN            | PK_S_CAP_GROUP              |     2 |     8 |       |     1   (0)|       |       |
|  27 |                TABLE ACCESS FULL          | SMA_STMODEL                 | 13965 |   381K|       |    87   (3)|       |       |
|  28 |           MAT_VIEW ACCESS FULL            | RPT_PROD_MV                 |   109K|  1288K|       |  1656   (3)|       |       |
|* 29 |         HASH JOIN                         |                             |   141K|    17M|       |  3191   (3)|       |       |
|* 30 |          INDEX RANGE SCAN                 | UK_PP_STD_PRICE_STDP_ID     |  4128 |   108K|       |    23   (0)|       |       |
|* 31 |          HASH JOIN                        |                             |  5341 |   532K|       |  3165   (3)|       |       |
|* 32 |           TABLE ACCESS FULL               | POS_CUST_XREF               |    54 |  2268 |       |    25   (4)|       |       |
|* 33 |           HASH JOIN                       |                             |   193K|    11M|       |  3137   (3)|       |       |
|* 34 |            TABLE ACCESS FULL              | S_CPT_AUDIT                 |    68 |  2108 |       |    76   (4)|       |       |
|* 35 |            HASH JOIN                      |                             |   745K|    20M|       |  3052   (2)|       |       |
|  36 |             TABLE ACCESS FULL             | S_CPT_SEQ_NO                |  1301 | 16913 |       |     5   (0)|       |       |
|  37 |             MERGE JOIN CARTESIAN          |                             | 88205 |  1378K|       |  3037   (2)|       |       |
|* 38 |              INDEX RANGE SCAN             | UK_PP_STD_PRICE_STDP_ID     |  4128 | 20640 |       |    23   (0)|       |       |
|  39 |              BUFFER SORT                  |                             |    21 |   231 |       |  3014   (2)|       |       |
|* 40 |               TABLE ACCESS FULL           | S_CPT_PRICE_CODE            |    21 |   231 |       |     1   (0)|       |       |
|  41 |      HASH GROUP BY                        |                             |     1 |   191 |       | 16421   (5)|       |       |
|* 42 |       FILTER                              |                             |       |       |       |            |       |       |
|  43 |        NESTED LOOPS                       |                             |     1 |   191 |       | 16419   (5)|       |       |
|* 44 |         HASH JOIN                         |                             |     7 |  1176 |       | 16370   (5)|       |       |
|* 45 |          HASH JOIN                        |                             |    74 |  8584 |       |  4790   (3)|       |       |
|* 46 |           HASH JOIN                       |                             |    60 |  3780 |       |    31   (7)|       |       |
|* 47 |            TABLE ACCESS FULL              | POS_CUST_XREF               |    60 |  2100 |       |    25   (4)|       |       |
|* 48 |            TABLE ACCESS FULL              | POS_DISTI_GROUP             |   100 |  2800 |       |     5   (0)|       |       |
|* 49 |           TABLE ACCESS FULL               | PP_DEBIT_AUTHORIZATION      |   345 | 18285 |       |  4759   (3)|       |       |
|  50 |          PARTITION RANGE ALL              |                             | 18192 |   923K|       | 11579   (6)|     1 |    33 |
|* 51 |           INDEX FAST FULL SCAN            | POS_PP_INVENTORY_PK         | 18192 |   923K|       | 11579   (6)|     1 |    33 |
|* 52 |         VIEW                              | RPT_PRODUCT_VALUE_LEVEL     |     1 |    23 |       |     7   (0)|       |       |
|  53 |          UNION ALL PUSHED PREDICATE       |                             |       |       |       |            |       |       |
|* 54 |           FILTER                          |                             |       |       |       |            |       |       |
|  55 |            NESTED LOOPS OUTER             |                             |     1 |    46 |       |     2   (0)|       |       |
|  56 |             NESTED LOOPS OUTER            |                             |     1 |    42 |       |     2   (0)|       |       |
|  57 |              TABLE ACCESS BY INDEX ROWID  | SMA_STMODEL                 |     1 |    28 |       |     2   (0)|       |       |
|* 58 |               INDEX UNIQUE SCAN           | PK_SMA_STMODEL              |     1 |       |       |     1   (0)|       |       |
|* 59 |              INDEX UNIQUE SCAN            | PK_SEAEGO_PRODUCT_HIERARCHY |   298 |  4172 |       |     0   (0)|       |       |
|* 60 |             INDEX UNIQUE SCAN             | PK_S_CAP_GROUP              |     2 |     8 |       |     0   (0)|       |       |
|  61 |           NESTED LOOPS OUTER              |                             |     1 |    77 |       |     3   (0)|       |       |
|  62 |            NESTED LOOPS OUTER             |                             |     1 |    73 |       |     3   (0)|       |       |
|  63 |             NESTED LOOPS OUTER            |                             |     1 |    69 |       |     3   (0)|       |       |
|  64 |              NESTED LOOPS OUTER           |                             |     1 |    65 |       |     3   (0)|       |       |
|  65 |               NESTED LOOPS                |                             |     1 |    51 |       |     3   (0)|       |       |
|* 66 |                TABLE ACCESS BY INDEX ROWID| SMA_PRODUCTMODEL            |     1 |    23 |       |     2   (0)|       |       |
|* 67 |                 INDEX UNIQUE SCAN         | PK_SMA_PRODUCTMODEL         |     1 |       |       |     1   (0)|       |       |
|  68 |                TABLE ACCESS BY INDEX ROWID| SMA_STMODEL                 |     1 |    28 |       |     1   (0)|       |       |
|* 69 |                 INDEX UNIQUE SCAN         | PK_SMA_STMODEL              |     1 |       |       |     0   (0)|       |       |
|* 70 |               INDEX UNIQUE SCAN           | PK_SEAEGO_PRODUCT_HIERARCHY |   298 |  4172 |       |     0   (0)|       |       |
|* 71 |              INDEX UNIQUE SCAN            | PK_S_FAMILY                 |  1366 |  5464 |       |     0   (0)|       |       |
|* 72 |             INDEX UNIQUE SCAN             | PK_S_CAP_GROUP              |     2 |     8 |       |     0   (0)|       |       |
|* 73 |            INDEX UNIQUE SCAN              | PK_F_MODPRODMGR             |    22 |    88 |       |     0   (0)|       |       |
|* 74 |           MAT_VIEW ACCESS BY INDEX ROWID  | RPT_PROD_MV                 |     1 |    24 |       |     2   (0)|       |       |
|* 75 |            INDEX UNIQUE SCAN              | IDX_RPT_PROD_MV_PROD_NO     |     1 |       |       |     1   (0)|       |       |
--------------------------------------------------------------------------------------------------------------------------------------

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

   5 - access("C1"."CPT_PRICE_CODE"="S1"."CPT_PRICE_CODE")
   6 - filter("S1"."PRICE_PROTECTABLE"='Y')
   7 - access("G"."GROUP_DIST_NUMBER"="M"."DIST_NUMBER")
   8 - filter("G"."END_DATE">TO_DATE(:B2,'DD-MON-YYYY'))
   9 - access("L"."PROD_VALUE"="P"."PROD_VALUE" AND "L"."PROD_LEVEL"="P"."PROD_LEVEL")
  12 - access("ST"."MARKETING_NAME"="PH"."MARKETING_NAME"(+))
  14 - access("ST"."MOD_CAPACITY_FORMATTED"="SCG"."MOD_CAPACITY_FORMATTED"(+))
  17 - access("SF"."FAMILY"(+)=SUBSTRB("PM"."MODEL",1,3))
  19 - access("PM"."DESIGN_APPLICATION"="DA"."DESIGN_APPLICATION"(+))
  21 - access("PM"."MOD_DESC"="ST"."MOD_DESC")
  23 - access("ST"."MARKETING_NAME"="PH"."MARKETING_NAME"(+))
  25 - access("ST"."MOD_CAPACITY_FORMATTED"="SCG"."MOD_CAPACITY_FORMATTED"(+))
  29 - access("C"."CPT_PRICE_CODE"="P"."CUST_PRICE_TYPE")
  30 - access("P"."STDP_ID"=TO_NUMBER(:B1))
  31 - access("M"."SG_BILL_TO_CUST_NO"="A"."BILL_TO_CUST_NO")
  32 - filter("M"."SG_BILL_TO_CUST_NO" IS NOT NULL AND ("M"."INDIRECT_DISTI"='Y' OR "M"."CATEGORY_TYPE" LIKE 'DIRECT%') AND 
              "M"."ACTIVE_IND"<>'N' AND TRUNC(INTERNAL_FUNCTION("M"."ARCHIVE_DATE"))>TRUNC(SYSDATE@!))
  33 - access("A"."CUST_PRICE_TYPE"="C"."CPT_BILL_CODE")
  34 - filter("A"."START_DATE"<=TO_DATE(:B2,'DD-MON-YYYY') AND "A"."END_DATE">=TO_DATE(:B2,'DD-MON-YYYY'))
  35 - access("C"."CPT_PRICE_CODE"="C1"."CPT_PRICE_CODE")
  38 - access("P1"."STDP_ID"=TO_NUMBER(:B1))
  40 - filter("C1"."CPT_PRICE_CAT" LIKE 'NB%')
  42 - filter(TO_DATE(:B2,'DD-MON-YYYY')-6<=TO_DATE(:B2,'DD-MON-YYYY'))
  44 - access("INVT"."DIST_NUMBER"="G"."GROUP_DIST_NUMBER" AND "INVT"."PPCF_SHOW_DATE"="P"."BEGIN_DATE")
  45 - access("G"."DIST_NUMBER"="P"."DIST_NUMBER")
  46 - access("G"."GROUP_DIST_NUMBER"="M"."DIST_NUMBER")
  47 - filter(("M"."INDIRECT_DISTI"='Y' OR "M"."CATEGORY_TYPE" LIKE 'DIRECT%') AND "M"."ACTIVE_IND"<>'N' AND 
              TRUNC(INTERNAL_FUNCTION("M"."ARCHIVE_DATE"))>TRUNC(SYSDATE@!))
  48 - filter("G"."END_DATE">TO_DATE(:B2,'DD-MON-YYYY'))
  49 - filter("P"."PRICE_TYPE"='I' AND "P"."POS_PP_FLAG"='Y' AND ("P"."POS_PROCESSED_FLAG"<>'C' OR "P"."POS_PROCESSED_FLAG" 
              IS NULL) AND "P"."BEGIN_DATE"<=TO_DATE(:B2,'DD-MON-YYYY') AND "P"."BEGIN_DATE">=TO_DATE(:B2,'DD-MON-YYYY')-6)
  51 - filter("INVT"."PPCF_SHOW_DATE"<=TO_DATE(:B2,'DD-MON-YYYY') AND "INVT"."PPCF_SHOW_DATE">=TO_DATE(:B2,'DD-MON-YYYY')-6)
  52 - filter("L"."PROD_LEVEL"="P"."PROD_LEVEL")
  54 - filter("P"."PROD_VALUE"="INVT"."STMODEL")
  58 - access("ST"."MOD_DESC"="P"."PROD_VALUE")
  59 - access("ST"."MARKETING_NAME"="PH"."MARKETING_NAME"(+))
  60 - access("ST"."MOD_CAPACITY_FORMATTED"="SCG"."MOD_CAPACITY_FORMATTED"(+))
  66 - filter("PM"."MOD_DESC"="INVT"."STMODEL")
  67 - access("PM"."MODEL"="P"."PROD_VALUE")
  69 - access("ST"."MOD_DESC"="INVT"."STMODEL")
  70 - access("ST"."MARKETING_NAME"="PH"."MARKETING_NAME"(+))
  71 - access("SF"."FAMILY"(+)=SUBSTRB("PM"."MODEL",1,3))
  72 - access("ST"."MOD_CAPACITY_FORMATTED"="SCG"."MOD_CAPACITY_FORMATTED"(+))
  73 - access("PM"."DESIGN_APPLICATION"="DA"."DESIGN_APPLICATION"(+))
  74 - filter("MOD_DESC"="INVT"."STMODEL")
  75 - access("PROD_NO"="P"."PROD_VALUE")

Note
-----
   - 'PLAN_TABLE' is old version

和表的行数统计

    TABLE_Name              NUM_ROWS
    -----------             ---------
    POS_DISTI_GROUP          2009 
    POS_CUST_XREF            2801
    S_CPT_SEQ_NO             1301
    PP_STD_PRICE             2658450
    S_CPT_AUDIT            27200
    PP_DEBIT_AUTHORIZATION   1199420
    POS_PP_INVENTORY     7276850
    PP_STD_PRICE             2658450
    S_CPT_PRICE_CODE     192
    S_CPT_SEQ_NO             1301
    SMA_STMODEL          13965
    RPT_PROD_MV          109980

创建表语句。 CLICK HERE

表说明。 CLICK HERE

按照@jonearles 的建议重新运行gather_plan_statistics 来检索EXPLAIN PLAN。 CLICK HERE

*来自谷歌文档的链接

最佳答案

问题

执行计划中的聚合发生得太晚了。计划 ID 4 和 5 生成 130 亿行，占执行时间的 95%。 Oracle 错误地认为行数会更小，并且应该将较早的聚合合并在一起。

计划 ID 6 到 40 代表内联 View 的前半部分，在 UNION 之前.查询的那部分有两个 DISTINCT s，但执行计划的那部分没有任何类型的聚合操作。 Oracle 错误地认为最好先加入所有内容并执行一个 SORT UNIQUE , 而不是执行多个 SORT UNIQUE或 HASH GROUP BY并结合结果。

问题复现

在没有完全导出的情况下完全重现这个问题几乎是不可能的。尽管它只是一个中等复杂的 SQL 语句，但它涉及到数千个变量。下面的代码仅演示了 Oracle 如何错误地合并聚合操作。

首先，创建两个简单的表。每个都有 100K 行。 TEST1 有从 1 到 100000 的数字。TEST2 包含 100000 行，但只有一个不同的数字。为了人为地制定一个糟糕的计划，在 TEST2 上收集统计数据过早。优化器认为 TEST2 只有一行，但实际上有 100000。

drop table test1 purge;
drop table test2 purge;

create table test1(a number);
create table test2(a number);

insert into test1 select level from dual connect by level <= 100000;
insert into test2 values (1);
commit;

begin
    dbms_stats.gather_table_stats(user, 'test1');
    dbms_stats.gather_table_stats(user, 'test2');
end;
/

insert into test2 select 1 from dual connect by level <= 100000;
commit;

下面的示例查询检索所有不同的 TEST1.A，其中 A 也在不同的 TEST2.A 中。

默认情况下，使用人为错误的统计信息，Oracle 先连接表，然后执行 HASH GROUP BY和 HASH UNIQUE .这是一个糟糕的计划，它加入了所有人
来自 TEST2 的 100K 值。最好执行 HASH GROUP BY首先，然后只加入该表中的 1 行。

explain plan for
select distinct a from test1 where a in (select a from test2 group by a);

select * from table(dbms_xplan.display(format => 'outline'));

------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |     1 |     8 |    79   (2)| 00:00:01 |
|   1 |  HASH UNIQUE         |       |     1 |     8 |    79   (2)| 00:00:01 |
|   2 |   HASH GROUP BY      |       |     1 |     8 |    79   (2)| 00:00:01 |
|*  3 |    HASH JOIN         |       |     1 |     8 |    79   (2)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| TEST2 |     1 |     3 |     3   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| TEST1 |   100K|   488K|    76   (2)| 00:00:01 |
------------------------------------------------------------------------------

潜在解决方案 #1:提示

不幸的是，没有官方提示可以控制何时何地进行排序和分组。通过玩弄 outline格式选项我找到了一些可能有用的提示: USE_HASH_AGGREGATION , OUTLINE_LEAF , 和 PLACE_DISTINCT . (这些提示真的很棘手 - 我在示例中使用 group by 而不是另一个 distinct 的原因是因为我在 PLACE_DISTINCT 提示上遇到了很多麻烦!)

使用这些未记录的提示可以制定更好的计划。 TEST2 的结果通过 HASH GROUP BY马上，正如他们应该的那样。这类似于如果统计准确则将生成的计划。

explain plan for
select /*+ USE_HASH_AGGREGATION(@"SEL$5DA710D3") OUTLINE_LEAF(@"SEL$683B0107") */
distinct a from test1 where a in (select a from test2 group by a);

select * from table(dbms_xplan.display(format => 'outline alias'));

----------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     1 |     8 |    79   (2)| 00:00:01 |
|   1 |  HASH UNIQUE          |          |     1 |     8 |    79   (2)| 00:00:01 |
|*  2 |   HASH JOIN SEMI      |          |     1 |     8 |    79   (2)| 00:00:01 |
|   3 |    VIEW               | VW_NSO_1 |     1 |     3 |     3   (0)| 00:00:01 |
|   4 |     HASH GROUP BY     |          |     1 |     3 |     3   (0)| 00:00:01 |
|   5 |      TABLE ACCESS FULL| TEST2    |     1 |     3 |     3   (0)| 00:00:01 |
|   6 |    TABLE ACCESS FULL  | TEST1    |   100K|   488K|    76   (2)| 00:00:01 |
----------------------------------------------------------------------------------

可能的解决方案 #2:强制执行 ROWNUM 计划。

一个更简单、更安全的版本是使用 ROWNUM . ROWNUM是一个伪列，表示返回的行的顺序。当有 ROWNUM Oracle 无法移动 distinct和 group by因为它会影响那个顺序。

不幸的是，这个技巧需要额外的代码并在计划中生成额外的步骤。这些额外的步骤大多只是传递数据，不应该减慢完成的速度。

explain plan for
select distinct a from test1 where a in
(
    --Extra level only because we only want to project one column.
    --It's syntactically required, but the optimizer throws out this inline view.
    select a
    from
    (
        --The ROWNUM forces everything in this inline view to happen separately.
        select a, rownum
        from
        (
            select a from test2 group by a
        )
    )
);

select * from table(dbms_xplan.display(format => 'outline alias'));

---------------------------------------------------------------------------------
| Id  | Operation               | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |       |     1 |     8 |    79   (2)| 00:00:01 |
|   1 |  HASH UNIQUE            |       |     1 |     8 |    79   (2)| 00:00:01 |
|*  2 |   HASH JOIN SEMI        |       |     1 |     8 |    79   (2)| 00:00:01 |
|   3 |    VIEW                 |       |     1 |     3 |     3   (0)| 00:00:01 |
|   4 |     COUNT               |       |       |       |            |          |
|   5 |      VIEW               |       |     1 |     3 |     3   (0)| 00:00:01 |
|   6 |       HASH GROUP BY     |       |     1 |     3 |     3   (0)| 00:00:01 |
|   7 |        TABLE ACCESS FULL| TEST2 |     1 |     3 |     3   (0)| 00:00:01 |
|   8 |    TABLE ACCESS FULL    | TEST1 |   100K|   488K|    76   (2)| 00:00:01 |
---------------------------------------------------------------------------------

潜在解决方案#3:修正基数估计并希望最好。

如果估计的行数准确，则计划几乎总是好的。当行估计值相差很远时，找到基数错误的执行计划的第一部分。对于此计划，它似乎是计划 ID 36。 E-Rows 和 A-Rows 相差一个数量级:

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem |  O/1/M   |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
...
|* 36 |             TABLE ACCESS FULL             | POS_CUST_XREF               |      1 |     54 |    579 |00:00:00.01 |     131 |      0 |       |       |          |

步骤 36 有一个涉及 SYSDATE 的复杂谓词。

  36 - filter(("M"."SG_BILL_TO_CUST_NO" IS NOT NULL AND ("M"."INDIRECT_DISTI"='Y' OR "M"."CATEGORY_TYPE" LIKE 'DIRECT%') AND "M"."ACTIVE_IND"<>'N' AND
              TRUNC(INTERNAL_FUNCTION("M"."ARCHIVE_DATE"))>TRUNC(SYSDATE@!)))

即使有最新的统计数据，这种情况也很难预测。动态采样可能会有所帮助。尝试使用这样的顶级提示重新运行查询:

SELECT /*+ dynamic_sampling(6) */ ...

解决这些早期的差异通常会解决计划后期的其他问题。此示例只是基数不匹配的一种可能来源。可能需要其他技巧来改进其他基数估计。这可能是一种非常困难的方法，但它可以通过多种方式获得返回。

红鲱鱼

任何中等复杂的 SQL 语句都有许多潜在的改进。评论和答案中有几个好主意。但是在调整时，始终必须关注最慢的地方，而不是最容易修复的地方。这听起来很明显，但这是一个非常容易落入的陷阱。这就是为什么我要你使用 /*+ gather_plan_statistics*/ ，这就是为什么我的回答只关注计划中实际时间较长的部分。

例如，在我之前的评论中，我建议查看 NESTED LOOPS其中 ROWS = 1。现在我们有了实际时间，我们知道建议没有帮助。 (尽管一般来说，您仍然应该对具有大表但 ROWS=1 的计划持怀疑态度。)

关于sql - 查询语句在 oracle 上执行时间很长，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/19922915/

文章推荐： knockout.js - ko.validation.configure 与 ko.validation.init 的区别

文章推荐： png - 在Windows程序中使用.png文件而不是使用.ico文件

文章推荐： api - 为分页 API 制作 Scala 迭代器

c# - 为什么 Int64.MaxValue 很长？
这个问题在这里已经有了答案: Isn't an Int64 equal to a long in C#? (2 个答案) 关闭 9 年前。它不应该是一个整数类型吗？这样，一些使用 int 的函数
c# - 为什么 Stream.Position 很长
当我遇到一些我想知道的事情时，我正忙着解析一个二进制文件。 Stream.Position属性的类型为Int64或long。为什么是这样？因为流中的位置不能为负，所以使用 UInt64 不是更有意义吗
c - 在我调用函数来扫描某些内容后，我的程序无法继续运行？有人能找出问题所在吗？ (很长，但请帮忙!)
所以第一部分是我从用户那里获得输入，在本例中，输入是“1”作为从另一个函数接收的字符值。 printf ("\nPlease enter 1, 2, 3 or q: "); option =
php - json_encode() 期望参数 2 很长，字符串给定
我正在尝试使用以下代码从 REST 服务返回 JSON: $categories = $categoriesController->listAll(); if($categories){ hea
c - 我如何 printf 很长？这不应该工作吗？ %li
我阅读了文档，它说 long 是 %li，但打印输出返回为 -2147024891。是什么赋予了？最佳答案您甚至没有提供要打印的号码，但我猜您已经无意中发现了签名打印和未签名打印之间的区别。使用
php - mysqli::query() 期望参数 2 很长，给出字符串
我正在创建自定义购物车，我正在构建一个查询，该查询从检索我刚刚保存到购物车表中的 session_id 开始。我知道这个值被保存了，我在 mysql 命令行运行这个查询，它返回我需要的但我没有将值放入
ios - 如果 TextView 很长，则启用 ScrollView 滚动
我有一个包含 textView 的 scrollView。如果文本很长并且不适合屏幕，我想增加 textView 高度(我想我可以通过添加 NSLayoutConstraint outlet 并修改它
php - PDOStatement::fetchAll() 期望参数 1 很长，给定字符串？
我有一个基本的数据库处理程序类，其中有一个使用 PDO::FETCH_ASSOC 参数返回结果集的公共(public)方法: public function resultSet() { $th
android - 调用 PublishSubject.onNext() 和接收它之间的 Rx Interval 很长
在后台线程中，我调用 PublishSubject.onNext(); 并在主线程中通过 subscribe(PublishSubject.filter(message -> message.getI
security - 为什么 Amazon Web Services 的登录页面 URL 很长
我想知道为什么 Amazon Web Services 控制台登录页面有这么长的 url？为什么不只发布数据而不显示其中包含大量数据的冗长 url。以这种方式实现有什么充分的理由吗？最佳答案我认为
php - 错误 PDOStatement::bindParam() 期望参数 3 很长，给定的字符串
这个问题在这里已经有了答案: Can I mix MySQL APIs in PHP? (4 个答案) 关闭 6 年前。希望我犯了一个快速而明显的错误，我浏览了 previous question
java - 我该怎么办 session URL 很长，我无法附加 JSESSIONID=389729387392。解决方案是什么？
我得到了答案:如果我禁用了cookie，那么使用URL重定向我可以传递JSESSIONID，但我的URL已经很长，因为我使用它有约束的GET方法。那怎么办我应该使用我的 session 吗？我希望我的
php - DOMDocument::loadHTML() 期望参数 2 很长，尝试使用 LIBXML_HTML_NOIMPLIED 参数时给出的字符串
目前，当我使用 DOMDocument 对象并调用 saveHTML() 时，它会自动添加一些我不需要的 html 标签。我尝试了此处建议的解决方案 ( https://stackoverflow.c

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

sql - 查询语句在 oracle 上执行时间很长