Hive에서도 아래와 같이 'EXPLAIN' 명령문으로 쿼리 실행계획을 확인할 수 있습니다.
hive> EXPLAIN 쿼리문;
Hive의 실행계획은 다른 DBMS에 비해 실행계획의 가독성이 떨어진다는 점이 참으로 안타까운데요. 쿼리 수행 작업을 여러 Stage로 나눠 실행하고, 각 작업 단계에서도 Map과 Reduce 태스크로 일감을 분할해 처리하는 hive의 특성상 장문의 실행계획은 다른 대안이 없는 결과라고 생각합니다.
또 하나 아쉬운 점은, 파티션 테이블의 스캔 정보가 명확하지 않다는 점입니다.
오라클의 실행계획을 예를 들어 살펴보면, 'PARTITION RANGE SINGLE'이라는 Operation으로 하나의 파티션만 액세스 된다는 것을 쉽게 알 수 있습니다.
하지만, Hive에서의 실행계획에서는 PARTITION이라는 단어조차 찾아볼 수 없습니다.
그럼, 내가 작성한 쿼리문이 '테이블의 전체 데이터를 액세스 하는지?' 아니면 '조건에 맞는 파티션만 액세스(이하 Partition puruning)를 하는지?' 확인할 수 있는 방법은 없는 건가요?
방법은 두 쿼리 문의 실행계획 비교입니다.
첫 번째, WHERE 조건절 없이 전체 테이블 데이터를 SELECT 하는 쿼리의 실행계획을 확인해봅니다.
hive> EXPLAIN SELECT UID FROM ACT_TXT_DD;
위 실행계획에서 ACT_TXT_DD 테이블의 통계정보(Statistics)를 살펴봅니다. 로우 수(Num rows)가 1,199,025건이고 데이터 크기(Data size)가 2,053,929,825 Byte이네요.
두 번째, WHERE 조건절에 파티션 컬럼의 필터링 조건이 명시된 SELECT문의 실행계획을 확인해봅니다.
hive> EXPLAIN SELECT UID FROM ACT_TXT_DD WHERE BASE_DT='20200101';
파티션 컬럼 BASE_DT의 값이 '20200101'인 데이터만 추출하는 SELECT문의 실행계획은 아래와 같습니다.
통계정보(Statistics) 상의 로우 수(Num rows)는 101,835건이고 데이터 크기(Data size)는 174,443,355 Byte입니다.
위 WHERE 조건절이 없는 SELECT문의 실행계획과 비교해서 로우 수와 데이터 크기가 적어진 것을 확인할 수 있습니다. Partition puruning으로 테이블 스캔 범위가 적어져 위와 같은 통계정보를 출력하게 된 것입니다.
WHERE 조건절에 파티션 키 칼럼 필터링 조건을 입력했더라도, 형 변환 문제나 UDF 사용으로 인해 Partition puruning이 적용되지 않을 수도 있습니다. HQL 수행 속도가 매우 느리다면 위와 같은 방법으로 Partition puruning 여부를 확인해 보는 것이 HQL 최적화의 한 방법이 되겠습니다.
비효율적인 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 테이블을 액세스 하여 과도한 블럭 액세스를 유발하였는데, 쿼리를 조인으로 풀어 이러한 과도한 블럭 액세스를 제거한 것입니다.
