그 밖의 주요 기능

스무번째. 그 밖의 유용한 기능

지금까지 소개한 기능 이외에도, Oracle Database 10g는 통계 자동 수집, undo 데이타보존기간(undo retention)의 “guarantee”, 더욱 쉽고 강력해진 암호화 패키지 등의 유용한 기능을 제공합니다.

드디어 우리의 긴 여행도 막바지에 이르렀습니다. 앞의 글들을 통해 DBA의 작업을 쉽고 편하게 해 주는 다양한 툴, 팁, 테크닉을 소개해 드렸습니다.

지금까지 Oracle Database 10g의 특출한 기능이라 하더라도 DBA에게 많은 도움이 되지 않는다 면 소개하지 않는 것을 원칙으로 했습니다. 마지막 글도 Oracle 10g의 모든 기능을 섭렵하기에는 지면이 충분하지 않습니다. 이제 그간의 글을 마무리하면서, 언급될 가치가 있는 Oracle 10g의 중 요한 기능을 몇 가지 골라 소개하려 합니다.

자동적인 통계 수집

이미 많은 분들이 알고 계시겠지만, Oracle 10g에 오면서 마침내 Rules-Based Optimized(RB O)는 더 이상 지원되지 않는 (기능은 남아있지만) 것으로 결정되었습니다. Oracle9i Database 를 사용하는 IT 조직은 Cost-Based Optimizer(CBO) 환경으로 업그레이드함으로써 계속적인 기 술지원을 받을 수 있을 뿐 아니라 query rewrite, partition pruning과 같은 고급 기능을 활용할 수 있게 됩니다. 하지만 이 경우 통계(statistics)가 (정확히 말하면 통계의 부재가) 문제가 됩니 다.

CBO가 최적의 실행 경로를 생성하려면 정확한 통계를 필요로 하며, DBA는 통계가 정기적으로 수집될 수 있도록 데이타베이스를 관리해야 합니다. 10g 이전의 환경에서는 (여러 가지 이유 때 문에) 정확한 통계의 수집을 위한 DBA의 노력이 수포로 돌아가는 경우가 허다했습니다. 이러한 문제 때문에 CBO가 “제멋대로 동작한다”는, 다시 말해 임의적으로 실행 경로를 바꾸곤 한다는 이론이 제기되기도 했습니다.

10g는 통계의 자동적인 수집 기능을 제공하며, 따라서 이와 같은 문제를 상당부분 해결하였습니 다. Oracle9i의 경우 테이블의 데이타가 얼마나 변경되었는지 확인하기 위해서는 테이블의 모니 터링 옵션을 활성화하고 (ALTER TABLE... MONITORING) 해당 테이블에 대한 DBA_TAB_MOD IFICATIONS 뷰를 조회해야 했습니다.

10g에서는 MONITORING 키워드가 사라진 대신, 초기화 매개변수 STATISTICS_LEVEL을 TYPI CAL 또는 ALL로 설정함으로써 통계 수집을 자동화하는 기능을 제공합니다. (디폴트 값은 TYPI CAL이며, 따라서 데이타베이스 설치 후 바로 통계 수집 작업이 자동 수행됩니다.) Oracle Datab ase 10g는 사전 정의된 Scheduler (Scheduler에 대해서는 제 19 주에 설명했습니다) 작업 GAT HER_STATS_JOB을 통해 통계 수집을 수행합니다. 이 작업은 STATISTICS_LEVEL 매개변수 값 에 의해 활성화됩니다.

통계 수집 작업은 매우 많은 자원을 소모합니다. 따라서 이 작업으로 인해 운영 중인 애플리케이 션의 성능이 저하되지 않음을 보장할 수 있어야 합니다. 10g는 통계 수집과 같은 자동 수행 작업 을 위해 special resource consumer group인 AUTO_TASK_CONSUMER_GROUP을 정의하고 있습니다. 이 consumer group이 수행하는 작업은 디폴트 consumer group에 비해 낮은 우선순 위를 가지며, 따라서 자동 수행되는 작업이 시스템의 성능을 저하시킬 가능성을 줄일 수 있습니 다.

