Chapter 02. SQL 문장의 실행 원리

 

 

 

< SQL 문장이 수행되는 과정>

 

 

  - User Process : SQL을 작성하는 프로그램 (SQL*PLUS, SQL Developer, Toad ···)
  - 권한검사 : 어떤 사용자가 해당 오브젝트에 접근 할 수 있는 권한여부를 확인하는 과정
  - Library Cache : 한번이라도 실행된 SQL 또는 PL/SQL 문장과 해당 문장의 실행계획이 공유되어 있는 공간
  - 옵티마이저 : 네이게이션 같은 프로그램, Data Dictionary 등을 참조하여 실행계획을 생성

 

 ▣ SQL 문장이 수행되는 과정

  ① User Process는 자신이 가져온 SQL 문장을 Server Process에 전달해주고 결과가 나올 때까지 기다림
  ② SQL문장을 받은 서버프로세스는 문법검사(Syntax check) 의미검사(Semantic Check)를 한 후 권한검사
  ③ Shared Pool의 Library Cache 검사 (=실행 계획이 있는지 검사)
      ⇒ Soft Parse
  ④ ┌ 실행 계획이 있는 경우 : Execution 단계로 진행
       └ 실행 계획이 없는 경우(=공유 X, Soft Parse 실패) : 옵티마이저를 찾아가 실행계획을 만들어 달라고 요청
         ⇒Hard Parse

  * Soft Parse를 실패할 경우에만 Hard Parse!!

 

 

1. SELECT 문장의 실행 원리

 

 

