SQL 옵티마이저 & SQL 처리 과정과 I O - t0e8r1r4y/SQLP_STUDY GitHub Wiki

1. 선택도( Selectivity )
   1. 전체 레코드 중에서 조건절에 의해 선택되는 레코드 비율
   2. 선택도 = 1 / NVD(Number of Distinct Values) (** ‘=’ 조건 검색 시 선택도)
2. 카디널리티( Cardinality )
   1. 전체 레코드 중에서 조건절에 의해 선택되는 레코드 개수
   2. 카디널리티 = 총 로우 수 x 선택도 = 총 로우 수 / NVD
3. 옵티마이저가 카디널리티를 사용하여 비용을 계산하고 테이블 액세스 방식, 조인 순서, 조인 방식을 결정함. 결론적으로 카디널리티를 결정 짖는 NVD를 정확하게 구해야 한다.

1. 오브젝트 통계
   1. 테이블 통계
   2. 인덱스 통계
   3. 컬럼 통계
2. 시스템 통계 : 애플리케이션 및 하드웨어 성능 특성을 측정한 것
   1. CPU 속도
   2. 평균적인 Single Block I/O 속도
   3. 평균적인 Multiblock I/O 속도
   4. 평균적인 Multiblock I/O 개수
   5. I/O 서브시스템의 최대 처리량(Throughput)
   6. 병렬 Slave의 평균적인 처리량(Throughput)

1. 옵티마이저는 통계정보를 활용하여 비용을 계산한다.

2. 예시

   # 인덱스 키값을 모두 '='조건을 검색할 때
   
   비용 = BLEVEL                         <- 인덱스 수직적 탐색 비용
         + AVG_LEAF_BLOCKS_PER_KEY      <- 인덱스 수평적 탐색 비용
         + AVG_DATA_BLOCKS_PER_KEY      <- 테이블 랜덤 액세스 비용
   

   # 인덱스 키값을 모두 '='조건을 아닐 때
   
   비용 = BLEVEL                             <- 인덱스 수직적 탐색 비용
         + ( LEAF_BLOCKS * 유효 인덱스 선태도 )  <- 인덱스 수평적 탐색 비용
         + ( CLUSTERING_FACTOR * 유효 테이블 선택도 ) <- 테이블 랜덤 액세스 비용
   

3. BLEVEL, LEAF_BLOCKS, CLUSTERING_FACTOR ← 인덱스 통계

4. 유효 인덱스 선택도, 유효 테이블 선택도 ← 컬럼 통계 및 히스토그램

   1. 유효 인덱스 선택도 : 전체 인덱스 레코드 중 액세스 조건에 의해 선택될 것으로 예상되는 레코드 비중
   2. 유효 테이블 선택도 : 전체 인덱스 레코드 중 인덱스 컬럼에 대한 모든 조건절에 의해 선택 될 것으로 예상되는 레코드의 비중

1. 비용기반 옵티마이저 ( Cost-Based-Optimizer ) ← 대용량 처리에 더 적합하다.
2. 규칙기반 옵티마이저 ( Rule-Based-Optimizer )

1. ALL_ROW : 전체 처리속도 최적화 → 응답 속도 최적화가 목표.
2. FIRST_ROWS : 최초 응답속도 최적화
3. FIRST_ROWS_N : 최초 N건 응답속도 최적화
   1. N은 힌트로 제공시 0보다 큰 정수값을 입력
   2. alter session, alter system으로 입력시 1, 10, 100, 1000 4가지 입력가능

1. SQL과 같은 연산자 형태
2. 인덱스, IOT, 클러스터, 파티션, MV 등 옵티마이징 팩터
3. 제약 설정 ( PK, FK, Check, Not Null과 같은 Constraint )
4. 통계정보
   1. 통계정보가 테이블 인덱스 생성 및 관리에 따라 적절히 수집되지 못하는 경우
