SQL 수행구조 - 데이터베이스 I/O메커니즘

블록단위 I/O
오라클을 포함한 모든 DBMS에서 I/O는 블록(SQL Server등 다른 DBMS는 '페이지') 단위로 이루어진다.
즉 하나의 레코드를 읽더라도 레코드가 속한 블록 전체를 읽는다.

블록단위 I/O는 버퍼 캐시와 데이터파일 I/O 모두에 적용된다.

• 데이터파일에서 DB 버퍼 캐시로 블록을 적재할때

• 데이터 파일에서 블록을 직접 읽고 쓸때(Direct Path I/O)

• 버퍼 캐시에서 블록을 읽고 쓸때

• 버퍼캐시에서 변경된 블록을 데이터 파일에 쓸때

  데이터 딕셔너리에 저장된 테이블 및 컬럼정보를 딕셔너리캐시에 적재할 때는 로우단위로 I/O한다. 딕셔너리 캐시를 로우 캐시(Row Cache)라고도 부르는 이유이다.

컬럼 갯수에따른 내부동작 - 전체선택(가) 과 필드선택(나) 비교
(가)SQL은 데이터정렬하는 과정에서 모든컬럼을 소트공간에 저장하므로 더 많은 공간을 사용한다. 모든컬럼을 클라이언트에 전송하므로 네트워크 전송
두 SQL이 읽는 블록개수는 Table Full Scan할대 서로같을 뿐아니라 인덱스를 이용할때도 같다. 블록단위로 I/O하기때문
특정로우 특정컬럼만 골라서 읽을수없다.
다만, (나)SQL에 선택하고자하는 필드를 인덱스에 추가하면, (나) SQL이 더 적은 양의 블록을 읽는다. 테이블 액세스 없이 인덱스 블록만 읽고도 결과집합을 생성할 수 있기 때문이다.

버퍼캐시 탐색 매커니즘
Direct Path I/O를 제외한 모든 블록 I/O는 메모리 버퍼캐시를 경유

• 인덱스 루트 블록을 읽을때
• 인덱스 루트블록에서 얻은 주소정보로 브랜치 블록을 읽을때
• 인덱스 브랜치 블록에서 얻은 주소 정보로 리프 블록을 읽을때
• 인덱스 리프 블록에서 얻은 주소 정보로 데이터 블록을 읽을때
• 테이블 블록을 Full Scan할때
  *병렬 프로세스로 테이블을 Full Scan할때는 Direct Path I/O가 작동하므로 버퍼캐시를 경유하지 않는다.

버퍼캐시 히트율(BCHR)
(캐시에서 곧바로 찾은 블록수 / 총 읽은 블록수) 100
= ((논리적 I/O - 물리적 I/O) / 논리적 I/O) 100
= ( 1 - (물리적 I/O) / 논리적I/O)) 100
= ( 1 - DISK / (QUERY + CURRENT)) 100
논리적 I/O는 SQL수행 과정에 읽은 총 블록수, QUERY항목(=Consistent모드로 읽은 블록 수)과 CURRENT항목(=CURRENT모드로 읽은 블록수)을 더해서 구한다
읽고자 하는 블록을 먼저 캐시에서 찾고, 못 찾으면 디스크에서 찾는다. 따라서 논리적 I/O횟수에는 물리적 I/O횟수가 이미 포함돼 있다.

LRU(Least Recently Used) - 반대는 MRU (Most Recently Used)
모든 DBMS는 사용빈도가 높은 데이터 블록들이 버퍼캐시에 오래 남아있도록 하기 위해 LRU알고리즘을 사용
모든 버퍼 블록 헤더를 LRU체인에 연결해서 사용빈도에 따라 수시로 위치를 옮기다가
Free버퍼가 필요해질때면 액세스 빈도가 낮은 데이터 블록을 수헌하여 밀어낸다. 그러면 자주 액세시되는 블록들이 캐시에 더 오래 남게 된다.

시퀀셜 액세스 & 랜덤 액세스
시퀀셜 액세스는 논리적/물리적으로 연결된 순서에 따라 차례대로 블록을 읽어나가는 방식, 인덱스와 테이블 스캔할때 이 방식을 사용
랜덤 엑세스는 논리적/물리적순서를 따르지 않고, 레코드 하나를 읽기 위한 한불록씩 접근하는 방식, 인덱스를 스캔하면서 얻은 ROWID로 테이블 블록을 액세스할때 이방식을 사용

SingleBlock I/O
인덱스를 이용할 때는 기본적으로 인덱스와 테이블 블록 모두 Single Block I/O 방식을 사용한다.
구체적으로 아래목록이 Single Block I/O대상 오퍼레이션이다.
인덱스는 소량 데이터를 읽을때 주로 사용하므로 이 방식이 효율적이다.

• 인덱스 루트 블록을 읽을때

• 인덱스 루트블록에서 얻은 주소정보로 브랜치 블록을 읽을때

• 인덱스 브랜치 블록에서 얻은 주소 정보로 리프 블록을 읽을때

• 인덱스 리프 블록에서 얻은 주소 정보로 테이블 블록을 읽을때

  테이블 full scan이나 인덱스를 fast full scan할때는 MultiBlock I/O방식을 사용
  인덱스를 통해 테이블을 읽을때는 Single Block I/O방식을 사용하므로 익스텐트 크기에 따라 I/O Call 횟수가 달라지지 않는다.
  db file sequential read대기이벤트가 발생한다

MultiBlock I/O
캐시에서 찾지 못한 특정 블록을 읽으려고 I/O Call할때 디스크상에 그 블록과 '인접한' 블록들을 한꺼번에 캐시에 미리 적재하는 기능이다.
Multiblock I/O 단위는 db_file_multiblock_read_count파라미터에 의해 결정된다.

'인접한 블록'이란 같은 익스텐션에 속한 블록을 의미하며, Multiblock I/O 방식으로 읽더라도 익스텐트 경계를 넘지못한다.
예를들어 한 익스텐트에 20개 블록이 담겨있고 Multiblock I/O단위가 8이라고할때 세번째 I/O Call에서는 4개 블록만 얻게된다.
이때 8개를 마저 채우기 위해 다음 익스텐트까지 읽지 않는다.

db file scattered read대기 이벤트가 나타난다.

병렬쿼리
/+ parallel(EMP, 10)/ 파라미터를 지정하지않을수있고, 테이블만, 병렬 프로세스 갯수도 지정할수있다.
병렬 프로세스 갯수 지정하지 않으면 시스템디폴트 갯수만큼 기동될 수 있음.
10개를 지정했다고 해서 10개가 뜨는건아니고 파라메타설정과 시스템 자원 리소스 여유에따라 자동으로 조절됨.
정확한 확인을 위해서는 Trace나 실시간SQL 모니터링을 이용할수 있다.

1개의 SQL처리시 1개프로세스를 띄워일하는데 빠른실행을 위해 8/16.59~~~ 프로세스를 병렬로 띄워서 SQL처리

SQL이 동시 많이 실행되는 OLTP환경에서는 자제하고, 큰 SQL을 FROM TIME TO TIME실행하는 DW환경에서 많이 사용.

병렬쿼리를 자주 수행하면 CPU와 메모리 자원을 많이사용하고, 잦은 체크포인트 수행으로 LGWR의 작업량이 증가해
커밋성능이 지연되는 등 온라인 트랜잭션 처리에 나쁜 영향을 줄수 있다.