IT/데이터자격증 기록

오라클 성능 고도화1,2 목차정리

JJo 2018. 10. 8. 14:11


SQLP자격증을 준비하면서 전문가가이드뿐만아니라 

오라클성능고도화1,2권도 참조하면서 정리해둔 목차입니다.


오라클 성능고도화2권이 SQLP자격증을 취득하는데

더 많이 보았고 관련이 있습니다.


도움되셨으면 좋겠습니다.






오라클 성능 고도화1

1장 오라클 아키텍처
  • 기본 아키텍처
  • DB 버퍼 캐시
  • 버퍼 Lock
  • Redo
  • Undo
  • 문장수준 읽기 일관성
  • Consistent vs. Current 모드 읽기
  • 블록 클린아웃
  • Snapshot too old
  • 대기 이벤트
  • Shared Pool

2장 트랜잭션과 Lock
  • 트랜잭션 동시성 제어
  • 트랜잭션 수준 읽기 일관성
  • 비관적 vs. 낙관적 동시성 제어
  • 동시성 구현 사례
  • 오라클 Lock

3장 오라클 성능관리

4장 라이브러리 캐시 최적화 원리
  • SQL과 옵티마이저
  • SQL 처리과정
  • 라이브러리 캐시 구조
  • 커서 공유
  • 바인드 변수의 중요성
  • 바인드 변수의 부작용과 해법
  • 세션 커서 캐싱
  • 애플리케이션 커서 캐싱
  • Static vs. Dynamic SQL
  • Dynamic SQL 사용 기준
  • Static SQL을 구현하기 위한 기법들

5장 데이터베이스 Call 최소화 원리
  • Call 통계
  • User Call vs. Recursive Call
  • 데이터베이스 Call이 성능에 미치는 영향
  • Array Processing활용
  • Fetch Call 최소화
  • 페이지 처리의 중요성
  • PL/SQL 함수의 특징과 성능 부하
  • PL/SQL 함수 호출 부하 해소 방안

6장 I/O 효율화 원리
  • 블록 단위 I/O
  • Memory vs. Disk I/O
  • Single Block vs. Multiblock I/O
  • Prefetch
  • Direct Path I/O
  • RAC캐시 퓨전
  • Result 캐시
  • I/O효율화 원리




오라클 성능 고도화2

