함수 튜닝

개발자를 위한 오라클 SQL 튜닝 내용에서 참고한 내용입니다.

Intro

분석 함수 튜닝

집계 함수의 한계

집계 함수(Aggregate Function)
- SUM(), AVG(), COUNT()
- SQL문에서 나온 결과 행들이 여러 개라면, 집계 함수를 사용함으로써 결과 행의 건수가 N보다 적어지는 한계가 있다.
- 집계 함수는 결과 행의 건수를 보장하지 않는다.
- 집계 내역을 뽑으면서 다른 정보도 같이 보려는 경우, 집계 함수는 적합하지 않다.

분석 함수의 유용성

분석 함수(Analytic Function)
- WHERE 절을 통해 나온 행들을 대상으로 다양한 집계나 통계를 구할 때 사용하는 함수이다.

분석 함수 튜닝

분석 함수 튜닝이란 분석 함수를 이용하여 SQL문의 성능을 극대화하는 모든 활동을 의미한다.

주요 분석 함수

RANK
- SELECT 문의 결과 내에서 특정 조건에 따른 순서를 구하는데, 동일한 값은 동일한 순위가 매겨진다.

SELECT RANK(/* 대상값 */) OVER (/* 순위조건 */)
FROM dual;

ROW_NUMBER
- SELECT 문의 결과 내에서 특정 조건에 따른 순위를 구하는데, 동일한 값이라도 다른 순위를 매겨준다.
- 시퀀스를 구하는 용도로 자주 사용한다.

SELECT ROW_NUMBER(/* 대상값 */) OVER (/* 순위조건 */)
FROM DUAL;

SUM
- SELECT 문 결과 내에서 특정 값의 합계를 계산하는 함수이다.

SELECT SUM(/* 대상값 */) OVER (/* 합계조건 */)
FROM DUAL;

SAL 컬럼 기준으로 정렬한 상태에서 각각의 행은 첫 행에서 현재 행까지의 합계를 산출하게 된다.

SELECT SUM() OVER (ORDER BY SAL rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) RANK
FROM DUAL;

MAX
- SELECT 문 결과 내에서 특정 조건에 따른 최대값을 구하는 함수이다.

SELECT MAX(/* 대상값 */) OVER (/* 조건절 */)
FROM DUAL;

AVG
- SELECT 문 결과 내에서 특정 조건에 따른 평균값을 구하는 함수이다.

SELECT AVG(/* 대상값 */) OVER (/* 조건절 */)
FROM DUAL;

RANK 함수를 이용하여 반복적인 테이블 스캔 제거

테이블 생성

CREATE TABLE TB_ORD
(
    ORD_NO   VARCHAR2(10), --주문번호
    ORD_DT   VARCHAR2(8),  --주문일자
    ORD_AMT  NUMBER(15),   --주문금액
    PRDT_CD  VARCHAR2(6),  --제품코드
    CUST_ID  VARCHAR2(10), --고객ID
    INST_ID  VARCHAR2(10), --입력자
    INST_DTM DATE,         --입력일시
    UPDT_ID  VARCHAR2(10), --수정자
    UPDT_DTM DATE          --수정일시
);

데이터 입력

CREATE TABLE DUAL_100
(
    DUMMY VARCHAR2(1)
);

INSERT INTO DUAL_100
SELECT DUMMY
FROM DUAL
CONNECT BY LEVEL <= 100;

COMMIT;

주문 테이블 NOLOGGING 모드 설정

ALTER TABLE TB_ORD
    NOLOGGING;

주문 테이블에 1000만건 데이터 입력

INSERT /*+ APPEND */ INTO TB_ORD --APPEND 힌트 사용
SELECT LPAD(TO_CHAR(ROWNUM), 10, '0'),
       TO_CHAR(SYSDATE - TRUNC(DBMS_RANDOM.VALUE(1, 3650)), 'YYYYMMDD'),
       TRUNC(DBMS_RANDOM.VALUE(100, 1000000)),
       LPAD(TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(1, 999999))), 6, '0'),
       LPAD(TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(1, 999999))), 10, '0'),
       'DBMSEXPERT',
       SYSDATE,
       NULL,
       NULL
FROM DUAL_100,
     (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 100000);

COMMIT;

기본키 생성

ALTER TABLE TB_ORD
    ADD CONSTRAINT TB_ORD_PK
        PRIMARY KEY (ORD_NO);

통계정보 생성

ANALYZE TABLE TB_ORD COMPUTE STATISTICS
    FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS SIZE 254;

분석 함수 튜닝 전 상황

