DB/친절한 SQL 튜닝

1. SQL 처리과정과 I/O

wooweee 2023. 8. 21. 18:34
728x90

 

1.1 SQL 파싱과 최적화

1.1.1 구조적 집합적 선언적 질의 언어

  • 용어
    • sql: structured Query Language (구조적 질의 언어)
    • 사용자 → 옵티마이저 → 프로시저
    • sql 옵티마이저 : 프로시저를 만들어내는 dmbs 내부 엔진 == 프로그래밍을 대신해 주는 역할
    • SQL 최적화 : DBMS 내부에서 프로시저를 작성하고 컴파일해서 실행 가능한 상태로 만드는 전 과정

 

1.1.2 SQL 최적화

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

 

1.1.3 SQL 옵티마이저

  • sql 옵티마이저
    • 사용자가 원하는 작업을 가장 효율적으로 수행할 수 있는 최적의 데이터 액세스 경로를 선택해 주는 DBMS 핵심 엔진
  • 참고
    • sql 옵티마이저는 sql 파서, 로우 소스 생성기와 같이 서버 프로세스가 가진 기능일 뿐이다.
    • 백그라운드 프로세스가 아니다. 백그라운드 프로세스: dbwr, lgwr, pmon, smon

 

1.1.4 실행계획과 비용

  • 실행계획을 잘 읽을 줄 알아야 한다.
  • dbms에도 sql 실행경로 미리보기 기능이 있다. 실행계획이 바로 그것이다.
  • sql 옵티마이저가 생성한 처리절차를 개발자가 확인 할 수 있게 트리 구조로 표현한 것이 실행계획
  • 토드나 오렌지 같은 쿼리 툴을 사용

실행계획 예시

0    SELECT STATEMENT Optimizer=ALL_ROWS (Cost=209 Card=5 Bytes=175)
1 0  TABLE ACCESS (BY INDEX ROWID) OF ’ EMP' (Cost=2 Card=5 Bytes=85)
2 1    NESTED LOOPS (Cost=209 Card=5 Bytes=175)
3 2    TABLE ACCESS (BY INDEX ROWID) OF ’ DEPT ’ (Cost=207 Card=1 Bytes=18)
4 3      INDEX (RANGE SCAN) OF 'DEPT_LOC_IDX ’ (NON-UNIQUE) (Cost=7 Card=1)
5 2    INDEX (RANGE SCAN) OF ’ EMP_DEPTNO_IDX ’ (NON-UNIQUE) (Cost=1 Card=5)
  • 옵티마이저가 T_X01 인덱스를 선택한 근거 = 비용
  • 비용 : 쿼리를 수행하는 동안 발생할 것으로 예상하는 I/O 횟수 또는 예상 소요시간을 표현한 값
  • 주의 : 비용은 어디까지나 예상시간이기 때문에 수동으로도 설정할 줄 알아야한다.

 

1.1.5 옵티마이저 힌트

  • 힌트 사용법 : 주석 기호에 ‘+’를 붙이기
  • SELECT FROM WHERE /*+ INDEX(A 고객_PK) */ 고객명/ 연락처/주소/가입일시 고객 A 고객ID= ’000000008'
  • 주의 사항 존재

 

1.2 SQL 공유 및 재사용

1.2.1 소프트 파싱 vs 하드 파싱

  • SGA(system global area)
    • 서버 프로세스와 백그라운드 프로세스가 공통으로 액세스하는 데이터와 제어 구조를 캐싱하는 메모리 공간
    • 라이브러리 캐시 : sql 파싱, 최적화, 로우 소스 생성과정을 거쳐 생성한 내부 프로시저를 캐시 한 것
    • db 캐시 : table data를 캐시한 것
  • 소프트 파싱 : sql을 캐시에서 찾아 곧바로 실행단계로 넘어가는 것
  • 하드 파싱 : 소프트 파싱 실패해서 ‘파싱, 최적화, 로우 소스 생성’ 단계까지 모두 거치는 것

