SQL 문장의 실행 원리
SQL 문장의 실행 원리
1) 사용자 문장 실행 시 User Process에서 Server Process로 실행한 SQL문 전달
2) User Process로부터 문장을 받은 Server Process가 해당 문장의 세부적 체크 진행
· Syntax Check : SQL문이 적절한 문법을 사용했는지 검사
· Semantic Check : SQL문에 포함된 오브젝트들이 실제로 존재하는지 검사
3) Parse과정 후 Shared Pool의 Library Cache에서 공유되어있는 실행계획이 있는지 체크
4) 실행계획이 있을 경우 Execution 진행 (Soft Parsing)
5) 실행계획이 없을 경우 Optimizer를 통해 Data dictionary 등을 참조하여 실행계획을 새로 생성 후 Library Cache에 저장 (Hard Parsing)
SELECT 문장의 실행 원리
Parse(구문분석) -> Bind(바인드) -> Execute(실행) -> Fetch(인출)
1) Parse(구문분석)
- User Process로부터 전달받은 SQL문장을 Server프로세스가 SQL문을 수행하기 위해 Parse Tree생성
- Parse Tree를 만드는 과정에서 Syntax Check, Semantic Check 진행
- Data Dictionary 조회를 통해 문법이 맞는지, 해당 테이블이 있는지 확인
- 자주 사용되는 Data Dictionary는 Shared Pool의 Dictionary Cache에 캐싱해 두어 성능을 높힘
- 오류가 없을 경우 SQL문장을 Hash 함수로 Hash Value로 변경한 후 Shared Pool의 Library cache에서
Hash Value와 비교하여 동일한 값이 있는지 확인 ( 커서 공유 혹은 Soft Parsing이라고 함 )
· Cursor : 메모리에 데이터를 저장하기 위해 만든 임시 저장 공간 ( 공유 커서, 세션 커서, 어플리케이션 커서 )
· Library Cache 안에 있는 커서는 공유 커서를 의미
· 공유 커서
- 한번 수행된 SQL 문장의 실행계획과 관련 정보를 보관
- 재활용을 통해 Hard Parse의 부담을 줄여 SQL 문장의 수행속도를 빠르게 함.
SELECT /* cursor_test */ empno, ename
FROM emp
WHERE empno = 7521 ;
-- sql의 실행횟수 및 커서공유 현황
SELECT sql_id
, sql_text
, parse_calls -- parse 시도 횟수
, loads -- hard parsing을 거쳐 loading된 횟수
, executions -- 실행 횟수
FROM v$sql
WHERE sql_text LIKE '%cursor_test%'
AND sql_text NOT LIKE '%V$SQL%';
--> parse 시도를 2번 했으며,
메모리에 이미 등록된 SQL이므로 hard parsing은 1번 했고,
실행 횟수는 2번 했음
옵티마이저(Optimizer) : SQL의 실행 계획을 생성해주는 네비게이션 역할
옵티마이저 모드 : RBO, CBO
1) RBO : Rule Based Optimizer(11g부터 사용 불가)
2) CBO : Cost Based Optimizer
- 데이터 딕셔너리 정보를 이용하여 판단
- 옵티마이저가 참조하는 데이터 딕셔너리 중 대부분은 Static Dictionary, 즉, 항상 최신 정보를 가지고 있음
- 데이터 딕셔너리를 관리해야 함 ( 통계정보 생성 및 관리 )
2) BIND(바인드)
- SQL문이 정확히 일치해야 Soft Parsing이 가능해짐
- 변수 값이 달라지는 동일 쿼리에 대해 모두 다른 SQL로 인식 -> Hard Parsing 유발
- 변수 값이 달라지는 부분에 바인드 변수 처리
ex) select ............
from emp
where empno = :emp_num:
- 바인드 처리하면 모든 SQL이 동일 실행계획을 가짐
3) Execute(실행)
- Parse와 Bind 단계 후 해당 데이터를 가져오기 위해 데이터가 저장되어있는 데이터 블록을 찾아 DB Buffer Cache에 복사하는 과정
- 사용자가 찾는 모든 데이터는 SGA의 DB Buffer Cache에 있어야 함
- 사용자가 찾거나 변경하는 모든 작업은 DB Buffer Cache에서 작업이 수행됨
- 서버프로세스는 해당블록을 찾기 위해 DB Buffer Cache를 확인 후 없는 경우 데이터 파일로부터 복사해옴
- 데이터 파일과 DB Buffer Cache의 데이터 이동은 BLOCK 단위
- DB_BLOCK_SIZE 크기만큼 데이터를 이동(default : 8K)
4) Fetch(인출)
- Execute 단계까지 수행하면, 원하는 데이터가 들어있는 블록이 DB Buffer Cache에 올라오게 됨
- 데이터의 I/O 최소 단위가 Block이므로, DB Buffer Cache에 원하는 데이터만 있는 것이 아닌 다른 데이터도 존재
- 사용자가 요청한 데이터만 골라내는 과정을 Fetch라고 함
- 정렬이 필요하거나 추가 작업을 요구하는 경우 Fetch과정에서 Sort를 하여 데이터를 보내주며, 정렬은 PGA영역(Program Global Area)에서 수행
DML 문장의 실행 원리
- 모든 의의 수행원리는 동일하다.
- DML의 수행단계는 Fetch과정만 없고 나머지는 동일
Parse(구문분석) -> Bind(바인드) -> Execute(실행)
1) 서버 프로세스는 Parse과정 수행
- Library Cache에 실행계획이 있는지 확인
- 있으면 Soft Parse, 없으면 Hard Parse 수행
2) 실행계획을 받은 서버프로세스는 Library Cache에 Plan정보 등록한 후 Execute 수행
- DB Buffer Cache에 update 하려는 데이터가 있는지 확인
- 없을 경우 데이터파일의 해당 블록을 DB Buffer Cache에 복사
3) Execute 단계에서 원하는 데이터가 들어있는 DB Buffer Cache를 가져온 후 Server 프로세스는
데이터 변경 내역을 Redo Log Buffer에 먼저 기록
4) 기록 후 Undo Segment에 원본 이미지를 기록한 후 DB Buffer Cache의 내용을 변경
□ [정리] 데이터 변경이 일어날 경우 순서
Redo Log Buffer에 기록 -> Undo Segment에 기록 -> DB Buffer Cache의 실제 데이터 변경
1. Parsing
2. Data Buffuer Cache 조회
3. datafile에서 DBC로 불러옴
4. 바꾼 데이터를 Redo Buffer에 올려둠
5. undo segment에 기록
6. Data Buffer Cache 데이터 변경
* Undo segment에 저장 하는 이유
실제 데이터 파일이므로 무거울 수 있지만
1. 서버가 갑작스럽게 내려갔을 때(memory flash) Before 이미지를 남기기 위해
2. 작업 외 인원이 조회하기 위해 ( 읽기의 일관성 )
Oracle Background Process
Oracle Process 종류
1) User Process : 사용자가 작성한 SQL 문장을 Server 프로세스로 전달하고 결과를 가져오는 프로세스
2) Server Process : User Process가 전해 준 SQL 문장을 실제 실행하는 프로세스
3) Background Process : Oracle Server가 시작되면 자동으로 시작되어 운영과 유지를 담당하는 프로세스
· User Process와 Server Process는 사용자가 접속하면 생성되고 접속을 종료하면 해제
· Background Process는 Oracle Server가 시작되면 함께 시작되고 종료되면 함께 종료
Database Writer (DBWR) [ Database Buffer Cache --> Data File ]
Database Buffer Cache에서 변경완료 후 저장 되어야 하는 블록(Dirty Block)을 데이터 파일로 저장하는 역할
□ DBWR 동작 시점
1. Checkpoint 신호가 발생 했을 때
2. Dirty Buffer 가 임계 값을 지났을 때
3. Time out 이 발생했을 때s
4. RAC Ping 이 발생했을 때 --> 서로 다른 instance에서 ins1에서 데이터 변경 했을 때 ins2가 해당 데이터 조회 했을 시
5. Tablespace 가 Read only 상태로 변경될 때
6. Tablespace 가 offline 될 때
7. Tablespace 가 begin backup 상태가 될 때
8. Drop table이나 Truncate table 될 때
1. redo #1 이 가득차면 archive에 저장
2. log change를 이용해 #2로 넘어감
Database Writer (DBWR) 개수 수정하기
select * from v$parameter
where name ='db_writer_processes';
DBW0~DBW9 (최대 10개)
Log Writer (LGWR)
- Server Process가 변경내역을 Redo Log Buffer에 기록하게 된다
- LGWR은 Redo Log Buffer에 있는 내용을 디스크의 Redo Log File로 저장한다.
- 만약 Commit 요청이 들어왔는데 Redo Log File이 없는 경우, Alert Log 파일에 해당 내용을 기록해두고
LGWR은 다음 Commit 요청을 수행하지 않고 대기 ==> DB 전체 중단
□ LGWR 동작 시점
1. Commit 이 발생 했을 때
2. 1/3이 찼을 때
3. 변경량이 1M가 되었을 때
4. 3초 마다
5. DBWR이 내려 쓰기 전에 -- 무조건 LogWriter가 먼저 실행
PMON (Process Monitor)
- Server Process 생성 및 관리
- Server Process fail일 경우
□ commit 된 데이터 저장
□ commit 안된 데이터 rollback
□ Lock Release
SMON (System Monitor) - Instance Recovery
- 인스턴스가 비정상 종료 되었을 경우, 인스턴스를 시작할 때 Clean Up하는 역할 (Instance Recovery)
- Instance Recovery 과정에서 누락된 Transaction을 Recovery하는 역할
- 비정상 종료된 Transaction Temporary segment를 Clean up하는 역할
1. 사용자 A가 홍길동을 일지매로 변경
2. 사용자 A가 commit 수행해서 변경내용이 Redo log file에 기록 됨
3. 사용자 B가 이순신을 강감찬으로 변경
4. 사용자 B는 commit을 안 했지만 Redo log Buffer의 변경내용이 1M가 되어 Redo log file에 기록이 됨.
5. Shutdown abort;
---> commit 된 데이터만 DataFile로 이동 됨. ( 다 읽은 후 롤백 처리까지 )
Roll Forward --> DB Open --> Roll backward
CKPT(Checkpoint Process)
- CKPT Process는 DBWR에게 Checkpoint 신호를 전달해 주며, Control file과 Data file Header에 해당 Check Point 정보 를 기록하는 역할 수행.
- Checkpoint 정보에는 Checkpoint 위치와 SCN(데이터 변경 시점), 해당 내용을 담고 있는 Redo log 내용의 위치 값 을 담고 있다.
Oracle Startup & Shutdown
Shutdown --(Pfile/Spfile)--> No mount --(Control file)-->mount --(Data file/Redo log file)--> OPEN
SQL > startup; -- shutdown 상태부터 open 상태까지 진행
SQL > startup nomount; -- shutdown 상태부터 nomount 단계 까지 진행 이후 진행은 alter명령어로 순서
SQL > startup mount; -- shutdown 상태부터 mount 단계까지 진행
SQL > alter database mount; -- nomount 상태부터 mount단계까지 진행
SQL > shutdown;
SQL > shutdown immediate;
SQL > shutdown abort;
1) NOMOUNT
- 서버 프로세스가 Parameter File을 찾아 읽음
- 여러 인스턴스가 존재하는 경우 해당 SID를 읽어 들여 Parameter File을 찾음
- Parameter File에는 정적 파라미터 파일인 Pfile과 동적 Parameter 파일인 Spfile이 있음
- 정적 파라미터 파일은 관리자가 수동으로 변경
- 동적 파라미터 파일은 서버 프로세스가 자동으로 변경
- Parameter파일을 읽고, 그 안에 저장되어 있는 파라미터 값을 참고하여 인스턴스 구성
- 인스턴스는 SGA와 Background Process들로 구성되어 있으므로 MOUNT단계에서 RAM에 인스턴스가 생성되어
작업할 수 있는 메모리 공간이 확보됨
- Alert Log 파일을 열어서 로깅을 시작
- Alert Log는 인스턴스가 시작되어 운영되고 종료될 때까지 중요한 내용을 모두 저장하는 파일
*profile에 등록하면 편하게 alert log를 확인할 수 있다.
alias alert='tail -f /oracle11/oracle/diag/rdbms/testdb/testdb/trace/alert_testdb.log'
2) MOUNT
- NOMOUNT 단계를 마치면, Control file을 읽고 MOUNT 단계 진행
- Control File의 위치정보는 Parameter File에 기록되어 있음
- MOUNT단계에서는 Database의 이상유무 확인 후 open 진행
- Instance Crash로 판단되면 OPEN 단계로 가기 전에 SMON이 Instance Recovery를 수행
- SMON이 Recovery를 수행할 경우 관련 내용을 Redo Log File에서 찾게 되는데, 복구하려는 내용이
Redo Log File에 없을 경우에는 SMON에 의한 Recovery 실행 -> Media Recovery 수행(Archive 필요)
- instance crash는 control file와 data file파일의 scn(checkpoint) 정보가 서로 다를 경우 crash를 판단하게 되고,
서로 다른 정보를 맞추는 과정이 instance recovery이다.
Oracle Parameter File
| pfile | - $ORACLE_HOME/dbs/initSID.ora - Text file - OS 편집기로 내용수정 가능 - 내용 변경 시 재 시작 해야 적용됨 - Dynamic으로 파라미터 적용 불가능 |
| spfile | - $ORACLE_HOME/dbs/spfileSID.ora - Binary file - OS 편집기로 내용수정 절대 불가능 - 내용 변경 시 재 시작 하지 않아도 적용됨(일부 제외) - Dynamic으로 파라미터 적용 가능 (alter system set) |
| *** 둘 다 존재 시 SPFILE만 사용함 *** | |
*** 둘 다 존재 시 SPFILE만 사용함 ***
1) sqlplus / as sysdba 접속 후
create pfile from spfile;
생성 후 spfile 지우고 다시 재부팅
2) pfile로 켜진지 확인 후 동적 변경이 가능한지 확인
· select * from v$parameter
where name like '%large_pool_size%'; -- 명령으론 수정 불가능 직접 init파일을 수정해야함!
· alter system set large_pool_size=16m; -- 오류는 안나나 실제적으론 수정이 안 되어있음
spfile 환경일 경우 파라미터 변경 옵션
- SQL > ALTER SYSTEM SET DB_CACHE_SIZE=30m Scope=Memory;
- Scope 옵션
· MEMORY : Spfile 내용은 변경하지 말고 현재 작동 중인 인스턴스에만 적용
· SPFILE : 현재 운영중인 인스턴스에 적용하지 말고, SPFILE 내용만 변경 (DB 재기동시 변경되도록 함)
· BOTH : 두 가지 모두에 적용 (Scope 옵션을 사용하지 않을 경우 기본 모드)
* SGA 남은 사이즈
SELECT CURRENT_SIZE/1024/1024 AS "FREE_MEMORY(MB)"
FROM V$SGA_DYNAMIC_FREE_MEMORY;
select * from v$parameter
where name like '%sga_max_size%';
alter system set sga_max_size=400m scope=spfile;
spfile환경에서) sga_max_size는 동적으로 변경 불가능하므로 both, memory 설정 불가능
Oracle shutdown
1) NORMAL ( 사용할 일 절대 없음 )
- shutdown normal 또는 shutdown 명령어로 종료
- 명령 전에 접속되어 있던 사용자가 있을 경우, 사용자가 있을 경우, 사용자들이 모두 스스로 접속을 종료할 때까지 기다렸다가 종료
- 사용자가 접속을 종료하지 않으면, Instance는 종료되지 않고 무한 대기
2) TRANSACTIONAL
- shutdown transactional 명령어로 종료
- 사용자가 스스로 접속을 종료할 때 가지 기다리지 않고 강제로 접속을 중단시킨 후 Instance를 종료
- 접속을 강제로 중단 시키는 시점은 사용자가 수행중인 Transaction이 끝나는 시점
- DML 작업을 수행 중일 경우에는 해당 트랜잭션을 종료하는 명령어(DDL,DCL,TCL) 수행 시 인스턴스를 종료
- 사용자가 트랜잭션을 종료하지 않게 되면 Instance를 종료할 수 없음(사용자가 COMMIT,ROLLBACK 명령어를 수행해야 함)
3) IMMEDIATE
- shutdown immediate 명령어로 종료
- 사용자의 행동에 상관없이 즉시 접속을 강제로 종료
- 접속이 종료되는 시점까지 해당 사용자가 수행한 작업중에 Commit이 완료된 데이터를 DB Buffer cache에서 찾아
데이터 파일로 저장해주고, 완료되지 않은 작업은 Rollback 시킨 후 Instance 종료
4) ABORT
- shutdown abort 명령어로 종료
- 사용자의 행동에 상관없이 즉시 접속을 강제로 종료
- immediate와 차이점은, 사용자가 수행한 작업을 저장하지도 Rollback하지도 않고 즉시 Instance 종료
- 다시 Startup될 때 SMON이 Instance Recovery를 수행해서 복구함
---> IMMEDIATE는 CheckPoint를 발생시키고 ABORT는 CheckPoint를 발생시키지 않음