5. 옵티마이저 힌트 : 옵티마이저는 힌트를 명령어로 인식하여 동작함. 아래의 경우 오인식 가능성 있음
   1. 문법적으로 맞지 않게 힌트를 기술
   2. 잘못된 참조 사용
   3. 의미적으로 맞지 않게 힌트를 기술
   4. 논리적으로 불가능한 액세스 경로
   5. 버그
6. 옵티마이저 관련 파라미터 : ex 버전 변경에 따른 파라미터 병경 등

1. 필요한 최소 블록만 읽도록 쿼리를 작성한다.
2. 최적의 옵티마이징 팩터를 제공한다.
   1. 옵티마이저 모드를 포함해 각종 파라미터를 적절한 값으로 설정하고, 통계정보를 잘 수집해 주는 것이 중요
   2. 전략적인 인덱스 구성이 필수적
   3. DBMS가 제공하는 기능을 적극적으로 활용해 옵티마이저가 최적의 선택을 할 수 있도록 수단 제공
3. 필요하다면, 옵티마이저 힌트를 사용해 최적의 액세스 경로로 유도한다.

구조적, 집합적, 선언적 질의 언어 → SQL ( Structured Query Language )

1. SQL is a set-based, declarative query language, not an imperative language such as C or BASIC
2. 원하는 결과를 구조적, 집합적(set-based)으로 선언
3. 결과를 만드는 과정은 절차적

SQL 최적화

1. SQL 파싱
   1. 파싱 트리 생성 : SQL 문을 이루는 개별 구성요소를 분석해서 파싱 트리 생성
   2. Syntax 체크 : 문법적으로 오류가 없는지 확인
   3. Semantic 체크 : 의미상 오류가 없는지 확인
2. SQL 최적화
   1. SQL최적화를 옵티마이저가 담당한다.
   2. 미리 수집한 시스템 및 오브젝트 통계정보를 바탕으로 다양한 실행경로를 생성해서 비교한 후 가장 효율적인 하나를 선택
3. 로우 소스 생성
   1. SQL 옵티마저가 선택한 실행 경로를 실제 실행 가능한 코드 또는 프로시저 형태로 포맷팅하는 단계
   2. 로우 소스 생성기(Row-Source Generator)가 담당

SQL 옵티마이저

1. 최적화 단계 요약
   1. 사용자로부터 전달받은 쿼리를 수행하는 데 후보군이 될만한 실행계획들을 찾아냄
   2. 데이터 닉셔너리에 미리 수집해 둔 오브젝트 통계 및 시스템 동계정보를 이용해 각 실행계획의 예상비용을 산정한다.
   3. 최저 비용을 나타내는 실행계획을 선택한다

실행계획과 비용

1. 옵티마이저는 비용(Cost)를 근거로하여 최적화하는 방법을 결정한다.

2. 사용하는 도구마다 다르겠지만 일반적으로 실행계획과 비용은 확인 할 수 있다. ( 아래 DBeaver 예시 )

   

옵티마이저 힌트

1. 통계정보에 담을 수 없는 데이터 또는 업무 도메인 특성을 활용해 개발자가 직접 더 효율적인 액세스 경로를 찾을 수도 있다. 이럴 때 옵티마이저 힌트를 사용하여 데이터 액세스 경로를 바꿀 수 있다.

2. 사용법 → 주석 기호에 ‘+’를 붙이면 된다.

   SELECT /*+ INDEX(A 고객_PK) */
   	고객명, 연락처, 주소, 가입일시
   FROM 고객 A
   WHERE 고객ID = '1'

3. 주의 사항

   1. 힌트와 힌트 사이에 콤마를 사용하면 안된다. → 첫번째 힌트만 유효함
   2. 테이블을 지정할 때 스키마명까지 명시하면 안됨
   3. 쿼리의 FROM 절 테이블명 옆에 ALIAS를 지정했다면 힌트에서도 ALIAS를 사용해야 된다. 그렇지 않으면 무효

4. 자주 사용하는 힌트 목록

   오라클 자주사용하는 힌트목록 정리(친절한 sql 튜닝)

