SQL 문장 실행 원리

전체 과정

정리하자면,

1. User Process가 서버 위치 정보가 들어있는 tnsnames.ora를 참고해 해당 서버로 찾아감

2. 서버 쪽 listener.ora 파일 안에 접속하려는 서버 IP가 있으면 리스너는 해당 Server Process를 불러옴

3. User Process는 Server Process에게 수행되어야 할 쿼리 전달

4. Server Prcoess는 Parse -> Bind -> Execute -> Fetch 과정을 통해 쿼리 수행

5. 작업이 끝나면 Server Process는 결과값은 User Process에게 전달하고, User Process는 사용자에게 전달

※ 주의해야 할 점 ※

오라클과 리스너는 서로 다른 프로그램이기 때문에

server는 켜있는데 listener가 죽어있는 경우도 있음 --> 신규 접속 불가능

listener가 켜져있는지 확인하려면 # tnsping SID 명령어를 사용

리스너는 새로 들어오는 client만 관리

이미 들어와 있는 사용자는 리스너를 통하지 않고 바로 server process와 연결되기 때문.

즉 리스너가 장애가 났을 경우,

전에 미리 접속해 있던 사람들은 계속 서버 이용이 가능하지만 새로 접속해야 하는 사용자들은 접속할 수 없다.

리스너 로그 파일(listener.log)이 2G가 넘어가게 되면 리스너가 죽어버리기 때문에 항상 관리해줘야 한다.

경로는 listener.ora파일에 있음

용량이 거의 차버렸다고 리스너 로그 파일을 그냥 지워버리면 안됨!!!!

$ cat /dev/null > listener.log

이런식으로 리스너파일의 용량을 0 으로 만들어줘야한다.

SELECT 문 실행 원리 

SQL 문장을 User Process가 Server Process에게 전달 한 후 

Parse --> Execute --> Fetch

1. Parse - 구문분석

Syntax Check 문법검사: 오라클 키워드 검사

Semantic Check 의미검사: 테이블이름이나 칼럼이름 검사

Shared Pool Check 권한검사: 해당 사용자가 이 테이블을 조회할 수 있는지 검사

검사들은 데이터 딕셔너리를 사용하게 되고 

데이터 딕셔너리를 캐싱해 두고 성능을 높여주는 곳이 Shared Pool 안의 Dictionary Cache 또는 Row Cache

Shared Pool의 Library Cache를 검사한 후 공유되어 있는 실행계획이 있는지 검사

있으면 바로 Execution 단계로 진행 --> Soft Parse 또는 커서 공유라고 함

없으면 Optimizer를 찾아가서 실행 계획을 만들어 달라고 요청  --> Hard Parse

Soft Parse

Library Cache안에 있는 커서 즉, 공유커서는

이미 한번 수행되었던 SQL 문장의 실행계획과 관련 정보를 보관 하고 있다가 재활용 해줌

Optimizer 옵티마이저가 실행계획을 만들어주는 부담을 줄이게 됨으로써 SQL 수행 속도를 빠르게 함

커서공유는 Parent Cursor 와 Child Cursor 모두가 완전히 동일해야만 이루어지게 됨

Hard Parse

Optimizer 는 실행 계획을 생성시켜주는 역할

실행 계획을 세울 때 데이터 딕셔너리(Static Data Dictionary) 정보를 보고 판단

옵티마이저가 실행 계획을 세워주는 대로 실행을 하기 때문에 옵티머아저의 실행 계획이 SQL 수행 속도에 절대적인 영향

Staitc Data Dictionary는 자동으로 업데이트 되지 않기 때문에 직접 관리 해줘야 함

인덱스 생성과 공간 관리 역시 사람이 관리 해줘야 함

2. Execute - 실행

하드 디스크의 데이터 파일에서 데이터가 들어있는 블록을 찾아 메모리 즉, DB Buffer Cache로 복사해 오는 과정

서버 프로세스가 해당 데이터를 가져오기 위해 해당 데이터가 저장되어 있는 블록을 찾게 됨

찾는 모든 데이터는 Database Buffer Cache에 있어야 함 

서버 프로세스는 해당 블록을 찾기 위해서 우선 DB Buffer Cache를 확인하는데 

DB Buffer Cache에 원하는 블록이 

있으면 즉시 다음단계인 Fetch 단계로 진행

없으면 서버 프로세스가 하드 디스크로 가서 해당 블록을 찾아 DB Buffer Cache로 복사

디스크에서 메모리로 복사해 오는 시간이 굉장히 오래걸리는 편 -> 성능에 영향을 준다.

인덱스 생성 해놓게 되면 디스크에 어떤 파일이 어디있는지 알기 때문에 성능 UP, 속도 UP

옵티마이저가 실행계획을 세울 때 인덱스가 있으면 인덱스를 보고 찾아가라고 하기 때문에 인덱스 생성은 성능과 연관됨

인덱스가 없다면 풀스캔을 해야하기 때문에 성능 DOWN

3. Fetch - 인출

DB Buffer Cache에 복사된 블록 중에서 사용자가 요청한 원하는 데이터만 골라내는 과정

DML 문장 실행 원리 

(INSERT / UPDATE / DELETE/ MERGE)

SELECT 문과 같이 Parse 과정까지는 동일

Execute 과정에서

원하는 데이터가 들어있는 블록을 DB Buffer Cache로 가져 온 후 

서버 프로세스는 PGA에서 change vector를 생성해 변경되는 값을 쓰고 나서

변경되는 데이터의 변경 내역을  Redo Log Buffer에 먼저 기록

그 후 Undo Segment에 이전 데이터 즉, 원본 데이터를 기록한 후 DB Buffer Cache의 내용을 변경

즉 Execute 과정에서

DB Buffer Cache로 데이터 복사 --> Redo Log Buffer에 데이터 변경 내용 기록 --> 

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

Fetch 과정은 없다.

★ 추가 과정들

redo log buffer에 기록하기 전 undo segment를 확보하고 작업함

commit이 되면, redo log file에 먼저 내려쓴 후, DB에 내려쓴다.

Redo Log Buffer에 DB내에서 데이터 변경이 생기는 모든 것들을 기록한다. DML / DDL 작업같은..

select 문은 조회하는 문장이기 때문에 Redo Log Buffer에 기록하지 않는 것임.

▶ 아직 데이터 변경에 대한 commit 작업을 안했을 때,

commit을 날리기 전까지는 다른 사용자들은 변경 내용에 대해 조회할 수 없기 때문에 수정된 값이 아닌 원본값을 보게됨

이것을 Consistent Read 라고 함.

--> 좀 더 자세한 원리

DML 작업할 때, DB Buffer Cache에서 데이터가 변경되고 거기에는 LOCK이 설정되어서

해당 작업에 대한 commit이나 rollback이 수행되기 전에는 아.무.도. 해당 블록을 볼 수없게 된다.

그래서 다른 사용자가 가틍ㄴ 조건의 데이터를 조회하는 쿼리를 날렸을 때

undo segment에 있는 데이터를 DB Buffer Cache로 복사해와서 조회한다.

DB Cache에 있는 블록들을 읽다가 SCN이 주변보다 빠른(높은)것이 있으면

undo segment 정보가 있는 undo block address를 통해 원본을 보게 된다.

갔는데 다른 사용자의 작업으로 인해 덮어쓰이게 되면 ORA-01555 Snapshot too old 라는 에러 발생하게 됨