[이론] chapter 02. SQL 문장의 실행 원리

[select 문장 실행원리]

1. 사용자 SQL 문장 수행

2. user process가 server process를 찾아서 SQL문장을 전달

3. oracle server process는 SQL을 분석 (SQL parser -> parse tree 생성)

4. 문법검사(syntax) -> 의미검사(semantic) -> 권한검사(dictionary cache사용=row cache)

5. 실행계획검사: SQL을 ASCII값으로 변경 -> hash함수로 hash값 생성 -> library cache의 hash list 확인

커서공유(soft parse) : libarary cache의 hash list를 확인하여 동일한 값이 있는지 확인하는 과정

hash bucket : hash list는 여러개의 hash bucket으로 이루어져있고(chain), hash value와 매칭됨

cursor : 실제 데이터 저장되는 임시공간, hash bucket당 cursor 여러개 chain 형태의 구조

공유커서=부모커서+자식커서 / 부모커서(SQL문장), 자식커서(사용자정보, 옵티마이저정보)

커서공유는 parent cursor와 child cursor가 모두 완전히 동일해야 soft parse성공

hash value-> hash bucket-> cursor (heap구조: 순서보장x, 빈자리저장, 저장속도↑, 검색속도↓)

library cache에 원하는 SQL과 실행계획이 들어있는 커서를 찾기위해서는 반드시 hash list 읽어야함

6. hard parse: soft parse 실패 -> 옵티마이저가 data dictionary참고,실행계획 make / 성공 -> 기존실행계획사용

optimizer가 dictionary cache를 참고하여 실행계획을 세우고 library cache에 등록

옵티마이저 : RBO(Rule Based Optimizer), CBO(Cost Based Optimizer, 10g부터 CBO만 지원)

CBO : dictionary 참고하여 규칙을 세움 / RBO : 정해져있는 규칙을 사용해서 실행계획을 세움

옵티마이저가 data dictionary를 참고하여 실행계획을 생성하는데

옵티마이저가 참조하는 dictionary 중 대부분은 static data dictionary -> 사람이관리, 자동업데이트X

10g부터 data dictionary정보를 자동으로 모아주고 관리해주는 기능 but, 딕셔너리관리 중요

7. 바인드(bind) : 바인드 변수값이 들어왔을 경우 변수값만 변경하여 1개의 parsing으로 1개의 실행계획으로 수행

soft parse를 많이 하게되므로 쿼리수행속도 good (hard parse보다 속도빠름)

데이터의 분포도가 균일할 때 bind사용하면 good / 균일하지않으면(편중된데이터) histogram생성

8. 실행(execute) : 디스크의 데이터파일에서 데이터가 들어있는 블록을 찾아 메모리로 복사해오는 과정

작업할 데이터는 반드시 database buffer cache에 존재해야함

①서버프로세스가 찾는 데이터가 들어있는 블록의 주소를 hash 함수로 hash value 생성

②DB buffer cache에 존재하는 데이터파일의 블록주소를 관리하는 hash list와 비교하여 존재여부확인

③원하는데이터가 DB buffer cache존재하면 fetch단계로 넘어감

④원하는데이터가 DB buffer cache존재하지 않는다면 서버프로세스가 디스크의 해당블록을 찾아서 복사

블록단위의 I/O : DB_BLOCK_SIZE 블록크기설정, 사이즈가크면 I/O줄어들지만 공간낭비->적당한크기중요

hash bucket(cache buffers chains latch) / working set(cache buffers lru chain latch)

9. 인출(fetch) : 사용자가 요청한 원하는 데이터만 골라내는 과정

sort 등의 작업은 PGA(Program Global Area)에서 이루어짐

 

[update 문장 실행원리]

- DML문장은 수행되는 원리 동일 (update, insert, delete, merge)

- select 문장 실행원리에서 fetch과정만 생략하고 나머지는 동일한 과정

- 트렌잭션 : 데이터가 변경되는 과정 (redo log buffer-> undo segment -> DB buffer cache)

1. 사용자SQL -> user process -> server process

2. parse : syntax -> semantic -> 권한검사 -> 실행계획검사 (soft parse)

3. hard parse : soft parse실패시, optimizer실행계획 make -> library cache에 등록

4. execute : DB buffer cache의 hash list와 비교하여 데이터존재여부 확인 -> 없으면 DB buffer cache로 복사

5. DB buffer cache에서 DML작업수행시 바로 DB buffer cache의 내용이 변경되지 않음

    ① 변경되는 내용을 redo log buffer에 기록 : 만약의 장애를 복구하기위해 변경내용기록

    ② undo segment에 이전이미지 기록 : DML작업수행중 다른사용자가 원본데이터에 접근할수있게 하기위함

    ③ redo log buffer와 undo segment에 순서대로 기록후 DB buffer cache의 실제데이터 변경