데이터모델링-데이타베이스 설계와 이용

카테고리 없음

데이터모델링-데이타베이스 설계와 이용

write8770 2024. 8. 11. 19:07

과목 6 데이타베이스 설계와 이용

제1장 데이터베이스 설계

제1절 저장 공간 설계

1.테이블

가.테이블

- Heap-Oraginzed Table

데이터 값의 순서에 관계 없이 데이터를 저장하는 일반 데이블

- Index Oraginzed Table 또는 Clustered Index Table

키 값의 순서대로 저장(기본키 또는 인덱스 순서)

- Patition Table

범위(Range), 해쉬 값(Hash), 목록(List)

대용량 데이터베이스 환경에서는 반드시 고려해야 한다

- External Table

파이 데이터를 데이블 형태로 이용할 수 있는 테이블

데이타웨어하우스(DW, Data Werehouse)에서

ETL(Extraction, Transformation, Loading)작업 등에 유용한 데이블이다

- Temporary Table

트렌잭션이나 세션별로 데이처를 저장하고 처리 할 수 있는 데이블

다른 세션에서 처리되는 데이터는 공유할 수 없다

나. 칼럼

- 데이블을 구성하는 요소로 데이터 타입, 데이터 길이로 정의된다

- 칼럼이 서로 참조 관계일 경우는 반드시 동일한 데이터 타입과 길이를 지정해야

한다 만약 이를 지키지 못하면 조인(Join) 연산시 정상적인 실행 계획을 예측

하기 곤란하다.

또는 내부 연산을 통해 Join 연산이 일어나 인덱스가 있어도 사용하지 못할수

있다

* 물리적인 칼람 순서 조정

- 고정 길이 칼럼이고 NOT NULL 인 칼럼은 선두

- 가변 길이 칼럼을 뒤편

- NULL 값이 많을 것으로 예상되는 칼럼을 뒤편에

-> 값이 변경될때 체인(Chain)발생을 억제하고 저장 공간을 효율적으로 사용하게됨

* 데이터 타입과 길이 지정시 다음과 같은 사항 고려

- 가변 길이 데이터 타입은 예상되는 최대길이로 정의

- 고정 길이 데이터 타입은 최소의 길이를 지정

- 소수점 이하 자리 수의 정의는 반올림되어 저장되므로 정확성을 확인하고 정의

다. 데이블 설계시 고려사항

- 칼럼 데이터 길이의 합이 1Block또는 페이지 사이즈 보다 큰 겨우는 수직 분할을

고려한다 (Chain 발생으로 속도 저하됨)

- 칼럼 길이가 길고 특정 칼럼의 사용 빈도 차이가 심한 경우이거나 다른 사용자

그룹이 특정 칼럼만을 사용하고 같이 처리되는 경우가 드문 경우는 수직 분할을 고려

- 수직 분할 고려시에는 분할되는 테이블이 하나의 트랜책션에 의해서 동시처리

되는 경우나 조인이 빈번히 발생되는 경우는 없어야 한다

- 주무일, 배송 일시, 계약일 등의 칼럼은 데이타 타입 사용을 자제한다

2. 데이블과 데이블 스페이스(T.S)

- 데이블은 T.S라는 논리적인 단위를 이용하여 관리되고 T.S는 물리적인

데이터 파일을 지정하여 저장된다

- 저장되는 내용에 따라 데이블용, 인덱스용, 임시용으로 구분하여 설계한다

- 테이블스페이스는 데이타 용량을 관리하는 단위로 사용한다

* 테이블용/인덱스용 T.S설계 유형들

- 테이블이 저장되는 T.S는 업무별로 지정한다

- 대용량 테이블은 독립적인 T.S를 지정한다

- 데이블과 인덱스는 분리하여 저장한다

- LOB 타입 데이터는 독립적인 공간을 지정한다

3. 용량 설계(목적)

- 저장공간의 효과적인 사용과 확장성을 보장하여 가용성을 높이기 위해

- H/W 특성을 고려하여 디스크 채널 병목을 최소화 하기위해

- 데이블이나 인덱스에 맞는 저장 옵션을 지정하기 위해

* 데이블 저장 옵션에 대한 고려사항

- 초기 사이즈, 중가 사이즈

- 트랜잭션 관련 옵션

- 최대 사이즈와 자동 증가

*저장 용량 설계 절차

- 용량분석 : 데이터 증가 예상건수, 주기, 로우길이등을 고려

- Object 별 용량산정 : 데이블, 인덱스에 대한 크기

- T.S별 용량 산정 : T.S별 Object 용량의 합

- 디스크 용량 산정 : T.S에 따른 디스크 용량과 I/O 분산설계

제2절 무결성 설계

1. 데이터 무결성

- 데이터의 정확성, 일관성, 유효성, 신뢰성을 위해 무효 갱신으로부터 데이터를 보호하기

위해서 무결성 설계가 필요하다

- 프로그램과 데이터베이스 기능을 이용하여 처리할 수 있음

가. 데이터 무결성

- 실체 무결성: 모든 실체는 식별자를 정의하고 그 식별자 값은 NULL이 아닌 유일한

값이어야 한다

- 영역 무결성: 칼럼 데이터의 타입, 길이, 유효 값이 일관되게 유지 되어야 한다.

- 참조 무결성: 데이터 모델에서 정의된 실체 간의 관계 조건을 유지하는 것

- 사용자 무결성 : 비즈니스 규칙이 데이터적으로 일관성을 유지 하는것

나. 데이터 무결성 강화 방법

1) 응용 프로그램 코드

- 데이터를 조작하는 프로그램 내에 데이터 생성,수정,삭제시 무결성 조건을

검증하는 코드를 추가함

- 장점 : 사용자 정의 등 복잡한 무결성 조건을 구현함

단점 : 관리의 어려움,개별적 실행으로 적정성 검토에 어려움

2) 데이터베이스트리거

- 트리거 이벤트시 저장 SQL을 실행하여 무결성 조건을 실행

