ch2. SQL문장의 실행원리

 ch2. SQL문장의 실행원리

 [녹음3]

 

* user process란?  : SQL을 작성하는 프로그램. (sqlplus, SQL Developer, toad, orange, Golden, editplus 등)  ex) >sqlplus scott/tiger --sqlplus라는 프로그램을 이용해 scott계정의 암호 tiger로 들어감.     - tnsnames.ora : user process는 서버가 어디에 있는지 위치정보(주소록)을 알아야 하는데, 이러한 IP, port번호, SID등을 적어 놓는 파일이 tnsnames.ora이다. (oracle server에 접속하기 위해, oracle client를 깔고, 이 프로그램으로 접속해야(netca) tnsnames.ora 파일이 생성된다.)    * Server process란?  : 데이터베이스 내에서 실제로 일을 하는 프로세스.    * Background process란?  : oracle server가 시작되면 자동으로 시작되어 운영과 유지를 담당하는 프로세스.

 

 * 사용자가 쿼리 실행후 server process가 oracle server에 접속(session)할 때 까지의 과정.

 

 

1. 사용자가 user process를 사용하여 SQL을 실행.  2. userprocess가 tnsnames.ora를 보고 리스너에 접속.(connection)  (최초 접속일 경우(창을 처음 열었을 때)만 리스너를 거친다.) (서버에 리스너가 돌고 있어야 한다.)  3. 리스너가 서버에 요청.  4. server process(담당자)가 나옴.  5. user process가 해당 SQL을 server process에게 전달해 요청.  6. server process가 oracle server에 접속한다. (session) ** 두번째 접속(창이 켜진 상태에서 다음 쿼리 칠 때)부터는 리스너에게 접속하지 않고, 바로 서버프로세스에게 접속하여 요청한다.

 

* putty로하면? (sqlplus, toad, orange..등등의 userporcess와 다르다.)

pc에서 putty를 쓰는 것은 위 과정을 거치지 않고, 서버에 다이렉트로 붙어버린다.(리스너와 관련 없다.)

 

ex) 같은 DB를 쓰는 장비 A,B,C,D,E 중에 D,E는 접속이 되고, A,B,C가 접속이 안되는 경우는... 리스너가 고장난 경우이다. 이유는 D,E는 리스너가 고장나기 전에 이미 최초접속을 했던 경우이므로, 다음에 접속을 할 때에는 리스너가 고장나도 문제가 없었지만, A, B, C는 리스너가 고장난 후에 최초접속을 한 것이기 때문에, 리스너를 거쳐야 하는 과정을 못 거쳐서 접속이 불가했던 것이다.

 

 

[녹음4]

1. Select 문장의 실행원리★★★완전완전중요★★★

 

 

(1)구문분석(Parse) : 실행 계획을 만드는(찾는) 단계. (c언어에서의 컴파일과 같은 검사!)

  ① 문법검사 (Syntax Check)

  ② 의미검사 (Semantic Check)

  ③ 권한검사 : 어떤 사용자가 해당 오브젝트에 접근할 수 있는 권한이 있는지 없는지 확인.

  ④ 실행계획★ : 옵티마이져가 실행계획을 세운다.★ (이 실행계획을 처음으로 세우는 것을 Hard Parse라고 부른다.)(쿼리의 성능을 결정.)

                     → 한번 하드 파싱을 하면, 그 결과를 기억을 해둔다. (Library cache)

 

구문분석 과정

