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) 한다.
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) 부하가 발생한다.
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번으로 줄어들게 된다.
유일 값이 적은 컬럼이 사용자 정의 함수에 입력 값으로 들어가면 재귀 호출을 획기적으로 줄일 수 있다.
