'친절한 SQL 튜닝' 요약정리 1탄: 용어정리

 

(주)디비안 조시형 대표님의  '친절한 SQL 튜닝의 바이블'  

구입하시길 강력 추천드립니다.

책 구입을 원하시는분은 요기를 클릭하시면 됩니다.

-저자 소개
現) 주식회사 디비안 대표이사 
오라클 성능 고도화 원리와 해법 1, 2 저자 
국가공인 SQLP, DAP 자격검정 전문위원
한국데이터진흥원과 한국DB산업협의회가 공동 주관하는 제1회 우수DB人상을 수상
인터넷 카페 디비안 포럼(WWW.DBIAN.NET) 운영
前) 이랜드, 인슈넷, 디엠에스랩, 엔코아, 비투엔


하단의 내용은 제가 예전에 읽었던 내용을 요약 정리한 것입니다.

 

https://coding-factory.tistory.com/743

 

 

 

1. sql 파싱

 

SQL 문장을 이루는 개별 요소를 분석하고 파싱해서 파싱트리를 생성 이 과정에서  syntax 체크, semantic 체크 이뤄짐

 

 

2. sql 최적화

 

옵티마이저는 가장 효율적인 방법으로 SQL을 수행할 최적의 처리 경로를 생성해주는 DBMS의 핵심 엔진

옵티마이저가 다양한 실행경로를 생성해서 비교후 가장 효율적인 하나를 선택
다양한 실행경로를 생성해서 비교(자동차 내비게이션)



옵티마이저 종류?
규칙 기반 옵티마이저와 비용 기반 옵티마이저

규칙 기반 옵티마이저: 사전에 정의된 규칙기반. 실행 우선순위( 예전방식) <--테이블에 몇개없는데 풀스캔안하고 인덱스 태움.
비용 기반 옵티마이저: 최소비용 계산 실행계획 수립. 엑세스 바용(cost)

https://coding-factory.tistory.com/743

 

3. SGA

 

1)DB buffer cache

2)Redo log buffer

3)Shared pool (Library cache, Data Dictionary Cache)

 

 

4. 소프트 파싱 vs 하드파싱

 

사용자 -> sql 파싱 -> 캐시에 존재 ? yes -> 실행 (소프트 파싱)

                                              ? no ->최적화 ->로우 소트 생성 ->실행(하드 파싱)

 

5. DBA(Data block Address)

 

모든 데이터  블록은 다스크 상에서 몇 번 데이터파일의 몇번째 블록인지 나타내는  고유 주소값을 갖음

데이터를 읽고 쓰는 단위가 블록이므로 데이터를 읽으려면 먼저 DBA부터 확인해야 함

 

 

6. 블록단위 I/O 

 

Sql 느린이유? 디스크I/O 처리하는동안 == 프로세스 SLEEP

블록이 바로 DBMS가 데이터를 읽고 쓰는단위임

데이터 I/o단위가 블록이므로 특정 레코드 하나를 읽고 싶어도 해당 블록을 통째로 읽음

테이블뿐만 아니라 인덱스도 블록 단위로 데이터를 읽고 씀

 

 

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

테이블 또는 인덱스 블록을 엑세스(읽는) 하는 방식

 

시퀀셜 엑세스: 논리적 또는 물리적으로 연결된 순서에 따라(앞or 뒤 순차적) 차례대로 블록을 읽는 방식

인덱스 리프 블록은 앞뒤를 가리키는 주소값을 통해 논리적으로 서로 연결돼 있다. 이 주소 값에 따라 앞 또는 뒤로 순차적으로 스캔하는 방식이 시퀀셜 엑세스다.

 

랜덤 액세스: 논리적, 물리적인 순서를 따르지 않고, 레코드 하나를 읽기 위해 한 블록씩 접근 하는 방식

 

 

8. DB 버퍼캐시

 

DB버퍼캐시도 SGA의 가장 중요한 구성요소중 하나임