① user procee로부터 SQL문장을 받은 Server process는 가장 먼저 SQL Parser를 통해서 각 SQL문장에 쓰인 키워드나 칼럼명 등을 분석해서 Parse tree를 생성하는데, 이 parse tree를 생성하는 과정에서 PGA에서 문법검사를 하게 되고, 이상 없으면 의미검사를 한다.  (oracle key word의 스펠링이 틀릴 경우 parse tree 생성단계에서 오류가 나고, 없는 테이블을 조회하면 의미검사단계에서 에러가 발생한다.)  **Dictionary cache(Row cache) : 해당 문법이 맞는지 틀린지 여부나 해당 테이블이 있는지 없는지 여부를 알기 위해 데이터 딕셔너리를 사용하는데, 자주 사용하는 데이터 딕셔너리를 캐싱해 두어 성능을 높이는 역할을 한다.  ② PGA에서 문법,의미,권한 검사를 하고나서, SGA안에 Shared pool의 library cache를 검사하는데, 실행계획이 있는지 검사하기 위해 Hash bucket을 읽어 검색한다. ( Soft parse=커서공유 : 한번 세워두었던 계획을 재활용 하는 것.)  ** library cache : 한번이라도 수행되었던 SQL문장이나 PL/SQL문장과 해당 문장의 실행계획들이 해당 용량만큼 전부 공유되어있는 공간  # soft parse 에 대해 더 자세히..  - 문법,의미,권한 검사를 하고나서, 오류가 없다면 이 SQL문장을 ASCII값(숫자값)으로 변경한 후 해당 숫자 값을 HASH함수를 통해 특정 HASH값으로 변경한다. 그리고 이 HASH VALUE를 shared pool의 library cache에 있는 hash value들과 비교해서 동일한 값이 있는지 확인한다.  - 주의할 점 : 만약 scott사용자와 hr사용자가 동일한 문장(대소문자, 공백, 주석까지 모두 동일) SQL을 수행했다 하더라도 사용자가 다르게 되면 이 문장들은 서로 다른 문장이 된다.  - soft parse가 hard parse보다 성능이 빠르긴 하지만, 많은 사용자가 데이터를 찾는데 latch는 1개밖에 없어서 동시에 작업할 수 있는 사용자가 1명밖에 없다면 속도가 느려질 수 있으므로 soft parse가 무조건 빠르진 않다.  ** HASH 함수란 : 입력된 값이 같을 경우 늘 출력 결과가 같고 입력 값이 다를 경우 출력 값은 항상 다르다는 것을 보장해주는 함수. 성능이 아주 빠르고 정확하기 때문에 대용량의 데이터를 비교해야만 하는 join에서 인덱스를 사용하지 않고 hash 함수를 이용해 비교하는 hash join 방법이 아주 많이 사용된다.  ** 커서(cursor) : 실제 내용이 들어가있는 공간. (공유커서, 세션커서, 어플리케이션커서)  - 공유 커서 : Library cache안에 있는 커서로, 이미 한번 수행되었던 SQL문장의 실행계획과 관련 정보를 보관하고 있다가 재활용함으로써 Hard parse의 부담을 줄여 SQL문장의 수행속도를 빠르게 하는 것. (parent cursor : SQL문장 자체에 대한 값 / child cursor : 사용자 정보나 optimizer mode정보)  → 사용자가 다르거나 옵티마이저 모드가 다르면 커서공유를 할 수 없다. parent cursor와 child cursor 모두가 완전 동일해야만 이뤄진다.  ** Hash list : 일일이 실제 커서를 방문해서 해당 내용이 있는지 없는지 찾지 못하므로, 어떤 커서에 어떤 데이터가 들어있는지 적힌 리스트로, chain구조로 다음 데이터가 연결되어 있다.  ex) 즉 메모리가 heap구조(순서가 보장되지 않고 빈자리 있으면 마구 저장하는 구조)이므로, 만약 hash value가 11이 나왔다면, 첫번째 11의 값을 가진 커서 값에 두번째 커서의 위치가 저장되어 있고, 두번째 커서에 세번째 커서값이 저장되어 있는 형태.  → hash list가 1개 밖에 없는데 10명의 사용자가 동시에 sql문장을 수행하게 되면 문제가 생기므로 오라클에서는 Library cache를 탐색하기 위해 반드시 librarh cache latch를 가지게끔 정하고 있는데, 이 때도 나머지 사용자들은 대기를 하면서 성능저하되므로, Library cache의 hash list를 select만 할 경우에는 서로 공유해서 한꺼번에 여러 명이 볼 수 있는 기능을 제공한다.  ③ 공유되어 있는 실행계획이 있으면 바로 다음 단계인 Execution단계로 진행하고, 없으면(커서 공유를 실패하면) 옵티마이져(내비게이션 역할)를 찾아가서 실행계획을 만들어 달라고 요청을 한다.  ④ 옵티마이져가 SGA안에 Shared pool의 Data Dictionary등을 참고해서 실행계획을 생성해, server process에게 넘겨준다. (Hard Parse)  - soft parse를 실패할 경우에만 hard parse한다.  - 옵티마이저에게 정확한 정보를 주는 것이 중요하므로 최신정보를 유지해야 하는데, 옵티마이저가 참조하는 딕셔너리 관리는 사람이 해줘야 한다.  - 옵티마이저가 처음에 여러 개의 실행계획을 만드는데, 그 후 비용을 계산해 효율적인 실행계획 1가지를 선택하는 것이다.  옵티마이져   RBO(Rule Based Optimizer)  규칙기반 옵티마이저  서버 프로세스가 실행계획을 세워 달라고 요청이 들어오면 미리 정해져 있는 상식적인 규칙을 사용하여 실행 계획을 세운다. (상황,예외 등을 따지지 않아 융통성이 없음.) (10g R2버전 부터 완전히 지원되지 않는다.)   CBO(Cost Based Optimizer)  비용기반 옵티마이저  서버 프로세스가 실행계획을 세워 달라고 요청이 들어오면 규칙을 참조한 현실적인 상황을 고려하여 Data dictionary를 참고해서 실행 계획을 세운다. (대부분 사용) (11g부터)  ⑤ server process는 이 실행 계획을 S.P(Shared pool)의 Library cache에 등록한 후 PGA로 복사해서 가져온 후 그걸 보고 실행단계로 넘어간다.

 