- 장점 : 통합 관리가 가능, 복잡한 요건 구현 가능

단점 : 운영 중 변경이 어려움, 사용상 주의가 필요함

3) 제약 조건

- 데이터베이스 제약 조건 기능을 선언하여 무결성을 유지함

- 장점 : 통합관리 가능, 간단한 선언으로 구현

변경이 용이하고 유효/무료 변경이 가능, 원천적으로 잘못된 데이타 방지

단점 : 복잡한 제약조건 구현 불가, 예외처리 불가

2. 실체 무결성

- 기본키 제약조건과 UNIQUE 제약조건을 이용하는 것이 바람직

가. 기본키 제약

- 가장 중요한 무결성 조건

- 식별자 값이 NOT NULL이면서 UNIQUE해야 한다.

나. UNIQUE 제약

- 기본키 제약과 같으나 NULL 이 존재 가능함

다. 식별자 설계 - 채번

- 대량의 트랜잭션 처리를 위해서는 식별자를 시퀀스나 오브젝트 시리얼을 이용

3. 영역 무결성

- 칼럼에 적용되어 단일 로우의 컬럼 값만으로 만족 여부를 판단할수 있다

- 프로그램 소스와 제약 조건을 상호 보완적으로 사용하는 것이 효과적

가. 데이터 타입과 길이

- 조회조건이나 비교연산에 사용하는 것은 문자 타입이 유리

나. 유효 값(CHECK)

다. NOT NULL

- 금액 계산시 NULL 값이 존재하면 연산이 불가능하여 예외사항이 발생

이를 위해 숫자 타입은 NOT NULL 제약조건을 부여하고 기본값을 0으로 한다

4. 참조 무결성

- 키 운영 규칙에 관한것

- 키본키나 외래키 값이 입력, 삭제 및 수정될 때 데이터의 정합성을 유지

하기 위한 방법

- 프로그램, 데이타베이스, 외래키 제약을 사용함

- 외래키 제약은 인덱스를 생성한다

- 외래키 제역을 편하가 확실하지만 성능상에 저하가 발생할수 있다

가. 입력 참조 무결성

- DEPENDENT : 참조되는(부모) 테이블에 기본키 값이 존재할 때만 입력 허용

- AUTOMATIC : 참조되는(부모) 데이블에 기본키 값이 없는 경우 생성 후 입력

- DEFAULT : 참조되는(부모) 데이블에 기본키 값이 없으면 기본값으로 입력

- CUSTOMIZED: 특정 조건이 만족할 때만 입력 허용

- NULL : 참조되는(부모) 테이블에 기본키 값이 없는 경우 왜래키를 NULL로 처리

- NO EFFECT : 조건 없이 입력 허용

- 응용프로그램에서 구현 반드시 참조 무결성이 유지되어야 하는 경우는 FK 제약 사용

나. 수정 삭제 참조 무결성

- RESTRICT : 참조하는(자식) 테이블에 기본 값이 없는 경우 삭제/수정 허용

- CASCADE : 참조하는(자식) 테이블과 참조하는 데이블의 외래키를 연쇄적으로 삭제/수정

- DEFAULT : 참조되는(부모) 테이블의 수정을 항상 허용하고 참조하는(자식)

테이블의 외래키를 지정된 기본값으로 변경

- CUSTOMIZED: 특정한 조건이 만족 할 때만 수정/삭제 허용

- NULL : 참조되는(부모) 테이블의 수정을 항상 허용하고 참조하는(자식)

테이블의 외래키를 NULL값으로 수정

- NO EFFECT : 조건없이 삭제/수용 허용

- 수정 무결성은 부모 식별자가 변경되었을 경우이다

- 삭제 참조 무결성은 제한(RESTRICT) 기능을 DBMS가 외래키 제약을 이용한다.

다. DEFAULT 규칙 정의의 필요성

- 참조 무결성 NULL값을 정의하는 것은 바람직하지 않다

라. 모델상에서 슈퍼 타입(Suber-type) - 서버타입(Sub-type) 관계

- 삽입시에는 DEPENDENT, AUTOMATIC 조건을 적용

- 삭제 변경시에는 CASCADE 조건을 적용

제3절 인덱스 설계

1.인덱스 기능

- 데이터 접근 경로를 단축시키는 기능을 한다

2.인덱스 절차

가.접근경로 수집

- 반복 수행되는 접근 경로 : 대표적인 것이 조인 컬럼

- 분포도가 양호한 칼럼

- 조회 조건에 사용된는 칼럼

- 자주 결합되어 사용되는 칼럼

- 데이터 정열 순서와 그룹핑 칼럼

- 일련번호를 부여한 칼럼

- 통계 자료 추출 조건

- 조회 조건이나 조인 조건 연산자

나.부포도 조사에 의한 후보 칼럼 선정

- 분포도(%) = 데이터별 평균 로우수 / 데이블의 총 로우수 X 10

- 분포도가 10 ~ 15 정도이면 인덱스 칼럼 후보로 사용

- 분포도 조사를 단일(복수) 칼럼을 대상으로 조사

- 분포도 조사 결과를 만족하는 칼럼을 인덱스 후보로 선정

인덱스 후보는 최소 조합으로 선별해야 한다

- 분포도가 불규칙한 것은 별도 표시하여 접근 형태에 따라 대책 마련

- 빈번히 변경이 발생하는 칼럼은 인덱스 후보에서 제외

다.접근경로 결정

라.칼럼 조합 및 순서 결정 (순차적으로 중요)

- 항상 사용되는 칼럼을 선두 칼럼에 둔다

- 등치 조건으로 사용되는 칼럼을 선행 칼럼으로 한다

- 분포도가 좋은 칼럼을 선행 칼럼으로 한다

- ORDER BY, GROUP BY 순서를 적용한다

마. 적용시험

3. 인덱스 구조

- B-Tree 인데스는 OLTP환경에서 사용

- 비트맵은 데이터웨어하우스 등에서 B-Tree의 단점을 보완한 인덱스

B-Tree Bitmap

