优化选择查询性能

Question

我有一个SELECT语句，运行非常慢，它阻碍了我们的夜间进程。

查询是：(请不要评论隐式联接语法，这是由运行此代码的Informatica自动生成的)：

SELECT *
  FROM STG_DIM_CRM_CASES,V_CRM_CASE_ID_EXISTS_IN_DWH,stg_scd_customers_key
 WHERE STG_DIM_CRM_CASES.CRM_CASE_ID = V_CRM_CASE_ID_EXISTS_IN_DWH.CASE_ID(+)
   AND STG_DIM_CRM_CASES.account_number = stg_scd_customers_key.account_number(+)
   and STG_DIM_CRM_CASES.Case_Create_Date between  stg_scd_customers_key.start_date(+) and  stg_scd_customers_key.end_date(+)

编辑：实际查询只选择account_number,start_date,end_date和另一个没有索引的列。

表格信息：

STG_DIM_CRM_CASES

Index - (Account_Number,Case_Create_Date)
size - 270k records.

stg_scd_customers_key

Index - Account_Number,Start_Date,End_Date
Partitioned - End_Date
Size - 500 million records.

V_CRM_CASE_ID_EXISTS_IN_DWH(视图)-

select  t.case_id
from crm_ps_rc_case t, dim_crm_cases x
where t.case_id=x.crm_case_id;

dim_crm_cases -

Indexed - (crm_case_id)
Size - 100 million .

crm_ps_rc_case -

Size - 270k records

编辑--如果不清楚，视图将返回270 k记录。

没有连接到stg_scd的查询耗时数秒，似乎是导致性能问题的部分原因，视图也在几秒钟内运行，尽管它正在被连接到一个1亿条记录表中。现在查询大约需要12到30分钟，这取决于我们的资源有多忙。

以下是执行计划：