▷ soft parse : 문법검사 → 의미검사 → 권한검사 → Shared Pool 에서 Library cache 검사.

▷ Hard parse : Library cache에 실행계획이 없으면(soft parse 실패하면) 옵티마이져 찾아가서 실행계획 만들어 달라고 요청 → 옵티마이저는 data dictionary등을 참조해서 실행계획을 생성

 

[녹음5]

(2)치환(bind)★★ : 특정 사용자에게 값을 입력받아서 바꿔서 대입하는 것이 바인드이다.

- 원하는 학생의 학번을 입력받아 점수를 조회하려고 하는데, 만약 1000명 모두 조회한다면 1000명이 학번만 다르고 실행계획은 같을 것이다. 즉 같은 테이블의 같은 칼럼을 조회하는 SQL이므로 파싱을 1000번하고 실행계획을 1000개 만드는 것보다 1번만 파싱해서 실행계획을 1개만 생성한 후 학번과 이름만 바꿔서 1000번 실행하는 것이 훨씬 부담도 적고 쿼리 수행속도도 빠를 것이다. 이 때 학번을 바인드 변수값이라고 부른다.

- 바인드 변수를 사용하면 soft parse를 많이 하므로 좋아지는 부분도 있지만 데이터들이 한 쪽으로 편중되어(skewed)있게 되면 index가 정상적으로 작동을 못한다는 단점도 있다. 즉 분포도가 균일하지 못하면 Bind 변수를 사용할 수 없다.

ex) 웹사이트에 로그인할 때 사용자마다 아이디,password만 바꿔치기 하는 것과 사용자가 책을 사고자 책을 검색하는 것 등이 바인드이다. 즉, '사용자에게 입력받거나 검색하는것'과 같은 것들을 생각해두면된다.

 

(3)실행(execute) : 필요한 자료를 하드디스크의 데이터파일에서 데이터가 들어있는 블록을 찾아 database buffer cache(메모리)로 카피해오는 과정.

- 모든 데이터의 조회와 변경 작업은 SGA내의 database buffer cache에서 이뤄지므로 오라클은 항상 작업을 위해 해당 블록을 데이터파일에서 찾아서 database buffer cache로 복사해 놓아야 한다. 그래서 서버 프로세스는 해당 블록을 찾기 위해 우선 datbase buffer cache를 확인한다.

서버프로세스가 찾는 블록이 database buffer cache에 있는지 없는지 검사하는 원리

① 서버프로세스가 찾는 블록의 주소를 Hash 함수에 넣어 Hash value를 만든다.  ② hash value와 database buffer cache hash list를 비교해서, 동일한 hash value와 database buffer cache에 있는지 검사한다.  ** database buffer cache Hash list : 서버프로세스가 데이터 파일의 블록 주소를 hash 값으로 변경시켜서 만든 리스트.  ③ database buffer cache에 원하는 블록이 있으면 즉시 다음 단계인 fetch단계로 진행하고, 못찾으면 서버 프로세스가 하드 디스크로 가서 해당 블록을 찾아 database buffer cache로 복사해온다.

 

Block 단위의 I/O

: 오라클은 데이터 파일에서 database buffer cache로 데이터를 복사해오거나, 또는 반대로 database buffer cache에서 데이터파일로 저장할 때 가장 최소 단위인 block단위로 움직인다.  - 초기화 파라미터 파일에 DB_BLOCK_SIZE로 크기가 결정되며, DB가 생성될 때 적용되어 그 이후에 DB를 다시 생성하기 전에는 변경할 수 없다.  (oracle 9i버전 : 4K / 10g버전 : 8K)  - block의 크기가 크면 I/o를 줄일 수 있으나, 공간 낭비가 많이 생길 수 있고, databse buffer cache에서 wait가 많이 생겨 성능이 저하 될 수 있다.  - 하나의 블록에는 데이터가 무지 많은데, 한 건만 복사해오는 것이 아니라, 내가 원하는 데이터가 들어있는 블록단위로 다 복사해온다. 이 블록이 어디 있는지 모를 때 인덱스를 이용해서 찾는다.  만약에 execute하는데 시간이 너무 오래(20초가량) 걸린다면 인덱스에 문제가 생긴 것이다.