1장 인덱스 원리와 활용(17 ~ 209)
  • 인덱스 구조
    • (1) 범위 스캔
    • (2) 인덱스 기본 구조
    • (3) 인덱스 탐색
    • (4) ROWID포맷
  • 인덱스 기본 원리
    • (1) 인덱스 사용이 불가능하거나 범위 스캔이 불가능한 경우
    • (2) 인덱스 컬럼의 가공
    • (3) 묵시적 형변환
  • 다양한 인덱스 스캔 방식
    • (1) Index Range Scan
    • (2) Index Full Scan
      • 인덱스를 이용한 소트연산 대체
    • (3) Index Unique Scan
    • (4) Index Skip Scan
      • Index Skip Scan이 작동하기 위한 조건
    • (5) Index Fast Full Scan
      • Index Fast Full Scan을 활용한 튜닝 사례
    • (6) Index Range Scan Descending
    • (7) And-Equal, Index Combine, Index Join
  • 테이블 Random액세스 부하
    • (1)인덱스 rowid에 의한 테이블 액세스
    • (2)인덱스 클러스터링 팩터
      • 버퍼 Pinning에 의한 논리적 I/O감소 원리
      • 손익분기점을 극복하기 위한 기능들
  • 테이블 Random 액세스 최소화 튜닝
    • (1) 인덱스 컬럼 추가
    • (2) PK 인덱스에 컬럼 추가
    • (3) 컬럼 추가에 따른 클러스터링 팩터 변화
      • 인덱스에 컬럼을 추가하면 테이블 Random 액세스 부하를 줄이는 효과가 있지만 인덱스 클러스터링 팩터가 나빠지느 부작용이 생길 수 있다
      • 결론적으로 변별력이 좋지 않은 컬럼 뒤에 변별력이 좋은 다른 컬럼을 추가할 때는 클러스터링 팩터 변화에 주의해야한다
    • (4) 인덱스만 읽고 처리
    • (5) 버퍼 Pinning 효과 활용
    • (6) 수동으로 클러스터링 팩터 높이기
  • IOT, 클러스터 테이블 활용
    • (1) IOT란?
    • (2) IOT 언제 사용할 것인가?
    • (3) Partitioned IOT
    • (4) Overflow 영역
    • (5) Secondary 영역
    • (6) 인덱스 클러스터 테이블
    • (7) 해시 클러스터 테이블
    • (8) IOT와 클러스터 테이블을 동시에 적용한 튜닝 사례
  • 인덱스 스캔 효율(Sequential 액세스 선택도 높이는 방법)
    • (1) 비교 연산자 종류와 컬럼 순서에 따른 인덱스 레코드의 군집성
    • (2) 인덱스 선행 컬럼이 등치 조건이 아닐 때 발생하는 비효율
    • (3) Between 조건을 IN-List로 바꾸었을 때 인덱스 스캔 효율
    • (4) Index Skip Scan을 이용한 비효율 해소
    • (5) 범위 검색 조건을 남용할 때 발생하는 비효율
    • (6) 같은 컬럼에 두 개의 범위검색 조건 사용 시 주의 사항
    • (7) Between과 Like 스캔 범위 비교
      • 정확히 시작지점과 끝지점을 정해주는것이 좋다
    • (8) 선분이력의 인덱스 스캔 효율
      • 너무 어려워서 성능고도화에서 넘어감
    • (9) Access Predicate와 Filter Predicate
    • (10) Index Fragmentation
  • 인덱스 설계
    • (1) 가장 중요한 두 가지 선택 기준
    • (2) 인덱스 설계는 공식이 아닌 전략과 선택의 문제
    • (3) 결합 인덱스 컬럼 순서 결정 시, 선택도 이슈
      • 선택도가 액세스 효율에 영향을 주지 않는 경우
      • 선택도가 높은컬럼을 앞쪽에 두는 것이 유리한 경우
        • 선택도가 높은 컬럼을 선두에 두면 나중에 범위검색 조건이 사용되거나 아예 조건절에서 누락되더라도 Index Skip Scan 또는 IN - List를 활용할 수 있어 유리하다.
      • 선택도가 낮은 컬럼을 앞쪽에 두는 것이 유리한 경우
      • 선택도가 낮은 컬럼을 선택하는 것이 유리한 경우
    • (4) Sort 오퍼레이션을 생략하기 위한 컬럼 추가
  • 비트맵 인덱스