========================================================================

구조 특징 : Branched Block 균형유지 전체 row의 인덱스 칼럼 값을 0/1로 저장

사용 환경 : OLTP DW, Data Mart

검색 속도 : 소량의 데이터를 검색 대량의 데이터를 검색

분 포 도 : 데이터 분포도가 높음 데이터 분포도가 낮음

장 점 : 입력,수정,삭제가 용이 OR 연산, NULL 값 비교 가능

단 점 : 스캔 범위가 넓을때 전체 인덱스 부하로 입력,

렌덤 I/O 발생 수정,삭제가 어려움

제4절 분산 설계

1. 분산 데이터베이스 개요

- 논리적으로 연관된 데이터베이스의 집합체가 물리적으로 분산되어 운영되고 사용자들은

하나의 데이터베이스처럼 놀리적으로 통하되어 공유되는 데이터 베이스를 뜻함

- 장점 : 자신의 데이터를 지역적으로 제어하여 원격 데이터에 대한 의존도를 감소시키고

단일 서버에서 불가능한 대용량 처리가 가능하며 기존 시스템에 서버를 추가하여

점진적 증가가 용이하다

단점 : 분산 처리에 의해 복잡도가 증가하여 소프트웨어 개발 비용이 증가하고

통제 기능이 취야하고 분산 처리에 따른 오류 발생 가능성이 내제된다

2. 분산 데이터베이스 설계

- 여러 개의 물리적인 데이터베이스를 논리적으로 단일 데이터베이스 사용 환경을

제공 하는 것이다

- 분할 투명성 : 데이블은 수직, 수평 분할하여 처리

- 위치 투명성 : 다른 위치 다른 저장소에 저장

- 중복 투명성 : 데이터 일관성 유지는 시스템 차원에서 해결

- 장해 투명성 : 구성 요소 장애에 무관한 트랜잭션의 원자성을 유지해야 한다

- 병행 투명성 : 다수 트랜잭션의 원자성을 유지하기 위해서 2PC을 이용한다

3.분산 설계 방식

가. 테이블 위치 분산

- 테이블 구조 변경 없이 데이블을 서버별로 분산시키는 것

- 테이블 마다 존재할 서버를 선정

나. 테이블 분할 분산

- 데이블 데이터를 분할 하여 분산하는 것

- 완전성 : 테이블의 모든 데이터를 대상으로 분할

- 재구성 : 분할된 것은 관계 연산을 사용하여 원래의 전역 실체로 재구성이 가능해야 함

- 상호 중첩 배제

수평 분할은 모든 데이터가, 수직분할은 식별자를 제외한 데이터가 중복되지 않아야 한다

1) 수평분할

- 특정 칼럼 값을 기준으로 row를 분리하는 것

- 분산된 테이블을 통합해도 식별자가 중복되지 않게 된다.

2) 수직분할

- 테이블 칼럼을 기준으로 분할 한다

다.데이터 복제 분산

- 동일한 테이블을 복수 서버에 생성하는 분산

데이터 복제는 실시간 처리의 필요성이 없는 경우 야간 일괄 복제 방식을 선택한다

- 부분복제 / 광역복제

4.데이터 통합

- 분산 아키텍쳐의 단점을 보완하고 정보의 직시성과 실시간 데이터 교환이라는

목적으로 통합 아키텍쳐를 구축한다

- DW, EAI를 이용

제5절 보안 설계

- DB 정보가 비 인가된자에 의해서 노출, 변조, 파괴 되는 것을 막는 것

- 지원에 접근하는 사용자 식별 : 사용자, 비밀번호, 사용자 그룹

- 보안 규칙 또는 권한 규칙에 대한 정의

- 사용자에 접근 요청에 대한 보안 규칙 검사 구현 : 보안 관리 시스템 구현

1. 사용자 식별 및 인증

2. 접근 통제

- 사용자 접근 통제

신분, 역할, 위치, 시간, 서비스 제한

- 사용자 통제

패스워드, 암호화, 접근 통제 목록 적용, 제한된 사용자 인터페이스, 보안등급

3. 감사추적

- 응용 프로그램 및 사용자 활동에 대한 일련의 기록을 실시

- 감사추적의 내용은 사용자 실행 프로그램, 사용 클라이언트, 사용자, 날짜 및 시간

접근 한 데이터, 이전 값 및 이후값을 저장함

4.데이터 접근 통제 모델

- 주체, 객체, 규칙을 정의하고 그들 간의 관계를 정의하여 접근을 통제함

5. 데이터 접근 제어 유형

- 값 비종속 규칙

보안의 대상을 객체로 보고 객체에 대한 인스턴스는 고려하지 않는 방법이다

- 값 종속 규칙

사원평가 데이블에 대한 접근은 사용자의 소속 부서 사원의 데이터만 접근을

허용하는 것과 같다

- 의미 종속 규칙

업무 시간대인 월요일에서 금요일 까지 시간데에만 데이터 접근 허용

6. 데이터 접근 제어 기법

- 임의 통제

사용자는 각 데이터 객체에 대해 서로 다른 권리들을 갖고 각 사용자의

개인적인 판단에 따라 권한을 이전

수평전파 : 1개 사용자에게 권한을 이전

수직전파 : 권한 부여의 깊이를 제한하지 않는 것

- 강제 통제

각 데이터 객체에 보안 분류 등급이 부여되고 각 사용자마다 인가 등급을

부여하여 통제하는 것

읽기 : 사용자 등급이 접근하는 객체의 등급과 같거나 높은 경우

수정/등록 : 사용자와 객체의 등급이 같은 경우

제2장 데이터베이스 응용

제1절 데이터베이스 관리 시스템(DBMS)

1. 개념적 데이터베이스 관리 시스템 아키텍쳐

- DBMS 서버는 인스턴스와 데이터베이스로 구성된다

- 인스턴스는 메모리 부분과 프로세스 부문으로 구성

그외 DB의 기동과 종료를 위하여 DBMS 환경 매개변수 파일과