1. 핵심 : SQL도 반복적으로 사용되는 부분은 캐시를 사용하여 빠르게 처리한다. ( 재사용 !!! )

   

2. 소프트 파싱 : 캐시에서 SQL을 찾아 곧바로 실행 단계로 넘어가는 것

3. 하드 파싱 : 캐시에서 찾지 못하여 최적화 및 로우 소스 생성 단계까지 모두 거치는 것

4. 라이브러리 캐시( Library Cache) : 캐시 저장소

1. 사용자 정의 함수/프로시저, 트리거, 패키지 등은 생성할 때부터 이름을 가짐
   1. 컴파일 상태로 딕셔너리 저장
   2. 사용자가 삭제하지 않는 한 영구 보관
   3. 실행과 동시에 라이브러리 캐시에 적재하여 공유
2. 그러나 SQL은 이름이 없다.

   1. SQL 자체가 이름 역할을 한다. → 아래 3개 모두 각각 이름이며 캐시에 각각 저장 된다.

      SELECT * FROM EMP e WHERE e.ID = 'dart011'
      SELECT * FROM EMP e WHERE e.ID = 'dart012'
      SELECT * FROM EMP e WHERE e.ID = 'dart013'

   2. 캐시 공간이 부족하면 버려졌다가 다음에 다시 실행할 때 최적화 과정을 거쳐 캐시에 적재가 된다.

   3. 따라서 쿼리의 재사용성을 높이기 위해서는 바인드 변수를 사용해야 한다. → ORM에서 사용 쿼리 조회하면 이렇게 쓰고 있다.

      public void login( String login_id ) throws Exception {
      	String SQLStatement = " SELECT * FROM EMP WHERE ID = ?";
        ... 
      }

SQL이 느린 이유

1. 원인 → I/O 병목 → Why?

데이터베이스 저장 구조

1. 구조

   

2. 테이블 스페이스 : 세그먼트를 담는 컨테이너, 여러 개의 디스크 상의 물리적인 OS파일로 구성됨

3. 세그먼트 : 테이블, 인덱스 처럼 데이터 저장공간이 필요한 오브젝트.

4. 익스텐트 : 공간을 확장하는 단위이며 연속된 블록의 집합. 테이블이나 인덱스에 데이터를 입력하다가 공간이 부족해지면 해당 오브젝트가 속한 테이블 스페이스로부터 익스텐트를 추가로 할당 받는다.

5. 데이터 블록 : 실제로 사용자가 입력한 레코드를 실제로 저장하는 공간

6. DBA ( Data Block Access )

   1. 주소값 → 디스크 상에서 몇 번 데이터파일의 몇 번째 블록인지를 나타내는 자신만의 고유 주소값
   2. 데이터를 read/write하는 단위가 블록이므로 데이터를 읽기 위해서는 DBA를 가장 먼저 확인한다.

7. 조회 방법 & 결과

   SELECT tablespace_name, segment_type, EXTENT_ID , file_id, block_id, blocks
   FROM DBA_EXTENTS
   WHERE OWNER = USER
   ORDER BY EXTENT_ID ;

   

블록단위 I/O

1. DBMS가 데이터를 읽고 쓰는 단위.

2. oracle의 기본 설정은 8KB 크기의 블록을 사용한다. ( 인덱스도 동일함 )

   

   select distinct bytes/blocks from user_segments;
   
   /* 책에서는 show 명령어를 사용하는데 sqlplus로 접속 시에만 가능. */
   show parameter block_size

시퀀셜 액세스 vs 랜덤 액세스

1. 시퀀셜 액세스 : 물리적으로 인접한 페이지를 차례대로 읽는 방식, 인접한 페이지를 여러 개 읽을 수 있음

   

2. 랜덤 액세스 : 정해진 순서없이 이동하는만큼 디스크의 물리적 움직임이 필요하고, 다중 페이지 읽기가 불가능

   

논리적 I/O vs 물리적 I/O

1. 논리적 블록 I/O : SQL을 처리하는 과정에 발생한 총 블록 I/O
   1. 메모리상의 버퍼 캐시를 경유하므로 메모리 I/O가 곧 논리적 I/O
   2. 캐시에서 읽어들이는 I/O
