2. SQL 문장 실행 원리

SQL 문장의 실행원리.

 - 사용자 문장 실행 시 User Process에서 Server Process로 실행한 SQL문 전달.

 - User Process로부터 문장을 받은 Server Process는 해당 문장에 대한 문법체크, 의미체크 후 권한검사 실행

  * 권한 검사란 해당 오브젝트에 접근할 수 있는 권한이 있는지 체크.

 - Parse과정 후 Shared Pool의 Library Cache에서 공유되어있는 실행계획이 있는지 체크.

  * Library Cache에는 한번이라도 실행된 SQL, PL/SQL문장의 실행계획이 공유된다.

  - 실행계획이 있을 경우 Execution 진행. 이 과정을 Soft Parsing 이라고함.

  - 실행계획이 없을 경우 Optimizer를 통해 Data dictionary 등을 참조하여 실행계획을 생성 ( Hard Parsing)

1. Select 문장의 실행원리

1-1) Parse( 구문분석 단계.)

- User Process로부터 전달받은 SQL문장을 Server프로세스가 SQL문을 수행하기 위해 Parse Tree생성.

- Parse Tree를 만드는 과정에서 Syntax Check, Semantic Check 진행

- 데이터 딕셔너리 조회를 통해 문법이 맞는지, 해당 테이블이 있는지 확인. 

  *  자주 사용되는 데이터 딕셔너리는 Shared Pool의 Dictionary Cache에 캐싱해 두어 성능을 높힌다.

- 오류가 없을 경우 SQL문장을 Hash 함수로 Hash Value로 변경한 후 Shared Pool의 Library cache에서 Hash Value와 비교하여 동일한 값이 있는지 확인.

  * 이 과정을 커서공유 또는 Soft Parse라고함.

 

# Cursor :  메모리에 데이터를 저장하기 위해 만든 임시 저장공간(공유 커서, 세션커서, 어플리케이션 커서가 있다.)

1. 공유 커서 역할 

  -  한번 수행된 SQL 문장의 실행계획과 관련 정보를 보관하고있다가 재활용을 통해 Hard Parse의 부담을 줄여 SQL 문장의 수행속도를 빠르게  

  -  커서 공유를 하기 위해서는 사용자와, 옵티마이저 모드가 같아야한다.

2. 공유 커서  구성

 1)  부모 커서 : SQL문장에 대한 값을 저장.

 2) 자식 커서: 사용자 정보, Optimizer 모드 같은 정보 저장.

Hash List : 오라클은 어떤 커서에 들어가있는 데이터 정보를 리스트로 관리 하는데, 이 리스트를 Hash List라고 함.

   Hash List에 있는 정보는 Chain 구조로 다음 데이터가 연결되어있음.

Library Cache안에 원하는 SQL 문과 실행계획이 있는 커서를 찾기 위해서는 Hash List를 읽어야만한다.

- 오라클 에서는 Library cache를 탐색하기위해 Library Cache Latch를 가져야만 한다.

- 사용자가 Select를 했을 경우 Hash List를 확인한 후 원하는 SQL과 실행계획이 없는 경우 서버프로세스는 Hard Parse 과정을 수행한 후 실행계획을 받아서 Library Cache에 신규로 등록한 후 다음단계로 넘어감.

 즉,  해당 SQL을 처음 실행한 경우 Hash List를 업데이트 해야한다. ( Hash List를 update 하거나, 신규로 등록할 경우 Library Cache Latch를 획득한 후 Shared Pool Latche가 추가로 필요함.)

커서공유를 실패한다면, Optimize를 통해 새로운 실행계획을 찾아야 한다.

* 옵티마이저가 새로운 실행계획을 세우는 단계를 Hard Parse라고 한다.

 1) RBO : Rule Based Optimizer.

 2)CBO : Cost Based Optimizer.

- CBO는 실행계획을 세울 때 데이터 딕셔너리 정보를 이용하여 판단.

- 옵티마이저가 참조하는 데이터 딕셔너리 중 대부분은 Static Dictionary 즉 항상 최신의 정보를 가지고있지 않는다.

- 데이터 딕셔너리를 관리해줘야한다.