STATISTICS_LEVEL을 TYPICAL로 설정한 상태에서 통계가 자동 수집되지 않게 하려면 어떻게 해야 할까요 간단합니다. 아래와 같은 명령을 사용하여 Scheduler 작업을 비활성화하면 됩니 다.

BEGIN

	DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');

END;

그렇다면 굳이 이렇게 할 설정할 필요가 있을까요 여러 가지 이유로 이러한 설정이 필요한 상황 이 있을 수 있습니다. 첫 번째로 테이블 내의 많은 레코드가 변경되었지만 데이타의 분포는 변하 지 않은 경우를 들 수 있습니다 (데이타 웨어하우스 환경에서 이러한 경우가 많이 발견됩니다). 이 경우 통계를 다시 수집할 필요가 없으며 이전의 통계를 그대로 사용해도 무방합니다. 두 번째 로 partition exchange를 이용하여 materialized view(MV)를 refresh하는 환경에서도, exchang ed table의 통계를 임포트해서 사용하므로 MV에 대한 통계에 대해 별도의 수집을 원치 않을 경 우입니다. 또 전체 통계 수집 작업을 중단하는 대신, 특정 테이블에 대한 자동 통계 수집만을 선 택적으로 비활성화하는 것도 가능합니다.

통계 히스토리

옵티마이저 통계 수집 과정에서 문제가 될 수 있는 상황의 하나로 execution plan이 변경되는 경우를 들 수 있습니다. 새로운 통계가 수집되기 전까지 정상적으로 수행되던 쿼리가, 새로운 통계를 기반으로 생성된 execution plan으로 인해 오히려 성능이 악화될 수 있습니다. 이러한 문제는 실제로 빈번하게 발생합니다.

이러한 문제를 방지하기 위해서는, 새로운 통계를 수집하기 전에 현재 통계 정보를 저장합니다. 이와 같이 함으로써, 문제가 생겼을 때 바로 예전의 통계로 복구하거나, 두 통계의 차이점을 분석해서 원인을 확인할 수 있습니다.

예를 들어 REVENUE 테이블에 대한 통계 수집 작업이 5월 31일 오후 10시에 실행된 이후 쿼리 성능이 악화되었다고 가정해 봅시다. 아래 명령을 사용하여 Oracle이 저장한 이전 통계로 복구될 수 있습니다.

begin

   dbms_stats.restore_table_stats (

      'ARUP',

      'REVENUE',

      '31-MAY-04 10.00.00.000000000 PM -04:00');

end;

위 명령은 (TIMESTAMP 데이타 타입을 통해 명시한 대로) 5월 31일 오후 10시 시점을 기준으로 통계를 복구합니다. 이와 같이 함으로써 새로운 통계로 인해 발생한 문제를 해결할 수 있습니다.

복구에 이용할 수 있는 과거 통계의 보존 기간은 매개변수를 통해 설정할 수 있습니다. 현재 설정된 보존 기간을 확인하려면 아래와 같이 질의합니다:

SQL> select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;GET_STATS_HISTORY_RETENTION

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

                         31

위 결과를 통해 31일간의 통계 정보를 보존할 수 있습니다. 단 이 기간이 반드시 보장되는 것은 아닙니다. 복구 가능한 정확한 시점을 확인하려면 아래와 같이 입력합니다:

SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;GET_STATS_HISTORY_AVAILABILITY

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

17-MAY-04 03.21.33.594053000 PM -04:00

위 결과는 현재 가장 오래된 통계가 5월 17일 오전 3시 21분에 생성된 것임을 알려주고 있습니다.

내장 함수를 이용하여 통계의 보존 기간을 변경할 수 있습니다. 보존 기간을 45일로 변경하려면 다음과 같이 입력합니다:

execute DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (45)

Guaranteed Undo Retention

