02. 옵티마이저 행동에 영향을 미치는 요소

옵티마이저 행동의 차이는 궁극적으로 실행계획의 차이를 말하며, 실행계획에 영향을 미치는 요소로는 아래와 같은 것들이 있다.

  (1) SQL과 연산자 형태
  (2) 인덱스, IOT, 클러스터링, 파티셔닝, MV등 옵티마이징 팩터
  (3) 제약 설정 : PK, FK, Not Null, Check
  (4) 옵티마이저 힌트
  (5) 통계정보 : 오브젝트 통계, 시스템 통계
  (6) 옵티마이저 관련 파라미터
  (7) DBMS 버젼과 종류

(1) SQL과 연산자 형태

결과가 같더라도 SQL을 어떤 형태로 작성했는지 또는 어떤 연산자를 사용했는지에 따라 옵티마이저가 다른 선택을 할 수 있고, 궁극적으로 쿼리 성능에 영향을 미친다.

(2) 인덱스, IOT, 클러스터링, 파티셔닝, MV등 옵티마이징 팩터

쿼리를 똑같이 작성하더라도 인덱스, IOT, 클러스터링, 파티셔닝, MV 등을 구성했는지 그리고 어떤 식으로 구성했는지에 따라 실행계획과 성능이 크게 달라진다.

(3) 제약 설정

데이터베이스가 논리적으로 의미 있는 자료만을 포함하도록 하는 데이터 무결성 규칙
이들 규칙을 애플리케이션으로 구현할 수도 있지만 DBMS가 제공하는 PK, FK, Not Null, Check 같은 제약(constraint)설정 기능을 이용해야 완벽한 데이터 무결성을 확보할 수 있다.
제약 설정은 데이터 무결성을 보장해 줄뿐만 아니라 옵티마이저가 쿼리 성능을 최적화하는 데에 매우 중요한 정보를 제공한다.

  • 개체 무결성(Entity Integrity)
  • 참조 무결성(Referential Integrity)
  • 도메인 무결성(Domain Integrity)
  • 사용자 정의 무결성(또는 업무 제약 조건)
PK 제약과 옵티마이저
select sum(주문수량), sum(주문금액), count(*), count(distinct 고객번호)
  from 주문
 where 고객번호 in (select 고객번호 
                        from 고객
                       where 가입일자 >= trunc(add_months(sysdate, -12)))
   and 주문일자 >= trunc(add_months(sysdate, -1))

서브쿼리 Unnesting(4장 2절 또는 코어 오라클 데이터베이스 스터디 자료(서브쿼리 Unnesting) 참조) 하고서 고객 테이블을 기준으로 NL 조인하려 할 때, 만약 고객 테이블에 PK 제약이 없다면 고객번호 중복을 제거하는 sort unique 오퍼레이션을 먼저 수행해야 한다.
실제 고객번호에 중복 값이 없더라도 옵티마이저에게 그런 사실을 알려주지 않으면 소용이 없다.

FK 제약과 옵티마이저 : 4장 6절 또는 코어 오라클 데이터베이스 스터디 자료(조인 제거) 참조
Not Null 제약과 옵티마이저
SYS@ora10g>select deptno, count(*) from scott.emp group by deptno ;

위 쿼리는 부서(deptno)별 사원 수를 집계하는 쿼리다.
옵티마이저가 이 쿼리를 최적화할 때 deptno 컬럼에 인덱스가 있으면 index full scan 또는 index fast full scan으로 바르게 처리할 수 있다.
하지만 deptno 컬럼에 not null 제약이 있을 때나 가능한 예기다.
not null 제약을 설정하지 않으면 옵티마이저는 null 값이 입력될 가능성을 염두에 두고 실행계획을 수립해야 하므로 테이블 전체를 스캔한다.

Check 제약과 옵티마이저
-- scott.emp 테이블 sal 컬럼에 아래와 같은 제약을 설정하면 5,000을 초과하는 값은 입력되지 않는다.
SYS@ora10g>alter table scott.emp modify sal check (sal <= 5000);
-- 옵티마이저도 쿼리를 최적화할 때 이 Check 제약 정보를 이용한다.
-- 아래와 같이 급여(sal)가 5,000을 초과하는 사원을 조회할 때면 filter 조건(null is not null)을 추가해 불필요한 I/O가 수행되지 않도록 한다.

SYS@ora10g>select * from scott.emp ;    

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    15 |   555 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    15 |   555 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

SYS@ora10g>select * from scott.emp where sal > 5000 ;

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    37 |     0   (0)|          |
|*  1 |  FILTER            |      |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |     1 |    37 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(NULL IS NOT NULL)
   2 - filter("SAL">5000)

