logo标签列表

Oracle - 查询运行非常缓慢

sqloracle
3

我有一个简单的查询，但一直运行不结束。其中有一个日期条件，一旦我去掉它，查询就会立刻返回结果。这个日期字段的格式为“31-MAR-15”。我不明白为什么这个条件会让查询变得如此缓慢。提前谢谢！

SELECT
  substr(a.id, 1, 2)   AS country,
  count(DISTINCT a.id) AS id_count,
  sum(a.amount)        AS amount
FROM table1 a
  JOIN table2 b ON a.id = b.id
  JOIN table3 c ON b.party_id = c.party_id
WHERE a.prod_type = 'INS'
  AND c.acct_type = 'LON'
  AND substr(a.id, 1, 2) = 'US'
  AND a.dump_dt = '31-MAR-15'
  AND substr(id, 4, 8) = '20150303'
GROUP BY substr(a.id, 1, 2);

执行计划：

PLAN_TABLE_OUTPUT
Plan hash value: 255044277

------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                        |     1 |   121 |   125K  (1)| 00:25:08 |
|   1 |  HASH GROUP BY                    |                        |     1 |   121 |   125K  (1)| 00:25:08 |
|   2 |   VIEW                            | VW_DAG_0               |     1 |   121 |   125K  (1)| 00:25:08 |
|   3 |    HASH GROUP BY                  |                        |     1 |    98 |   125K  (1)| 00:25:08 |
|   4 |     NESTED LOOPS                  |                        |       |       |            |          |
|   5 |      NESTED LOOPS                 |                        |     1 |    98 |   125K  (1)| 00:25:08 |
|   6 |       MERGE JOIN CARTESIAN        |                        | 12613 |   800K| 21133   (2)| 00:04:14 |
|*  7 |        TABLE ACCESS BY INDEX ROWID| TABLE1                 |     1 |    45 |    46   (0)| 00:00:01 |
|*  8 |         INDEX RANGE SCAN          | DATA_DATE__STG_BACKUP2 |  1040 |       |     6   (0)| 00:00:01 |
|   9 |        BUFFER SORT                |                        |   182K|  3564K| 21087   (2)| 00:04:14 |
|* 10 |         TABLE ACCESS FULL         | TABLE3                 |   182K|  3564K| 21087   (2)| 00:04:14 |
|* 11 |       INDEX RANGE SCAN            | BSB_PARTYID_IDX        |    22 |       |     3   (0)| 00:00:01 |
|* 12 |      TABLE ACCESS BY INDEX ROWID  | TABLE2                 |     1 |    33 |    10   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

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

   7-filter(SUBSTR(A.ID, 4, 8) = '20150303' AND SUBSTR(A.ID, 1, 2) = 'US'
              AND A.PROD_TYPE = 'INS')
   8 - access(A.DUMP_DT = '31-MAR-15')
  10 - filter(C.ACCT_TYPE = 'LON')
  11 – access(B.PARTY_ID = C.PARTY_ID)
  12 - filter(A.ID = B.ID)
1
dump_dt 的类型是什么？你有哪些索引？执行计划是什么？ - Mat
你检查过DUMP_DT上是否有索引了吗？ - PhillipD
2
这是Oracle的无效SQL，它不支持用AS关键字给表起别名。你没有告诉我们所有信息...正如其他人所说，表DDL和解释计划对于你得到一个好的答案是“必不可少”的。看起来你把日期存储为字符串，这总是一个灾难的配方，最后SUBSTR(ID, 4, 9)返回9个字符，而不是8个，所以除非ID小于13个字符长，否则我期望SUBSTR(ID, 4, 9) = '20150303'总是返回空，这意味着你的查询不会返回任何东西。你能澄清一下你的问题吗？ - Ben
1
请将此信息添加到您的问题中，包括相关索引和解释计划。（并且在发布此类问题时始终从一开始添加该信息。） - Mat
查询中的SUBSTR(ID, 4, 8) = '20150303'部分是正确的。问题出在A.DUMP_DT = '31-MAR-15'这一部分。当我运行解释计划时，该过滤器的谓词信息显示访问(A.DUMP_DT='31-MAR-15')时出现了问题。 - user2511999
显示剩余7条评论
3个回答
1

看起来优化器在对TABLE1应用这4个谓词后，显著低估了返回的行数。

A.PROD_TYPE = 'INS'
SUBSTR(A.ID, 1, 2) = 'US'
A.DUMP_DT = '31-MAR-15'
SUBSTR(ID, 4, 8) = '20150303'

稍微离题：更安全的做法是使用 ANSI 文本 date '2015-03-31' 而不是隐式转换字符串 '31-MAR-15'。此外，该语句存在一些错误，比如在前两个谓词之间缺少条件，并且在最后一个谓词前缺少 A.。

首先，确保所有表格上都有准确的统计数据，并查看是否更改了解释计划：

begin
    dbms_stats.gather_table_stats(user, 'TABLE1');
    dbms_stats.gather_table_stats(user, 'TABLE2');
    dbms_stats.gather_table_stats(user, 'TABLE3');
end;
/

"

“智能列”ID使得在应用条件后估计返回的行数变得困难。 如果更改数据模型已经太晚，您至少可以向Oracle提供一些扩展统计信息来帮助处理谓词：

"
select dbms_stats.create_extended_stats(user, 'TABLE1', '(SUBSTR(ID, 1, 2))') from dual;
select dbms_stats.create_extended_stats(user, 'TABLE1', '(SUBSTR(ID, 4, 8))') from dual;

我猜想SUBSTR(A.ID, 1, 2) = 'US'是一个常见的值，但是如果没有扩展统计信息，Oracle就无法得知。额外的直方图可能会显著增加基数。然后优化器将不会选择两个不相关表之间的笛卡尔积。
1
我已经简化了WHERE子句中对A.ID字段的条件。
A.ID LIKE 'US_20150303%'

具有与

相同的效果。
substr(a.id, 1, 2) = 'US' AND substr(id, 4, 8) = '20150303'

如果列 A.ID 被索引，那么应用 SUBSTR(a.ID,..) 函数将使索引无效。

另一方面，a.dump_dt 似乎是一个日期类型的列，因此在该列上应用过滤器的首选方法可能是

a.dump_dt = TO_DATE('31-MAR-15', 'DD-MON-RR')

替换为

而不是

。
a.dump_dt = '31-MAR-15'

后者主要取决于运行查询的Oracle客户端的NLS_DATE_FORMAT，有时可能会通过忽略对a.dump_dt上索引的使用而对性能产生负面影响。
因此，重写后的查询如下：
SELECT
  SUBSTR(A.ID, 1, 2)   AS country,
  COUNT(DISTINCT A.ID) AS id_count,
  SUM(A.amount)        AS amount
FROM table1 A
  JOIN table2 b ON A.ID = b.ID
  JOIN table3 c ON b.party_id = c.party_id
WHERE A.prod_type = 'INS'
  AND c.acct_type = 'LON'
  AND A.ID LIKE 'US_20150303%'
  AND A.dump_dt = TO_DATE('31-MAR-15', 'DD-MON-RR')
GROUP BY SUBSTR(A.ID, 1, 2);
1
你好，能否编辑你的回答并解释一下这个查询语句是如何解决问题的呢？仅仅给出代码是不被鼓励的，也可能会被删除。谢谢。 - Tim Malone
-1

尝试使用Oracle提示来稳定选择计划，或者您可以使用以下技巧：

....
And A.DUMP_DT+0 =  to_date('31-MAR-15','dd-mon-    rr')
...
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接