[ 예상 실행 계획 ]
문제에 있는 실행계획은 "예상실행계획"임. 즉, 실행계획의 수치를 모두 믿어서는 안됨.
예상 실행계획으로 볼 수 있는 이유는 "Rows" 컬럼임.
실행 후 실행계획 볼때 사용하는 display_curosr로 보면 "E-Rows","A-Rows"가 출력됨.
상품기본"이력"임시 테이블(지문상 천만건)이고, 심지어 파티션 128개 인데 5,248건으로 출력되고 있음.
논리상 맞지 않음. 물론, where 조건절에 합당한 조건절이 있다면 모르겠으나. 문제에는 표기된 것 없음.
그리고 힌트에 병렬 degree 32 모든 테이블에 준것으로 보아 전체 테이블 대상으로 조인하는 것으로 이해됨.
* 결론 : "상품기본임시테이블" FTS 하는데 5,428 ROW 라는 통계 정보는 현실과 동 떨어진 정보.
[ BROADCAST 분배 방식 ]
: 상품기본이력임시 테이블을 동적분배 SeT 에 있는 32개 병렬 프로세스가 block granule로 나눠서 읽습니다.
각각 프로세스가 병렬 조인 처리하는 Set 에 있는 32개 프로세들에게 모두 전달합니다.
( 1 process -> 추출한 데이터 -> 32 process ) 이 작업을 동적 분배 하는 32개 모두 동일하게 함.
결국 조인처리하는 프로세스 32개는 상품기본이력임시에서 추출된 데이터를 모두 똑같이 가지게 됩니다.
* Inter operation parallelism
: 데이터 분배 set (32 process) 과 조인 처리 하는 set (32 process)간에 발생하는 데이터 통신.
[ 문제 발생 원인 ]
문제는 모두 HASH JOIN 처리되고 있으며, Temp 세그먼트까지 다 쓴 상황까지 갔으면 BUILD INPUT을 생성하는데
대량 데이터가 사용 된것으로 보면 됩니다.
즉, BUILD INPUT 생성시 SESSION에서 할당받은 PGA 용량으로는 부족하여 TEMP SEGMENT까지 사용 되었다.
그래서 어디에서 쓰였을까 찾기! 실행계획의 데이터 재분배 관점으로 보면 "BROADCAST " 3개가 눈에 들어옵니다.
아래 3개 읽어보시면 1번이 문제될 요소로 밖에 없습니다.
1. 상품기본이력임시 PART
다시 말씀드리지만, 이건 예상 실행 계획임.
이력테이블의 전체 파티션을 조회하는데 많은 데이터가 출력되었을것입니다.
위 [예상실행계획]에서 언급했지만 5,248건을 broadcast 하는 것이 아닙니다.
5248건 x 32 ( 병렬 조인 처리 프로세스 개수 )은 소량입니다. ORA-01652 발생이 되지 않을것임.
전체 테이블(천만건) * 32로 판단해야합니다. (선택한 컬럼으로구성된 천만건)
천만건 전달하는 (예시로써) 데이터 1Gb 로 잡아도 * 32 = 하면 32GB 입니다.
이렇게 큰 수치가 동적 재분배가 되어 "HASH JOIN의 BUILD INPUT 역할"을 합니다.
보통 1GB 단위는 세션 PGA에서 처리할 수 없으므로, TEMP SEGEMNT를 사용합니다.
32GB를 TEMP SEGMENT에 쓰기 작업한 결과 용량 부족으로 ora-01652 에러가 발생 함.
위는 예제로 1GB였지만 상황에 따라 10GB이상으로 분배가 될 수 있습니다. 그러면 320GB까지도 발생될 수 있습니다.
여기서 문제의 핵심은 BROADCAST로 데이터를 분배를 한 이후, 병렬 조인 처리시 분배된 데이터가 하는 역할이 무엇인지
이해하는 것입니다.
문제에서 하는 역할은 BUILD INPUT 이며, SWAP 현상으로 메모리를 사용하는구나 이해하는게 핵심이지 않나 싶습니다.
세션 PGA 할당 받은 내역은 보통 100MB도 안되기 때문에, 큰 데이터가 BUILD INPUT 역할을 한 경우 TEMP를 쓰게 된다.
그래서 분배된 대용량 데이터가 BUILD INPUT 역할하고, TEMP SEGMENT SIZE가 작거나, 사용해야할 용량이 부족하면
ORA-01652를 만날수 있겠구나.
그래서 BROADCAST를 함부로 쓰지말자는 것입니다. 함부로 쓰지 말자는 것이지 기피하는것은 아닙니다.
"현장에서는 복잡하고 긴 HASH JOIN 범벅이 되어 있는 병렬 조인 SQL에서 BROADCAST 할 만한 애들을 보면
어찌나 기쁜지 가뭄의 단비를 만난 겪입니다."
2. 상품기본 이력임시와 상품기본 JOIN 후 BROADCAST
: 데이터 조회된 상황에 따라 BROADCAST할지 파악 해야되나, 문제로봐서는 위 1번을 감안하면 데이터
많을것으로 판단 됩니다. 그런데 PROBE INPUT인 코드상세 테이블이 코드성 테이블이므로, BUILD INPUT 으로
올리는게 맞습니다. 그렇게 해야만 "조인 결과의 큰 데이터"가 BUILD INPUT 역할을 하지 않아 TEMP SEGMENT를
사용할 일이 없어지게됨. 이것이 성능 개선!"
그리고 HASH JOIN은 보통 소량 데이터 BUILD INPUT, 대량 데이터 PROBE INPUT 역할인것을 우리는 다 알고 있음.
3. 코드 상세 => 코드성테이블은 데이터적으므로 BROADCAST 적합.
[ 조치 ]
pq_distribute(b, none, partition)
그래서 답안은 "상품기본" 테이블을 "상품기본이력임시" 테이블과 동일한 파티션 키로 "동적 파티셔닝"으로 해결했네요.
즉, 상품기본이 용량이 2gb라면 최대 2gb만 동적 파티셔닝을 위해 사용함.
(실제 사용하는 컬럼이 적다면 더 적어지겠죠 ). 뻥튀기가 없음.
상품기본이력임시 테이블의 BROADCAST 재분배 작업은 사라짐. ( 문제 원인 해결 ).
그리고 파티션 Granule 단위로 병렬 JOIN 처리하자는 것입니다.
파티션 단위 병렬 조인이 선택된 이유는 전체 파티션 대상으로 병렬 조인 처리시에서 유리한 부분이 있습니다.
병렬 프로세스 마다 파티션 단위로 병렬 조인 처리하는데,
총 작업할 파티션 개수가 적다면 특정 파티션 조인 작업이 오래 걸리고, 나머지가 모두 끝났다면
오래 수행중인 프로세스가 끝나야 sql 수행이 끝나므로 성능 부하의 원인이 될 수 있음.
( 이때는 데이터 상황에 따라 BROADCAST, HASH 데이터 재분배 or REPLICATE 병렬 조인 방법 등으로 극복 ).
반대로 문제와 같이 작업해야할 파티션 개수가 많다면 답안과 같이 파티션 단위 병렬 조인 처리 시도.
[ 요약 ]
"상품기본이력임시" 테이블을 FTS한 데이터를 BROADCAST 방식으로 데이터 재분배 작업을 했다.
대용량 데이터가 추출되고 재분배 작업을 발생했다.
분배된 대량 데이터는 BUILDINPUT 역할을 했기 때문에 SESSION에서 할당받은 PGA만으로는 처리가 안되기에
SWAP 현상이 발생하여 TEMP SEGMENT를 사용했다. 대량으로 사용했기에 ORA-01652을 만났다.
( Temp segment 사용량 = BroadCast 대상 테이블에서 추출한 데이터량 * 32개 ( 병렬조인 프로세스 ) )
이를 해결하기 위해 "상품기본이력임시" 테이블을 재분배작업을 하지 않고,
"상품기본" 테이블을 동적파티셔닝 한 이후에 PARTITION 단위 병렬 조인 처리했다.