[기타SQL]/SQL tunning

4. oracle optimizer(미완)

JJ* 2014. 12. 17. 12:18

1. Optimizer 개요

▶Optimizer의 역할

* SQL은 선언적 언어이다.

 - 절차적(procedural)언어는 모든 처리 과정을 사용자가 기술한다.

 - 선언적(Declarative)언어인 SQL문장이 표현하는 것은 : 원하는 데이터의 집합이지, 그 데이터를 얻기위한 구체적인 방법이 아니다.

* 하나의 SQL을 수행하는 방법은 매우 다양한데, 그 다양한 방법 중 어떤 것을 이용하느냐에 따라 실행 효율에 커다란 차이가 존재한다.

* SQL의 실행계획을 수립하는데, 가장 효율적인 실행계획을 찾는 것이 목표이다.

- 다양한 요소들이 전체적으로 고려되어야 하는 복잡한 문제로, RDBMS의 핵심요소이다.

* 최적화에는 크게 두가지의 접근방법이 존재한다.

 Rule-based optimization (RBO) 

 미리 정해진 규칙에 따라 실행 계획을 수립 (ex.수동카메라)

 Cost-based optimization (CBO)

 실행비용(cost)의 예측을 통한 지능적인 실행계획의 수립 (ex.자동카메라)

 

 

▶SQL처리구조와 optimizer

 

 

 

▶Optimizer가 수행하는 연산

* 표현식(expression) 및 조건 평가

 - 상수를 포함하는 수식 또는 조건이 있는 경우 그에 대해 가능한 계산을 모두 수행

* 효율적인 수행을 위해 필요한 경우 주어진 SQL문장을 동등한 다른 SQL문장으로 변환

 - OR Expansion

 - Subquery Unnesting

 - View Merging

 - Predicate pushing

 - query rewrite

* 최적화 접근방법 및 최적화 목표의 결정

 - RBO 또는 CBO

 - CBO를 선택한 경우 → 전체 처리의 최적화? 아니면 가장 빠른 응답?

* 실행 계획의 결정

 - 최적의 접근경로

 - 최적의 join순서

 - 최적의 join방식

 

▶SQL실행계획

* 하나의 SQL문장을 실행하기 위해 oracle 서버는 여러 단계의 작업들을 수행하는데, 이러한 step들의 조합이 실행계획(Execution plan)이다.

* 실행계획을 구성하는 가장 중요한 요소 - 접근경로 / join순서 / join방식

* ex) 다음과 같은 SQL문장을 실행하는 경우

- 항상 바로 붙어있는 두개만 비교하면 된다.

- 순서 : 3→5→4→6

- 설명 : 붙어있는 3과 4만 비교했을 때 3이 먼저이므로, 3부터하고, 4와 5비교해서 5가 그다음인 것이다. 3과 5를 비교하는 것 아님!

 

 

2. Rule-based optimization(RBO)

▶Rule-based optimization(RBO)의 특징

* 미리 정해진 규칙에 의한 실행 계획 결정 → 상식에 의거하지만 융통성은 없다.

 - 연산자 우선순위

 - 접근 경로의 우선순위

 - SQL문장의 syntax규칙

* 데이터에 대한 통계 내지 실제 SQL을 수행할 경우에 소요될 비용에 대한 고려를 하지 않음.

 ex) 인덱스가 있다면 무조건 사용한다. 테이블의 크기, 인덱스의 효율등은 무시해서 인덱스를 사용하지 않아야 더 빠를 수도 있는데..

* oracle 버전 6이하와의 역 호환성을 위해 지원

 

▶RBO 순위 결정 방식 - 밑에서부터 시작한다.

 순위

 접근경로 

 1

 Single row by ROWID

 2

 Single row by cluster join 

 3

 Single row by hash cluster key with unique or primary key 

 4

 Single row by unique or primary key 

 5

 cluster join 

 6

 Hash cluster key 

 7

 Indexed cluster key 

 8

 Composite index 

 9

 Single-column index 

 10

 Bounded range search on indexed columns 

 11

 Unbounded range search on indexed columns 

 12

 sort-merge join 

 13

 MAX or MIN of indexed column 

 14

 ORDER BY on indexed column 

 15

 Full table scan 

 

 

 

3. Cost-based optimization(CBO)

▶Cost-based optimization(CBO)의 특징

* 데이터에 대한 각종 통계를 사용한다.

