실행 구조

사용자는 데이터베이스 내에 있는 데이터를 조작하기 위해 데이터베이스에서 제공하는 인터페이 스를 사용한다. 대부분의 다중 사용자를 지원하는 데이터베이스는 클라이언트/서버 구조이며, 클라 이언트 부분에 사용자 인터페이스를 제공한다.

사용자는 데이터베이스를 이용할 수 있는 사용자 인터페이스에서 명령어를 입력하고 데이터베이 스에 결과를 요청하면 네트워크 서비스를 통하여 데이터베이스 인스턴스(엔진)에 전달된다. 전달된 명령어는 문법적인 오류나 의미적인 오류를 확인하고 옵티마이저(Optimizer)에 의해 SQL로 요구 된 결과를 최소의 비용으로 처리할 수 있는 최적의 처리 경로를 결정하여 실행 계획(Execution Plan)을 작성한다. 실행 계획에 의해 데이터베이스 엔진은 실행(Execution) 과정이 반복된다. 사용 자에게 전달될 데이터 결과가 있으면 네트워크 서비스를 통하여 정해진 버퍼 사이즈만큼씩 전달 (Fetch)한다.

옵티마이저

관계형 DBMS에서 사용되는 언어는 SQL이다. SQL 언어의 특징은 사용자가 데이터베이스에서 자신이 원하는 데이터(What)만 정의하고 그 데이터를 어떻게(How) 구하는가는 DBMS가 자동으로 결정해 처리해 준다. 어떻게 처리할 것인가를 결정하는 것이 옵티마이저이다.

여러 개의 테이블들을 조인할 때 조인 순서, 조인 방법, 테이블 액세스 방법을 선택하는 것을 실행 계획이라고 한다. 옵티마이저는 가능한 실행 계획들을 모두 검토하고 이중에서 가장 효과적인 것을 결정한다. 옵티마이저가 최적의 실행 계획을 찾는 과정을 최적화라 한다.

최적화 과정은 주어진 SQL 질의를 처리할 수 있는 모든 실행 계획을 다 고려할 수 없으므로 비용 을 산정한다. 비용 산정은 데이터베이스 내의 데이터들에 대해 갖고 있는 통계 정보와 비용을 예측하 는 모델을 이용하여 비용을 계산한다. 이를 비용 기준 최적화(CBO, Cost-Based Optimization)라 한다. 이때 실행 계획에 대한 것은 예상 비용이며, 실제 수행할 때의 비용과는 차이가 있다. 비용 산 정의 과정 없이 일정한 액세스 방법에 따라 정해진 우선순위로 실행 계획을 작성하는 것을 규칙 기준 최적화(RBO, Rule-Based Optimization)라 한다.

SQL 튜닝은 특정 SQL 질의의 수행 시간을 단축하는 것이다. 옵티마이저와 관련한 방법으로는 SQL 재작성, 힌트 사용, 새로운 인덱스 추가, 통계 데이터의 추가/갱신 등을 통해 옵티마이저가 더 욱 더 효율적인 실행 계획을 생성하도록 하는 것이다.

SQL 실행 단계

사용자의 SQL 질의는 크게 다음 4단계를 거쳐서 수행된다.

1. 1. 파싱(Parser)
2. 2. 옵티마이저(Query Optimizer)
3. 3. 로우 소스 생성(Row Source Generator)
4. 4. SQL 실행(SQL Execution Engine)

파싱(Parser) 단계

SQL의 구문(syntactic)과 의미(semantic)가 정확한지 검사하고, 참조된 테이블에 대해 사용자가 접근 권한을 가지고 있는지를 검사한다. 그리고 라이브러리 캐시에서 같은 SQL 문장이 존재하는 지 찾는다. 같은 SQL 문장 중에 같은 버전이 존재하면 기존 정보를 이용하여 실행하고, 존재하지 않으면 다음 단계를 진행한다.

옵티마이저(Query Optimizer) 단계

앞에서 넘겨받은 결과 정보(parsed query)를 이용해 최적의 실행 계획을 선택한다.

로우 소스 생성(Row Source Generator) 단계

옵티마이저에서 넘겨받은 실행 계획을 내부적으로 처리하는 자세한 방법을 생성하는 단계이다.
‘로우 소스’란 실행 계획을 실제로 구현하는 각 인터페이스를 지칭하는 말로, 테이블 액세스 방법, 조인 방법, 정렬(sorting) 등을 위한 다양한 로우 소스가 제공된다. 따라서 이 단계에서는 실행 계 획에 해당하는 트리 구조의 로우 소스들이 생성된다.