파일 목록(데이터파일, 로그파일)을 기록하는 제어파일이 있다

2. DB 서버의 시작과 종료

- DB 사용을 위해서는 DB관리자가 DBMS 매개변수 파일을 읽어 인스턴스를 시작 한다

- 매개변수파일 : 인스턴스와 DB에 대한 구성 매개변수의 목록이 있는 TEXT 파일

인스턴스 구성 매개변수를 특정값으로 설정하여 인스턴스의

메모리와 프로세스를 초기화 한다

가. 데이터베이스 서버 시작

1) 인스턴스 시작

- 매개변수 파일 읽기 -> 초기화 메모리변수값을 결정하고 데이터베이스

정보를 위해 사용되는 메모리 공유 영역을 할당 -> 백그라운드 프로세스 생성

2) 데이터베이스 마운트

- 인스턴스가 DB를 마운트 하고 제어파일과 데이터 파일을 읽어 들인다.

- DB는 여전히 닫힌 상태로 관리자만 접근가능

3) 데이터베이스 열기

- 일반 DB 사용가능

- 자동복구작업

- 미확정 분산 트랜잭션 해결

- 읽기 전용 모드에서 보수 작업

나. 데이터베이스 서버 종료

1) 데이터베이스 닫기

- 데이터베이스를 닫으면 메모리에 있는 데이터베이스 데이터와 로그를 데이터 파일과

리두 로그 파일에 각각 기록 -> 온라인 데이터 파일과 로그 파일 닫기

- 제어 파일을 열려 있는 상태임

2) 마운트 해제

- 제어 파일 닫기

3) 인스턴스 종료

- 인스턴스를 할당된 메모리에서 제거 -> 백그라운트 프로세스 종료

3. 데이터 베이스 구조

가. 데이터 사전

- 데이터베이스의 정보를 제공하는 읽기 전용 테이블 또는 VIEW 집합

- 데이터베이스의 모든 스키마 객체 정보

- 스키마 객체에 대해 할당된 영역의 상이즈와 현재 사용 중인 영역의 사이즈

- 열에 대한 기본값

- 무결성 제약 조건에 대한 정보

- 사용자 이름, 사용자에게 부여된 권한과 규칙

- 기타 일반적인 데이터베이스 정보

- 동적 성능 테이블 : 세션, 잠금, SQL pool 등 다양한 정보를 제공함

나. 데이터베이스, 데이블스페이스 데이터 파일

다. 데이터 블록, 확장 영역 및 세그멘트 간의 관계

- 데이터베이스 영역의 할당 단위

1) 데이터 블록

- DBMS가 데이터를 읽는 가장 작은 단위

- 1회 물리저인 디스크 I/O량을 결정함

- Header : 블록 주소와 세그멘트유형(데이터, 인덱스) 정보..

Row directory : 행 데이터 영역에 있는 각 행 조각의 주소를 포함하여 블록에

있는 실제행에 대한 정보

Free space : 새로운 행을 삽입하거나 후행 널을 널이 아닌 값으로 갱신할때와

같이 추가 영역이 필요한 행을 갱신할때 할당

Row Data : 실제적인 데이터가 저장된 공간

2) 데이터 확장 영역

- 특정 유형의 정보를 저장히기 위해 할당된 몇개의 연속적인 데이터 블록

- 생성된 오브잭트를 Drop하거나 Truncate해야 데이블스페이스로 반환된다

3) 세그먼트

- 특정 논리적 저장 영역 구조에 대한 모든 데이터를 포함하는 확정 영역 집합

4. 메모리 구조

1) DBMS 정보 저장

- 실행된는 프로그램 코드

- 현재 사용하지 않더라도 접속되어 있는 세션 정보

- 프로그램 실행 동안 필요한 정보

- 프로세스 간에 공유하거나 교환 정보

- 보조 메모리에 영구적으로 저장된 캐시 데이터

- DBMS는 소프트웨어 코드 영역, 시스템 메모리 영역, 프로그램 영역으로 구분

시스템 메모리 영역 = 데이터베이스 버퍼 + 로그 버퍼

2) 데이터베이스 버퍼

- 인스턴스에 동시에 접속한 모든 사용자 프로세스는 데이터베이스 버퍼에 대한

액세스를 공유한다

- 더티(Dirty)목록과 LRU(Least Recently Used)목록을 가지고 있다.

- 더티목록은 더티버퍼(수정되었지만 아직 디스크에 기록되니 않은 버퍼)를 갖고 있음

- LRU는 빈버퍼, 현재 엑서스 중인 고정된 버퍼, 더티목록을 갖고 있음

3) 로그 버퍼

- DB의 변경 사항 정보를 유지하는 것으로 원형 버퍼임

- 서버 프로세스에 의해 로그파일에 작성된다

4) 공유 풀

- 라이브러리 캐시, 딕셔너리 개시, 저어 구조 등으로 구성됨

- 라이브러리 캐시 : SQL 영역, 저장 프로시저 영역, 제어 구조 등을 공유함

- 딕셔널리 캐시 : 데이터 딕셔널리 정보를 공유한다

- 공유 풀 :

5) 정렬 영역

- 정렬되어야 한는 데이터의 양이 메모리 영역을 초과할 때는 데이터를 작은 부분으로

나눈 후 각 부분을 개별적으로 정렬하고 개별적으로 정렬된 결과는 병합하여

최종 결과를 생성한다

5. 프로세스 구조

가. 사용자 프로세스

- 응용 프로그램이나 DB도구를 실행 할 때 생성된다

나. 서버 프로세스

- 사용자 프로세스와 통신을 하는 역할

- 다중 쓰레드 방식과 단일 쓰레드 방식이 있음

- 다중 쓰레드 방식은 단일 서버 프로세스를 여러 사용자 세션간에 공유

단일 쓰레드 방식은 각 사용자에 세션에 대해 서버 프로세스를 생성한다

다. 백그라운드 프로세스

- Process Monitory(PMON)

- System Monitory (SMON)

- Database Writes (DBWn)