(4) 옵티마이저 힌트

아래와 같은 경우가 아니면 힌트를 가장 우선적으로 따른다.
  1. 문법적으로 맞지 않게 힌트를 기술
  2. 잘못된 참조 사용 : 없는 테이블이나 별칭을 사용한 경우, 없는 인덱스명을 지정한 경우
  3. 의미적으로 맞지 않게 힌트를 기술 : 예를 들어, 서브쿼리에 unnest와 push_subq를 같이 기술한 경우
  4. 논리적으로 불가능한 액세스 경로 : 조인절이 등치(=) 조건이 하나도 없는데 해시 조인으로 유도하거나, 아래처럼 null 허용컬럼에 대한 인덱스를 이용해 전체 건수를 세려고 시도하는 등

select /*+ index(e emp_ename_idx) */ count(*) from emp e

  5. 버그

  • 위 5가지 경우에 해당하지 않는 한 옵티마이저는 기본적으로 힌트의 내용을 먼저 따르고 남은 부분만을 자신의 판단에 따라 최적화한다.
    옵티마이저는 힌트를 선택 가능한 옵션 정도로 여기는 게 아니라 사용자로부터 주어진 명령어(directives)로 인식한다.

(5) 통계정보

통계정보에 대해서는 4절과 8절에서 자세히 설명한다.
어찌 보면 3장 전체가 통계정보의 중요성을 다룬다 해도 과언이 아니다. 그만큼 통계정보는 중요하고, 옵티마이저에게 미치는 영향력이 절대적이다.
뒤에서 카디널리티와 비용 계산 원리에 대해 설명할 때 느끼겠지만 CBO의 모든 판단 기준은 통계정보에서 나온다.
통계정보가 없으면 작동하지 않는 기능들도 많다.
이에 대한 정확한 이해없이는 데이터베이스 성능 문제를 다루는 것 자체가 불가능할 정도로 최신 옵티마이저는 통계정보를 중심으로 움직인다.

(6) 옵티마이저 관련 파라미터

SQL, 데이터, 통계정보, 하드웨어 등 모든 환경이 동일하나데도 오라클 버전을 업그레이드하면 옵티마이저가 다르게 작동하는 경험을 누구나 한다.
옵티마이저의 그런 행동 변화는 대개 파라미터의 추가 또는 변경을 통해 이루어진다.
옵티마이저 모드 외에도 옵티마이저 행동에 영향을 미치는 파라미터가 많다.
10g부터는 아래 쿼리를 통해 그 목록을 쉽게 얻을 수 있다.

SYS@ora10g>select name, value, isdefault, default_value
from v$sys_optimizer_env ;

NAME                                     VALUE                     ISD DEFAULT_VALUE
---------------------------------------- ------------------------- --- -------------------------
parallel_execution_enabled               true                      YES true
optimizer_features_enable                10.2.0.1                  YES 10.2.0.1
cpu_count                                1                         YES 1
active_instance_count                    1                         YES 1
parallel_threads_per_cpu                 2                         YES 2
hash_area_size                           131072                    YES 131072
bitmap_merge_area_size                   1048576                   YES 1048576
sort_area_size                           65536                     YES 65536
sort_area_retained_size                  0                         YES 0
pga_aggregate_target                     16384 KB                  YES 16384 KB
parallel_query_mode                      enabled                   YES enabled
parallel_dml_mode                        disabled                  YES disabled
parallel_ddl_mode                        enabled                   YES enabled
optimizer_mode                           all_rows                  YES all_rows
cursor_sharing                           exact                     YES exact
star_transformation_enabled              false                     YES false
optimizer_index_cost_adj                 100                       YES 100
optimizer_index_caching                  0                         YES 0
query_rewrite_enabled                    true                      YES true
query_rewrite_integrity                  enforced                  YES enforced
workarea_size_policy                     auto                      YES auto
optimizer_dynamic_sampling               2                         YES 2
statistics_level                         typical                   YES typical
skip_unusable_indexes                    true                      YES true
optimizer_secure_view_merging            true                      YES true

25 rows selected.

옵티마이저의 행동 변화는 대개 긍정적인 방향으로 작용하지만 그렇지 못한 기능들 때문에 Hidden 파라미터가 필요하다.
오라클은 새로 구현한 기능을 곧바로 적용하지 않고 Hidden 파라미터(기본적으로 off된 상태)를 함께 제공함으로써 충분한 테스트와 검증을 거치고, 문제가 없다고 판단될 때 공식적으로 발표하는 순서를 밟는다.
공식화된 후라도 문제가 생기면 파라미터로 기능을 off 시킬 수 있다.
충분한 테스트를 거치더라도 오라클을 업그레이드하면 전에 없던 문제점들이 발생하기 마련인데,
만약 옵티마이저의 갑작스런 변화를 원치 않는다면 optimizer_features_enable 파라미터를 이전 버전으로 설정하면 된다.

