Oracle 노하우 및 팁
안녕하세요. 강정식입니다.
지난주에 제가 글을 올린 ’View를 만들때는 상도덕(?)이 필요하다?’에 대한 주제로 글을 써보겠습니다.
일반적으로 뷰를 사용하는 목적은 보안 측면, 편의 측면, 관리 측면에서 효율적이기 때문입니다.
첫번째인 보안 측면은 SQL에서 어떤 집합을 만든 View를 사용하기 때문에 그 View 안의 내용이 공개되지
않으므로 보안을 유지할 수 있습니다.
두번째인 편의 측면은 여러 업무 영역에서 다양한 데이터를 뽑아 정보를 보여줄 경우 각각의 업무영역에
대해 자세히 알아야 하나, 실질적으로 다양한 업무를 모두 알기란 쉽지가 않습니다. 하지만 각각의
업무에 대해 View로 제공할 경우, 이 View를 이용하여 다양한 업무영역을 이용할 수 있기 때문에
편의성이 증가하게 됩니다.
마지막으로 세번째인 관리 측면은 두번째 살펴본 것 처럼 다양한 업무들을 View로 관리할 경우
유지보수 및 재활용을 할 수 있기 때문에 관리하는데 상당히 유용합니다.
이런 유용한 장점을 가지고 있는 View인데도 불구하고 이를 잘 못 사용할 경우 성능측면에서
아주 안좋은 결과를 도출하게 되어 심할 경우 서버의 CPU 사용률을 100%로 높힐 수도 있습니다.
실제 제가 지원하고 있는 사이트에서도 튜닝이 필요한 프로그램의 3~40% 정도는 이런 View를 잘못
생성 및 관리하여 오픈 이후 운영에서 성능저하의 원인이 되고 있습니다.
그러므로 View를 생성 및 관리시 이런 주의 사항들을 충분히 숙지하여 향후 유지보수에 큰 비용을
지불하지 않도록 노력하는 것이 중요할 것으로 생각합니다.
지굼부터 제가 사이트에서 직접 경험한 내용들을 바탕으로 View를 만들 ㅤㄸㅒㅤ 왜 상도덕이 필요한지 살펴보도록
하겠습니다.
목차는 아래와 같이 진행하겠습니다.
I. ’View Merging’ 이란?
II. 전용 뷰 vs 공용 뷰
III. view 안에 가공된 컬럼
IV. Alias를 전략적으로 사용해야...
V. ’View Merging’을 방해하는 나머지 요소들
I. ’View Merging’ 이란?
1. Cost Based Query Transformation(CBQT)
1) CBQT란 옵티마이저가 주어진 SQL을 가지고 실행계획을 만들 ㅤㄸㅒㅤ 기존 SQL을 보다 최적화하기 위해
SQL을 재가공하는 것을 말합니다. 이런 대표적인 것들이 10g에 와서 강화가 되었는데,
’View Merging, Subquery Unnesting, Push Predicate’ 등이 있습니다.
이중 우리가 살펴볼 내용이 바로 ’View Merging’ 인데요. 아래에서 그 내용을 살펴보겠습니다.
2) View Merging Sample
① EMP 테이블에서 DEPT별 SAL 합계 구하는 View
CREATE OR REPLACE VIEW EMP_DEPT_SUM_V AS
SELECT DEPTNO,
SUM(SAL) SUM_SAL
FROM EMP
GROUP BY DEPTNO;
② View를 이용한 SQL
SELECT D.DNAME,
D.LOC,
EDSV.SUM_SAL
FROM DEPT D,
EMP_DEPT_SUM_V EDSV
WHERE D.DEPTNO = EDSV.DEPTNO
AND D.DNAME = ’SALES’
;
③ 옵티마이저가 CBQT의 View Merging을 통해 재구성한 SQL
SELECT D.DNAME,
D.LOC,
EDSV.SUM_SAL
FROM DEPT D,
(SELECT DEPTNO,
SUM(SAL) SUM_SAL
FROM EMP
GROUP BY DEPTNO) EDSV
WHERE D.DEPTNO = EDSV.DEPTNO
AND D.DNAME = 'SALES'
;
④ View Merging 실행계획
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 54 | 7 (15)| 00:00:01 |
| 1 | HASH GROUP BY | | 2 | 54 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 35 | 1 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 5 | 135 | 6 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | DEPT | 1 | 20 | 5 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_N1 | 5 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("D"."DNAME"=’SALES’)
5 - access("D"."DEPTNO"="DEPTNO")
보시는 것처럼 EMP_DEPT_SUM_V 뷰 안에 GROUP BY가 있는데 이 GROUP BY를 따로 실행한것이 아니라
DEPT 테이블에서 DNAME = ’SALES’에 대한 한 로우를 가지고 EMP_DEPT_SUM_V 뷰 안으로 들어가
해당되는 DEPTNO에 대해서 SAL을 SUM()한 결과입니다.
이처럼 옵티마이저는 사용자가 View를 사용하는 SQL을 던질 때 이를 재가공하여 최적화된 SQL을
다시 만들고 있습니다.
그럼 View Merging이 안된다면 어떻게 될까요? 아래에서 살펴보겠습니다.
⑤ No Merging 실행계획
SELECT /*+ NO_MERGE(EDSV) */ -- EDSV 뷰를 Merging하지 않도록 하는 힌트
D.DNAME,
D.LOC,
EDSV.SUM_SAL
FROM DEPT D,
EMP_DEPT_SUM_V EDSV
WHERE D.DEPTNO = EDSV.DEPTNO
AND D.DNAME = ’SALES’;
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 12 (17)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 46 | 12 (17)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | DEPT | 1 | 20 | 5 (0)| 00:00:01 |
| 3 | VIEW | EMP_DEPT_SUM_V | 3 | 78 | 6 (17)| 00:00:01 |
| 4 | HASH GROUP BY | | 3 | 21 | 6 (17)| 00:00:01 |
| 5 | TABLE ACCESS FULL| EMP | 14 | 98 | 5 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPTNO"="EDSV"."DEPTNO")
2 - filter("D"."DNAME"=’SALES’)
보시는 것처럼 DEPT와 EMP_DEPT_SUM_V 뷰가 조인될 ㅤㄸㅒㅤ ’NESTED LOOP’로 조인된 것이 아니라
’HASH JOIN’으로 되었고 이 부분에서 access("D"."DEPTNO"="EDSV"."DEPTNO") 처럼 조인이
된것을 알 수 있습니다.
그리고 뷰 안으로 DEPT 값이 침투를 못했기 때문에 GROUP BY가 VIEW 안에서 먼저 풀리고 있습니다.
대부분 View Merging이 안될때는 이처럼 실행계획에 VIEW가 보인다거나 HASH JOIN으로 풀리고
있습니다.
2. View Merging이 될 경우와 안될 경우의 일량 차이
1) View Merging vs No Merging
그림에서 보시다시피 View Merging이 될 경우 DEPT 테이블에서 DNAME = ’SALES’ 한건에 대해
EMP 테이블과 매칭되는 1만 건의 데이터만 SUM() 작업을 하면 되나 No Merging이 될 경우
DEPT 테이블에서 한건을 가져온 뒤 EMP 테이블에서 11만건을 독립적으로 수행하여 얻은
EMP 테이블에서 11만건을 수행하여 얻은 결과를 DEPT 테이블의 한건과 매칭되는 데이터를
필터로 빼내기 때문에 일량 차이는 ’1만건 vs 11만건’ 이라는 엄청난 차이를 보이게 됩니다.
2) View는 가급적 Merging이 되야 함
- 이처럼 VIew는 그 차체적으로 데이터를 줄여주는 조건이 대부분 들어가 있지 않기 때문에
이 View를 사용하는 SQL에서 그 View를 줄여주는 조건을 조인으로 연결시키므로 가급적
조인이 될 ㅤㄸㅒㅤ View Merging이 되어야 성능을 보장받을 수 있습니다.
3) 전략적으로 View Merging을 안시키기도 함
- 하지만 항상 View Merging이 좋은것만은 아닙니다. 때에 따라 Mering을 안시키고 연결시켜야
조인 레코드가 증가하지 않기 때문에 일량을 줄이는 케이스도 있지만, 이는 극히 소수에
불과하므로 전략적으로 사용할 때 유용한 방법입니다.
4) CBQT의 RBO, CBO
- 오라클 DBMS는 버전업이 될수록 옵티마이저 수행을 RBO보다 CBO로 변환을 시켰고 10g에 와서는
100% CBO 기반으로 옵티마이저가 수행이 됩니다. 그리고 이 CBQT 또한 9i까지는 Rule Base로
동작을 하였는데 10g에 와서 이 부분까지도 Cost Base로 강화가 되었습니다.
- 그러다보니 9i까지 View를 사용하고 있는 SQL이 잘 수행되었다가 10g로 업그레이드하면서
Cost Base로 변경되는 과정에서 View Merging이 잘 안되어 수행속도가 오래 걸리는 프로그램이
다수 발생되게 되었습니다.
- 그러므로 10g에서는 더욱 이 CBQT(View Merging 포함)에 대해 신경을 많이 써야 할 것으로
생각됩니다.
이번에는 Chapter I 까지만 기술을 하고 차주에 다음 내용을 정리하도록 하겠습니다.
읽으시고 이상한점이나 궁금하신 내용이 있으시면 리플 달아주시기 바랍니다.
감사합니다.
==> 오타 SQL 인듯, 수정 바랍니다.
좋은 지적 감사여~
) 버튼을 클릭하여 작성 하시면 됩니다.