1.2.2 바인드 변수의 중요성

  • 이름없는 sql 문제
    • sql은 이름이 따로 없다.
    • sql 자체가 이름이기 때문에 텍스트 중 작은 부분이라도 수정되면 그 수간 다른 객체로 생성되는 문제점을 가진다.
    • 일회성 및 무효화된 sql까지 모두 저장하려면 많은 공간이 필요하고, 그만큼 sql을 찾는 속도도 느려진다. 따라서 sql을 영구 저장하지 않는 쪽을 선택한다.
  • 공유 가능 sql
    • sql을 찾기 위해 사용하는 키 값 = sql 문 그 자체
    • 의미적으로 모두 같지만, 실행할 때 각각 최적화를 진행하고 라이브러리 캐시에서 별도 공간을 사용한다.
    • SELECT * FROM CUSTOMER WHERE LOGIN_ID = ?
    • ? : 바인딩 변수 - 성능 , 보안
    • 매개 변수가 있는 함수
    • prepared statement

 

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

1.3.1 SQL이 느린 이유

  • 99% I/O 때문
  • sql 쿼리가 I/O를 찾는 동안 process가 멈춤상태로 존재한다. 쓰레드

1.3.2 데이터베이스 저장 구조

  • 테이블 스페이스 - 세그먼트 - 익스텐트 - 블록 - 로우
    • 블록 : 데이터를 읽고 쓰는 단위
    • 블록 1개 : 하나의 테이블이 독점, 한 블록에 저장된 레코드는 모두 같은 테이블 레코드다
    • 익스텐트 : 공간을 확장하는 단위, 연속된 블록 집합
    • 익스텐트 1개 : 하나의 테이블이 독점 == 한 익스텐트에 담긴 블록은 모두 같은 테이블 블록
    • 세그먼트 : table space를 object 종류에 따라 나눈 것
    • 테이블 스페이스 : 모든 object 저장하는 공간, 여러개 데이터 파일
    • 데이터 파일 : 디스크 상의 물리적인 OS 파일
  • 참고
    • 블록 == 페이지
    • 세그먼트에 할당된 모든 익스텐트가 같은 데이터파일에 위치하지 않을 수 있다.
  • DBA(data block address)
    • 모든 데이터 블록은 디스크 상에서 몇 번 데이터 파일의 몇번째 블록인지를 나타내는 자신만의 고유 주소값를 DBA 라고 한다.
    • 인덱스를 이용해 테이블 레코드를 읽을 때는 인덱스 rowid 이용
    • rowid : dba + 로우 번호(블록 내 순번)
    • table 스캔 할 때 테이블 세그먼트 헤더에 저장된 익스텐트 맵을 이용

1.3.3 블록 단위 I/O

  • 한번 읽을 때 8k 씩 읽는다.

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

테이블 또는 인덱스 블록 을 읽는 방식

  1. 시퀀셜 액세스 : 논리적 또는 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식
    • 블록간에 서로 논리적인 연결고리를 갖고 있지 않는데 시퀀셜 방식으로 액세스 할 수 있는 이유?
      • 세그먼트 헤더에 map 으로 세그먼트에 할당된 익스텐트 목록을 관리 = 익스텐트 맵
      • 익스텐트 맵 은 각 익스텐드의 첫 번째 블록 주소 값을 가짐
      • 각 익스텐트의 첫 번째 블록 뒤에 연속해서 저장된 블록을 순서대로 읽으면, 이게 full table scan
  2. 랜덤 액세스 : 논리적, 물리적인 순서를 따르지 않고, 레코드 하나를 읽기 위해 한 블록씩 접근하는 방식

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

  • 데이터 블록을 읽을 땐 항상 버퍼캐시부터 탐색
  • 없을시, I/O 뒤짐

1.3.6 Single Block I/O vs Multiblock I/O

  • 데이터를 모두 캐시에 적재할 수 없기 때문에 디스크에서 받아와서 적재를 해야한다.
  • 읽고 적재하는 방식 2가지
    1. Single block : 한 번에 한 블록씩 요청해서 메모리에 적재하는 방식
      • 인덱스를 이용할 때 기본적으로 인덱스와 테이블 블록 모두 single block 방식 사용
    2. Muliti block : 한 번에 여러 블록씩 요청해서 메모리에 적재하는 방식

1.3.7 Table Full Scan vs Index Range Scan

  • 테이블에 저장된 데이터를 읽는 2가지 방식
    1. table full scan
    2. index range scan

과목 : 친절한 sql 튜닝

1단원 sql 처리과정과 i/o

1.1.2 sql 파싱과 최적화

sql 최적화

  1. sql 파싱
    1. 파싱 트리 생성
    2. syntax 체크
    3. semantic 체크
  2. sql 최적화 : 통계 정보 바탕으로 다양한 실행경로를 생성해서 비교후 가장 효율적인 하나를 선택, db 성능을 결정하는 가장 핵심적인 엔진
  3. 로우 소스 생성

