성능 차이가 발생하는 이유
SQL 이 느린 이유는 거의 대부분 디스크 I/O에서 발생하기 마련이다 컴공을 공부하다 보면 마주하는 이슈 중에 하나가 CPU의 속도에 비해서 데이터가 저장이 되어 있는 하드디스크의 I/O 속도는 매우 느린 편에 속합니다 CPU는 수많은 프로세서들을 동시에 처리하고 있는데 생성 -> 준비 -> 실행 -> 대기 이때 수많은 프로세스를 거치는 와중에 SQL의 하드 파싱의 끝 로우 소스 생성에 의해서 프로시저가 생성되면 이를 통해서 디스크의 I/O 프로세서를 가동해서 데이터의 원천을 가져오게 됩니다 이때 CPU 와 하드디스크의 속도 차이로 인해서 디스크에서 데이터를 읽는 도중에 해당 프로세서는 대기 상태로 빠지게 되고 주기적으로 CPU 가 해당 데이터를 전부 찾아왔는지 점검을 하고 다 찾아왔으면 그것을 우리에게 반환을 해주게 됩니다 결국 SQL의 성능의 차이는 디스크 I/O 속도에 의해서 출발하게 됩니다
데이터를 저장하려면 먼저 테이블 스페이스를 생성해야 한다 테이블 스페이스는 세그먼트를 담든 컨테이너로서 여러 개의 데이터 파일로 구성된다 테이블 스페이스를 생성했으면 세그먼트를 생성한다 세그먼트는 테이블, 인덱스처럼 데이터 저장 공간이 필요한 오브젝트 세그먼트는 여러 익스텐트로 구성되는데 파티션 구조가 아니라면 테이블도 하나의 세그먼트요 인덱스도 하나의 세그먼트다 이때 익스텐트는 공간을 확장을 뜻하는데 테이블이나 인덱스를 할당하다가 공간이 부족하면 해당 오브젝트가 속한 테이블 스페이스로부터 익스텐트를 추가로 할당받게 됩니다
위의 그림처럼 하나의 익스텐드 안에 테이블 정보든 인덱스 정보가 담기긴 하지만 물리적인 데이터는 데이터 블록이라는 곳에 저장이 된다 하나의 블록은 하나의 테이블이 독점하고 한 블록에 저장된 레코드는 모두 같은 테이블 레코드이다 다만 익스텐트가 확장될 때는 모든 인스텐트가 같은 데이터 파일 안에 있지 않을 수도 있다 정확하게 말하면 99% 확률로 다른 곳에 위치하게 되는데 이는 파일 경합을 줄이고 데이터를 가능한 여러 데이터 파일로 분산해서 저장하기 때문이다
Data Block Address
모든 데이터는 디스크 상에서 몇 번 데이터파일의 몇 번째 블록인지 나타내는 자신의 고윳값을 가지게 되는데 이를 Data Block Address라고 하고 하나의 데이터를 읽으려면 해당 데이터의 DBA를 읽어야 한다
1
2
3
4
5
6
7
8
9
10
11
12
13
다음에 접속됨:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show parameter block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
SQL>
SQLPLUS에서 다음과 같이 입력하면 8KB의 block_size 가 나오게 되는데 이것이 오라클의 기본 사이즈이고 데이터 1Byte를 읽을 때 기본적으로 8KB인 하나의 블록을 통째로 읽게 되는 것입니다
데이터 블록을 읽는 방법
데이터 블록을 읽는 방법은 시퀀셜 액세스와 랜덤 액세스 두가지가 있습니다
시퀀셜 액세스는 논리적 또는 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식입니다 블록들은 자신의 앞과 뒤를 가리키는 주소값으로 연결이 되어 있는 논리적인 상태입니다
랜덤 액세스 말그대로 랜덤하게 접근하는 것을 말합니다 이는 논리적 물리적 순서를 따르지 않고 레코드 하나를 읽기 위한 한 블록씩 접근하는 방식을 말합니다
SGA (System Global Area)
매번 SQL 처리마다 디스크를 읽게 된다면 이느 매우 비효율적인 방법입니다 그래서 DBMS는 데이터 캐싱 메커니즘이 필요로 하는데 DB 버퍼캐시는 라이브러리 캐시처럼 SGA의 가장 중요한 구성요소입니다 라이브러리 캐시가 하드 파싱 된 SQL 프로시저의 결과물을 저장하는 공간이라면 Data Buffer Cache는 어렵게 디스크에서 읽은 데이터를 캐싱 해둠으로써 같은 블록에 대한 반복적인 디스크 I/O call 을 줄이는데 목표가 있습니다
그렇기에 데이터를 읽을 땐 반드시 Data Buffer Cache를 먼저 읽고 읽고자 하는 데이터 블록이 없으면 디스크 I/O를 수행합니다 참고로 해당 부분은 모든 사용자가 접근할 수 있는 공유 공간입니다
1
2
3
4
5
6
7
8
9
10
11
12
SQL> show sga
Total System Global Area 3221223192 bytes
Fixed Size 9139992 bytes
Variable Size 687865856 bytes
Database Buffers 2516582400 bytes
Redo Buffers 7634944 bytes
SQL>
SQLPLUS에서 다음과 같이 명령어를 입력하면 이중 Database Buffers 가 우리가 위에서 설명하는 데이터 버퍼 캐시 부분에 해당합니다 메모리 캐시가 클수록 좋지만 모든 데이터를 캐시에 적재할 수 없는 이는 비용적인 한계와 물리적인 공간의 한계에 따른 것입니다 전체 데이터 중 일부만 캐시에 적재해서 읽을 수 있고 이때 찾지 못한 데이터 블록들은 다시 I/O call 을 통해서 디스크에서 DB 버퍼캐시로 적재하고 읽게 됩니다
Single Block I/O vs Mulitblock I/O
이는 데이터 블록을 읽을 때 사용하는 방식 중 하나로 한 번에 하나의 블록만 읽는 것을 Single Block I/O라고 하고 한 번에 여러 블록을 읽는 것을 Mulitblock I/O라고 합니다 기본적으로 인덱스와 테이블 블록 모두 Single Block I/O 방식을 채택하고 있고 Mulitblock I/O 많은 데이터 블록을 읽을 때 사용하며 이때는 인덱스를 이용하지 않고 테이블 전체를 스캔할 때 사용하게 됩니다
1
2
3
4
5
6
7
8
9
SQL> show parameter db_file_multiblock_read_count
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 128
SQL>
이는 Mulitblock I/O 한 번에 읽는 데이트 블록의 개수를 말하게 되는데 우리가 위에서 1byte의 데이터를 읽기 위해서 가져오는 최소한의 블록 크기는 8kb임으로 Mulitblock I/O 는 한 번에 데이터를 읽을 때 1MB의 데이터를 읽어오게 돕니다 공식은 128 * 8KB = 1024KB = 1MB 이렇게 되는 것입니다
TableFullScan vs IndexRangeScan
SQL의 성능을 체크함에 있어 반드시 TableFullScan 이 성능에 나쁜 것인가? 반대로 인덱스를 태웠다면 성능에 항상 좋은 것인가 그렇지 않다 테이블 전체를 읽는 것을 앞으로 TableFullScan이라고 할 것이고 반대의 상황은 IndexRangeScan 언뜻 들어보면 테이블 전체를 스캔하는 게 당연히 성능에 좋지 않다고 선입견이 생길 수 있다 하지만 어떤 프로그램은 테이블 전체를 읽는 것이 성능에 더 좋을 수도 있고 반대로 힌트를 줘서 인덱스를 태울 때 성능이 더 떨어질 수도 있다는 것이다
상황은 각기 다른 상황이다 TableFullScan 은 시퀀셜 액세스와 Multiblock I/O 방식으로 디스크 블록을 읽습니다 한 블록에 속한 모든 레코드를 한 번에 읽어 들이고 캐시에서 못 찾으면 한 번의 I/O Call 을 통해서 수십수백 개의 블록을 한꺼번에 캐싱하고 읽는 방식입니다 이 방식을 사용하는 SQL 은 스토리지 스캔 성능이 좋은 만큼 쿼리 성능도 좋아집니다
반면에 소량의 데이터를 찾을 땐 수백만 수천만 건의 데이터를 스캔하는 것은 비효율적입니다 큰 테이블에서 소량 데이터를 검색할 때는 반드시 인덱스를 이용해야 합니다 이때 IndexRangeScan 은 랜덤 액세스와 Single Block I/O 방식으로 디스크 블록을 읽습니다 캐시에서 블록을 못 찾으면 레코드 하나를 읽기 위해서 반복적인 I/O Call 이 일어나고 그때마다 프로세서는 대기 상태로 빠지게 됩니다 그래서 많은 데이터를 읽을 때는 불리한 메커니즘입니다 이는 위에서 스토리지 성능에 따른 성능 변화가 없음으로 반드시 쿼리나 인덱스로 성능을 향상시켜주어야 합니다