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

데이터베이스 설계 단계에서 튜닝하기

데이터 정합성을 유지할 수 있는 대책을 마련하고, 성능을 위해 필요하다면 테이블, 컬럼, 관계에 대해 반정규화를 적용한다.
대용량 테이블의 경우 필요한 데이터에 대해서는 파티셔닝을 이용하여 테이블 분할을 검토한다.

이력을 관리해야 하는 테이블에 대해서는 필요하다면 시작과 종료나 현재 상태 등을 명확하게 명시하여 SQL 문장의 실행 성능을 보장한다.

테이블 접근 유형에 따라 전체 스캔 방식이나 B 트리 인덱스, 비트맵 인덱스, 클러스터링과 해싱 적용 등을 고려한다.

테이블이 조회를 주로 하는지 입력,수정,삭제 작업이 주로 발생하는지를 고려하여 적당한 인덱스 개수를 지정해야 한다. 극단적으로 조회만 발생하는 경우에는 테이블에 인덱스가 많이 있어도 무방하지만, 입력, 수정, 삭제가 빈번한 테이블에서는 인덱스가 많으면 매번 인덱스를 수정해야 하므로 성능을 저하시키는 원인이 된다.
분산 데이터베이스를 적용했을 경우 원격 테이데베이스를 이용할 때 성능 저하가 예상된다면 스냅샷을 이용한 복제 테이블 생성 등을 고려한다.
공통적으로 관리하는 코드 데이터에 대한 접근이 빈번하다면 애플리케이션의 메모리에 상주시키고 함수를 사용하여 코드 변환을 하도록 유도한다.
PK는 일반적으로 지정된 순서대로 인덱스를 생성하므로 키를 구성하는 컬럼 순서를 복합 컬럼 인덱스를 지정하는 규칙에 따라 나열한다.
FK에 대해서는 가급적 인덱스를 생성하여 전체 스캔이 발생하는 경우와 불필요하게 발생하는 잠금을 피하도록 한다.

복합 컬럼 인덱스를 지정하는 규칙은

첫째, 항상 조회 조건으로 사용하는 컬럼이 존재한다면 맨앞에 오게 한다.
- 기업의 업무에서는 일반적으로 사업소코드,지사코드,지점코드 또는 회사코드와 같이 지역을 나타내거나 데이터를 분리하는 단위를 첫째 컬럼으로 이용한다.
둘째는 WHERE 조건절에서 '='로 비교되는 컬럼이 있으면 앞쪽에 오게 한다.
섯째는 데이터의 분포도가 낮은 컬럼이 앞쪽에 오게 한다.
넷째는 정렬이 자주발생하는 컬럼이 앞쪽에 오게 한다.

SYSTEM 테이블 스페이스에는 데이터를 관리하는 딕셔너리 정보만 포함하고, 일반 오브젝트는 저장하지 않도록 한다.

오라클 패치


Oracle Version Upgrade 의 이유 * Optimizer Upgrade, New Feature, Maintenance

Oracle Patch           * DB Bug 수정
Oracle Patch 종류
특정 문제를 완벽하게 해결한 패치셋(patchset)이나
차기 릴리즈를 발표하기 전에 임시 패치(interim patch, 혹은'one-off'patch) 가 있다.

SYSAUX Tablespace 정보


* New Mandatory Tablespace: SYSAUX

이 tablespace는 다음과 같은 모든 Oracle option들과 기능들에 관한
데이타베이스 metadata를 관리하기 위해 새로이 추가되었다.

Option or Feature Schema Tablespace in 8, 8i or 9i
\---------------------\* \------------\* \-------------------------\-
XML DB XDB XDB_RESINFO
OLAP CWMLITE CWMLITE
Text CTXSYS DRSYS
Ultra Search WKSYS DRSYS
Intermedia and Spatial ORDSYS SYSTEM
Workspace Manager WMSYS
Data Mining DMSYS ODM

Oracle Label Security LBACSYS SYSTEM

Recovery Catalog RMAN TOOLS
EM Repository SYSMAN OEM_REPOSITORY
Stored Outlines OUTLN SYSTEM

Streams SYS (STREAM$_xxx) SYSTEM
LogMiner SYSTEM SYSTEM
Logical Standby SYSTEM SYSTEM

Statspack PERFSTAT User Specified
Job Scheduler SYS SYSTEM

Auditing SYS (AUD$) \
SYS (FGA$) * SYSTEM
SYS (FGA_LOG$) /

