logo标签列表

优化处理包含大型表的Oracle SQL性能

sqloracle12c
4
我有一个涉及4个大表和几个小表连接的 Oracle SQL 查询。 大表是 TBL_1、TBL_2、TBL_3 和 TBL4，每个表都有约 8M 条记录。其余的表是小表，少于 10K 条记录。
问题：即使没有数据要返回，查询也需要超过 3 分钟时间。
表和索引统计信息已经是最新的，这些表上没有旧的统计信息。
我尝试过使用提示，但没有效果。
请看下面我的观察结果：
查询：
    SELECT a.*, ROWNUM AS rnm
      FROM (  SELECT c.idntfr,
             pr.program_name AS "Program",
             e.case_number,
             (SELECT DECODE (s.status_name,
                     'EA', 'A',
                     'ED', 'D',
                     'EU', 'U',
                     s.status_name)
                FROM TBL_5 ms, status s
               WHERE     ms.status_type_cid = 7
                 AND mbr_sid = c.mbr_sid
                 AND ms.status_type_cid = s.status_type_cid
                 AND s.status_cid = ms.status_cid
                 AND ms.oprtnl_flag = 'A'
                 AND SYSDATE BETWEEN ms.from_date AND ms.TO_DATE),
             DECODE (
                LENGTH (TRIM (e.social_security_nmbr)),
                NULL, 'Not Available',
                (   SUBSTR (e.social_security_nmbr, 1, 3)
                 || '-'
                 || SUBSTR (e.social_security_nmbr, 4, 2)
                 || '-'
                 || SUBSTR (e.social_security_nmbr, 6, 4)))
                AS "SSN",
             e.last_name || ',' || e.first_name || ' ' || e.middle_name,
             TO_CHAR (e.injury_date, 'MM/dd/yyyy'),
             DECODE (e.gender_lkpcd,
                 'M', 'Male',
                 'F', 'Female',
                 'U', 'Unknown'),
             e.mbr_sid,
             pr.program_cid,
             e.last_name,
             e.social_security_nmbr,
             e.first_name AS
            FROM TBL_1 c,
             program pr,
             TBL_2 e,
             TBL_3 mai,
             TBL_4 uaxou
           WHERE     c.mbr_sid = e.mbr_sid
             AND c.mbr_sid = mai.mbr_sid
             AND c.oprtnl_flag = 'A'
             AND c.idntfr_type_cid = 423
             AND TRUNC (SYSDATE) BETWEEN c.from_date AND c.TO_DATE
             AND TRUNC (SYSDATE) BETWEEN e.from_date AND e.TO_DATE
             AND e.oprtnl_flag = 'A'
             AND e.status_cid = 2
             AND mai.oprtnl_flag = 'A'
             AND mai.status_cid = 2
             AND TRUNC (SYSDATE) BETWEEN mai.from_date AND mai.TO_DATE
             AND e.program_code = pr.program_code
             AND pr.oprtnl_flag = 'A'
             AND uaxou.user_acct_sid = 1
             AND uaxou.oprtnl_flag = 'A'
             AND SYSDATE BETWEEN uaxou.from_date AND uaxou.TO_DATE
             AND uaxou.org_unit_sid = mai.org_unit_sid
        ORDER BY "Program" ASC) a
     WHERE ROWNUM < 102;

以下条件没有数据。
    AND uaxou.user_acct_sid = 1

期望结果：如果没有返回数据，响应时间应该在4秒以内。

解释计划：

