문서의 선택한 두 판 사이의 차이를 보여줍니다.
양쪽 이전 판 이전 판 | 마지막 판 양쪽 다음 판 | ||
study:oracle:datadb:2week_1 [2010/05/13 10:20] starlits |
study:oracle:datadb:2week_1 [2010/05/13 10:25] starlits |
||
---|---|---|---|
줄 1: | 줄 1: | ||
====== 제 3장 SQL의 실행계획 ======= | ====== 제 3장 SQL의 실행계획 ======= | ||
- | - | + | * |
- | + | ||
- | ===== 3.1 SQL과 옵티마이져 | + | |
- | + | ||
- | | + | |
- | - | + | |
- | * SQL은 결과에 대한 요구 | + | |
- | * 옵티마이져는 없는 길을 생성해 주는 것이 아님. 이미 존재하는 길을 단지 찾아줄 뿐 => | + | |
- | + | ||
- | + | ||
- | ==== 3.1.1 옵티마이져와 우리의 역할 | + | |
- | + | ||
- | - 숫자 목록 관계형 데이터베이스의 출현배경 : 데이터들 간에 물리적인 연결고리를 가지지 않아도 논리적인 연결고리만 있다면 원하는 데이터를 액세스 할 수 있다. | + | |
- | - SQL? 요구서에 불과함. 표준언어로 채택한 것 | + | |
- | - 옵티마이져 ? 사용자의 요구에 따라 그 때마다 최적의 경로를 찾아 주는 역할 책임자 | + | |
- | - 옵티마이져 실행계획에 영향을 주는 것들 | + | |
- | - 옵티마이져는 단지 이미 존재하는 길들 중에서 하나를 선택해 주는 역할 => 사용자의 역할이 중요 | + | |
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | ==== 3.1.2. 옵티마이져의 형태 ==== | + | |
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | 가. 규칙기반 옵티마이져 (RBO : Rule_based Optimizer) | + | |
- | - 인덱스 구조나 사용 연산자에 부여된 순위로써 최적경로 결정 | + | |
- | ① ROWID로 1로우 액세스 | + | |
- | ② 클러스터 조인에 의한 1로우 액세스 | + | |
- | ③ Unigue HASH Cluster에 의한 1로우 액세스 | + | |
- | ④ Unique INDEX에 의한 1로우 액세스 | + | |
- | ⑤ CLUSTER 조인 | + | |
- | ⑥ Non Unique Hash Cluster Key | + | |
- | ⑦ Non Unique Cluster Key | + | |
- | ⑧ Non Unique 결합 인덱스 | + | |
- | ⑨ Non Unique 한 컬럼 인덱스 | + | |
- | ⑩ 인덱스에 의한 범위처리 | + | |
- | ⑪ 인덱스에 의한 전체범위 처리 | + | |
- | ⑫ Sort Merge 조인 | + | |
- | ⑬ 인덱스 컬럼의 MIN, MAX처리 | + | |
- | ⑭ 인덱스 컬럼의 ORDER BY | + | |
- | ⑮ 전체 테이블 스캔 | + | |
- | + | ||
- | * 단점 | + | |
- | - 통계정보를 전혀 가지지 않음 | + | |
- | - 경우에 따라 비현실적인 처리경로 수립 | + | |
- | + | ||
- | + | ||
- | + | ||
- | | + | |
- | + | ||
- | | + | |
- | + | ||
- | + | ||
- | * 장점 | + | |
- | - 수립될 처리경로 예측가능 | + | |
- | - 사용자가 원하는 처리경로로 유도하기가 용이 | + | |
- | - 일반적인 보편타당성이 있음(신뢰성) | + | |
- | + | ||
- | + | ||
- | 나. 비용기준 옵티마이져(Cost_based Optimizer) | + | |
- | ① 관계형 데이터베이스가 추구하는 이상형 | + | |
- | ② 처리방법들에 대한 비용을 미리 산정해보고 그 중 가장 적은 비용이 들어가는 처리방법 선택 | + | |
- | ③ 통계정보의 형태와 종류는 DBMS 제품이나 버전에 따라 차이 | + | |
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | * 장점 | + | |
- | + | ||
- | - 현실을 감안한 최적화 | + | |
- | - 위의 예시에서 B인덱스 조건이 A조건보다 적은 범위를 처리하고 있다면 옵티마이져는 B인덱스를 처리주관 조건으로 선택 | + | |
- | - 분포도 : 통계정보에서 가장 주용한 정보 | + | |
- | * 분포도가 좋다-> 컬럼값의 종류가 많다 -> | + | |
- | - 컬럼의 모든 값들에 대해 분포도를 가지면 정확하겠지만 불가능 => | + | |
- | - 컬럼값에 저장할 분포도의 종류는 버켓(Bucker)의 개수에 따라 결정 | + | |
- | * 넓이균형 히스토그램(Width_balanced Histogram) :버켓에 저장할 분포도는 컬럼값의 최소치와 최대치에 대해 균등한 범위로 분할하여 각 범위별로 보유한 로우의 수를 보관 (컬럼값의 종류가 적거나 분포도의 편차가 심하지 않는 경우) | + | |
- | * 높이균형 히스토그램(Height_balanced Histogram) : 총 로우 수를 버켓수만큼으로 나누어 각 버켓에 저장되는 값의 범위를 기록 (분포도나 컬럼값의 편차가 심한 경우) | + | |
- | - 통계정보의 관리를 통한 제어 | + | |
- | - 통계정보를 어떻게 관리할 것이냐을 고심 | + | |
- | - 테이블마다 통계정보를 관리할 수 없음 => 테이블을 모니터링하는 기능 이용 ( USER_TAB_MODIFICATION와 같은 딕셔너리 뷰를 통해 확인) | + | |
- | - 만약 통계테이블을 수동으로 관리 하고자 한다면 수집방법, | + | |
- | - 최악의 상황이 발생할 확률의 감소 | + | |
- | - 적절하지 못한 인덱스 구성, 고품질의 SQL을 사용하지 못해도 대부분 최소한의 수행 속도 보장 | + | |
- | - 사용자의 쿼리를 재해석하여 새로운 쿼리로 변형시킴 | + | |
- | + | ||
- | + | ||
- | * 단점 | + | |
- | - 실행계획 예측의 곤란 | + | |
- | - 사용자가 예측불가능하므로 애플리케이션을 작성할 때 미리 적절한 대응하기 어렵고, 종합적인 전략을 수립하기 곤란 | + | |
- | - 버전에 따른 변화 | + | |
- | - 실행계획 제어가 곤란 | + | |
- | + | ||
- | + | ||
- | 다. 옵티마이져의 발전 방향 | + | |
- | - 비용기준 옵티마이져로 발전해 나간다. | + | |
- | - 단위SQL 보다는 테이블이나 인덱스 단위로 관리 | + | |
- | + | ||
- | | + | |
- | - 통계정보의 수집과 관리를 위해 DBMS에서 제공하는 패키지들이 계속 보강되고 있음 | + | |
- | - 과거에는 I/O에 비중. 최근에는 CPU의 수행정도와 사용량을 추가함 | + | |
- | - 통계정보의 보다 간편한 수집이나 관리를 위해 DBMS_STATS 패키지 이용하면 좋음 | + | |
- | => 테이블이나 인덱스와 같은 데이터베이스 오브젝트에 대한 통계정보 생성을 용이하게 해줌. 테이블이나 인덱스와 관련된 통계정보를 생성할 수 있으며, 견본데이터를 추출하여 통계정보를 생성하는것도 가능. 대용량 테이블이라면 모든 데이터를 대상으로 통계정보를 수집할 수 없으므로 좋은 방법 | + | |
- | 지정 파라메터 : ESTIMATE_PERCENT 로 직접 DBMS_STATS.AUTO_SAMPLE_SIZE 로 자동으로 판단 | + | |
- | => 통계정보 수집시간을 단축하기 위해 병렬로 처리 가능 | + | |
- | | + | |
- | + | ||
- | · 초기의 비용기준 옵티마이져는 특정 상황에 맞추어져 있기 때문에 문제 | + | |
- | | + | |
- | 지정한 기간동안 시스템에서 실제로 일어나고 있는 활동들을 모니터링하고 분석을 | + | |
- | · 통계정보 교체하는 방법 | + | |
- | | + | |
- | · 시스템 통계의 개선은 테이블이나 인덱스 통계정보와는 다르게 시작되는 SQL에 | + | |
- | · 새로운 통계정보가 갱신되면 이전 버전은 자동으로 이력이 저장 => 원상회복에 유용 | + | |
- | => 딕셔너리뷰를 통해 확인 가능하며 ‘RESTORE' | + | |
- | · ‘PURGE_STATS' | + | |
- | + | ||
- | ★ 3.1.2.3 옵티마이져 목표의 선택 옵티마이져가 수행하는 최적화 작업이란 일종의 시뮬레이션이다. 시뮬에이션이란 어떠한 현상이나 사건을 모형화하여 가상으로 수행시켜 봄으로써 실제 상황에서의 결과를 예측하는 것.-옵티마이져 모드의 종류 | + | |
- | + | ||
- | + | ||
- | + | ||
- | ★ | + | |
- | ① 기존의 아우트라인에서 새로운 개별 아우트라인으로 복제 | + | |
- | | + | |
- | ② 아우트라인을 수정할 수 있는 툴이나 유_OUTLN_EDIT패키지에 있는 여러 | + | |
- | ③ 생성된 개별 아우트라인을 검증하기 위해 USE_PRIVATE_OUTLINES을 TRUE로 | + | |
- | CREATE OR REPLACE OUTLINE outln_1 FROM PRIVATE prv_01_1; | + | |
- | 3.1.2.4 실행계획의 고정화최적의 실행계획을 적용할려고 해도 옵티마이져가 고집을 부릴 때 제어필요=> | + | |
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | p143쪽 그림과 설명 | + | |
- | - 아우트라인의 관찰 | + | |
- | + | ||
- | + | ||
- | - 옵티마이져 업그레이드 시의 적용 | + | |
- | | + | |
- | + | ||
- | ① 현재의 규칙기준으로 수행되는 SQL에 대하여 특정 카테고리를 주고 아우트 라인 생성 | + | |
- | ALTER SESSION SET CREATE_STORED_OUTLINES =category_name; | + | |
- | 전체적으로 원한다면 ALTER SYSTEM 으로 한다. | + | |
- | ② 수집기간을 적절하게 한다. | + | |
- | ③ 아우트라인 생성을 종료시키려면 CREATE_STORED_OUTLINES파라메터를 ' | + | |
- | ④ DBMS_STATS 패키지를 이용하여 통계정보 생성 | + | |
- | ⑤ 옵티마이져 모드를 RULE에서 CHOOSE로 변경 | + | |
- | ⑥ ALTER SESSION 명령어를 이용하여 USE_STORED_OUTLINES 파라메터에 앞서 생성해 두었던 카페고리를 지정하여 아우트라인 적용( ALTER SYSTEM) | + | |
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | ⓑ옵티마이저 버전 업그레이드 위 ①~③번 수행후 애플리케이션을 수행하면서 테스트 실시 | + | |
- | 업그레이드 후 문제가 발생되면 아우트라인을 적용하여 과거 버전의 수행계획을 고수 -> | + | |
- | · 아우트라인 적용방법 | + | |
- | + | ||
- | ① 원하는 대상 SQL에 대해 원하는 실행계획을 아우트라인으로 생성 | + | |
- | ALTER OUTLINES outline_name CHANGE CATEGORY TO out_category_name; | + | |
- | + | ||
- | ② ALTER SESSION SET USE_STORED_OUTLINES=our_category_name을 통해 아우트라인 적용 | + | |
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | ★ 3.1.2.5. 옵티마이져의 한계 | + | |
- | ★ 3.1.3 옵티마이져의 최적화 절차-옵티마이져의 처리 과정 ⑴ 최조에 사용자가 실행한 SQL은 데이터베이스의 데이터딕셔너리를 참조하여 파싱. | + | |
+ | - [[study: | ||
+ | - [[study: | ||
+ | - [[study: | ||
+ | - [[study: | ||
+ | - [[study: |