1.1.3 sql 옵티마이저

dbms에도 sql 실행경로 미리보기 기능이 존재, 실행계획이 바로 그것, sql 옵티마이저가 생성한 처리절차를 사용자가 확인할 수 있게 아래와 같이 트리 구조로 표현한 것

1. SQL 처리과정과 I/O

1.1 SQL 파싱과 최적화

1.1.1 구조적 집합적 선언적 질의 언어

  • 용어
    • sql: structured Query Language (구조적 질의 언어)
    • 사용자 → 옵티마이저 → 프로시저
    • sql 옵티마이저 : 프로시저를 만들어내는 dmbs 내부 엔진 == 프로그래밍을 대신해 주는 역할
    • SQL 최적화 : DBMS 내부에서 프로시저를 작성하고 컴파일해서 실행 가능한 상태로 만드는 전 과정

1.1.2 SQL 최적화

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

1.1.3 SQL 옵티마이저

  • sql 옵티마이저
    • 사용자가 원하는 작업을 가장 효율적으로 수행할 수 있는 최적의 데이터 액세스 경로를 선택해 주는 DBMS 핵심 엔진
  • 참고
    • sql 옵티마이저는 sql 파서, 로우 소스 생성기와 같이 서버 프로세스가 가진 기능일 뿐이다.
    • 백그라운드 프로세스가 아니다. 백그라운드 프로세스: dbwr, lgwr, pmon, smon

1.1.4 실행계획과 비용

  • 실행계획을 잘 읽을 줄 알아야 한다.
  • dbms에도 sql 실행경로 미리보기 기능이 있다. 실행계획이 바로 그것이다.
  • sql 옵티마이저가 생성한 처리절차를 개발자가 확인 할 수 있게 트리 구조로 표현한 것이 실행계획
  • 토드나 오렌지 같은 쿼리 툴을 사용

실행계획 예시

0    SELECT STATEMENT Optimizer=ALL_ROWS (Cost=209 Card=5 Bytes=175)
1 0  TABLE ACCESS (BY INDEX ROWID) OF ’ EMP' (Cost=2 Card=5 Bytes=85)
2 1    NESTED LOOPS (Cost=209 Card=5 Bytes=175)
3 2    TABLE ACCESS (BY INDEX ROWID) OF ’ DEPT ’ (Cost=207 Card=1 Bytes=18)
4 3      INDEX (RANGE SCAN) OF 'DEPT_LOC_IDX ’ (NON-UNIQUE) (Cost=7 Card=1)
5 2    INDEX (RANGE SCAN) OF ’ EMP_DEPTNO_IDX ’ (NON-UNIQUE) (Cost=1 Card=5)
  • 옵티마이저가 T_X01 인덱스를 선택한 근거 = 비용
  • 비용 : 쿼리를 수행하는 동안 발생할 것으로 예상하는 I/O 횟수 또는 예상 소요시간을 표현한 값
  • 주의 : 비용은 어디까지나 예상시간이기 때문에 수동으로도 설정할 줄 알아야한다.

1.1.5 옵티마이저 힌트

  • 힌트 사용법 : 주석 기호에 ‘+’를 붙이기
  • SELECT FROM WHERE /*+ INDEX(A 고객_PK) */ 고객명/ 연락처/주소/가입일시 고객 A 고객ID= ’000000008'
  • 주의 사항 존재

1.2 SQL 공유 및 재사용

1.2.1 소프트 파싱 vs 하드 파싱

  • SGA(system global area)
    • 서버 프로세스와 백그라운드 프로세스가 공통으로 액세스하는 데이터와 제어 구조를 캐싱하는 메모리 공간
    • 라이브러리 캐시 : sql 파싱, 최적화, 로우 소스 생성과정을 거쳐 생성한 내부 프로시저를 캐시 한 것
    • db 캐시 : table data를 캐시한 것
  • 소프트 파싱 : sql을 캐시에서 찾아 곧바로 실행단계로 넘어가는 것
  • 하드 파싱 : 소프트 파싱 실패해서 ‘파싱, 최적화, 로우 소스 생성’ 단계까지 모두 거치는 것