SQL 실행(SQL Execution Engine)

생성된 로우 소스를 SQL 수행 엔진에서 수행해 결과를 사용자에게 돌려주는 과정이다. 소프트 파싱(Soft Parsing)과 하드 파싱(Hard Parsing)은 크게 옵티마이저 단계의 포함 여부에 따른 차이이다. 즉, 소프트 파싱은 이미 최적화를 한 번 수행한 SQL 질의에 대해 옵티마이저 단계와 로우 소스 생성 단계를 생략하는 것이고, 하드 파싱은 이 두 단계를 새로 수행하는 것이다. 따라서 하 드 파싱은 통계 정보 접근과 실행 계획 탐색으로 인해 시간이 많이 걸린다.

명령어

데이터베이스와 사용자 간의 통신을 하기 위해 사용자는 DBMS에서 제공하는 명령어를 사용한다. 이들 명령어는 DBMS마다 문법 차이는 있지만 ANSI SQL92 Entry Level을 준수하고 있다. 명령 어에 대한 분류 방법도 차이가 있으나 데이터 정의 언어(DDL), 데이터 조작 언어(DML), 제어 명령 어(Control Statements)로 나뉜다.

데이터 정의 언어(DDL, Data Definition Language)

스키마 객체를 생성(CREATE)하고, 구조를 변경(ALTER)하고, 삭제(DROP), 명칭을 변경(Rename)하는 데 사용한다. 데이터베이스, 사용자, 테이블, 칼럼, 데이터 타입, 참조 무결성 제약 정의, 영역 무결성 제약 정의, 인덱스 등 모든 객체는 DDL에 의해서 관리된다.

DDL 실행은 현재 진행되는 트랜잭션에 대해 암시적으로 COMMIT을 실행하므로 트랜잭션 명령 어에서 제어할 수 없다. 생성, 변경, 삭제 작업은 실행 후 취소가 불가능하므로 사용상 주의가 필요 하다. Truncate 명령어는 데이터를 삭제하는 delete와 유사하지만 Truncate는 DDL이고 delete는 DML이므로 내부적으로 수행 구조가 다르다. 그 외 사용자 특권(Privilege)과 역할(Role)을 허가 (Grant)하고 취소(Revoke, Deny)하거나 데이터 사전에 코멘트를 추가하고 오디팅(Auditing) 옵션 을 설정하는 등의 명령어가 있다.

데이터 조작 언어(DML, Data Manipulation Language)

DML은 데이터베이스에 있는 데이터를 조작할 수 있게 해주는 명령어로 DELETE, INSERT, SELECT, UPDATE 등이 대표적인 ANSI 표준 DML 명령어이다. 그 외 MERGE, LOCK TABLE, EXPLAIN PLAN 등이 있다. DML은 현재 트랜잭션을 암시적으로 처리 하지 않으므로 별도의 트랜 잭션 관리 명령어와 같이 수행한다.

DML문 처리 단계는 다음과 같다.

1단계 커서 생성

커서는 SQL문에 대해 독립적으로 생성된다. 커서는 모든 SQL문에 사용될 수 있도록 생성된다. 대부분의 애플리케이션에서 커서는 자동으로 생성되지만 선행 컴파일러 프로그램에서는 커서 생 성이 암시적으로 발생할 수도 있고 명시적으로 선언될 수도 있다.

2단계 명령문 구문 분석

SQL문을 변화하여 유효한 명령문인지 검증

• 데이터 딕셔너리를 탐색해 테이블과 열 정의 검사
• 구문 분석 잠금을 획득하여 객체 정의가 변경되지 않도록 함
• 참조한 스키마 객체에 액세스 권한 검사
• 명령문에 대한 최적의 실행 계획을 결정
• 공유 SQL 영역으로 로드
• 분산 명령문의 경우 명령문 모두 또는 일부를 원격 노드로 라우트

3단계 질의 결과 설명(SELECT일 때)

데이터 유형, 길이, 이름 등 질의 결과의 특성을 판별한다.

4단계 질의 결과 출력 정의(SELECT일 때)