* execute과정에서 누군가가 수정중인데, 다른사람이 그걸 지우게되면 큰 문제가 생긴다. 그걸 방지하기 위해 lock을 걸어둔다.

 

(4)인출(fetch) : Buffer cache에 블록이 복사되어 왔을 때, 그 블록에서 사용자가 원하는 데이터만을 골라내는 과정.

- 사용자가 정렬(sort)등의 추가작업을 요구했을 경우 Fetch과정에서 sort를 완료해서 데이터를 보내주게 되는데, 정렬을 하는 장소가 PGA(Program global area)라는 공간이며 이 공간은 서버프로세스 별로 각각 독립적으로 할당이 되어 사용된다.

 

 

 

2. Update 문장의 실행원리. (모든 DML문장(insert, update, delete, merge)는 동일한 원리로 수행됨.)

** 모든 DML문장(insert, update, delete, merge 문장은 동일한 원리로 수행된다.

- select문의 수행단계에서 fetch과정만 없으나, execute 과정이 select문 수행단계보다 아주 복잡하다

** Transaction(TX) : 데이터가 변경되는 과정.

 

홍길동을 일지매로 바꾸라는 update문장 수행과정

(select문 수행단계와 동일)

1. user process가 server process에게 쿼리를 전송(connection)하면 server process가 oracle server에 접근(session)한다.  2. server process는 제일 먼저 Parse단계로 문법검사, 의미검사, 권한검사를 한다.  3. shared pool이 library cache를 살펴서 SQL과 실행계획이 있는지 본다. (soft parse)  4. soft parse를 실패하면 옵티마이져에게 실행계획을 생성해달라고 요청하고, 옵티마이져는 생성한다.(Hard parse)  5. 생성된 실행계획을 받은 서버프로세스는 library cache에 등록한다.  6. parse 과정이 끝난 후 database buffer cache를 살펴서 '홍길동' 이 있는지 보게되고, 없으면 데이터 파일로 가서 해당 블록을 database buffer cache로 복사해온다. (execute)

execute 단계 (select문 수행과정과 다른 점) ★★★

7. redo log buffer에 '홍길동->일지매'로 바꾸라는 변경내역(Change vector)을 적는다.★  ** redolog buffer란 : 데이터가 변경될 때 만약의 장애를 복구하기 위해 변경내역을 기록해두는 공간.  8. undo segment에 변경전 데이터를 기록한다. (홍길동) ★  9. Database buffer cache의 실제 데이터를 변경한다.★

 

 

 

---

(이하 나중에 다시)

Instnace Recovery

change vector 안에는 check point(DB cache에 있는 데이터를 dbw0가 file에 내려쓰는 것) 하지않고, commit한상태에서 db를꺼버리면, redolog file에는 저장되있지만 아직 원본데이터(file)는 안바뀌었다.  그러나 db를 다시 키면, redolog를 읽어보니 바뀌었다는 변경내역이 저장되어있으므로, SMON이 redologfile을 읽고 원본데이터를 다시 바꿔준다.  ( redolog buffer안에 적힌 내용은 변경후 데이터를 저장하지 않아도 살려낼 수 있다.)

반대로 사용자가 100만건 데이터를 update쳤는데, 잘못쳐서 rollback을 쳤다. 그런데 rollback되는 도중에 db가 꺼져버렸다.  이 때 SMON이 redologfile을보니 commit이 된 흔적이 없으므로, roll back이 되지 않은 상태로 남게 된다.(?????????맞나???????)  (change vector안에는 '홍길동->일지매'로 바꾸겟다는 문장과 '일지매를 ->다시 홍길동'으로 바꾸겠다는 (rollback할수있는) 문장도 셋트로 들어가있다.)

 

------------------------------ 이하 다시정리 ------------------------------------------------

- commit을 하지 않아도 데이터는 바뀐다.

- rollback을하면 일지매를 지우고 다시 홍길동을 덮어씌운다.

- 만약 commit을 치면 Redolog buffer에 있는걸 redolog file에 내려쓴다. (lgwr)

- redolog file에 commit되지 않은 것도 저장된다.

 (ex. 100만건 update치고있으면 10만건 쳤을때 이미 commit되지않은것도 redologfile에 들어가있어.)
- update sql문장이 redo buffer에 저장된다.

- insert만 실행할 당시에는 이문장이 commit됬는지 안됐는지 알 수 없어. 그래서 다 수행해.