# 함수 튜닝 > [개발자를 위한 오라클 SQL 튜닝](https://www.hanbit.co.kr/store/books/look.php?p_code=E9267570814) 내용에서 참고한 내용입니다. ## 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 절의 연산을 수행한다. --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://seokrae.gitbook.io/sr/book/tune/_12.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.