1. SQL 처리 과정과 I/O

1.1 구조적, 집합적, 선언적 질의 언어

• SQL : 구조절 질의언어
• 오라클 PL/SQL, SQL SERVER T-SQL처럼 절차적(procedural)프로그램기능을 구현할수있는 확장언어도 제공하지만, sql은 기본적으로 구조적, 집합적, 선언적 질의언어
• 사용자 > sql > 옵티마이저 > 실행계획 > 프로시저

1.2 SQL 최적화

• DBMS내부에서 프로시저를 작성하고 컴파일해서 실행가능한 상태로만드는 전과정
• 실행과정
  1) SQL 파싱
  - 파싱트리생성 : SQL문을 이루는 개별 구성요솧 분석해서 파싱트리 생성
  - 문법체크 : 문법적 오류 체크
  - SEMANTIC체트 : 의미상 오류체크 ex)존재하지않는 테이블 또는 컬럼이있는지, 오브젝트 권한이 있는지
  2) SQL최적화
  - 옵티마이저가 담당, 미리 수집한 시스템 및 오브젝트 통계정보 바탕으로 다양한 실행경로 생성
  - 생성된 실행경료 비교하여 가장 효율적인것 하나를 선택
  - 데이터 베이스 성능을 결정하는 가장 핵심엔진
  3) 로우소스 생성
  - 생성한 실행경로를 실제 가능한 코드또는 프로시저 형태로 포맷팅
  - 로우 소스 생성기가 담당

1.3 SQL 옵티마이저

• 사용자가 원하는 작업을 가장 효율적으로 수행할수있는 최적의 데이터 엑세스 경로를 선택해주는 DBMS핵심엔진
• 옵티마이저 최적화단계요약
  1) 사용자로부터 전달받은 쿼리를 수행하는데 후보군이 될만한 실행계획들을 찾는다.
  2) 데이터 딕셔너리에 미리 수집해 둔 오브젝트 통계 및 시스템 통계정보를 이용해 각 실행계획 예상비용 산정
  3) 최저 비용을 나타내는 실행계획을 선택

1.4 실행계획과 비용

• 옵타미어지는 내비게이션과 비슷(경로요약, 모의주행)
• DBMS에도 SQL실행경로 미리보기 기능이있다 > 실행계획(Execution Plan)
• 실행계획의 cost기반으로 최적실행계획 선정
• Execution Plan의 cost도 어디까지나 예상치, 실제 수행하는 I/O와 많은 차이가 난다.

1.5 옵티마이저 힌트

• SQL옵티마이저가 대부분 좋은 선택을 하지만 완벽하진않다
• SQL이 복잡할수록 실수가능성도 증가
• 이때 힌트를 통해 데이터 엑세스 경로를 바꿀수있다.
• 인자를 나열할땐 콤마사용할 수 있지만, 힌트와 힌트사이에 사용하면 안된다(첫번째것만 유효)
• 힌트에서 테이블 지정시 스키마명까지 명시하면 안된다.
• FROM절에서 ALIAS사용시 힌트에서도 반드시 ALIAS사용해야 한다.
• 자주 사용하는 힌트목록 https://velog.velcdn.com/images%2Ftothek%2Fpost%2F9bbd0df0-66b7-444c-9991-0810bcf7a905%2F%EC%9E%90%EC%A3%BC%EC%93%B0%EB%8A%94%ED%9E%8C%ED%8A%B8.PNG

2. SQL공유 및 재사용

2.1 소프트 파싱 VS 하드 파싱

• SQL파싱, 최적화, 로우소스 생성 과정을 커쳐 생성한 내부 프로시저를 반복 재상요할 수 있도록 캐싱해두는 메모리 공간을 라이브러리 캐시 라고한다.
• SGA는 서버프로세스와 백그라운드 프로세스가 공통으로 액세스하는 데이터와 제어구조를 캐싱하는 메모리 공간
• 사용자가 SQL문을 전달하면 DBMS는 SQL을 파싱한 후 해당 SQL을 라이브러리 캐시에 존재하는지 확인
• 있으면 바로 실행 (소프트 파싱)
• 없으면 최적화단계 거친다. (하드파싱)
• 최적화과정은 왜 하드한가?
  • 매우많은 작업이 필요함
  • 무수한 경우의수고려
  • 다양한 조인방법, 다양한 인덱스 스캔방법
  • 테이블, 컬럼, 인덱스구조에관한 기본정보, 오브젝트 통계, 시스템통계 등 수많은 정보를 참고