Plan hash value: 2272581586

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                             |   101 | 22220 |  1361   (1)| 00:00:01 |
|   1 |  NESTED LOOPS                         |                             |     1 |    58 |     7   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                        |                             |     1 |    58 |     7   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID BATCHED| TBL_5                       |     1 |    31 |     6   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                  | XIF1TBL_5                   |     8 |       |     3   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN                  | XPKSTATUS                   |     1 |       |     0   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID         | STATUS                      |     1 |    27 |     1   (0)| 00:00:01 |
|*  7 |  COUNT STOPKEY                        |                             |       |       |            |          |
|   8 |   VIEW                                |                             |   169 | 37180 |  1361   (1)| 00:00:01 |
|   9 |    NESTED LOOPS                       |                             |   169 | 36166 |   767   (0)| 00:00:01 |
|  10 |     NESTED LOOPS                      |                             | 11904 | 36166 |   767   (0)| 00:00:01 |
|  11 |      NESTED LOOPS                     |                             |    62 | 11284 |   333   (0)| 00:00:01 |
|  12 |       NESTED LOOPS                    |                             |    45 |  6660 |   108   (0)| 00:00:01 |
|  13 |        NESTED LOOPS                   |                             |    33 |  3564 |     9   (0)| 00:00:01 |
|* 14 |         TABLE ACCESS BY INDEX ROWID   | PROGRAM                     |     5 |    70 |     2   (0)| 00:00:01 |
|  15 |          INDEX FULL SCAN              | XAK1OWCP_PROGRAM            |     2 |       |     1   (0)| 00:00:01 |
|* 16 |         TABLE ACCESS FULL             | TBL_2                       |    20 |  1880 |     4   (0)| 00:00:01 |
|* 17 |        TABLE ACCESS BY INDEX ROWID    | TBL_1                       |     1 |    40 |     3   (0)| 00:00:01 |
|* 18 |         INDEX RANGE SCAN              | TUNE_WS_19NOV10_X2          |     1 |       |     2   (0)| 00:00:01 |
|* 19 |       TABLE ACCESS BY INDEX ROWID     | TBL_3                       |     1 |    34 |     5   (0)| 00:00:01 |
|* 20 |        INDEX RANGE SCAN               | XIE2_TBL_3                  |     3 |       |     2   (0)| 00:00:01 |
|* 21 |      INDEX RANGE SCAN                 | XIF3TBL_4                   |   192 |       |     1   (0)| 00:00:01 |
|* 22 |     TABLE ACCESS BY INDEX ROWID       | TBL_4                       |     3 |    96 |     7   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------

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

   3 - filter("MS"."STATUS_TYPE_CID"=7 AND "MS"."OPRTNL_FLAG"='A' AND "MS"."TO_DATE">=SYSDATE@! AND 
          "MS"."FROM_DATE"<=SYSDATE@!)
   4 - access("MBR_SID"=:B1)
   5 - access("S"."STATUS_TYPE_CID"=7 AND "S"."STATUS_CID"="MS"."STATUS_CID")
   7 - filter(ROWNUM<102)
  14 - filter("PR"."OPRTNL_FLAG"='A')
  16 - filter("E"."PROGRAM_CODE"="PR"."PROGRAM_CODE" AND "E"."OPRTNL_FLAG"='A' AND "E"."STATUS_CID"=2 AND 
          "E"."FROM_DATE"<=TRUNC(SYSDATE@!) AND TRUNC(INTERNAL_FUNCTION("FROM_DATE"))<=TRUNC(TRUNC(SYSDATE@!)) AND 
          "E"."TO_DATE">=TRUNC(SYSDATE@!) AND TRUNC(INTERNAL_FUNCTION("TO_DATE"))>=TRUNC(TRUNC(SYSDATE@!)))
  17 - filter("C"."FROM_DATE"<=TRUNC(SYSDATE@!) AND "C"."TO_DATE">=TRUNC(SYSDATE@!))
  18 - access("C"."MBR_SID"="E"."MBR_SID" AND "C"."IDNTFR_TYPE_CID"=423 AND "C"."OPRTNL_FLAG"='A')
  19 - filter("MAI"."OPRTNL_FLAG"='A' AND "MAI"."STATUS_CID"=2 AND "MAI"."FROM_DATE"<=TRUNC(SYSDATE@!) AND 
          "MAI"."TO_DATE">=TRUNC(SYSDATE@!))
  20 - access("C"."MBR_SID"="MAI"."MBR_SID")
  21 - access("UAXOU"."USER_ACCT_SID"=1)
  22 - filter("UAXOU"."ORG_UNIT_SID"="MAI"."ORG_UNIT_SID" AND "UAXOU"."OPRTNL_FLAG"='A' AND 
          "UAXOU"."FROM_DATE"<=SYSDATE@! AND "UAXOU"."TO_DATE">=SYSDATE@!)

这是从v$parameter查询的输出结果。

    NAME                                |   VALUE
    compatible                          |   12.2.0
    optimizer_adaptive_plans            |   TRUE
    optimizer_adaptive_reporting_only   |   FALSE
    optimizer_features_enable           |   12.2.0.1

