제5장 문 처리 (by kkabong) [2009.04.30]
SQL 문의 유형에 대한 이해
DDL(데이터 정의 언어)
- 오라클 데이터 사전을 수정, 즉 테이블 생성, 사용자 추가, 테이블로 부터 열삭제, 트리거 생성 등을 수행하기 위하여 실행되는 문
DML(데이터 조작 언어)
- 데이터베이스의 데이터를 액세스하고 수정하기 위하여 실행되는 문으로서 SELECT, INSERT, MERGE, UPDATE, DELETE명령을 포함한다.
문은 어떻게 실행되는가?
- 파싱 : 제출된 문의 유효성을 검증하기 위하여 구문과 의미를 검사한다.
- 최적화 : 데이터베이스에서 문을 수행하기 위하여 사용될 수 있는 최적의 계획을 생성한다.
- 행-원본 생성 : 최적화된 계획을 취하여 실행 계획, 즉 실행을 위한 트리를 만든다.
- 문 실행 : 실제로 쿼리를 수행하기 위하여 행-원본 생성 과정의 결과를 실행한다. DDL또는 수정 DML 문의 경우에는 이 과정이 마지막이지만 쿼리 문의 경우에는 이 과정이 클라이언트 애플리케이션이 데이터를 검색하는데 활용하는 인출 단계의 시작이다.
파싱
- 오라클은 SQL 문의 종류를 결정하며 DDL문과 DML문은 서로 다른 과정을 밟는다.
- DDL문은 오라클 풀에 저장되지 않기 때문에 공유 풀 검사의 대상이 아니다.
DML문 인 경우 아래 과정을 거친다.
- 구문분석 : 쿼리가 유효한 SQL인가?
- 의미분석 : 쿼리가 유효한 SQL인 경우 이치에 맞는지, 엑세스 가능한 객체를 액세스 하고 있는지등을 판단함
- 공유 풀 검사 : 이전에 누군가에 의해 파싱되었으며 오라클이 이미 수행된 작업을 재 사용할 수 있는가?
- 소프트 파스와 하드 파스로 수행되어 지는데 DDL은 항상 하드파스 형태로 수행되며 결코 재사용 되지 않는다.
최적화와 행-원본 생성
- DML문은 일생동안 최소한 한번은 최적화 된다. (하드파스 과정에서 발생한다.)
- CPU작업을 필요로 하는 절차로서 실제로 문이 실행되는 시간보다 오래 수행될 수도 있다.
- 하드파싱은 CPU사이클을 소비할 뿐만 아니라 공유풀에 높은 래치를 유발한다.
규칙기반의 최적화기(RBO)
- 쿼리계획이 잘 알려진 규칙의 집합에 근거하여 작성된다. 성문화된 규칙만이 적용된다.
비용기반의 최적화기(CBO)
- 엑세스되는 실제 데이터에 관한 통계정보에 근거하여 쿼리가 최적화 된다.
실행
- 행-원본 생성기의 출력을 위하여 실제로 문을 실행한다. 이것만이 DML 실행의 필수 과정이다.
오라클의 COMMIT과정
- 서버 프로세스는 SCN과 함께 커밋 기록을 리두 로그 버퍼에 위치 시킴
- LGWR은 모든 리두 로그 버퍼 엔트리와 커밋 기록을 리두 로그 파일에 연속적으로 옮김(이후 오라클 서버는 실패하는 경우에도 변경 사항을 유지)
- 사용자에게 COMMIT이 완료되었다는 것을 알림
- 서버 프로세스는 트랜잭션이 완료되었으며 자원 잠금(lock)이 해제되었다는 것을 나타내는 정보를 기록
SCN(System Change Number)
- 트랜잭션이 커밋될 때마다 오라클 서버는 트랜잭션에 커밋 SCN(System Change Number)을 지정
- 데이터베이스 내에서 Unique
- SCN을 사용함으로써 오라클 서버는 운영 체제의 날짜나 시간에 의존하지 않고 일관성 검사를 수행가능
문 실행 요약
① Parse(구문분석)
- User Process는 쿼리문 분석 또는 컴파일하라는 요구와 함께 Server Process 로 쿼리문을 보낸다.
- Server Process 는 명령의 유효성을 검사한 후 SGA의 공유풀을 사용하여 문장을 컴파일 하고 실행 계획을 세운다
- Syntax Check
- Semantic Check( 의미론적 Check )
- Object resolution
- 권한유무
- Lock( DDL 방지)
- ParseForm( ParseTree )생성[VLDB: 컴파일된 상태 ] \+ execution plan 생성( Optimazer )
- 여기에서 SQL 문, Parse Form, Execution Plan은 Library Cache 안에 저장됨/ Object resolution, 권한유무는 DD( Data Dictionary )에서 찾음.
② Execute( 실행 )
- 서버 프로세스는 데이터를 읽어올 준비가 된다.
③ Fetch( 인출 )
- 오라클 서버가 쿼리문에의해 읽혀진 행들을 사용자에게 전송한다.
- 전송에 이용하는 메모리양에 따라 사용자에게 쿼리 결과를 전송하기 위해 한번 또는 여러번 인출이 필요할 수 있다.
동일 SQL 문으로 인식하는 경우
- 대소문자 동일
- 공백(sapce)동일(8i 부터는 더블 공백도 하나의 공백으로 처리)
- Schema(=User)
- 변수
- / 로 재실행할 경우 parsing 은 안하지만 권한 검사는 한다. 동일 문장 실행시에도 같음
참고
쿼리의 시작과 끝
일관된 읽기 메커니즘의 기능
- 올바르고 일관된 해답을 얻을 수 있도록 해준다 : 이 과정은 암묵적으로 진행되며, 오라클에서 일관된 읽기를 얻을 수 있는 방법은 없다.
- 잠금 또는 잠기지 않고 올바르고 일관된 해답을 얻을 수 있도록 해 준다 : 다른 데이터베이스 시스템은 일관된 읽기를 허용하지만 동시성이 저하된다.
- 오라클과 달리 이들 데이터베이스에서는 동일한 데이터에 대한 동시 수정과 읽기를 차단하는 공유 읽기 잠금과 기타 메커니즘이 사용된다.
수정 DML의 시작과 끝
① Data Block & Rollback Block 잡는다
- 데이터베이스 버퍼 캐시에 데이터와 롤백 블록이 존재하지 않다면 서버 프로세스는 데이터 파일에서 읽어들여서 데이터베이스 버퍼 캐시에 블록의 사본을 위치 시킨다.
② Lock 을 건다
- 서버프로세스가 수정될 행에 lock 을 건다
③ Redo Log Buffer에 기록
- 서버 프로세스가 롤백 블록과 데이터에 가해진 변경 사항을 리두 로그 버퍼에 기록한다.
- 롤백 블록에는 데이터가 수정되기 전의 이미지 (Before Image)가 기록된다. 그래서 DML문장에 대해서는 필요한 경우에 롤백이 가능하다.
- 데이터 블록에는 데이터의 새로운 값이 기록된다.
④ Rollback Block 에 기록후 Data Block 에 쓴다.
- 서버프로세스는 데이터베이스 버퍼 캐시에서 이전 이미지를 롤백 블록에 기록한 후에 데이터 블록을 변경. 변경된 두 블록은 dirty buffer 즉, 디스크의 해당 블록과 같지 않은 버퍼로 표시된다.
- Delete 나 Insert시에도 유사한 과정을 거치며 Delete 의 경우 이전 이미지는 삭제된 행의 컬럼값을 포함하고 Insert의 경우 롤백에 저장된 행 위치 정보만을 포함한다.
DDL 처리
- DDL에 관하여 알아두어야 할 것은 문이 실행되기 직전과 COMMIT OR ROLLBACK직후에 묵시적인 COMMIT이 수행된다는 사실이다.
바인드 변수의 사용
바인드 변수를 사용하면 무엇이 좋은가?
- 바인드 변수를 사용하지 않으면 성능이 낮다.
- 바인드 변수를 사용하지 않으면 확장성이 낮다.
- 바인드 변수를 사용하지 않으면 코드 작성이 어렵다.
- 바인드 변수가 사용되지 않은 코드의 안전도는 낮다.
모든 규칙에는 예외가 있다
- 바인드 변수는 쿼리를 파싱할 때 애플리케이션이 사용하는 래치(잠금이라고 읽어도 좋다)의 수를 줄이기 위한 훌륭한 방법이다.
- 소프트 파싱은 하드파싱보다 CPU시간을 약간 적게 사용하며 바인드 변수는 소프트 파싱을 성취하기 위한 수단이다.
- SQL문에 바인드 변수를 사용하는 대신 문자열 리터럴을 사용하면 시스템이 SQL삽입에 노출된다.
- SQL문에 문자열 리터럴을 사용하면 사용자가 인용부호와 같은 예기치 못한 문자를 입력할 경우 실행되지 않게 될 수 있다.
- 사용중인 환경에 상관없이 바인드 변수를 사용하는 방안이 바인드 변수를 사용하지 않는것 보다 좀더 빠를 뿐만 아니라 쉽게 작성된다.
가능한 한 적게 파싱하기
- 가능한 한 암묵적이거나 명시적인 커서 형태로 정적 SQL을 사용한다.
- 같은 동적 문을 여러차례 걸쳐 반복적으로 수행해야 하는 경우에는 EXECUTE IMMEDIATE 보다는 DBMS_SQL을 사용한다.
- DBMS_SQL 대신에 배열을 사용하여, 즉 다수의 입력으로 하나의 동적문을 실행하는 방안을 고려한다. 이렇게 하면 실행시간 실행에 극적인 차이가 생기며 확장성이 엄청나게 제고된다.
- 왜냐하면, 필요한 리소스의 양이 감소하고 필요한 래치의 수가 눈에 띄게 줄어들기 때문이다.