Oracle9i에서 처음 소개된 Automatic Undo Retention 기능은 ORA-1555 “Snapshot Too Old” 에러가 발생할 가능성을 상당 수준 줄여줍니다. 하지만 이 에러는 (많이 줄어들기는 했지만) 여전히 발생하고 있습니다. 왜일까요

이 질문에 대답하기 위해서는, 먼저 undo segment가 동작하는 원리를 이해해야 합니다. 오라클 데이타베이스의 데이타가 변경되면, (SGA 내부의) 캐시에 있는 블록이 즉각적으로 변경되면서, 기존의 데이타 이미지(past image)는 undo segment에 저장됩니다. 트랜잭션이 커밋 되면, 기존의 이미지는 더 이상 불필요하며, 따라서 삭제가 가능합니다. 하지만 Undo segment의 모든 공간이 액티브 트랜잭션에 의해 사용되고 있다면 세그먼트 내에서 가장 오래된 익스텐트(extent)에 덮어 쓰기를 시도합니다 (이 과정을 “wrapping”이라 부르기도 하며, V$ROLLSTAT 뷰의 WRAPS 컬럼을 통해 확인됩니다). 하지만, 롱-러닝 트랜잭션(long-running transaction)이 사용되는 경우와 같은 특별한 상황에서, 데이타베이스는 액티브 트랜잭션을 위해 세그먼트를 확장하기도 합니다 (V$ROLLSTAT 뷰의 EXTENDS 컬럼을 통해 확인됩니다). 쿼리가 데이터의 일관성을 보장하기 위해서 undo segment의 익스텐트(extent)에 저장된 이미지를 요구했지만 해당 익스텐트가 이미 재사용된 경우, 쿼리는 ORA-1555 에러를 발생시킵니다.

초기화 매개변수 UNDO_RETENTION을 통해 undo 데이타가 보존되는 기간을 (초 단위로) 설정할 수 있습니다. 이처럼 보존 기간을 설정함으로써, inactive 상태의 undo extent라 하더라도 일정 기간 삭제되지 않음을 보장할 수 있습니다. 이 방법을 사용하면 inactive 상태의 익스텐트(extent)가 재사용되는 상황을 예방하고, 따라서 ORA-1555 에러가 발생할 가능성을 줄일 수 있습니다.

하지만 UNDO_RETENTION 매개변수로 ORA-1555의 발생 가능성을 근본적으로 제거할 수는 없습니다. 세그먼트의 확장이 불가능한 경우, 데이타베이스는 가장 오래된 inactive 익스텐트를 강제로 재사용합니다. 따라서 일부 롱-러닝 트랜잭션으로부터 ORA-1555 에러가 발생할 가능성은 상존합니다.

10g에서는 이러한 문제가 해결되었습니다. 이제는 undo 테이블스페이스를 생성하면서 undo retention “guarantee”를 설정할 수 있습니다. 그 예가 아래와 같습니다:

CREATE UNDO TABLESPACE UNDO_TS1

DATAFILE '/u01/oradata/proddb01/undo_ts1_01.dbf'

SIZE 1024M

RETENTION GUARANTEE;	

마지막 부분의 “RETENTION GUARANTEE”는 undo tablespace로 하여금 만료되지 않은 undo 익스텐트의 보존을 “보장”하도록 설정합니다. 기존 undo tablespace는 ALTER 구문을 이용하여 아래와 같이 수정할 수 있습니다:

ALTER TABLESPACE UNDO_TS2 RETENTION GUARANTEE;

익스텐트의 보존을 “보장”하는 것을 원치 않는 경우(Oracle 9i 방식)에는 다음과 같이 입력합니다:

ALTER TABLESPACE UNDO_TS2 RETENTION NOGUARANTEE;

테이블스페이스에 undo retention이 “guarantee” 되고 있는지의 여부는 아래와 같이 확인할 수 있습니다.

SELECT RETENTION

FROM DBA_TABLESPACES

WHERE TABLESPACE_NAME = 'UNDO_TS1';