这是添加GATHER_PLAN_STATISTICS后的解释计划，显示实际基数值：
    Plan hash value: 2272581586

    -------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                             | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    -------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                      |                             |      1 |        |      0 |00:00:00.01 |       0 |
    |   1 |  NESTED LOOPS                         |                             |      0 |      1 |      0 |00:00:00.01 |       0 |
    |   2 |   NESTED LOOPS                        |                             |      0 |      1 |      0 |00:00:00.01 |       0 |
    |*  3 |    TABLE ACCESS BY INDEX ROWID BATCHED| TBL_5                       |      0 |      1 |      0 |00:00:00.01 |       0 |
    |*  4 |     INDEX RANGE SCAN                  | XIF1TBL_5                   |      0 |      8 |      0 |00:00:00.01 |       0 |
    |*  5 |    INDEX UNIQUE SCAN                  | XPKSTATUS                   |      0 |      1 |      0 |00:00:00.01 |       0 |
    |   6 |   TABLE ACCESS BY INDEX ROWID         | STATUS                      |      0 |      1 |      0 |00:00:00.01 |       0 |
    |*  7 |  COUNT STOPKEY                        |                             |      1 |        |      0 |00:00:00.01 |       0 |
    |   8 |   VIEW                                |                             |      1 |    169 |      0 |00:00:00.01 |       0 |
    |   9 |    NESTED LOOPS                       |                             |      1 |    169 |      0 |00:00:00.01 |       0 |
    |  10 |     NESTED LOOPS                      |                             |      1 |  11904 |      0 |00:00:00.01 |       0 |
    |  11 |      NESTED LOOPS                     |                             |      1 |     62 |      0 |00:00:00.01 |       0 |
    |  12 |       NESTED LOOPS                    |                             |      1 |     45 |      0 |00:00:00.01 |       0 |
    |  13 |        NESTED LOOPS                   |                             |      1 |     33 |      0 |00:00:00.01 |       0 |
    |* 14 |         TABLE ACCESS BY INDEX ROWID   | PROGRAM                     |      1 |      5 |      1 |00:00:00.01 |       2 |
    |  15 |          INDEX FULL SCAN              | XAK1OWCP_PROGRAM            |      1 |      2 |      2 |00:00:00.01 |       1 |
    |* 16 |         TABLE ACCESS FULL             | TBL_2                       |      1 |     20 |      0 |00:00:00.01 |       0 |
    |* 17 |        TABLE ACCESS BY INDEX ROWID    | TBL_1                   |      0 |      1 |      0 |00:00:00.01 |       0 |
    |* 18 |         INDEX RANGE SCAN              | TUNE_WS_19NOV10_X2          |      0 |      1 |      0 |00:00:00.01 |       0 |
    |* 19 |       TABLE ACCESS BY INDEX ROWID     | TBL_3                       |      0 |      1 |      0 |00:00:00.01 |       0 |
    |* 20 |        INDEX RANGE SCAN               | XIE2_TBL_3                  |      0 |      3 |      0 |00:00:00.01 |       0 |
    |* 21 |      INDEX RANGE SCAN                 | XIF3TBL_4           |      0 |    192 |      0 |00:00:00.01 |       0 |
    |* 22 |     TABLE ACCESS BY INDEX ROWID       | TBL_4                   |      0 |      3 |      0 |00:00:00.01 |       0 |
    -------------------------------------------------------------------------------------------------------------------------------

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

       3 - filter(("MS"."STATUS_TYPE_CID"=7 AND "MS"."OPRTNL_FLAG"='A' AND "MS"."TO_DATE">=SYSDATE@! AND 
              "MS"."FROM_DATE"<=SYSDATE@!))
       4 - access("MBR_SID"=:B1)
       5 - access("S"."STATUS_TYPE_CID"=7 AND "S"."STATUS_CID"="MS"."STATUS_CID")
       7 - filter(ROWNUM<102)
      14 - filter("PR"."OPRTNL_FLAG"='A')
      16 - filter(("E"."PROGRAM_CODE"="PR"."PROGRAM_CODE" AND "E"."OPRTNL_FLAG"='A' AND "E"."STATUS_CID"=2 AND 
              "E"."FROM_DATE"<=TRUNC(SYSDATE@!) AND "E"."TO_DATE">=TRUNC(SYSDATE@!)))
      17 - filter(("C"."FROM_DATE"<=TRUNC(SYSDATE@!) AND "C"."TO_DATE">=TRUNC(SYSDATE@!)))
      18 - access("C"."MBR_SID"="E"."MBR_SID" AND "C"."IDNTFR_TYPE_CID"=423 AND "C"."OPRTNL_FLAG"='A')
      19 - filter(("MAI"."OPRTNL_FLAG"='A' AND "MAI"."STATUS_CID"=2 AND "MAI"."FROM_DATE"<=TRUNC(SYSDATE@!) AND 
              "MAI"."TO_DATE">=TRUNC(SYSDATE@!)))
      20 - access("C"."MBR_SID"="MAI"."MBR_SID")
      21 - access("UAXOU"."USER_ACCT_SID"=1)
      22 - filter(("UAXOU"."ORG_UNIT_SID"="MAI"."ORG_UNIT_SID" AND "UAXOU"."OPRTNL_FLAG"='A' AND 
              "UAXOU"."FROM_DATE"<=SYSDATE@! AND "UAXOU"."TO_DATE">=SYSDATE@!))