- checkpoint(CKPT)

- Log Writer(LGWR)

제2절 데이터 액세스

1. 실행구조

- 사용자 요청(SQL) -> 네트웍크 서비스 -> DB 인스턴스 (엔진) -> 문법 오류, 확인

-> Optimizer (실행계획) 선정 -> 실행계획에 따라 DB엔진은 실행 과정 반복

-> 사용자에게 전달될 DATA 있는 경우 -> 네트워크 서비스 -> Buffer 만큼 전달

가. 옵티마이져

- SQL은 사용자가 DB에서 자신이 원하는 데이터(What)만 정의 하고 그 데이터를

어떻게(How) 구하는가는 DBMS가 자동으로 결정해서 처리함

- 비용 기준 최적화 :

규칙 기준 최적화 : 일정한 엑서느 방법에 따라 정해진 우선 순위로 실행 계획을 작성

나. SQL 실행단계

1) 파싱

- SQL은 구문과 의미 검사 / 참조된 테이블의 접근 권하여부 확인.

- 파싱트리 형태로 변형되어 옵티마이저에게 넘겨짐

2) 옵티마이저 단계

- 파싱 트리를 이용해서 최적의 실행 경로를 고른다

3) 로우 소스 생성 단계

- 옵티마이저에게 넘겨받은 실행 계획을 내부적으로 처리하는 자세한 방법을 생성

- 테이블 엑세스 방법, 조인 방법, 정렬 등을 위한 다양한 로우소스가 제공됨

4) SQL 실행

- 생성된 로우 소스를 SQL 수행 엔진에서 수행해서 결과를 돌려줌

- 소프트 파싱 : 최적화를 한번 수해한 SQL 질의에 대해 옵치마이저 단계와

로우소스 생성 단계를 생략 하는것

- 하드 파싱 : 전체 단계를 수행

2.명령어

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

- 스키마 오브젝트를 생성, 구조 변경, 삭제, 명칭 변경하는데 사용

- DDL 실행은 현재 진행되는 트랜젝션에 대해서 암시적으로 COMMIT을 실행

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

* DML 처리 단계

- 1단계 커서 생성

- 2단계 명령문 구문 부석

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

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

- 5단계 변수 바인드

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

- 7단계 명령문 실행

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

- 9단계 커서 닫기

다. 제어 명령문(Control Statement)

- 트렌젝션 제어문 : 1개 이상의 SQL 문장을 논리적으로 하나의 처리 단위로

적용하기 위해서 사용하는 명령어 이다.(COMMIT, ROLLBACK)

- 세션 제어문 : 실행하고 저장 프로시저 문에는 사용할 수 없으며 사용자

세션의 특성을 정의하는 명령어이다

- 시스템 제어문 : 시스템이나 DB 레벨에서 재기동 없이 환경 변수 등을

조절할 수 있다.

3. 저장프로시저

가. 저장 프로시저 설계 지침

- 높은 응집도와 낮은 결함도를 유지한 설계가 필요

- DBMS에서 제공하는 기능은 프로시저로 정의 안함

- 선언적 무결성 제약 조건을 사용하여 수행할 수 있는 간단한 데이터

무결성 규칙을 프로시저로 정의하지 않는다

나. 프로시저의 장점

- 보안 :

- 성능 : 네트웍을 통해 보내야 하는 량을 현격히 감소 시킬수 있음

한번 호출해서 사용한 이후에는 고유풀에 존재

- 메모리 할당

- 생산성 : 불필요한 코딩을(개발에서) 줄일 수 있다

- 무결성 : 응용 프로그램의 무결성과 일관성을 향상시킨다

4.트리거(Trigger)

가. 트리거 사용

- 자동적으로 파생된 열값 생성

- 잘못된 트랙개션 방지

- 복잡한 보안 권한 강제 수행

- 분산 데이터베이스의 노드 상에서 참조 무결성 강제 수행

- 복잡한 업무 규칙 강제 수행

- 이벤트 로깅 작업이나 감사 작업

- 동기 테이블 복제 작업

- 데이블 액세스에 대한 통계 수집

나. 트리거 유형

- 행 트리거 및 명령문 트리거

- 행 크리거 : 데이블이 트리거링 명령문에 의해 영향을 받을 때마다 실행

명령문 크리거 : 한번만 실행 (ex delete), 보안 감사나 감사 레코드 생성에 이용

- BEFORE / ALTER 트리거

- BEFORE : 불필요한 ROLLBACK을 제거하기 위해

INSERT, UPDATE 문을 완료하기 전에 특정 열 값을 구하기 위해 사용

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

- 트리거 제한 사항은 트리거 실행을 위해서 참(TRUE)이어야 하는 논리적 표현식을 지정

제3절 트랜잭션

- 데이터 동시성 : 다수의 사용자가 동시에 데이터에 접근할 수 있어야 함

데이터 일관성 : 각각의 사용자가 자신의 트랜잭션이나 다른 사람의 트랜젝션에 변경된

내용을 포함하여 일관된 값을 본다는 의미

- 병렬제어 : 다스의 사용자가 데이터베이스에 동시에 접근하여 같은 데이터를 조회

또는 갱실을 할 때 데이터 일관성을 유지하기 위한 일련의 조치

낙관적 병렬제어 :다수 사용자가 동시에 같은 데이터를 접근할 경우가 적다는 전재로 구현

비관적 병렬제어 :다수 사용자가 동시에 같은 데이터를 접근할 경우가 많다는 전재로 구현

잠김(Locking)이나 Timestamp Ordering이 있다

1. 트랜잭션 관리

- COMMIT, ROLLBACK

- 다중 사용자 환경에서 병행제어와 고장회복을 위한 기법

병행제어 : 한 사용자의 작업이 다른 사용자의 작업에 의해 영향 받지 못하도록 하는 것

고장회복 : 데이터 처리 중 통신, 하드웨어 소프트웨어 오류 발생 등 예기치 않은

예외 사항에 대한 조치

2. 트랜잭션의 특징

