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 迭代器

macos - 执行 wine != 执行 `which wine`
我有一个“有趣”的问题，即以两种不同的方式运行 wine 会导致: $> wine --version /Applications/Wine.app/Contents/Resources/bin/wi
javascript - CRONTAB 执行 Python，使用 puppeteer 执行 Node 来进行网页抓取不起作用
我制作了这个网络抓取工具来获取网页中的表格。我使用 puppeteer (不知道 crontab 有问题)、Python 进行清理并处理数据库的输出但令我惊讶的是，当我执行它时 */50 * * *
javascript - 对 javascript 函数的 Objective-C 调用何时被调用/执行，何时不被调用/执行？
JavaScript 是否被调用或执行取决于什么？准确地说，我有两个函数，它们都以相同的方式调用: [self.mapView stringByEvaluatingJavaScriptFromStri
python - 为什么使用 statsmodels 执行 OLS 和使用 scikit 执行 PooledOLS 时会得到相同的结果？
我目前正在使用 python 做一个机器学习项目(这里是初学者，从头开始学习一切)。只是想知道 statsmodels 的 OLS 和 scikit 的 PooledOlS 使用我拥有的相同面板数据
c# - 通过 Enterprise Guide 执行 SAS 和从 .Net 执行 IOM 之间的区别
在使用集成对象模型 (IOM) 后，我可以执行 SAS 代码并将 SAS 数据集读入 .Net/C# 数据集 here . 只是好奇，使用 .Net 作为 SAS 服务器的客户端与使用 Enterpr
javascript - jQuery 不会使用 animate : top 200px function. 执行，但它会使用 animate: height 执行
有一些直接的 jQuery 在单击时隐藏打开的 div 未显示，但仍将高度添加到导航中以使其看起来好像要掉下来了。这个脚本工作正常: $(document).ready(funct
java - 为什么我的代码使用 'IF' 执行 'ELSE' 和 '==' ，但不使用 '.equals' 执行？
这个问题已经有答案了: How do I compare strings in Java? (23 个回答) 已关闭 4 年前。这里是 Java 新手，我正在使用 NetBeans 尝试一些简单的代
python - Keras 2.0.8 仅使用 Python 3.x 执行 1 个 epoch，使用 2.x 执行 10 个
如果我将它切换到 Python 2.x，它执行 10。这是为什么？训练逻辑回归模型 import keras.backend as
JavaScript 执行
我有两个脚本，它们包含在 HTML 正文中。在第一个脚本中，我初始化一个 JS 对象，该对象在第二个脚本标记中引用。 ... obj.a = 1000; obj.
执行@number时的Java链接列表错误消息
每当我运行该方法时，我都会收到一个带有数字的错误以下是我的代码。 public String getAccount() { String s = "Listing the accounts";
java - 执行 while 循环以显示菜单
我已经用 do~while(true) 创建了我的菜单；但是每次用户输入一个数字时，它不会运行程序，而是再次显示菜单!你怎么看？ //我的主要方法 public static void main(St
ipython - 执行/命令完成时通知
执行命令后，如何让IPython通知我？我可以使用铃声/警报还是通过弹出窗口获取它？我正在OS X 10.8.5的iTerm上运行Anaconda。最佳答案使用最新版本的iTerm，您可以在she
java - Swing 执行
您好，我刚刚使用菜单栏为 Swing 编写了代码。但是问题出现在运行中。我输入: javac Menu.java java Menu 它没有给出任何错误，但 GUI 没有显示。这是我的源代码以供引用:
.net - 执行.NET应用程序时验证Authenticode签名
我觉得这里缺少明显的东西，但是我看不到它写在任何地方。我使用Authenticode证书对可执行文件进行签名，但是当我开始学习有关它的更多信息时，我对原样的值(value)提出了质疑。签名的exe
按钮单击事件上的 JavaScript 执行
我正在设计一个应用程序，它使用 DataTables 中的预定义库来创建数据表。我想对数据表执行删除操作，为此应在按钮单击事件上执行 java 脚本。 $(document).ready(functi
Haskell - 执行 while 循环
我是 Haskell 新手，如果有人愿意帮助我，我会很高兴!我试图让这个程序与 do while 循环一起工作。第二个 getLine 命令的结果被放入变量 goGlenn 中，如果 goGlenn
java - 执行 while 循环时出现问题
我有一个用 swing 实现迷你游戏的程序，在主类中我有一个循环，用于监听游戏 map 中的 boolean 值。使用 while 实现的循环不会执行一条指令，如果它是唯一的一条指令，我不知道为什么。
java - 执行.jar时将OJBDC添加到类路径
我正在尝试开发一个连接到 Oracle 数据库并执行函数的 Java 应用程序。如果我在 Eclipse 中运行该应用程序，它可以工作，但是当我尝试在 Windows 命令提示符中运行 .jar 时，
java future 执行
我正在阅读有关 Java 中的 Future 和 javascript 中的 Promises 的内容。下面是我作为示例编写的代码。我的问题是分配给 future 的任务什么时候开始执行？当如下行创
java - 执行 && 最有效的方法？
我有一个常见的情况，您有两个变量(xSpeed 和 ySpeed)，当它们低于 minSpeed 时，我想将它们独立设置为零，并在它们都为零时退出。最有效的方法是什么？目前我有两种方法(方法2更干净

行者123

个人简介

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

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

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