1.2.2 바인드 변수의 중요성

  • 이름없는 sql 문제
    • sql은 이름이 따로 없다.
    • sql 자체가 이름이기 때문에 텍스트 중 작은 부분이라도 수정되면 그 수간 다른 객체로 생성되는 문제점을 가진다.
    • 일회성 및 무효화된 sql까지 모두 저장하려면 많은 공간이 필요하고, 그만큼 sql을 찾는 속도도 느려진다. 따라서 sql을 영구 저장하지 않는 쪽을 선택한다.
  • 공유 가능 sql
    • sql을 찾기 위해 사용하는 키 값 = sql 문 그 자체
    • 의미적으로 모두 같지만, 실행할 때 각각 최적화를 진행하고 라이브러리 캐시에서 별도 공간을 사용한다.
    • SELECT * FROM CUSTOMER WHERE LOGIN_ID = ?
    • ? : 바인딩 변수 - 성능 , 보안
    • 매개 변수가 있는 함수
    • prepared statement

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

1.3.1 SQL이 느린 이유

  • 99% I/O 때문
  • sql 쿼리가 I/O를 찾는 동안 process가 멈춤상태로 존재한다. 쓰레드

1.3.2 데이터베이스 저장 구조

  • 테이블 스페이스 - 세그먼트 - 익스텐트 - 블록 - 로우
    • 블록 : 데이터를 읽고 쓰는 단위
    • 블록 1개 : 하나의 테이블이 독점, 한 블록에 저장된 레코드는 모두 같은 테이블 레코드다
    • 익스텐트 : 공간을 확장하는 단위, 연속된 블록 집합
    • 익스텐트 1개 : 하나의 테이블이 독점 == 한 익스텐트에 담긴 블록은 모두 같은 테이블 블록
    • 세그먼트 : table space를 object 종류에 따라 나눈 것
    • 테이블 스페이스 : 모든 object 저장하는 공간, 여러개 데이터 파일
    • 데이터 파일 : 디스크 상의 물리적인 OS 파일
  • 참고
    • 블록 == 페이지
    • 세그먼트에 할당된 모든 익스텐트가 같은 데이터파일에 위치하지 않을 수 있다.
  • DBA(data block address)
    • 모든 데이터 블록은 디스크 상에서 몇 번 데이터 파일의 몇번째 블록인지를 나타내는 자신만의 고유 주소값를 DBA 라고 한다.
    • 인덱스를 이용해 테이블 레코드를 읽을 때는 인덱스 rowid 이용
    • rowid : dba + 로우 번호(블록 내 순번)
    • table 스캔 할 때 테이블 세그먼트 헤더에 저장된 익스텐트 맵을 이용

1.3.3 블록 단위 I/O

  • 한번 읽을 때 8k 씩 읽는다.

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

테이블 또는 인덱스 블록 을 읽는 방식

  1. 시퀀셜 액세스 : 논리적 또는 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식
    • 블록간에 서로 논리적인 연결고리를 갖고 있지 않는데 시퀀셜 방식으로 액세스 할 수 있는 이유?
      • 세그먼트 헤더에 map 으로 세그먼트에 할당된 익스텐트 목록을 관리 = 익스텐트 맵
      • 익스텐트 맵 은 각 익스텐드의 첫 번째 블록 주소 값을 가짐
      • 각 익스텐트의 첫 번째 블록 뒤에 연속해서 저장된 블록을 순서대로 읽으면, 이게 full table scan
  2. 랜덤 액세스 : 논리적, 물리적인 순서를 따르지 않고, 레코드 하나를 읽기 위해 한 블록씩 접근하는 방식

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

  • 데이터 블록을 읽을 땐 항상 버퍼캐시부터 탐색
  • 없을시, I/O 뒤짐

1.3.6 Single Block I/O vs Multiblock I/O

  • 데이터를 모두 캐시에 적재할 수 없기 때문에 디스크에서 받아와서 적재를 해야한다.
  • 읽고 적재하는 방식 2가지
    1. Single block : 한 번에 한 블록씩 요청해서 메모리에 적재하는 방식
      • 인덱스를 이용할 때 기본적으로 인덱스와 테이블 블록 모두 single block 방식 사용
    2. Muliti block : 한 번에 여러 블록씩 요청해서 메모리에 적재하는 방식

1.3.7 Table Full Scan vs Index Range Scan

  • 테이블에 저장된 데이터를 읽는 2가지 방식
    1. table full scan
    2. index range scan
  •  
저작자표시 비영리 변경금지

'DB/친절한 SQL 튜닝'의 다른글

  • 현재글1. SQL 처리과정과 I/O

관련글

티스토리툴바