1.데이터베이스 설계
1.1저장공간 설계
컬럼>테이블>tablespace> 데이터 파일
가.테이블
-행(Row)과 열(Column)으로 구성되는 가장 기본적인 데이터베이스 객체
1)테이블
-데이터의 저장 형태, 파티션 여부, 데이터의 유지 기간 등에 따라 다양하게 분류
-Heap, Clustered, Partitioned(Range, List, Hash, Composite), external, temporary
*Heap-organized table: 표준 테이블 형태. 로우의 저장 위치는 로우가 삽입될때 결정
-입력 시 정렬을 하지 않으므로 입력에 대한 부하가 적음
-인덱스와 데이터 저장 공간이 분리
-대량의 트랜잭션이 발생하는 로그성 테이블
-Oracle, PostgreSQL
*Clustered index table:
-Primary Key 값이나 인덱스 키 값의 순서로 데이터가 저장되는 테이블
-B-tree 구조의 Leaf Node에 데이터 페이지가 존재함
-입력시 정렬에 대한 부하 발생
-인덱스 저장 공간에 데이터 함께 저장
-prefetch가 가능
-PK를 조건으로 사용되는 경우 더 빠르게 데이터에 접근 가능
-크기가 작고 자주 액세스 되는 코드성 테이블
-칼럼 수가 작고 행의 수가 많은 테이블 (주로 통계성 테이블)
-소량의 랜덤엑세스에 유리
-넓은 범위를 조회해야 하는 테이블에 적합
-MySQL, MariaDB, SQL Server
*Partitioned table:
-파티셔닝: 대용량의 테이블을 파티션이라는 논리적인 단위로 나눔으로써 성능이 저하 방지, 관리 용이
-대용량I/O 성능, 가용성, 확장성
-유형: 범위 분할(Range), 해시 분할(Hash), 결합 분할(Composite) 등
#범위(Range) 파티션 테이블
-주로 날짜 컬럼을 기준으로 데이터를 월, 일, 년 등의 기준으로 파티셔닝할때 사용
-범위를 너무 세분화하여 파티션의 개수가 지나치게 많아지는 것을 지양
#목록(List) 파티션 테이블
-미리 정해진 코드성 칼럼의 값을 키로 파티셔닝(지역을 기준으로 파티셔닝)
-데이터 분포도를 파악하여 데이터를 각 파티션에 고루 분산
#해시(Hash) 파티션 테이블
-관리상의 이점보다는 성능상의 이점
-대용량 데이터 처리 시 데이터 블록(페이지)의 경합 감소
#복합(Composite) 파티션 테이블
-파티션의 순서가 중요 (뒤에 분할된 파티션을 서브파티션이라고 함)
*external table: 외부 파일을 마치 DB안에 존재하는 일반 테이블 형태로 이용할 수 있는 DB 객체
-DW의 ETL 작업에 유용
*temporary table: 트랜잭션이나 세션별로 데이터를 저장하고 처리할 수 있는 임시 테이블
2)컬럼
-테이블을 구성하는 요소로, 데이터 타입과 길이로 정의
#컬럼 데이터 타입에 따라 물리적인 칼럼 순서 조정
-고정 길이 칼럼이고 NOT NULL인 칼럼은 선두에 정의
-가변 길이 칼럼을 뒤편으로 배치
-NULL 값이 많을 것으로 예상되는 칼럼을 뒤편으로 배치
#데이터 타입과 길이 지정 시 고려사항
-가변 길이 데이터 타입은 예상되는 최대 길이로 정의
-고정 길이 데이터 타입은 최소 길이로 지정
-소수점 이하 자리 수의 정의는 반올림되어 저장되므로 정확성을 확인하고 정의
#컬럼값비교
-양쪽 모두 CHAR 타입: 두 칼럼 중 길이가 짧은 쪽 칼럼에 공백을 추가하여 길이를 맞춘 후 비교
-문자열비교에서 어느 한쪽이 VARCHAR 타입이면 각각의 문자를 비교하여 서로 다른 값이 나타나면 문자 값이 큰 칼럼이 크다고 판단
-NUMBER.와 CHAR타입을 비교할때 CHAR을 NUMBER타입으로 변환하여 비교, 변환이 불가한 경우 NUMBER를 CHAR로 변환후 비교
-각 문자타입이 상수값과 비교될때는 결정되어 있는 칼럼의 데이터 타입과 같도록 변환된 후 비교
3)테이블 설계시 고려사항
-칼럼 데이터 길이 합이 1 블록 사이즈보다 큰 경우 수직 분할
-칼럼 길이가 길고 특정 칼럼의 사용 빈도 차이가 심한 경우 수직분할
-테이블과 인덱스는 분리하여 저장하면 I/O병목을 최소화
-임시세그먼트는 독립적인 공간을 사용이 좋음: DB내부에서 객체 생성
나.테이블과 테이블 스페이스
-테이블은 테이블스페이스라는 논리적인 단위를 이용하여 관리
-테이블스페이스는 물리적인 데이터 파일을 지정하여 저장
#데이터용/인덱스용 테이블스페이스 설계 유형
-테이블이 저장되는 테이블 스페이스는 업무별로 지정
대용량 테이블은 독립적인 테이블 스페이스 지정
테이블과 인덱스는 분리 저장
LOB 타입 데이터는 독립적인 공간 지정
다.용량설계
#목적
-정확한 데이터 용량을 예측하여 저장공간을 효과적인 사용과 저장 공간에 대한 확장성을 보장하여 가용성을 높이기 위함
-H/W 특성을 고려하여 디스크 채널 병목을 최소화
-디스크 I/O를 분산하여 접근 성능을 향상
-테이블이나 인덱스에 맞는 저장 옵션 지정
#테이블 저장 옵션에 대한 고려사항
-초기 사이즈, 증가 사이즈
-트랜잭션 관련 옵션
-최대 사이즈와 자동 증가
#저장 용량 설계 절차
-용량 분석: 데이터 증가 예상 건수, 주기, 로우 길이 등을 고려함
-오브젝트별 용량 산정: 테이블, 인덱스에 대한 크기
-테이블스페이스별 용량 산정: 테이블스페이스별 오브젝트 용량의 합계
-디스크 용량 산정: 테이블스페이스에 따른 디스크 용량과 I/O 분산 설계
1.2무결성 설계
가.데이터 무결성
-데이터의 정확성, 일관성, 유효성, 신뢰성을 위해 무효 갱신으로부터 데이터를 보호
-종류에 따라 장단점이 존재하므로 선택적인 적용 필요
1)데이터 무결성 종류
-실체/영역/참조/사용자 정의 무결성
2)데이터 무결성 강화 방법
#애플리케이션: 데이터를 조작하는 프로그램 내에 데이터 생성, 수정, 삭제 시 무결성 조건을 검증하는 코드를 추가
#DB 트리거: 트리거 이벤트 시 저장 SQL을 실행하여 무결성 조건을 실행함
-트랜잭션이 실패할 확률이 높아짐
#제약조건: 데이터베이스 제약 조건 기능을 선언하여 무결성을 유지함
-기본적인 DB요건은 쉽게 무결성 유지 가능
| 구분 | 장점 | 단점 |
| 애플리케이션 | 사용자 정의 같은 복잡한 무결성 조건을 구현함 | -소스코드에 분산되어 관리의 어려움 -개별적으로 시행되므로 적정성 검토에 어려움 |
| DB트리거 | -통합 관리가 가능함 -복잡한 요건 구현 가능 |
-운영 중 변경이 어려움 -사용상 주의가 필요함 |
| 제약조건 | -통합 관리가 가능함 -간단한 선언으로 구현 가능 -변경이 용이하고 유효/무효 상태 변경 가능함 -원천적으로 잘못된 데이터 발생을 막을 수 있음 |
-복잡한 제약조건 구현 불가능 -예외적인 처리가 불가능 |
나.엔터티 무결성
-실체에서 개체의 유일성을 보장하기 위한 특성
-기본키 제약을 정의하면 해당 테이블은 Clustered Index Table로 변경되어 데이터가 키값순으로 재배열됨
-1대다 관계에서 1쪽의 유일성을 보장
#PK 제약:NOT NULL, UNIQUE
#Unique 제약: 경우에 따라 NULL 가능
#식별자 설계-채번: DBMS에서 제공하는 일련번호를 발생시키는 시퀀스같은 객체나 시리얼같은 데이터 타입을 이용
다.영역 무결성(도메인)
-칼럼에 적용되어 단일 로우의 칼럼 값만으로 만족 여부를 판단
#데이터 타입 & 길이
#유효값(CHECK)
#NOT NULL
라.참조 무결성
-두 실체 사이의 관계 규칙을 정의하기 위한 제약 조건으로 데이터가 입력, 수정, 삭제될 때 두 실체의 튜플들 사이의 정합성과 일관성을 유지
#입력 참조 무결성: 애플리케이션에서 구현(자식테이블 입력)
DEPENDENT: 참조되는(부모) 테이블에 PK 값이 존재할 때만 입력을 허용
AUTOMATIC: 참조되는(부모) 테이블에 PK 값이 없는 경우는 PK를 생성 후 입력
DEFAULT: 참조되는(부모) 테이블에 PK 값이 없는 경우 자식의 FK를 지정된 기본값으로 입력
CUSTOMIZED: 특정한 조건이 만족할 때만 입력을 허용
NULL: 참조되는(부모) 테이블에 PK 값이 없는 경우 외부키를 NULL 값으로 처리
NO EFFECT: 조건 없이 입력을 허용
#수정/삭제 참조 무결성: 부모 식별자가 변경되었을 경우(부모테이블 수정)
RESTRICT: 참조하는(자식) 테이블에 PK 값이 없는 경우 삭제/수정 허용
CASCADE: 참조되는(부모) 테이블과 참조하는 (자식)테이블의 FK를 연쇄적 삭제/수정
DEFAULT: 참조되는(부모) 테이블의 수정을 항상 허용하고 참조하는(자식) 테이블의 FK를 지정된 기본값으로 변경
CUSTOMIZED: 특정한 조건이 만족할 때만 수정/삭제 허용
NULL: 참조되는(부모) 테이블의 수정을 항상 허용하고 참조하는(자식) 테이블의 FK를 NULL 값으로 수정
NO EFFECT: 조건 없이 삭제/수정 허용
#디폴트 규칙 정의의 필요성: NULL 조건은 DEFAULT 조건으로 설계
#모델상에서 슈퍼타입(SUPER-TYPE)-서브타입(SUB-TYPE) 관계
*삽입 시에는 DEPENDENT, AUTOMATIC 조건을 적용하고, 삭제나 변경 시에는 CASCADE 조건을 적용
1.3인덱스 설계
가.인덱스 기능
-인덱스: DB 테이블의 ROW 키값과 데이터의 물리적주소를 매핑한 테이블을 저장하는 영역
나.인덱스 설계 절차
-최소의 인덱스 구성으로 모든 접근 경로를 제공할 수 있어야 전략적인 인덱스 설계
-접근경로수집>후보결정>접근경로설정>칼럼조합 및 순서결정>적용시험
1)접근경로 수집
-테이블에서 데이터를 검색하는 방법. 테이블스캔. 인덱스스캔
-조인,분포도,조회,결합,정렬,그룹핑,일련번호,통계
#반복 수행되는 접근 경로: 대표적인 것이 조인 칼럼
#분포도가 양호한 칼럼: 주민번호 등은 단일 칼럼 인덱스로도 충분한 수행 속도를 보장 받을 수 있는 후보
#조회 조건에 사용되는 칼럼
#자주 결합되어 사용되는 칼럼
#데이터 정렬 순서와 그룹핑 칼럼
#일련번호를 부여한 칼럼: 이력관리용
#통계 자료 추출 조건
#조회 조건이나 조인 조건 연산자
2)분포도 조사에 의한 후보 칼럼 선정
-수집된 접근 경로 칼럼들을 대상으로 분포도를 조사. 설계단계에서는 현재 데이터를 참고하거나 예상한 상황을 고려하여 예측
-분포도=데이터별 평균 로우 수 / 테이블의 총 로우 수 x 100
*분포도가 10 ~ 15% 정도이면 인덱스 칼럼 후보. 인덱스를 통한 액세스는 싱글 블록으로I/O을 하고, 테이블 스캔은 멀티 블록으로 I/O
*분포도는 단일 칼럼을 대상으로 조사
*단일 칼럼으로 만족하지 않는 경우 결합 칼럼들에 대한 분포도 조사
*분포도 조사 결과를 만족하는 칼럼은 인덱스 후보로 선정
*기형적으로 분포도가 불규칙한 경우는 별도 표시하여 접근 형태에 따라 대책을 마련
*빈번히 변경이 발생하는 칼럼은 인덱스 후보에서 제외
3)접근경로 결정
-인덱스 후보 목록을 이용하여 접근 유형에 따라 어떤 인덱스 후보를 사용할 것인지 결정
4)칼럼 조합 및 순서 결정(항등분정)
-단일 칼럼의 분포도가 양호하면 단일 칼럼 인덱스로 확정
#항상 사용되는 컬럼을 선두 칼럼
#등치( = )조건으로 사용되는 컬럼을 선행 칼럼
#분포도가 좋은 컬럼을 선행 칼럼
#ORDER BY, GROUP BY 순서를 적용
*드라이빙 조건: 인덱스 탐색범위를 대폭 축소하는 조건, 보통 범위검색은 인덱스 탐색 효율을 떨어뜨림.
*매칭도: 드라이빙 조건으로 사용된 컬럼수
*선택도: 전체레코드중 조건절에 의해 선택되는 비율
#항상 등치조건으로만 사용하면 인덱스 수 증가
#NOT IN, <>, NOT LIKE, NOT IN등 부정 연산은 인덱스 사용 불가
or 연산 이후는 인덱스 매칭도 계산 안함
-between, like, <, > 조건은 해당 컬럼은 인덱스를 사용하지만, 이후 인덱스 컬럼은 인덱스를 사용하지 않음.
-in, = 컬럼은 이후 컬럼도 인덱스를 사용.
-in의 인자로 서브쿼리가 들어가면 서브쿼리가 먼저 실행되어 in은 체크조건이 됨.
-값을 가공하면 인덱스 사용불가.
-인덱스 구성컬럼 중 하나라도 NOT NULL이면, IS NULL 조회에 인덱스 사용 가능.
5)적용시험
-설계된 인덱스를 적용하고 접근 경로별로 인덱스가 사용되는지 시험
다.인덱스 구조
-트리,해시,비트맵,함수,조인,도메인, 클러스터, 파티션 인덱스
1)트리기반 인덱스
-상용 DBMS에서는 B+ 트리 인덱스를 주로 사용.
-루트에서 리프 노드까지 모든 경로의 깊이가 같음
-데이터 삽입과 삭제 성능 우수
-모든 키값은 리프노트에 존재하며, 양방향 연결됨
-범위검색시 성능 우수
-B+트리 각 노드는 최소한 반이상 차 있어야 함
-B*트리 각 노드는 최소한 2/3 이상 차 있어야 함
-처리 범위가 넓으면 수행속도 저하
-인덱스를 생성한 컬럼을 가공하면 인덱스 사용 불가
-인덱스를 생성한 컬럼순으로 정렬됨
-분포도가 나빠지면 수행속도 저하
2)해쉬 기반 인덱스
-키 값에 대응하는 버킷을 식별하고 탐색
3)비트맵(BITMAP) 인덱스
-B+트리는 키 값에 Rowid 리스트를 저장
-비트맵에서 비트의 위치는 테이블에서 로우의 상대적인 위치를 의미하므로 해당 테이블이 시작되는 물리적인 주소만 알면 실제 로우의 물리적인 위치를 계산
-데이터 분포도가 나쁜 칼럼에 적합 예)성별
-저장공간과 I/O를 감소
| 구분 | B-TREE | BITMAP |
| 구조 특징 | Root block, branch block, leaf block으로 구성되며, 인덱스 깊이를 동일하게 유지하는 트리 구조 | 키 값이 가질 수 있는 각 값에 대해 하나의 비트맵을 구성 |
| 사용 환경 | OLTP | DW, Mart 등 OLAP |
| 검색 속도 | 처리 범위가 좁은 데이터 검색 시 유리함 | 다중 조건을 만족하는 데이터 검색 시에 유리함(특히, 비정형 쿼리) |
| 분포도 | 데이터 분포도가 좋은 칼럼에 적합 | 데이터 분포도가 나쁜 칼럼에 적합 |
| 장점 | 입력, 수정, 삭제가 용이함 | 비트 연산으로 OR 연산, NULL 값 비교 등이 가능함 |
| 단점 | 처리 범위가 넓을 때 수행 속도 저하 | 전체 인덱스 조정의 부하로 입력, 수정, 삭제가 어려움 |
3)함수기반 인덱스
-함수나 수식(expression)으로 계산된 결과에 대해 B+ 트리 인덱스나 Bitmap Index를 생성, 사용할 수 있는 기능 제공
4)비트맵 조인 인덱스
-단일 객체로만 구성되었던 기존 인덱스와 달리 여러 객체의 구성 요소(칼럼)로 인덱스 생성
-물리적인 구조는 비트맵 인덱스와 완전히 동일. -인덱스의 구성 컬럼이 베이스 테이블의 컬럼 값이 아니라 조인된 테이블의 컬럼 값
5)도메인 인덱스
-개발자가 자신이 원하는 인덱스 타입을 생성
6)Cluster Index
-인덱스부와 데이터부로 나누어져 있으며, 인덱스부는 일반적인 B*tree 인덱스로 구성
-데이터 삽입시 키순서에 따라 지정된 위치에 저장되어야 하므로 데이터 페이지의 유지비용이 매우 높음(데이터 페이지의 Spit 발생 가능성 높음)
-Cluster Index가 존재할 경우 Non Cluster Index는 RID대신 Cluster Index의 키값을 가짐
-운영중에 Cluster Index를 생성하면 구조적으로 데이터 페이지의 개편이 일어나므로 많은 오버헤드 발생
7)파티션 인덱스
-각 인덱스 파티션이 담당하는 테이블의 파티션 범위에 따라 글로벌 파티션 인덱스와 로컬 파티션 인덱스로 구분
#비 파티션 인덱스
-파티션 테이블에 생성된 비 파티션 인덱스는 글로벌 인덱스라 부르며 파티셔닝되지 않은 인덱스가 파티셔닝된 전체 테이블과 대응되는 구조
#로컬 파티션 인덱스(많이 사용)
-테이블이 파티셔닝된 기준 그대로 파티셔닝된 파티션 인덱스.
-인덱스 파티션과 테이블 파티션이 1:1로 대응되는 구조
-테이블 파티션키가 조건절에 없으면 비효율
-테이블 파티션에 작업시, 인덱스 파티션 자동관리
#글로벌 파티션 인덱스
-테이블의 파티션과 독립적으로 인덱스 파티션을 구성하는 것
-파티셔닝되지 않은 테이블에도 적용
-인덱스 파티션키가 인덱스 선두 컬럼이어야 함.
#인덱스 재생성 작업을 고려중에 우선순위가 높은 작업 대상은 빈번한 수정이 발생하는 컬럼을 포함한 인덱스임
#인덱스를 사용할 수 없는 경우
-컬럼 가공
-NULL 비교
-부정 비교
-like는 인덱스 사용가능
#테이블/인덱스 파티셔닝 이유
-가용성, 조회성능, 경합 분산
-단, 저장 효율은 나빠질 수 있음.
1.4분산 설계
가.분산 DB개요
-하나의 논리적 DB가 네트워크상에서 여러 컴퓨터에 물리적으로 분산되어 있지만 사용자가 하나의 DB처럼 인식할 수 있도록 논리적으로 통합된 DB
-원격 데이터에 대한 의존도 감소
-단일 서버에서 불가능한 대용량 처리가 가능
-기존 시스템에 서버를 추가하여 점진적 증가가 용이
-장애전파가 안되어 신뢰도와 가용성이 향상
나.분산 DBMS 투명성
#분할 투명성: 전역 스키마가 어떻게 분할되어있는지를 알 필요가 없음
#위치 투명성: 데이터의 물리적인 위치도 알 필요가 없음
#중복 투명성: 어떤 데이터가 중복되었는지, 어디에 중복 데이터를 보관하고 있는지 알 필요가 없음
#장애 투명성: DB가 분산되어 있는 시스템이나 네트워크에 장애가 발생하더라도 데이터의 무결성 보장
#병행 투명성: 다수 트랜잭션이 동시에 수행되어도 결과의 일관성 유지 (Locking, Timestamp)
다.분산 설계 전략
-중앙 집중형 DB처럼 한 컴퓨터에서만 DB를 관리하고 여러 지역에서 접근할 수 있도록 하는 방식
-지역 DB에 데이터를 복제하고 실시간으로 복제본을 갱신하는 방식
-지역 DB에 데이터를 복제하고 주기적으로 복제본을 갱신하는 방식
-데이터 분할 시 전체 지역 DB를 하나의 논리적 DB로 유지하는 방식
-데이터 분할 시 각각의 지역 DB들을 독립된 DB로 유지하는 방식
라.분산 설계 방식
1)테이블 위치 분산
-테이블이 전역적으로 중복되지 않음. 테이블마다 위치를 다르게 지정 > 테이블마다 존재할 서버를 결정
2)분할(Fragmentation)
#완전성: 분할 시에 전역 릴레이션 내의 모든 데이터가 어떠한 손실도 없이 분할로 매핑
#재구성: 관계 연산을 사용하여 원래의 전역 릴레이션으로 재구성이 가능
#상호 중첩 배제: 분할 시 하나의 분할에 속한 데이터 항목이 다른 분할의 데이터 항목에 속하지 않아야 함
#수평분할: 특정 속성의 값을 기준으로 분할(동일한 속성)
#수직분할: 속성을 기준으로 분할
3)할당(Allocation)
-동일한 분할을 복수 서버에 생성하는 분산 방법,
-분할의 중복이 존재하는 할당 방법과 중복이 존재하지 않는 할당 방법으로 구분
#부분 복제: 전역 서버 테이블의 일부 데이터를 지역 서버에 복제
#광역 복제: 전역 서버 테이블의 전체를 지역 서버에 복제
마.데이터 통합
-통합 방식은 DW를 이용하는 방법과 EAI를 이용하는 방법. 혼합 방법
1.5보안 설계
-DB 보안: DB정보가 비인가자에 의해 노출, 변조, 파괴되는 것을 막는 활동
-신분,역할,위치,시간,서비스 제한 등의 요건을 이용하여 설계
-뷰를 사용하여 데이터 접근을 이중화
-터미널 혹은 네트워크 주소등 확인이 가능한 접근 위치나 경로만 접근 허용
-일과 시간, 마감시간 등 특정시간에 대하여 접근 허용
가.접근통제 기능
-접근통제는 보안시스템의 중요한 기능적 요구 사항.
-임의의 사용자가 어떤 데이터에 접근하고자 할 때 접근을 요구하는 사용자를 식별하고, 사용자의 요구가 정상적인 것인지를 확인, 기록하고 보안 정책에 근거하여 접근을 승인하거나 거부함으로써 비인가자의 불법적인 자원 접근 및 파괴를 예방하는 보안 관리의 모든 행위
#DAC
-임의적 접근 통제는 사용자의 신원에 근거를 두고 권한을 부여하고 취소하는 메커니즘을 기반. Grant, Revoke
#MAC
-강제적 접근 통제는 주체와 객체를 보안 등급 중 하나로 분류하고, 주체가 자신보다 보안 등급이 높은 객체를 읽거나 쓰는 것을 방지
나.보안모델
1)접근 통제 행렬(Access Control Matrix)
-임의적 접근 통제를 위한 보안 모델
-행은 주체, 열은 객체, 행&열은 주체와 객체가 가지는 권한의 유형
-주체: DB에 접근할 수 있는 조직의 개체. 일반적으로 객체에 대하여 접근을 시도하는 사용자
-객체: 보호되고 접근이 통제되어야 하는 DB의 개체. 테이블, 칼럼, 뷰, 프로그램, 논리적인 정보의 단위
-규칙: 주체가 객체에 대하여 수행하는 DB의 조작. 입력, 수정, 삭제, 읽기와 객체의 생성과 파괴 등
2)기밀성 모델: Bell-Lapadula
-군사용 보안 구조의 요구 사항을 충족시키기 위하여 정보의 불법적인 파괴나 변조 보다는 기밀성 유지에 초점을 둔 최초의 수학적인 모델
-단순 보안 규칙: No-Read-Up. 주체는 자신보다 높은 등급의 객체를 읽을 수 없음
-*(스타)-무결성 규칙: No-Write-Down. 주체는 자신보다 낮은 등급의 객체에 정보를 쓸 수 없음
-강한 *(스타) 보안 규칙: 주체는 자신과 등급이 다른 객체에 대하여 읽거나 쓸 수 없음
2)무결성 모델: Biba(BLP의 반대)
-정보의 일방향 흐름 통제를 이용하여 정보의 비밀성을 제공하는 기밀성 모델에서 발생하는 정보의 부당한 변경 문제를 해결하기 위해 개발된 무결성 기반의 보안 모델
-단순 보안 규칙: No-Read-Down. 주체는 자신보다 낮은 등급의 객체를 읽을 수 없음
-*(스타)-무결성 규칙: No-Write-Up. 주체는 자신보다 높은 등급의 객체에 정보를 쓸 수 없음
다.접근 통제 정책
#신분-기반 정책(DAC)
*개인 또는 그들이 속해 있는 그룹들의 신분에 근거하여 객체에 대한 접근을 제한하는 방법. IBP, GBP
#규칙-기반 정책(MAC)
-강제적 접근 통제와 동일한 개념, 주체가 갖는 권한에 근거하여 객체에 대한 접근을 제한. MLP, CBP
#역할-기반 정책(RBAC)
-GBP의 한 변형된 형태. 정보에 대한 사용자의 접근이 개별적인 신분이 아니라 개인의 직무 또는 직책에 의해 결정
라.접근통제 메커니즘
-패스워드: 데이터 및 프로그램 접근권한 확인
-암호화
-접근통제목록(ACL): 객체를 기준으로 접근 통제 정보를 저장. 어떤 사용자들이 객체에 대하여 어떤 행위를 할 수 있는지를 나타냄. 주체의 수가 많아지면 관리 어려움
-CL(Capability List): ACL 문제해결. 주체가 접근할 수 있는 객체와 접근 권한을 주체에 저장하는 방식
-보안등급: 주체나 객체 등에 부여된 보안 속성의 집합
-통합 정보 메커니즘: 두가지 이상의 복합된 특성으로 구현
마.접근통제 조건
#값 종속 통제
-객체에 저장된 값에 따라 접근 통제 허가. 예)계약금액에 따른 기밀 수준 차등
#다중 사용자 통제
-지정된 객체에 대해 다수의 사용자가 연합하여 접근. 예)두명이상 다수결
#컨텍스트 기반 통제
-특정 시간, 네트워크 주소등 확인이 가능한 접근 경로나 위치, 인증 수준 등과 같은 외부적인
요소에 의존하여 객체의 접근을 제어하는 방법
바.감사 추적
-애플리케이션 및 사용자가 DB에 접근하여 수행한 모든 활동을 일련의 기록으로 남기는 기능
2.데이터베이스 이용
2.1DBMS
가.개념적 DBMS 아키텍처
-DBMS 서버는 인스턴스와 데이터베이스로 구성.
-인스턴스는 메모리 부문과 프로세스 부문으로 구성.
-그 외 DB의 기동과 종료를 위하여 DBMS 환경을 정의한 매개변수 파일과 파일 목록(데이터 파일, 로그 파일)을 기록한 제어 파일이 있음
-구성요소: 데이터베이스 파일, 데이터 저장관리자, 질의처리기, 트랜잭션관리자.
나.데이터베이스 서버 시작과 종료
-데이터베이스 관리자가 DBMS 인스턴스를 시작. 인스턴스 시작은 매개변수 파일을 읽음
#초기화 매개변수
-파일과 같은 항목에 이름을 지정
-최대 값과 같은 한계를 설정
-메모리 크기와 같은 용량에 영향을 주는 매개변수(가변 매개변수)
-인스턴스를 시작할 데이터베이스 이름
-로그 파일의 처리 방법
-DB 제어 파일의 이름과 위치
1)DB 서버 시작
#인스턴스 시작: 매개변수 파일을 읽어 초기화 매개변수 값을 결정하고 프로세스를 구동
#DB 마운트: 데이터베이스의 제어파일을 오픈
#DB 오픈: 데이터파일, 로그파일을 오픈
#DB 닫기: 메모리에 있는 모든 DB 데이터와 로그를 데이터 파일과 리두 로그 파일에 각각 기록하고 온라인 데이터 파일과 온라인 로그 파일을 닫음
#DB마운트 해제: DB 마운트를 해제하여 DB와 인스턴스 간의 관계를 끊고 DB의 제어 파일을 닫음
#인스턴스 종료: 할당된 공유 영역이 메모리에서 제거되고 백그라운드 프로세스가 종료
가.DBMS 추상화
-메타데이터 관리: 데이터의 값뿐만 아니라 그 데이터의 정의나 설명, 파일의 구조, 데이터 항목의 유형과 저장 형식, 제약조건과 같은 메타 데이터를 시스템 카탈로그 또는 데이터 사전에 저장, 관리
-데이터 독립성: 데이터의 파일 구조가 프로그램으로부터 분리되어 시스템 카탈로그 또는 데이터 사전으로 관리
-데이터 추상화: 사용자에게 데이터에 대한 개넘적인 접근만을 제공. 물리구조 알필요 없음
-트랜잭션과 동시성제어: 여러 사용자가 동시에 동일한 데이터베이스에 접근
나.DBMS 사상
-외부, 개념, 내부 스키마
-개념적 단계: 추상화의 최상위 단계로 최종 사용자들이 관심을 갖는 데이터베이스의 부분만을 정의
-논리적 단계: 데이터베이스의 전체 구조를 추상화하는 단계로 데이터베이스에 저장된 데이터와 데이터 간의 관계, 권한(보안), 무결성과 같은 부가적인 정보를 정의
-물리적 단계: 가장 낮은 추상화 단계로 데이터가 실제 어떻게 저장되어 있는지 원시 수준의 데이터 구조를 정의. 저장된 데이터의 유형, 인덱스의 종류, 칼럼의 표현/순서, 각 레코드의 물리적 순서 등
-개념적－논리적 사상: 논리적 단계의 데이터 독립성
-논리적－물리적 사상: 물리적 단계의 데이터 독립성
다.DBMS 구성요소
-DB 파일: 데이터 사전을 저장하는 파일과 사용자의 데이터를 저장하는 파일
-데이터 저장 관리자: 데이터 사전과 사용자 데이터 파일에 접근하여 데이터를 읽고 쓰는 책임을 가진 구성요소
-질의 처리기:DB 사용자가 물리적인 단계의 지식이 없어도 개념적인 단계에서 SQL을 이용하여 스키마를 정의하고 해당 스키마에 데이터를 저장, 변경, 조회
-트랜잭션 관리자: DB파일에 여러 명의 사용자가 동시에 접근하여 데이터를 처리하더라도 데이
터에 이상 현상이 없도록 데이터의 일관성을 유지
2.DBMS의 종류
-Oracle, IBM DB2, MSSQL Server, MySQL, MariaDB, PostgreSQL
다.데이터베이스 구조
1)데이터 사전(DD, Data Dictionary)
-연관된 DB정보를 제공하는 읽기전용 테이블 또는 뷰 집합
#포함내용
-데이터베이스의 모든 스키마 객체 정보
-스키마 객체에 대해 할당된 영역의 사이즈와 현재 사용 중인 영역의 사이즈
-열에 대한 기본 값
-무결성 제약 조건에 대한 정보
-사용자 이름, 사용자에게 부여된 권한과 역할
-기타 일반적인 데이터베이스 정보
2)데이터베이스, 테이블 스페이스 및 데이터 파일
-데이터는 테이블을 통해서 논리적으로는 테이블스페이스에 물리적으로는 해당 테이블스페이스와 연관 데이터 파일에 데이터를 저장
3)데이터 블록, 확장 영역 및 세그먼트 간의 관계
-DBMS는 데이터베이스의 모든 데이터에 대한 논리적 데이터베이스 영역을 할당
-DB영역의 할당 단위는 데이터 블록, 확장영역, 세그먼트
#데이터 블록
*데이터를 저장하는 가장 작은 단위
*하나의 데이터 블록은 디스크상의 물리적 DB영역의 특정 바이트 수에 해당(2K, 4K, 8K) 테이블스페이스별로 사이즈 결정가능.
*1회 물리적인 디스크 I/O량을 결정
*데이터베이스의 용도(OLTP, DW)에 따라 적절한 사이즈 설정
*데이터 블록 체인을 최소화
*데이터블록의 구성
Header: 블록 주소와 세그먼트 유형 등 블록정보
Row directory: 램 데이터 영역에 있는 각 행 조각의 주소를 포함하여 블록에 있는 실제 행에 대한 정보
Free space: 새로운 행을 삽입하거나 후행 null을 null이 아닌 값으로 갱신할 때와 같이 추가 영역이 필요한 행을 갱신할 때 할당
Row data: 실제 데이터 저장
#데이터 확장 영역(EXTENT)
*특정 유형의 정보를 저장하기 위해 할당된 몇 개의 연속적인 데이터 블록
*데이터 블록의 초기 확장역영을 세그먼트에 할당, 할당된 데이터 블록이 모두 채워지면 새로운 확장영역을 자동 할당
*저장공간 낭비를 줄이고 무한정 확장되는 것을 방지
*데이터를 삭제하여도 확장된 영역을 반환하지 않음. 생성된 객체를 Drop하거나 Truncate해야 테이블스페이스로 반환
#세그먼트(SEGMENT)
*테이블스페이스 내에 어떤 논리적인 구조를 정의하기 위해 할당한 확장 영역의 집합으로, 테이블, 인덱스, 임시용 세그먼트가 지원
*특정 테이블의 데이터 세그먼트를 형성
*특정 테이블의 인덱스 세그먼트를 형성
라.메모리 구조
#DBMS 정보 저장
-실행되는 프로그램 코드
-현재 사용하지 않더라도 접속되어 있는 세션 정보
-프로그램 실행되는 동안 필요한 정보
-프로세스 간에 공유하거나 교환되는 정보(예 : Locking 정보)
-보조 메모리에 영구적으로 저장된 캐시 데이터
#데이터베이스 버퍼(BUFFER)
*데이터 파일로부터 읽어 들인 데이터 블록의 복사본을 저장
-먼저 프로세스에서 요구한 데이터를 버퍼에서 검색
-버퍼에 없으면 데이터파일에서 로드
-전체 테이블을 스캔한 경우 테이블 블록을 버퍼로 읽어들여 LRU목록의 MRU끝에 놓고, 자주사용되는 블록이 남아있도록 신속히 제거
-버퍼크기는 데이터 요구에 대한 적중률에 영향
*버퍼는 더티 목록과 LRU(Least Recently Used) 목록을 저장
#로그 버퍼(LOG BUFFER)
*데이터베이스의 변경 사항 정보를 유지하는 것으로 일반적으로 원형 버퍼를 사용
#공유 풀(SHARED POOL)
*라이브러리 캐시, 딕셔너리 캐시, 제어 구조 등으로 구성. 라이브러리 캐시는 SQL 영역, 저장 SQL 프로시저 영역, 제어 구조 등을 공유하고, 딕셔너리 캐시는 데이터 사전 정보를 공유
#정렬 영역(SORT AREA)
*인덱스를 생성하거나 SQL문에 GROUP BY 연산, ORDER BY가 있을 경우 정렬 작업을 수행
마.프로세스 구조
#사용자 프로세스
*애플리케이션이나 데이터베이스 도구를 실행할 때 생성
#서버 프로세스
*사용자 프로세스와 통신을 하는 역할. 다중 스레드 서버 방식과 단일 서버 프로세스 방식
#백그라운드 프로세스
*데이터베이스가 동작하기 위한 프로세스들로 구성
2.2데이터 액세스
가.실행 구조
-사용자는 데이터베이스를 이용할 수 있는 사용자 인터페이스에서 명령어를 입력하고 DB에 결과를 요청하면 네트워크 서비스를 통하여 DB인스턴스(엔진)에 전달
-전달된 명령어는 문법적인 오류나 의미적인 오류를 확인하고 옵티마이저에 의해 SQL로 요구된 결과를 최소의 비용으로 처리할 수 있는 최적의 처리 경로를 결정하여 실행 계획을 작성
-실행 계획에 의해 DB엔진은 실행 과정을 반복
-사용자에게 전달될 데이터 결과가 있으면 네트워크 서비스를 통하여 정해진 버퍼 사이즈만큼씩 전달 (Fetch)
*실행계획: SQL을 처리하기 위한 실행절차와 방법.
-데이터베이스 엔진이 쿼리에서 요청한 데이터를 검색하고 처리하기 위해 수행하는 단계를 설명하는 로드맵
-조인방법/순서, 엑세스 기법
-대소문자 구분: SQL이 조금이라도 다르면 다른 것임
1)옵티마이저
-SQL의 실행계획을 작성(SQL을 어떻게 처리할 것인가를 결정)
-최적의 실행 계획을 찾는 과정을 최적화
-비용 기준 최적화(CBO): 비용을 우선순위로 실행계획을 작성. 비용 산정은 데이터베이스 내의 데이터들에 대해 갖고 있는 통계 정보와 비용을 예측하는 모델을 이용하여 비용을 계산
-규칙 기준 최적화(RBO):일정한 액세스 방법에 따라 정해진 우선순위로 실행 계획을 작성
2)SQL 실행 단계
#파싱(Parser)
-SQL의 구문과 의미가 정확한지 검사하고, 참조된 테이블에 대해 사용자가 접근 권한을 가지고 있는지를 검사
-라이브러리 캐시에서 같은 SQL 문장이 존재하는지 검색
SQL튜닝: SQL재작성, 힌트사용, 새로운 인덱스추가, 통계 데이터의 추가/갱신
#옵티마이저(Query Optimizer)
-앞에서 넘겨받은 결과 정보(parsed query)를 이용해 최적의 실행 계획을 선택
-실행계획의 실행 순서는 동일 레벨인 경우에 위에서 아래로, 레벨이 다른 경우에는 안에서 바깥으로 처리
#로우 소스 생성(Row Source Generator)
-옵티마이저에서 넘겨받은 실행 계획을 내부적으로 처리하는 자세한 방법을 생성
-로우 소스: 실행 계획을 실제로 구현하는 각 인터페이스를 지칭. 테이블 액세스 방법, 조인 방법, 정렬 등
#SQL 실행(SQL Execution Engine)
-생성된 로우 소스를 SQL 수행 엔진에서 수행한 결과를 사용자에게 돌려주는 과정
-소프트 파싱: 이미 최적화를 한 번 수행한 SQL 질의에 대해 옵티마이저 단계와 로우 소스 생성 단계를 생략
-하드 파싱: 이 두 단계를 새로 수행
나.명령어
-DDL, DML, 제어명령어
1)데이터 정의 언어(DDL, Data Definition)
-스키마 객체를 생성하고, 구조를 변경하고, 삭제, 명칭을 변경하는데 사용. CREATE, ALTER, DROP, RENAME,Truncate
-데이터베이스, 사용자, 테이블, 컬럼, 데이터 타입, 참조 무결성 제약 정의, 영역 무결성 제약 정의, 인덱스 등 관리
- 현재 진행되는 트랜잭션에 대해 암시적으로 COMMIT을 실행
2)데이터 조작 언어(DML, Data Manipulation)
-데이터베이스에 있는 데이터를 조작할 수 있게 해주는 명령어로 DELETE, INSERT, SELECT, UPDATE 등
#DML 처리 단계
*1단계 커서 생성: SQL문에 대해 독립적으로 생성. 모든 SQL문에 사용될 수 있도록 생성
*2단계 명령문 구문 분석: SQL문을 변화하여 유효한 명령문인지 검증
*3단계 질의 결과 설명(SELECT): 데이터 유형, 길이, 이름 등 질의 결과의 특성을 판별
*4단계 질의 결과 출력 정의(SELECT): 질의에 대한 정의 단계에서 위치, 크기, 인출한 각 값을 받기 위해 정의된 변수의 데이터 유형을 지정
*5단계 변수 바인드: 값을 찾을 수 있는 메모리 주소를 지정
*6단계 명령문 병렬화(병렬처리): 다중 서버 프로세스로 하여금 동시에 SQL문의 작업을 수행
*7단계 명령문 실행: SELECT나 INSERT문이면 잠금 불필요. UPDATE, DELETE문에 영향을 받는 모든 행은 트랜잭션에 대한 다음 처리까지 잠금
*8단계 질의 로우 인출(SELECT): 인출 단계에서 행이 선택되고 정렬
*9단계 커서 닫기
3)제어 명령어(Control Statement), DCL, TCL
-트랜잭션 제어문은 1개 이상의 SQL문을 논리적으로 하나의 처리 단위로 적용하기 위해 사용하는 명령어. Commit, Rollback
-세션 제어문은 실행하고 저장하는 SQL문에는 사용할 수 없으며 사용자 세션의 특성을 정의하는 명령어
-시스템 제어문은 시스템이나 데이터베이스 레벨에서 재기동 없이 환경 변수 등을 조정
다.트랜잭션 제어
-한 개 이상의 SQL문을 논리적으로 하나의 처리 단위로 적용하기 위해 사용하는 명령어
-COMMIT: 트랜잭션이 시작되고 현재 시점까지 변경/저장된 모든 데이터를 DB에 영구적으로 반영
-ROLLBACK: 트랜잭션이 시작되고 현재 시점까지 변경/저장된 모든 데이터를 무효화하고 트랜잭
션이 시작되기 전의 상태로 되돌림
-SAVEPOINT: 트랙잭션이 시작되고 데이터의 변경/저장이 진행되는 중간 지점마다 위치를 저장하여 ROLLBACK 명령 수행 시 ROLLBACK 지점을 명시하여 해당 지점까지만 ROLLBACK할 수 있음
2.뷰(View)의 활용
-하나 이상의 기본 테이블(또는 뷰)에서 원하는 데이터를 선택하여, 미리 SQL로 정의하여 놓은 가상 테이블
～기본 테이블로부터 유도된 가상의 테이블이므로 물리적인 저장 공간을 가지지 않음
～물리적인 공간을 가지지 않으므로 인덱스를 생성할 수 없음
~ 정의된 뷰가 기본 테이블의 기본 키를 포함하지 않으면 뷰를 통한 데이터의 입력, 수정, 삭제 작업 불가
～ALTER VIEW 명령문을 이용하여 정의를 수정할 수 없음
~ 기본 테이블로부터 논리적 데이터 독립성을 제공할 수 있음
～이미 정의된 뷰를 이용한 다른 뷰를 정의할 수 있어, SQL의 재사용성을 높일 수 있음
~ 기본 테이블에 대한 권한은 제거하고 제공 가능한 칼럼만 뷰로 제공함으로써 보안성 확보
-특정 DBMS(티베로)는 실체화된 뷰를 제공: 정의된 뷰를 이용하여 실행된 결과를 저장하고 있어 데이터를 저장하는 공간이 존재. 주로 DW에서 성능 향상 목적, OLTP에서 데이터의 복제와 같은 목적
3.사용자 관리
-DCL: 사용자를 생성, 삭제하고 권한을 제어할 수 있는 명령어
～사용자 생성 : CREATE USER
～사용자 삭제 : DROP USER
~ 권한 부여 : GRANT 〈권한 목록〉 TO 〈사용자ID>
～권한 회수 : REVOKE〈권한 목록〉 FROM〈사용자ID>
다.저장 프로시저
-DML은 비절차적 언어(PL/SQL, Transaction-SQL)을 이용해 저장 프로시저 생성
1)저장 프로시저 설계 지침
-높은 응집도와 낮은 결합도를 유지한 설계. 하나의 작업을 중점적으로 완료하도록 프로시저를 정의
2)저장 프로시저의 장점(생성무보메)
-보안: 사용자는 작성자의 권한으로 실행되는 프로시저와 함수를 통해서만 데이터에 액세스하도록 데이터베이스 작업 제한 가능
-성능: 네트워크를 통해 보내야 하는 정보의 양을 현격하게 감소
메모리 할당: 많은 사용자의 실행을 위해 프로시저의 단일 복사본만이 메모리에 로드
-생산성: 프로시저 집합으로 애플리케이션을 설계하여 불필요한 코딩을 피하고 생산성을 증가
-무결성: 애플리케이션의 무결성과 일관성을 향상
라.트리거
-접속된 사용자나 사용되는 애플리케이션에 관계없이 트리거 이벤트가 발생되면 DBMS에 의해 암시적으로 실행
1)트리거 사용
-파생값,무결성제약,권한,업무규칙,이벤트로깅,감사,테이블복제,엑세스통계
-자동적으로 파생된 열 값 생성(예 : 합계, 잔액, 재고량 등)
-잘못된 트랜잭션 방지(예 : 무결성 제약 구현)
-복잡한 보안 권한 강제 수행
-분산 데이터베이스의 노드상에서 참조무결성 강제 수행
-복잡한 업무규칙 강제 수행
-이벤트 로깅 작업이나 감사 작업
-동기 테이블 복제 작업
-테이블 액세스에 대한 통계 수집
2)트리거 유형
#행 트리거 및 명령문 트리거
-행 트리거: 테이블이 트리거링 명령문에 의해 영향을 받을 때마다 실행
-명령문 트리거: 테이블에서 트리거링 명령문에 의해 영향을 받는 행 수에 관계없이 한 번 실행
#BEFORE 및 AFTER 트리거
-BEFORE 트리거는 명령문이 실행되기 전에 트리거 작업을 실행
-AFTER 트리거는 명령문이 실행된 후에 트리거 작업을 실행
3)트리거링 이벤트와 제한 조건
2.3트랜잭션
- 더 이상 나눌 수 없는업무 처리 단위( 자기완결성)
가.트랜잭션 관리
-트랜잭션은 하나의 논리적 작업 단위를 구성하는 하나 이상의 SQL문으로 구성
-Commit: 실행한 논리적 작업 단위 전체가 성공적으로 종료되면 그 트랜잭션은 영구적으로 데이터베이스에 저장
-Rollback: 실행한 SQL 중 하나라도 정상 종료되지 않으면 논리적인 작업 단위 전체를 이전 상황으로 회복
-다중 사용자 환경에서 트랜잭션은 동시성 제어와 고장 회복(Recovery) 기법에 의하여 관리
나.트랜잭션 특징(ACID)
원자성(ATOMICITY): 트랜잭션은 완전히 수행하거나 전혀 수행되지 않은 상태로 회복
일관성 유지(CONSISTENCE): 트랜잭션을 실행하면 데이터베이스를 하나의 일관된 상태에서 또 다른 일관된 상태로 변경
고립성(ISOLATION): 하나의 트랜잭션은 완료될 때까지 자신이 갱신한 값을 다른 트랜잭션들이 보게 해서는 안됨
영속성(DURABILITY): 한 트랜잭션이 DB를 변경시키고 그 변경이 완료되면 결과는 이후의 어떠한 고장에도 손실되지 않아야 함. 회복기법으로 보장
다.트랜잭션의 일관성
-트랜잭션 수준 읽기 일관성은 트랜잭션이 시작된 시점을 기준으로 일관성 있게 데이터를 읽는 특성
-다른 트랜잭션에 의한 변경사항은 무시. 자신이 발생시킨 변경사항은 읽어야 함
1)낮은 단계 트랜잭션 고립화 수준에서 발생할 수 있는 현상들
#DIRTY READ(= UNCOMMITTED DEPENDENCY)
-다른 트랜잭션이 변경 중인 데이터를 읽었는데, 그 트랜잭션이 최종 롤백됨으로써 현재 트랜잭션이 비일관성 상태에 놓이는 현상
#NON-REPEATABLE READ(= INCONSISTENT ANALYSIS)
-한 트랜잭션 내에서 같은 쿼리를 두 번 수행할 때 그 사이에 다른 트랜잭션이 값을 수정 또는 삭제 함으로써 두 쿼리의 결과가 상이하게 나타나는 현상
#PHANTOM READ
-한 트랜잭션 내에서 같은 쿼리를 두 번 수행할 때 첫 번째 쿼리에서 없던 유령 레코드가 두 번째 쿼리에서 나타나는 현상
2)트랜잭션 고립화 수준(Transaction Isolation Level)
#레벨 0(= READ UNCOMMITTED), DNP
-트랜잭션에서 처리 중인 아직 커밋되지 않은 데이터를 다른 트랜잭션이 읽는 것을 허용
-Dirty Read, Non-Repeatable Read, Phantom Read 현상 발생
#레벨 1(= READ COMMITTED), NP
-트랜잭션이 커밋되어 확정된 데이터만 읽는 것을 허용
- Non-Repeatable Read, Phantom Read 현상 발생
#레벨 2(= REPEATABLE READ), P
-선행 트랜잭션이 읽은 데이터는 트랜잭션이 종료될 때까지 후행 트랜잭션이 갱신하거나 삭제하는 것을 불허
-Phantom Read 현상 발생
#레벨 3(= SERIALIZABLE READ)
-선행 트랜잭션이 읽은 데이터를 후행 트랜잭션이 갱신하거나 삭제하지 못할 뿐만 아니라 중간에 새로운 레코드를 삽입하는 것도 불허. 완벽한 읽기 일관성 제공
라.동시성 제어
-다수의 사용자가 데이터베이스에 동시에 접근하여 같은 데이터를 조회 또는 갱신을 할 때 데이터 일관성을 유지하기 위한 일련의 조치
-갱신손실, 모순판독문제를 방지하기 위함
#갱신손실(Lost Update)
-트랜잭션A와 트랜잭션B가 동시에 값을 읽어들인 후 수정하면 값이 두 트랜잭션 중 하나가 처리한 값이 되어 나머지 한 트랜잭션의 값이 반영되지 않는 현상
#모순 판독 문제
-한 트랜잭션이 값을 갱신중인데 다른 트랜잭션이 값을 읽어서 합계를 계산
1)낙관적 동시성 제어
-사용자들이 같은 데이터를 동시에 수정하지 않을 것이라고 가정
-읽는 시점에는 잠김을 사용하지 않지만 데이터를 수정하고자 하는 시점에 앞서 읽은 데이터가 다른 사용자에 의해 변경되었는지를 반드시 검사
-데이터를 읽을 때는 잠김을 설정하지 않음. 잠김이 유지되는 시간이 매우 짧아져 동시성을 높이는데 유리
2)비관적 동시성 제어
-사용자들이 같은 데이터를 동시에 수정할 것이라고 가정
-한 사용자가 데이터를 읽는 시점에 잠김을 걸고 조회 또는 갱신 처리가 완료될 때까지 이를 유지
-시스템 동시성이 심하게 떨어질 수 있으니 wait 또는 nowait 옵션을 함께 사용
-Locking, Timestamp Ordering
마.동시성 제어기능
-Locking, 2PC, Timestamp 등의 기법
-잠김은 암시적인 잠김 (Implicit Locking)과 명시적인 잠김(Explicit Locking)
#잠김 단위(LOCK GRANULARITY 또는 ISOLATION LEVEL)
-잠김 단위는 잠김 대상의 크기를 뜻하며, 단위가 커지면 관리해야 하는 대상의 수가 적어지므로 DBMS가 관리하기 쉬워지지만 동일한 잠김 대상에 동시 액세스할 확률이 높아져 충돌이 자주 발생
-잠김의 단위가 작아지면 관리해야 하는 대상의 수가 많아져 관리하기는 어려워지지만 동일한 잠김 대상에 동시 액세스할 확률이 낮아져 충돌 횟수는 적어짐
#잠김 확산(LOCKING ESCALATION)
-관리해야 하는 잠금 단위의 개수가 미리 설정한 임계치에 도달하게 되면 잠김의 단위를 현재 관리하고 있는 단위보다 하나 높은 수준으로 올리는 기능
#잠김(LOCKING)의 유형
-읽기 작업에서는 공유 잠금(Shared lock)을 필요로 하고 쓰기 작업에서는 배타적 잠금(Exclusive lock)
RS: row share(or SS: sub share)
RX: row exclusive(or SX: sub exclusive)
S: share
SRX: share row exclusive(or SSX: share/sub exclusive)
X: exclusive
#2PC(2 PHASE COMMIT)
-2개 이상의 트랜잭션들이 병행적으로 처리되었을 때의 데이터베이스 결과는 그 트랜잭션들을 임의의 직렬적인 순서로 처리했을 때의 결과와 논리적으로 일치해야 함.
-병렬로 수행되는 트랜잭션의 직렬 가능성을 보장하는 방법
-트랜잭션은 Locking을 거는 성장 단계(Growing Phase)와 Locking을 푸는 축소 단계(Shrinking Phase)의 2단계로 구성. 분산DB에서도 동일
#교착 상태(DEAD LOCK)
-다른 사용자가 잠근 자원이 해제되기를 기다리면서 자신이 잠근 자원을 해제하지 않는 상태
-상호배제: 어느 자원에 대해 한 프로세스가 이미 사용 중이면 다른 프로세스는 기다림
-점유와 대기: 한 프로세스가 자원을 할당받은 채로 다른 프로세스가 할방받은 자원이 해제되기를 기다림
-비선점: 자원을 할당 받은 프로세스로부터 자원을 빼앗지 못함
-환형대기: 자원할당 그래프 상에서 환형 사슬이 존재
바.동시성 구현 사례
-선분이력:
사.고장 회복
-트랜잭션 처리 중 장애가 발생했을 경우 데이터를 트랜잭션이 시작되기 이전 상태로 회복. UNDO로 롤백
아.잠김 지속 시간(Locking duration)
-Locking에 의한 경합은 식별자 번호를 얻기 위한 채번 로직에서 많이 발생. 따라서 채번은 트랜잭션 종료 시점에 실시하여 locking duration을 최소화하거나 시퀀스나 데이터 타입으로 자동 번호 발생 객체를 사용
#채번 설계
-오브젝트나 데이터 타입을 이용하여 자동으로 발생하는 DB기능을 이용하면 잠김 현상에 의한 지연 최소화
-잠김지속시간 단축을 위해 장기 트랜잭션을 최소화
-채번 테이블은 최적화된 액세스를 위해서 인덱스 전략이 필요.
2.4백업 및 복구
-전산 장비의 고장이나 사고에 대비하여 주기적인 백업을 실시하고 장애의 원인을 해결한 후 데이터베이스를 복구해야함
가.장애유형
#사용자 실수: 테이블의 삭제하거나 잘못된 트랜잭션 처리로 데이터의 일관성에 문제가 되는 경우
#미디어 장애: 하드웨어 장애는 CPU, 메모리, 디스크 등 다양하게 발생. 하드웨어 장애는 하드웨어 교체로 문제를 해결할 수 있지만 미디어 장애는 데이터가 파손되는 장애
#구문 장애: 프로그램 오류나 사용 용량이 부족하거나 여유 공간이 부족하여 발생하는 장애
#사용자 프로세스 장애: 사용자 프로그램이 비정상 종료되거나 네트워크 이상으로 세션이 종료되므로 발생
#인스턴스 장애: 시스템이 비정상적인 요인으로 메모리나 데이터베이스 서버 프로세스가 중단된 경우
나.로그 파일
#로그파일 기록 시기
-트랜잭션 시작시점
-데이터의 입력, 수정, 삭제 시점
-트랜잭션 rollback 시점
#로그 파일 내용
:트랜잭션이 발생할 때마다 COMMIT이나 ROLLBACK에 관계없이 모든 내용을 기록
-트랜잭션 식별자
-트랜잭션 레코드
-데이터 식별자
-갱신 이전 값(Before Image)
-갱신 이후 값(After Image)
#로그 파일 보관
로그 파일은 로그 버퍼 내용을 Flat-file 형식으로 기록. 로그 파일을 저장 매체(테이프)로 영구 보관
다.데이터베이스 복구 알고리즘
-동기적 갱신(Synchronous I/O): 트랜잭션 실행 내용인 DB버퍼를 저장매체에 동기적으로 기록하는 기법.
-비동기적 갱신(Asynchronous I/O): 트랜잭션이 완료된 내용을 일정 시간이나 작업량에 따라 시간 차이를 두고 DB버퍼 내용을 저장 매체에 기록하는 기법
#NO-UNDO/REDO
-데이터베이스 버퍼의 내용을 비동기적으로 갱신. 트랜잭션이 성공적으로 수행되어 완료점에 도달할 때까지 DB 변경 내용이 기록되지 않음.
-트랜잭션이 완료된 후 DB버퍼에 기록되고 저장 매체에 기록되지 않는 상태에서 시스템이 파손되었다면 트랜잭션의 내용을 재실행
#UNDO/NO-REDO
-DB 버퍼의 내용을 동기적 갱신. 트랜잭션이 완료되기 전에 DB버퍼 내용을 모두 동시적으로 기록하므로 완료된 트랜잭션들은 어떤 연산도 재실행할 필요가 없음
-트랜잭션들이 완료되기 이전에 시스템 파손이 발생할 경우 변경된 내용을 취소
#UNDO/REDO
-데이터베이스 버퍼의 내용을 동기/비동기적으로 갱신할 경우 모든 갱신이 DB에 기록되기 전에 트랜잭션이 완료될 수 있으므로 완료된 트랜잭션이 DB에 기록되지 못했다면 재실행
#NO-UNDO/NO-REDO
-DB 버퍼 내용을 동시적으로 저장 매체에 기록하나 DB와는 다른 영역에 기록 하는 경우
-항상 트랜잭션의 실행 상태와 데이터베이스의 내용이 일치하므로 데이터베이스 버퍼의 내용을 취소하거나 재실행할 필요가 없음
-복구작업은 DB오픈전인 마운트 상태에서 진행
라.백업 종류
-물리백업: 로그파일 백업 실시(완전복구), 로그파일 백업 없음(백업시점까지 복구)
-논리백업: DBMS 유틸리티
마.데이터베이스 백업 가이드 라인
#정기적인 Full-backup
#DB 구조 변화 전후 Full-backup
-테이블 스페이스의 생성 또는 삭제
-테이블 스페이스에 데이터 파일을 추가하거나 변경했을 때
-Log 파일을 변경했을 때
-Archive Log Mode로 전환 시 Control 파일만이라도 백업
-No-archive log mode로 전환할 때는 Full backup
#Read-write 수행이 많은 테이블스페이스는 자주 온라인 백업
#백업 파일은 2본 이상을 보유. Incomplete Recovery를 용이하게 함
#논리 백업은 특정 데이터 또는 특정 테이블 오류 시 복구가 용이함
#분산 데이터베이스는 동일 모드에서 백업을 수행함
#Read-only테이블스페이스는 온라인 백업 불필요
#로그파일 없이는 완벽한 복구 불가능.
#논리백업에 의한 복구는 백업시점상태로 데이터를 생성하는 것으로 UNDO/REDO 작업이 없음
3.데이터베이스 성능 개선
3.1성능 개선 방법론
가.성능 개선 목표
-처리능력, 처리시간, 응답시간, 로드 시간
#처리 능력(THROUGHPUT)
-해당 작업을 수행하기 위해서 소요되는 시간으로 수행되는 작업량을 나눔으로써 정의
처리능력 = 트랜잭션 수 / 시간
#처리 시간(THROUGHPUT TIME)
-작업이 완료되는데 소요되는 시간
*배치작업 수행시간 단축을 위한 고려사항
-병행 처리
-인덱스 스캔보다 Full 테이블 스캔
-Nest-Loop 조인보다 Hash 조인
-대량 작업을 하기 위한 SORT_AREA, HASH_AREA 의 메모리 확보
-병목을 없애기 위해서 작업 계획
-대형 테이블인 경우는 파티션으로 생성
#응답 시간(RESPONSE TIME)
-입력을 위해 사용자가 키를 누른 때부터 시스템이 응답할 때까지 시간
*응답시간 향상을 위한 고려사항
-인덱스를 이용하여 액세스 경로를 단축
-부분범위 처리를 실시
-Sort-Merge 조인이나 Hash 조인을 사용하지 않고 Nest-Loop 조인으로 처리
-잠김(Locking) 발생을 억제. 예:시퀀스 오브젝트 이용
-하드 파싱을 억제(옵티마이저, 로우 소스 생성 단계 생략)
#로드 시간(LOAD TIME)
-정기적이거나 비정기적으로 발생되는 DB에 데이터를 로드하는 작업 수행 시간
*로드시간 단축을 위한 고려사항
-로그 파일을 생성하지 않는 Direct Load를 사용
-병렬 로드 작업을 실시.
-DISK IO 경합이 없도록 작업을 분산
-인덱스가 많은 테이블인 경우는 인덱스를 삭제하고 데이터 로드 후 인덱스를 생성
-파티션을 이용하여 작업을 단순화
나.성능개선 절차
1)분석
#자료수집
-DB 모니터링
-데이터베이스 객체 현황 파악
-물리 설계 요소에 대해 성능과 관련된 지표
#목표설정
-수집된 기초 자료를 통해 데이터 모델 분석, 액세스 패스 분석, 시스템 자원 현황 분석, SQL 성능 분석, SQL 효율 분석 등을 종합하여 성능상에 병목이나 지연 등과 같은 문제 요소 등을 구체적으로 파악하고 성능 튜닝의 대상이 되는 목표들을 구체화하여 방향을 설정
2)이행
-성능상의 문제 요소로 파악된 대상에 대해 최적화 방안을 수립하고 적용
-DB 파라미터 조정
-전략적인 저장 기법 적용을 위한 물리 설계 및 디자인 검토
-비효율적으로 수행되는 SQL 최적화
-네트워크 부하 등을 고려한 DB분산 구조 최적화
-적절한 인덱스 구성 및 사용을 위한 인덱스 설계 등의 최적화 작업
3)평가
-분석 단계에서 진단을 통해 분류된 문제 요소들에 대해 설정된 개선 목표와 이행단계에서 구체적인 튜닝 작업을 수행한 후의 성과를 비교 측정
다.성능 개선 접근방법
-SQL(애플리케이션)>DB Object> DBMS Instance(메모리, 프로세스)> DBMS 환경(서버, 디스크)
-DB와 애플리케이션(SQL)의 비효율로 발생
-문제점의 튜닝을 통한 DB 최적화
-문제가되는 SQL을 튜닝
라.SQL 성능 분석
-실행 계획: SQL이 실행되기 위해서는 실행 가능한 소스코드로 변환되어야 하는데 이 소스코드를 생성하기 위한 실행 전략을 말함
-옵티마이저는 실행 계획을 수립하기 위해 SQL을 관계 대수 형태로 변환하고 개별 연산자의 알고리즘을 다양한 방법으로 결합해 이들을 트리 형태로 표현
1)실행계획분석사례
마.성능 개선 도구
-DBMS에서는 서버 상태, Trace, Dictionary 정보, 실행 계획을 제공
*SQL Trace 분석
SQL 수행시간:
-total cpu / total count = 0.016 / 9 = 0.0013
-total elaspse / exectue count = 0.203 / 7 = 0.029 (0.03 초 이내)
parse 오버헤드: parse elapsed / execute elapsed = 0.147 / 0.015 = 0.01 (10% 이내)
Block I/O : total disk / total rows = 58 / 560 = 0.1 (1미만)
Disk 병목: total elpased / total cpu = 0.203 / 0.016 = 0.01, 100% 이상이면 병목현상(풀스캔)
Array Processing: fetch rows / fetch count = 560/7 = 80, 1보다 큰 10배수면 O, 1에 가까우면 X
recursive overhead: recursive execute / non-recursive execute
#call : 커서 상태에 따라 Parse, Execute, Fetch 세 개의 Call로 나누어 각각에 대한 통계정보를 보여준다.
-Parse : 커서를 파싱하고 실행계획을 생성하는데 대한 통계이다.
-Execute : 커서의 실행 단계에 대한 통계이다.
-Fetch : 레코드를 실제로 Fetch하는 데 대한 통계이다.
#count :: Parse, Execute, Fetch 각 단계가 수행된 횟수이다.
#cpu :: 현재 커서가 각 단계에서 사용한 cpu time이다.
#elapsed :: 현재 커서가 각 단계를 수행하는 데 소요된 시간이다. Call 단위로 측정한다.
Elapsed Time = CPU Time + Wait Time = Response시점 - Call 시점
-하나의 SQL을 수행할 때 Total Elapsed Time은 수행 시 발생하는 모든 Call의 Elapsed Time을 더해서 구한다.
#disk : 디스크로부터 읽은 블록 수
#query : Consistent 모드에서 읽은 버퍼 블록 수
#current : Current모드에서 읽은 버퍼 블록수
#rows :: 각 단계에서 읽거나 갱신한 처리건수
3.2조인(Join)
-카티션 프로덕트를 수행 후 셀렉션과 프로젝션을 수행하는 데이터베이스의 연산
-Nested loops 조인, Sort merge 조인, Hash 조인. Hybrid 조인, Star 조인과 Semi 조인
-조인 조건이 (=)로 정의되었다 면 equi 조인이라 하며, >=, <=, between 등이 조인 조건에 기술되었다면 between 조인
-조인의 결과가 Outer 집합에 의하여 결정되는 Outer 조인(합집합)과 Inner 집합에 의하여 결정되는 Inner 조인(교집합)
-Oter 집합: 바깥쪽 루프에서 엑세스 되는 집합
-Inner 집합: 안쪽 루프에서 엑세스되는 집합
-Inner Join = B
-Outer Join = A
-드라이빙테이블: 먼저 액세스되는 테이블, outer table
-드리븐테이블: 나중에 액세스되는 테이블, inner table
가.Nested-Loop 조인
-조인 연산은 두 집합을 카티션 프로덕트 형태로 모든 튜플을 열거한 다음, 조인에 만족하지 않는 튜플을 제거하는 두 가지 알고리즘으로 구성
-Outer table처리범위에 따라서 Inner 테이블 처리 범위 결정
-처리하는 데이터 양이 많을 경우 과도한 Random I/O 엑세스 발생.
-적은 규모의 데이터 조인에 유리하기 때문에 OLTP환경에서 비교적 많이 사용.
-조인칼럼의 인덱스 유무가 조인의 순서와 성능에 영향
-결과를 하나씩 받아서 순차적으로 조인하는 형태이므로 부분범위 처리 가능
#조인조건
-Indexed nested-loop 조인으로 수행되려면 Inner 테이블에 조인 조건으로 사용된 칼럼에 반드시 인덱스가 존재해야함
-드라이빙 행수에 따라서 조인 행수가 결정됨.
#조인순서
-옵티마이저는 조인 시 먼저 드라이빙 테이블을 결정하고 나머지 집합의 조인 순서를 결정
-드라이빙 테이블은 드라이빙의 범위가 가장 작은 집합, 즉 가장 작은 작업량을 가지고 드라이빙할 수 있는 집합을 선정하고 조인의 순서를 정할 때에는 조인의 효율이 좋은 집합을 먼저 조인에 참여
나.Sort-Merge 조인
-조인하려는 두 집합을 조인 속성으로 정렬 sorted lists를 만든 후 이들을 merge하는 조인
-동등 조인(=, equi), 비동등 조인에서도 사용이 가능.
#출력 및 연결 순서
-정렬된 결과를 순차적으로 비교하여 머지를 수행하므로 Outer 집합의 정렬 순서와 Inner 집합의 정렬 순서가 머지되어 출력
#조건절
-정렬에 참여하는 로우의 수에 의해 수행 속도 결정
#이용
-독립적으로 처리 범위를 줄인 후 조인에 참여하므로 테이블 각각의 조건에 의해 대상 집합을 줄일 수 있을 때 유리
-처리 대상이 전체 테이블일 때 랜덤 IO 부하가 큰 Nested-Loop 조인보다 유리
-조인 속성에 인덱스가 없을 때 Simple Nested-Loop 조인보다 성능이 우수
-효과적인 수행을 위해서는 적정한 SORT AREA 사이즈 확보 필요. Sort Area Size가 부족하면 디스크에서 정렬작업 수행하기 때문에 많은 오버헤드 발생.
-정렬에 대한 부하가 많이 발생하므로 대용량 처리 시 수행 속도 저하 가능.
다.Hash 조인
-관계형 데이터베이스에서 비용이 가장 많이 들어가는 조인 방법이지만 대용량의 데이터 조인 시에 Sort-merge 조인이나 Nested-Loop 조인보다 더 좋은 성능
-조인 컬럼에 인덱스가 없어서 Natural Join이 비효율일때
-Natural Join시 드라이빙 집합 쪽으로 조인 엑세스량이 많아 Random 엑세스 부하가 심할때
-equi join 조건이 하나라도 있어야 함.
- 랜덤액세스 없음
-정렬작업 없음
-조인에 참여한 두 집합 중에서 작은 집합의 해쉬 테이블을 메모리상에 만들고 큰 집합은 조인을 위해 해쉬 테이블을 탐침
-아주 큰 테이블과 아주 작은 테이블의 조인에 적합하며 Hash Area Size에 따라 성능 편차가 심함
#조인 수행 단계
STEP 1 - 파티션 수: 파티션의 개수가 많으면 I/O 효율 떨어짐
STEP2 - 해쉬 함수: 조인에 참여한 두 집합 중에서 작은 집합을 드라이빙 테이블로 선택하여 메모리로 읽어 들임. 해쉬되는 로우가 파티션에 골고루 분산되게 하기 위해 해쉬 함수1을 사용하여 해당 파티션에 매핑하고, 해쉬 함수2를 사용하여 생성된 해쉬 값은 다음 단계를 위하여 조인키와 함께 저장
STEP3 - 비트맵 벡트: 비트맵은 2차원 버켓으로 이루어져 있으며 해쉬 함수 1,2를 통과하여 생성
STEP 4 - 디스크에 파티션 쓰기: Hash area가 모두 차면 가장 큰 파티션이 디스크로 내려감
STEP5 - 메모리에 적재 가능한 최대 파티션
최대 파티션 수를 정하고 크기순으로 파티션을 정렬
파티션 번호가 가장 작은 것부터 선택하여 메모리에 로드
나머지는 디스크에 저장
STEP6 - 작은 집합의 해시 테이블 생성: 이미 생성된 해쉬 값을 사용하여 해시 테이블을 생성
STEP7 - 비트 벡터 필터링: 조인 수행 시 비트맵과 대조하여 1이면 조인을 해야 하는 로우로 인식하고 아니면 조인이 필요 없는 로우이므로 버림
STEP8 - 처리되지 않은 파티션 쌍을 디스크로부터 읽어오기: 큰 집합의 파티션이 끝나면 작은 집합의 수행되지 않은 파티션들이 최대한 메모리로 올려지고 해쉬 테이블이 생성. 그에 대응되는 큰 집합의 파티션이 다시 읽혀져 메모리 조인이 실행
라.Hybrid 조인
-Nested-Loop 조인과 Sort-merge 조인의 알고리즘을 혼합
-Inner 테이블의 인덱스를 탐침할 때마다 테이블을 검색하는 것이 아니라 Inner 테이블의 인덱스를 탐침한 후 그 결과를 중간 집합으로 생성하여 중복이 없는 Rowid 리스트를 만들고, 그 결과를 가지고 Inner 테이블의 페이지를 탐침하는 조인 방식
*Star 조인
-여러 개의 작은 테이블을 Cartesian product 조인한 결과와 대용량 테이블과 조인
-DW환경에서 큰 팩터 테이블과 아주 작은 디멘전 테이블이 조인할때 유리
-랜덤 엑세스 감소
-/*+ start */ 힌트 사용
-조인조건은 연산자로 표시하기도 하는데 조인조건이 등호(=)로 정의되어 있다면 Natural 조인을 의미
-조인 조건으로 =, <=, >=, between 등의 연산자 모두 사용 가능
-Inner join(교집합)은 inner table에 조인 조건을 만족하는 집합이 존재하는 경우에만 결과집합에 참여
-Outer join(합집합)은 Outer table에 존재하는 집합중에서 Inner table join 조건을 만족하는 집합이 존재하지 않더라도 결과집합에 참여
3.3애플리케이션 성능 개선
가.온라인 프로그램 성능 개선
-성능 개선 목표는 응답시간 단축
#온라인 프로그램의 특징
-화면 조회가 가능할 정도로 1회 조회 데이터가 소량
-신속한 트랜잭션 처리가 요구
-조회 조건이 단순
-업무 형태에 따라 데이터 액세스 패턴이 고정됨
*온라인 프로그램 성능 개선 작업 고려사항
-사용 빈도가 높은 SQL 문을 개선
-인덱스를 이용하여 데이터 액세스 범위를 감소
-부분범위 처리로 응답 시간 단축
-부분 범위 처리를 하기 위해서 Nested-Loop 조인과 인덱스를 이용한 정렬을 유도
-장기 트랜잭션 처리를 억제
#부분범위처리
-논리적으로 전체범위를 읽지 않고 결과를 얻을 수 없는 경우를 제외하고 부분범위처리 가능
-UNION을 포함한 SQL은 전체범위 처리를 통해서만 결과를 얻을 수 있음
-ORDER BY를 사용하여도 동일한 순서의 인덱스가 존재하면 부분범위 처리로 수행
-조회조건이 여러 테이블에 분산되어 있거나 정렬 또는 집계가 필요한 경우에는 부분범위 처리가 어려움
나.온라인 프로그램의 성능 개선 사례
1)상수 바인딩에 의해 발생되는 파싱 부하
:상수는 하드 파싱, 변수는 소프트 파싱.
2)웹게시글 형태의 인터페이스 시 부분 범위 처리
3)과다한 함수 사용으로 인한 부하 발생
다.배치 프로그램의 성능 개선
- 처리시간의 최소화에 가장 큰 목적
-전체 처리 시간을 개선하기 위해 전체 범위 처리, 병렬처리, 조인 방식(Nested-loop조인을 Hash join으로 변경) 등의 개선 필요
-자원의 경합 감소를 위해서 작업계획을 실시
#배치 작업 서버 이슈
-절대 수행 시간 부족
-수행 결과 검증 시간 확보의 어려움
-오류에 따른 재처리 시간 확보가 불가능
-미완료 시 대안 제시에 어려움
-미처리 또는 지연으로 파급되는 문제 해결에 장시간 소요됨
-일배치 작업은 익일 영업시간 전에 월배치 작업은 익월전에 작업이 완료되어야 Racing현상이 발생하지 않음.
-작업계획 시 오조작이나 장애 등을 대비하여 여유 수행시간을 확보해야 함
-재작업에 따른 절대시간이 필요함
1)절차적인 처리 방식의 비효율
-SQL이 루프 내에서 반복적으로 수행되는 구조이므로 DBMS call이 과도하게 발생.
-단위 SQL이 반복적으로 수행되는 구조이므로 Random I/O 발생이 증가
-동일 데이터를 반복해서 읽음
-업무 규칙을 절차적으로 구성하였기 때문에 업무 규칙이 변경되면 프로그램 구조의 수정이 불가피.
-다수의 단위 SQL로 구성되어 있어 개별적인 단위 SQL의 개선만 가능.
2)절차적인 처리방식의 보완 요소
-이중 커서 사용을 하지 않고 조인을 이용하여 단일 커서를 사용
-동일 모듈내에서는 같은 데이터를 2회 이상 읽지 않게 프로그램을 구조화
-최소한 개별적인 SQL 단위 비효율을 제거
3)집합적인 처리방식의 고려사항
-집합적 처리: 사용자가 기술한 SQL을 1번의 DBMS Call로 Result Set을 생성하는 DBMS 연산
-SQL 작성 후 원하는 방식으로 실행 계획이 수립되었는지 반드시 확인
-대량 배치 처리와 같이 대용량 데이터를 처리해야 하는 경우는 Hash 조인이 유리
-분포도가 나쁘면 Random IO 비효율이 급속도로 증가하므로 인덱스 스캔보다 Table full scan 이 유리
-대용량 데이터 처리 시 병렬처리를 사용하여 처리 시간을 단축
-Hash 조인이나 집계를 위한 정렬작업을 고려하여 추가 메모리를 세션에 할당
-집합 처리에 의한 작업은 병행 처리, Full scan, 메모리 영역 확보 등으로 짧은 시간에 DB의 자원을 확보하여 처리
-작업의 종속성을 고려하여 배치 프로그램 작업 계획을 수립하여 자원의 경합을 낮춤.
4)분석 함수를 통한 성능 개선 방안
-업무 분석 요구를 수용하기 위해 포인터와 오프셋의 개념을 추가시킨 다양한 분석 함수의 다양한 기능을 포함
*장점
-쿼리 속도 향상
-셀프조인, 절차적 로직을 Native SQL에서 바로 적용하여 Join 및 프로그램 오버헤드 감소
-유지보수 용이, 생산성 향상
#오라클함수
-RANKING FAMILY:대상 집합에 대하여 특정 칼럼(들) 기준으로 순위나 등급을 매기는 분석 함수 그룹
-WINDOW AGGREGATE FAMILY:현재 행을 기준으로 지정된 window 내의 행들을 대상으로 집단화 (aggregation)를 수행하여 여러 가지 유용한 집계 정보(running summary, moving average 등)를 구하는 분석 함수군
-REPORTING AGGREGATE FAMILY: 서로 다른 두 가지의 집계 레벨을 비교하고자 하는 목적으로 사용하는 분석 함수군
-LEAD/LAG FAMILY:서로 다른 두 행 값을 오프셋으로 비교하기 위한 분석 함수응
-Rollup: 시간 및 지역처럼 계층적 분류를 포함하고 있는 데이터의 설계에 적합
-Cube: Group by의 확장. 디멘전 그룹에 대해 가능한 모든 값에 대하여 Cross-tabulation Values를 생성
5)파티션 스토리지 전략을 통한 성능 향상 방안
-파티셔닝은 대용량의 큰(지속적으로 증가하는) 테이블을 파티션이라는 보다 작은 단위로 나눔으로써 성능이 저하되는 것을 방지하고 관리를 보다 수월하게 하고자 하는 개념
-컬럼단위 수직 파티션, 범위 파티션(Range)
-데이터 액세스 시에 파티션 단위로 액세스 범위를 줄여 I/O에 대한 성능 향상
-여러 분할 영역으로 나눔으로써 전체 데이터의 훼손 가능성이 감소하고 데이터의 가용성이 향상
-각 분할 영역을 독립적으로 백업하고 복구
-디스크 스트라이핑으로 I/O 성능을 향상
3.4서버 성능 개선
가.객체 튜닝
-테이블, 인덱스, 세그먼트에 관련한 사항이 대상
-객체는 성능을 고려하여 설계
-저장 장치를 이루는 블록, 확장 영역, 세그먼트에 관련된 사항을 튜닝
-인덱스는 삭제, 갱신으로 스큐 현상이 심한 경우는 재구성 작업이 필요
-I/O 병목이 발생하지 않게 물리적인 배치를 실시
나.인덱스 튜닝
-메모리 부분과 프로세스가 튜닝 대상
#메모리
-메모리는 Buffer 캐시, library 캐시 등의 히트율에 의해서 평가하여 조정
-Sort area, Hash area는 스와핑 발생 여부에 따라 사이즈를 결정
#프로세스
대부분의 DBMS가 다중 프로세스 시스템이고 필요에 따라서 추가적인 프로세스 가동이 가능
#LATCH 경합
-트랜잭션 처리를 위한 경합이 발생
-객체 생성이나 변경 등으로 경합이 발생 가능
다.환경 튜닝
-하드웨어나 운영체제 관점에서의 튜닝
#CPU
CPU 사용율:성능을 평가하기 위한 기준
-SAR(System Activity Report)로 모니터링 했을 때 CPU 사용이 %usr > %sys > %wio 순으로 되는 것이 바람직
-%idle이 일반적으로 20~30%을 유지하는 것이 바람직하며, 0%인 상태가 지속적으로 유지되면 증설 고려
-Sun - ps, HP - top 또는 glance, IBM - monitor 등의 프로세스별 모니터링이 가능
#Memory 튜닝
-Paging(page-in, page-out)과 프로세스 단위의 Paging 현상인 Swapping 발생 상태를 확인
-DBMS를 포함한 사용자 사용 메모리 크기가 전체 크기의 40 ~ 60%를 유지하는 것이 바람직
#I/O 튜닝
-데이터베이스 병목은 I/O에 의해서 발생
-물리적인 디스크와 디스크 채널을 분산하므로 성능을 개선 가능
-읽기/쓰기 작업에 따른 분산이 필요
-Block (File System)보다는 Raw Device가 I/O 성능에 유리
-고가용성을 위한 시스템구성, RAID구성, 버전 등에 따른 패치 적용이 정상적이지 않을 경우 성능상 결정적인 영향을 줄 수 있음
-DB 가 사용하는 메모리 영역에 따라서 Hit율을 일정수준 이상으로 유지한다면 메모리 용량에 따른 성능 개선은 영향이 없음
#네트워크 튜닝.
-대용량 데이터 전송이 빈번하다면 전송속도와 대역폭 증가 고려
#튜닝계획
1단계: Access Path를 조사하여 인덱스 디자인 실시
2단계: 실행계획을 이용한 SQL 튜닝
3단계: Trace를 이용한 SQL 튜닝
4단계: 통합테스트를 통한 성능검증 및 튜닝
-온라인 프로그램은 응답속도 단축이 목표
-온랑인 프로그램은 효과적인 인덱스 디자인없이 성능향상 불가능
-잠재된 문제 SQL을 튜닝하기 위해서 Trace분석에 의한 접근방법이 효과적
-온라인 프로그램은 실행횟수가 많은 프로그램부터 튜닝을 진행하는 것이 전체 시스템의 성능 향상
#성능개선작업 목표설정 기준
-SQL 처리시간
-응용프로그램에서 측정된 DB 응답시간
-데이터 이행을 위한 로드 시간
#성능 개선 방법
-성능 개선 목표에 따라서 개선대상과 범위를 설정. 개선대상은 트레이스 기능이나 모니터링 도구를 이용하여 실행시간, 실행횟수, 대기시간, 발생 I/O 등을 고려하여 시스템 자원을 많이 사용하는 순으로 선정.
-가장 많이 실행되는 SQL문
-트랜잭션 처리에 의한 지연현상도 개선 대상
-SQL 성능 개선은 처리 프로그램의 특성에 따라 다른 개선안에 제시
#모니터링도구 장단점
-성능개선작업에 상위대기이벤트를 수집하여 정보로 제공하는 도구가 많이 사용.
-모니터링은 문제를 발견하기 위한 도구, 복합적인 문제를 해결하는 것은 사람
-단위SQL의 문제점을 제시할 수 있으나 그 SQL이 실행하는 프로그램 구조에 대한 개선점을 제시하는데 한계
#컬럼 null 연산
col1 col2 col3
============================
10 20 null
15 null null
50 70 20
select sum(col2+col3) 는 90
-컬럼간 연산에 null이 포함되면 결과는 null
-행간 연산에 null이 포함되면 결과는 null 아님
'IT 자격증 > DAP,DAsP' 카테고리의 다른 글
| DAP 6과목 데이터 품질 관리 이해 (0) | 2024.05.14 |
|---|---|
| DAP 4과목 데이터 모델링 (0) | 2024.05.14 |
| DAP 3과목 데이터 표준화 (0) | 2024.05.13 |
| DAP 2과목 데이터 요건 분석 (0) | 2024.05.13 |
| DAP 1과목 전사아키텍처 이해 (0) | 2024.05.13 |
