Database Oracle Administrator_SQL 문장의 실행 원리

Step 2. SQL 문장의 실행 원리

 

드디에 Step 2로 넘어 왔네요..ㅎ Step 1에서의 Oracle Server 구조는 정말 중요하고 기초가 되는 부분입니다.

지금도 기초지만..ㅎ 여기서 다루는 것들도 매우 중요하고 튜닝이나 성능에 관련이 된 부분이고 궁금해야할 부분입니다.

저같은 경우는 SQL이 왜 돌아가고 어떻게 돌아가는지 또 왜 이렇게 돌아가지? 뭐가 관련이 있지? 하면서 공부하고 있습니다.

쓸데없는 호기심은 아닌것같군요..ㅎ SQL 실행원리는 뒤쪽의 개념도 포함이 된 부분이라 좀 이해가 힘든 부분이 많습니다.

지금 글을 쓰고 있는 저도 어렵어렵..ㅎ 그래도 잘 설명하고 이해하도록 해야죠 ㅎ

 

SQL 문장 실행 원리는 크게 보면 아래 그림과 같습니다.

 

 

 

좀 길죠..? 가로로 놓을까 했는데 귀챠니즘이 퍼져서 안그리고 그냥 메뉴얼에 있는거..ㅎ

이것은 보시다싶이 두가지의 경우가 있습니다. Soft Parse와 Hard Parse. 앞에서도 몇번 언급을 했었습니다.

Soft Parse는 SGA의 Shared Pool의 Library Cache에 SQL(혹은 PL/SQL)의 문장과 결과가 있는 경우 였죠?

SQL 문장실행 -> Syntax Check -> Sematic Check -> Shared Pool Check -> Execution 이 일어납니다.

그럼 언제 Library Cache 내용을 들여다 볼까요? Shared Pool Check에서 들여다 본다는 겁니다. 위 그림도 우회해서 가죠?ㅎ

 

그럼 Hard Parse는? 모든 과정을 차례차례 모두 다 거치고 가는 아이..ㅠ 그래서 성능적으로 오래걸리게 되는 겁니다..ㅎ 무겁무겁..

 

다시한번 보면 Syntax Check는 문법검사라고 해서 키워드(SELECT, FROM, WHERE 등)를 검사하는 겁니다. 그래서 띄어 쓰면 오류!

Semantic Check는 테이블, 컬럼 등의 사용자마다 다른 부분들을 검사합니다. 그래서 없으면 오류가 뜨는 거죠!

Shared Pool Check는 위에서 말했듯이 해당 SQL(혹은 PL/SQL)문장과 결과가 Library Cache 내에 있는지를 조회합니다.

있으면 Soft Parse로 Execution으로 다이렉트! 없다면 Hard Parse로 Optimization으로 가게 됩니다.

Optimization에서 옵티마이저가 네비게이션 같은 프로그램이라고 서진수쌤이 많이 설명을 하셨습니다. 그 이유는 뒤에서 자세히 ㅎ. 옵티마이션이라는 과정에서는 옵티마이저가 Data Dictionary 등을 참조해서 실행계획을 생성하는 역할을 합니다. 시간이 많이 걸려서 Hard~

Optimization 과정에서 여러개의 실행 계획을 세우고 Row Source Generation 과정에서 그 중 가장 좋은 계획을 선택하게끔 합니다.

그리고 나서 Execution에서 실행을 하는 것입니다.

차근차근 스스로 정리하면 어렵지 않답니다. 아직 제대로 어려운 부분이 나오지 않았으니까요 하하하..

 

이것도 한번 저만의 식으로 예를 들어서 한번 설명을 해보겠습니다.

지금 여름이 지났지만..ㅎ 한여름에 MT를 친구들이서 가려고 합니다~ 완전 신나죠??ㅎ

하지만 MT를 가려면 계획을 세워야 할 것들이 한두가지가 아닙니다..ㅠㅠ 그래서 체계적으로 친구들과 계획을 세우기로 했습니다.

엠티를 가기 위해서 꼭 필요한 것은(ex) Syntax)? 장소(팬션), 돈 입니다. 키워드죠. 이것이 없으면 난감합니다..ㅎ