SELECT Y.*
FROM (
         SELECT /*+ NO_MERGE*/
             ORD_DT,
             MAX(ORD_AMT) AS ORD_AMT
         FROM TB_ORD
         WHERE ORD_DT BETWEEN TO_CHAR(SYSDATE - 30, 'YYYYMMDD') AND TO_CHAR(SYSDATE, 'YYYYMMDD')
         GROUP BY ORD_DT
     ) X,
     TB_ORD Y
WHERE Y.ORD_DT = X.ORD_DT
  AND Y.ORD_AMT = X.ORD_AMT
ORDER BY Y.ORD_DT;

SQL 분석
- NO_MERGE 힌트를 사용함으로써 인라인 뷰가 View Merging되어 메인쿼리인 TB_ORD Y와 같은 레벨에서 수행되는 것을 방지해준다.
- 즉, 인라인 뷰 X 내의 SQL은 해당 인라인 뷰 내에서 처리하게 한다.
SQL의 문제점
- TB_ORD 테이블에는 별도의 인덱스가 존재하지 않지만, SQL은 TB_ORD 테이블을 2번 스캔하고 있다.
- 이런 SQL은 분석 함수를 이용하여 한 번만 스캔하도록 튜닝해야 한다.
실행 계획
- 인라인 뷰 내에 TB_ORD 테이블을 테이블 풀 스캔(TABLE ACCESS FULL)한다.
- GROUP BY 연산을 수행한다.
- GROUP BY 연산에 대한 인라인 뷰
- TB_ORD 테이블을 테이블 풀 스캔(TABLE ACCESS FULL)한다.
- GROUP BY 연산의 결과 값과 인라인 뷰 결과를 해시 조인(Hash Join)한다.
- 소팅 연산(Sort Order By)을 수행
- SELECT 절의 연산을 수행

분석 함수 튜닝 후 상황

SELECT ORD_NO,
       ORD_DT,
       ORD_AMT,
       PRDT_CD,
       CUST_ID,
       INST_ID,
       INST_DTM,
       UPDT_ID,
       UPDT_DTM
FROM (
         SELECT ORD_NO,
                ORD_DT,
                ORD_AMT,
                PRDT_CD,
                CUST_ID,
                INST_ID,
                INST_DTM,
                UPDT_ID,
                UPDT_DTM,
                RANK() OVER (PARTITION BY ORD_DT ORDER BY ORD_AMT DESC) AS RN
         FROM TB_ORD
         WHERE ORD_DT BETWEEN TO_CHAR(SYSDATE - 30, 'YYYYMMDD') AND TO_CHAR(SYSDATE, 'YYYYMMDD'))
WHERE RN = 1
ORDER BY ORD_DT;

SQL 분석
- RANK 함수를 사용하여 ORD_DT 별 ORD_AMT의 내림차순 순위를 구한다.
- RANK 함수로 구한 순위 중 1위인 건만 추출한다.
실행 계획
- 인라인 뷰 내에 TB_ORD 테이블을 테이블 풀 스캔(TABLE ACCESS FULL) 한다.
- RANK 함수 연산을 수행한다.
- 테이블 풀 스캔 연산의 결과에 대한 인라인 뷰이다.
- SELECT 절의 연산을 수행한다.

SUM 함수를 이용하여 반복적인 테이블 스캔 제거하기

테이블 생성

CREATE TABLE TB_SALE_MONTH
(
    AGENT_NO VARCHAR2(4),
    YYYYMM   VARCHAR2(6),
    SALE_AMT NUMBER(9)
);

데이터 입력

ALTER TABLE TB_SALE_MONTH
    NOLOGGING;

INSERT /*+ APPEND */ INTO TB_SALE_MONTH -- APPEND 힌트 사용
SELECT LPAD(TO_CHAR(B.LV), 4, '0'),
       A.YYYYMM,
       TRUNC(DBMS_RANDOM.VALUE(100000, 10000000), -3)
FROM (
         SELECT TO_CHAR(ADD_MONTHS(SYSDATE, -ROWNUM + 1), 'YYYYMM') YYYYMM
         FROM DUAL
         CONNECT BY LEVEL <= 120
     ) A,
     (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 9999) B;

COMMIT;

기본키 생성

ALTER TABLE TB_SALE_MONTH
    ADD CONSTRAINT TB_SALE_MONTH_PK
        PRIMARY KEY (AGENT_NO, YYYYMM);

통계정보 생성

ANALYZE TABLE TB_SALE_MONTH COMPUTE STATISTICS
    FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS SIZE 254;

튜닝 전 상황

SELECT A.AGENT_NO,
       A.YYYYMM,
       MIN(A.SALE_AMT),
       SUM(B.SALE_AMT)