2.2 바인드 변수 중요성

• SQL은 한글자만 달라져도 새로운 실행계획을 생성.(대소문자도 구분)
• 바인드 변수를 사용하지않고 아래쿼리를 사용한다고하면(고객정보 500만건)
  얼마나많은 하드파싱이 생길지 생각해보라(CPU사용량이 급격히 올라갈것이다.)

  "SELECT * FROM CUSTOMER WHERE LOGIN_ID ='" + login_id+ "'";

• 바인드 변수 사용시 최초 한번만 하드파싱이 일어나고, 그이후부터는 캐싱된 SQL을 사용한다(소프트파싱)

3. 데이터 저장 구조 및 I/O 메커니즘

• I/O튜닝이 곧 SQL튜닝이라고해도 과언이아님.
• SQL튜닝원리를 제대로 이해하려면 I/O에 대한 이해가 매우중요.

3.1 SQL이 느린 이유

• 대부분이 디스크 I/O때문
• I/O란? : 잠(SLEEP) => OS또는 I/O서브시스템이 I/O를 처리하는동안 프로세스는 잠을 자기때문
• 프로세스는 실행중인 프로그램이며, 생성-(준비-실행-대기 반복) - 종료의 생명주기를 가진다.
• 여러 프로세스가 하나의 CPU를 공유할 수 있지만, 특정순간에는 하나의 프로세스만 CPU를사용할 수 있기때문에 이런메커니즘이 필요
  
  
• I/O CALL속도는 SINGLE BLOCK I/O기준 평균 10ms쯤된다 초당 100블록쯤 읽는셈
• 큰 캐시를 가진 SAN스토리지는 4~8ms정도, 초당 125~250블록
• SSD까지 활용하는 최근 스토리지는 1~2ms, 초당 500~1000블록
• 스토리지 성능이 빨라지고있지만 우리기대에는 못미침 어떤SQL이 SINGLE블록 I/O방식으로 10000블록 읽으면
  가장 최신 스토리지에서도10초이상 기다려야 한다.
• 수많은 프로세스에 의해 동시다발적으로 발생하는I/O CALL때문에 디스크경합이심해지고 그만큼 대기시간도 늘어난다. 10초가아닌 수십초가걸리수도있음.
• SQL이 느린이유가 이것이다, DISK I/O, 이것이 SQL성능을 좌우한다고해도 과언이 아니다.

3.2 데이터베이스 저장구조

• 테이블스페이스 : 세그먼트를 담는 콘테이너, 여러 개의 데이터파일(디스크상 물리파일)로 구성
  , 테이블, 인덱스를 생성할때 어떤 테이블스페이스에 저장할지를 구성
• 세그먼트 : 테이블, 인덱스처럼 데이터 저장공간이 필요한 오브젝트
  익스텐트로 구성, 파티션 구조가 아니라면 테이블도 하나의 세그먼트
  인덱스도 하나의 세그먼트.
  LOB 컬럼은 그 자체가 하나의 세그먼트를 구성하므로 자신이 속한 테이블과 다른 별도 공간에 값을 저장
• 익스텐트 : 공간을 확장하는 단위, 테이블이나 인덱스에 데이터입력하다 공간이 부족하면 오브젝트가 속한 테이블스페이스로부터 추가 익스텐트를 할당받는다.
  블록들의 연속된 집합이기도 하다.
• 블록 : 익스텐트단위로 공간을 확장하지만, 사용자가 입력한 레코드를 실제 저장하는공간은 데이터블록
  입출력의 최소단위이다. (다른DB에서는 페이지라고도한다)
  하나의 블록에 저장된 레코드는 모두 같은 테이블의 레코드이다.(익스텐트도 마찬가지)
  
  
• 세그먼트 공간이 부족하면 테이블스페이스로부터 익스텐트 추가할당받는다고 했는데,
  세그먼트에 할당된 모든 익스텐트가 같은 데이터파일에 위치하지 않을 수 있다.
  서로 다른 데이터파일에 위치할 가능성이 높다
  하나의 테이블스페이스를 여러 데이터파일로 구성하면, 파일 경헙을 줄이기 위해 DBMS가 데이터를 가능한 여러 데이터 파일로 분산해서 저장하기 때문
  
