SELECT 쿼리 성능 최적화
나는 있습니다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(+)
edit: 실제 쿼리는 선택만 합니다.account_number,start_date,end_date인덱스되지 않은 다른 열도 있습니다.
테이블 정보:
STG_DIM_CRM_케이스
Index - (Account_Number,Case_Create_Date)
size - 270k records.
stg_scd_message_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_dim_cases -
Indexed - (crm_case_id)
Size - 100 million .
crm_ps_rc_case -
Size - 270k records
편집 - 명확하지 않은 경우 보기에서 270k 레코드를 반환합니다.
에 하지 않은 입니다.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년의 기록을 걸러냈기 때문입니다.
잘 부탁드립니다.
엔진은 제한 기준을 적용하기 전에 뷰 조인에서 500m 레코드까지 100m 이상의 레코드를 해결해야 합니다(따라서 교차 조인을 생성하고 많은 레코드를 생성한 후 구문 분석할 수 있는 경우에도 마찬가지입니다).그래서 외부 조인으로 작성했는데도 엔진이 그런 방식으로 처리할 수 없습니다(이유는 모르겠습니다).
따라서 최소 100m*500m = 50,000m에서는 생성해야 할 많은 데이터가 생성된 다음 구문 분석/제한됩니다.
뷰를 제거함으로써 엔진이 인덱스를 최적화하고 사용할 수 있으므로 50,000m 레코드 조인이 필요하지 않습니다.
문제 해결에 시간을 할애해야 하는 분야:
- 뷰를 제거하여 잠재적인 오버헤드 문제를 제거할 수 있습니다.
- stg_scd_customers_key와 V_CRM_CASE_ID_EXIST_IN_DWH 사이에 연결이 없음을 인식합니다.즉, STG_DIM_CRM_CARES to 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 절의 순서는 중요하지 않지만 다음을 고려해야 합니다.출발 시 엔진이 나열된 순서대로 작동하고 500m 아래로 제한한 다음 뷰에서 보조 데이터를 추가하는 것이 논리적으로 더 빠릅니다.
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(+)
모든 파티션을 검색하는 데 문제가 있습니다.
| HASH : || | (| 28 | | | | | 범위ALL | 18 | PX SEND | :TQ10001 | 515M | 14G | 507K (3) 01:58:28 | S-> 14G | S-> 19 | 18 | 18 | 18 | 18 | 18 | 18 | 18 | 18 | 18 | 18 | 11:18
| | 515M | 14G | 507K (3) | 01:58:28 | 1 | 2982 | |
| 26 | 20 | 테이블 액세스 풀 | STG_SCD_CUSTERMS_KEY | 515M | 14G|
이 테이블에 왼쪽 조인을 사용하고 있기 때문에 발생합니다.bind 변수를 사용하여 파티션을 하나 선택할 수 있습니까?파티션 키란?병렬에 대한 힌트는 보이지 않지만 당신의 계획에 따르면 병렬을 사용합니다.물체 레벨에 평행도가 있습니까?parallel을 제거하고 post explain plan을 post explain 없이 해주실 수 있나요?
상황이 적용되기 전에 모든 행을 완전히 실행하고 되돌리고 있는 것으로 의심되는 뷰가 문제라고 생각합니다.
인 효과는 을 추가하는 입니다.CASE_ID만약 그렇다면 그것은 무효가 아닙니다.CRM_CASE_ID해당 파일에 있습니다. 그렇지 않으면 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.*실제로 필요한 열만 있으면 반환할 데이터가 적기 때문에 속도가 빨라집니다.인덱스만 사용할 수 있기 때문에 실제로 선택한 모든 열(포함 인덱스)과 함께 검색 키에도 인덱스를 배치하면 속도가 상당히 빨라집니다.
최소한 모든 조인된 열에 인덱스가 있는지 확인해야 합니다.
을 문제Oracle 필을행얻을수있방에서 가져오는 이 두 입니다.stg_scd_customers_key단의 (A) 단일수행다니합을작업▁다▁either▁(▁does▁(▁single니수행a합▁ita을)작단.FULL SCAN해당 테이블에서 해당 테이블의 행을 필터링한 다음 원하지 않는 행을 필터링합니다. 그렇지 않으면 (B) 270,000개의 인덱스 검색을 수행합니다. 각 인덱스의 높이에 따라 각 3~5개의 논리적 I/O와 테이블에서 실제로 블록을 읽기 위한 또 다른 논리적 I/O를 수행합니다.
멀티블록 읽기 및 기타 최적화 기능을 사용할 경우FULL SCAN통계를 는 다음과 같이 합니다.FULL SCAN더 빠를 겁니다.그리고 그것이 옳을 가능성이 높습니다.
Oracle에 더 나은 옵션을 제공해야 합니다.
여러분이 있는 곳에서 구체화된 관점을 사용할 수 없다면, 좋은 "가난한 사람"의 구체화된 관점은 커버링 인덱스라고 불리는 것입니다.자, 그건 당신의 질문에 합리적이지 않습니다, 당신이 하기 때문에.SELECT *하지만 당신은 정말로 모든 칼럼이 필요합니까?stg_scd_customers_key?
당신이 얻을 수 있는 열 목록을 잘라낼 수 있다면,stg_scd_customers_key가 (A)로 할 수 .account_number,start_date,그리고.end_date합니다. 및 (B)는 선택해야 합니다.
예:
SELECT stg_im_crm_cases.*, V_CRM_CASE_ID_EXISTS_IN_DWH.*, stg_scd_customers_key.column_1, stg_scd_customers_key.column_2
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(+)
쿼리를 만들고 stg_scd_customers_key(account_number, start_date, end_date, column_1, column_2)에 인덱스를 생성할 수 있다면 Oracle에 더 나은 대안을 제시할 수 있을 것입니다.이제 테이블 대신 인덱스만 읽을 수 있습니다.
그렇게 큰 테이블에서는, 당신이 그것을 시도하기 전까지는 보장이 없습니다.그러나 색인을 다루는 것은 종종 의사가 지시한 것에 불과합니다. (물론 새로운 색인에 대한 모든 일반적인 주의 사항이 적용됩니다.)
몇 가지 고려 사항:
INDEX
crm_ps_rc_case에 대한 색인이 없습니다.case_id 270k JOIN (않음)으로요, 270k <-> 100m에 가입하시는군요.
SELECTED COLUMNS
는 견해V_CRM_CASE_ID_EXISTS_IN_DWH을 선택합니다.t.case_id하지만 그것은 선택해야 합니다.x.crm_case_id 인 하 덱 결 해 을 싱 까t.case_id이렇게 하면 HASH JOIN이 모든 실행 계획에 퍼집니다.(좋지 않음)
특히 큰 테이블에서는 범위 결합/결합이 항상 문제가 되지만 범위에 조건을 추가하는 문제를 제한할 수 있습니다.제가 설명해 드리자면, 이 조건들을 당신의 것에 추가하려고 노력하세요.WHERE절:
AND stg_scd_customers_key.end_date = (
SELECT min(r.end_date)
FROM stg_scd_customers_key r
WHERE r.end_date >= STG_DIM_CRM_CASES.Case_Create_Date
)
AND stg_scd_customers_key.start_date = (
SELECT max(r.start_date)
FROM stg_scd_customers_key r
WHERE r.start_date <= STG_DIM_CRM_CASES.Case_Create_Date
)
예, 270k * 2개의 하위 쿼리를 계산하지만, 최종 조인은 IO 작업을 훨씬 적게 제한하는 재귀 작업에서 작동합니다(더 나을 것입니다).
ORDER는 다음과 .
만약 그렇다면, 또는 그것이 중요하지 않다면, 내 경험에 따르면, 충돌하는 보고서들이 있습니다.그래요.약간의 개선에 불과할 수도 있지만 다음에서 인덱스를 수정할 수 있습니다.stg_scd_customers_key서를거꾸하의 하기Start_Date그리고.End_Date제 경험상, 저는 범위 필터링이 인덱스의 하한 앞에 상한을 두는 것이 더 효율적이라는 것을 알게 되었습니다.
언급URL : https://stackoverflow.com/questions/38613118/optimizing-select-query-performance
'programing' 카테고리의 다른 글
| Angular2 - 스타일에 [_ngcontent-mav-x] 추가 (0) | 2023.06.26 |
|---|---|
| Mounted hook에서 내부의 로컬 값을 저장하는 방법 - Vue 3 (0) | 2023.06.26 |
| Kotlin + Gradle 확인되지 않은 참조 (0) | 2023.06.26 |
| MongoDB 구조: 단일 컬렉션 대 여러 소규모 컬렉션 (0) | 2023.06.26 |
| 클래스 속성을 만드는 방법? (0) | 2023.06.26 |