* 실제 SQL을 수행할 때 소용될 비용을 예측하고 그 값을 기준으로 실행계획결정 → 보다 현실적이고 지능적인 판단 내린다.

 - I/O 횟수

 - CPU사용량

 - 메모리 사용량

 - 네트워크 전송량

* oracle7에서 처음 도입.

 - 지속적으로 향상.

 - 일반적으로 RBO보다 우수하다.

 

▶ CBO에서만 사용가능한 기능들

Ÿ테이블 및 인덱스의 Partitioning

Ÿ인덱스 구성 테이블 (Index-organized table)
ŸReverse key 인덱스
ŸFunction-based 인덱스
ŸSELECT 문장에서의 SAMPLE 절
Ÿ병렬 Query 및 병렬 DML
ŸStar Join 및 Star 변형
ŸOptimizer 확장
ŸMaterialized View를 이용한 Query rewrite
ŸEnterprise Manager progress meter
Ÿ해쉬 Join
Ÿbitmap 인덱스 및 bitmap Join 인덱스
Ÿ인덱스 skip scan 알고리즘

 

 

▶RBO와 CBO의 차이점 ★★ (다시 조사)

CBO가 개선이됐지만 통계정보등의 문제점이 있다. 해결하려면 대안도 얘기해줘야된다.

 

 

 

4. Optimizer 동작의 조정

▶Optimizer 모드 및 목표의 선택

* oracle 9i optimizer의 기본동작

→ 통계 정보를 update 많이 쳐줘야 한다. 그러면 속도가 느려져.(11g부터는 자동으로 업데이트치는 기술 등장AWR?)

 - CBO : 최소한 하나의 참조 개체에 대한 통계가 준비되어 있다면 사용한다. (일반적으로 사용)

 - 기본적으로는 최적의 throughput이 목표이다. (전체 row의 빠른 처리를 지향)

 - 빠른 Response time을 목표로 할 수 있도록 조정 가능 (처음 몇 row의 반환을 지향) 

 - RBO : 해당 SQL문이 참조하는 개체들에 대해 사용할 수 있는 통계가 전혀 없는 경우 사용한다.

*사용자도 optimizer 모드 및 목표를 설정할 수 있다.

 인스턴스 레벨 

 OPTIMIZER_MODE 파라미터의 설정 (파라미터 파일의 수정 또는 alter system 명령) 

 세션 레벨

 OPTIMIZER_MODE 파라미터의 설정 (alter session 명령)

 명령문 레벨

 optimizer hint 사용

 

▶인스턴스 또는 세션 레벨

 OPTIMIZER_MODE 파라미터

 CHOOSE

 oracle9i optimizer의 기본 동작에 따름. (default 값)

 RULE 

 통계 존재 여부에 상관없이 RBO 사용.

 ALL_ROWS 

 전체 처리율의 최적화

 FIRST_ROWS[_n] 

 처음 결과가 나올 때까지의 시간을 줄이기 위해 최적화.

 n=1, 10, 100, 또는 1000

 

▶SQL 문장 레벨 

 Optimizer hint

 /*+ CHOOSE */

 oracle 9i optimizer의 기본동작에 따름. 

 /*+ RULE */ 

 통계 존재 여부에 상관없이 RBO사용. 

 /*+ ALL_ROWS */ 

 전체 처리율의 최적화 

 /*+ FIRST_ROWS[(n)] */ 

 처음 결과가 나올 때까지의 시간을 위해 최적화 (n=임의의 자연수) 

 

▶Join Key인덱스와 Join순서

 

 

74~81page

 

 

 

 

 

 

 

 

 

 

▶RBO동작의 조정

* RBO는 SQL의 syntax에 기반하며, 내장된 규칙에 따라 동작하므로, 조정이 어려우며 조정할 수 있는 범위 또한 제한적이다.

 - FROM 절에서 테이블 순서 변경

 - WHERE 절에서 순서 변경

 - 인덱스의 생성 및 삭제

 - 기타 syntax 트릭의 사용

 *참조* syntax트릭에 대해서.. 

 

 select * from emp

 where ename='FORD'

 and sal between 1000 and 2000

 → 상식적으로는 sal이 1000과 2000사이인 데이터보다 ename이 FORD인 데이터가 더 적을 것이라 예상하기 때문에, RBO는 앞에 있는 문장(인덱스 있는 것)을 먼저 읽게 된다. 그런데 예상외로 ename이 FORD인 데이터가 더 많을 수도 있다. 그래서 DBA가 미리 예상하고 쿼리를 아래처럼 바꿔줘야한다. 

 

 select * from emp

 where substr(ename,1,4)='FORD'

 and sal between 1000 and 2000

 → 인덱스 걸려있는 것에 함수를 써버리면, RBO가 인덱스를 못쓰니까 sal을 먼저 읽게 만들 수도 있다.

 

 

