|
오라클 실습 |
[1] |
|
등록일:2009-05-28 00:32:21 (0%) 작성자: 제목:Hash Join Right (Semi/Anti/Outer) 의 용도 |
|
<2009.03.11 : 아래 내용중의 Right 의 의미는 Oracle 10g Performance Tuning Guide 19-21 에 나온대로 "올바른" 이란 뜻이 아니라 "Left 혹은 Right Outer Join 시의 Right" 의 의미로 바꿉니다. 하지만 이글의 핵심인 "이전버젼까지는 항상 후행집합으로 되던것이 10g 부터는 선행집합이 될수 있다." 는 바뀐것이 없습니다. > 오늘은 Hash Join Right (Semi/Anti/Outer) 의 용도에 대하여 알아보려한다. Oracle 10g 부터 Hash Join 은 서서히 변화하기 시작하였다. 특히 Hash Join Right (Semi/Anti/Outer) 기능을 사용하여 대용량 집합의 Join 시 획기적인 성능향상을 이루었다. Hash Join 에서 Right 옵션이 붙음으로서 획기적인 성능향상을 이루었다는 이유는 무엇일까? Semi/Anti Join은 항상 메인 쿼리가 수행된 후 서브쿼리의 데이터를 체크하는 방식이다. 따라서 Semi/Anti Join 의 경우 서브쿼리는 항상 후행집합이 될수 밖에 없다. Hash Outer Join 의 경우도 마찬가지로 (+) 표시가 붙는 쪽의 집합은 항상 후행집합이 될수 밖에 없었다. 하지만 10g 부터 Hash Join Right (Semi/Anti/Outer) 기능이 나오게 되면서 서브쿼리 혹은 아우터 Join 되는 쪽의 집합이 선행집합이 될수 있다. 이때 Right 의 뜻은 left 집합 대신에 right(후행집합)을 선행집합으로 하겠다는 뜻이다. 9i 까지 Hash Join (Semi/Anti/Outer)의 경우에 눈물을 머금고 대량의 집합을 선행처리할수 밖에 없었지만 이제는 자유롭게 선행집합을 선택할수 있는것이다. 국내외 튜닝책을 막론하고 이막강한 기능에 대하여 제대로 다루는 것을 본적이 없다. 왜냐하면 초대용량 DB(VLDB)에서 Join Method 를 튜닝해본 사람만이 이 기능이 얼마나 중요한지 피부로 느낄수 있기 때문이다. 아래의 스크립트를 보자. 환경 : 10.2.0.4
1.테이블을 생성하고 Aanlyze 를 한다.
1.테이블 생성 CREATE TABLE BIG_EMP AS SELECT ROWNUM AS EMPNO, A.ENAME, A.JOB, A.MGR, A.HIREDATE, A.SAL, A.COMM, A.DEPTNO FROM EMP A, (SELECT LEVEL AS NO FROM DUAL CONNECT BY LEVEL <= 2000) B;
ALTER TABLE BIG_EMP ADD ( CONSTRAINT PK_BIG_EMP PRIMARY KEY (EMPNO) USING INDEX );
dbms_stats.gather_table_stats(user, 'BIG_EMP', cascade => true);
2.Hash Semi Join 을 유도한다.
select a.empno, a.sal from big_emp a where exists (select /*+ use_hash(b) */ b.deptno from dept b where b.deptno = a.deptno ) ; -------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts |Cost (%CPU)| A-Rows | A-Time | Buffers | Used-Mem | -------------------------------------------------------------------------------------------------------- |* 1 | HASH JOIN RIGHT SEMI| | 1 | 4 (25)| 26000 |00:00:00.06 | 176 | 813K (0)| | 2 | INDEX FULL SCAN | PK_DEPT | 1 | 1 (0)| 4 |00:00:00.01 | 1 | | |* 3 | TABLE ACCESS FULL | BIG_EMP | 1 | 2 (0)| 26000 |00:00:00.01 | 175 | | --------------------------------------------------------------------------------------------------------
위의 통계정보를 보면 176 블럭을 scan 했으며 Hash area size 를 813 K를 사용했다는걸 알수 있다. 작은 용량의 테이블인 DEPT 를 Driving 집합(Build Input) 으로 선택하고 BIG_EMP 테이블을 후행(Probe) 테이블로 Hash 조인 함으로서 최적의 조인이 되었다. 그렇다면 Hash Join Right Semi 를 사용하지 않으면 어떻게 될것인가? Subquery Unnesting 기능을 이용하면 작은 용량의 테이블인 DEPT 를 Driving 집합(Build Input) 으로 선택할수는 있다. 하지만 아래처럼 약간의 손해를 감수해야 한다.
select /*+ gather_plan_statistics ordered */ a.empno, a.sal from big_emp a where exists (select /*+ use_hash(b) */ b.deptno from dept b where b.deptno = a.deptno ); 위의 SQL 을 보면 강제로 MAIN 쿼리에 ordered 힌트를 주어 Semi Join 이 아닌 SubQuery Unnesting 이 되었다. ordered 힌트를 사용한 이유는 서브쿼리가 Semi Join 에 실패할 경우 Subquery Unnesting 을 시도하게 되는데 이때 서브쿼리블럭이 From 절의 가장 좌측으로 오기 때문이다. 사용자가 ordered 힌트등을 사용하면 오라클 내부적인 leading 힌트와 Swap_join_inputs 힌트 등이 Override 되어 무시된다. 따라서 Semi Join 이 아닌 Subquery Unnesting 되는 것이다. 이제 Plan 을 보자.
------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts |Cost (%CPU)| A-Rows | A-Time | Buffers | Used-Mem | ------------------------------------------------------------------------------------------------------ |* 1 | HASH JOIN | | 1 | 5 (40)| 26000 |00:00:00.37 | 176 | 808K (0)| | 2 | SORT UNIQUE | | 1 | 1 (0)| 4 |00:00:00.01 | 1 | 2048 (0)| | 3 | INDEX FULL SCAN | PK_DEPT | 1 | 1 (0)| 4 |00:00:00.01 | 1 | | |* 4 | TABLE ACCESS FULL| BIG_EMP | 1 | 2 (0)| 26000 |00:00:00.31 | 175 | | ------------------------------------------------------------------------------------------------------ 처음 예제와 조인순서와 Scan 한 블럭수및 Hash area size 사용량은 대동소이 하지만 Subquery Unnesting 이 발생하여 불필요한 Sort 가 발생 하였다. 위의 SQL 의 경우 Subquery Unnesting 은 메인쿼리의 결과집합을 보존하기 위하여 Sort Unique 혹은 Hash Unique 작업이 추가적으로 발생된다. Subquery Unnesting 이 항상 나쁜것은 아니지만 대용량 집합간의 조인시는 엄청난 부담이 될수 밖에 없다. 서브쿼리쪽은 Sort Unique 작업이 추가적으로 필요하기 때문이다. 그렇다고 덩치가 큰 BIG_EMP를 선행테이블로 할수도 없는것이다. 이것이 바로 Hash Join Right Semi 가 10g 에서 나타난 이유이다. 그렇다면 이럴 경우에 강제로 Hash Join Right Semi 를 발생시키려면 어떻게 해야 되겠는가? 이럴때 간단하게 사용할수 있는것이 QB_NAME 을 이용한 Global 힌트와 USE_HASH 및 SWAP_JOIN_INPUT 힌트이다. 아래의 스크립트를 보자.
select /*+ gather_plan_statistics LEADING(A) USE_HASH(@sub B) SWAP_JOIN_INPUTS(@sub B) */ a.empno, a.sal from big_emp a where exists (select /*+ qb_name(sub) */ b.deptno from dept b where b.deptno = a.deptno ); --------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | Cost (%CPU)| A-Rows | A-Time | Buffers | Used-Mem | --------------------------------------------------------------------------------------------------------- |* 1 | HASH JOIN RIGHT SEMI| | 1 | 4 (25)| 26000 |00:00:01.05 | 176 | 813K (0)| | 2 | INDEX FULL SCAN | PK_DEPT | 1 | 1 (0)| 4 |00:00:00.01 | 1 | | |* 3 | TABLE ACCESS FULL | BIG_EMP | 1 | 2 (0)| 26000 |00:00:00.99 | 175 | | ---------------------------------------------------------------------------------------------------------
다시 정상적인 Hash Join Right Semi 로 돌아왔다. 간단히 힌트를 설명하자면 QB_NAME 은 쿼리블럭명을 강제로 지정하는 힌트이고 Swap_join_inputs 힌트는 Probe 쪽 집합(후행 집합) 을 강제로 Build Input 집합(선행집합) 으로 바꾸는 힌트이다. 그리고 Use_hash 힌트에 대하여 한마디 하자면 원래 Use_hash 힌트는 후행 집합에 대해서만 사용하는 힌트이다. 하지만 USE_HASH(A B) 이런식으로 사용해도 ORACLE 이 힌트를 아래처럼 변환시켜버린다. USE_HASH(A B) --> LEADING(A B) USE_HASH(B) 오라클사에서 명시적인 용어가 없기 때문에 필자는 이것을 Internal Hint Transformation 이라 부른다. 다음에 기회가 되면 Internal Hint Transformation 에 대하여 글을 올릴까 한다.
결론 : 10g 부터 나온 Hash Join Right (Semi/Anti/Outer) 기능을 적재적소에 활용하면 대용량 집합간의 join 성능을 획기적으로 향상시킬수 있다.
참고로 Hash Join Right Anti Plan 으로 유도하는 것은 Exists 대신 Not Exists 로 바꾸면 된다. Hash Join Right Outer 를 유도하는 예제는 아래와 같다.
select /*+ LEADING(A) USE_HASH(B) SWAP_JOIN_INPUTS(B) */ a.empno, a.sal from big_emp a, dept b where a.deptno = b.deptno(+)
|
[본문링크] Hash Join Right (Semi/Anti/Outer) 의 용도
|
[1]
|
|
|
|
|
코멘트(이글의 트랙백 주소:/cafe/tb_receive.php?no=31520 |
|
|
|
|
|
|
|
|
|
Copyright byCopyright ⓒ2005, SSISO Community All Rights Reserved.
|
|
|