• 블록 : 데이터 읽고쓰는 단위
• 익스텐트 : 공간 확장단위, 연속된 블록 집합
• 세그먼트 : 데이터 저장공간이 필요한 오브젝트 (테이블,인덱스,파티션,LOB등)
• 테이블스페이스 : 세그먼트를 담는 콘테이너
• 데이터파일 : 디스크상 물리적 파일

3.3 블록 단위 I/O

• 블록이 DBMS가 데이터를 읽고 쓰는 단위
• 데이터 I/O가 블록단위이니 특정레코드하나(1ROW)를 읽고 싶어도 해당블록을 통째로 읽는다.(심지어 컬럼하나만 읽고싶어도)
• 테이블뿐 아니라 인덱스도 블록 단위로 데이터를 읽고 쓴다.
  

3.4 시퀀셜 엑세스 VS 랜덤엑세스

• 시퀀셜엑세스 : 논리적,물리적으로 연결된 순서에 따라 차례대로 블록을 읽는방식 (인덱스 RANGE SCAN)
  각 익스텐트의 첫번재 블록 뒤에 연속해서 저장된 블록을 순서대로 읽으면, 그것이 FULL TABLE SCAN
• 랜덤 엑세스 : 논리적, 물리적 순서를 따르지않고 레코드하나를 읽기 위해 한블록 씩 접근하는 방식
  
  

3.5 논리적I/O VS 물리적I/O

• DB버퍼캐시 : 데이터 캐시!, 디스크에서 읽은 데이터블록을 캐싱해둠으로 같은 블록에대한 I/O CALL을 줄이는데 목적
  공유메모리 영역이라 같은 블록을 읽는 다른 프로세스도 득을본다.

• 논리적 I/O 메모리 버퍼캐시에서발생한 총 블록 I/O

• 물리적 I/O : 디스크에서 발생한 총 블록 I/O
  SQL처리 도중 읽어야 할 블록을 버퍼캐시에서 찾지 못할때만 디스크를 액세스하므로, 논리적 블록 I/O중 일부를 물리적으로 I/O한다.

• 메모리 I/O는 전기신호인데 반해, 디스크 I/O는 액세스암을 통해 물리적 작용이 일어나므로 메모리I/O에 비해 상당히 느림. 보통 10000배느림. 디스크경합이 심하면 더느림

  
  

• SQL을 수행하면서 읽은 총 블록I/O가 논리적 I/O

• DIRECT PATH READ방식을 제외하면 모든 블록은 DB버퍼캐시를 경유해서 읽는다.

• 따라서 논리적I/O횟수는 DB버퍼캐시에서 블록을 읽은 횟수와 일치

• 버퍼캐시 히트율

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

• 공식에서 보이듯이 전체 블록중에서 물리적 디스크 I/O를 수반하지않고 곧바로 메모리에서 찾은 비율

• 온라인트랜잭션을 주로 처리하는 애플리케이션이라면, 시스템 레벨에서 평균 99% 히트율을 달성해야한다.

• 물리적 I/O가 성능을 결정하지만, 실제 SQL성능을 향상하려면 물리적 I/O가 아닌 논리적 I/O를 줄여야 한다.

• 논리적 I/O가줄면 물리적I/O도 감소하니까 결국 성능도 향상된다.