End-to-End Tracing

성능 문제를 진단하기 위한 방법의 하나로, sql_trace를 활성화하여 데이타베이스 쿼리를 추적하고 tkprof와 같은 툴을 사용하여 그 결과를 분석하는 방법이 자주 사용됩니다. 하지만, 이 방법은 공유 서버(Shared Server) 아키텍처 상에 구현된 데이타베이스 환경에서는 그 유용성이 심각하게 제한됩니다. 이러한 경우 사용자의 요구사항을 처리하기 위해서 여려 공유서버 프로세스(shared server process)들이 생성됩니다. 사용자 BILL이 데이타베이스에 접근하려는 경우, dispatcher는 사용 가능한 공유 서버 중 하나와 사용자를 연결합니다. 현재 사용 가능한 서버가 존재하지 않는 경우, 새로운 공유 서버가 생성됩니다. 이 세션이 트레이스(trace) 작업을 시작하면, 해당 공유 서버의 프로세스에 의해 호출된 작업들의 트레이스가 수행됩니다.

이제 BILL의 세션이 idle 상태가 되고 LORA의 세션이 active 상태가 되었다고 가정합시다. BILL에게 서비스를 제공하던 공유 서버는 LORA의 세션에 할당되었습니다. 이 시점 이후의 트레이스 정보는 BILL의 세션이 아닌 LORA의 세션을 추적합니다. LORA의 세션이 inactive 상태가 되면, 공유 서버는 다른 active 세션에 할당되며, 다시 BILL 또는 LORA의 세션과는 전혀 무관한 트레이스정보를 만들어 냅니다.

10g에서는 end-to-end tracing 기능을 통해 이러한 문제를 해결하였습니다. 10g에서의 트레이스 작업은 세션 단위로 수행되지 않고 client identifier와 같은 정보를 기준으로 수행됩니다. 새로 추가된 DBMS_MONITOR 패키지가 바로 이러한 목적을 위해 사용됩니다.

예를 들어, identifier “account_update”를 갖는 모든 세션을 트레이스하려는 경우를 가정해 봅시다. 먼저 트레이스를 설정하기 위해서 아래와 같이 입력합니다:

exec DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE('account_update');

위 명령은 “account_update”를 identifier로 하는 모든 세션에 대한 트레이스 작업을 활성화합니다. 데이타베이스에 연결하는 과정에서, BILL은 다음과 같은 명령을 통해 client identifier를 설정합니다:

exec DBMS_SESSION.SET_IDENTIFIER ('account_update')

“account_update”를 identifier로 하는 모든 세션을 트레이스 하도록 설정된 상황이므로, 위의 세션 역시 트레이스 되고 user dump destination 디렉토리에 트레이스 파일(trace file)이 생성됩니다. 다른 사용자가 데이타베이스에 연결하면서 client identifier를 “account_update”로 설정하는 경우, 새로운 세션 역시 자동으로 트레이스 됩니다. 결국 아래와 같은 명령을 통해 트레이스를 disable할 때까지, “account_update”를 client identifier로 하는 모든 세션이 트레이스 됩니다:

exec DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE('account_update');

생성된 트레이스 파일은 tkprof 툴을 통해 분석할 수 있습니다. 하지만 각각의 세션이 별도의 트레이스 파일을 생성하는 문제가 있습니다. 문제를 효과적으로 분석하려면, 통합된 형태의 트레이스 파일이 필요할 것입니다.

trcsess 툴을 이용하면 “account_update”를 client identifier로 하는 모든 세션의 트레이스 정보를 추출하여 하나의 파일에 저장할 수 있습니다. 트레이스 정보를 통합하려면 user dump destination 디렉토리로 이동하여 다음과 같이 실행합니다:

trcsess output=account_update_trc.txt clientid=account_update *

위 명령을 실행하면 account_update_trc.txt 파일이 생성됩니다. 이 파일은 일반적인 트레이스 파일과 같은 포맷을 가지며, tkprof 툴을 이용하여 분석될 수 있습니다.

