1. 요약
1-1. 저장 부문의 개선
- 자동 세그먼트 공간 관리 (Automatic Segment Space Management)
- 로컬 관리 타입의 시스템 테이블스페이스 (Locally Managed System Tablespace)
- 자동 언두 관리 (Automatic Undo Management)
1-2. 인덱스 관리 부문의 개선
- 사용되지 않는 인덱스의 확인 (Identifying Unused Indexes)
- 인덱스 재 생성 (Rebuilding Indexes)
- 빗맵 조인 익덱스 (Bitmap Join Indexes)
1-3. 메모리와 I/O 부문의 개선
- 동적인 메모리 관리 (Dynamic Memory Management)
- 래치 경합에 대한 관리 향상 (Latch Contention Improvements)
1-4. Optimizer 개선
- 신속한 응답 속도에 대한 최적화 (Fast Response Optimization)
- 향상된 통계 정보 수집 (Enhanced Statistics Gathering)
- 실행 계획에 대한 캐쉬 (Cached Execution Plans)
- CURSOR_SHARING 파라미터에서의 Literal 사용 (Using Literals with the CURSOR_SHARING Parameter)
- RBO 지원 중단 공지 (Rule-Based Optimizer Desupport Notice)
1. 저장 부문의 개선
1) 자동 세그먼트 공간 관리 (Automatic Segment Space Management)
데이터베이스 공간 관리는 DBA의 중요한 역할 가운데 하나이며, 데이터베이스가 중단되지 않고 서비스를 제공 할 수 있도록 공간 사용에 대한 계획 수립과 관찰에 많은 시간을 할애하게 된다.
Oracle 9i에서 새로 도입된 기능을 활용하면, 이와 같은 공간 관리 작업을 단순화 되고, 최적화된 상태를 유지할 수 있도록 하여, 성능 개선과 관련된 노력을 줄이는데 도움을 준다.
자동 공간 관리 기능은, 테이블이나 인덱스가 차지하는 여유 공간(free space)에 대한 관리를 단순하게 하는 기능으로, 공간 활용율을 높이고, 실환경에서의 성능 개선과 확정성에 도움을 준다. Oracle 9i 이전 버전에서는 FREELISTS라고 불리는 데이터 구조체를 통해 객체가 점유하고 있는 공간 내에서 새로운 row에 대한 insert가 가능한 공간을 관리 해 왔다. DBA는 FREELIST 와 FREELIST GROUP의 갯수를 객체 생성시 지정 할 수 있었다. 이때, PCTUSED 값은 FREELIST에 특정 블럭을 속하게 하거나, 속하지 않게 하는 기준 역할을 수행하게 된다. 한편, 새로운 자동 세그먼트 관리 기능에서는 객체 내의 공간 관리를 명시적으로 지정하지 않고도 효과적으로 활용될 수 있는 기능을 제공하고 있으며, 객체에 할당된 블럭들의 공간 활용 정도를 bitmap을 사용하여 관리하게 하고 있다. bitmap의 상태는 주어진 블럭에 얼마나 많은 공간이 가용한지를 나타낸다. (예. 75% 이상, 50% - 70%, 25%-50%, 25% 미만) 또한 블럭이 초기화 되어 있는지 여부도 나타내 준다.
이 기능은, 공간 관리된 각종 조정 사항(FREELISTS, FREELIST GROUP, PRCUSED)을 수작업으로 조정할 필요성을 없애, 특정 데이터베이스 객체 내의 공간 관리를 수작업으로 관리하지 않아도 되게 하였다.
동시에 데이터베이스 입장에서는 특정 블럭이 얼마나 사용되고 있는지 여부를 좀더 정확히 파악할 수 있게 됨으로써, 공간 활용성을 높일 수 있게 되었다. 특히 매 row의 크기가 매우 다양한 경우 많은 효과를 얻을 수 있다. 또한 이 기능은 DML 처리의 성능 개선에도 도움을 주게 되는데 이것은, 여유 공간에 대한 정보를 lookup 하기 위한 대기 시간을 bitmap을 사용함으로써 현격하게 감소 시킬 수 있기 때문이다.
2) 로컬 관리 타입의 시스템 테이블스페이스 (Locally Managed System Tablespace)
9i release 2 (9.2.0.2.0) 부터, 시스템 테이블스페이스도 로컬 관리 타입으로 생성될 수 있다. 로컬 관리 테이블스페이스는, 해당 테이블스페이스의 extent를 bitmap 형태로 데이터파일별로 관리하며, 해당 데이터파일내의 여유 블럭(free block)과 사용중인 블럭(used block)에 대한 정보를 관리한다. Bitmap의 각 bit는 하나의 블럭 또는 일련의 블럭의 상태에 해당한다. Extent가 할당되거나, 재사용하기 위해 free 상태로 되돌려 지면, 오라클은, 블럭의 새로운 상태를 반영시키기 위해 bitmap 정보를 갱신시킨다. 이와 같은 변경 작업은 rollback 정보를 생성하지 않는데, 그 이유는 데이터 딕셔너리에 존재하는 테이블의 정보를 변경하지 않기 때문이다. (테이블스페이스 quota 정보는 예외), 한편 딕셔너리로 관리되는 테이블스페이스는 rollback 정보가 발생하기 된다. 로컬 관리 타입의 테이블스페이스에 좀더 자세한 정보와, 이미 존재하는 테이블스페이스를 로컬 관리 타입으로 변경시키는 방법은 bulletin: 18261와 bulletin:18260에 좀더 자세히 기술되어 있다.
3) 자동 언두 관리 (Automatic Undo Management)
Oracle9i 데이터베이스는 undo (rollback) 세그먼트를 자동으로 관리할 수있다. DBA는 더이상, rollback segment의 크기나 갯수를 계획하고 튜닝하거나, 특정 트랜잭션을 특정 rollback segment에 할당하는 등의 결정을 내릴 필요가 없다. Oracle 9i에서는, undo space를 하나의 undo tablespace에 할당시켜, 데이터베이스가 undo block에 대한 경합이나, consistent read retention 또는 공간 관리 등의 사안을 자동으로 처리 하도록 하게 할 수 있다. 이와 같은 설계를 통해 정적인 rollback segment를 관리하는 대신 하나의 undo 테이블스페이스를 undo space로 지정함으로써, 나머지 사항에 대한 관리를 자동화 시킬 수 있다.
2. 인덱스 관리 부문의 개선
1) 사용되지 않는 인덱스의 확인 (Identifying Unused Indexes)
Oracle 9i에서는, 특정 인덱스가 사용되고 있는지 여부를 판단 할 수 있게 해 준다. 만약 인덱스가 사용되고 있지 않다면 그 인덱스는 drop 시킬 수 있고, 그만큼 불필요한 데이터베이스 내부 처리 작업(인덱스 관리작업)을 줄일 수 있다.
특정 인덱스가 사용되고 있는지에 대한 모니터의 시작은 다음 명령을 사용한다.
ALTER INDEX index MONITORING USAGE
후에, 모니터를 중단 시키는데는 다음과 같은 명령을 사용한다.
ALTER INDEX index NOMONITORING USAGE
인덱스가 모니터 기간동안 사용되었는지 여부를 알기 위해서는 V$OBJECT_USAGE를 살펴 봄으로써 알 수 있다. 이 뷰에는 USED라는 컬럼이 있는데, 만약 모니터 기간동안 인덱스가 사용되었는지 여부에 따라 값으로 YES 또는 NO값이 들어가게 된다. 이 뷰는 또한 모니터를 언제 시작하여 언제 종료 시켰는지에 대한 정보도 가지고 있으며, 현재 모니터가 진행중인지 여부를 알기 위해 MONITORING (YES 또는 NO 값) 컬럼을 참조하면된다. MONITORING USAGE 명령을 사용할 때 마다 V$OBJECT_USAGE의 해당 인덱스에 대한 값들이 재 설정되며, 이전에 모니터 한 정보는 제거되거나 재 설정되고, 새로운 모니터 시작 시간이 기록된다. NOMONITORING USAGE 명령을 실행 시키면 더이상 모니터링을 수행하지 않게 되며, 종료 시간이 기록된다. 이후 ALTER INDEX ... MONITORING USAGE 명령을 수행하기 전 까지는 V$OBJECT_USAGE의 해당 인덱스에 대한 정보는 변경되지 않는다.
2) 인덱스 재 생성 (Rebuilding Indexes)
Oracle 9i 부터, 인덱스를 online 상태에서 rebuild 할 수 있으며, compute statistics 작업도 동시에 수행 시킬 수 있다. 이전 버전에서는 다음과 같이 인덱스 재 생성작업을 수행 했어야 했다.
alter index index_name rebuild online
또는
alter index index_name rebuild compute statistics
* index_name은 실제 인덱스 명
하지만, Oracle 9i에서는 위 작업을 한번에 수행 시킬 수 있다.
alter index index_name rebuild compute statistics online;
이것은 인덱스 재 생성 기간동안에도 인덱스에 대한 액세스를 허용하면서, 통계 정보를 수집하는 작업을 동시에 수행하는 것을 의미한다. 인덱스를 online 상태에서 재 생선 할 때는 새로운 인덱스가 index 테이블스페이스에서 생성되고, 그 기간동안에는 이전 인덱스가 사용되게 된다. online 옵션을 사용하게 되면, 인덱스를 재 생성하는 동안 테이블이나 파티션에 대한 DML 작업도 가능하게 된다. 새로운 인덱스 생성이 완료되면, 이전 인덱스는 drop 되게 된다. 만약 인덱스 재 생성시 online 옵션을 지정하지 않으면, 인덱스 재 생성 기간 동안 데이터베이스에서는 테이블에 전체에 대한 lock을 획득하여, 작업을 수행하게 된다. 한편 nologging이라는 attribute를 사용 할 수도 있는데, 이것은 인덱스 재 생성 기간동안 redo 정보를 만들지 않도록 지정하는 것이다. (예: alter index index_name rebuild compute statistics online nologging;)
Oracle 9i 부터는, 일반 테이블 또는 index organized table(secondary index 포함)에 대한 reverse key index, function-based index, key compressed index 에 대한 online index 재 생성도 지원을 한다.
3) 빗맵 조인 익덱스 (Bitmap Join Indexes)
Bitmap Join Index(BJI)는 Oracle 9i에 추가된 새로운 기능이다. Bitmap Join Index는 join의 결과를 미리 저장하여, 실재 실행시에는 join 작업을 하지 않도록 하는 기능이다. BJI는 DW 환경의 star schema 환경에서 특히 유용하다. 하나 이상의 디멘젼 테이블의 컬럼을 외래키를 통해 팩트 테이블의 조회 결과의 제약 조건으로 지정할 때 Bitmap Join Index를 사용하면 실제 join 작업이 발생하지 않도록 할 수 있다.
3. 메모리와 I/O 부문의 개선
1) 동적인 메모리 관리 (Dynamic Memory Management)
메모리 관리 부문은 Oracle 9i에서 개선된 주요 부문이다. 전통적으로 SGA를 늘리거나 줄이기 위해서는 DBA가 데이터베이스 인스턴스를 shutdown 해야만 했다. Oracle 9i에서는 buffer cache와 shared pool의 크기를 동적으로 관리할 수 있는 기능을 제공해 준다. 또한 buffer cache 크기에 대한 권고 값을 제시하는 기능이 제공되는데, 이것은 buffer cache의 크기를 다르게 지정할 때의 성능의 변화를 예측할 수 있게 해 준다. Oracle 9i에서는, 또한, SQL 실행에 필요한 working memory에 대한 자동 관리 기능이 제공되는데, private memory에 대한 할당 량을 통제하는 initialization run time paraeter에 대한 자동 튜닝이 된다. 이 기능을 사용하여, 초급 DBA는, DW, 리포팅 애플리케이션 성능 개선을 위한 메모리 파라미터 튜닝에 필요한 시간과 노력을 줄일 수 있고, 고급 DBA는 각각의 work load별 메모리 튜닝에 들어가는 노력을 피할 수 있다. 좀더 자세한 정보는 Bulletin #: 12090을 참조.
2) 래치 경합에 대한 관리 향상 (Latch Contention Improvements)
Oracle 9i에서는 여러 부문에 대한 latch contention이 줄어들거나 없어짐으로써, 사용량이 많은 시스템의 성능 향상에 도움이 될 수 있게 개선 하였다. I/O에 대한 일반적인 개선사항이 적용되었으며, 이와 같은 개선에는 direct I/O에 대한 자동 튜닝, prefetching, 인덱스에 대한 row source의 skip/scan 등이 포함된다. 이 사항들은 DW 환경 뿐 아니라 OLTP 환경에도 도움이 된다.
4. Optimizer 개선
1) 신속한 응답 속도에 대한 최적화 (Fast Response Optimization)
신속한 응답시간은 OLTP 사용자에게 중요하며, 일반적으로 OLTP 사용자는 첫번째 row 가 신속하게 나타나는 것을 중요시 하는 경향이 있으며 전체 질의 처리 결과를 처리하는 시간에는 무관심하다. 특히 처리 결과 값이 많을 경우 그러하다. 그와 같은 사용자를 위해서는, 질의 처리시 가급적 처리 결과의 첫번째 결과 값이 신속하게 되돌려 질 수 있도록 (전체 결과 처리 시간을 단축시키지는 않더라도) 최적화 시켜야 한다. Cost Based Optimizer에서는 신속한 응답 속도를 위해 두가지 최적화 방법을 채택하고 있다. 예전부터 사용되오던 방식은 FIRST_ROWS 힌트를 사용하거나, FIRST_ROWS 초기화 파라미터를 사용하는 것이다. 이 기능은 Oracle 7 버젼 이상의 CBO에서 사용되오던 방식이다. Oracle 9i에서 새로 추가된 방식은 FIRST_ROWS_n 이라는 힌트나, 파라미트럴 사용하는 것이다.
2) 향상된 통계 정보 수집 (Enhanced Statistics Gathering)
오라클의 query optimizer에서는 데이터베이스의 객체에 대한 통계 정보를 사용한다( 각 테이블의 row 수와 같은 정보) 이와 같은 정보는 DBMS_STATS 라는 패키지를 통해 수집된다. Oracle 9i에서는 dbms_stats 패키지가 DBA 입장에서 통계 정보를 좀더 적절하게 지정할 수 있고, 좀더 사용하기 쉽게 개선 되었다. 예를 들어 자동적으로 sampling %를 결정하고, histogram에 사용할 컬럼이 지정될 수 있게 되었다. 이와 같은 부문이 자동화 됨으로써, DBA는 좀더 정확한 정보를 간편하게 수집할 수 있게 되었다. 또한 새로운 시스템 통계가 추가 되었는데 이것은 CBO가 CPU와 System I/O 상황을 고려하도록 하기 위해서이다. 각각이 실행 계획 후보중에서 optimizer는 I/O와 CPU 비용을 예상하여 선택하게 된다.
3) 실행 계획에 대한 캐쉬 (Cached Execution Plans)
Oracle 9i 이전 버젼에서는 질의에 대한실행 계획을 PLAN_TABLE에 저장할 수 있었다. (생성은 utlxplan.sql 스크립트 실행) 하지만, 이 실행 계획이 실제 질의 처리시 사용되리라는 보장은 할 수가 없었는데 이것은, 사용자 세션 환경(예, sort_area_size, hash_area_size)에 따라 다른 방식의 실행 계획을 선택할 수 있었기 때문이다. Oracle 9i에서는, V$SQL_PLAN이라는 동적 view가 추가 되었는데, 이 view는 chched cursor가 사용중인 동안, 실제 실행 계획을 조회하는데 사용될 수 있다. Oracle 9i release 2 에서는, 2개의 테이블이 추가 되었는데 (V$SQL_PLAN_STATISTICS, V$SQL_PLAN_STATISTICS_ALL) 이것은 각각의 cached cursor에 대한 좀더 상세한 정보를 조회하는데 사용될 수 있으며, 실행계획을 실제 실행시키는데 필요한 통계 정보등을 포함한다.
4) CURSOR_SHARING 파라미터에서의 Literal 사용 (Using Literals with the CURSOR_SHARING Parameter)
cursor_sharing 파라미터는 Oracle 8.1.6에서 새로 추가된 기능으로, Oracle 8i에서는 이 파리미터 값으로 EXACT 또는 FORCE 값을 사용할 수 있었다. 값이 FORCE로 지정될 경우 literal은 시스템이 생성한 binding variable로 데치되었다. 여러개의 statement에서 literal 값만 다를 경우에는, 애플리케이션 내 SQL에서 literal을 사용한다 하더라도 cursor가 공유될 수 있게 하였다. 이 파라미터는 초기화 파라미터 파일이나 시스템 또는 세션 레벨에서 동적으로 지정가능하다.
ALTER SESSION SET cursor_sharing = FORCE;
또는
ALTER SYSTEM SET cursor_sharing = FORCE;
참고: FORCE를 지정할 경우 시스템이 literal이 위치한 곳에 bind variable을 생성하며, cost based optimizer에 의해 선택 될 실행 계획과는 다른 실행계획이 선택될 수 있는데, 이것은 최적화된 실행 계획을 선택하는데 사용 가능 했던 literal 값을 더이상 참조 할 수 없기 때문이다.
Oracle 9i에서는 CURSOR_SHARING=SIMILAR 로 지정할 수 있게 되었다. SIMILAR는 statement가 일부 literal 값에 차이가 있다고 하더라도 나머지 부분이 동일하다면, cursor를 공유하도록 하는 것이다. 이때 listeral의 차이가 statement가 처리하고자 하는 내용이나, 실행계획이 최적화된 방향에 영향을 미치지 않아야 cursor가 공유되게 된다. 이것은 FORCE를 사용함으로써, 바람직하지 않은 실행 계획을 선택하는 단점을 개선하는 역할을 하게 된다. 오라클은 literal이 bind variable에 의해 대체 되어도 무방한지 여부를 먼저 점검하게 된다. 결과적으로 일부 SQL은 좀더 최적화된 실행 계획을 위해 공유 되지 않을 수도 있다.
5) RBO 지원 중단 공지 (Rule-Based Optimizer Desupport Notice)
Oracle 9i Release 2 (9.2.x)는 Rule Based Optimizer를 지원하는 마지막 버젼으로, 후속 버젼에서는 지원하지 않을 계획이다. 자세한 정보는 Note:189702.1 참조.