그리고 엠티를 갈때마다 바뀌는 것은(ex) Semantic)? 사람 인원수, 먹을 것들이겠죠?ㅎ

그런데 작년 엠티와 똑같은 인원, 똑같은 장소, 똑같은 먹을 것들, 똑같은 시세로 가려고 합니다. 그럼 계획을 짤 필요가 있을까요?

그냥 그대로 영수증 보고 사고, 장소에 연락하고 떠나면 되는 겁니다!(Execution) 이게 Soft Parse!

하지만 사람인원수가 다르고 장소도 예전꺼가 마음에 안들고 먹을 것도 다르게 사려고합니다. 그럼? 시세부터 장소, 조미료 등등

배분을 해야하고 차량 픽업과 계획을 하나하나 인터넷과 몸이 고생하면서 알아봐야하죠.. 이게 Optimization이라고 합시다.

이때 한가지의 경우만 나오는 것이 아니라 교통수단만 하더라도 렌트를 할지 기차로 갈지 버스로 갈지, 먹을 것도 고기를 더살지 술을 더살지 계획은 여러가지가 나오겠죠. 그 중 하나만 딱 선택하는 것이 Row Source Generation! 그리고 Execution! MT GoGo~ 출발이죠.

저만의 방식으로 설명을 했는데 이해가 잘 되시는지...ㅎ 이렇게 엠티로 예를 들어도 어느정도 맞는 것 같습니다. 어거지인 것도 있지만..ㅎ

이런식으로 이해하면 좀 더 재밌지 않을까 합니다 ㅎ MT의 안좋은 추억이 있다면 씁쓸하겠지만요..ㅎ

 

전체적인 SQL 실행 원리는 이렇게 마무리하고 본격적인 Select 문장 실행 원리를 시작해보겠습니다.

 

 

 

이렇게 Parse -> Bind -> Execute -> Fetch 이렇게 총 네개의 단계로 이루어져 있습니다. Update같은 DML들도 비슷합니다.

 

맨 먼저 Parse!(구문분석)입니다.

제가 보기엔 Parse 부분이 제일 복잡하고 어려운 것 같습니다..ㅎ 정리를 정말 잘해야 이해가 되는 부분...ㅜ

처음부터 보면 User Process가 Server Process에게 SQL 문장을 전달해 준 다음 Server Process는 해당 SQL문을 처리하는데 SQL Parser를 통해서 SQL 문장에 쓰인 키워드나 컬럼명 등을 분석해서 Parse Tree를 생성합니다. 처음 듣는 용어..ㅎ

 

Parse Tree를 만드는 과정은 Syntax Check -> Semantic Check 를 하게 됩니다. 이 말은 키워드나 컬럼 등의 요소가 불확실하거나 없을 경우에는 Parse Tree가 생성되지 않는다는 겁니다. 이 과정에서 사용하는 것이 Data Dictionary인데요. 자주 들락날락 하면 또 성능에 관련이 있겠죠??ㅎ 그래서 예전에 언급한 Data Dictionary Cache에 자주쓰는 것들을 Cache 해서 성능을 높이는데 이용합니다.

용어들이 익숙해지시나요?ㅎ 이렇게 구조에 대해서 자주 언급하는 경우가 많아서 Step 1을 잘 알아야 앞으로도 잘한다는 것!ㅎ

 

Parse Tree과정에서 Semantic Check 까지 했습니다. 그럼 다음에는 Shared Pool Check죠? Shared Pool Check는 좀 다른 방법을 써서 합니다. 바로 Hash라는 개념이 나옵니다. Semantic Check까지 끝난 SQL문은 ASCII 값(숫자값)으로 변환을 한 후 해당 숫자 값을 Hash함수를 통해 Hash값으로 변경하게 됩니다. 그리고 이 Hash값을 Shared Pool의 Library Cache의 Hash 값들과 비교를 합니다.