이처럼 end-to-end tracing 기능을 활용하여 트레이스 정보를 보다 쉽게 수집할 수 있습니다. 또 세션 별로 alter session set sql_trace = true 명령을 사용하는 대신, client identifier를 기준으로 트레이스를 enable/disable할 수 있습니다. 같은 패키지를 통해 제공되는 SERV_MOD_ACT_TRACE_ENABLE 프로시저를 사용하면 dbms_application_info패키지를 이용해서 설정된 특정 서비스, 모듈, 또는 액션의 조합으로 트레이스를 수행하는 것도 가능합니다.

데이타베이스 사용 현황

오라클 데이타베이스의 파티셔닝(partitioning) 기능은 별도로 구매해야 하는 옵션입니다. 요즘처럼 예산이 제한되는 상황에서는, 구입을 결정하기 전에 파티셔닝이 정말로 사용자에게 효과적으로 활용되고 있는지 먼저 고민하지 않을 수 없습니다.

이 질문을 사용자에게 던지는 대신 데이타베이스에게 물어보십시오. Automatic Workload Repository(제 6 주 연재에서 설명한 바 있습니다)는 설치된 모든 기능의 사용 정보를 1주 1회의 단위로 수집합니다.

데이타베이스의 사용 패턴을 보여주는 중요한 뷰가 두 가지 존재합니다. 첫 번째로, DBA_HIGH_WATER_MARK_STATISTICS 뷰는 현재 데이타베이스에 사용되는 각 기능의 최대값을 보여줍니다. 실행 결과의 예가 아래와 같습니다.

NAME             HIGHWATER LAST_VALUE DESCRIPTION

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

USER_TABLES            401        401 Number of User Tables

SEGMENT_SIZE    1237319680 1237319680 Size of Largest Segment (Bytes)

PART_TABLES             12          0 Maximum Number of Partitions belonging

                                      to an User Table

PART_INDEXES            12          0 Maximum Number of Partitions belonging

                                      to an User Index

USER_INDEXES           832        832 Number of User Indexes

SESSIONS                19         17 Maximum Number of Concurrent Sessions

                                      seen in the database

DB_SIZE         7940079616 7940079616 Maximum Size of the Database (Bytes)

DATAFILES                6          6 Maximum Number of Datafiles

TABLESPACES              7          7 Maximum Number of Tablespaces

CPU_COUNT                4          4 Maximum Number of CPUs

QUERY_LENGTH          1176       1176 Maximum Query Length

위에서 확인할 수 있는 것처럼, 이 뷰는 데이타베이스의 사용 패턴에 대한 몇 가지 중요한 정보를 제공합니다. 예를 들어 사용자가 최대 12 개의 partitioned table을 생성했지만, 현재는 하나도 사용되고 있지 않음(LAST_VALUE = 0)을 확인할 수 있습니다. 이 정보는 데이타베이스가 셧다운 되는 경우에도 그대로 유지되며, 마이그레이션 작업을 위한 계획 단계에서 매우 유용하게 활용될 수 있습니다.

하지만 위의 뷰가 제공하는 정보만으로는 부족한 부분이 있습니다. 위 결과를 통해 12개의 partitioned table이 생성되었다는 것을 확인할 수 있지만 이 기능이 마지막으로 사용된 시간은 알 수 없습니다. DBA_FEATURE_USAGE_STATISTICS 뷰를 사용하면 이러한 문제의 해답을 쉽게 찾을 수 있습니다. 파티셔닝 기능에 관련한 뷰의 조회 결과가 아래와 같습니다:

DBID                          : 4133493568

NAME                          : Partitioning

VERSION                       : 10.1.0.1.0

DETECTED_USAGES               : 12

TOTAL_SAMPLES                 : 12

CURRENTLY_USED                : FALSE

FIRST_USAGE_DATE              : 16-oct-2003 13:27:10

LAST_USAGE_DATE               : 16-dec-2003 21:20:58

AUX_COUNT                     :