我已经尝试了各种提示 USE_HASH(c e) 和其他各种组合，但都没起作用。

有一个有趣的观察结果是，如果我注释掉条件：

    --AND uaxou.user_acct_sid = 1

结果在7秒钟内返回。(显然，在这种情况下返回数据)。 那么，当没有数据返回时，是什么导致查询花费了那么长的时间？(即，这个条件没有被注释掉 AND uaxou.user_acct_sid = 1) 我让慢查询完成，它花费了10分46秒。没有数据返回。 以下是解释计划。我不知道为什么A-Time与实际执行时间不匹配。
    Plan hash value: 2272581586

    -------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                             | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    -------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                      |                             |      1 |        |      0 |00:00:00.01 |       0 |
    |   1 |  NESTED LOOPS                         |                             |      0 |      1 |      0 |00:00:00.01 |       0 |
    |   2 |   NESTED LOOPS                        |                             |      0 |      1 |      0 |00:00:00.01 |       0 |
    |*  3 |    TABLE ACCESS BY INDEX ROWID BATCHED| TBL_5                       |      0 |      1 |      0 |00:00:00.01 |       0 |
    |*  4 |     INDEX RANGE SCAN                  | XIF1TBL_5                   |      0 |      8 |      0 |00:00:00.01 |       0 |
    |*  5 |    INDEX UNIQUE SCAN                  | XPKSTATUS                   |      0 |      1 |      0 |00:00:00.01 |       0 |
    |   6 |   TABLE ACCESS BY INDEX ROWID         | STATUS                      |      0 |      1 |      0 |00:00:00.01 |       0 |
    |*  7 |  COUNT STOPKEY                        |                             |      1 |        |      0 |00:00:00.01 |       0 |
    |   8 |   VIEW                                |                             |      1 |    169 |      0 |00:00:00.01 |       0 |
    |   9 |    NESTED LOOPS                       |                             |      1 |    169 |      0 |00:00:00.01 |       0 |
    |  10 |     NESTED LOOPS                      |                             |      1 |  11904 |      0 |00:00:00.01 |       0 |
    |  11 |      NESTED LOOPS                     |                             |      1 |     62 |      0 |00:00:00.01 |       0 |
    |  12 |       NESTED LOOPS                    |                             |      1 |     45 |      0 |00:00:00.01 |       0 |
    |  13 |        NESTED LOOPS                   |                             |      1 |     33 |      0 |00:00:00.01 |       0 |
    |* 14 |         TABLE ACCESS BY INDEX ROWID   | PROGRAM                     |      1 |      5 |      1 |00:00:00.01 |       2 |
    |  15 |          INDEX FULL SCAN              | XAK1OWCP_PROGRAM            |      1 |      2 |      2 |00:00:00.01 |       1 |
    |* 16 |         TABLE ACCESS FULL             | TBL_2                       |      1 |     20 |      0 |00:00:00.01 |       0 |
    |* 17 |        TABLE ACCESS BY INDEX ROWID    | TBL_1                       |      0 |      1 |      0 |00:00:00.01 |       0 |
    |* 18 |         INDEX RANGE SCAN              | TUNE_WS_19NOV10_X2          |      0 |      1 |      0 |00:00:00.01 |       0 |
    |* 19 |       TABLE ACCESS BY INDEX ROWID     | TBL_3                       |      0 |      1 |      0 |00:00:00.01 |       0 |
    |* 20 |        INDEX RANGE SCAN               | XIE2_TBL_3                  |      0 |      3 |      0 |00:00:00.01 |       0 |
    |* 21 |      INDEX RANGE SCAN                 | XIF3TBL_4                   |      0 |    192 |      0 |00:00:00.01 |       0 |
    |* 22 |     TABLE ACCESS BY INDEX ROWID       | TBL_4                       |      0 |      3 |      0 |00:00:00.01 |       0 |
    -------------------------------------------------------------------------------------------------------------------------------

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

       3 - filter(("MS"."STATUS_TYPE_CID"=7 AND "MS"."OPRTNL_FLAG"='A' AND "MS"."TO_DATE">=SYSDATE@! AND 
              "MS"."FROM_DATE"<=SYSDATE@!))
       4 - access("MBR_SID"=:B1)
       5 - access("S"."STATUS_TYPE_CID"=7 AND "S"."STATUS_CID"="MS"."STATUS_CID")
       7 - filter(ROWNUM<102)
      14 - filter("PR"."OPRTNL_FLAG"='A')
      16 - filter(("E"."PROGRAM_CODE"="PR"."PROGRAM_CODE" AND "E"."OPRTNL_FLAG"='A' AND "E"."STATUS_CID"=2 AND 
              "E"."FROM_DATE"<=TRUNC(SYSDATE@!) AND "E"."TO_DATE">=TRUNC(SYSDATE@!)))
      17 - filter(("C"."FROM_DATE"<=TRUNC(SYSDATE@!) AND "C"."TO_DATE">=TRUNC(SYSDATE@!)))
      18 - access("C"."MBR_SID"="E"."MBR_SID" AND "C"."IDNTFR_TYPE_CID"=423 AND "C"."OPRTNL_FLAG"='A')
      19 - filter(("MAI"."OPRTNL_FLAG"='A' AND "MAI"."STATUS_CID"=2 AND "MAI"."FROM_DATE"<=TRUNC(SYSDATE@!) AND 
              "MAI"."TO_DATE">=TRUNC(SYSDATE@!)))
      20 - access("C"."MBR_SID"="MAI"."MBR_SID")
      21 - access("UAXOU"."USER_ACCT_SID"=1)
      22 - filter(("UAXOU"."ORG_UNIT_SID"="MAI"."ORG_UNIT_SID" AND "UAXOU"."OPRTNL_FLAG"='A' AND 
              "UAXOU"."FROM_DATE"<=SYSDATE@! AND "UAXOU"."TO_DATE">=SYSDATE@!))
