비효율적인 PL/SQL(Function)을 제거하여 성능을 높인 쿼리 예제를 살펴보도록 하겠습니다. 이 포스트에 사용된 예제는 부하를 유발하는 PL/SQL를 보여주기 위해 임의로 작성된 쿼리입니다. 최대한 가독성을 높이기 위해 짧게 작성하고자 노력했으며, 이로 인하여 조금은 억지스러운 면도 있지만 설명을 위한 예제이니 참고 바랍니다.
[ 튜닝 전 ]
먼저 튜닝 전 쿼리를 살펴보도록 하겠습니다.
SELECT SALE_DT
, PROD_NO
, FNC_GET_PROD_NM(PROD_NO) AS PROD_NM
FROM PROD_SALE
WHERE SALE_DT = '20191201';
PROD_SALE 테이블 데이터 중 SALE_DT 값이 '20191201'인 데이터의 SALE_DT, PROD_NO, PROD_NM 값을 추출하고 있습니다. 이 중 PROD_NM 값은 FNC_GET_PROD_NM 함수를 호출한 결과 값을 출력합니다.
위 쿼리에서 사용된 함수 FNC_GET_PROD_NM의 내용은 아래와 같습니다.
CREATE OR REPLACE FUNCTION FNC_GET_PROD_NM (V_PRODNO IN NUMBER)
RETURN VARCHAR2
IS
V_PROD_NM VARCHAR2(100);
V_PROD_STTS_CD VARCHAR2(10);
V_RES_NM VARCHAR2(100);
BEGIN
SELECT PROD_NM, PROD_STTS_CD
INTO V_PROD_NM, V_PROD_STTS_CD
FROM PROD
WHERE PROD_NO = V_PRODNO ;
IF V_PROD_STTS_CD = '10' THEN
V_RES_NM := '[판매중] ' || V_PROD_NM;
ELSIF V_PROD_STTS_CD IN ('30','40') THEN
V_RES_NM := '[판매종료] ' || V_PROD_NM;
ELSIF V_PROD_STTS_CD IN ('50','60') THEN
V_RES_NM := '[판매예정] ' || V_PROD_NM;
ELSE
V_RES_NM := '[기타] ' || V_PROD_NM;
END IF;
RETURN V_RES_NM;
END;
FNC_GET_PROD_NM 함수는 입력받은 인자 값 V_PRODNO에 매칭 되는 상품명(더 정확하는 상품 상태 값과 상품명)을 반환하는 함수입니다. PROD 테이블에서 PROD_NO 컬럼의 값이 입력받은 인자 값 V_PRODNO와 일치되는 데이터의 PROD_NM과 PROD_STTS_CD 값을 추출하여 V_PROD_NM과 V_PROD_STTS_CD 변수에 각각 입력합니다. IF문을 이용해 V_PROD_STTS_CD 값에 따라 상품상태값과 상품명(V_PROD_NM)을 합친 문자열을 V_RES_NM에 대입하고, 마지막에 V_RES_NM 값을 반환하게 됩니다.
위 쿼리의 AUTOTRACE 결과를 살펴보겠습니다.
AUTOTRACE의 Statistics를 보면 recursive calls이 과도하게 발생한 것을 확인할 수 있습니다. recursive calls는 오라클 내부적으로 필요한 작업이나 SQL문을 수행할 때 발생합니다. 예를 들어, 데이터를 Insert시 공간이 부족하면 recursive calls로 공간을 동적으로할당하는 작업을 하게 됩니다. 여기서는 PL/SQL 내의 SQL문을 수행을 위해 recursive calls를 호출하였습니다.
SELECT절에 사용된 PL/SQL의 실행 횟수는 SELECT 되는 데이터의 건수와 비슷합니다. SELECT되는 각 로우의 값을 입력 인자 값으로 해서 PL/SQL을 수행하게 됩니다. PL/SQL 결과가 캐시에 저장되어 있다면 PL/SQL 수행 없이 캐쉬 내 결과 값을 반환하게 되므로 항상 SELECT 되는 데이터 건수와 동일한 것은 아닙니다.
PROD_SALE 테이블에서 SALE_DT 값이 '20191201'인 데이터는 251,896건입니다. 251,896건의 recursive calls이 발생했으니 데이터 건수만큼 함수가 호출되었음을 확인할 수 있습니다. 여기서 가장 큰 문제는 FNC_GET_PROD_NM 함수를 호출할 때마다 함수 내부의 쿼리문을 반복해서 실행하게 된다는 것입니다. SQL 최적화를 위해서는 블럭 액세스를 줄여야 하는데, 함수내 쿼리문을 실행하기 위해 PROD 테이블을 여러 번 액세스하게되어 블럭 액세스(consistent gets)가 과도하게 증가하였습니다.
위 쿼리를 최적화하기 위해서는 과도한 블럭 액세스를 유발하는 함수를 제거해야 합니다.
[ 튜닝 후 ]
아래는 위 튜닝 전 쿼리에서 함수 제거를 위해 함수 내 PROD 테이블 조회를 조인으로 풀어낸 쿼리입니다.
SELECT S.SALE_DT
, S.PROD_NO
, CASE WHEN PROD_STTS_CD = '10' THEN '[판매중] ' || P.PROD_NM
WHEN PROD_STTS_CD IN ('30','40') THEN '[판매중] ' || P.PROD_NM
WHEN PROD_STTS_CD IN ('50','60') THEN '[판매중] ' || P.PROD_NM
ELSE '[기타] ' || P.PROD_NM END AS PROD_NM
FROM PROD_SALE S
, PROD P
WHERE S.PROD_NO = P.PROD_NO
AND SALE_DT = '20191201'
;
PROD_SALE 테이블의 데이터는 튜닝 전 쿼리처럼 SALE_DT 값이 '20191201'인 데이터만 추출합니다. 그리고 FNC_GET_PROD_NM함수 대신에 ROD_SALE 테이블과 PROD 테이블을 PROD_NO 키를 이용해 조인을 하고 있습니다. SELECT절의 PROD_NM 항목은 함수 내 연산처럼 PROD_STTS_CD 값에 따라 상태 값을 CASE문을 이용해 분기하여 PROD_STTS_CD에 따른 상품상태값과 상품명(PROD_NM)을 합친 결과로 출력됩니다.
실행계획을 보면 PROD_SALE 테이블과 PROD 테이블을 FULL TABLE SCAN 하여 HASH 조인했음을 알 수 있습니다. 두 테이블 중 크기가 상대적으로 작은 PROD 테이블이 BUILD 테이블이 되어 해쉬 테이블을 만들고 PROD_SALE 테이블이 PROBE 테이블이 되어 해시 조인했음을 확인할 수 있습니다.
해시 조인을 수행하였기 때문에 튜닝 후 쿼리에서는 PROD 테이블을 한 번만 액세스 하였습니다. 튜닝 전 쿼리에서는 함수 실행을 위해 SELECT 건수만큼 PROD 테이블을 액세스 하여 과도한 블럭 액세스를 유발하였는데, 쿼리를 조인으로 풀어 이러한 과도한 블럭 액세스를 제거한 것입니다.
SELECT CNT1, CNT2, CNT3, CNT4, CNT5, CNT6, CNT7, CNT8
FROM (
SELECT (SELECT COUNT(*) FROM CHG_LOGS WHERE REG_DT=:B1 AND ST_CD='0' AND LOG_LVL='1') CNT1
, (SELECT COUNT(*) FROM CHG_LOGS WHERE REG_DT=:B1 AND ST_CD='1' AND LOG_LVL='1') CNT2
, (SELECT COUNT(*) FROM CHG_LOGS WHERE REG_DT=:B1 AND ST_CD='2' AND LOG_LVL='1') CNT3
, (SELECT COUNT(*) FROM CHG_LOGS WHERE REG_DT=:B1 AND ST_CD='3' AND LOG_LVL='1') CNT4
, (SELECT COUNT(*) FROM CHG_LOGS WHERE REG_DT=:B1 AND ST_CD='4' AND LOG_LVL='1') CNT5
, (SELECT COUNT(*) FROM CHG_LOGS WHERE REG_DT=:B1 AND ST_CD='5' AND LOG_LVL='1') CNT6
, (SELECT COUNT(*) FROM CHG_LOGS WHERE REG_DT=:B1 AND ST_CD='6' AND LOG_LVL='1') CNT7
, (SELECT COUNT(*) FROM CHG_LOGS WHERE REG_DT=:B1 AND ST_CD='7' AND LOG_LVL='1') CNT8
FROM DUAL
) A;
CHG_LOGS 테이블의 조건별 건수를 출력하는 쿼리입니다. ST_CD 값 별 데이터 건수를 계산하기 위해 스칼라 서브 쿼리 안에서 CHG_LOGS 테이블을 액세스하고 있습니다. 문제는 유사한 스칼라 서브 쿼리가 총 8개가 존재한다는 점입니다. 다시 말해서, CHG_LOGS 테이블을 8번 중복해서 액세스 한다는 뜻입니다.
위 쿼리의 AUTOTRACE 결과를 확인해보겠습니다.
위 쿼리의 실행계획을 살펴보면 아래와 같은 패턴(인덱스 스캔 및 테이블 랜덤 액세스)을 8번 반복해서 수행한 것을 확인할 수 있습니다.
위와 같은 반복적인 액세스 작업 때문에 과도한 블럭 읽기(109,718블럭)가 발생하고 이로 인하여 쿼리 수행 시간도 길어지게 되었습니다.
그럼, 어떤 방식으로 중복 액세스를 제거하여 쿼리 성능을 높일 수 있는지 확인해보도록 하겠습니다.
[ 튜닝 후 ]
아래는 튜닝 후 쿼리입니다.
SELECT NVL( SUM( CASE WHEN ST_CD='0' THEN 1 ELSE 0 END), 0) AS CNT1
, NVL( SUM( CASE WHEN ST_CD='1' THEN 1 ELSE 0 END), 0) AS CNT2
, NVL( SUM( CASE WHEN ST_CD='2' THEN 1 ELSE 0 END), 0) AS CNT3
, NVL( SUM( CASE WHEN ST_CD='3' THEN 1 ELSE 0 END), 0) AS CNT4
, NVL( SUM( CASE WHEN ST_CD='4' THEN 1 ELSE 0 END), 0) AS CNT5
, NVL( SUM( CASE WHEN ST_CD='5' THEN 1 ELSE 0 END), 0) AS CNT6
, NVL( SUM( CASE WHEN ST_CD='6' THEN 1 ELSE 0 END), 0) AS CNT7
, NVL( SUM( CASE WHEN ST_CD='7' THEN 1 ELSE 0 END), 0) AS CNT8
FROM CHG_LOGS A
WHERE REG_DT=:B1
AND LOG_LVL='1';
중복 액세스를 피하기 위해 스칼라 서브 쿼리를 제거하였습니다. CHG_LOGS 테이블은 한 번만 액세스 하도록 변경하였으며, WHERE 절에는 각 스칼라 서브 쿼리에서 동일하게 적용되었던 조건(REG_DT=:B1 AND LOG_LVL='1')을 입력하였습니다. 그리고 CASE문을 이용하여 ST_CD 별 데이터 건수를 계산하였습니다.
위와 같이 변경된 쿼리의 AUTOTRACE 결과는 아래와 같습니다.
실행계획을 살펴보면 IX_CHG_LOG 인덱스와 CHG_LOGS 테이블을 한번씩 액세스하고 있는 것을 확인하실 수 있습니다. 액세스 중복이 제거되어 실행시간(0.02초)과 블럭 읽기(13,714블럭)가 감소된 것을 확인하실 수 있을 것입니다. 물론, 중복 제거의 방법으로 함수를 사용하여 CPU의 사용량이 증가하게 됩니다. 하지만, 쿼리에서 가장 큰 부하는 블럭 읽기이기 때문에 부하 측면에서 CPU 사용량은 상대적으로 낮은 비용이라고 판단할 수 있습니다.
지금까지 살펴봤던 SQL 튜닝 방법이 집합적 사고의 한 예라고 볼 수 있습니다. 데이터 블럭 하나하나가 퍼즐 조각이며, 각 퍼즐 조각을 최대한 적게 활용하여 멋진 작품인 결과물을 만들어내는 게 집합적 사고에 의한 쿼리문 작성인 거죠. 많은 분들이 시간적 여유를 갖고 적은 퍼즐 조각으로 멋진 작품을 탄생시키는 기쁨을 느껴보셨으면 좋겠습니다.
테이블 조인 방식은 테이블의 각 로우를 매핑 시 어떤 메커니즘을 사용하느냐에 따라 조인을 구분하는 겁니다.
데이터 셋의 건수 등을 기반으로 옵티마이져가적합한 조인 메커니즘을 선택하여 실행 계획을 생성하게 됩니다. 이때, 테이블 통계정보의 오류 등으로 인하여 적합하지 않은 조인 방식이 선택될 수도 있습니다. 잘못 선택된 조인 방식은 때때로 성능상의 부하를 유발하게 됩니다. 따라서, 각 조인 방식에 대한메카니즘을파악하여 조회 쿼리에 적합한 조인 방식이 선택되도록 유도하는 것이 데이터베이스 성능 향상을 위한 중요한 방법 중의 하나가 되겠습니다.
오라클에서 제공하는 조인 방식(Join methods)은 아래와 같습니다.
Join Methods
1. NL(Nested Loops) 조인
Nested Loops 조인(이하 NL 조인)을 한국어로 직역하면 '중첩 반복 조인'입니다. 뜻 그대로 거듭된 반복 작업으로 조인을 수행하는 방식이 바로 NL 조인입니다.
NL 조인은드라이빙 테이블*의 한 로우와 매칭되는 드리븐 테이블**내 로우를 찾기위해 드리븐 테이블의 모든 데이터를 탐색하고 조인 컬럼 값이 일치하는 경우 양쪽 테이블의 두 로우를 연결합니다. 드라이빙 테이블에서 조회 조건에 부합하는 로우가 여러 건인 경우에는 로우 수만큼 위 작업을 반복적으로 수행하여 조인을 완료하게 됩니다.
* 드라이빙 테이블(Driving table) : 조인 대상 테이블 중 먼저 액세스하는 테이블을 말하며 Outer table이라고도 표현 가능합니다. ** 드리븐 테이블(Driven-to table) : 조인 대상 테이블 중 나중에 액세스하는 테이블을 말하며 Inner table이라고도 표현 가능합니다.
1.1. NL 조인 작동 방식
오라클 문서에서 슈도(pseudo) 코드로 표현한 NL 조인 로직은 아래와 같습니다. NL 조인과 중첩된 For loop 수행이 개념적으로 동일한 로직이라고 설명하고 있습니다.
우선 드라이빙 테이블(위 로직에서는 employees 테이블)에서 조건에 맞는 (where X=Y) 로우를(바깥쪽, Outer)For문을 이용해 모두 추출하게 됩니다. 이때, 드라이빙 테이블에서 로우가 추출될때마다(안쪽, Inner) For문을 수행하게 됩니다. For문을 수행하면서 드리븐 테이블(위 로직에서는 departments 테이블)의 로우를 탐색하는데, 드라이빙 테이블의 조인 컬럼 값과매칭 된다면output에 조인 결과를 저장하게 됩니다.
위 로직을 그림으로 표현하면 아래와 같습니다.
Nested Loops Join
위에서 설명했듯이 NL 조인은 드라이빙 테이블에서 추출되는 데이터 건수만큼 드리븐 테이블을 반복해서 탐색하게 됩니다. 드라이빙 테이블에서 10건의 로우가 추출되면 드리븐 테이블을 10번 반복해서 액세스를 하게되고, 10만 건의 로우가 추출되면 드리븐 테이블을 10만 번 반복해서 액세스를 하게 됩니다.
이와 같은 NL 조인의 특성 때문에 아래 조건을 충족해야만 NL 조인 시 좋은 성능을 보장 받을 수 있습니다.
1) 조인 테이블 중 추출 건수가 적은 테이블을 드라이빙 테이블로 선정. 드라이빙 테이블의 추출 건수가 곧 드리븐 테이블의 액세스 반복 횟수가 되므로 건수가 더 적은 테이블이 드라이빙 테이블로 선정되어야 합니다.
2) 드리븐 테이블에 조인 조건 컬럼으로 구성된 인덱스가 존재. 드리븐 테이블은 드라이빙 테이블의 추출 건수만큼 반복해서 액세스하게 됩니다. 이때 조인 컬럼으로 구성된 인덱스가 존재한다면 드리븐 테이블의 모든 데이터를 액세스 하지 않아도 인덱스를 이용해 조인 조건에 맞는 로우를 추출할 수 있게 됩니다. 만약 조인 컬럼으로 구성된 인덱스가 존재하지 않는다면 Full table scan이 반복적으로 발생하여 성능상 이슈가 발생할 수 있습니다.
1.2. NL 조인 실행 계획
아래 쿼리는 EMPLOYEES 테이블과 DEPARTMENTS 테이블을 조인하는 쿼리 예제입니다. 두 테이블의 조인 컬럼은 DEPARTMENT_ID이며, EMPOLYEES 테이블의 LAST_NAME이 'A'로 시작하는 데이터만 추출하고자 합니다. EMPLOYEES 테이블에서 LAST_NAME이 'A'로 시작하는 데이터는 4건이며, DEPARTMENTS 테이블의 데이터 건수는 27건입니다.
SELECT E.EMPLOYEE_ID
, E.FIRST_NAME
, E.LAST_NAME
, D.DEPARTMENT_NAME
FROM EMPLOYEES E
, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND E.LAST_NAME LIKE 'A%' ;
위 쿼리의 실행 계획은 아래와 같습니다.
1) 1차 Nested Loops ( Id = 2 ) 1-1)EMP_NAME_IX 인덱스를 탐색하여 "E.LAST_NAME LIKE 'A%'" 조건에 부합하는 rowid를 취득합니다. (Id = 4) 1-2) 1-1)에서 얻은 rowid로EMPLOYEES 테이블을 액세스하여 EMPLOYEE_ID와 DEPARTMENT_ID값을 취득합니다. (Id = 3) 1-3) 1-2)의 데이터가 NL조인의 Outer가 됩니다. For문처럼 Outer의 로우를 하나씩 인출하여DEPT_ID_PK 인덱스를 탐색하고 DEPATMENT_ID 값과 매칭 되는 rowid를 취득합니다.EMPLOYEES 테이블과DEPARTMENTS 테이블의 인덱스와 NL조인을 하게 됩니다.(id = 5,1)
2) 2차 Nested Loops ( Id = 1 ) 2-1) 1-3)의 데이터가 NL 조인의 Outer가 됩니다. For문처럼 Outer의 로우를 하나씩 인출하여 rowid로 DEPARTMENTS 테이블에 액세스하여 DEPARTMENT_NAME 값을 취득합니다. (Id = 6,1)
2. 해시(Hash) 조인
해시 조인은 조인 키 컬럼 값의 해시 함수 결과를 이용하여 조인하는 방식입니다. NL 조인과는 상대적으로 큰 데이터 셋을 조인하기 위한 방식입니다.
2.1. Hash 조인 작동 방식
해시 조인에서도 조인 대상 테이블 중 크기가 더 작은 테이블부터 액세스 합니다. 해시 조인에서는 먼저 액세스 하는 테이블을 빌드(Build) 테이블, 나중에 액세스 하는 테이블은 프로브(Probe) 테이블이라고 부릅니다.
①빌드 테이블을 읽어 각 로우의 조인 키 컬럼 값을 해시 함수에 대입합니다. ② 해시 함수의 결과 값을 이용해 메모리(PGA)에 해시 테이블을 생성합니다. 해시 함수 결과 값은 해시 테이블에서 각 로우가 저장된 슬롯의 위치를 나타냅니다. 만약, 조인 키 컬럼 값이 상이하지만 동일한 해시 함수 결과 값이 나온 로우는 동일한 슬롯(Slot)에 저장되며 각 데이터는 링크드 리스트로 연결됩니다.
Hash join
③ 빌드 테이블의 모든 로우가 해시 테이블에 저장이 완료되면, 프로브 테이블의 데이터를 한 로우씩 읽기 시작합니다. 프로브 테이블도 빌드 테이블과 마찬가지로 조인 키 컬럼 값을②에서 사용된동일한 해시 함수에 입력하여 결과 값을 계산합니다. ④ 계산된 해시 함수 결과 값을 이용해 해시 테이블 내 관련된(해시 결과 값이 동일한) 슬롯을 찾아갑니다. 만약, 해시 결과 값이 동일한 슬롯이 없다면 조인에 실패한 로우이므로 프로브 테이블의 다음 로우를 읽어 조인 연산을 계속 진행합니다. ⑤ 해시 결과 값으로 관련 슬롯을 찾았으면 슬롯 안에 저장된 빌드 테이블의 로우를 확인합니다. 로우의 조인 컬럼 값이 프로브 테이블 로우의 조인 컬럼 값과 일치하면 두 로우를 매핑하여 결과 셋(Result set)에 저장합니다. 슬롯 내에는 동일한 조인 컬럼 값을 가진 로우가 여러 건 존재할 수 있으므로 슬롯 내 링크드 리스트를 끝까지 탐색하여 조인을 완료합니다.
해시 조인에서는 아래 조건을 충족하는 경우 좋은 성능을 보장 받을 수 있습니다.
1) 조인 테이블 중 추출 건수가 적은 테이블을 빌드 테이블로 선정. 조인을 위해 빌드 테이블의 데이터는 PGA 메모리의 해시 테이블에 저장됩니다. 해시 테이블의 크기가 커서PGA 메모리가부족해지면 가상 메모리를 사용하기 떄문에 DISK I/O가 발생하게 됩니다. 따라서, 데이터 건수가 적은 테이블이 빌드 테이블로 선정되어야 하며, 쿼리문 상에 결과 셋에 저장될 컬럼명만 명시하여 해시 테이블의 크기를 최소화해야 합니다.
2) 프로브 테이블은 Full table scan이 유리. 일반적으로 해시 조인에서 프로브 테이블은 대용량의 테이블입니다. 만약 인덱스를 이용해 프로브 테이블을 액세스하면 테이블 랜덤 액세스가 과도하게 발생하여 블록 I/O가 증가하게 됩니다. 테이블 액세스 없이 INDEX SCAN만으로 데이터 추출이 가능한 경우는 제외하고, 되도록이면 인덱스 스캔 없이 테이블만 스캔하여 블럭 I/O를 줄여야 합니다.
참고로, 오라클 문서에서 슈도(pseudo) 코드로 표현한 해시 조인 로직은 아래와 같습니다.
아래 쿼리는 PROD 테이블과 PROD_SALE 테이블을 조인하는 쿼리 예제이며, 조인 컬럼은 PROD_NO입니다.PROD 테이블의 전체 테이블 건수는71,734건입니다. PROD_SALE 테이블은 SALE_DT 컬럼으로 파티셔닝 되어 있으며, SALE_DT = '20191231'인 데이터 건수는287,290건으로 대용량 테이블입니다.
SELECT S.SALE_DT
, P.PROD_NM
, S.PROD_QTY
FROM PROD P
, PROD_SALE S
WHERE P.PROD_NO = S.PROD_NO
AND S.SALE_DT = '20191231' ;
위 쿼리의 실행 계획은 아래와 같습니다.
1) 두 테이블 중에서 크기가 작은 PROD 테이블이 빌드 테이블이 되며, FULL TABLE SCAN을 하게 됩니다. PROD 테이블의 각 ROW를 읽으면서 조인 칼럼의 값인 PROD_NO 값을 해시 함수에 대입한 결과 값으로 해시 테이블을 메모리에 생성합니다. (Id = 2)
2) 해시 테이블 생성이 완료되면 프로브 테이블인 PROD_SALE 테이블을 FULL TABLE SCAN하기 시작합니다.(Id = 4)PROD_SALE테이블은Where 절의 파티션 컬럼 필터링 조건으로 인하여 하나의 파티션만 액세스 하게 됩니다.(Id = 3)
3)PROD_SALE테이블의 로우를 하나씩 읽으면서 조인 컬럼 값인 PROD_NO 값을 해시 함수에 대입한 결과 값으로 해시 테이블 내 관련 슬롯을 찾아가게 됩니다. 슬롯에 저장된 빌드 테이블의 데이터와 조인 키 칼럼 값을 비교하면서 컬럼 값이 일치하는 데이터와 매핑하여 조인을 완료하게 됩니다. (Id = 1)
3. 소트 머지(Sort Merge) 조인
소트 머지 조인은 조인 대상 테이블 데이터의 조인 키 컬럼 값을 기준으로 각 데이터 셋을 정렬한 후 작은 값부터 양쪽의 조인 키 컬럼 값이 매칭 되는지 확인하여 조인하는 방법입니다.
오라클의 옵티마이저는 조인 조건이 동등 조건(=)이 아니거나(해시 조인은 동등 조건만 가능) 인덱스 사용으로 정렬 작업을 생략 할 수 있는 경우에 해시 조인 대신 소트 머지 조인을 선택합니다. 또한, 해시 조인에서 해시 테이블이 너무 커서 PGA 메모리 부족으로 가상 메모리(디스크)를 사용하게 되면 디스크 I/O가 빈번하게 발생하게 됩니다. 프로브 테이블의 조인 키 칼럼 값은 정렬된 상태가 아니기 때문에 해시 테이블의 블록을 여러 번 읽어야 할 수도 있습니다. 하지만 소트 머지 조인은 조인 키 컬럼 값이 정렬된 상태이기 때문에 가상 디스크를 사용하더라도 디스크 I/O 횟수가 해시에 비해 훨씬 적다는 장점이 있습니다.
3.1.Sort Merge조인 작동 방식
소트 머지 조인을 수행하기 위해서 각 조인 대상 테이블은 조인 키 컬럼 기준으로 정렬되어 메모리(PGA)에 저장됩니다.
정렬된 첫 번째 데이터 셋에서 첫 번째 로우를 읽어 조인 키 컬럼 값이 두 번째 데이터 셋에 존재하는지 확인합니다. 만약, 매칭되는 조인 키 컬럼 값이 두 번째 데이터 셋에 존재하지 않는다면 첫 번째 데이터 셋을 순차적으로 읽어 먼저 읽은 로우의 칼럼 값 보다 큰 로우의 컬럼 값을 찾습니다.
위 그림에서 첫 번째 데이터 셋의 '10'은 두 번째 데이터 셋의 첫 번째 값인 '20'보다 작은 값이므로 매칭되는 로우가 없으므로 첫 번째 데이터 셋의 '10' 다음의 값인 '20'을 읽게 됩니다.
두 번째 테이블에서 아직 탐색이 진행되지 않았으므로 첫 번째 컬럼 값부터 데이터를 읽습니다. 이 값이 첫 번째 데이터 셋에서 읽은 값 '20'과 동일하다면 해당 값보다 큰 데이터가 나올 때까지 두 번째 데이터 셋을 읽어 나가면서 매칭 된 데이터를 결과 셋에 저장하게 됩니다. 두 번째 테이블에서 '20'보다 큰 값을 발견하면 해당 로우의 위치를 기억해놓고 첫 번째 테이블로 돌아가 다음 로우를 읽습니다. 로우 값 '30'을 읽어 두 번째 테이블에서 마지막 탐색 위치의 값과 비교합니다. 두 번째 테이블의 값도 '30'이므로 두 데이터를 결과 셋에 저장합니다. 마찬가지로 두 번째 테이블에서의 탐색을 계속 진행하는데 다음 로우의 값은 '30'보다 크므로 탐색을 중지하고 다시 첫 번째 데이터셋으로 돌아가 다음 로우를 읽습니다.
첫 번째 데이터 셋의 다음 로우의 값 '40'은 두 번째 데이터 셋의 마지막 탐색 로우의 값인 '50'보다 작으므로 매칭 되는 로우가 없는 것입니다. 다시 첫 번째 데이터 셋의 다음 로우인 '50'을 읽습니다.두 번째 데이터 셋의 마지막 탐색 로우의 값인 '50'과 동일하므로 두 번째 데이터 셋을 계속 탐색하면서 매칭되는 데이터를 결과 셋에 저장하게 됩니다. 두 데이터 셋의 탐색이 종료되면 조인 작업이 완료된 것입니다.
참고로, 오라클 문서에서 슈도(pseudo) 코드로 표현한 소트 머지 조인 로직은 아래와 같습니다.
아래 쿼리는 EMPLOYEES 테이블과 DEPARTMENTS 테이블을DEPARTMENT_ID 조인 키 컬럼 기준으로조인하는 쿼리 예제입니다. 두 테이블을 인덱스 사용 없이 Full table scan을 하기 위해 NO_INDEX 힌트를 사용하였으며, 소트 머지 조인을 하기 위해 USE_MERGE 힌트를 사용하였습니다.
SELECT /*+ USE_MERGE(d e) NO_INDEX(e) NO_INDEX(d) */
D.DEPARTMENT_NAME
, E.FIRST_NAME
, E.LAST_NAME
FROM EMPLOYEES E
, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID ;
위 쿼리의 실행 계획은 아래와 같습니다.
1) 첫 번째 테이블 DEPARTMENTS 테이블을 Full table scan 하여 조인 키 컬럼 기준으로 정렬하는 Sort join 작업을 진행합니다. ( Id = 3,2 )
2) 두 번째 테이블 EMPLOYEES 테이블도 Full table scan하여 조인 키 컬럼 기준으로 정렬하는 Sort join 작업을 진행합니다. ( Id = 5,4 )
3) 정렬된 키 컬럼 값을 순차적으로 읽어 두 테이블 양쪽 모두에 조인 컬럼 키 값이 존재하면 매핑하여 데이터 결과 셋에 추가합니다. ( Id = 1 )
이상으로 조인 방식에 대해 설명드렸습니다.
참고로 데이터베이스에서 조인을 구분하는 방법은 위에서 언급한 조인 방식 외에 조인 종류도 존재합니다. 우리가 쿼리 작성 시 사용하는 Inner join, Outer join 등의 문법으로 구분하는 조인 방법입니다. (깊게 살펴보면 한 쪽 테이블에만 존재하는 데이터 출력 방법이라고 설명할 수 있습니다.)
오라클 홈페이지 상단 로고 옆에 메뉴 버튼을 클릭해보면 위와 같이 오라클에서 제공하는 다양한 서비스들을 확인할 수 있을 것입니다.
오라클 데이터베이스가 국내 데이터베이스 점유 시장 1위인 관계로 많은 분들이 오라클 사가 단지 데이터베이스 회사로만 알고 있을 수 있습니다. 하지만, 위 메뉴에서 확인되는 것처럼 오라클은 데이터베이스 외에도 다양한 솔루션을 개발하여(물론 자바와 MySQL처럼 인수 합병에 의해 자사 솔루션으로 종속시킨 경우도 있긴합니다.) 시장에 선보이고 있습니다.
오라클도 역시시대 흐름에 따라 클라우드 플랫폼 전쟁에 본격적으로 참여한 상태입니다. 후발주자로 타 플랫폼을 따라잡기 위해 가성비를 내세우며 클라우드 플랫폼 마케팅을 진행하고 있는 상황입니다. 공식 홈페이지 메뉴 구성에서만 봐더라도 그동안의 주력 상품이었던 On-Premise 오라클 데이터베이스가 메인 자리를 Cloud 플랫폼에 내어주게되었네요. (뭐 이정도는 당연지사로 느껴질 수 있겠죠.)
아무튼, 이 포스팅에서 우리는 데스크탑에서 테스트 용으로 사용 할 데이터베이스를 설치할 계획이니, 위 메뉴에서 빨간색으로 표시된 'On-Premise Infrastructure' 아래에 'Oracle Database'를 선택하면 됩니다.
참고로 On-Premise Infrastrcture는 사용자 소유의 서버나 데스크탑에 설치하여 사용하는 환경을 의미합니다. 아마존 AWS나 마이크로소프트 Azure와 같은 클라우드 환경과는 상반되는 의미라고 생각하면 되겠습니다.
오라클 메인 화면에서 'Oracle database'를 클릭하면 위와 같이 다운로드 웹페이지로 연결되는 'Download Oracle Database 19c' 버튼을 확인 할 수 있을 것입니다. 위 버튼을 클릭하면 아래와 같이 버전 및 OS 환경별로 오라클을 다운로드 받을 수 있는 페이지가 표시 됩니다.
위 웹페이지에 접속하여 본인 OS 환경에 맞는 설치파일을 다운로드 받으면 됩니다. 이 포스트에서는 Windows 10 환경에 오라클을 설치 할 계획이므로 'Microsoft Windows x64(64-bit)'를 선택하겠습니다.
참고로, 오라클은 2018년부터 오라클 네이밍 방식이 변경되었습니다. 12c까지는 메이저 버전을 사용하였는데요, 2018년도부터는 출시 년도를 사용하는 명명법으로 바뀌게 되었다고 합니다.
아직 20c 버전의 출시 예정일은 공지되지 않은 상태이고요. 현재 최신 버전은 작년에 선보인 19c입니다.
그리고 '19c'처럼 숫자 뒤에 붙쳐진 알파벳에도 의미가 존재합니다. 19c에 사용된 'c'는 Clould의 약자입니다. 12c 버전부터 'c'를 사용하기 시작했는데요. 새로운 메이저 버전에 적용된 클라우드 컴퓨팅 기술을 표현하는 방식이라고 이해하시면 되겠습니다. 이런 네이밍 방식이 적용된 것은 오라클 8i (8.1.5.0) 버전부터였는데요. 8i에 사용된 'i'는 Internet을 뜻합니다. 메이저 버전인 8 뒤에 'i'를 붙여 인터넷 환경을 제공하는 데이터베이스라는 의미를 버전을 통해 표현을 한 것입니다. 메이저 버전이 10으로 업그레이드되면서 숫자 뒤에 붙여지는 알파벳이 'g'로 변경되었습니다. 'g'는 'Grid'를 뜻하고요. 마찬가지로 그리드 컴퓨팅 기술을 적용한 데이터베이스라고 생각하시면 됩니다.
다시 본론으로 돌아와서 설치파일 다운로드를 계속 진행해보겠습니다.
다운로드 버튼을 클릭하면 아래와 같이 라이센스 정책 동의에 관한 창이 뜨게 됩니다. 오라클 라이센스 정책을 확인 후 동의한다는 체크 박스를 클릭하고 Download 버튼을 클릭하면 본격적으로 설치 파일이 로컬에 다운로드 됩니다. 이 때, 오라클 계정으로 로그인 되어 있어야만 다운로드가 가능합니다.
2. 오라클 소프트웨어 설치
다운로드한 파일을 압축 해제하면 아래와 같은 설치 파일을 볼 수 있을 것입니다.
setup.exe 파일을 실행하면 아래와 같이 설치 다이얼로그가 나타날 것입니다.
첫 화면에서는 두 가지 설치 옵션을 확인할 수 있습니다.
- 단일 인스턴스 데이터베이스 생성 및 구성 : 다이얼로그에서의 설명처럼 소프트웨어 설치 뿐만 아니라 데이터베이스 생성까지 한번에 진행하게 됩니다. - 소프트웨어만 설정 : 우선 소프트웨어만 설치합니다. 데이터베이스 생성은 추후에 DBCA(Database Configuration Assistant) 프로그램을 실행하여 작업을 할 수 있습니다.
여기서는 DBCA를 사용하여 데이터베이스를 생성 할 계획이므로 '소프트웨어만 설정' 옵션을 선택하도록 하겠습니다. 다음 버튼을 클릭하면 아래와 같이 왼쪽 목록에 '소프트웨어만 설정'에 맞도록 설치 순서가 변경되어 있음을 확인 할 수 있을 것입니다.
그리고 이 단계에서는 데이터베이스 설치 유형을 선택해야 합니다.
- 단일 인스턴스 데이터베이스 설치 : 하나의 데이터베이스를 한 서버에서 실행시키는 방법입니다. 하나의 서버로 운영되기 때문에 서버 장애가 발생하면 서비스가 중지되는 문제점이 있습니다. 이 문제점을 보완하기 위해 두 대의 서버를 HA(High ability) 또는 이중화로 구성하여 사용하기도 합니다.
- Oracle Real Application Clusters 데이터베이스 설치 : RAC 설치. 클러스터링된 다수의 서버에 각각 인스턴스를 실행하여 데이터를 처리하는 구성 방법입니다. 단일 인스턴스로 서버 이중화를 구성하게 되면 동시에 한 서버만 Active 상태가 되고, 남은 서버는 Active 서버가 Failover되어 서비스가 넘어 올 때까지(절체 될 때까지) Standby하게 됩니다. 서버 자원의 부하가 Active인 하나의 서버에만 집중되기에 자원의 낭비가 심하게 됩니다. 이러한 이중화 구성의 문제점을 해결하고자 나온 기술이 RAC입니다.
RAC 설치를 위해서는 다수의 서버가 필요하고, 이 과정은 테스트 용도이기 때문에 '단일 인스턴스 데이터베이스 설치'를 선택하여 데이터베이스를 설치하도록 하겠습니다.
설치하고자 하는 데이터베이스 버전을 선택한 후 다음 버튼을 클릭합니다.
오라클 홈 사용자 계정은 오라클에서 권장하는 '가상 계정 사용'을 선택하도록 하겠습니다.
Oracle Base 경로를 지정해줍니다.
메모리 크기 등 설치 환경에 대한 검사를 진행하게 됩니다. 검사가 완료되고 설치 환경상의 문제가 없다면 아래와 같이 소프트웨어 설치 정보가 출력됩니다.
이제 '설치' 버튼을 클릭하면 소프트웨어 설치가 시작됩니다.
소프트웨어가 정상적으로 설치되었다면 아래와 같은 성공 메세지를 확인 할 수 있을 것입니다.
그리고 윈도우 시작메뉴를 보면 아래와 같이 새로 생성된 'Oracle - OraDB19Home1' 디렉토리와 설치된 오라클 소프트웨어를 확인하실 수 있을것입니다.
3. 데이터베이스 생성
Windows 환경에서 신규 오라클 데이터베이스는 DBCA(Database Configuration Assistant) 프로그램을 사용하여 생성합니다. 시작 메뉴 에서 'Oracle - OraDB19Home1' 디렉토리에 있는 Database Configuration Assistant를 선택하여 실행해 보겠습니다.
첫 화면에서는 사용자가 수행 할 작업을 선택하게 됩니다. 여기서는 신규로 데이터베이스를 생성 할 계획이므로 '데이터베이스 생성'을 선택 한 후 다음을 클릭합니다.
데이터베이스 생성 모드는 일반 구성과 고급 구성 중에서 선택 할 수 있습니다. 여기서는 일반 구성 모드를 선택하여 설치하도록 하겠습니다. 일반 구성 모드를 선택하여도 기본적인 데이터베이스 정보 입력이 필요합니다.
- 전역 데이터베이스 이름 : Oracle System Identifier(SID)를 뜻합니다. 단일 인스턴스 구성 모드에서도 데이터베이스는 여러 개 생성이 가능합니다. 단지 특정 시점에 하나의 데이터베이스만 서비스가 가능 할 뿐입니다. 데이터베이스를 시작할 때 오라클 소프트웨어는 ORACLE_SID라는 환경 변수에 명시된 데이터베이스를 실행하게 됩니다. 이렇듯 SID는 데이터베이스를 Unique하게 구별해주는 식별자 역할을 하게됩니다.
- 저장 영역 유형 : 데이터베이스 파일 등을 어떤 형태로 저장 할 지 결정하는 옵션입니다. 오라클에서는 ASM(Automatic Storage Management, 자동 저장 영역 관리)으로 디스크 저장 영역을 관리해주는 기능을 제공해주고 있습니다. 이 기능을 사용하면 파일 경합 등에 의한 부하가 감소되는 장점이 있습니다. 하지만, 별도의 디스크 볼륨이 필요하므로 데스크탑 환경에서는 선택하기 힘든 기능입니다. 여기서는 운영체제 '파일 시스템'을 선택하도록 하겠습니다.
- 데이터베이스 파일 위치 : 데이터베이스 파일이 위치하게 될 기본 경로를 설정하게 됩니다.
- FRA(빠른 복구 영역) : 아카이브 로그, flashback 로그 및 RMAN 백업 공간으로 FRA 영역을 사용할 수 있습니다. FRA 영역에서 파일을 저장 할 기본 경로를 설정해줍니다.
- 데이터베이스 문자 집합 : 데이터베이스의 캐릭터 셋을 설정합니다. 호환을 위해 기본값인 UTF-8을 선택하겠습니다.
- 관리 비밀번호 및 비밀번호 확인 : 관리자 계정의 비밀번호를 입력합니다.
- 컨테이너 데이터베이스로 생성 : 이번 설치작업에서는 테스트 용도이므로 이 항목은 비활성화 합니다.
위와 같이 입력되었으면 다음 버튼을 클릭합니다.
최종 설치 전 설정한 데이터베이스 생성 정보를 마지막으로 확인하게 됩니다. '완료'버튼을 클릭하면 데이터베이스 생성을 시작하게 됩니다.
데이터베이스 생성이 정상적으로 완료되면 아래와 같은 화면을 볼 수 있을 것입니다.
완료 화면에서 '비밀번호 관리' 버튼을 클릭하면 아래와 같이 데이터베이스 사용자 계정의 잠금 여부와 비밀번호 설정이 가능합니다.
4. 오라클 환경 변수 확인
Windows 환경에서 오라클의 환경 변수는 레지스트리를 통해 관리되고 있습니다.
윈도우 시작 메뉴 옆 검색 창에서 'regedit'를 입력하면 레지스트리 편집기를 확인 할 수 있습니다. 레지스트리 편집기를 클릭하여 실행해보겠습니다.
레지스트리 편집기가 실행되면 왼쪽 탐색 트리에서 '컴퓨터\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDB19Home1'를 선택하여 이동합니다.
그러면 위와 같이 오라클 환경 변수 값들을 확인 할 수 있을 것입니다.
5. 오라클 데이터베이스 실행 및 테스트
지금까지 설치한 데이터베이스에 접속해보도록 하겠습니다.
시작 메뉴 에서 'Oracle - OraDB19Home1' 디렉토리에 있는'SQL Plus'를 선택하여 실행해 보겠습니다.
sys 사용자로 로그인합니다. sys 계정은 시스템 계정이므로 사용자명 입력 시 뒤에 '/as sysdba'를 입력해줘야 합니다.
정상적으로 데이터베이스에 접속되었으면 테스트를 위해 임의의 테이블 tab1을 생성하고 데이터를 입력해보겠습니다.