가. 원자성

- 트랜잭션은 완전히 수행되거나 전혀 수행되지 않는 상태로 회복되어야 한다

나. 일관성 유지

- 트랜잭션을 실행하면 DB를 하나의 일관된 상태에서 또 다른 일관된 상태로 바뀐다

프로그래머나 무결성 제약 조건을 시행하는 DBMS에서 처리 한다

다. 고립성

- 트랜잭션이 완료될 때까지 자신이 갱신한 값을 다른 트랜잭션들이 보게 해서는 안됨

- 고립성 때문에 임시 갱신 문제해결 가능, 연쇄 복귀하지 않음

- 갱신에 따른 손실이 없어야 하며 모순판독이 없고 반복 읽기 성질을 갖는다

라. 영속성

- 변경완료 되면 결과는 이후의 어떠한 고장에도 손실되지 않아야 한다

3. 병행제어

- 다중 사용 환경에서 발생할 수 있는 갱신불실문제, 모순적인 판독문제 방지

- 갱신분실 : 2개 이상의 트렌잭션이 동일한 데이터를 동시에 갱신할 경우 발생

모순적인 판단 문제 : 트랜잭션이 중가 수행 결과를 다를 트랜잭션이

참조함으로써 발생하는 오류

- 모순 해결 방법 - 잠김( Locking)사용

- 잠김 : 다중 사용자 환경에서 데이터 처리 과정에 있는 데이터를 읽지 못하게

하는 기법

암시적인 잠금 : DDL 를 실행할 때와 같이 DBMS에 의해서 자동으로 실행

명시적인 잠금 : 트랜잭션에 제어에 의해서 실시된다

가. 잠김 단위

- 잠김 대상의 크기를 뜻하며 단위가 커지면 DBMS가 관리하기 쉬워지지만 충돌이

자주 발생하고 작아지면 관리는 어렵지만 충돌 횟수는 적어진다

- 잠긴 지속으로 인해서 대상이 커지는 현상을 잠김 확산이라고 한다

나. 정용 잠김과 공용 잠김

- 전용 잠김 : 어떤 형태로든 자원을 접근 할 수없다

공용 잠김 : 읽기 작업이 가능하나 변경 작업을 할 수 없다

다. 2PC

- 트랜잭션 필요시 잠김을 필요한 만큼 할수 있으나 일단 첫 잠김을 해지하면

더이상 잠김을 걸수 없다

라. 교착 상태

- 다른 사용자가 감겨진 자원이 해제되기를 기다리면서 자신이 잠근 자원을 해제

하지 않는 상태로 무한 대리고 빠지게 된다.

* 4가지 교착 상태

- 상호배제 : 이미 사용중인 다른 프로세스를 기다리는 것

- 점유와 대기 : 자원을 할당 받은 채로 나머지 자원을 할당 받기 위해

다른 프로세스의 자원이 해제되기를 기다리는 프로세스가 존재

- 비 중단 : 자원을 할당 받은 프로세스로 부터 자원을 강제로 빼지 못함

- 환형 대기 : 지원 할당 그래프상에서 프로세스의 환형 사슬이 존재

4. 고장회복

- 트랙잭션 처리 중 ERROR 발생시 트랜잭션이 시작되기 이전 상태로 회복되어야 함

UNDO 실행

5. 잠김 지속 시간

- 지속시간을 최소화하는 것이 잠김에 의한 지연 문제를 최소화 하는 것임

제4절 백업과 복구

1. 장애 유형

- 사용자 실수 : TABLE 삭제

- 미디어 장애 : CPU, 메모리, 디스크 등 -> DATA손실로 연결됨 (수시 백업 필요)

- 구문장애 : 프로그램 오류, 사용 용량 부족, 여유 공간 부족

- 사용자 프로세스 장애 : 사용자 프로그램 비정상 종료, 네트워크 이상으로 세션

종료된 경우

- 인스턴스 장애: 시스템 비정상적인 요인으로 메모리나 데이터베이스 서버

프로세스가 중단된 경우

2. 로그 파일

- 데이터 베이스에서 처리되는 트랜책션의 내용을 모두 기록한 것

데이터 복구를 위해 가장 기본적인 매체

가.로그 파일 기록시기

- 트랜잭션 시작 시점

- 데이터의 입력, 수정, 삭제 시점

- 트랜잭션 Rollbck, commit 시점

나.로그 파일 내용

- 로그 파일은 트랙잭션이 발생할 때마나 commit이나 rollback에 관계없이

모든 내용을 기록한다

- 트랜잭션 식별자, 레코드, 데이터 식별자, 갱신 이전 값, 갱신 이후의 값

3.데이터베이스 복구 알고리즘

- 동기적 쟁신 : 트랜잭션 실행 내용인 데이터베이스 버퍼를 지정매체에

동기적으로 기록한는 것

- 비동기적 쟁신 : 트랜잭션이 완료된 내용을 일정 시간이나 작업량에 따라

시간 차이를 둘고 DB 내용을 저장 매체에 기록하는 것

가. NO-UNDO/REDO (취소 없고 재 실행)

- 비동기적으로 갱신하는 경우 데이터베이스 복구 알고리즘

- 비동기이므로 아직 반영되지 않은 상태이므로 취소 필요 없음

DB 버퍼에 기록되고 저장 매체에 지록되지 않은 상태에서 시스템이

파손되었을 때 트랜잭션의 내용을 재 실행

나. UNDO/NO-REDO (취소 , 재실행 없음)

- 동기적으로 갱신하는 경우

- 트랜잭션이 완료되기 이전에 시스템이 파손이 발생할 경우 변결된 내용을 취소

DB버퍼 내용을 모두 동시적으로 기록하므로 재실행 필요 없음

다. UNDO/REDO

- 동기/비동기적으로 갱신할 경우

라. NO-UNDO/NO-REDO

- 동시적을 저장 매체에 등록하나 데이터 베이스와는 다른 영역에 기록하는 경우

4.백업종류

-------------------------------------------------