라이브러리캐시가 SQL 과 실행계획, DB 저장형 함수/프로시저 등을 캐싱하는 코드캐시라고 한다면 DB 버퍼캐시는 데이터 캐시임

디스크에서 어렵게 읽은 데이터 블록을 캐싱해 둠으로써 같은 블록에 대한 반복적인 I/O call 을 줄이는게 목적

데이터 블록을 읽을때는 항상 버퍼캐시부터 탐색.없을 경우에만 디스크에서 읽음.이때도 디스크 읽기전 버퍼캐시에 적재하고 디스크읽음

 

9. 논리적 I/o, 물리적 I/O

 

논리적 블록 I/o는(==메모리 버퍼캐시 I/O) sql문을 처리하는 과정에 DB버퍼캐시에서 발생한 총 블록 I/o를 말함

ex)둘레2미터 바퀴 자전거로 15km 가려면 바퀴 7500번 회전해야함

SQL이 참조하는 테이블에 데이터를 입력하거나 삭제하지 않는 상황에서 조건절에 같은 변수 값을 입력하면 ,아무리 여러번 실행해도 매번 읽는 블록 수는 같다. SQL을 수행하면서 읽은 총 블록I/O가 논리적 I/O다.

Direct path Read 방식으로 읽는 경우제외하면 모든 블록은 DB 버퍼캐시를 경유해서 읽는다. 따라서 논리적 I/O횟수는 DB 버퍼캐시에서 블록을 읽은 횟수와 일치한다. 논리적 I/O가 메모리 I/O와 같은 개념은 아니지만 결과적으로 수치는 같다

 

 

물리적 블록 I/o는(==디스크 I/O) 디스크에서 발생한 총 블록 I/O를 말함

DB버퍼캐시에서 블록을 찾지 못해 디스크에서 읽은 블록 I/O가 물리적 I/O임

데이터 입력이나 삭제가 없어도 물리적 I/O는 SQL을 실행할 때마다 다르다. 첫번째 실행할 때보다 두번째가 줄어들고 세번째 실행할 땐 더 줄어든다. 연속해서 실행하면 DB 버퍼캐시에서 해당 테이블 블록의 점유일이 점점 높아지기 때문이다. 한참 후에 다시 실행하면 반대로 물리적 I/O가 늘어난다. DB 버퍼캐시가 다른 테이블 블록으로 채워지기 때문이다.

 ex)풍향(순풍/역풍), 도로 기울기(오르막,내리막),도로 상태에 따라 달라짐

 

논리적 블록 I/o(메모리 I/O)는 전기적 신호,물리적 블록 I/o(디스크 I/O)는 액세스 암을 통해 물리적 작용이 일어나므로 메모리 I/O에 비해 1만배느림

 

https://debaeloper.tistory.com/3?category=1019750

 

 

 

10. 버퍼캐시 히트율

 

버퍼캐시의 효율을 측정하는 데 전통적으로 가장 많이 사용해 온 지표는 버퍼캐시 히트율

BCHR(buffer cache hit ratio) 

=(캐시에서 곧바로 찾은 블록수/ 총 읽은 블록수)x100

=((논리적 I/o -물리적 I/O)/ 논리적 I/O) x100

물리적 I/O =논리적 I/0 x (100-BCHR) 

BCHR이 70%, 논리적 I/O가 1만개면 물리적  I/O는 3천개

논리적 I/O를 1천개로 줄이면 물리적 I/O도 3백개

 

 

BCHR은 읽은 전체 블록중에서 물리적인 디스크 I/O를 수반하지 않고 곧바로 메모리에서 찾은 비율  

실제 sql성능을 향상하려면 물리적 I/o가(외생변수) 아닌 SQL 튜닝을 통해 줄일 수 있는 통제 가능한 내생변수인 논리적 I/o를 줄여야함