<SELECT 문장의 수행 단계>

 (1) Parse(구문 분석)
  * SQL문을 전달받은 서버프로세스는 해당 SQL문을 처리하는데 가장 먼저 SQL Parser를 통해서

     각 SQL 문장에 쓰인 키워드나 컬럼명등을 분석해서 Parse Tree라는 것을 생성

  * Parse Tree를 만드는 과정에서 문법 검사 등을 하고 이상이 없으면 의미검사를 한다.
    ⇒ key word(SELECT, FROM등)의 스펠링이 틀릴 경우 Parse Tree 단계에서 오류가 생기고
       스펠링은 맞지만 없는 테이블을 조회했다면 Semantic Check(의미검사) 단계에서 에러 발생

 

  - Dictionary Cache : 해당 문법이 맞는지 틀린지 여부나 테이블의 유무를 알기 위해

                                데이터 딕셔너리를 사용하게 되고 자주 사용하는 데이터 딕셔너리를 캐싱해 두어서

                                성능을 높이는 역할을 하는 곳.

  * 오류가 없는 SQL문장을 ASCII값으로 변경한 후 해당 숫자 값을 HASH 함수를 통해 특정 HASH 값으로 

     변경하게 됨.

  * HASH Value를 Shared Pool의 Library Cache에 있는 Hash Value들과 비교해서 동일한 값이 있는지를 확인    

    ⇒ 커서 공유 또는 Soft Parse

 

  - Hash 함수 : 주로 오라클에서 두 개의 데이터가 서로 같은지 다른지를 비교할 때 많이 사용됨
  - 커서(Cursor) : 메모리에 어떤 데이터를 저장하기 위해 만드는 임시 저장 공간
                         (공유 커서, 세션커서, 어플리케이션 커서)
  - 공유 커서 : 이미 한번 수행되었던 SQL 문장의 실행계획과 관련 정보를 보관하고 있다가 재활용하므로써 

                     Hard Parse(옵티마이저가 실행 계획을 만들어주는 것)의 부담을 줄여 SQL 문장의 수행 속도를

                     빠르게 하는 것.

  * 동일한 SQL 문장이라 하더라도 사용자가 다르거나 옵티마이저 모드가 다르면 커서 공유를 할 수 없다.

 

  - 공유 커서 ┌ 부모 커서(Parent Cursor) : SQL 문장 자체에 대한 값
                  └ 자식 커서(Child Cursor) : 사용자 정보, 옵티마이저 모드 정보
     ⇒ 즉, 커서 Parent Cursor와 Child Cursor가 완전히 동일해야만 이루어짐

 

  - Hash List : 어떤 커서에 어떤 데이터가 들어있는지를 리스트로 관리하는 것(라이브러리 캐시 내용 저장)
    ⇒ 라이브러리 캐시 안에서 원하는 SQL 문과 실행 계획이 들어있는 커서를 찾기 위해서는

        반드시 Hash List를 읽어야 한다.

  * Hash List가 1개 밖에 없기 때문에 여러 명이 동시에 접근하기 위해서는 Hash List Latch를 가져야 한다.
    ⇒ 성능저하 ⇒ Hash List를 select만 할 경우에는 서로 공유해서 한꺼번에 여러 명이 볼 수 있는 기능을 제공
                        : 사용자가 Select 문장을 수행했다는 뜻이 아님
                           Soft Parse를 성공하는 경우엔 Hash List를 Select만 해도 다음 작업이 가능함.

  - 옵티마이저(Optimizer) : 새로운 실행 계획을 세워줌

                                         (여러 테이블의 조인에서 어떤 테이블을 먼저 읽어야하는지···)

  - 옵티마이저 종류 : 실행 계획을 세우는 방식의 차이
    ┌ Rule Based Optimizer(RBO) : 미리 세워져 있는 규칙을 사용해서 실행계획을 세움 (예측가능)
    └ Cost Based Optimizer(CBO) : 규칙을 참조한 현실적인 상황이 실행 계획을 세우는 주요 요소가 됨
 
  - Hard Parse : 옵티마이저가 새로운 실행 계획을 세우는 단계

 

 

 (2) Bind(값 치환)

      : 실행 계획을 1개만 생성 한 후 바인드 변수 값을 바꾸어 여러 번 실행하는 것
    ┌ 장점 : Soft parse를 많이 하기 됨
    └ 단점 : 일반적으로 테이블에 입력되어 있는 데이터들이 한쪽으로 편중되어(Skewed)있게 되면

                    index 가 정상적으로 작동을 못함

 

 

 (3) Execute(실행)

     : 데이터파일에서 데이터가 들어 있는 블록을 찾아 Database Buffer Cache로 복사해오는 과정
 
  * Database Buffer Cache는 데이터 파일의 블록 주소를 Hash 값으로 변경시켜서

    리스트(Hash List)로 만든 후 그 리스트와 데이터 파일 블록을 관리하고 있다.

 

 

 ▣ Database Buffer Cache에서 서버 프로세스가 찾는 블록이 있는지 없는지 검사하는 원리
    ⇒ 서버 프로세스가 찾는 블록의 주소를 Hash 함수에 넣어 Hash Value를 만들고

          이렇게 만들어진 Hash Value와 Database Buffer Cache Hash List를 비교해서

          동일한 Hash Value가 Database Buffer Cache에 있는지를 검사

  * 데이터를 복사해 오거나 데이터 파일로 저장할 때 가장 최소 단위인 Block 단위로 움직임
  * Block Size는 초기화 파라미터 파일에 DB_BLOCK_SIZE로 정의되어 있음
  * DB가 생성될 때 적용되며 이후에 DB를 다시 생성하기 전까지는 변경할 수 없다. (10g 기준 – 8K)
    크키가 크면 I/O를 줄일 수 있는 장점이 있지만 공간 낭비가 많이 생길 수 있고 Database Buffer Cache에

    Wait가 많이 생겨 성능이 저하될 수 있다.

    ⇒ Excute 단계까지 수행이 되면 데이터가 들어있는 블록이 메모리(DB Buffer Cache)로 올라오게 됨
      (원하는 데이터만 들어있는 것이 아니라 Block단위로 불러오기 때문에 다른 데이터들도 들어 있다)

 

 

 (4) Fetch(인출)

      : 사용자가 원하는 데이터만 골라내는 과정

   * 만약 사용자가 정렬(Soft) 등의 추가 작업을 요구했을 경우 Fetch 과정에서 Sort를 완료해서 데이터를 보내줌
   * 정렬이 발생하는 장소는 PGA이며 PGA는 서버 프로세스 별로 각각 독립적으로 할당되어 사용됨

 

 

 

2. Update 문장의 실행 원리

 

  * 모든 DML(Insert, Update, Delete) 문장의 수행 원리는 동일함
  * SELECT 문의 수행단계에서 Fetch 과정만 없고 나머지는 동일(Execute 과정이 SELECT 문보다 훨씬 복잡)

 

 

 (1) Parse , (2) Bind 과정은 SELECT와 동일

 

 

 (3) Execute(실행)

 

     ① DataBase Buffer Cache를 살펴보고 없다면 데이터파일로 가서 해당 블록을 Database Buffer Cache로 복사함
     ② 서버 프로세스는 변경되는 데이터의 변경내역을 Redo Log Buffer에 기록
        - Redo Log Buffer : 데이터가 변경 될 때 만약의 장애를 복구하기 위해 변경내역을 기록해두는 공간
     ③ Undo Segment에 이전(원본) 이미지를 기록한 후 Database Buffer Cache의 내용을 변경
         ⇒ 데이터가 변경되는 것을 통틀어서 오라클에서는 Transaction(트랜잭션) 이라고 부름

 

 

 

서진수,『오라클 관리 실무』, 생능출판사, 2013, pp.60-77