这个查询给我的印象是结构和呈现都很差。能否重构一下这个查询？将相关子查询转换为连接是一个好的开始，使用现代连接语法强调用于连接和过滤的条件也是如此。我还发现每个表都在单独过滤 oprtnl_flag 等内容，而不是在该列上进行连接。我想知道（不知道查询的预期功能和数据性质）日期是否也被过滤为一个范围，而不是根据具体匹配日期进行连接？ - Steve
可能没有任何单一措施的性能提升，但它将为可能的性能改进和简化查询的视觉结构（通过将所有连接移动到查询的连接子句区域）提供基础。当您说“所有数据都是带有标志A的”时，您是否意味着数据库中不存在其他标志？我强烈建议您为查询引入更多结构-转换为ANSI-92连接是一个很好的开始。针对这些表中的任何一个进行简单查询（使用给定的过滤器）需要多长时间？ - Steve
@Steve，目前所有数据都存在于OPRTNL_FLAG ='A'中，因为我们刚从传统系统转换了数据。一旦系统投入运营，它也会有其他标志，但大多数记录将是'A'。我已经尝试隔离表并使用过滤条件运行，结果在2秒内完成。当我将所有4个大表连接在一起时，它会影响性能。 - AJ.
我明白了。这里似乎没有明显的简单解决方案。这类问题的解决方法往往是试错法。从可以过滤到最小的相关行的表开始，然后以允许最小化过滤并保持性能可接受的方式加入下一个表，依此类推——按照您期望执行的顺序精确构建查询，并解决每个步骤中出现的任何低效率问题（例如调整索引、调整数据库结构、材料化视图、构建连接或以替代顺序进行过滤等）。 - Steve
感谢Steve和RBarryYoung的所有帮助。统计数据是一周前收集的，DBA_TAB_STATISTICS中没有过期的统计数据，但由于这并没有起到帮助作用，我联系了DBA，他重新收集了底层表和索引的统计数据。现在查询返回结果少于一秒钟。问题：如果DBA_TAB_STATISCIS.STALE_STATS='NO'，如何确定是否需要再次收集表和索引的统计数据？ - AJ.
显示剩余3条评论
1个回答
2

自适应计划可能会改善执行计划。