논리적 I/O를 줄임으로써 물리적 I/O를 줄이는 것이 곧 튜닝임

 목적지에 빨리 이동하려면 내생변수에 변화를 주어야한다. 더 가까운 길을 선택하면 된다.

BCHR공식을 이루는 물리적I/O는 통제 불가능한 외생변수다.(메모리 증설해서 DB 버퍼캐시를 늘리는 방법외에) 이것을 직접 줄일 방법은 없다. 반면, 논리적 I/O는 통제 가능한 내생변수다. SQL 을 튜닝해서 논리적 I/O를 줄이면 물리적 I/O도 줄고 그만큼 성능도 향상된다.

 

다시 말하지만 블록을 읽을 때는 해당 블록을 먼저 버퍼캐시에서 찾아보고 없을때만 디스크에서 읽는다. 이때도 디스크에서 곧바로 읽는게

아니라 먼저 버퍼캐시에 적재하고서 읽는다. 따라서 DB 버퍼캐시에서 읽은 블록에는 디스크에서 읽은 블록이 이미 포함되있다.

 

 

 

 

11. Single Block I/o  vs Multi block I/O

 

한번에 어느 정도의 데이터 블록을 읽어 메모리에 적재하는지

 

캐시에서 찾지 못한 데이터 블록은 I/O call 을 통해 디스크에서 DB 버퍼캐시로 적재하고서 읽을 때 

Single block I/O: I/O call 할때, 한번에 한 블록씩 요청해서 메모리에 적재하는 방식

인덱스 이용시 기본적으로 인덱스와 테이블 블록 모두 Single block I/O방식 사용

 

Multiblock I/O: 여러 블록을 요청해서 메모리에  적재하는 방식 (풀스캔시)

많은 데이터 블록을 읽을 때는 Multiblock I/O가 효율적이다. FUll스캔시 Multiblock I/O 단위를 크게 설정하면 성능이 좋아지는 이유다.

블록을 DB 버퍼캐시에서 찾지 못하면 해당 블록을 디스크에서 읽기 위해 I/O 콜하고 그 동안 프로세스는 대기 큐에서 잠.(대량 데이터 조회할때 여러번 잠자면 비효율)

DBMS사이즈가 얼마건 간에 OS 단에서는 보통 1MB 단위로 I/o를 수행

오라클은 db_file_multiblock_read_count 파라미터로 정한값만큼 가능. 오라클 레벨 I/O단위가 8KB이므로 이 파라미터를 128로 설정하면 최대한 담게 됨(8KB * 128= 1M)

 

https://stackoverflow.com/questions/25139513/oracle-what-is-single-block-vs-multiblock-io

 

 

 

12. Table full scan  vs Index range scan

 

테이블에 저장된 데이터를 읽는 방식은 두가지

Table full scan은 시퀀셜 엑세스와 multiblockI/O 방식으로 디스크 블록을 읽음(한번의 수면)

한 블록에 속한 모든 레코드를 한 번에 읽어 들이고 캐시에서 못 찾으면 한 번의 깊은 수면을 통한 인접한 수십~수백개 블록을 한꺼번에 I/O하는 매커니즘

 

 

Index range scan은 랜덤엑세스와 single Block I/O 방식으로 디스크 블록을 읽음 (매번 수면)

캐시에서 블록을 못 찾으면 레코드 하나를 읽기 위해 매번 잠을 자는 I/O매커니즘임.

 

시퀀셜 엑세스와 Multiblock I/O가 아무리 좋아도 수십~수백건의 소량 데이터를 찾을 때 수백만~수천만 건 데이터를 스캔하는건 비효율적이다. 큰 테이블에서 소량 데이터를 검색할 때는 반드시 인덱스를 이용해야한다.

인덱스는 큰 테이블에서 아주 적은 일부 데이터를 빨리 찾기 위한 도구

읽을 데이터가 일정량을 넘으면 인덱스보다 table full scan이 유리