이 과정을 커서공유 또는 Soft Parse라고 합니다. 주의할 것은 문장이 똑같더라도 사용자별로도 식별을 하기 때문에 사용자가 다를경우 다른 문장으로 인식됩니다. 글로만 쭈루룩 설명하니까 어렵죠..?ㅎ 풀어서 써보겠습니다.

 

애초에 Hash를 왜 쓸까 먼저 생각해보도록 하겠습니다. HASH가 무엇인지 아시나요? 제가 처음 접한 것은 보안쪽이었습니다.

 

 

 

그림에서는 알고리즘이라고 했지만 위에서 말한 함수라고 생각합시다. HASH함수를 거쳐서 ORACLE에서 문자열이 나오는지 숫자열이 나오는 지는 모르겠지만 이러한 개념입니다. 분명한건 동일한 함수를 썼을 경우 원본데이터로 나오는 해시값은 하나라는 겁니다.

그러면 어떻게 될까요? 수많은 값들을 숫자열 혹은 문자열로 된 HASH값을 가지고 비교해 볼 수 있지 않을까요?

그렇기 때문에 HASH를 쓰는 겁니다. 비교를 수월하게 하기 위해서.

다시 말하면 A라는 원본데이터는 HASH함수를 통하면 B라는 HASH값이 나오고 C라는 원본데이터는 B라는 HASH값이 절~~대 나올 수 없다는 것입니다. 그래서 비교가 가능 한 것이지요.

 

다시 돌아와서 그럼 SQL에서 얻은 Hash Value를 가지고 어떻게 비교를 하는지를 보겠습니다. 저도 헷갈리는 거라 ProDBA에 물어물어서 이해를 했습니다. 괜히 어렵게 생각한 것 같기도 하고..ㅎ

Shared Pool의 Library Cache에서 동일 SQL에 대한 Hash Value를 얻기 위해선 맨처음 Hash Bucket을 조회를 해야합니다.

사전이나 옥편에서 보면 첫 알파벳 혹은 자음 또는 한자 횟수를 가지고 카테고리를 잡고 단어를 찾는 것과 같습니다.

Hash Value 별로 비슷한것들끼리 모아서 Bucket으로 묶은 다음 그걸 먼저 조회하는거죠.

예를 들어서 SQL을 Hash 값으로 바꾼 abc123이 있으면 Bucket은 a로 시작하는 것들을 묶어서 놓고 Bucket을 조회 할때 나도 a로 시작하니까 a Bucket에서 조회를 해야지~ 라고해서 a Bucket 내의 정확한 Hash Value인 abc123을 찾는 거죠. 지금 이건 그냥 예에요.. 이렇게 실제로 묶이는 것이 아니라 동작의 예 ㅎ 이렇게 할 수 있다는 거죠! 비슷한것끼리 묶은 묶음부터 찾고 상세히 찾아 들어가는것!

 

오라클에서는 실제 내용이 들어가 있는. 즉, Value 값이 들어가 있는 공간을 커서(Cursor)라고 하고 커서는 세개로 나누어 집니다.

앞에서 말한 공유 커서를 비롯해서 세션 커서, 어플리케이션 커서 이렇게 세가지의 커서가 있습니다.

공유커서는 앞에서 말했듯이 Library Cache에 SQL 구문들에 대한 정보를 저장해서 공유해 쓰는 것입니다.

세션 커서와 어플리케이션 커서는 PGA 개념과 같이 나오는데요. 성능 향상을 위한 튜닝과도 연관이 있는 부분입니다.

세션커서는 Library Cache에 저장되어있는 커서를 이용하려 하면 PGA 영역의 Private SQL Area에 Library Cache 상에 존재하는 공유 커서의 위치와 커서를 실행하기 위한 정보도 기록합니다. 이렇게 PGA 영역에 저장하고 있는 커서에 대한 정보 또한 커서이고 공유 커서 정보를 통해 PGA 영역에 세션커서를 생성하는 것을 커서를 오픈한다 라고도 합니다.

어플리케이션 커서는 PGA에 있는 커서를 사용하기 위해 클라이언트 어플리케이션에도 리소스를 할당하는 커서입니다.

 