물리백업 | 로그 파일 백업실시 | 완전복구

|----------------------------------------

| 로그 파일 백업 없음 |

-------------------------------| 백업시점까지 복구

논리백업 DBMS 유틸리티 |

-------------------------------------------------

5. 데이터베이스 백업 가이드 라인

* 정기적인 Full Backup 실행

* 데이터베이스 구조적 변화가 생긴 전후 백업을 수행

- 테이블스페이스 생성 또는 삭제

- 테이블스페이스에 데이터 파일을 추가하거나 변경했을 때

- 로그(Log) 파일을 변경 했을 때

- Archive Log Mode로 전환시 Control 파일만이라도 백업실시

No archive log mode 전환할 때는 백업을 수행

- 일기-쓰기 수행이 많은 테이블스페이스는 자주 온라인 백업을 실시함

- 백업파일 2본 이상을 보유한다

- 논리 백업은 특정 데이터 또는 특정 테이블 오류시 복구가 용이함

- 분산 데이터베이스는 동일 모드에서 백업을 수행

- 일긱 전용 테이블스페이스는 백업에서 제외

* 신뢰성

- 고장 시간을 측정한 것으로 (평균 고장시간 또는 MTTF)

데이터베이스 시스템의 정상적인 서비스가 중단되는 것을 의미한다

* 유용성

- 다운된 데이터베이스 시스템을 정상으로 회복시키는데 걸리는 시간

(평균 복구 시간 또는 MTTR) + 신뢰성

- 유용성 = MTTF/(MTTF_MTTR)

제3장 데이터베이스 성능 개선

제1절 성능 개선 방법론

1.성능 개선 목표

가. 처리능력

- 해당 작업을 수행하기 위해 소요되는 시간

- 처리능력 = 트랜잭션수 / 시간

- 처리능력은 전체적인 시스템 시각에서 측정하고 평가된다

나. 처리시간

- 작업이 완료되는데 소요되는 시간을 의미

- 배치 프로그램의 성능 목표로 설정한다

* 처리시간 단축 고려사항

- 병행 처리 실시

- 인덱스 스켄보다 FULL 시켄

- Nest-Loop 조인보다 Hash 조인으로 처리

- 대량 작업을 하기 위한 SORT_AREA, HASH_AREA의 메모리를 확보한다

- 병목을 없애기 위해서 작업 계획을 한다

- 대형 테이블인 경우는 파티션으로 생성한다

다. 응답시간

- 입력을 위해 사용자가 키를 누늘 순간부터 시스템이 응답할때까지의 시간

- 사용자가 느끼는 시스템의 성능 척도 (OLTP 시스템의 성능지표)

- 인덱스를 이용하여 액세스 경로를 단축

- 부분 범위 처리를 실시한다

- Sort-Merge 조인이나 Hash 조인을 사용하지 않고 Nest-Loop 조인으로 처리

- 불필요한 결과 정렬 작업을 없애거나 인덱스 이용한 정렬

- 잠김 발생을 억제한다

- 하드 파싱 억제

라. 로드시간

- 데이터베이스에 데이터를 로드하는 작업 수행 시간

- 로그파일을 생성하지 않는 다이렉트 로드을 사용한다

- 병렬로드 작업을 실시한다

- 디스크 I/O 경합이 없도록 작업을 분산한다

- 인덱스가 많은 테이블인 경우는 인덱스를 삭제하고 데이터 로드 후

인덱스를 생성한다

- 파티션을 이용하여 작업을 단순화 한다

2. 성능 개선 절차

가. 분석

- 자료 수집

- 목표 설정

나. 이행

* 최적화 실행

- DB 파라미터 조정

- 전랴적인 저장 기법 적용을 위한 물리 설계 및 디자인 검토

- 비효율적으로 수행되는 SQL문에 대한 최적화

- 네트워크 부하 등을 고려한 데이터베이스 분산 구조에 대한 최적화

- 적절한 인덱스 구성 및 사용을 위한 인덱스 설계 최적화

다. 평가

3. 성능 개선 접근 방법

- 시스템의 성능 문제는 하드웨어 자원문제와 DBMS 설계, SQL 비효율 등의

잘 못된 개발로 나뉜다

- 접근 방법

응용 프로그램의 튜닝(SQL) -> DBMS 서버(메모리, 프로세스) 튜닝

-> 외부 환경(디스크, 네트웍) 튜닝

4. 성능 개선 도구

제2절 조인(JOIN)

1.Nested-Loop 조인

- 드라이빙 조건은 좁은 것이 유리 하고 확인 조건은 넓은 것이 유리

* 이용

- 조인 카럼에 인덱스 필요

- 처리량이 작은 경우에 유리

- 부분 범위 처리에 유리

- 조인의 순서에 의해서 수행 속도가 결정됨

(드라이빙 조건의 선책이 중요)

2.Sort-Merge 조인

- 조인될 각 행 소스를 정렬한다 (행렬은 조인 칼럼 값을 기준으로 정렬)

- DBMS는 정렬된 칼럼값을 비교하여 같은 경우에 두개의 소스를 병합하고

결과 행 소스를 출력한다

- 연결고리가 없을 경우에 발생

- 조인 칼럼 순서로 결과가 출력된다

스켐과 소트작업이 테이블 별로 진행하기 때문에 연결순서는 영향 없음

- 소트에 참여하는 행의 수에 의해 수행 속도가 결정된다

* 이용

- 드라이빙 조건에 독립적이므로 테이블 각각의 조건에 의해서 대상 집합을

줄일수 있을 때 유리하다

- 처리 대상이 전체 테이블일 때 랜덤 I/O 부하가 큰 Nested-Loop 조인보다 유리

- 연결고리가 없을때 수행가능

- 효과적인 수행을 위해서 정렬 영역 사이즈 설정이 필요하다

3.Hash 조인

- 두 Table 중 작은 Table을 선행 테이블로 결정한다

- 선행 테이블을 이용하여 해쉬 테이블을 구성한다 (Build Input)

