SQL문 실행원리

1. SQL 동작원리

 1) SQL 문장이 수행되는 과정

SQL 실행 과정

   1. user process는 자신이 가져온 SQL문을 server process에 전달하기 위해 tnsnames.ora를 참고하여 서버를 찾아가 

      서버에서 작동하고 있는 listener에 접속 요청  (첫번째 연결에서만 listener 필요)

   2. listener가 서버에 요청하면 user process와 server process가 연결

   3. user process에서 server process에 SQL문 전달

   4. server process가 oracle server에 접속

 

 

 

 2) select문 실행 원리

   2-1) 구문분석

• 1. user process로 부터 SQL문을 받은 server process는 가장 먼저 SQL Parser를 통해 SQL문장에 쓰인 키워드나 컬럼명 등을 분석하여 Parse tree를 생성하는데, 이 과정에서 PGA에서 문법검사를 하게 되고, 이상이 없으면 의미검사
• 의미검사란 문법에 오류가 없으면 sql문장을 ASCII값(숫자값)으로 변경하고 해당 값을 HASH함수를 통해 특정 Hash 값으로 변경  (Hash 함수 : 입력된 값이 같을 경우 늘 출력결과가 같고 입력값이 다를경우 출력값이 항상 다르다는 것을 보장하는 함수)
• 2. PGA에서 문법,의미,권한을 검사하고 얻은 Hash 값을  SGA안에 Shared pool의 library cache에 있는 Hash value와 비교해 동일값이 있는지 확인 후, 실행계획이 있는지 검사하기 위해 Hash bucket을 읽어 검색 (=> Soft Parse  : 실행계획 재활용)  혹은 커서 공유 (공유커서 : 이미 한번 수행되었던 SQL문의 실행계획과 관련 정보를 보관하고 있다가 재활용해 Hard Parse의 부담을 줄여 수행속도를 빠르게 함/  커서 : 실제 내요이 들어가 있는 공간)
• * library cache : 한번이라도 수행되었던 SQL문장이나 PL/SQL 문장과 해당 문장의 실행계획들이 해당 용량만큼 전부 공유되어있는 공간
• * Hash list :  일일이 실제 커서를 방문해 해당 내용이 있는지 없는지 찾지 못하므로, 어떤 커서에 어떤 데이터가 들어가 있는지 적혀있는 리스트로, chain 구조로 데이터가 연결되어 있음
• 3. 공유되어 있는 실행계획이 있으면 execute 단계로 진행, 없으면(커서 공유를 실패하면) 옵티마이저를 찾아가 실행계획 생성 요청
• 4. 옵티마이저가 SGA안에 Shared pool의 Data Dictionary등을 참고해 실행계획을 생성해, server process에 넘겨줌(=> Hard Parse)

 

   2-2) 치환

• 특정 사용자에게 값을 입력받아 바꿔서 대입하는 것
• 바인드 변수를 사용하면 soft parse를 많이 하므로 좋아지는 부분도 있지만 데이터들이 한 쪽으로 편중되면 index가 정상적으로 작동하지 못한다는 단점도 있음. 즉, 분포도가 균일해야 사용할 수 있음

 

   2-3) 실행

• 필요한 자료를 하드디스크의 데이터파일에서 데이터가 들어있는 블록을 찾아 database buffer cache(메모리)로 카피해오는 과정
• 모든 데이터의 조회와 변경 작업은 database buffer cache에서 이뤄지므로 오라클은 항상 작업을 위해 해당 블록을 데이터파일에서 찾아 database buffer cache로 복사해 놓아야 함
• server process는 해당 블록을 찾기 위해 우선 database buffer cache를 확인
• ※ server process가 찾는 블록이 database buffer cache에 있는지 없는지 검사하는 원리
• → server process가 찾는 블록의 주소를 Hash함수에 넣어 Hash value를 만들고 hash value와 database buffer cache hash list를 비교해, 동일한 값이 있는지 검사
• * databae buffer cache hash list : server process가 데이터 파일의 블록 주소를 hash 값으로 변경시켜 만든 리스트
• → database buffer cache에 원하는 블록이 있으면 다음 단계인 fetch진행, 없으면 server process가 하드 디스크로가서 블록을 찾아 database buffer cache로 복사
• ※ block 단위의 I/O
• → 데이터파일에서 database buffer cache로 데이터 복사하거나, 반대로 database buffer cache에서 데이터 파일로 저장할 때 가장 최소 단위인 block 단위로 움직임
• → block크기가 크면 I/O를 줄일 수 있으나, 공간 낭비가 많이 생길 수 있고, database buffer cache에서 wait가 많이 생겨 성능 저하
• → 인덱스 생성으로 디스크에 어떤 파일이 어디에 있는지 알기 때문에 성능, 속도 좋아짐(인덱스 없으면 풀스캔)

 

 

   2-4) 인출

• buffer cache에 블록이 복사되어 왔을 때, 그 블록에서 사용자가 원하는 데이터만 골라내는 과정
• 정렬등의 추가작업이 있는 경우 fetch과정에서 sort를 완료해 데이터를 보내주게 되는데 정렬하는 장소가 PGA라는 공간이며 이 공간은 server process별로 각각 독립적으로 할당되어 사용

 

 

 3) DML 문장 실행원리(insert, update, delete, merge)

   select문과 같이 parse 과정까지는 동일 → execute (fetch과정은 없음)

   

   3-1) execute 단계 

• redo log buffer에 변경 내역을 적음
• undo segment에 변경전 데이터 기록
• database buffer cache의 실제 데이터 변경
• * redolog buffer :  데이터가 변경될 때 만약의 장애를 복구하기 위해 변경내역을 기록해두는 공간
• redo log buffer에 기록하기 전 undo segment를 확보하고, commit되면 redo log buffer에 먼저 내려쓴 후 db에 씀
• redo log buffer에는 DB내에서 데이터 변경이 생기는 모든 것들을 기록(select는 조회하는 문장이기 때문에 기록x)

  ※  데이터 변경에 대한 commit작업이 이루어지지 않았을 때

    -> DML 작업 시, db buffer cache에서 데이터가 변경되고 거기에는 lock이 설정되어 해당 작업에 대한 commit이나

        rollback이 수행되기 전까지는 아무도 해당 블록을 볼 수 없음

    -> 다른 사용자가 같은 조건의 데이터를 조회하는 쿼리를 날렸을 때 undo segment에 있는 데이터를 db buffer 

        cache로 복사해 조회함