<오라클 성능 고도화 원리와 해법2> Ch03-02 옵티마이저 행동에 영향을 미치는 요소
오라클 성능 고도화 원리와 해법2 - Ch03-02 옵티마이저 행동에 영향을 미치는 요소
옵티마이저 행동의 차이는 궁극적으로 실행 계획의 차이를 말하며, 실행 계획에 영향을 미치는 요소로는 아래와 같은 것들이 있다.
(1) SQL과 연산자 형태
(2) 인덱스, IOT, 클러스터링, 파티셔닝, MV 등 옵티마이징 팩터
(3) 제약 설정: PK, FK, Not Null, Check
(4) 옵티마이저 힌트
(5) 통계 정보: 오브젝트 통계, 시스템 통계
(6) 옵티마이저 관련 파라미터
(7) DBMS 버전과 종류
이들 요소에 의해 옵티마이저가 구체적으로 어떤 영향을 받는지 살펴보자.
(1) SQL과 연산자 형태
결과가 같더라도 SQL을 어떤 형태로 작성했는지 또는 어떤 연산자(=, in, like, between, 부등호 등)를 사용했는지에 따라 옵티마이저가 다른 선택을 할 수 있고, 궁극적으로 쿼리 성능에 영향을 미친다. 전자에 대해서는 쿼리 변환에서 보게 될 것이고, 후자에 대해서는 충분히 설명하였다.
(2) 인덱스, IOT, 클러스터링, 파티셔닝, MV 등 옵티마이징 팩터
쿼리를 똑같이 작성하더라도 인덱스, IOT, 클러스터링, 파티셔닝, MV 등을 구성했는지 그리고 어떤 식으로 구성했는지에 따라 실행 계획과 성능이 크게 달라진다.
(3) 제약 설정
데이터베이스가 논리적으로 의미 있는 자료만을 포함하도록 하는 데이터 무결성 규칙으로는 아래 4가지가 있다.
• 개체 무결성(Entity Integrity)
• 참조 무결성(Referential Integrity)
• 도메인 무결성(Domain Integrity)
• 사용자 정의 무결성(또는 업무 제약 조건)
이들 규칙을 애플리케이션으로 구현할 수도 있지만 DBMS가 제공하는 PK, FK, Check, Not Null 같은 제약(constraint) 설정 기능을 이용해야 완벽한 데이터 무결성을 확보할 수 있다. 제약 설정은 데이터 무결성을 보장해줄 뿐만 아니라 옵티마이저가 쿼리 성능을 최적화하는 데에 매우 중요한 정보를 제공한다.
PK 제약과 옵티마이저
아래 쿼리는 가입 후 1년이 넘지 않은 고객의 지난 한 달간 주문 실적을 조회하고 있다.
1
2
3
4
5
6
7
8
select sum(주문수량), sum(주문금액), count(A), count(고객번호) as 주문건수
from 주문
where 고객번호 in (
select 고객번호
from 고객
where 가입일자 >= trunc(add_months(sysdate, -12))
)
and 주문일자 > trunc(add_months(sysdate, -1));
서브 쿼리를 Unnesting 하고 서브 쿼리를 기준으로 NL 조인 하려 할 때, 만약 고객 테이블에 PK 제약이 없다면 고객 번호 중복을 제거하는 sort unique 오퍼레이션을 먼저 수행해야 한다. 실제 고객 번호에 중복 값이 없더라도 옵티마이저에게 그런 사실을 알려주지 않으면 소용이 없다.
좀 더 자세한 설명은 4장 2절 서브 쿼리 Unnesting 중 ‘(6) 서브 쿼리가 M 쪽 집합이거나 Nonunique 인덱스일 때’ 를 참조하기 바란다.
또 다른 예로서 수정 가능 조인 뷰(Updatable Join View)를 들 수 있는데, 이 기능이 작동하려면 조인되는 1쪽 테이블에 PK 제약이 있어야 한다. 수정 가능 조인 뷰에 대해서는 2장 7절을 참조하기 바란다.
FK 제약과 옵티마이저
4장 6절에서 설명하는 조인 제거(Join Elimination) 기능을 참조하기 바란다. 이외에도 FK 제약이 있을 때만 작동하는 기능들이 여럿 있는데, 11g에 추가된 Reference 파티셔닝(6장 1절 6항 참조)도 그중 하나다.
Not Null 제약과 옵티마이저
아래는 부서(depino) 별 사원 수를 집계하는 쿼리다.
1
select deptno, count(*) from emp group by deptno;
옵티마이저가 이 쿼리를 최적화할 때 deptno 컬럼에 인덱스가 있으면 index full scan 또는 index fast full scan으로 빠르게 처리할 수 있다. 하지만 deptno 컬럼에 not null 제약이 있을 때 나 가능한 얘기다. Not Null 제약을 설정하지 않으면 옵티마이저는 Null 값이 입력될 가능성을 염두에 두고 실행 계획을 수립해야 하므로 테이블 전체를 스캔한다.
업무적으로 이 컬럼에 null 값을 허용하지 않더라도 옵티마이저에게 그런 사실을 알려주지 않으면 소용없는 것이다.
Check 제약과 옵티마이저
emp 테이블 sal 컬럼에 아래와 같은 제약을 설정하면 5,000을 초과하는 값은 입력되지 않는다.
1
alter table emp modify sal check (sal <= 5000);
그러면 옵티마이저도 쿼리를 최적화할 때 이 정보를 이용한다. 예를 들어, 아래와 같이 급여(sal)가 5,000을 초과하는 사원을 조회할 때면 filter 조건(Mulisnotnul)을 추가해 불필요한 IVO가 수행되지 않도록 한다.
1
2
3
4
5
6
select * from emp where sal > 5000
Rows Row Source Operation
---- ---------------------
0 FILTER (cr=0 pr=0 pw=0 time=9 us)
0 TABLE ACCESS FUL EMP (cr=0 pr=0 pw=0 time=0 us)
6장 1절에서 보게 될 수동 파티셔닝 기능도 check 제약이 옵티마이저 행동에 미치는 영향을 설명하는 좋은 예라고 할 수 있다. 파티션 뷰에서 참조하는 테이블에 check 제약을 설정하면 파티션 프로닝 기능이 작동한다.
(4) 옵티마이저 힌트
두말할 것도 없이 옵티마이저 힌트는 옵티마이저에게 매우 강한 영향력을 끼친다. 너무 강력해서 한 번 그 맛을 느끼면 모든 것을 힌트로 해결하려는 유혹에 빠지기 쉽다.
새삼스럽게 힌트가 무엇이고 어떤 종류의 것들이 있는지를 여기서 다룰 생각은 없지만 옵티마이저에게 어떤 의미를 주는지는 잠깐 살펴보려고 한다.
“힌트는 강제사항이 아니어서 옵티마이저는 자신의 선택과 비교해 더 나은 쪽을 결정한다”는 설명을 들은 적이 있다. 과연 그럴까? 옵티마이저가 가끔 사용자의 힌트를 무시하는 것을 보고 그런 생각을 갖게 됐을 것으로 짐작되지만 옵티마이저는 아래와 같은 경우가 아니면 힌트를 가장 우선적으로 따른다.
- 문법적으로 맞지 않게 힌트를 기술
- 잘못된 참조 사용: 없는 테이블이나 별칭(Alias)을 사용한 경우, 없는 인덱스명을 지정한 경우 등
- 의미적으로 맞지 않게 힌트를 기술: 예를 들어, 서브쿼리에 unnest와 push_subg를 같이 기술한 경우(unest되지 않은 서브쿼리만이 push_suba 힌트의 적용 대상이며, 4장에서 자세히 설명함)
- 논리적으로 불가능한 액세스 경로: 조인절에 등치 조건이 하나도 없는데 해시조인으로 유도하거나, 아래처럼 Null 허용 컬럼에 대한 인덱스를 이용해 전체 건수를 세려고 시도하는 등
select /*+ index e(emp ename_idx) */ count (*) from emp e - 버그
5번 항목(버그)이 너무 포괄적이어서 결국 힌트를 무시하는 경우가 있 다는 뜻이 되지만 여기서 강조하고자 하는 것은, 위 경우에 해당하지 않는 한 옵티마이저는 기본적으로 힌트의 내용을 먼저 따르고 남은 부분만을 자신의 판단에 따라 최적화한다는 점이다. 즉, 옵티마이저는 힌트를 선택 가능한 옵션 정도로 여기는 게 아니라 사용자로부터 주어진 명령어(directives)로 인식한다.
이런 속도 차이 때문에 힌트를 많이 사용하자고 주장하는 게 절대 아니므로 오해하지 말기 바란다. 옵티마이저 힌트에 관한 일반적인 사용 원칙은 다음과 같다.
- 가급적 힌트 사용을 자제하고, 옵티마이저가 스스로 좋은 선택을 할 수 있도록 돕는다.
- 옵티마이저가 잘못된 선택을 할 때만 힌트를 사용한다.
위 기준은 일반론적인 것일 뿐, 오히려 힌트를 적극적으로 사용해야 하는 시스템 환경도 많다. 힌트를 사용하지 않으면 데이터 특성 변화에 따라 실행 계획이 유연하게 바뀔 수 있는 장점이 있지만 그런 유연성이 오히려 치명적인 결과를 가져올 수 있기 때문이다. 그런 시스템이라면 힌트 사용이 불가피하고 어차피 사용할 거면 힌트를 아주 엄격하게 기술해주어야 한다.
앞에서 RBO CBO냐의 논쟁은 더 이상 불필요한 것이 돼버렸다고 했는데, 힌트 사용에 관한 논쟁은 계속 진행 중이며 영원히(?) 끝나지 않을지도 모를 일이다.
(5) 통계정보
통계정보에 대해서는 4절과 8절에서 자세히 설명한다. 어찌보면 3장 전체가 통계정보의 중요성을 다룬다 해도 과언이 아니다. 그만큼 통계정보는 중요하고, 옵티마이저에게 미치는 영향력이 절대적이다.
뒤에서 카디널리티와 비용 계산 원리에 대해 설명할 때 느끼겠지만 CBO의 모든 판단 기준은 통계정보에서 나온다. 통계정보가 없으면 작동하지 않는 기능들도 많다. 이에 대한 정확한 이해 없이는 데이터베이스 성능 문제를 다루는 것 자체가 불가능할 정도로 최신 옵티마이저는 통계정보를 중심으로 움직인다. 통계정보에 대해 막연한 정의만 알고 있던 독자라면 본장을 통해 확실한 개념과 전략을 갖는 계기가 되길 바란다.
(6) 옵티마이저 관련 파라미터
SQL, 데이터, 통계정보, 하드웨어 등 모든 환경이 동일한데도 오라클 버전을 업그레이드하면 옵티마이저가 다르게 작동하는 경험을 누구나 한다. 옵티마이저의 그런 행동 변화는 대개 파라미터의 추가 또는 변경을 통해 이루어진다.
옵티마이저 모드에 대해서는 이미 살펴보았고, 그 외에도 옵티마이저 행동에 영향을 미치는 파라미터가 많다. 10g부터는 아래 쿼리를 통해 그 목록을 쉽게 얻을 수 있다.
1
2
select name, value, isdefault, default_value
from v$sys_optimizer_env
사실 이 뷰를 통해 확인 가능한 목록은 극히 일부에 지나지 않는다. 종종 변경할 때가 있는 공식 파라미터를 주로 보여주며, 공개되지 않은 무수히 많은 Hidden 파라미터 중에서는 관리자가 기본값을 변경한 것만 보여준다.
옵티마이저의 행동 변화는 대개 긍정적인 방향으로 작용하지만 그렇지 못한 기능들 때문에 Hidden 파라미터가 필요하다. 오라클은 새로 구현한 기능을 곧바로 적용하지 않고 Hidden 파라미터(기본적으로 애된 상태를 함께 제공함으로써 충분한 테스트와 검증을 거치고, 문제가 없다고 판단될 때 공식적으로 발표하는 순서를 밟는다. 공식화된 후라도 문제가 생기면 파라미터로 기능을 off 시킬 수 있다.
이처럼 충분한 테스트를 거치더라도 오라클을 업그레이드하면 전에 없던 문제점들이 발생하기 마련인데, 만약 옵티마이저의 갑작스런 변화를 원치 않는다면 optimizer features enable 파라미터를 이전 버전으로 설정하면 된다.
1
alter system set optimizer features enable = "9.2.0.4";
(7) DBMS 버전과 종류
옵티마이저 관련 파라미터와 상관없이 버전에 따라 다른 실행 계획을 수립하는 경우도 있다. 적용했을 때 확실히 좋은 성능을 보장해준다면 굳이 그 기능을 off 시킬 필요가 없으므로 파라미터도 불필요하다. 또한, 당연한 얘기겠지만 같은 형태의 SQL인데도 DBMS 종류(오라클, SQLServer, Sybase, DB)에 따라 내부적으로 처리하는 방식에 차이가 있다.