6   |   0 | SELECT STATEMENT                |                             |  3278K|  1297M|   559K  (4)| 02:10:37 |       |       |        |      |            |
7   |   1 |  PX COORDINATOR                 |                             |       |       |            |          |       |       |        |      |            |
8   |   2 |   PX SEND QC (RANDOM)           | :TQ10003                    |  3278K|  1297M|   559K  (4)| 02:10:37 |       |       |  Q1,03 | P->S | QC (RAND)  |
9   |*  3 |    HASH JOIN OUTER              |                             |  3278K|  1297M|   559K  (4)| 02:10:37 |       |       |  Q1,03 | PCWP |            |
10  |   4 |     PX RECEIVE                  |                             | 29188 |    10M| 50662   (5)| 00:11:50 |       |       |  Q1,03 | PCWP |            |
11  |   5 |      PX SEND HASH               | :TQ10002                    | 29188 |    10M| 50662   (5)| 00:11:50 |       |       |  Q1,02 | P->P | HASH       |
12  |*  6 |       HASH JOIN RIGHT OUTER     |                             | 29188 |    10M| 50662   (5)| 00:11:50 |       |       |  Q1,02 | PCWP |            |
13  |   7 |        BUFFER SORT              |                             |       |       |            |          |       |       |  Q1,02 | PCWC |            |
14  |   8 |         PX RECEIVE              |                             | 29188 |   370K| 50575   (5)| 00:11:49 |       |       |  Q1,02 | PCWP |            |
15  |   9 |          PX SEND BROADCAST      | :TQ10000                    | 29188 |   370K| 50575   (5)| 00:11:49 |       |       |        | S->P | BROADCAST  |
16  |  10 |           VIEW                  | V_CRM_CASE_ID_EXISTS_IN_DWH | 29188 |   370K| 50575   (5)| 00:11:49 |       |       |        |      |            |
17  |* 11 |            HASH JOIN            |                             | 29188 |   399K| 50575   (5)| 00:11:49 |       |       |        |      |            |
18  |  12 |             TABLE ACCESS FULL   | CRM_PS_RC_CASE              | 29188 |   199K|   570   (1)| 00:00:08 |       |       |        |      |            |
19  |  13 |             INDEX FAST FULL SCAN| DIM_CRM_CASES$1PK           |   103M|   692M| 48894   (3)| 00:11:25 |       |       |        |      |            |
20  |  14 |        PX BLOCK ITERATOR        |                             | 29188 |    10M|    87   (2)| 00:00:02 |       |       |  Q1,02 | PCWC |            |
21  |  15 |         TABLE ACCESS FULL       | STG_DIM_CRM_CASES           | 29188 |    10M|    87   (2)| 00:00:02 |       |       |  Q1,02 | PCWP |            |
22  |  16 |     BUFFER SORT                 |                             |       |       |            |          |       |       |  Q1,03 | PCWC |            |
23  |  17 |      PX RECEIVE                 |                             |   515M|    14G|   507K  (3)| 01:58:28 |       |       |  Q1,03 | PCWP |            |
24  |  18 |       PX SEND HASH              | :TQ10001                    |   515M|    14G|   507K  (3)| 01:58:28 |       |       |        | S->P | HASH       |
25  |  19 |        PARTITION RANGE ALL      |                             |   515M|    14G|   507K  (3)| 01:58:28 |     1 |  2982 |        |      |            |
26  |  20 |         TABLE ACCESS FULL       | STG_SCD_CUSTOMERS_KEY       |   515M|    14G|   507K  (3)| 01:58:28 |     1 |  2982 |        |      |            |
27  ------------------------------------------------------------------------------------------------------------------------------------------------------------
28   
29  Predicate Information (identified by operation id):
30  ---------------------------------------------------
31   
32     3 - access("STG_DIM_CRM_CASES"."ACCOUNT_NUMBER"="STG_SCD_CUSTOMERS_KEY"."ACCOUNT_NUMBER"(+))
33         filter("STG_DIM_CRM_CASES"."CASE_CREATE_DATE">="STG_SCD_CUSTOMERS_KEY"."START_DATE"(+) AND 
34                "STG_DIM_CRM_CASES"."CASE_CREATE_DATE"<="STG_SCD_CUSTOMERS_KEY"."END_DATE"(+))
35     6 - access("STG_DIM_CRM_CASES"."CRM_CASE_ID"="V_CRM_CASE_ID_EXISTS_IN_DWH"."CASE_ID"(+))
36    11 - access("T"."CASE_ID"="X"."CRM_CASE_ID")

注释：添加索引可能是一个问题，取决于索引。这并不是唯一使用此表的地方，因此索引可能会干扰这些表上的其他命令(主要是插入)。

我还尝试在stg_scd上添加一个过滤器，并排除所有小于Table_Cases中最小日期的日期，但这并没有帮助，因为它只过滤了一年的记录。

提前谢谢。

Answer 1

我认为正在发生的是，引擎在应用限制条件之前，必须解析视图连接到500 M记录的100m+记录(因此它创建了交叉连接，即使它可以使用索引生成大量记录，然后进行解析)。所以即使你把它写成一个外部连接，引擎也不能这样处理它(我不知道为什么)

所以，在至少100米*500米=5万米时，需要生成大量的数据，然后解析/限制。

通过消除视图，引擎可以更好地优化和使用索引，从而消除对5万米记录连接的需求。

我将把时间集中在故障排除的领域：

• 消除视图只是为了将其作为潜在的开销问题删除。
• 认识到stg_scd_customers_key和V_CRM_CASE_ID_EXISTS_IN_DWH之间不存在联系。这意味着引擎可能在解析STG_DIM_CRM_CASES到stg_scd_customers_key的结果之前进行交叉连接。

考虑删除视图，或者使用内联视图。

消除这一观点：

SELECT *
  FROM STG_DIM_CRM_CASES 
      ,crm_ps_rc_case t
      ,dim_crm_cases x 
      ,stg_scd_customers_key
 WHERE t.case_id=x.crm_case_id
   AND STG_DIM_CRM_CASES.CRM_CASE_ID = t.CASE_ID(+)
   AND STG_DIM_CRM_CASES.account_number = stg_scd_customers_key.account_number(+)
   AND STG_DIM_CRM_CASES.Case_Create_Date 
       between  stg_scd_customers_key.start_date(+) and stg_scd_customers_key.end_date(+)