alter system set optimizer_features_enable = "9.2.0.4" ;

(7) DBMS 버젼과 종류

옵티마이저 관련 파라미터와 상관없이 버전에 따라 다른 실행계획을 수립하는 경우도 있다.
적용했을 때 확실히 좋은 성능을 보장해 준다면 굳이 그 기능을 off 시킬 필요가 없으므로 파라미터도 불필요하다.
DBMS 버전에 따라 실행계획이 변하는 경우부터 살펴보자.

select max(empno) from emp ;

오라클7 버전까지는 위와 같은 쿼리를 최적화하려고 아래와 같이 index_desc 힌트와 rownum 조건을 사용하곤 했다.

select /*+ index_desc(emp emp_pk) */ empno from emp where rownum = 1 ;
Row     Row  Source   Operation
-----  ---------------------------------------
   1    COUNT STOPKEY (cr=1 pr=0 pw=0 time=61 us)
   1     INDEX FULL SCAN EMP_PK (cr=1 pr=0 pw=0 time 36 us)

하지만 오라클8 버전부터는 그럴 필요가 없다.
아래와 같이 옵티마이저가 알아서 그런 처리를 대신해 주기 때문이다.
다만, 해당 오브젝트에 대한 통계정보가 수집돼 있어야 한다.
10g부터는 기본 동적 샘플링 레벨이 2이기 때문에 통계정보가 없더라도 아래와 같은 방식으로 잘 작동한다.

SYS@ora10g>select max(empno) from scott.emp ;

-----------------------------------------------------------------------------------------
| Id  | Operation                  | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |            |     1 |     4 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |            |     1 |     4 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| PK_EMP_IDX |    15 |    60 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

9i 이전 버전에서 통계정보를 지우고 수행해 보면 아래와 같은 결과가 나타나므로 주의해야 한다.

select max(empno) from emp ;

Rows    Row  Source   Operation
-----   -----------------------------------
   1    SORT AGGREGATE
   14    INDEX FULL SCAN EMP_PK

문서에 대하여


# 이 문서는 오라클클럽에서 작성하였습니다.
# 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
# 출처 : http://www.gurubee.net/pages/viewpage.action?pageId=6259429&
※ 위 내용은, 여러 자료를 참고하거나 제가 주관적으로 정리한 것입니다.
   잘못된 정보나 보완이 필요한 부분을, 댓글 또는 메일로 보내주시면 많은 도움이 되겠습니다.
09 26, 2010 23:27 09 26, 2010 23:27


Trackback URL : http://develop.sunshiny.co.kr/trackback/547

Leave a comment

« Previous : 1 : ... 336 : 337 : 338 : 339 : 340 : 341 : 342 : 343 : 344 : ... 648 : Next »

Recent Posts

  1. HDFS - Python Encoding 오류 처리
  2. HP - Vertica ROS Container 관련 오류...
  3. HDFS - Hive 실행시 System Time 오류
  4. HP - Vertica 사용자 쿼리 이력 테이블...
  5. Client에서 HDFS 환경의 데이터 처리시...

Recent Comments

  1. Article writing is also a exciteme... 레깅스룸 02 16,
  2. I like reading an article that can... 부산고구려 02 16,
  3. Asking questions are truly nice th... 해운대룸싸롱 02 15,
  4. Pretty! This was an extremely wond... 분당셔츠룸 02 15,
  5. Wonderful beat ! I would like to a... 레깅스룸가격 02 14,

Recent Trackbacks

  1. london relocation ltd london relocation ltd 16 02
  2. extract your knowledge extract your knowledge 16 02
  3. dog cart dog cart 15 02
  4. This Site This Site 15 02
  5. massive action massive action 15 02

Calendar

«   02 2020   »
            1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29

Bookmarks

  1. 위키피디아
  2. MysqlKorea
  3. 오라클 클럽
  4. API - Java
  5. Apache Hadoop API
  6. Apache Software Foundation
  7. HDFS 생태계 솔루션
  8. DNSBL - Spam Database Lookup
  9. Ready System
  10. Solaris Freeware
  11. Linux-Site
  12. 윈디하나의 솔라나라

Site Stats

TOTAL 2846556 HIT
TODAY 118 HIT
YESTERDAY 544 HIT