▶ CBO 동작의 조정

 * 데이터에 대한 통계 수집 (가장 원칙적인 방법)

  - CBO가 최적의 실행계획을 산출하기 위해서 정확한 통계 정보의 제공이 필수적이다.

 * Hint의 사용 - SQL문장 레벨

 * CBO의 행동에 영향을 주는 파라미터 설정 - 인스턴스 또는 세션 레벨 

 

 Optimizer Hint 

 * 실행 계획의 결정은 원래 optimizer의 역할이나 optimizer가 반드시 최적의 실행계획만을 산출하는 것은 아니다.

 * 사용자가 optimizer가 알 수 없는 정보를 알고 있을 수 있으므로 사용자는 Hint를 이용하여 optimizer의 결정에 영향을 미칠 수 있다.

 * 필요한 경우 Hint를 전역적으로 저장하는 방법에 의해 실행 계획의 일관성을 제공해주는 Stored outline 기능을 사용할 수 있다.

 * Hint는 /*+ RULE */ 을 제외하면 항상 CBO를 호출한다.

 hint문법
 

 select /*+ index(e idx_emp_ename) */    -- 올바른 문법

 select /* + index(e idx_emp_ename) */    -- 이렇게 /*+ 사이에 공백이 들어가면 안된다.

 

 

 Hint 사용의 규칙

 * SQL 블록의 첫 키워드 바로 뒤에 입력

 * 각 블록에는 하나의 힌트 주석만 있어야 하지만, 하나의 힌트 주석은 여러 개의 힌트 포함 가능하다.

  ex) /*+ a b c */

 * 힌트는 해당 블록에만 적용

 * 문장에 alias 를 사용하는 경우 힌트는 그 alias를 참조해야 한다.

 * 힌트는 Optimizer에 의해 무시될 수 있다.

  - syntax 또는 semantics상 잘못 지정된 hint

  - 비효율적인 실행 계획을 지정하는 hint

 Hint 사용 예
 

 UPDATE /*+ INDEX(e EMPLOYEES_JOB_IX) */
       employees e
   SET e.salary =
       (SELECT /*+ INDEX(m EMPLOYEES_SALARY_IX) */
              (e.salary + m.salary)/2
          FROM employees m
         WHERE m.emp_id = e.mgr_id   
           AND m.salary > e.salary)
 WHERE e.job = 'INSTRUCTOR'
   AND e.hiredate > TO_DATE('01/01/1998',

                            'dd/mm/yyyy');

 

 CBO에 영향을 주는 파라미터들 

 ŸOPTIMIZER_MODE

 ŸOPTIMIZER_FEATURES_ENABLE
 ŸCURSOR_SHARING
 ŸDB_FILE_MULTIBLOCK_READ_COUNT
 ŸSORT_AREA_SIZE, HASH_AREA_SIZE, PGA_AGGREGATE_TARGET 
 ŸHASH_JOIN_ENABLED
 ŸOPTIMIZER_INDEX_CACHING
 ŸOPTIMIZER_INDEX_COST_ADJ
 ŸOPTIMIZER_MAX_PERMUTATIONS
 ŸPARTITION_VIEW_ENABLED
 ŸQUERY_REWRITE_ENABLED
 ŸSTAR_TRANSFORMATION_ENABLED

 

 

5. CBO 통계의 수집

▶CBO 내부 구조

 

* Estimator : 다음의 것들을 예측한다. (caldinality, selectivity, cost)

* plan의 조사는 정교한 내부 알고리즘에 의해 일정시간 내에 완료한다.

* 최종적으로 cost가 가장 작은 plan이 선택된다.

 

▶Cost 계산의 기본 요소 : 데이터 볼륨

* cardinality : 어떤 row set에 속하는 row들의 개수

* selectivity(선택도) : 어떤 row set에 특정한 조건을 적용하는 경우 그 조건을 만족하는 row들의 비율

 - Query의 술어(predicate), 칼럼의 값들의 분포도 들과 밀접한 관련이 있다.

 

▶CBO와 통계정보

* cardinality, selectivity, 궁극적으로 cost계산을 위해서는 데이터의 정확한 통계정보가 필요하다.

