꿈꾸는 개발자, DBA 커뮤니티 구루비

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 대신에 배열을 사용하여, 즉 다수의 입력으로 하나의 동적문을 실행하는 방안을 고려한다. 이렇게 하면 실행시간 실행에 극적인 차이가 생기며 확장성이 엄청나게 제고된다.
왜냐하면, 필요한 리소스의 양이 감소하고 필요한 래치의 수가 눈에 띄게 줄어들기 때문이다.