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
、インデックスが作成されていない他の 1 つの列のみが選択されます。
テーブル情報:
STG_DIM_CRM_ケース
Index - (Account_Number,Case_Create_Date)
size - 270k records.
stg_scd_顧客キー
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,000 件のレコードを返します。
結合のないクエリは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
が、1 年分のレコードのみがフィルターされたため、役に立ちませんでした。
前もって感謝します。
ベストアンサー1
私が思うに、エンジンは、制限基準を適用する前に、ビュー結合から 1 億件以上のレコードを 5 億件のレコードに解決する必要があるのです (したがって、クロス結合が作成され、インデックスを使用できる場合でも、生成して解析するレコードが大量に発生します。そのため、外部結合として記述したとしても、エンジンはそのように処理できません (理由はわかりません))
したがって、最小でも 100m*500m = 50,000m となり、生成して解析/制限するデータは膨大になります。
ビューを削除することで、エンジンはインデックスをより適切に最適化して使用できるようになり、50,000 m のレコード結合が不要になります。
トラブルシューティングに時間を集中する領域:
- 潜在的なオーバーヘッドの問題を排除するためだけにビューを削除します。
- 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(+)
理由としては: -http://www.dba-oracle.com/art_hints_views.htm
where 句の順序は重要ではありませんが、次の点を考慮してください。オフチェイスでは、エンジンはリストされた順序で実行され、500 m を制限してからビューから補足データを追加すると、論理的には高速になります。
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(+)