이렇게 세개의 커서를 보면 물고 물리는 것을 알 수 있습니다. 공유커서를 사용하기 위해서 세션커서를 써야하고 세션커서를 쓰기 위해 어플리케이션 커서를 써야한다. 그래서 총 세개의 커서를 모두 사용해야 한다고 보면 될 것 같습니다.

 

Parse 과정에서 가장 많이 말하는 것은 계속 언급하듯이 공유 커서입니다. 공유커서는 Hard Parse를 쓰는 부담을 줄여서 실행 속도를 빠르게 하는 것입니다. Library Cache에 이전 기록이 남아있다면 다시한번 Optimization을 하지 않아도 되니까요 ㅎ

 

하지만 여기서도 주의 해야할 것이 있습니다. 이전에 말했던 적이 있는데 사용자가 다르면 SQL 문장이 같고 Library Cache에 저장이 되어 있더라도 공유 커서가 잡히지 않습니다. 왜 그런지 알아보도록 하겠습니다. 사용자가 다르거나 옵티마이저 모드가 다르거나 하면 커서 공유를 할 수가 없는데요. 공유 커서는 부모커서와 자식커서로 나뉘게 됩니다. Parent Cursor에는 SQL 문장 자체 값이 들어 있고 Child Cursor에는 사용자 정보나 Optimizer Mode가 들어있는 것입니다. Parent와 Child 값이 동일해야만 커서 공유를 할 수 있는 것입니다.

 

참많고 복잡한 관계로 얽혀있죠..?ㅎ

 

 

 

커서의 구분과 공유커서의 구분입니다. 역할은 위에 써놨습니다. 구조만 잘 파악하고 있으면 어렵지만은 않은 커서인 것 같습니다.

 

커서는 여기까지 설명하고 다시 Library Cache와 Hash의 관계로 가보겠습니다. Library Cache에서 Hash Bucket을 보고 들어가서 공유커서를 사용한다고 했습니다. 그런데 Hash Bucket만 보고 들어갔는데 커서가 안에 100개나 있다고 합시다. 그럼 순차적으로 첫번째 들어갔는데 아니네~ 나와서 두번째 봤는데 아니네~ 나와서 계속 돌아다닌다면 이것도 성능적으로 문제가 있겠죠.. 그래서 리스트로 관리합니다. 이 리스트에는 어떤 커서에 어떤 데이터가 들어있다~ 라는 것을 알려주는 리스트 입니다. 이 리스트를 Hash List라고 합니다.

이 Hash List의 데이터들은 자전거 체인처럼 있다고 해서 Chain 구조로 데이터가 연결되어 있습니다.

 

Chain은 어떻게 생겼나요? 하나하나의 구성요소가 붙고붙고붙어서 하나의 끈처럼 연결되어 있죠? Hash List도 마찬가지입니다.

그런데 왜 Chain 구조를 하고 있을까~ 생각해봅시다. 데이터가 연속적으로 들어있다면 Chain 구조같은거 필요 없이 그냥 사전처럼 쭈루룩 보면 되겠죠? 그럼 어떻게 되어있길래 쓰느냐.. 비연속적인 순서로 데이터가 쌓여져 있는 겁니다. 이 메모리의 구조를 Heap 구조라고 합니다. 비연속적으로 되어 있으면 어떻게 될까요? 요리조리 왔다갔다 하면서 시간을 더 버리겠죠.. 빨리 User Process한테 데이터를 갖다줘야하는데.. 찾기도 힘들고..ㅎ 그래서 Chain이라는 것이 나왔는데 일정한 공간의 열을 만들어 놓고 그 안에 Hash Value들이 쏙쏙쏙 들어가 있는거죠. 극장을 한번 예로 들어보겠습니다.

 

 

 