정리

 - 서버 프로세스가 SQL 문장을 실행하기 위해서는 실행계획이 필요하다.

 - 해당 SQL문장이 이미 수행되어 실행계획이 Shared Pool의 Library Cache에 캐싱 되어있는경우 Soft Parse를 진행한다.

 - 해당 SQL 문장이 처음 실행될 경우 Optimizer를 통해 실행계획을 만들고 이 과정을 Hard Parse라고한다.

 

1-2) Bind.

같은 테이블의 같은 컬럼의 결과를  조회할 경우 Bind 변수를 사용하면, 1번 파싱으로 같은 실행계획을 사용하므로  쿼리 수행속도가 빠를 것이다.

1-3)Execute

Parse와 Bind 단계 후 서버프로세스는 해당 데이터를 가져오기위해 데이터가 저장되어있는 데이터 블록을 찾아 DB Buffer Cache에 복사하는 과정을 Execute라고 함.

* 오라클은 작업을 하기위해 해당 블록을 데이터 파일에서 찾은 후 DB Buffer Cache로 복사해 놓아야한다. 

- 사용자가 찾는 모든 데이터는 SGA의 DB Buffer Cache에 있어야 한다.

- 사용자가 찾거나 변경하는 모든 작업은 DB Buffer Cache에서 작업이 수행된다.

- 서버프로세스는 해당블록을 찾기위해 DB Buffer Cache를 확인 후 없는 경우 데이터 파일로부터 복사해온다.

- DB Buffer Cache는 데이터파일의 블록 주소를 Hash 값으로 변경하여 리스트로 데이터파일 블록을 관리한다.

* DB Buffer Cache에 원하는 블록이 있는지 확인과정.

- 블록 주소를 Hash함수에 넣어 Hash Value를 만든다.

- 해당 Hash Value와 DB Bufffer Cache Hash List를 비교하여 동일한 Hash Value가 DB Buffer Cache에 있는지 검사.

=> 원하는 블록이 있는 경우 다음단계인 Fetch  단계 수행, 없는 경우 하드 디스크에서 해당블록을 DB Buffer Cache에 복사해옴.

1-4) Fetch

- Execute 단계까지 수행하면, 원하는 데이터가 들어있는 블록이 DB Buffer Cache에 올라오게됨.

- 데이터의 I/O 최소 단위가 Block이므로, DB Buffer Cache에 원하는 데이터만 있는것이 아닌 다른 데이터도 있으므로 

사용자가 요청한 데이터만 골라내는 과정을 Fetch라고한다.

- 정렬이 필요하거나 추가 작업을 요구하는 경우 Fetch과정에서 Sort를 하여 데이터를 보내주며, 정렬은 PGA영역(Program Global Area)에서 수행한다.

2. Update 문장의 실행원리.

- 모든 DML의 수행원리는 동일하다.

- DML의 수행단계는 Fetch과정만 없고 나머지는 동일하다.

ex)  UPDATE EMP

      SET SAL= 30000

     WHERE EMPNO=1000;

1) 서버프로세스는 Parse과정 수행

   - Shared Pool 의 Library Cache를 살핀 후 실행계획이 있는지 확인. 있으면 Soft Parse, 없으면 Hard Parse수행

2) 실행계획을 받은 서버프로세스는 Library Cache에 등록한 후 Execute 수행

   - DB Buffer Cache에 emp table의 1000번 사원이 있는지 확인, 없을 경우 데이터파일의 해당 블록을 DB Buffer Cache에 복사.

3)Execute 단계에서 원하는 데이터가 들어있는 DB Buffer Cache를 가져온 후 Server프로세스는 데이터 변경 내역을 Redo Log Buffer에 먼저 기록.

 * Redo Log Buffer: 데이터가 변경될 경우 장애를 복구하기 위해 변경 내역을 기록하는 공간.

                               기록 후 Undo Segment에 원본 이미지를 기록한 후 DB Buffer Cache의 내용을 변경.

*** 데이터 변경이 일어날 경우

Redo Log Buffer에 기록 -> Undo Segment에 기록 -> DB Buffer Cache의 실제 데이터 변경

이 과정을 오라클에서는 Transaction이라고함