IT/데이터자격증 기록

SQLP 전문가가이드 목차정리

JJo 2018. 10. 8. 14:06

SQLP자격증을 준비하면서 공부하는 중간중간에
'내가 지금 어디부분을 공부하고있고 오라클 성능고도화에서는 어디부분이구나' 라는걸 인지하면서
공부하기 위해 정리해두었습니다.

복습할 때도 전체목차를 보면서 이해 안되는 부분이 있는지 공부했었구요.
도움되셨으면 좋겠습니다.





과목1. 데이터모델링

과목2. SQL기본 및 활용

과목3. SQL 고급 활용 및 튜닝
  • 1장 아키텍처 기반 튜닝 원리 (성능고도화1 : 4,5,6장)
    • 제1절 데이터베이스 아키텍처
      • 아키텍처 개관
        • 1. Oracle 아키텍처
        • 2. SQL Server 아키텍처
      • 프로세스
        • 1. 서버프로세스
          • 1) 전용 서버 방식
          • 2) 공유 서버 방식
        • 2. 백그라운드 프로세스
          • 1) CKPT : Write Ahead Logging방식(데이터 변경 전에 로그부터 남기는 메커니즘)을 사용하는 DBMS는 Redo로그에 기록해 둔 버퍼 블록에 대한 변경 사항 중 현재 어디까지를 데이터 파일에 기록했는지 체크포인트 정보를 관리
        • 3. 파일 구조
          • 가) 데이터 파일
            • 1) 블록(=페이지)
            • 2) 익스텐트
            • 3) 세그먼트
            • 4) 테이블 스페이스
          • 나) 임시 데이터 파일
          • 다) 로그 파일
        • 4. 메모리 구조
          • 가) DB 버퍼 캐시
          • 나) 공유 풀
            • 딕셔너리 캐시
            • 라이브러리 캐시
          • 다) 로그 버퍼
            • Write Ahead Logging
            • Log Force at commit
            • Fast Commit
          • 라) PGA
            • UGA : 공유서버 방식으로 연결할 때는 1:M관계를 갖는다. 즉, 세션이 프로세스보다 많아질 수 있는 구조로서 하나의 프로세스가 여러 개 세션을 위해 일한다. 따라서 각 세션을 위한 독립적인 메모리 공간이 필요해지는데, 이를 UGA라고 한다
            • CGA : Oracle은 하나의 데이터 베이스 Call을 넘어서 다음 Call까지 계속 참조되어야 하는 정보는 UGA에 담고, Call이 진행되는 동안에만 필요한 데이터는 CGA에 담는다
            • Sort Area : DML문장은 하나의 Execute Call내에서 모든 데이터 처리를 완료하므로 Sort Area가 CGA에 할당. Select문장의 경우 수행 중간 단계에 필요한 Sort Area는 CGA에 할당되고, 최종 결과집합을 출력하기 직전 단계에 필요한 Sort Area는 UGA에 할당
        • 5. 대기 이벤트
          • 가. 라이브러리 캐시 부하
          • 나. 데이터베이스 Call과 네트워크 부하
          • 다. 디스크 I/O부하
            • db file sequential read : Single Block I/O
            • db file scattered read : Multi Block I/O
          • 라. 버퍼 캐시 경합
          • 마. Lock 관련 대기 이벤트
    • 제2절 SQL 파싱 부하
      • 1. SQL처리 과정
        • 가. SQL파싱
        • 나. SQL최적화
      • 2. 캐싱된 SQL 공유 
        • 가. 실행계획 공유 조건
        • 나. 실행계획을 공유하지 못하는 경우
      • 3. 바인드 변수 사용하기
        • 가. 바인드 변수의 중요성
        • 나. 바인드 변수 사용 시 주의사항
        • 다. 바인드 변수 부작용을 극복하기 위한 노력
          • 적응적 커서 공유 
      • 4. Static SQL과 Dynamic SQL
        • 가. Static SQL : 런타임시에 절대 변하지 않으므로 Precompile 단계에서 구문분석, 유효오브젝트여부, 오브젝트액세스 권한등을 체크하는 것이 가능하다.
        • 나. Dynamic SQL : 런타임시에 변함, 따라서 Precompile 시 Syntax, Semantics체크가 불가능하므로 Dynamic SQL에 대해선 Precompiler는 내용을 확인하지 않고 그대로 DBMS의 전달한다
        • 다. 바인드 변수의 중요성 재강조
      • 5. 애플리케이션 커서 캐싱
        • 같은 SQL을 아주 여러번 반복해서 수행해야할 때,  첫 번째는 하드파싱이 일어나겠지만 이후부터는 라이브러리 캐시에 공유된 버전을 찾아 가볍게 실행할 수 있다. 그렇더라도 SQL문장의 문법적, 의미적 오류가 없는지 확인하고, 해시함수로부터 반환된 해시 값을 이용해 캐시에서 실행계획을 찾고, 수행에 필요한 메모리 공간을 할당하는 등의 작업을 매번 반복하는 것은 비효율적이다. 이런 과정을 없애는걸 '애플리케이션 커서 캐싱'
        • PL/SQL에서는 별도로 옵션을 적용하지 않더라도 자동적으로 커서를 캐싱
    • 제3절. 데이터베이스 Call과 네트워크 부하
      • 1. 데이터베이스 Call종류
        • 가. SQL커서에 대한 작업 요청에 따른 구분
        • 나. Call 발생 위치에 따른 구분
          • 1) User Call
            • User Call을 최소화 하려는 방법
              • Loop 쿼리를 해소하고 집합적 사고를 통해 One SQL로 구현
              • Array Processing
              • 부분범위처리 원리 활용
              • 효과적인 화면 페이지 처리
              • 사용자 정의함수/프로시저/트리거의 적절한 활용
          • 2) Recursive Call
            • SQL파싱, 데이터 딕셔너리조회, 사용자 정의 함수/프로시저 내에서의 SQL
      • 2. 데이터베이스 Call과 성능
        • 가. One SQL 구현의 중요성
        • 나. 데이터베이스 Call과 시스템 확장성
      • 3. Array Processing 활용
      • 4. Fetch Call 최소화
        • 가. 부분범위 처리
        • 나. ArraySize조정에 의한 Fetch Call 감소 및 블록 I/O 감소 효과
      • 5. 페이지 처리 활용
      • 6. 분산쿼리
      • 7. 사용자 정의 함수/프로시저의 특징과 성능
        • 가. 사용자 정의 함수/프로시저의 특징
        • 나. 사용자 정의 함수/프로시저에 의한 성능 저하 해소 방안
          • 사용자 정의 함수는 소량의 데이터를 조회할 때, 또는 부분 범위처리가 가능한 상황에서 제한적 사용. 성능을 위해서라면 가급적 함수를 풀어 조인 또는 스칼라 서브쿼리 형태로 변환하려고 노력해야한다.
    • 제4절. 데이터베이스 I/O 원리
      • 데이터베이스 I/O튜닝을 위해선느 인덱스,조인,옵티마이저원리,소트원리 등에 관한 종합적인 이해가 필요한데 3~5장에서 다룸
      • 1. 블록 단위 I/O
      • 2. 메모리I/O vs. 디스크 I/O
        • 가. I/O 효율화 튜닝의 중요성
        • 나. 버퍼 캐시 히트율
        • 다. 네트워크, 파일시스템 캐시가 I/O효율에 미치는 영향
      • 3. Sequential I/O vs. Random I/O
        • 가. Sequential 액세스에 의한 선택 비중 높이기
          • Sequential 액세스 효율성을 높이려면, 읽은 총 건수 중에서 결과집합으로 선택되는 비중을 높여야 한다.
        • 나. Random 액세스 발생량 줄이기
      • 4. Single Block I/O vs. MultiBlock I/O
      • 5.  I/O 효율화 원리
        • 가. 필요한 최소 블록만 읽도록 SQL 작성
        • 나. 최적의 옵티마이징 팩터 제공
        • 다. 필요하다면, 힌트를 사용해 최적의 인덱스 경로로 유도
  • 2장 Lock과 트랜잭션 동시성 제어
    • 제1절. Lock
      • 1. Lock기본
        • 가. Lock이란?
        • 나. 공유 Lock과 배타적 Lock
        • 다. 블록킹과 교착상태
      • 2. SQL Server Lock
      • 3. Oracle Lock
        • 가. 로우 Lock
        • 나. 테이블 Lock
  • 제2절. 트랜잭션
    • 1. 트랜잭션의 특징
    • 2. 트랜잭션 격리성
  • 제3절. 동시성 제어
    • 1. 비관적 동시성 제어 vs. 낙관적 동시성 제어
    • 2. 다중버전 동시성 제어
  • 3장 옵티마이저 원리 (성능고도화2 : 3,4장)
    • 1절. 옵티마이저
      • 1.옵티마이저 소개
        • 가. 옵티마이저란?
        • 나. 옵티마이저 종류
          • 1) 규칙기반 옵티마이저
          • 2) 비용기반 옵티마이저
        • 다. 최적화 과정
        • 라. 최적화 목표
          • 1) 전체 처리속도 최적화
          • 2) 최초 응답속도 최적화
      • 2.옵티마이저 행동의 영향을 미치는 요소
        • 가. SQL과 연산자 형태
        • 나. 옵티마이징 팩터
        • 다. DBMS 제약설정
        • 라. 옵티마이저 힌트
        • 마. 통계정보
        • 바. 옵티마이저 관련 파라미터
        • 사. DBMS 버전과 종류
      • 3. 옵티마이저 한계
        • 가. 옵티마이징 팩터의 부족
        • 나. 통계정보의 부정확성
        • 다. 바인드 변수 사용 시 균등분포 가정
        • 라. 비현실적인 가정
        • 마. 규칙에 의존하는 CBO
        • 바. 하드웨어 성능 특성
      • 4. 통계정보를 이용한 비용계산 원리
        • 가. 선택도
        • 나. 카디널리티
        • 다. 히스토그램
        • 라. 비용
      • 5. 옵티마이저 힌트
    • 쿼리변환
      • 1. 쿼리변환이란?
      • 2. 서브쿼리 Unnesting
      • 3. 뷰 Merging
      • 4. 조건절 Pushing
      • 5. 조건절 이행
      • 6. 불필요한 조인 제거
      • 7. OR 조건을 Union으로 변환
      • 8. 기타 쿼리 변환

  • 4장 인덱스와 조인 (성능고도화2 : 1,2장)
    • 제1절. 인덱스 기본 원리
      • 1. 인덱스 구조
        • 가. 인덱스 기본 구조
        • 나. 인덱스 탐색
      • 2. 다양한 인덱스 스캔 방식
        • 가. Index Range Scan
        • 나. Index Full Scan
        • 다. Index Unique Scan
        • 라. Index Skip Scan
        • 마. Index Fast Full Scan
        • 바. Index Range Scan Descending
      • 3. 인덱스 종류
        • 가. B Tree 인덱스
          • 1) Unbalanced Index
          • 2) Index Skew
            • 오라클에서는, 텅빈 인덱스 블록은 커밋하는 순간 FreeList로 반환되지만 인덱스 구조 상에는 그대로 남는다. 새로운 값이 하나라도 입력되기 전 다른 노드에 인덱스 분할이 발생하면 그것을 위해서도 이들 블록이 재사용 된다. 레코드가 모두 삭제된 블록은 언제든 재사용 가능하지만, 문제는 다시 채워질때까지 인덱스 스캔효율이 낮다는데에 있다.
            • SQL Server에선 Index Skew현상이 발생하지 않는다. 주기적으로 B Tree인덱스를 체크함으로써 지워진 레코드와 페이지를 정리해주는 메커니즘을 갖기 때문
          • 3) Index Sparse
            • 인덱스 블록 전반에 걸쳐 밀도가 떨어지는 현상
          • 4) 인덱스 재생성
        • 나. 비트맵 인덱스
        • 다. 함수기반 인덱스
        • 라. 리버스 키 인덱스 : 거꾸로 입력하기 때문에 '='조건만 가능
        • 마. 클러스터 인덱스
        • 바. 클러스터형 인덱스 / IOT

    • 제2절. 인덱스 튜닝
      • 1. 인덱스 튜닝 기초
        • 가. 범위 스캔이 불가능하거나 인덱스 사용이 아예 불가능한 경우
        • 나. 인덱스 칼럼의 가공
        • 다. 묵시적 형변환
      • 2. 테이블 Random 액세스 최소화
        • 가. 인덱스 Rowid에 의한 테이블 Random 액세스
        • 나. 인덱스 손익분기점
        • 다. 테이블 Random 액세스 최소화 튜닝
          • 1) 인덱스 칼럼 추가
          • 2) Covered Index(인덱스만 읽고 처리)
          • 3) Include Index(인덱스 키 이외 미리 지정한 칼럼을 리프레벨에 함께 저장)
          • 4) IOT, 클러스터형 인덱스, 클러스터 테이블 활용
          • 5) 수동으로 클러스터링 팩터 높이기
      • 3. 인덱스 스캔 범위 최소화
        • 가. 인덱스 선행 칼럼이 범위조건일 때의 비효율
        • 나. 범위조건을 In-List로 전환
        • 다. 범위조건을 2개 이상 사용할 때의 비효율
      • 4. 인덱스 설계
    • 제3절. 조인 기본 원리
      • 1. Nested Loop Join
        • 가. 기본 메커니즘
        • 나. NL Join 수행 과정 분석
        • 다. NL Join의 특징
      • 2. Sort Merge Join
        • 가. 기본 메커니즘
        • 나. Sort Merge Join의 특징
          • 조인 하기 전에 양쪽 집합을 정렬한다
          • 부분적으로, 부분범위처리가 가능하다
          • 테이블별 검색 조건에 의해 전체 일량이 좌우된다.
          • 스캔 위주의 조인 방식이다.
      • 3. Hash Join
        • 가. 기본 메커니즘
        • 나. Build Input이 가용 메모리 공간을 초과할 때 처리 방식
        • 다. Build Input 해시 키 값에 중복이 많을 때 발생하는 비효율
        • 라. Hash Join사용기준
          • 한 쪽 테이블이 가용 메모리에 담길정도로 충분히 작아야함
          • Build Input해시 키 칼럼에 중복 값이 거의 없어야 함
      • 4. Scalar Subquery
        • 가. Scalar Subquery의 캐싱효과
        • 나. 두 개 이상의 값을 리턴하고 싶을 때
    • 제4절. 고급 조인 기법
      • 1. 인라인 뷰 활용 : 1:M관계 테이블 조인 시 M관계가 되는데 다시 1관계로 그룹핑 해야한다면 M테이블을 조건에 맞춰 미리 1관계로 인라인뷰내에서 처리하고 조인한다
      • 2. 배타적 관계의 조인 : b.개통신청번호(+) = decode(a.작업구분, '1', a.접수번호) / c.장애접수번호(+) = decode(a.작업구분, '2', a.접수번호)
      • 3. 부등호 조인
      • 4. Between 조인
        • 가. 선분이력이란?
        • 나. 선분이력 기본 조회 패턴
        • 다. 선분이력 조인
        • 라. Between 조인
      • 5. Rowid 활용
  • 5장 고급 SQL 튜닝 (성능고도화2 : 5,6,7장)
    • 제1절. 고급 SQL활용
      • 1. CASE문 활용
      • 2. 데이터 복제 기법 활용
      • 3. Union All을 활용한 M:M관계의 조인
      • 4. 페이징처리
        • 가. 일반적인 페이징처리용 SQL
        • 나. 뒤쪽까지 자주 조회할 때
      • 5. 윈도우 함수 활용
      • 6. With 구문 활용
    • 제2절. 소트 튜닝
      • 1. 소트와 성능
        • 가. 메모리소트와 디스크소트
        • 나. 소트를 발생시키는 오퍼레이션
      • 2. 데이터 모델 측면에서의 검토
      • 3. 소트가 발생하지 않도록 SQL작성
        • 가. Union 을 Union ALL로 대체
        • 나. Distinct를 Exists서브쿼리로 대체
        • 다. 불필요한 Count연산 제거
      • 4. 인덱스를 이용한 sort연산 대체
      • 5. 소트 영역을 적게 사용하도록 SQL 작성
        • 가. 소트 완료 후 데이터 가공
        • 나. Top-N 쿼리
      • 6. 소트 영역 크기 조정
    • 제3절. DML 튜닝
      • 1. 인덱스 유지 비용
      • 2. Insert 튜닝
        • 가. Oracle Insert 튜닝
      • 3. Update 튜닝
        • 가. Truncate & Insert 방식 사용
        • 나. 조인을 내포한 Update 튜닝
          • Oracle 수정 가능 조인뷰 활용
          • Oracle Merge문 활용
    • 제4절. 파티션 활용
      • 1. 파티션 개요
      • 2. 파티션 유형
        • 1) Range 파티셔닝
        • 2) Hash 파티셔닝
        • 3) List 파티셔닝
        • 4) Composite 파티셔닝
      • 3. 파티션 Pruning
        • 가. 정적 파티션 Pruning
        • 나. 동적 파티션 Pruning
      • 4. 인덱스 파티셔닝
        • 가. Local 파티션 인덱스 vs. Global 파티션 인덱스
        • 나. Prefixed파티션 인덱스 vs.  Nonprefixed 파티션 인덱스
        • 다. 인덱스 파티셔닝 가이드
    • 배치 프로그램 튜닝
      • 1. 배치 프로그램 튜닝 개요
        • 가. 배치 프로그램이란?
        • 나. 배치 환경의 변화
        • 다. 성능 개선 목표 설정
        • 라. 배치 프로그램 구현 패턴과 튜닝 방안
      • 2. 병렬 처리 활용
        • 가. Query Coordinator 와 병렬 서버 프로세스
        • 나. Intra-Operation Parallelism과 Inter-Operation Parallelism
        • 다. 테이블큐
        • 라. IN-OUT 오퍼레이션
        • 마. 데이터 재분배
        • 바. pq_distribute 힌트 활용
        • 사. 병렬 처리 시 주의사항