문서의 이전 판입니다!
제 3장 SQL의 실행계획
-실행계획의 중요성? 액세스 효율에 가장 결정적인 영향을 미침
★ 3.1 SQL과 옵티마이져-옵티마이져 목표 : SQL로 요구된 결과를 최소의 비용으로 처리할 수 있는 처리 경로를 결정- SQL과 Optimizer의 관계 · SQL은 결과에 대한 요구 · 옵티마이져는 없는 길을 생성해 주는 것이 아니라 이미 존재하는 길을 단지 찾아 줄 뿐 ⇒사용자가 부여한 영향 요소에 따라 논리적으로 존재하는 최적이 달라짐
★ 3.1.1 옵티마이져와 우리의 역할- 관계형 데이터베이스의 출현배경 : 데이터들 간에 물리적인 연결고리를 가지지 않아도 논리적인 연결고리만 있다면 원하는 데이터를 액세스 할 수 있다. ⇒논리적인 방법만으로 데이터를 찾을 수 있는 장치가 필요 - SQL? 요구서에 불과함. 표준언어로 채택한 것 - 옵티마이져 ? 사용자의 요구에 따라 그 때마다 최적의 경로를 찾아 주는 역할 책임자- 옵티마이져 실행계획에 영향을 주는 것들
옵티마이져는 단지 이미 존재하는 길들 중에서 하나를 선택해 주는 역할 => 사용자의 역할이 중요
★ ① 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 ⑮ 전체 테이블 스캔 3.1.2. 옵티마이져의 형태① 규칙기반 옵티마이져 (RBO : Rule_based Optimizer) - 인덱스 구조나 사용 연산자에 부여된 순위로써 최적경로 결정
EX> 1,000,000 로우를 가지는 TAB1테이블과 100로우를 가지는 TAB2테이블의 로우수를 알지 못하기 때문에 어느쪽이 전체 테이블 스캔 하는것이 유리한지 구별할 수 없다. EX> 2종류의 값만 가지고 있어 평균분포도가 50%인 컬럼 A로 구성된 인덱스와 10,000종류를 가지고 있어 0.01%의 평균 분포도를 가진 컬럼 B로 구성된 인덱스가 존재. A='10' AND B LIKE '123%'을 주었을때 무조건 ‘=’로 사용한 컬럼A를 사용하는 것이 더 유리하다고 판단.
· 통계정보를 전혀 가지지 않음 · 경우에 따라 비현실적인 처리경로 수립 - 장점 · 수립될 처리경로 예측가능 · 사용자가 원하는 처리경로로 유도하기가 용이 · 일반적인 보편타당성이 있음(신뢰성)
② 비용기준 옵티마이져(Cost_based Optimizer)
처리방법 선택
⇒통계정보는 테이블의 로우 수와 블록 수, 블록당 평균 로우수, 로우의 평균길이,
컬럼별 상수값의 종류, 분포도, 컬럼 내 NULL값의 수, 클러스터링 팩터,
① 현실을 감안한 최적화
분포도가 좋다→ 컬럼값의 종류가 많다 →조건을 만족하는 처리범위가 좁다는 것을 의미하므로 일의 양을 결정하는 가장 중요한 요소
⇒컬럼값의 범위별로 분포도 보유하는 방법
· 넓이균형 히스토그램(Width_balanced Histogram) :버켓에 저장할 분포도는 컬럼값의 최소치와 최대치에 대해 균등한 범위로 분할하여 각 범위별로 보유한 로우의 수를 보관 (컬럼값의 종류가 적거나 분포도의 편차가 심하지 않는 경우)
· 높이균형 히스토그램(Height_balanced Histogram) : 총 로우 수를 버켓 수만큼으로 나누어 각 버켓에 저장되는 값의 범위를 기록 (분포도나 컬럼값의 편차가 심한 경우) => 두가지 방법은 사용자가 선택 할 수 없음. 단 버켓 수 지정 가능 그러나 DBMS의 패키지 dbms_stats 등의 프로시져를 활용하여 자동 결정
② 통계정보의 관리를 통한 제어
USER_TAB_MODIFICATION와 같은 딕셔너리 뷰를 통해 확인
⇒스크립트 작성, 작업스케줄링 할 수 있는 S/W활용,
제공된 프로시져(GRTHER_DATABASE_STAT 등)
③ 최악의 상황이 발생할 확률의 감소
인덱스의 길이, 최대 최소값, 리프 블록수, 가동 시스템의 I/O나 CPU 사용정보 등 - 장점
① 실행계획 예측의 곤란
② 버전에 따른 변화 ③ 실행계획 제어가 곤란 -단점
- 옵티마이져의 발전 방향
· 비용기준 옵티마이져로 발전해 나간다. · 단위SQL 보다는 테이블이나 인덱스 단위로 관리
-통계정보 관리를 위한 제언
· 통계정보의 수집과 관리를 위해 DBMS에서 제공하는 패키지들이 계속 보강되고 있음 · 과거에는 I/O에 비중. 최근에는 CPU의 수행정도와 사용량을 추가함 · 통계정보의 보다 간편한 수집이나 관리를 위해 DBMS_STATS 패키지 이용하면 좋음 => 테이블이나 인덱스와 같은 데이터베이스 오브젝트에 대한 통계정보 생성을 용이하게 해줌. 테이블이나 인덱스와 관련된 통계정보를 생성할 수 있으며, 견본 데이터를 추출하여 통계정보를 생성하는것도 가능. 대용량 테이블이라면 모든 데이터를 대상으로 통계정보를 수집할 수 없으므로 좋은 방법 => 견본데이터는 5% 이하로 선정하는게 바람직 지정 파라메터 : ESTIMATE_PERCENT 로 직접 DBMS_STATS.AUTO_SAMPLE_SIZE 로 자동으로 판단 => 통계정보 수집시간을 단축하기 위해 병렬로 처리 가능 병렬등급(Degree)은 직접 지정/ 프로시져(Auto_dergree)를 이용하여 자동으로 지정
· 초기의 비용기준 옵티마이져는 특정 상황에 맞추어져 있기 때문에 문제 =>시스템 통계(System statistics) : 해당 시스템이 가지고 있는 성능특성을 실제로 측정함으로 보다 정확한 정보를 옵티마이져에게 제공. 시스템 통계를 생성할 때는 지정한 기간동안 시스템에서 실제로 일어나고 있는 활동들을 모니터링하고 분석을 해야 한다. =>관련 프로시져(DBMS_STATS.GATHER_SYSTEM.STATS)를 수행시켜 통계정보를 수집해 주어야 함
· 통계정보 교체하는 방법
=>DBMS_STATS패지지의 ‘IMPORT_SYSTEM_STATS' 프로시져를 사용하면 간단. 이 작업은 ’DBMS_JOB'패키지를 이용하여 작업을 등록하고 적당한 시간에 자동으로 수행되도록 함
· 시스템 통계의 개선은 테이블이나 인덱스 통계정보와는 다르게 시작되는 SQL에 대해서만 새로운 통계정보로 파싱할 뿐