2장 조인 원리와 활용 (211 ~ 361)
  • NL조인
    • (1) 기본 메커니즘
    • (2) 힌트를 이용해 NL조인을 제어하는 방법
    • (3) NL조인 수행 과정 분석
    • (4) NL 조인의 특징
    • (5) NL 조인 튜닝 실습
    • (6) 테이블 Prefetch
    • (7) 배치 I/O
    • (8) 버퍼 Pinning효과
  • 소트머지 조인
    • (1) 기본 메커니즘
    • (2) 소트 머지 조인의 특징
    • (3) First 테이블에 소트 연산을 대체할 인덱스가 있을 때
    • (4) 조인할 First집합이 이미 정렬돼 있을 때
      • group by가 있더라도 sort oper가 일어날 수 있는데 이는 hash group by로 처리했기 때문, order by절을 명시해 sort group by 로 유도하면 First집합에 대한 Sort Join오퍼레이션은 사라진다.
  • 해시 조인
    • (1) 기본 메커니즘
      • 해시테이블이 PGA영역에 할당되므로 빠르다
    • (2) 힌트를 이용한 조인 순서 및 Build Input 조정
    • (3) 두 가지 해시 조인 알고리즘
      • Leading힌트는 먼저 읽을 테이블을 정하는 힌트고 실제 build input을 정하는 힌트는 swap_join_inputs힌트
    • (4) Build Input이 Hash Area를 초과할 때 처리방식
    • (5) Build Input 해시 키 값에 중복이 많을 때 발생하는 비효율
    • (6) 해시 조인 사용기준
      • 한 쪽 테이블이 Hash Area에 담길 정도로 충분히 작아야함
      • Build Input 해시 키 컬럼에 중복 값이 거의 없어야함

  • 조인 순서의 중요성
    • NL조인의 경우 : 선행테이블 결과건수 작고 후행테이블의 인덱스 잘되어있는게 무조건 효율 좋음
    • 소트머지조인과 해시조인의 경우 : 작은게 유리하지만 소트의 경우 디스크소트가 발생할 정도의 큰 테이블을 포함할 떄는 큰테이블을 드라이빙하는것이 조금 더 빠르다.
  • Outer 조인
    • (1) Outer NL 조인
    • (2) Outer 소트 머지 조인
    • (3) Outer 해시 조인
    • (4) Full Outer 조인
      • Full Outer Join = Left Join + Union All + Anti Join(Not Exists필터)
      • Union ALL을 이용한 Full outer join(내가 업무에 자주쓰는 방식)
  • 스칼라 서브쿼리를 이용한 조인
    • (1) 스칼라 서브쿼리
    • (2) 스칼라 서브쿼리의 캐싱 효과
    • (3) 두 개 이상의 값을 리턴하고싶을 때
  • 조인을 내포한 DML 튜닝
    • (1) 수정 가능 조인 뷰 활용`
    • (2) Merge문 활용
      • Merge Into 활용 1
      • Merge Into 활용 2
    • (3) 다중 테이블 Insert 활용
  • 고급 조인 테크닉
    • (1) 누적 매출 구하기
    • (2) 선분이력 끊기
    • (3) 데이터 복제를 통한 소계구하기
    • (4) 상호배타적 관계의 조인
    • (5) 최종 출력 건에 대해서만 조인하기
    • (6) 징검다리 테이블 조인을 이용한 튜닝
    • (7) 점이력 조회
    • (8) 선분이력 조인
    • (9) 선분이력 조인 튜닝
    • (10) 조인에 실패한 레코드 읽기

3장 옵티마이저 원리(363 ~ 461)
  • 옵티마이저 
    • (1) 옵티마이저란
    • (2) 규칙기반 옵티마이저
    • (3) 비용기반 옵티마이저
    • (4) 옵티마이저 모드
  • 옵티마이저 행동에 영향을 미치는 요소
    • (1) SQL과 연산자 형태
    • (2) 인덱스, IOT, 클러스터링, 파티셔닝, MV 등 옵티마이징 팩터
    • (3) 제약설정
    • (4) 옵티마이저 힌트
    • (5) 통계정보
    • (6) 옵티마이저 관련 파라미터
    • (7) DBMS버전과 종류
  • 옵티마이저의 한계
    • (1) 부족한 옵티마이징 팩터
    • (2) 부정확한 통계
    • (3) 히스토그램의 한계
    • (4) 바인드 변수 사용 시 균등분포 가정
    • (5) 결합 선택도 산정의 어려움
    • (6) 비현실적인 가정
    • (7) 규칙에 의존하는 CBO
    • (8) 하드웨어 성능 특성
  • 통계정보1
    • (1) 테이블 통계
    • (2) 인덱스 통계
    • (3) 컬럼 통계
    • (4) 시스템통계
  • 카디널리티
    • (1) 선택도
    • (2) 카디널리티
    • (3) NULL값을 포함할 때
    • (4) 조건절이 두 개 이상일 때
    • (5) 범위검색 조건일 때
    • (6) cardinality 힌트를 이용한 실행계획 제어
  • 히스토그램
    • (1) 히스토그램 유형
    • (2) 도수분포 히스토그램
    • (3) 높이균형 히스토그램
    • (4) 바인드 변수 사용 시 카디널리티 계산
    • (5) 결합 선택도
  • 비용
    • (1) I/O 비용 모델
    • (2) CPU 비용 모델
  • 통계정보2
    • (1) 전략적인 통게수집 정책의 중요성
    • (2) DBMS_STATS
    • (3) 컬럼 히스토그램 수집
    • (4) 데이터 샘플링
    • (5) 파티션 테이블 통계 수집
    • (6) 인덱스 통계 수집
    • (7) 캐싱된 커서 Invalidation
    • (8) 자동 통계 수집
    • (9) Statistics Preference

4장 쿼리변환(463 ~ 571)
  • 쿼리변환이란?
  • 서브쿼리 Unnesting
    • (1) 서브쿼리의 분류
    • (2) 서브쿼리 Unnesting의 의미
    • (3) 서브쿼리 Unnesting의 이점
    • (4) 서브쿼리 Unnesting기본 예시
    • (5) Unnesting된 쿼리의 조인 순서 조정
    • (6) 서브쿼리가 M쪽 집합이거나 Nonunique 인덱스일 때
    • (7) 필터 오퍼레이션과 세미조인의 캐싱효과
    • (8) Anti 조인
    • (9) 집계 서브쿼리 제거
    • (10) Pushing 서브쿼리
  • 뷰 Merging
    • (1) 뷰 Mergingㅇ 이란?
    • (2) 단순 뷰 Merging
    • (3) 복합 뷰 Merging
    • (4) 비용기반 쿼리 변환의 필요성
    • (5) Merging되지 않은 뷰의 처리방식
  • 조건절 Pushing
    • (1) 조건절 Pushdown
    • (2) 조건절 Pullup
    • (3) 조인조건 Pushdown
  • 조건절 이행
  • 조인 제거
  • OR-Expasion
    • (1) OR-Expansion기본
    • (2) OR-Expansion 브랜치별 조인 순서 최적화
    • (3) 같은 컬럼에 대한 OR-Expansion
    • (4) nvl/decode 조건식에 대한 OR-Expansion
  • 공통 표현식 제거
  • Outer 조인으르 Inner조인으로 변환
  • 실체화 뷰 쿼리로 재작성
  • 집합 연산을 조인으로 변환
  • 기타 쿼리 변환
    • (1) 조인 컬럼에 IS NOT NULL조건 추가
    • (2) 필터 조건 추가
    • (3) 조건절 비교 순서

5장 소트 튜닝(573 ~ 623)
  • 소트 수행 원리
    • (1) 소트 수행 과정
    • (2) 소트 오퍼레이션 측정
    • (3) Sort Area
      • PGA
      • UGA
      • CGA
      • Sort Area 할당 위치
    • (4) 소트 튜닝 요약
  • 소트를 발생시키는 오퍼레이션
    • (1) Sort Aggregate - sum(..), min(..) 실제 소트가 발생하지 않음
    • (2) Sort Order By  
    • (3) Sort Group by - 정렬된 group by결과를 얻고자 한다면 실행계획에 설령'sort group by'라고 표시되더라도 반드시 order by를 명시해야한다.
    • (4) Sort Unique
    • (5) Sort Join
    • (6) Window Sort
  • 데이터 모델 측면에서의 검토
    • 사례1,2,3
  • 소트가 발생하지 않도록 SQL작성
  • 인덱스를 이용한 소트 연산 대체
    • (1) Sort Order By 대체
    • (2) Sort Group By 대체
    • (3) 인덱스가 소트 연산을 대체하지 못하는 경우
      • 전체범위 처리로 쿼리할 시
      • 인덱스 컬럼에 not null제약조건이 없을 때
  • Sort Area를 적게 사용하도록 SQL 작성
    • (1) 소트를 완료하고 나서 데이터 가공하기
    • (2) Top-N 쿼리
    • (3) 분석함수에서의 Top-N 쿼리
  • Sort Area 크기 조정

6장 파티셔닝
  • 테이블 파티셔닝
  • 파티션 Pruning
  • 인덱스 파티셔닝

7장 병렬처리

  • 기본개념
  • 병렬 Order by와 Group by
  • 병렬조인
  • PQ_DISTRIBUTE 힌트
  • 병렬 처리에 관한 기타상식