- 후행 테이블은 해쉬 값을 이용하여 선행 테이블과 조인한다 (Probe Input)

- Build Input 크기

* 이용

- 대용량 데이터 엑세스, 배치처리, 전체 테이블 조인 때 유리

- 양쪽 테이블의 조건으로 각각 범위를 줄일수 있을때

- 병행 처리로 수행 속도 향상이 가능할때

- 메모리 사이즈 조정으로 수행 속도 향상이 가능하다

제3절 애프리케이션 성능 개선

1. 온라인 프로그램 성능개선

- 응답시간 단축이 대부분

* 온라인 프로그램의 특징

- 회면 조회가 가능할 정도로 1회 조회 데이터가 소량이다

- 신속한 트랜잭션 처리가 요구된다

- 조회 조건이 단순하다

- 업무 형태에 따라 데이터 액세스 패턴이 고전되어 있다.

* 온라인 프로그램 성능 개선 작업시 고려 사항

- 사용빈도가 높은 sql문을 개선하는 것이 효과적

- 인덱스를 이요한 데이터 엑세스 범위를 줄이는 것이 효과적

- 부분 범위 처리로 응답 시간 단축

- 부분 범위 처리를 하기 위해서 Nested-Loop 조인과 인덱스를 이용한 정렬을 유도

- 장기 트랜잭션 처리를 억제한다

가. 상수 바인딩에 의해 발생되는 파싱 부하

나. 웹 게시글 형태의 인터페이시스 부분 범위 처리

다. 과다한 함수 사용으로 인한 부하 발생

- 결과 행 중 일부에서 행에 대해서 복잡한 연산이 필요한 경우

- 집계 처리를 부분 범위처리화 할 경우

- 복잡한 계산 처리가 자주 변경되어 이를 통합 관리할 필요가 있는 경우

2.대용량 DB 배치 프로그램 성능

* 배치 작업 이슈 목록

- 절대 수행시간 부족

- 수행 결과 검증 시간 확보의 어려움

- 오류에 따른 재 처리 시간 확보가 불가능

- 미완료시 대안 제시에 어려움

- 미처리 또는 지연으로 파급 되는 문제 해결에 장시간이 소요

가. 절차적인 처리에서 집합적인 처리 방식으로 전환

* 절차적 처리의 비효율

- 반복 DBMS call 이 발생

- 랜덤 I/O 발생을 유발한다

- 동일 데이터를 중복해서 읽는다

- 업무 규칙 변경시 프로그래 구조 수정이 불가피

- 개별적인 SQL 개선은 가능하지만 전체 최적화 불가능

* 절차적 처리를 보완하는 요소

- 이중 커서 사용을 하지 않고 조인을 이용하여 단일 커서를 사용

- 동일 모듈 내에서 같은 데이터을 2번 이상 읽지 않게 프로그래 구조화

- 최소한 개별적인 SQL 단위 비효율 제거

* 집합적 처리시 유의 사항

- SQL작성 후 실행 계획 확인함

- 대량 배치 처리는 Hash-join 사용하는 것이 유리

- 랜덤 I/O 비요율 없애기 위해서는 인덱스 스켄보다 Table Full Scan 방식유리

- Full Table Scan 작업시 병행처리을 사용함

- Hash 조인이나 집계를 위한 소트 작업을 고려하여 추가 메모리 세션에 할당

나. 분석 함수를 통한 성능 개선 효과

- Ranking Family : RANK, DENSE_RANK, CUME_DIST, PERCENT_RAND, NTILE,ROW_NUMBER

- Window Aggregate Family (집계정보) : MAX, SUM, AVG, STDDEV, VARIACE, COUNT..

- Reporting Aggregate Faily

- LEAD/LAG Family

다. 파티션 스토리지 전략을 통한 성능 향상 방안

* 파티션 TABLE의 장점

- 데이터 엑서스시 파티션 단위로 엑세스 범위를 줄여 I/O 성능향상

- 전체 데이타 훼손 가능성 감소

- 각 분할 영역을 독립적으로 백업하고 복구할 수 있다

- 디스크 스트라이핑으로 I/O 성능을 향상 시킬수 있다(디스크 암(ARM) 경합감소)

제4절 서버 성능 개선

1. 오브젝트 튜닝

- 테이블, 인덱스, 세그멘트에 관련한 사항이 대상

- 오프젝트는 성능을 고려하여 설계되어야 한다

- 저장 장치를 이루는 블록, 확장영역, 세그멘트에 관련된 사항을 튜닝한다

- 인덱스는 삭제, 갱신으로 스큐 현상이 심한 경우는 재구성 작업이 필요

- I/O 병목이 발생하지 않게 물리적인 배치를 실시한다

2. 인스턴스 튜닝

가. 메모리

- 버퍼 캐시, 라이브러리 캐시 등의 HIT ratio에 의해서 평가하여 조정한다

- Sort Area, Hash Area는 스와핑 발생 여부에 따라서 사이즈를 결정한다

나. 프로세스

- DBMS가 다중 프로세스 시스템이고 필요에 따라 추가적인 프로세스 가동이 가능

다. Latch 경합

- 트랜잭션 처리를 위한 경합 발생

- 오브잭트 생성이나 변경 등으로 결합이 발생할 수 있다

3. 환경 튜닝

가. CPU 튜닝

- CPU 사용율 평가

- 사용량이 USR > SYS > WIO 순이 바람직

- idle가 일반적으로 20~30% 유지 하는 것이 바람직

0%인 상태가 지속되면 증설을 고려

나. 메모리 튜닝

- DBMS를 포함한 사용자 사용 메모리 크기가 전체 크기의 40%~60% 유지하는 것이 바람직

다. I/O 튜닝

- 데이터베이스 병목은 I/O에 의해 발생

- 물리적인 디스크와 디스크 채널을 분산하므로 성능을 개선할 수 있다

- 읽기/쓰기 작업에 따른 분산이 필요하다

- Cook Device보다 Raw Device가 I/O 성능에 유리

라. 네트워크 튜닝