지금 막 예매를 진행하고 있는 따끈따끈한 CGV 극장 배치도 입니다. 현재 뿌옇게 된것은 예매불가 혹은 예매완료입니다. 그리고 검회색의 선택가능한 좌석들도 있네요. A~K가 Hash Bucket이라고 생각하고 A열 ~ K열까지 번호로 구성되어 있는 것이 Hash List, 즉 Chain 구조라고 생각해봅시다. A(Bucket)라는 열에는 현재 01~12의 좌석이 있습니다. 그중 04와 09는 예매불가죠? Hash Value가 들어 있다는 것입니다. 하지만 그외의 10개의 칸에는 예매가능, Hash Value가 없어서 넣을 수 있다는 것을 보여주고 있죠. 이처럼 일정한 공간을 만들어 놓고 그 안에 고객이 무작위로 좌석을 고르듯이 이 메모리의 데이터도 순서대로가 아닌 무작위로 데이터를 넣기 때문에 이러한 구조를 써서 리스트를 만드는 겁니다. 무작위로 할 경우에는 위에서 말했듯이 더 오래걸리고 중부난방으로 배치되어 더 찾기 어렵기 때문이죵!

 

Hash List가 이렇게 존재 한다는것! 기억해주시구요. 가장 중요한 것은 Library Cache 안에서 원하는 SQL 문장과 실행 계획이 들어있는 커서를 찾기 위해서는 반드시 Hash List를 읽어야 한다는 겁니다. 이로 인해 문제가 생기는 데요. Hash List는 하나만 존재합니다.

동시에 10명이 조회를 원한다면? Latch가 발생하고 또다시 성능으로 이어지는 악순환이..ㅠ 그래서 가장 많이 쓰이는 Select의 경우만 서로 공유해서 한꺼번에 여러명이 볼 수 있는 기능을 제공합니다. 이렇게 Soft Parse로 SQL의 문장과 실행 계획이 Library Cache에 있다면 조회해서 바로 Execution으로 가지만 없다면 Hard Parse를 한 후 Hash List를 업데이트 해야겠죠. 추가하려면 다시 Library Cache Latch가 필요하고 추가적으로 Shared Pool Latch도 필요합니다. Latch가 참 많이 나오는 개념이네요..ㅎ

 

하나의 Latch만으로 계속 돌리기에는 너무나 큰 무리가 있기 때문에 Session_Cache_Cursor라는 파라미터가 나옵니다. 한 세션 내에서 세 번 이상 수행된 Cursor를 Cache하는 기능인데요. 뒤쪽 파라미터부분에서 자세히 다룰거지만 간단하게 말하면 자주 쓰는 SQL 문장일 경우 그 횟수를 세번으로 지정해서 세번이상 사용한 SQL을 PGA로 옮겨서 Library Cache까지 가지 않아도 PGA에서 바로 찾아서 쓸 수 있게끔 하는 겁니다. 그럼 Latch가 발생하지 않아서 좀 더 빨리 진행이 될 수 있겠죠?ㅎ

 

이제 Soft Parse의 설명은 끝이 났습니다. Shared Pool Check과정에서 Library Cache 부분의 Hash Value를 찾을 수 없는 경우!

Hard Parse 부분으로 넘어가죠. 실행계획과 문장이 없다는 거니까 이제부터 실행계획을 만들어야겠죠. 실행계획은 누가 만든다?

Optimizer가 한다~ㅎ Optimization 과정에서 만듭니다. 여러 계획을 만들어서 Row Source Generation에서 가장 좋은 것을 고른다고 했죠? 네비게이션을 생각하면 하나의 경로만 만드는 것이 아니라 여러가지의 경로로 만들고 가장 좋은 경로를 선택하는 것과 같습니다.

 

옵티마이저가 하는 것은 실행계획 수립이라고 말을 했는데요. 어떻게 하느냐가 관건입니다. 예를들어 옵티마이저는 조인에서 어느 테이블을 먼저 읽어야 하는지 또 어떤 인덱스를 실행시켜야 좋은지 등을 판별하는데 그 척도가 되는 것이 RBO와 CBO가 있습니다.

RBO 옵티마이저와 CBO 옵티마이저의 차이는 실행계획을 세우는 방식의 차이인데요.

RBO(Rule Based Optimizer)는 Server Process가 실행 계획을 세워달라고 요청이 들어오면 미리 정해놓은 규칙을 사용해서 수립합니다.