FROM TB_SALE_MONTH A,
     TB_SALE_MONTH B
WHERE A.YYYYMM >= B.YYYYMM
  AND A.AGENT_NO = B.AGENT_NO
GROUP BY A.AGENT_NO, A.YYYYMM
ORDER BY A.AGENT_NO, A.YYYYMM;

SQL 분석
- 월별 누적 합을 구하기 위해서는 B는 A보다 작아야 한다.
- AGENT_NO 컬럼을 기준으로 조인 조건을 걸어준다. 이러면 AGENT_NO별 합계를 구할 수 있다.
SQL 문제점
- TB_SALE_MONTH 테이블을 2번 스캔하고 있다.
- SUM 분석 함수를 사용한다면 해당 테이블을 단 한번만 스캔하여 결과를 도출할 수 있다.
실행 계획
- TB_SALE_MONTH 테이블을 테이블 풀 스캔(TABLE ACCESS FULL)한다.
- TB_SALE_MONTH 테이블을 테이블 풀 스캔(TABLE ACCESS FULL)한다.
- 위의 테이블 풀 스캔한 두 결과를 해시 조인(HASH JOIN)한다.
- GROUP BY 연산을 한다.
- SELECT 절의 연산을 수행한다.

튜닝 후 상황

SELECT AGENT_NO,
       YYYYMM,
       SALE_AMT,
       SUM(SALE_AMT) OVER
           (PARTITION BY AGENT_NO ORDER BY AGENT_NO, YYYYMM ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM TB_SALE_MONTH;

SQL 분석
- 분석 함수인 SUM를 이용
- AGENT_NO 컬럼을 기준으로 잡는다.
- AGENT_NO과 YYYYMM 컬럼을 기준으로 정렬을 수행한다.
- 합계 연산의 범위를 지정한다.
- 맨 처음부터 현재 행까지의 합계를 낸다.
실행 계획
- TB_SALE_MONTH 테이블을 테이블 풀 스캔(TABLE ACCESS FULL)한다.
- SUM 분석 함수를 수행한다.
- SELECT 절의 연산을 수행한다.

사용자 정의 함수 튜닝

사용자 정의 함수

사용자 정의 함수(User Defined Function)는 특정 업무 프로세스를 사용자가 정의해두고 필요할 때 호출하여 사용하는 함수를 의미한다.

예시

특정 사원번호의 급여를 10% 인상한 후 인상된 급여를 리턴하는 사용자 정의 함수이다.

CREATE OR REPLACE FUNCTION FC_UPDATE_SAL(V_EMPNO IN NUMBER)
    RETURN NUMBER
    IS
    V_SAL EMP_SAL$TYPE;

BEGIN

    UPDATE EMP
    SET SAL = SAL * 1.1
    WHERE EMPNO = V_EMPNO;
    COMMIT;

    SELECT SAL
    INTO V_SAL
    FROM EMP
    WHERE EMPNO = V_EMPNO;
    RETURN V_SAL;
END;

SELECT FC_UPDATE_SAL(7369)
FROM DUAL;

사용자 정의 함수의 재귀 호출 부하

내장 함수(Built-In Function)와 사용자 정의 함수(User Defined Function)가 있다.
내장 함수는 DBMS 엔진 내에 네이티브 코드로 컴파일된 상태로 존재하므로 빠른 속도를 보장한다.
사용자 정의 함수는 PL/SQL 가상 머신 내에서 구동되어 내장 함수보다 컨텍스트 스위칭(Context-Switching) 부하가 발생한다.
이러한 부하를 재귀 호출(Recursive Call) 부하라 한다.

사용자 정의 함수 튜닝

사용자 정의 함수를 사용해야 하는 경우 성능을 고려하여 SQL 튜닝을 진행해야 한다.

사용자 정의 함수 재귀 호출 부하 최소화하기

테이블 생성

CREATE TABLE TB_EMP
(
    EMP_NO  VARCHAR2(10), --사원번호
    EMP_NM  VARCHAR2(50), --사원명
    JOB_CD  VARCHAR2(6),  --직업코드
    JOIN_DT VARCHAR2(8),  --입사일자
    SAL     NUMBER(15),   --급여
    DEPT_NO VARCHAR2(6)   --부서번호
);

데이터 입력

INSERT INTO TB_EMP
SELECT LPAD(TO_CHAR(ROWNUM), 10, '0'),
       DBMS_RANDOM.STRING('U', 50),
       LPAD(TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(1, 100))), 6, '0'),
       TO_CHAR(SYSDATE - TRUNC(DBMS_RANDOM.VALUE(1, 3650)), 'YYYYMMDD'),
       TRUNC(DBMS_RANDOM.VALUE(1200, 12000)),
       LPAD(TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(1, 50))), 6, '0')