• 위사진의 BCHR을 구하는공식
  (1 - (601,458/(1,351,677+12,367)) * 100 = 55.9%
• BCHR이 SQL성능을 좌우하지만, BCHR이 높다고해서 효율적인 SQL을 의미하지는 않는다.
• 같은 블록을 비효율적으로 반복해서 읽으면 BCHR이높아진다.

3. 6 SINGLE BLOCK I/O VS MULTI BLOCK I/O

• 메모리에 모두올리면 좋지만 현실적으로 불가.
• 캐싱모소한 데이터 블록은 I/O CALL을통해 디스크에서 버퍼캐시로 적재하고 읽는다.
• Single Block I/O : 한번에 한 블록씩 요청해서 메모리에 적재하는 방식, 기본적으로 사용
  • 인덱스 루트 블록 읽을때
  • 인덱스 루트블록에서 얻은 주소정보로 브랜치 블록 읽을때
  • 인덱스 브랜치 블록에서 얻은 주소 정보로 리프 블록을 읽을때
  • 인덱스 리프 블록에서 얻은 주소 정보로 테이블 블록을 읽을때
• Multi Block I/O : 한번에 여러 블록씩 요청해서 메모리에 적재하는 방식
  • 많은 데이터블록을 읽을때 효율적
  • 인덱스 이용하지않는 테이블 전체스캔
  • 테이블이 클수록 Multiblock I/O단위도 크면 좋다.
• 읽고자하는 블록을 버퍼캐시에서찾지못하면 디스크에서읽기위해 I/O Call, 그동안 프로세스는 대기큐에서 휴식
• 대용량 테이블이면 수많은 블록을읽는동안 여러차례 쉴텐데, 기왕에 하는거 한꺼번에 많은양을 요청해야잠자는 횟수를 줄이고 성능을 높일 수 있다.
• 일반적으로 OS레벨 I/O단위가 1MB, 오라클 레벨 I/O단위가 8KB이므로 파라미터를 128로설정하면 담을수있는만큼 최대한 담게 된다(8KB * 128 = 1MB), 1MB이상은 담을수없으므로 그이상설정해도 소용없다.
  오라클에서 그이상 설정은 간으하지만, OS는 자신의 I/O단위만큼씩만 읽는다.
• 인접한 블록이란 익스텐트에 속한블록, MULTIBLOCK I/O방식으로 읽어도 익스텐트 경계를 넘지못한다.
  예를들어 한 익스텐트에 20개 블록이 담겨있고, Multiblock I/O 단위가 8이라고할대, 세번째 I/O Call에서는 네개 블록만 얻게된다. 이때 네개를 더읽기 위해 다음 익스텐트까지 읽지 않는다.
• Multi block I/O중간에 Single Block I/O가 발생하는 이유 59페이지!

3.7 Table Full Scan VS Index Range Scan

• 무조건 풀스캔이 나쁜것이아니다. 접근해야할 데이터양에따라 full scan, index scan을 구분하여사용해야한다.
• 데이터가많은데 인덱스 스캔을할경우 계속 Single Block I/O가발생하는데, 이럴땐 Multi Block I/O로 풀스캔하는것이 더빠르다.

3.8 캐시 탐색 메커니즘

• 버퍼캐시 탐색과정을 거치는 명령

  • 인덱스 루트블록 읽을때
  • 인덱스 루트블록에서 얻은 주소정보로 브랜치블록읽을때
  • 인덱스 브랜치 블록에서 얻은 주소정보로 리프블록을 읽을때
  • 인덱스 리프블록에서 얻은 주소정보로 테이블블록읽을때
  • 테이블 블록을 full scan할때

• DBMS는 버퍼캐시를 해시구조로 관리

• 해시구조 특징

  • 같은 입력값은 항상 동일한 해시 체인(=버킷)에 연결됨
  • 다른 입력값(예를들어 4와 9)이 동일한 해시체인(=버킷)에 연결될 수 있음
  • 해시 체인 내에서는 정렬이 보장되지 않음.

• 메모리 공유자원에 대한 엑세스 직렬화

  • 하나의 버퍼블록을 두 개 이상의 프로세스가 '동시에' 접근하려고할때 문제가 발생
  • 동시접근하면 블록 정합성에 문제가 있을수있음.
  • 자원을 공유하는것처럼보이지만 내부에선 한 프로세스씩 순차접근하도록 구현해야하며 이를 위해 직렬화(Serialization)메커니즘이 필요하다. 쉽게말해 줄세우기다)

• 해시 체인 레치 : 자물쇠를 열수있는 키를 획득한 프로세스만이 체인으로 진입가능

• 버퍼 lock : 버퍼블록 자체 직렬화 매커니즘

• 이런 직렬화 메커니즘에 의한 캐시 경합을 줄이려면, SQL튜닝을 통해 쿼리일량(논리적 I/O) 자체를 줄여야 한다.

요약!!!!!!!!!!!!!!!!

물리적I/O횟수를 줄이자! 물리적 I/O를 줄이려면 논리적I/O를줄이도록 SQL을 튜닝하자!!!
논리적 I/O를 줄이면 DISK I/O뿐아니라  직렬화에의한 캐시 경합을 줄여서 속도향상을할수있다
I/O횟수를 줄이는것이 SQL튜닝의 핵심이다!!!!

무조건 인덱스스캔이 좋은것은아니다!!!!!! 데이터양에따라 풀스캔(MULTIBLOCK I/O)가 좋을수도있다
CARDINALITY를 보고 잘판단해서 결정해야한다!!!!!!!!!!!!!!!!!