질의에 대한 정의 단계에서 위치, 크기, 인출한 각 값을 받기 위해 정의된 변수의 데이터 유형을 지정한다.

5단계 변수 바인드

값을 찾을 수 있는 메모리 주소를 지정한다.

6단계 명령문 병렬화(병렬 처리일 때)

병렬화는 다중 서버 프로세스로 하여금 동시에 SQL문의 작업을 수행하도록 하므로 작업이 더 신속하게 완료될 수 있다.

7단계 명령문 실행

SELECT나 INSERT문이면 데이터의 내용이 변경되지 않으므로 어떠한 행에 대해서도 잠금이 필요하지 않다. UPDATE, DELETE문에 영향을 받는 모든 행은 트랜잭션에 대한 다음 처리까지 잠 겨있어 데이터베이스의 다른 사용자는 사용할 수 없다.

8단계 질의 로우 인출(SELECT일 때)

인출 단계에서 행이 선택되고 정렬된다. 연속적인 인출(Fetch)을 통해 마지막 행을 인출할 때까지 다른 결과 행이 읽혀진다.

9단계 커서 닫기

제어 명령어(Control Statement)

제어 명령어는 DBMS마다 사용자에게 제공하는 형태에 차이가 있다. 단순한 명령어 형태로 제공되 기도 하고 저장 프로시저 형태로 제공되기도 한다. 오라클과 MS-SQL을 비교하면 아래 표와 같다.

[표 5-2-2] 제어 명령어

구분	Oracle	MS-SQL
Transaction Control	COMMIT [work]	COMMIT [work|transaction]
	ROLLBACK	ROLLBACK [work|transaction]
	SAVEPOINT	SAVE TRAN[SACTION]
	Set transaction	SET
Session control	Alter session	SET
	Set role	Sp_setapprole
	Alter system	Sp_configure
	Alter database	Sp_dboption

트랜잭션 제어문은 1개 이상의 SQL문을 논리적으로 하나의 처리 단위로 적용하기 위해 사용하는 명령어이다. 트랜잭션의 처음과 종료는 명시적으로 COMMIT되거나 ROLLBACK을 처리한다.

세션 제어문은 실행하고 저장하는 SQL문에는 사용할 수 없으며 사용자 세션의 특성을 정의하는 명 령어이다. 개별적인 사용자 세션에 따라 환경 변수를 변경할 수 있다. 예를 들어, 대량의 배치 작업을 하기 위해 메모리 사이즈를 변경하거나 NLS 환경 변수를 클라이언트 프로그램과 동일하게 조정하는 등의 작업을 지원한다.

시스템 제어문은 시스템이나 데이터베이스 레벨에서 재기동(Restart) 없이 환경 변수 등을 조정할 수 있다. 데이터베이스 전체적으로 NLS 환경 변수를 변경하는 작업 등이 가능하다.

저장 프로시저

데이터 조작 언어는 비절차적 언어이다. 대부분의 DBMS에서는 이를 보완하기 위해 절차적 언어 를 제공한다. 오라클은 PL/SQL로, MS-SQL은 Transaction-SQL로 지원한다. 절차적 언어를 이 용하여 저장 프로시저를 생성할 수 있다.

저장 프로시저 설계 지침

높은 응집도와 낮은 결합도를 유지한 설계가 필요하다. 하나의 작업을 중점적으로 완료하도록 프 로시저를 정의한다. 여러 프로시저 코드에서 불필요하게 중복될 수 있는 공통적인 하위 작업이 있을 수 있으므로 여러 개의 서로 다른 하위 작업을 갖는 긴 프로시저는 정의하지 않는다. DBMS에서 제공되는 기능과 중복되는 프로시저는 정의하지 않는다. 선언적 무결성 제약 조건을 사용하여 수행할 수 있는 간단한 데이터 무결성 규칙을 프로시저로 정의하지 않는다.

저장 프로시저의 장점

보안

데이터 보안을 강제로 수행한다. 사용자는 작성자의 권한으로 실행되는 프로시저와 함수를 통해서 만 데이터에 액세스하도록 데이터베이스 작업을 제한할 수 있다. 프로시저를 실행하는 권한만 있 는 사용자는 프로시저를 호출할 수는 있지만 테이블 데이터를 조작할 수는 없다.

성능