순위	접근경로
1	Single row by ROWID
2	Single row by cluster join
3	Single row by hash cluster key with unique or primary key
4	Single row by unique or primary key
5	Cluster join
6	Hash cluster key
7	Indexed cluster key
8	Composite index
9	Single-column index
10	Bounded range search on indexed cloumns
11	Unbounded range search on indexed columns
12	Sort-merge join
13	MAX or MIN of indexed column
14	ORDER BY on indexed column
15	Full table scan

 

이렇게 RBO는 15가지의 목차로 되어있습니다. 진행방향은 15부터 1차례로 역순방향으로 조회를 해서 맞춰갑니다.

예를 들어서 select * from emp where empno=7902; 라는 SQL문을 수행하는데 EMPNO에 대해 인덱스가 생성이 되어 있다고 하고 Soft Parse가 실패해서 Hard Parse로 왔다고 하면 15번부터 하나하나 대입해서 보는 겁니다. 15번은 기본값처럼 확보가 되고 14번은 order by가 없으므로 패스~ 13번도 패스~ 등등등 넘어가면 9번에 index죠? empno에 index가 있다고 했으니까 확보하고 다시 1번까지 쭉 올라갑니다. 보면 15번과 9번이 확보되었죠? 우선순위는 낮은게 좋으므로 9번이 채택되어 실행이 됩니다. 이것이 RBO방식입니다.

 

여기서 큰문제점이 있죠. 앞뒤가 꽉 막혀있다는점. 모든 경우의 수를 15가지로 맞춰 놓았기 때문에 한계가 있다는 것입니다.

그리고 다른 것들을 고려하지 않고 우선순위가 높다는 이유만으로 채택을 하기때문에 또한 성능적인 측면에서 문제가 있을 수 있다는거죠.

그래서 RBO는 Oracle 7부터 규칙이 추가되지 않다가 Oracle 10g R2 이후로 아에 지원이 되지 않습니다. CBO만 가능한거죠.

하지만 RBO를 언급한 것은 CBO를 이해하기에 아주 좋기 때문에 언급한겁니다. 이전 것을 알아야 이후것도 잘 쓸 수 있으니까요.

 

그럼 CBO(Cost Based Optimizer)는 무엇일까요? CBO는 Data Dictionary를 보고 판별을 합니다.

Data Dictionary에는 Oracle에 대한 모든 정보들이 들어있기때문에 아주 중요한 요소중 하나입니다. USER_TABLES, DBA_TABLES 등과 같은 Dictionary를 보고 판단한다는 것이죠. 이렇듯 Optimizer는 Hard Parse를 주관하는 아이이기 때문에 튜닝을 할때 성능을 얘기할때 중요하게 다루어야 하는 문제입니다. 여기서 하나 더 알아야 할 것이 있는데요. Data Dictionary는 대부분이 자동 업데이트가 되지 않습니다.

그럼 어떤 문제가 발생할까요? Optimizer를 네비게이션에 빗대어서 설명을 했었는데요. 데이터베이스를 업데이트하지 않은 네비게이션이 제대로 길을 찾아서 줄까요? 그 길이 없어지거나 다른 좋은길이 생겼다면? 크나큰 손실이죠. 그만큼 Dictionary가 업데이트가 되지 않는 것이 성능상의 큰 문제가 될 수 있다는 겁니다. 특정한 경우 자동업데이트가 되지만 항상 최신정보가 아니라는점!

그래서 사람이 Dictionary를 관리해 주어야 한다는 점! 10g부터는 Dictionary 정보를 자동으로 모아주고 관리해 주는 고마운 기능이 생기기도 했습니다. 그래도 Dictionary는 중요중요 ㅎ

 

다시한번 크게 정리를 해보도록 하겠습니다. Parse부분만 지금 24시간째 붙들고있네요.. 오늘 사랑니 두개를 빼서 오래걸렸네요..ㅠ

Parse는 실행계획을 세우고 가장 좋은 실행계획을 선택하기 위한 과정입니다. 순서도로 한번 보도록 할까요?

 

 

 