파티션테이블 정보


FROM 엔코아

문 : 박범철
답 : 한준희
은행에서 운영 dba로 일하고 있는데, 운영중인 테이블에 대한 파티션 적용을 고려하던 중
의문나는 사항들이 있어 문의드립니다.
경험자 분들의 답변 부탁드리겠습니다.
아래 문의사항 외에 고려할 사항이 있다면 그 부분도 언급부탁드립니다.

정성어린 답변에 미리 감사드립니다. \^\^
* 현재 파티션 고려대상 선정 기준 : 10GB 이상 테이블(5,000만~2억 건)

* 문의사항
1. 파티션 적용의 고려대상 기준은 무엇인가?
(예: 성능상 문제가 발생하는 경우에만 / 특정 용량 이상인 경우 등)

파티션을 적용할 것이냐 말것이냐의 결정 기준은 파티션의 특징을 정확하게 이해하고
있어야 기준을 이해하실 수 있을 겁니다.

파티션이라는 것은 테이블을 수평분할한 것으로 어떠한 생성 규칙에 맞는 데이터들만
하나의 세그먼트에 몰아두겠다는 것이고, 파티션을 사용한 PROGRAM에서 그렇지 않은
프로그램과 비교해 볼 때 효과를 얻을 수 있는 경우는 바로 읽고 처리해야 할 데이터가
상당히 많을 때 해당 조건을 만족하는 파티션만 읽고 마칠 수 있는 경우입니다.
전체 테이블을 모두 다 읽고 그 중에 조회조건에 맞는 것만 선택하며
다른 것은 버리는 경우보다 I/O 측면에서 유리한 것입니다.

즉 내가 주로 처리하는 데이터 량이 일정량 이상이 되어 인덱스만 가지고는 효과를 보기
어렵다거나, 보관주기가 지난 데이터를 주기적으로 삭제해야 하는데 삭제에 대한 부담을
많이 갖고 있을 때 이를 효율적으로 처리하기 위한 방법으로 파티션이라는 것을 도입하여 사용하고 있습니다.

2. 대용량테이블에 대해서 반드시 파티션을 적용해야만 하는가?
일반테이블로 유지할 경우 발생할 수 있는 문제점은?

대용량 테이블이라 하더라도 주로 ACCESS하는 데이터가 소량이고 OLTP성 처리가
대부분이라면 궂이 파티션을 적용하지 않아도 되겠지요. 데이터 관리 측면을 제외한 성능상에서의 의견입니다.

3 ACCESS 패턴이 PREFIX INDEX 만으로 제한되지 않아서 NONPREFIX INDEX 가 상당수 존재하는 경우
(업무상 파티션키 조건이 항상 들어올 수 없는 경우, 고객번호로 검색 등)에는
성능상 마이너스 효과가 발생할 것으로 보이는데 이 경우에도 대용량테이블이라면 파티션 적용을 해야하는가?

4. 위의 경우는 테이블과 인덱스의 파티션키가 동일한 경우를 가정하였는데,
인덱스의 파티션 키를 테이블의 파티션 키가 아닌 해당 인덱스의 컬럼중 일부로 하여
PREFIX INDEX로 구성하는 경우에는 성능이 보장되며, 이런 구성을 많이 사용하는지? 관리상 어떤 어려움이 발생할 수 있는지?

인덱스에는 PREFIX와 NON-PREFIX 두개가 있습니다. PREFIX형태의 조회조건이 들어오지
않오 AD-HOC QUERY가 빈번하게 발생한다면 GLOBAL INDEX 혹인 LOCAL NON-PREFIX인덱스를 사용하시면 됩니다.

예를 들면 조회조건에 고객번호가 = 로 들어오는데 파티션은 고객번호를 선두로 하지 않은
일자 컬럼을 기준으로 만들었다고 하겠습니다.

이 경우 고객번호로 LOCAL NON-PREFIX INDEX를 만든 다음 PARTITION PRUNING 조건인
일자가 들어오지 않고 고객번호만 들어왔다면 전체 PARTITION에 있는 LOCAL INDEX에
대 놓고 고객 번호검색을 하게 될 것입니다.

인덱스를 통해 ACCESS함에 모든 파티션에 있는 LOCAL INDEX를 ACCESS하게 됩니다만
TABLE FULL SCAN에 비해 성능상의 이익이 분명 존재합니다.
3~4번 문제 공히 고려되는 내용이라 생각하시면 되겠네요.