2. 물리적 블록 I/O : 디스크에서 발생한 총 블록 I/O
   1. 디스크를 액세스하는 디스크 I/O
3. 데이터 캐시를 사용하는 이유 : 물리적 블록 IO가 논리적 블록 IO에 비해 10,000배 정도 느리다. 그래서 캐시를 사용해서 물리적 블록 IO를 최소화 한다.
4. 버퍼캐시 히트율 ( Buffer Cache Hit Ratio)

   1. 공식

      공식
      BCHR = ( 캐시에서 곧바로 찾은 블록 수 / 총 읽은 블록 수 ) * 100
           = (( 논리적 I/O - 물리적 I/O ) / 논리적 I/O ) * 100
           = ( 1 - (물리적 I/O)/(논리적I/O) ) * 100
      
      물리적 I/O = 논리적 I/O * ( 100% - BCHR )
      

   2. 물리적 IO가 성능을 결정하지만 성능 향상은 논리적 IO를 줄여야 한다.

   3. 논리적 I/O를 줄임으로써 물리적 I/O를 줄이는 것 → SQL 튜닝

Single Block I/O vs Multiblock I/O

1. Single Block I/O : 한 번에 한 블록씩 요청해서 메모리에 적재하는 방식 → 인덱스를 사용할 때

   1. 인덱스 루트 블록을 읽을 때
   2. 인덱스 루트 블록에서 얻은 주소 정보로 브랜치 블록을 읽을 때
   3. 인덱스 브랜치 블록에서 얻은 주소 정보로 리프 블록을 읽을 때
   4. 인덱스 리프 블록에서 얻은 주소 정보로 테이블 블록을 읽을 때

2. Multiblock I/O : 한 번에 여러 블록씩 요청해서 메모리에 적재하는 방식 → 테이블 전체를 스캔 할 때

   1. Full Scan

   2. I/O Call 발생 시 목적 대상과 같은 인스텐트에 있는 블록들을 한꺼번에 읽어 캐시에 미리 적재할 때

      SQL> show parameter db_file_multiblock_read_count
      
      NAME				     TYPE	 VALUE
      ------------------------------------ ----------- ------------------------------
      db_file_multiblock_read_count	     integer	 62
      

3. 아래의 이유로 Multiblock I/O 도중 Single Block I/O이 발생하기도 함

   1. 부분적으로 캐시에서 데이터를 읽어오고 나머지를 디스크에서 읽어오는 경우
   2. Full Scan 중에 Chain이 발생한 로우를 읽을 때

Table Full Scan vs Index Range Scan

1. Table Full Scan
   1. 시퀀셜 액세스와 Multiblock IO 방식으로 디스크 블록을 읽는다.
   2. 큰 테이블에서 다량
2. Index Range Scan
   1. Single Block IO 방식과 랜덤 액세스 방식으로 디스크 블록을 읽는다.
   2. 큰 테이블에서 소량의 데이터를 탐색하는 경우 유리함

캐시 탐색 매커니즘

1. 버퍼캐시 탐색 과정을 거치는 상황

2. 매커니즘

   1. 같은 입력 값은 항상 동일한 해시 체인(=버킷)에 연결 됨
   2. 다른 입력 값이 동일한 해시 체인에 연결 될 수 있음
   3. 해시 체인 내에서는 정렬이 보장되지 않음

   

3. 메모리 공유자원에 대한 액세스 직렬화

   1. 공유자원에 동시 접근을 하면 정합성 문제가 생긴다.
   2. 내부적으로 한 프로세스씩 순차적으로 접근하도록 구현해야 하며, 이를 위해 직렬화 매커니즘이 필요함.
   3. 직렬화를 지원하는 매커니즘이 래치 → 래치는 일종의 lock이다.
   4. 직렬화 매커니즘에 의한 캐시 경함을 줄이려면 논리적 I/O 자체를 줄여야 한다.