* 통계정보는 static dictionary에 저장하는데, 자동으로 되지 않으므로 사용자에 의한 수집이 필요하다.

 - ANLYZE 명령과 DBMS_STATS 패키지 (단, 10g부터는 AWR이라는 기능 때문에 정해진 시간에 자동으로 update친다.)

 

 ANLYZE 명령 

 

- estimate : 기존에 만들어져 있는 통계정보가 유효한지 안한지 검증한다.

 

 

 DBMS_STATS패키지

 * oracle 8.1.6에서 도입

 * 통계 정보의 수집 외에도 개별 통계값의 설정, Dictionary와 사용자 정의 테이블 간의 통계 이동과 같은 기능도 제공한다.

 * ANALYZE에 비해 우월하다.

  - 다양한 작업이 가능

  - 테이블, 인덱스 단위 뿐 아니라 schema, 데이터베이스 전체에 대한 통계 수집 가능

  - 통계 수집 작업의 병렬화 가능

 

▶통계 정보 수집 예

 ANALYZE 명령을 사용하는 경우 

 ANALYZE TABLE sh.customers

 ESTIMATE STATISTICS ;

 DBMS_STATS 패키지를 사용하는 경우 

 dbms_stats.GATHER_TABLE_STATS (

   ‘sh‘,  -- schema

   ‘customers‘,  -- table

   NULL,  -- partition

   20,  -- sample size(%)

   TRUE,  -- block sample?

   'FOR ALL COLUMNS‘,  -- column spec

   4,  -- degree of parallelism

   'DEFAULT‘,  -- granularity

   TRUE  -- cascade to indexes

 );

 

▶테이블 통계 (아직은 세부적인 내용 몰라도되)

 DBA_TABLES

 ALL_TABLES

 USER_TABLES

- 행 수

- 사용된 블록 수 (항상 정확)

- 빈 블록 수 (항상 정확)

- 블록 내의 평균 사용 가능 공간

- 체인화 되거나 이전된 행 수

- 평균 행 길이

- 마지막 ANALYZE 날짜 및 표본 크기

 

▶인덱스 통계 (아직은 세부적인 내용 몰라도되)

 DBA_INDEXES

 ALL_INDEXES

 USER_INDEXES

- 인덱스 레벨 (항상 정확)

- Leaf 블록 수

- Distinct key의 수

- key당 평균 leaf 블록 수

- key당 평균 데이터 블록 수

- 인덱스 항목 수

- clustering factor

- 마지막 ANALYZE 날짜 및 표본 크기

 

▶데이터 분포와 Histogram

* 데이터의 분포가 균등한 경우 동등 조건의 selectivity

 selectivity = 1 / # distinct values 

- 하지만 실제 데이터의 분포도는 그렇지 않을 수 있다. 따라서 데이터 분포의 비대칭성을 나타낼 수 있는 통계 정보가 필요.

 

▶Histogram의 생성

 ANALYZE 명령을 이용한 histogram 생성 

 ANALYZE TABLE 테이블명

 COMPUTE STATISTICS FOR COLUMNS 칼럼명 SIZE n;

 DBMS_STATS 패키지를 이용한 histogram 생성

 EXECUTE DBMS_STATS.GATHER_TABLE_STATS (
   ‘schema_name’,
   ‘table_name’,
   METHOD_OPT => ‘FOR COLUMNS SIZE n column_name’
 );

 

▶인덱스와 데이터 분포도 예제

 

  → 통계정보도 있고, 인덱스도 존재하는데, 실행계획을 인덱스로 하지 않고, full scan했다. 왜냐하면 데이터의 분포도에 대한 정확한 통계 정보가 없었기 때문이다.

 

  → 그래서 분포도를 조사해보니 REGI에 다 몰려있는 것으로 확인된다. 이런 상황을 skewed되어있다고 하는데, 이 경우에 histogram을 생성해준다.

 

  → histogram을 생성해주니 인덱스를 사용하면서 속도가 굉장히 빨라졌다.

 

  → HISTOGRAM의 문제점 : bind 변수를 쓸 수 없다.

  → bind는 사용자에게 입력을 받는 것인데, histogram으로 만들어 버리면 사용자에게 입력받으면 무조건 full scan을 해야 되므로..

 

 

▶Histogram 가이드라인

* 데이터 분포의 불균형이 심한 칼럼에만 사용

* 추가적 저장 공간이 필요하므로 상당한 성능 개선이 있는 경우에만 사용

* 바인드 변수를 사용하는 경우 histogram은 사용되지 않는다.

 

저작자표시 비영리 변경금지