간단히 보면 이렇네요..ㅎ 전체적인 맥락을 잡은건데 이해가 되시나요? 세부적인 사항들은 위에서 다 설명을 드렸고 그걸 다 기억을 해야한다는 것!! 이거 보면 다 기억나야됩니다. 쏙쏙 ㅎㅎ Parse의 과정은 이렇게 끝을 내고 이제 BIND(바인드)로 넘어가보겠습니다.

 

BIND는 그렇게 어려울 것이 없는 것 같습니다. Parse는 참 오래걸렸는데요..ㅎ

Bind는 같은 테이블의 같은 컬럼을 많이 조회하는 것을 줄이는 건데요. 1000건을 조회하려고하면 1000번 Parsing 해야되는 것이 아니라 1번 Parsing하고 실행계획을 1개만 생성해서 조건을 다르게 주어서 조회하면 더 낫지 않을까 하는 방법입니다. 그래서 말하는 게 Bind 변수라는 것이죠. PGA에서 Bind 변수를 저장하는 곳은? Persistent Area 였습니다. 여기에 저장하고 저장하고 해서 조회하는 거죠.

 

Bind는 단점을 가지고 있는데요. 분포도가 균일하지 않을 경우에는 bind를 사용할 수 없다는 겁니다. 여기서 분포도는 데이터가 집중적으로 쏠려 있다는 것인데요. 그럼 조회가 더 어렵겠죠. 이러한 경우에는 통계정보인 histogram을 생성해야 하는데 histogram을 생성하면 bind가 되지 않습니다.. 그래서 추가된 기능이 많습니다. 특히 11g에서는 Adaptive Cursor Sharing이라는 기능도 있습니다. 이건 추후 파라미터에서 설명을..ㅎ 지금은 저도 잘 이해가 안돼서ㅠ..이렇듯 Bind는 단점도 있지만 Hard Parse를 줄여주어서 큰 성능 향상이 됩니다.

전에 테스트 본것중에 Bind를 쓰면 1분 50초 정도였는데 Bind를 해제하니까 6분정도 걸리는 것을 본적이..ㅎ 그정도로 차이가 많이납니다.

되도록이면 사용하도록 하는게 좋겠죠?ㅎ

 

Execute(실행)입니다. 제 3단계죠!ㅎ 이 부분은 Data Buffer Cache와 관련이 많은 부분입니다. 실행을 해야하기 때문입니다.

일반적으로 데이터는 하드디스크, Oracle에서는 Data File들이 가지고 있습니다. 이전에 SGA 구성요소 하면서 말했던 것들이지요.

Oracle이 Data File에 있는 것을 읽기 위해 RAM같은 Data Buffer Cache를 활용을 한다고 했습니다.

 

여기서의 운용방법도 어찌보면 Parse와 비슷합니다. Server Process는 자신이 원하는 데이터를 가진 Data 블록를 찾아야 겠죠? 그러기 위해선 비교를 해야겠죠. 비교를 하기에 적합한 것은? Hash! Server Process는 자신이 원하는 데이터블록의 주소를 Hash 함수에 넣어서 Hash Value를 만들고 Data Buffer Cache가 Hash Value로 관리하는 주소 리스트와 대조를 해봅니다. 원하는 블록이 Cache에 있을 경우 가져가서 Fetch에 이용하는 것이고 없다면 Data File에서 가져와야겠죠. Data File에서 원하는 파일을 Cache로 끌어올린다음에 가져가야합니다.

 

여기서 Execute의 단계의 역할이 명확히 나오는데요. Hard Disk의 Data File에서 Data가 들어있는 블록을 찾아 Data Buffer Cache로 복사해오는 과정을 말합니다. 그래야 Server Process가 데이터를 조회할 수 있으니까요.

 

하나 더 중요한 개념을 말하자면 DB Buffer Cache와 Data File의 관계입니다. 데이터를 옮기면 그만큼 I/O가 발생을 할텐데요. 한번에 얼마나 많은 데이터를 옮기느냐도 중요합니다. 특히 Oracle은 Block단위의 I/O를 운용하고 있습니다. 그러면 Block의 크기가 중요하겠죠.