각각의 SQL문 실행과 비교할 때 네트워크를 통해 보내야 하는 정보의 양을 현격하게 줄인다. 한 번 정보를 보낸 후에는 사용될 때마다 호출되기 때문이다. 데이터베이스에서 프로시저를 컴파일한 상태로 사용되므로 실행 시 별도 컴파일이 필요 없고 공유 풀을 이용하여 재사용된다.

메모리 할당

많은 사용자의 실행을 위해 프로시저의 단일 복사본만이 메모리에 로드된다. 동일한 코드를 공유 하면 애플리케이션에 의한 메모리 요구를 줄인다.

생산성

개발 생산성을 증가시킨다. 프로시저 집합으로 애플리케이션을 설계하여 불필요한 코딩을 피하고 생산성을 증가시킨다. 작업 수행에 필요한 SQL문을 재작성하지 않고도 모든 애플리케이션에 의 해 호출될 수 있다. 데이터 관리 방법이 변경되면 사용하는 애플리케이션이 아닌 프로시저만 수정 하면 된다.

무결성

애플리케이션의 무결성과 일관성을 향상시킨다. 검증된 프로시저는 다시 테스트하지 않고 많은 애 플리케이션에서 재사용할 수 있다. 프로시저가 참조하는 데이터 구조가 변경되었다면 프로시저만 재컴파일하면 된다. 프로시저를 호출하는 애플리케이션은 수정하지 않아도 된다.

트리거(Trigger)

DBMS에서 INSERT, UPDATE, DELETE문을 관련 테이블에 대해 실행하거나 데이터베이스 시 스템 작업이 발생하면 암시적으로 실행되는 트리거를 정의할 수 있다. 저장 프로시저와 트리거는 호 출하는 방법이 다르다. 프로시저는 사용자, 애플리케이션 또는 트리거에 의해 명시적으로 실행된다. 반면에 하나 이상의 트리거는 접속된 사용자나 사용되는 애플리케이션에 관계없이 트리거 이벤트가 발생되면 DBMS에 의해 암시적으로 실행된다.

트리거 사용

과다한 트리거 사용은 복잡한 내부 종속성을 초래하여 대규모 애플리케이션에서 유지 관리를 어렵 게 하므로 주의가 필요하다. 트리거는 DML 작업이 정규 업무 시간 동안 실행되도록 테이블에 대해 DML 작업을 제한할 수 있다. 트리거는 다음과 같이 사용한다.

• 자동적으로 파생된 열 값 생성(예 : 합계, 잔액, 재고량 등)
• 잘못된 트랜잭션 방지(예 : 무결성 제약 구현)
• 복잡한 보안 권한 강제 수행
• 분산 데이터베이스의 노드상에서 참조 무결성 강제 수행
• 복잡한 업무 규칙 강제 수행
• 이벤트 로깅 작업이나 감사 작업
• 동기 테이블 복제 작업
• 테이블 액세스에 대한 통계 수집

트리거 유형

행 트리거 및 명령문 트리거

행 트리거는 테이블이 트리거링 명령문에 의해 영향을 받을 때마다 실행된다. 예를 들어 UPDATE 문이 테이블의 여러 행을 갱신하면 각 행에 대해 한 번씩 실행된다. 명령문 트리거는 테이블에서 트 리거링 명령문에 의해 영향을 받는 행 수에 관계없이 한 번 실행한다. 예를 들어, DELETE문이 여 러 행을 삭제하면 테이블에서 삭제되는 행 수에 관계없이 한 번만 실행된다. 보안 감사나 감사 레코 드를 만들 때 사용된다.

BEFORE 및 AFTER 트리거

BEFORE 트리거는 명령문이 실행되기 전에 트리거 작업을 실행한다.

• 불필요한 rollback을 제거하기 위해 트리거 작업이 실행 여부를 결정할 때 사용된다.
• 트리거링 INSERT 또는 UPDATE문을 완료하기 전에 특정 열 값을 구하기 위해 사용된다.

AFTER 트리거는 명령문이 실행된 후에 트리거 작업을 실행한다.

트리거링 이벤트와 제한 조건

트리거링 이벤트는 특정 테이블에 대한 INSERT, UPDATE, DELETE문이 실행될 때이다. 모든 DBMS에서 지원하는 사항은 아니지만 그 외에 시스템 차원에서 이벤트를 발생할 수 있다. 트리거 제한 사항은 트리거 실행을 위해 TRUE여야 하는 논리적 표현식을 지정한다.