FROM DUAL
CONNECT BY LEVEL <= 100000;

COMMIT;

기본키 생성

ALTER TABLE TB_EMP
    ADD CONSTRAINT TB_EMP_PK
        PRIMARY KEY (EMP_NO);

인덱스 구성

CREATE INDEX TB_EMP_IDX01 ON TB_EMP (DEPT_NO);

통계 정보 생성

ANALYZE TABLE TB_EMP COMPUTE STATISTICS
    FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS SIZE 254;

사용자 정의 함수 생성

CREATE OR REPLACE FUNCTION FN_GET_EMP_CNT(IN_DEPT_NO IN VARCHAR2)
    RETURN NUMBER
AS
    CNT NUMBER;
BEGIN
    SELECT COUNT(*)
    INTO CNT
    FROM TB_EMP A
    WHERE A.DEPT_NO = IN_DEPT_NO;
    RETURN (CNT);
END;
/

사용자 정의 함수 재귀 호출 부하 최소화하기 튜닝 전

SELECT A.*,
       CASE
           WHEN A.EMP_CNT BETWEEN 0 AND 5
               THEN '5명 이하'
           WHEN A.EMP_CNT BETWEEN 6 AND 10
               THEN '10명 이하'
           WHEN A.EMP_CNT BETWEEN 11 AND 100
               THEN '100명 이하'
           WHEN A.EMP_CNT BETWEEN 101 AND 1000
               THEN '1000명 이하'
           ELSE '1000명 초과'
           END AS 소속부서사원수
FROM (
         SELECT EMP_NO,
                EMP_NM,
                DEPT_NO,
                FN_GET_EMP_CNT(DEPT_NO) AS EMP_CNT
         FROM TB_EMP
     ) A
ORDER BY EMP_NM;

SQL 분석
- FN_GET_EMP_CNT 함수로 부서별 사원수를 구한다.
SQL 문제점
- TB_EMP 테이블에는 10만건의 데이터가 존재하므로 FN_GET_EMP_CNT 함수는 총 10만번 호출된다.
- 이러한 경우 재귀 호출 부하가 발생하게 되어 DBMS를 장애 상황으로 몰고 갈 수 있다.
실행 계획
- TB_EMP 테이블을 테이블 풀 스캔(TABLE ACCESS FULL)한다.
- 소팅 연산(SORT ORDER BY)을 수행한다.
- SELECT 절의 연산을 수행

사용자 정의 함수 재귀 호출 부하 최소화하기 튜닝 후

SELECT A.*,
       CASE
           WHEN A.EMP_CNT BETWEEN 0 AND 5
               THEN '5명 이하'
           WHEN A.EMP_CNT BETWEEN 6 AND 10
               THEN '10명 이하'
           WHEN A.EMP_CNT BETWEEN 11 AND 100
               THEN '100명 이하'
           WHEN A.EMP_CNT BETWEEN 101 AND 1000
               THEN '1000명 이하'
           ELSE '1000명 초과'
           END AS 소속부서사원수
FROM (
         SELECT EMP_NO,
                EMP_NM,
                DEPT_NO,
                (
                    SELECT FN_GET_EMP_CNT(DEPT_NO)
                    FROM DUAL
                ) AS EMP_CNT
         FROM TB_EMP
     ) A
ORDER BY EMP_NM;

SQL 분석
- 사용자 정의 함수 호출 부분을 DUAL 테이블을 이용하여 스칼라 서브쿼리로 구성
- 스칼라 서브쿼리의 캐싱 효과로 인해 극적인 성능 향상을 이루게 된다.

스칼라 서브쿼리의 캐싱 효과 확인

서브쿼리의 캐싱 효과를 확인하기 위한 DEPT_NO의 유일 값 확인
10만 번 재귀 호출할 거를 49번으로 줄어들게 된다.
유일 값이 적은 컬럼이 사용자 정의 함수에 입력 값으로 들어가면 재귀 호출을 획기적으로 줄일 수 있다.

SELECT COUNT(DISTINCT DEPT_NO)
FROM TB_EMP;

실행 계획
- TB_EMP 테이블을 테이블 풀 스캔(TABLE ACCESS FULL)한다.
- 소팅 연산(SORT ORDER BY)을 수행한다.
- DUAL 테이블을 이용
- SELECT 절의 연산을 수행한다.

Previous아우터 조인 Next부분 범위 처리 튜닝

Last updated 4 years ago

Was this helpful?