您将问题标记为12c，但看起来执行计划由于某种原因未使用自适应计划。自适应计划允许Oracle在运行时更改操作，例如在嵌套循环和哈希连接之间切换。

嵌套循环适用于少量的行，而哈希连接适用于大量的行。由于所有行的估计值都很小，但查询运行了三分钟，我猜测优化器明显低估了表达式和连接的基数，并且使用了太多的嵌套循环。

如果启用了自适应计划，则执行计划底部应显示如下内容：

Note
-----
   - this is an adaptive plan

由于缺少该注释，我猜测您的数据库存在参数问题，导致自适应计划无法运行。运行以下查询并查看是否关闭了其中一个功能，或者这些功能的版本设置低于12：

由于缺失该注释，我猜测您的数据库存在参数问题，导致自适应计划无法运行。运行以下查询并查看是否关闭了其中一个功能，或者这些功能的版本设置低于12：

select name, value
from v$parameter
where name in (
    'optimizer_adaptive_features', --12.1 only
    'optimizer_adaptive_plans', --12.2+
    'optimizer_adaptive_reporting_only',
    'optimizer_features_enable',
    'compatible'
    )
order by 1;

编辑1

我不确定为什么适应性计划对你不起作用。如果没有人能够弄清楚，那么我们将需要调查执行计划的实际值，以精确找出哪些操作速度较慢。

获取实际数字的至少有两种方法。如果您可以更改并重新运行有问题的查询，您可以使用提示GATHER_PLAN_STATISTICS。

--Run slow query and wait for it to finish:
select /*+ gather_plan_statistics */ * from dual;

--Find the SQL_ID of the query using some distinctive text:
select *
from v$sql
where lower(sql_fulltext) like '%gather_plan_statistics%';

--Generate execution plan with actual values.
select *
from table(dbms_xplan.display_cursor(sql_id => 'SQL_ID from above', format=>'allstats last'));

如果您无法更改查询，您可以使用SQL监视器报告查找实际值。（此功能需要企业版和调整包许可证。）"最初的回答"
--Generate SQL Monitoring Report:
select dbms_sqltune.report_sql_monitor(sql_id => 'SQL_ID from above') from dual;

编辑2

你确定你找到了正确的SQL_ID吗？你可能需要再次检查GV$SQL。有时，如果SQL是从应用程序或PL/SQL块提交的，则会切换大小写。如果有人运行alter system flush shared_pool;、收集统计信息或等待时间过长，那么真正的SQL语句很少会过期。

如果确实是正确的执行计划，那么查询不会花费任何时间。通常这意味着网络发送结果或应用程序处理结果需要花费时间。但由于没有返回行，因此不太可能出现网络或应用程序问题。

如果时间花费在数据库上，但不是在该查询上，那么我猜测可能是解析问题或递归查询问题。可以通过跟踪找到解析问题，但这些问题是由极端异常的问题或查询引起的，可能不是这种情况。

也许Oracle用于收集元数据的查询之一花费了太长时间。对于许多查询，Oracle需要运行其他查询来检查特权、动态采样等。您可能需要调整其中一个其他查询，下面的语句可以帮助您完成这个痛苦的过程：

--Clear existing run times (be careful running this on production).
--(This won't flush queries that are actively running.)
alter system flush shared_pool;

--Run your slow SQL statement here.
--...

--Now look for anything "weird" that has taken up most of the time. 
select elapsed_time/1000000 seconds, gv$sql.*
from gv$sql
order by seconds desc;
@AJ。你的参数看起来很好，我很惊讶自适应计划在这里没有被使用。我可能漏掉了什么，但我不确定是什么。此外，实际统计数据的执行计划与您描述的问题不符。实际运行时间少于一秒钟。你能生成慢查询的实际数字而不是快速查询吗？ - Jon Heller
请问您能帮忙吗？我该如何生成快速查询的实际数字？ - AJ.
@AJ。我添加了一些有关生成实际数字的详细信息。虽然我认为您会想要它们用于慢查询，而不是快速查询。 - Jon Heller
谢谢。我已经添加了注释。不过我仍然感到困惑。 - AJ.
感谢Jon的所有帮助。统计数据是一周前收集的，DBA_TAB_STATISTICS中没有过期的统计数据，但由于这并没有起到帮助作用，我联系了DBA，并让他重新收集了底层表和索引的统计数据。现在查询返回结果少于一秒钟。问题：如果DBA_TAB_STATISTICS.STALE_STATS='NO'，如何确定是否需要再次收集表和索引的统计数据？ - AJ.
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接