초기화 파라미터 파일의 DB_BLOCK_SIZE라는 파라미터로 조정이 가능한데 이것은 DB를 재부팅 하기 전까지 값이 변하지 않습니다.

9i 버전의 초기값은 4k였고 10g는 8k로 향상이 되었습니다. 아무래도 데이터가 많아지고 H/W의 성능이 좋아진 이유도 있겠죠.

DB_BLOCK_SIZE가 커지면 I/0는 줄일 수 있지만 공간 낭비가 많이 생길 수 있고 DB Buffer Cache에 Wait가 많이 생겨서 성능 저하가 일어나는 경우도 생깁니다. 그래서 어느정도의 표준점을 두고 해야되는 것 같습니다. 시간이 많이걸리거나 I/O가 너무 많이 발생해도 안되 기때문에 잘 관리해야하는 ORACLE..ㅎ 여기에 관한 자세한 것도 뒤에서 다루도록 하겠습니다.

 

이제 Exeucte도 끝이나고 마지막 4단계 Fetch(인출)입니다. 방금전에 DB Buffer Cache의 I/O가 Block단위로 되어있다고 말했습니다. 그럼 데이터도 Block단위로 올라올겁니다. 그러면 자신이 원하지 않는 데이터도 같이 딸려올 가능성이 있다는 것이죠. 그래서 Fetch 단계에서는 자신이 원하는 데이터만을 쏙쏙 뽑아서 가는 것을 말합니다. 그래서 인출이라고 하는겁니다. Sort에 대한 요청이 있었다면 이것도 Fetch 단계에서 Soft를 완료해서 데이터를 보내줍니다. Soft는 어디에서 발생하죠? PGA의 Soft Area가 있었습니다.

 

이렇게 Select의 SQL 문장 실행 원리를 살펴보았는데 꽤 기네요..ㅎㅎ 오래걸릴만도.. 그만큼 이해를 잘해야되는 부분같습니다.

 

바로 Update(DML)를 설명을 하도록 할건데요. 전체적으로 비슷하지만 Redo Log Buffer와 Undo Segment가 나타납니다.

Update(DML)도 똑같이 Parse -> Bind -> Execute -> Fetch가 일어납니다. 동일하죠 ㅎ 동일한 부분도 많기 때문에 한번에다루는 겁니다... 많았으면 SELECT 따로 DML 따로 했겠죠..ㅎ 안그래도 지금 스크롤이 좀 많은데..

 

Parse과정까지는 똑같습니다. Bind도 똑같죠. 하지만 Select를 제외한 DML들은 Execute가 복잡합니다. 단순히 DB Buffer Cache로 데이터를 끌어오고 확인하는 것이 아닌 추가적으로 변경내역을 저장하기 때문입니다. 이전에 Redo Log가 복구시에 꼭 필요하다는게 이 말이었습니다. Execute과정에서 Server Process는 변경되는 데이터의 변경내역을 Redo Log Buffer에 먼저 기록을 합니다.

그후 Undo Segment에 이전 이미지(데이터)를 기록한 후 Data Buffer Cache의 내용을 변경하게 됩니다. 이순서는 매우매우 중요합니다.

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

이 순서를 잘 기억을 해야합니다. 복구시에도 유용히 쓰이는 과정이기도 합니다. Backgrond Process부분과 Redo Log부분에서도 또다시 등장 할 겁니다. 중요하니까요 ㅎ 그리고 얽히고 설킨 Oracle이니까요 ㅎㅎ Transaction 부분도 나중에 설명을!

 

이렇게 SQL 문장의 실행원리는 끝이 났습니다. 책에서는 한 챕터네요..ㅎ 뿌듯하기도 하고 좀더 자세히 다루고 싶은 마음도 있지만.. 아직 많은 부분들이 기다리고 있기때문에 ㅎㅎ 다음에는 Background Process들입니다. 앞에서 했던 Oracle Server 구성요소들의 이야기와 룰들이 나오겠습니다. 그렇게 어려운 부분은 없으니 쭉쭉쭉 나갈것 같네요 ㅎ 다음 포스팅에서! 뿅