FEATURE_INFO                  :

LAST_SAMPLE_DATE              : 23-dec-2003 21:20:58

LAST_SAMPLE_PERIOD            : 615836

SAMPLE_INTERVAL               : 604800

DESCRIPTION                   : Oracle Partitioning option is being used -

                                there is at least one partitioned object created.

이 뷰를 통해 파티셔닝 기능이 현재 데이타베이스에서 사용되지 않고 있으며 (CURRENTLY_USED : FALSE), 마지막으로 액세스된 시간이 2003년 12월 16일 오후 9시 20분임을 알 수 있습니다. 사용 현황 정보의 샘플링은 매 604,800초 (7일) 단위로 수행되고 있습니다 (SAMPLE_INTERVAL 컬럼). 그리고 LAST_SAMPLE_DATE 컬럼은 사용 현황 정보가 마지막으로 샘플링 된 시점을 표시하고 있습니다.

커맨드 라인 인터페이스 대신 Enterprise Manager 10g를 사용해서 같은 정보를 확인할 수도 있습니다. 이를 위해 EM에서 Administration 탭으로 이동 한 후 Configuration Management 항목 아래의 “Database Usage Statistics” 링크를 클릭합니다 (그림 1과 2 참조).

더 쉽고 강력해진 암호화 옵션

DBMS_OBFUSCATION_TOOLKIT (DOTK) 패키지를 기억하십니까 Oracle9i 및 이전 버전에서 암호화를 적용하려면 이 패키지를 사용하는 것 이외에 다른 대안이 없었습니다. DOTK 패키지는 대부분의 보안제품처럼 대부분의 데이타베이스 환경에 적절한 수준의 보안 환경을 제공했지만, 숙련된 해커의 공격에 쉽게 무력화되는 약점이 있었습니다. DOTK 패키지에 결여된 중요한 기능의 하나로, 기존의 DES(Digital Encryption Standard) 및 DES3 (Triple DES)보다 강력한 암호화 기능을 제공하는 Advanced Encryption Standard(AES)를 들 수 있습니다.

10g에는 보다 강력한 암호화 패키지인 DBMS_CRYPTO가 추가되었습니다. 이 내장 패키지는 DOTK에 구현되지 않은 암호화 기능을 모두 포함하고 있을 뿐 아니라, 기존 함수와 프로시저에 대해서도 보다 개선된 기능을 제공합니다. (한 예로, DBMS_CRYPTO에는 최신 암호화 기술인 256 비트 AES 알고리즘이 포함되어 있습니다.) DBMS_CRYPTO의 ENCRYPT 함수(프로시저로도 사용됩니다)는 아래와 같은 매개변수를 사용합니다:

The ENCRYPT ENCRYPT 함수는 암호화된 값을 RAW 데이타 타입으로 반환합니다. 반환된 결과를 문자열로 변환하려면 아래와 같이 실행합니다:

utl_i18n.raw_to_char (l_enc_val, 'AL32UTF8')

which is the reverse of the casting to RAW.

암호화된 결과를 해독하려면 DECRYPT 함수(또는 프로시저로도 사용됩니다)를 사용합니다. 이처럼 DBMS_CRYPTO 패키지를 사용하여. 데이타베이스 환경에 개선된 보안 모델을 구현할 수 있습니다.

결론

Oracle Database 10g가 제공하는 새로운 기능을 모두 설명하는 것은 불가능에 가깝습니다. 하지만 지난 20주에 걸쳐, DBA에게 가장 중요한 의미를 갖는 일부 기능을 선별하여 소개했으며, 이번 주에는 지난 19주 동안 소개되지 않았던 몇 가지 기능을 마지막으로 설명해 드렸습니다. 이번 연재가 여러분에게 유익했기를 바랍니다. 의견이 있으신 경우 언제든 보내주시기 바라며, 여러분이 가장 마음에 들어 하는 기능이 어떤 것이었는지 알려주시면 제게 많은 참고가 될 것입니다.