使用内联视图：

SELECT *
  FROM STG_DIM_CRM_CASES 
  (select  t.case_id
   from crm_ps_rc_case t, dim_crm_cases x
   where t.case_id=x.crm_case_id) V_CRM_CASE_ID_EXISTS_IN_DWH
      ,stg_scd_customers_key
 WHERE STG_DIM_CRM_CASES.CRM_CASE_ID = V_CRM_CASE_ID_EXISTS_IN_DWH.CASE_ID(+)
   AND STG_DIM_CRM_CASES.account_number = stg_scd_customers_key.account_number(+)
   AND STG_DIM_CRM_CASES.Case_Create_Date 
       between  stg_scd_customers_key.start_date(+) and stg_scd_customers_key.end_date(+)

为什么：- views.htm

虽然where子句的顺序应该不重要:在停止追逐时，引擎按所列顺序执行，将500米降下来，然后从视图中添加补充数据在逻辑上会更快。

SELECT *
  FROM STG_DIM_CRM_CASES,stg_scd_customers_key,V_CRM_CASE_ID_EXISTS_IN_DWH
 WHERE STG_DIM_CRM_CASES.account_number = stg_scd_customers_key.account_number(+)
   and STG_DIM_CRM_CASES.Case_Create_Date between  stg_scd_customers_key.start_date(+) and  stg_scd_customers_key.end_date(+)
   and STG_DIM_CRM_CASES.CRM_CASE_ID = V_CRM_CASE_ID_EXISTS_IN_DWH.CASE_ID(+)

Answer 2

问题是在扫描所有分区时：

18 \ PX发送散列:28 10001\x515m\x14G\\ 507K (3)\x{e76f}\{e76f}\x{e76f}\x{e76f}{##**$}}\{e76f}\{e76f}\x{e76f} \x{e76f}\x{e76f}=515 m\\x{e76f}\\x{e76f} 507K (3)\x{e76f}\x{e76f}\x{e76f} *

之所以发生这种情况，是因为您对此表使用了左联接。可以使用绑定变量选择一个分区吗？什么是分区密钥？我看不出并行的暗示，但按照你的计划，它使用并行。在任何对象级别上是否存在平行度？你能不能删除平行和张贴解释计划，而不是平行？

Answer 3

我认为问题在于视图，我怀疑该视图在应用条件之前完全执行并返回所有行。

视图的总体效果是添加列CASE_ID，如果在其中找到CRM_CASE_ID，则为非null，否则为null。我用两个直接连接和一个CASE表达式替换了视图。通过用逻辑替换视图的方便性，您可以直接连接到其中的每个表，从而避免一个级别的连接深度。

尝试运行此版本的查询：

SELECT
  a.*, b.*, c.*,
  CASE WHEN t.case_id is not null and X.case_id is not null then t.case_id END CASE_ID
FROM STG_DIM_CRM_CASES a
LEFT JOIN crm_ps_rc_case t
  ON t.case_id = a.CRM_CASE_ID
LEFT JOIN dim_crm_cases x
  ON x.crm_case_id = a.CRM_CASE_ID
LEFT JOIN V_CRM_CASE_ID_EXISTS_IN_DWH b
  ON a.CRM_CASE_ID = b.CASE_ID
LEFT JOIN stg_scd_customers_key c
  ON a.account_number = c.account_number
 and a.Case_Create_Date between c.start_date and  stg_scd_customers_key.end_date

如果只使用实际需要的列替换a.*, b.*, c.*，则会加快速度，因为返回的数据更少。如果还将索引放在查找键上，加上实际选择的所有列(覆盖索引)，则会大大加快速度，因为只能使用索引访问。

您应该至少验证所有联合到列中都有索引。