Automatic Workload Repository

AWR을 이용하여 분석과 튜닝을 위한 데이타베이스 성능 통계정보와 메트릭을 수집하고, 데이타베이스에서 사용한 정확한 시간을 확인하거나 세션 정보를 저장할 수 있습니다.

데이타베이스에 성능에 관련한 문제가 생겼을 때, 귀하가 DBA로서 가장 먼저 취하는 조치는 무엇입니까? 아마도 문제에 일정한 패턴이 존재하는지 확인하는 것이 가장 일반적인 접근방법의 하나일 것입니다. “동일한 문제가 반복되는가?”, “특정한 시간대에만 발생하는가?”, 또는 “두 가지 문제에 연관성이 있는가?” 등의 질문을 먼저 제기해 봄으로써 보다 정확한 진단을 수행할 수 있습니다.

Oracle DBA들은 데이타베이스 운영에 관련한 통계정보를 수집하거나 성능 메트릭(metric)을 추출하기 위해 써드 파티 툴, 또는 직접 개발한 툴을 사용하고 있습니다. 이렇게 수집된 정보는, 문제 발생 이전과 이후의 상태를 비교하는 데 이용됩니다. 과거에 발생했던 이벤트들을 재현해 봄으로써 현재 문제를 다양한 관점에서 분석할 수 있습니다. 이처럼 관련 통계정보들을 지속적으로 수집하는 것은 성능 분석에서 매우 중요한 작업 중의 하나입니다.

오라클은 한동안 이를 위해 Statspack이라는 이름의 빌트-인 툴을 제공하기도 했습니다. Statspack은 상황에 따라 매우 유용하긴 했지만, 성능 관련 트러블슈팅 과정에서 요구되는 안정성이 결여되었다는 문제가 있었습니다. Oracle Database 10g는 성능 통계정보의 수집과 관련하여 그 기능이 비약적으로 향상된 Automatic Workload Repository(AWR)을 제공합니다. AWR은 데이타베이스와 함께 설치되며, 기본적인 통계정보뿐 아니라 통계정보로부터 유추된 메트릭(derived metric)도 함께 수집합니다. 간단하게 테스트해 보기 AWR을 이용한 새로운 기능들은 $ORACLE_HOME/rdbms/admin 디렉토리의 awrrpt.sql 스크립트를 실행하고, 수집된 통계정보와 메트릭 정보를 바탕으로 생성된 리포트를 확인함으로써 가장 쉽게 이해할 수 있습니다. awrrpt.sql 스크립트는 Statspack과 유사한 구조로 되어있습니다. 먼저 현재 저장된 AWR 스냅샷을 모두 표시한 후, 시간 간격 설정을 위한 입력값을 요구합니다. 출력은 두 가지 형태로 제공됩니다. 텍스트 포맷 출력은 Statspack 리포트와 유사하지만, AWR 리포지토리를 기반으로 하며 (디폴트로 제공되는) HTML 포맷을 통해 section/subsection으로 구분된 하이퍼링크를 제공하는 등 사용자 편의성을 강화하였다는 점에서 차이를 갖습니다. 먼저 awrrpt.sql 스크립트를 실행하여 리포트를 확인해 보시고, AWR의 기능에 대한.특성을 이해하시기 바랍니다. 구현 원리 이제 AWR의 설계방식과 구조에 대해 알아보기로 합시다. AWR은 수집된 성능관련 통계정보가 저장되며 이를 바탕으로 성능 메트릭을 제공함으로서 잠재적인 문제의 원인 추적을 가능하게 해주는 근간을 제공해 줍니다. Statspack의 경우와 달리, Oracle10g는 AWR을 활용하여 새로운 MMON 백그라운드 프로세스와, 여러 개의 슬레이브 프로세스를 통해 자동적으로 매시간별 스냅샷 정보를 수집합니다. 공간 절약을 위해, 수집된 데이타는 7일 후 자동으로 삭제됩니다. 스냅샷 빈도와 보관 주기는 사용자에 의해 설정 가능합니다. 현재 설정값을 보기 위해서는 아래와 같이 명령을 수행하면 됩니다:

select snap_interval, retention
from dba_hist_wr_control;

SNAP_INTERVAL       RETENTION
------------------- -------------------
+00000 01:00:00.0   +00007 00:00:00.0

위의 실행결과는 스냅샷이 매 시간대 별로 수집되고 있으며 수집된 통계가 7일 동안 보관되고 있음을 보여주고 있습니다. 스냅샷 주기를 20분으로, 보관 주기를 2일로 변경하기 위해서는 아래와 같이 수행하면 됩니다. (매개변수는 분 단위로 표시됩니다.)

begin
   dbms_workload_repository.modify_snapshot_settings (
      interval => 20,
      retention => 2*24*60
   );
end;

AWR은 수집된 통계를 저장하기 위해 여러 개의 테이블을 사용합니다. 이 테이블들은 모두 SYS 스키마의 SYSAUX 테이블스페이스 내에 저장되어 있으며, WRM$_* 또는 WRH$_*의 네임 포맷을 갖습니다. WRM$_* 테이블은 수집 대상 데이타베이스 및 스냅샷에 관련한 메타데이타 정보를, WRH$_* 테이블은 실제 수집된 통계 정보를 저장하는데 사용됩니다. (예측하시는 바와 같이, H는 “historical”, M은 “metadata”의 약자를 의미합니다.) 이 테이블을 기반으로 DBA_HIST_라는 prefix를 갖는 여러 가지 뷰가 제공되고 있으며, 이 뷰들을 응용하여 자신만의 성능 분석 툴을 만들 수도 있습니다. 뷰의 이름은 테이블 이름과 직접적인 연관성을 갖습니다. 예를 들어 DBA_HIST_SYSMETRIC_SUMMARY 뷰는 WRH$_SYSMETRIC_SUMMARY 테이블을 기반으로 합니다.

AWR 히스토리 테이블은 Statspack에서는 수집되지 않았던 다양한 정보(테이블스페이스 사용 통계, 파일시스템 사용 통계, 운영체제 통계 등)를 제공합니다. 테이블의 전체 리스트는 아래와 같이 데이타 딕셔너리 조회를 통해 확인할 수 있습니다:

select view_name from user_views where view_name like 'DBA\_HIST\_%' escape '\';

DBA_HIST_METRIC_NAME 뷰는 AWR에 수집되는 주요 메트릭과 메트릭이 속한 그룹, 그리고 수집 단위(unit) 등을 정의하고 있습니다. DBA_HIST_METRIC_NAME 뷰의 레코드에 대한 조회 결과의 예가 아래와 같습니다:

DBID                  : 4133493568
GROUP_ID              : 2
GROUP_NAME            : System Metrics Long Duration
METRIC_ID             : 2075
METRIC_NAME           : CPU Usage Per Sec
METRIC_UNIT           : CentiSeconds Per Second 

위에서는 "초당 CPU 사용량(CPU Usage Per Sec)" 메트릭이 “100분의 1초(CentiSeconds Per Second)” 단위로 수집되고 있으며, 이 메트릭이 "System Metrics Long Duration” 그룹에 속함을 확인할 수 있습니다. 이 레코드를 DBA_HIST_SYSMETRIC_SUMMARY와 JOIN하여 실제 통계를 확인할 수 있습니다:

select begin_time, intsize, num_interval, minval, maxval, average, standard_deviation sd 
from dba_hist_sysmetric_summary where metric_id = 2075;

BEGIN    INTSIZE NUM_INTERVAL   MINVAL  MAXVAL  AVERAGE           SD
----- ---------- ------------   ------- ------- --------  ----------
11:39     179916           30         0      33        3  9.81553548
11:09     180023           30        21      35       28  5.91543912

... 후략 ...

위 조회 결과를 통해 백 분의 1초 단위로 CPU 자원이 어떻게 소비되고 있는지 확인할 수 있습니다. SD(standard deviation, 표준 편차) 값을 참조하면 계산된 평균 값이 실제 부하와 비교하여 얼마나 오차를 갖는지 분석 가능합니다. 첫 번째 레코드의 경우, 초당 백 분의 3초의 CPU 시간이 소모된 것으로 계산되었지만, 표준 편차가 9.81이나 되므로 계산된 3의 평균값이 실제 부하를 정확하게 반영하지 못하는 것으로 볼 수 있습니다. 반면 28의 평균값과 5.9의 표준 편차를 갖는 두 번째 레코드가 실제 수치에 더 가깝다고 볼 수 있습니다. 이러한 트렌드 분석을 통해 성능 메트릭과 환경 변수의 상관 관계를 보다 명확하게 이해할 수 있습니다. 통계의 활용 지금까지 AWR의 수집 대상이 어떻게 정의되는지 알아보았습니다. 이번에는 수집된 데이타를 어떻게 활용할 수 있는지 설명하기로 합니다. 성능 문제는 독립적으로 존재하는 경우가 거의 없으며, 대개 다른 근본적인 문제를 암시하는 징후로서 해석되는 것이 일반적입니다. 전형적인 튜닝 과정의 예를 짚어보기로 합시다: DBA가 시스템 성능이 저하되었음을 발견하고 wait에 대한 진단을 수행합니다. 진단 결과 “buffer busy wait”이 매우 높게 나타나고 있음을 확인합니다. 그렇다면 문제의 원인은 무엇일까요? 여러 가지 가능성이 존재합니다: 인덱스의 크기가 감당할 수 없을 만큼 커지고 있을 수도 있고, 테이블의 조밀도(density)가 너무 높아 하나의 블록을 메모리에 읽어 오는 데 요구되는 시간이 제한된 때문일 수도 있고, 그 밖의 다른 이유가 있을 수 있습니다. 원인이 무엇이든, 문제가 되는 세그먼트를 먼저 확인해 보는 것이 필요합니다. 문제가 인덱스 세그먼트에서 발생했다면, 리빌드 작업을 수행하거나 reverse key index로 변경하거나 또는 Oracle Database 10g에서 새로 제공하는 hash-partitioned index로 변경해 볼 수 있을 것입니다. 문제가 테이블에서 발생했다면, 저장 관련 매개변수를 변경해서 조밀도를 낮추거나, 자동 세그먼트 공간 관리(automatic segment space management)가 설정된 테이블스페이스로 이동할 수 있을 것입니다. DBA가 실제로 사용하는 접근법은 일반적인 방법론, DBA의 경험 및 지식 등을 그 바탕으로 합니다. 만일 똑같은 일을 별도의 엔진이, 메트릭을 수집하고 사전 정의된 로직을 바탕으로 적용 가능한 방법을 추론하는 엔진이 대신해 준다면 어떨까요? DBA의 작업이 한층 쉬워지지 않을까요? 바로 이러한 엔진이 Oracle Database 10g에 새로 추가된 Automatic Database Diagnostic Monitor (ADDM)입니다. ADDM은 AWR이 수집한 데이타를 사용하여 결론을 추론합니다. 위의 예의 경우, ADDM은 buffer busy wait이 발생하고 있음을 감지하고, 필요한 데이타를 조회하여 wait이 실제로 발생하는 세그먼트를 확인한 후, 그 구조와 분포를 평가함으로써 최종적으로 해결책을 제시합니다. AWR의 스냅샷 수집이 완료될 때마다, ADDM이 자동으로 호출되어 메트릭을 점검하고 권고사항을 제시합니다. 결국 여러분은 데이타 분석 및 권고사항 제시를 담당하는 풀 타임 DBA 로봇을 하나 두고, 보다 전략적인 업무에 집중할 수 있게 된 셈입니다. Enterprise Manager 10g 콘솔의 “DB Home” 페이지에서 ADDM의 권고사항과 AWR 리포지토리 데이타를 확인할 수 있습니다. AWR 리포트를 보려면, Administration->Workload Repository->Snapshot의 순서로 메뉴를 따라가야 합니다. ADDM의 자세한 기능은 향후 연재에서 소개하도록 하겠습니다. 특정 조건을 기준으로 알림 메시지를 생성하도록 설정하는 것도 가능합니다. 이 기능은 Server Generated Alert라 불리며, Advanced Queue에 푸시(push) 형태로 저장되고 리스닝 중인 모든 클라이언트에 전달되는 형태로 관리됩니다. Enterprise Manager 10g 역시 Server Generated Alert의 클라이언트의 하나로서 관리됩니다. 타임 모델 (Time Model) 성능 문제가 발생했을 때, 응답시간을 줄이기 위한 방법으로 DBA의 머릿속에 가장 먼저 떠오르는 것은 무엇일까요? 말할 필요도 없이, 문제의 근본원인을 찾아내어 제거하는 것이 최우선일 것입니다. 그렇다면 얼마나 많은 시간이 (대기가 아닌) 실제 작업에 사용되었는지 어떻게 확인할 수 있을까요? Oracle Database 10g는 여러 가지 자원에 관련한 실제 사용 시간을 확인하기 위한 타임 모델(time model)을 구현하고 있습니다. 전체 시스템 관련 소요 시간 통계는 V$SYS_TIME_MODEL 뷰에 저장됩니다. V$SYS_TIME_MODEL 뷰에 대한 쿼리 결과의 예가 아래와 같습니다.

STAT_NAME                                     VALUE
-------------------------------------         --------------
DB time                                       58211645
DB CPU                                        54500000
background cpu time                           254490000
sequence load elapsed time                    0
parse time elapsed                            1867816
hard parse elapsed time                       1758922
sql execute elapsed time                      57632352
connection management call elapsed time       288819
failed parse elapsed time                     50794
hard parse (sharing criteria) elapsed time    220345
hard parse (bind mismatch) elapsed time       5040
PL/SQL execution elapsed time                 197792
inbound PL/SQL rpc elapsed time               0
PL/SQL compilation elapsed time               593992
Java execution elapsed time                   0
bind/define call elapsed time                 0 

위에서 DB time이라는 통계정보는 인스턴스 시작 이후 데이타베이스가 사용한 시간의 누적치를 의미합니다. 샘플 작업을 실행한 다음 다시 뷰를 조회했을 때 표시되는 DB time의 값과 이전 값의 차이가 바로 해당 작업을 위해 데이타베이스가 사용한 시간이 됩니다. 튜닝을 거친 후 DB time 값의 차이를 다시 분석하면 튜닝을 통해 얻어진 성능 효과를 확인할 수 있습니다. 이와 별도로 V$SYS_TIME_MODEL 뷰를 통해 파싱(parsing) 작업 또는 PL/SQL 컴파일 작업에 소요된 시간 등을 확인할 수 있습니다. 이 뷰를 이용하면 시스템이 사용한 시간을 확인하는 것도 가능합니다. 시스템 / 데이타베이스 레벨이 아닌 세션 레벨의 통계를 원한다면 V$SESS_TIME_MODEL 뷰를 이용할 수 있습니다. V$SESS_TIME_MODEL 뷰는 현재 연결 중인 active/inactive 세션들의 통계를 제공합니다. 세션의 SID 값을 지정해서 개별 세션의 통계를 확인할 수 있습니다. 이전 릴리즈에서는 이러한 통계가 제공되지 않았으며, 사용자들은 여러 정보 소스를 참고해서 근사치를 추측할 수 밖에 없었습니다. Active Session History Oracle Database 10g의 V$SESSION에도 개선이 이루어졌습니다. 가장 중요한 변화로 wait 이벤트와 그 지속시간에 대한 통계가 뷰에 추가되어, V$SESSION_WAIT를 별도로 참조할 필요가 없게 되었다는 점을 들 수 있습니다. 하지만 이 뷰가 실시간 정보를 제공하므로, 나중에 다시 조회했을 때에는 중요한 정보가 이미 사라져 버리고 없을 수 있습니다. 예를 들어 wait 상태에 있는 세션이 있음을 확인하고 이를 조회하려 하면, 이미 wait 이벤트가 종료되어 버려 아무런 정보도 얻지 못하는 경우가 있을 수 있습니다. 또 새롭게 추가된 Active Session History(ASH)는 AWR과 마찬가지로 향후 분석 작업을 위해 세션 성능 통계를 버퍼에 저장합니다. AWR과 다른 점은, 테이블 대신 메모리가 저장 매체로 이용되며 V$ACTIVE_SESSION_HISTORY 등을 통해 조회된다는 사실입니다. 데이타는 1초 단위로 수집되며, 액티브 세션만이 수집 대상이 됩니다. 버퍼는 순환적인 형태로 관리되며, 저장 메모리 용량이 가득 차는 경우 오래된 데이타부터 순서대로 삭제됩니다. 이벤트를 위해 대기 중인 세션의 수가 얼마나 되는지 확인하려면 아래와 같이 조회하면 됩니다:

select session_id||','||session_serial# SID, n.name, wait_time, time_waited
from v$active_session_history a, v$event_name n
where n.event# = a.event#

위 쿼리는 이벤트 별로 대기하는 데 얼마나 많은 시간이 사용되었는지를 알려 줍니다. 특정 wait 이벤트에 대한 드릴다운을 수행할 때에도 ASH 뷰를 이용할 수 있습니다. 예를 들어, 세션 중 하나가 buffer busy wait 상태에 있는 경우 정확히 어떤 세그먼트에 wait 이벤트가 발생했는지 확인하는 것이 가능합니다. 이때 ASH 뷰의 CURRENT_OBJ# 컬럼과 DBA_OBJECTS 뷰를 조인하면 문제가 되는 세그먼트를 확인할 수 있습니다. ASH 뷰는 그 밖에도 병렬 쿼리 서버 세션에 대한 기록을 저장하고 있으므로, 병렬 쿼리의 wait 이벤트를 진단하는 데 유용하게 활용됩니다. 레코드가 병렬 쿼리의 slave process로서 활용되는 경우, coordinator server session의 SID는 QC_SESSION_ID 컬럼으로 확인할 수 있습니다. SQL_ID 컬럼은 wait 이벤트를 발생시킨 SQL 구문의 ID를 의미하며, 이 컬럼과 V$SQL 뷰를 조인하여 문제를 발생시킨 SQL 구문을 찾아낼 수 있습니다. CLIENT_ID 컬럼은 웹 애플리케이션과 같은 공유 사용자 환경에서 클라이언트를 확인하는 데 유용하며, 이 값은 DBMS_SESSION.SET_IDENTFIER를 통해 설정 가능합니다. ASH 뷰가 제공하는 정보의 유용성을 감안하면, AWR과 마찬가지로 이 정보들을 영구적인 형태의 매체에 저장할 필요가 있을 수도 있습니다. AWR 테이블을 MMON 슬레이브를 통해 디스크로 flush 할 수 있으며, 이 경우 DBA_HIST_ACTIVE_SESS_HISTORY 뷰를 통해 저장된 결과를 확인할 수 있습니다. 수작업으로 스냅샷 생성하기 스냅샷은 자동으로 수집되도록 디폴트 설정되어 있으며, 원하는 경우 온디맨드 형태의 실행이 가능합니다. 모든 AWR 기능은 DBMS_WORKLOAD_REPOSITORY 패키지에 구현되어 있습니다. 스냅샷을 실행하려면 아래와 같은 명령을 사용하면 됩니다:

execute dbms_workload_repository.create_snapshot

위 명령은 스냅샷을 즉각적으로 실행하여 그 결과를 table WRM$_SNAPSHOT 테이블에 저장합니다. 수집되는 메트릭의 수준은 TYPICAL 레벨로 설정됩니다. 더욱 자세한 통계를 원하는 경우 FLUSH_LEVEL 매개변수를 ALL로 설정하면 됩니다. 수집된 통계는 자동으로 삭제되며, 수작업으로 삭제하려는 경우 drop_snapshot_range() 프로시저를 실행하면 됩니다. 베이스라인 성능 튜닝 작업을 수행할 때에는 먼저 일련의 메트릭에 대한 베이스라인(baseline)을 수집하고 튜닝을 위한 변경 작업을 수행한 뒤, 다시 또 다른 베이스라인 셋을 수집하는 과정을 거치는 것이 일반적입니다. 이렇게 수집된 두 가지 셋을 서로 비교하여 변경 작업의 효과를 평가할 수 있습니다. AWR에서는 기존에 수집된 스냅샷을 통해 이러한 작업이 가능합니다. 예를 들어 매우 많은 자원을 사용하는 apply_interest라는 프로세스가 오후 1시부터 3시까지 실행되었고, 이 기간 동안 스냅샷 ID 56에서 59까지가 수집되었다고 합시다. 이 스냅샷들을 위해 apply_interest_1이라는 이름의 베이스라인을 아래와 같이 정의할 수 있습니다:

exec dbms_workload_repository.create_baseline (56,59,'apply_interest_1')

위 명령은 스냅샷 56에서 59까지를 ‘apply_interest_1’이라는 이름의 베이스라인으로 표시합니다. 기존에 설정된 베이스라인은 아래와 같이 확인합니다:

select * from dba_hist_baseline;

      DBID BASELINE_ID BASELINE_NAME        START_SNAP_ID END_SNAP_ID
---------- ----------- -------------------- ------------- -----------
4133493568           1 apply_interest_1                56          59

튜닝 과정을 거친 후, 또 다른 이름(예: apply_interest_2)의 베이스라인을 생성하여, 이 두 가지 베이스라인에 해당하는 스냅샷의 메트릭을 비교할 수 있습니다. 이처럼 비교 대상을 한정함으로써 성능 튜닝의 효과를 한층 향상시킬 수 있습니다. 분석이 끝나면 drop_baseline(); 프로시저로 베이스라인을 삭제할 수 있습니다 (이 때 스냅샷은 그대로 보존됩니다). 또 오래된 스냅샷이 삭제되는 과정에서, 베이스라인과 연결된 스냅샷은 삭제되지 않습니다. 결론 이 문서는 AWR의 매우 기초적인 기능만을 소개하고 있습니다. 더욱 자세한 내용을 확인하시려면 Oracle Database 10g 제품 문서를 확인하시기 바랍니다. 기술백서(“ The Self-Managing Database: Automatic Performance Diagnosis)에서도 AWR과 ADDM를 매우 깊이 있게 다루고 있습니다. 제 18주에는, ADDM을 이용하여 실제 문제를 해결하는 방법에 대해 자세하게 설명할 예정입니다.

Flashback 테이블

실수로 삭제한 테이블을 손쉽게 다시 유효화할 수 있는 Oracle Database 10g의 Flashback 테이블 기능

매우 중요한 테이블을 실수로 삭제하여 즉시 복구해야 하는 상황은 생각보다 자주 일어나는 시나리오입니다. (때로는 이처럼 불운한 사용자가 DBA일 수도 있습니다!)

Oracle9i Database에는 Flashback 질의 옵션 개념이 도입되어 데이타를 과거의 시점에서부터 검색하지만, 테이블 삭제 같은 DDL 작업을 순간적으로 되돌릴 수는 없습니다. 이 경우 유일한 수단은 다른 데이타베이스에서 테이블스페이스 적시 복구를 사용한 다음, 엑스포트/임포트 또는 기타 메서드를 사용해 현재 데이타베이스에 테이블을 다시 생성하는 것입니다. 이 프로시저를 수행하려면 복제를 위해 다른 데이타베이스를 사용하는 것은 물론, DBA의 많은 노력과 귀중한 시간이 요구됩니다.

하지만 Oracle Database 10g의 Flashback 테이블 기능으로 들어가면 몇 개의 문만 실행하여 삭제된 테이블을 간단히 검색할 수 있습니다. 그럼, 지금부터 이 기능의 작동 원리에 대해 알아보도록 하겠습니다.

자유로운 테이블 삭제 먼저, 현재 스키마의 테이블을 확인해 봅시다.

SQL> select * from tab;

TNAME                    TABTYPE  CLUSTERID
------------------------ ------- ----------
RECYCLETEST              TABLE

그런 다음, 아래와 같이 고의로 테이블을 삭제합니다.

SQL> drop table recycletest;

Table dropped.

이제 테이블의 상태를 확인합니다.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BIN$04LhcpndanfgMAAAAAANPw==$0 TABLE

RECYCLETEST 테이블이 사라졌지만 새 테이블인 BIN$04LhcpndanfgMAAAAAANPw==$0이 있다는 점에 주목합니다. 좀 더 자세히 설명하면 삭제된 테이블 RECYCLETEST가 완전히 사라지는 대신 시스템 정의 이름으로 이름이 변경된 것입니다. 이 테이블은 여전히 동일한 테이블스페이스에 있으며 원래 테이블과 구조도 동일합니다. 테이블에 인덱스 또는 트리거가 정의되어 있는 경우, 마찬가지로 테이블과 동일한 명명 규칙을 사용하여 이름이 변경됩니다. 프로시저 같은 종속적인 소스는 무효화되지만, 대신 원래 테이블의 트리거 및 인덱스가 이름이 변경된 테이블인 BIN$04LhcpndanfgMAAAAAANPw==$0에 들어가 삭제된 테이블의 완전한 객체 구조를 보존합니다.

테이블 및 연관된 객체는 PC에 있는 것과 유사한 “휴지통(RecycleBin)”이라고 하는 논리적 컨테이너에 들어갑니다. 하지만 이들 객체가 이전에 있던 테이블스페이스에서 옮겨지는 것은 아니며 계속 해당 테이블스페이스에서 공간을 차지하고 있습니다. 휴지통은 단순히 삭제된 객체의 목록을 만드는 논리적 구조입니다. 휴지통의 컨텐트를 확인하려면 SQL*Plus 프롬프트에서 다음 명령을 사용합니다(SQL*Plus 10.1이 있어야 함).

SQL> show recyclebin

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ ------------------
RECYCLETEST      BIN$04LhcpndanfgMAAAAAANPw==$0 TABLE        2004-02-16:21:13:31

이렇게 하면 테이블의 원래 이름인 RECYCLETEST는 물론, 삭제된 후 생성된 새 테이블 이름과 동일한 휴지통에서의 새 이름이 표시됩니다. (참고: 정확한 이름은 플랫폼별로 다를 수 있습니다.) 테이블을 다시 유효화하기 위해서는 FLASHBACK TABLE 명령만 사용하면 됩니다.

SQL> FLASHBACK TABLE RECYCLETEST TO BEFORE DROP;

FLASHBACK COMPLETE.

SQL> SELECT * FROM TAB;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
RECYCLETEST                    TABLE

자, 테이블이 정말 간단히 유효화되지 않습니까? 지금 휴지통을 확인하면 비어 있습니다.

여기서 유의할 점은 테이블을 휴지통에 넣는다고 해도 원래 테이블스페이스의 공간이 제거되는 것은 아니라는 것입니다. 공간을 제거하려면 다음을 사용해 휴지통을 지워야 합니다.

PURGE RECYCLEBIN;

하지만 Flashback 기능을 사용하지 않고 테이블을 완전히 삭제하려면 어떻게 해야 할까요? 이 경우 다음을 사용하면 테이블을 영구적으로 삭제할 수 있습니다.

DROP TABLE RECYCLETEST PURGE;

이 명령을 실행하면 테이블 이름이 휴지통 이름으로 변경되는 것이 아니라, 10g 이전 버전에서처럼 영구적으로 삭제됩니다. 휴지통 관리 이 프로세스에서 테이블을 완전히 삭제하지 않아 테이블스페이스를 해제하지 않은 상태에서 삭제된 객체가 테이블스페이스의 모든 공간을 차지하면 어떤 일이 발생할까요? 답은 간단합니다. 그 같은 상황은 결코 발생하지 않습니다. 데이타 파일에 데이타를 추가할 공간을 확보해야 할 정도로 휴지통 데이타가 테이블스페이스로 꽉 차는 상황이 발생하면 테이블스페이스는 이른바 “공간 압축” 상태에 들어갑니다. 위의 시나리오에서 객체는 선입선출 방식으로 휴지통에서 자동으로 지워지며, 종속된 객체(예: 인덱스)는 테이블보다 먼저 제거됩니다.

마찬가지로 특정 테이블스페이스에 정의된 사용자 할당량에도 공간 압축이 발생할 수 있습니다. 테이블에는 사용 가능한 공간이 충분하지만 사용자는 할당된 공간이 부족할 수 있습니다. 이러한 상황에서 Oracle은 해당 테이블스페이스의 사용자에 속한 객체를 자동으로 지웁니다. 이 외에도 여러 가지 방법으로 휴지통을 수동으로 제어할 수 있습니다. 삭제한 후 휴지통에서 TEST라고 명명된 특정 테이블을 삭제하려면 다음을 실행하거나,

PURGE TABLE TEST;

아래와 같이 해당 휴지통 이름을 사용합니다.

PURGE TABLE "BIN$04LhcpndanfgMAAAAAANPw==$0";

이 명령을 실행하면 휴지통에서 TEST 테이블과 인덱스, 제약 조건 등과 같은 모든 종속 객체가 삭제되어 일정 공간을 확보하게 됩니다. 하지만 휴지통에서 인덱스를 영구적으로 삭제하려면 다음을 사용합니다.

purge index in_test1_01;

이렇게 하면 인덱스만 제거되며 테이블의 복사본은 휴지통에 남아 있습니다. 때로는 상위 레벨에서 지우는 것이 유용할 수도 있습니다. 예를 들어, 테이블스페이스 USERS의 휴지통에 있는 모든 객체를 지워야 한다면 다음을 실행합니다.

PURGE TABLESPACE USERS;

휴지통에서 해당 테이블스페이스의 특정 사용자만 지워야 하는 경우도 있습니다. 이 접근방법은 사용자가 많은 수의 과도 상태 테이블을 생성 및 삭제하는 데이타 웨어하우스 유형의 환경에 유용합니다. 다음과 같이 위의 명령을 수정해 지우기 작업을 특정 사용자만으로 제한할 수 있습니다.

PURGE TABLESPACE USERS USER SCOTT;

사용자 SCOTT는 다음 명령으로 휴지통을 지웁니다.

PURGE RECYCLEBIN;

DBA는 다음을 사용해 테이블스페이스의 모든 객체를 지울 수 있습니다.

PURGE DBA_RECYCLEBIN;

위에서 살펴본 것처럼 휴지통은 사용자의 특정한 요구에 맞는 다양한 방식으로 관리할 수 있습니다. 테이블 버전 및 Flashback 다음과 같이 동일한 테이블을 여러 번 생성 및 삭제해야 하는 경우도 흔히 발생합니다.

CREATE TABLE TEST (COL1 NUMBER);
INSERT INTO TEST VALUES (1);
COMMIT;
DROP TABLE TEST;
CREATE TABLE TEST (COL1 NUMBER);
INSERT INTO TEST VALUES (2);
COMMIT;
DROP TABLE TEST;
CREATE TABLE TEST (COL1 NUMBER);
INSERT INTO TEST VALUES (3);
COMMIT;
DROP TABLE TEST;

여기서 TEST 테이블을 순간적으로 되돌린다면 COL1 열의 값은 어떻게 될까요? 기존의 개념에서 보면 휴지통에서 테이블의 첫 번째 버전이 검색되고 COL1 열의 값은 1이 될 것입니다. 하지만 실제로는 첫 번째가 아닌 테이블의 세 번째 버전이 검색되므로 COL1 열의 값은 1이 아닌 3이 됩니다. 이 때 삭제된 테이블의 다른 버전을 검색할 수도 있습니다. 하지만 TEST 테이블이 존재하는 이러한 작업이 불가능한데, 이 경우 다음 두 가지를 선택할 수 있습니다.

• 다음과 같이 이름 바꾸기 옵션을 사용합니다.

  FLASHBACK TABLE TEST TO BEFORE DROP RENAME TO TEST2;
  FLASHBACK TABLE TEST TO BEFORE DROP RENAME TO TEST1;
  

  이렇게 하면 테이블의 첫 번째 버전은 TEST1으로, 두 번째 버전은 TEST2로 다시 유효화됩니다. 또한 TEST1 및 TEST2에서 COL1의 값은 각각 1과 2가 됩니다. 또는
• 복원할 테이블의 특정 휴지통 이름을 사용합니다. 이를 위해 먼저 테이블의 휴지통 이름을 식별한 후 다음을 실행합니다.

  FLASHBACK TABLE "BIN$04LhcpnoanfgMAAAAAANPw==$0" TO BEFORE DROP RENAME TO TEST2;
  FLASHBACK TABLE "BIN$04LhcpnqanfgMAAAAAANPw==$0" TO BEFORE DROP RENAME TO TEST1;
  

  이렇게 하면 삭제된 테이블의 두 가지 버전이 복원됩니다.

주의 사항 삭제 취소 기능을 사용하면 테이블의 이름이 원래대로 돌아가지만 인덱스 및 트리거 같은 연관된 객체는 그렇지 않으며 계속 휴지통 이름으로 남아 있습니다. 또한 뷰 및 프로시저 같이 테이블에 정의된 소스는 재컴파일되지 않으며 무효화된 상태로 남게 됩니다. 이러한 이전 이름들은 수동으로 검색한 다음 순간적으로 되돌린 테이블에 적용해야 합니다. 이 정보는 USER_RECYCLEBIN으로 명명된 뷰에서 관리됩니다. 테이블을 순간적으로 되돌리기 전에 다음 질의를 사용해 이전 이름을 검색합니다.

SELECT OBJECT_NAME, ORIGINAL_NAME, TYPE
FROM USER_RECYCLEBIN
WHERE BASE_OBJECT = (SELECT BASE_OBJECT FROM USER_RECYCLEBIN
WHERE ORIGINAL_NAME = 'RECYCLETEST')
AND ORIGINAL_NAME != 'RECYCLETEST';

OBJECT_NAME                    ORIGINAL_N TYPE
------------------------------ ---------- --------
BIN$04LhcpnianfgMAAAAAANPw==$0 IN_RT_01   INDEX
BIN$04LhcpnganfgMAAAAAANPw==$0 TR_RT      TRIGGER

테이블을 순간적으로 되돌리면 RECYCLETEST 테이블의 인덱스 및 트리거에는 OBJECT_NAME 열에 나타난 이름이 지정됩니다. 위의 질의에서는 원래 이름을 사용해 객체의 이름을 다음과 같이 변경할 수 있습니다.

ALTER INDEX "BIN$04LhcpnianfgMAAAAAANPw==$0" RENAME TO IN_RT_01;
ALTER TRIGGER "BIN$04LhcpnganfgMAAAAAANPw==$0" RENAME TO TR_RT;

한가지 유의해야 할 예외는 비트맵 인덱스입니다. 비트맵 인덱스를 삭제하면 휴지통에 들어가지 않으므로 검색할 수 없습니다. 또한 뷰에서 제약 조건 이름을 검색할 수 없습니다. 따라서 이 인덱스의 이름은 다른 소스에서 변경해야 합니다. Flashback 테이블의 다른 용도 Flashback Drop Table에는 테이블 삭제 작업을 되돌리는 것 외에도 다른 기능이 있습니다. Flashback 질의와 마찬가지로 이를 사용해 테이블을 다른 시점으로 다시 유효화하여 전체 테이블을 “이전” 버전으로 바꿀 수 있습니다. 예를 들어, 다음 문을 사용하면 테이블을 시스템 변경 번호(SCN) 2202666520으로 다시 유효화합니다.

FLASHBACK TABLE RECYCLETEST TO SCN 2202666520;

이 기능은 Oracle Data Pump 기술로 다른 테이블을 생성하고 Flashback으로 테이블을 해당 SCN의 데이타 버전으로 채운 다음, 원래의 테이블을 새 테이블로 바꿉니다. 테이블을 어느 정도까지 순간적으로 되돌릴 수 있는지 확인하려면 Oracle Database 10g의 버전 관리 기능을 사용합니다. (자세한 내용은 이 시리즈의 1주 부분을 참조하십시오.) 또한 Flashback 절에 SCN 대신 타임 스탬프를 지정할 수도 있습니다. Flashback 테이블 가능에 대한 자세한 내용은 Oracle Database Administrator's Guide 10g Release 1 (10.1)을 참조하십시오.

한층 강화된 엑스포트/임포트: Oracle Data Pump

Oracle Database 10g 유틸리티로 크게 향상된 데이타 이동 기능

지금까지 엑스포트/임포트 툴세트는 열악한 속도에 대한 불만에도 불구하고 최소한의 노력으로 여러 플랫폼에 데이타를 전송하기 위해 사용해 온 유틸리티였습니다. 임포트는 단순히 엑스포트 덤프 파일에서 각 레코드를 읽고 이를 일반적인 INSERT INTO 명령을 사용해 대상 테이블에 삽입하기만 하므로 임포트 프로세스가 느린 것은 그리 놀랄만한 일이 아닙니다.

이제 프로세스 속도가 월등히 향상된 Oracle Database 10g의 보다 새롭고 빠른 엑스포트/임포트 툴킷인 Oracle Data Pump, the newer and faster sibling of the export/import toolkit in Oracle Database 10g를 사용해 보십시오.

Data Pump는 엑스포트/임포트 프로세스의 전체 구성을 나타냅니다. 일반적인 SQL 문을 사용하는 대신 독점 API로 데이타를 현저하게 빠른 속도로 로드 및 언로드합니다. 제가 테스트해본 결과, 직접 모드의 엑스포트보다 성능이 10-15배 향상되었으며, 임포트 프로세스 성능도 5배 이상 증가했습니다. 또한 엑스포트 유틸리티와 달리 프로시저 같은 특정 유형의 객체만 추출할 수 있습니다.

Data Pump Export

이 새로운 유틸리티는 원래의 엑스포트인 exp와 구분하기 위해 expdp라고 합니다. 이 예에서는 Data Pump를 사용해 약 3GB 크기의 대형 테이블인 CASES를 엑스포트합니다. Data Pump는 서버 측에서 파일 조작을 사용하여 파일을 생성하고 읽으므로 디렉토리를 위치로 사용합니다. 여기서는 filesystem /u02/dpdata1을 사용해 덤프 파일을 유지할 예정입니다.

create directory dpdata1 as '/u02/dpdata1';
grant read, write on directory dpdata1 to ananda;

그리고 다음과 같이 데이타를 엑스포트합니다.

expdp ananda/abc123 tables=CASES directory=DPDATA1 
  dumpfile=expCASES.dmp job_name=CASES_EXPORT

이제 이 명령의 각 부분을 분석해 보겠습니다. 사용자 ID/암호 조합, 테이블 및 덤프 파일 매개변수는 말 그대로이므로 설명이 필요 없습니다. 원래의 엑스포트와 달리 파일이 클라이언트가 아닌 서버에 생성됩니다. 위치는 디렉토리 매개변수 값 DPDATA1로 지정되며, 이는 이전에 생성된 /u02/dpdata1을 가리킵니다. 또한 프로세스를 실행하면 서버의 디렉토리 매개변수로 지정된 위치에 로그 파일이 생성됩니다. 이 프로세스에는 기본적으로 DPUMP_DIR로 명명된 디렉토리가 사용되므로 DPDATA1 대신 생성할 수 있습니다.

위의 job_name 매개변수를 보면 원래의 엑스포트에 없는 특별한 항목이 하나 있습니다. 모든 Data Pump 작업은 작업(job)을 통해 이뤄집니다. Data Pump 작업은 DBMS 작업과 달리 주 프로세스를 대신해 데이타를 처리하는 단순한 서버 프로세스입니다. 마스터 제어 프로세스라고 하는 이 주 프로세스는 Advanced Queuing을 통해 이러한 작업 노력을 조정하는데, 이는 마스터 테이블이라고 하는 런타임 시 생성된 특수 테이블을 통해 이뤄집니다. 제시한 예에서 expdp를 실행하면서 사용자 ANANDA의 스키마를 검사하면 job_name 매개변수에 해당되는 CASES_EXPORT 테이블이 있음을 알 수 있습니다. expdp가 종료되면 이 테이블은 삭제됩니다.

엑스포트 모니터링

DPE(Data Pump Export)를 실행하면서 Control-C를 누르면 화면상에 메시지 표시를 중지하지만 프로세스 자체를 엑스포트하지는 않습니다. 대신 다음과 같이 DPE 프롬프트를 표시합니다. 이제 프로세스는 소위 “대화식” 모드에 들어갑니다.

Export>

이 접근방법에서는 DPE 작업에 여러 명령을 입력할 수 있습니다. 요약을 확인하려면 다음과 같이 프롬프트에 STATUS 명령을 사용합니다.

Export> status
Job: CASES_EXPORT
  Operation: EXPORT                         
  Mode: TABLE                          
  State: EXECUTING                      
  Degree: 1
  Job Error Count: 0
  Dump file:  /u02/dpdata1/expCASES.dmp 
      bytes written =  2048

Worker 1 Status:
  State: EXECUTING                      
  Object Schema: DWOWNER
  Object Name: CASES
  Object Type: TABLE_EXPORT/TBL_TABLE_DATA/TABLE/TABLE_DATA
  Completed Objects: 1
  Total Objects: 1
  Completed Rows: 4687818

하지만 이것은 상태 표시일 뿐이며 엑스포트는 백그라운드에서 실행되고 있습니다. 화면의 메시지를 계속 확인하려면 Export> 프롬프트에서 CONTINUE_CLIENT 명령을 사용합니다.

병렬 작업

PARALLEL 매개변수를 통해 엑스포트시 하나 이상의 스레드를 사용하면 작업 속도를 크게 개선할 수 있습니다. 스레드마다 개별 덤프 파일을 생성하므로 매개변수 dumpfile은 병렬화 만큼 많은 여러 항목을 갖게 됩니다. 또한 하나씩 명시적으로 입력하는 대신 다음과 같이 대체 문자를 파일 이름으로 지정할 수 있습니다.

expdp ananda/abc123 tables=CASES directory=DPDATA1 
  dumpfile=expCASES_%U.dmp parallel=4 job_name=Cases_Export

여기서 dumpfile 매개변수에 어떻게 대체 문자 %U가 생기는지 주목합니다. 이 대체 문자는 파일이 필요에 따라 생성되고 형식은 expCASES_nn.dmp이 됨을 나타내는데, 여기서 nn은 01에서 시작하며 필요에 따라 증가하게 됩니다. 병렬 모드에서는 상태 화면에 네 개의 작업자 프로세스가 표시됩니다. (기본 모드에서는 프로세스가 한 개만 표시됩니다.) 모든 작업자 프로세스가 데이타를 동시에 추출하며 진행률을 상태 화면에 표시합니다. 데이타베이스 파일 및 덤프 파일 디렉토리 파일 시스템에 액세스하려면 I/O 채널을 반드시 구분해야 합니다. 그렇지 않으면 Data Pump 작업의 유지와 관련된 오버헤드가 병렬 스레드의 이점을 뛰어넘어 성능을 저하시킬 수 있습니다. 병렬화는 테이블 수가 병렬 값보다 크고 테이블이 대규모인 경우에만 적용됩니다. 데이타베이스 모니터링 데이타베이스 뷰에서 실행되는 Data Pump 작업에 관해서도 자세한 정보를 확인할 수 있습니다. 작업을 모니터링하는 기본 뷰는 DBA_DATAPUMP_JOBS로 작업에서 실행되는 작업자 프로세스(DEGREE 열)의 수를 알려줍니다. 그 밖의 중요한 뷰에는 DBA_DATAPUMP_SESSIONS가 있는데, 이전 뷰 및 V$SESSION과 조인하면 주 포그라운드(Foreground) 프로세스 세션의 SID를 확인할 수 있습니다.

select sid, serial#
from v$session s, dba_datapump_sessions d
where s.saddr = d.saddr;

이 명령에는 포그라운드 프로세스의 세션이 표시됩니다. 경고 로그에서는 보다 유용한 정보를 얻을 수 있습니다. 프로세스가 시작되면 MCP 및 작업자 프로세스가 다음과 같이 경고 로그에 나타납니다.

kupprdp: master process DM00 started with pid=23, OS id=20530 to execute - 
  SYS.KUPM$MCP.MAIN('CASES_EXPORT', 'ANANDA');

kupprdp: worker process DW01 started with worker id=1, pid=24, OS id=20532 to execute - 
  SYS.KUPW$WORKER.MAIN('CASES_EXPORT', 'ANANDA');

kupprdp: worker process DW03 started with worker id=2, pid=25, OS id=20534 to execute - 
  SYS.KUPW$WORKER.MAIN('CASES_EXPORT', 'ANANDA');

경고 로그에는 Data Pump 작업을 위해 시작된 세션의 PID가 표시됩니다. 실제 SID는 이 질의를 사용해 확인합니다.

select sid, program from v$session where paddr in 
 (select addr from v$process where pid in (23,24,25));

PROGRAM 열에는 경고 로그 파일의 이름에 해당되는 프로세스 DM(마스터 프로세스) 또는 DW(작업자 프로세스)가 표시됩니다. SID 23 같은 작업자 프로세스에서 병렬 질의를 사용하는 경우, V$PX_SESSION 뷰에서 확인할 수 있습니다. 이 뷰에는 SID 23으로 표시된 작업자 프로세스에서 실행되는 모든 병렬 질의 세션이 나타납니다.

select sid from v$px_session where qcsid = 23;

V$SESSION_LONGOPS 뷰에서는 작업 완료에 걸리는 시간을 예측하는 또 다른 유용한 정보를 얻을 수 있습니다.

select sid, serial#, sofar, totalwork
from v$session_longops
where opname = 'CASES_EXPORT'
and sofar != totalwork;

totalwork 열에는 총 작업량이 표시되는데, 이 중 현재까지 sofar 작업량을 완료했으므로 이를 통해 얼마나 더 시간이 걸릴지 예측할 수 있습니다. Data Pump Import 하지만 Data Pump에서 가장 눈에 잘 띄는 부분은 데이타 임포트 성능입니다. 이전에 엑스포트된 데이타를 임포트하려면 다음을 사용합니다.

impdp ananda/abc123 directory=dpdata1 dumpfile=expCASES.dmp job_name=cases_import

임포트 프로세스의 기본 작업 방식은 테이블 및 연관된 모든 객체를 생성하고 테이블이 있는 상태에서 오류를 만들어 내는 것입니다. 기존 테이블에 데이타를 추가해야 하는 경우 위의 명령행에 TABLE_EXISTS_ACTION=APPEND를 사용할 수 있습니다. DPE와 마찬가지로 프로세스 도중 Control-C를 누르면 DPI(Date Pump Import)의 대화식 모드를 표시하며 Import>가 프롬프트됩니다. 특정 객체 작업 한 사용자에서 특정 프로시저만 엑스포트하여 다른 데이타베이스나 사용자에 다시 생성해야 했던 경험이 있습니까? 기존의 엑스포트 유틸리티와 달리 Data Pump는 특정 유형의 객체만 엑스포트할 수 있습니다. 예를 들어, 다음 명령을 실행하면 테이블, 뷰 또는 함수 등은 제외하고 오로지 프로시저만 엑스포트할 수 있습니다.

expdp ananda/iclaim directory=DPDATA1 dumpfile=expprocs.dmp include=PROCEDURE

To export only a few specific objects--say, function FUNC1 and procedure PROC1--you could use

expdp ananda/iclaim directory=DPDATA1 dumpfile=expprocs.dmp 
  include=PROCEDURE:\"=\'PROC1\'\",FUNCTION:\"=\'FUNC1\'\"

이 덤프 파일은 소스의 백업으로 사용됩니다. 때로는 이를 사용해 DDL 스크립트를 생성하여 나중에 사용할 수도 있습니다. DDL 스크립트 파일을 생성하려면 SQLFILE이라고 하는 특수 매개변수를 사용합니다.

impdp ananda/iclaim directory=DPDATA1 dumpfile=expprocs.dmp sqlfile=procs.sql

이 명령은 DPDATA1로 지정된 디렉토리에 procs.sql로 명명된 파일을 생성하며 엑스포트 덤프 파일 내의 객체 스크립트가 들어 있습니다. 이 접근방법을 사용하면 다른 스키마에 원본을 보다 신속하게 생성할 수 있습니다. INCLUDE 매개변수를 사용하면 객체가 덤프 파일에서 포함 또는 제외되도록 정의할 수 있습니다. 예를 들어, INCLUDE=TABLE:"LIKE 'TAB%'" 절을 사용하면 이름이 TAB로 시작하는 테이블만 엑스포트할 수 있습니다. 마찬가지로 INCLUDE=TABLE:"NOT LIKE 'TAB%'" 구문을 사용하면 TAB으로 시작하는 모든 테이블을 제외시킬 수 있습니다. 아니면 EXCLUDE 매개변수를 사용해 특정 객체를 제외시킬 수 있습니다. Data Pump를 사용하면 외부 테이블로 테이블스페이스를 이동할 수도 있는데, 이렇게 하면 진행 중인 병렬화를 다시 정의하고 기존 프로세스에 테이블을 추가하는 등의 작업에 매우 효과적입니다(이는 본 문서의 범위를 벗어난 내용이므로 자세한 내용은 Oracle Database Utilities 10g Release 1 10.1을 참조하십시오). 다음 명령을 실행하면 Data Pump 엑스포트 유틸리티에서 사용 가능한 매개변수 목록이 생성됩니다.

expdp help=y

마찬가지로 impdp help=y 명령을 실행하면 DPI의 모든 매개변수가 표시됩니다. Data Pump 작업을 실행하는 동안 DPE 또는 DPI 프롬프트에 STOP_JOB을 실행하여 작업을 일시 중지한 다음 START_JOB으로 다시 시작할 수 있습니다. 이 기능은 공간이 부족하여 계속하기 전에 정정해야 하는 경우 유용하게 사용할 수 있습니다. 자세한 내용은 Oracle Database Utilities 10g Release 1 10.1 설명서 1부를 참조하십시오.

세번째.
손쉬운 이름 변경: 향상된 테이블스페이스 관리

Sparser인 SYSTEM, 사용자 기본 테이블스페이스 정의 지원, 새로운 SYSAUX 및 이름 바꾸기 등으로 수월해진 테이블스페이스 관리

누구나 SYSTEM 테이블스페이스에 SYS 및 SYSTEM을 제외한 세그먼트를 생성하면서 좌절감에 머리를 쥐어 뜯으며 고민한 경험이 있을 것입니다.

Oracle9i Database 이전 버전에서는 사용자를 생성할 때 DEFAULT TABLESPACE를 명시하지 않으면 기본값이 SYSTEM 테이블스페이스로 설정되었습니다. 사용자가 세그먼트를 생성하는 동안 테이블스페이스를 명시적으로 지정하지 않는 경우, 명시적으로 부여 받은 것이든 시스템 권한 UNLIMITED TABLESPACE를 통한 것이든 사용자가 테이블스페이스 할당량을 갖고 있으면 SYSTEM에 생성되었습니다. Oracle9i에서는 DBA가 명시적인 임시 테이블스페이스 절 없이 생성된 모든 사용자에 대해 기본 임시 테이블스페이스를 지정하도록 하여 이 문제를 어느 정도 해결했습니다.

Oracle Database 10g에서도 이와 유사하게 사용자에게 기본 테이블스페이스를 지정할 수 있습니다. 우선, 데이타베이스를 생성하는 과정에 CREATE DATABASE 명령은 DEFAULT TABLESPACE 절을 포함할 수 있습니다. 데이타베이스의 생성이 끝나면 다음을 실행하여 기본 테이블스페이스를 만들 수 있습니다.

ALTER DATABASE DEFAULT TABLESPACE <tsname>;

DEFAULT TABLESPACE 절 없이 생성된 모든 사용자는 기본값으로 <tsname>을 갖게 됩니다. 기본 테이블스페이스는 이 ALTER 명령을 사용해 언제든 변경하여 다른 위치의 기본값으로 다른 테이블스페이스를 지정할 수 있습니다.

여기서 중요한 점은 일부 사용자에 대해 다른 어떤 항목이 명시적으로 지정되어 있어도 이전 테이블스페이스와 함께 모든 사용자의 기본 테이블스페이스가 <tsname>으로 변경된다는 것입니다. 예를 들어, 사용자 생성 도중 사용자 USER1 및 USER2의 기본 테이블스페이스를 각각 TS1 및 TS2로 명시적으로 지정했다고 가정합니다. 데이타베이스의 현재 기본 테이블스페이스는 TS2지만, 나중에는 데이타베이스의 기본 테이블스페이스가 TS1으로 변경됩니다. USER2의 기본 테이블스페이스를 TS2로 명시적으로 지정했다 하더라도 TS1으로 바뀌게 되므로 이러한 부작용을 염두에 둬야 합니다!

데이타베이스 생성 과정에 기본 테이블스페이스를 지정하지 않으면 기본값이 SYSTEM으로 설정됩니다. 하지만 기존 데이타베이스의 기본 테이블스페이스는 어떻게 알 수 있을까요? 우선, 다음 질의를 실행합니다.

SELECT PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE';

DATABASE_PROPERTIES 뷰에는 기본 테이블스페이스 외에도 기본 임시 테이블스페이스, 전역 데이타베이스 이름, 시간대 등과 같은 몇 가지 매우 중요한 정보가 표시됩니다.

중요하지 않은 스키마의 기본 테이블스페이스

인텔리전트 에이전트 사용자 DBSNMP 및 데이타 마이닝 사용자 ODM 같은 여러 스키마는 사용자 작업과 직접적인 관련이 없지만 데이타베이스 무결성을 위해 나름대로 중요한 역할을 합니다. 이러한 스키마의 일부는 기본 테이블스페이스가 SYSTEM인데, 이는 해당 특수 테이블스페이스 내에서 객체가 확산되는 또 다른 이유이기도 합니다.

Oracle Database 10g에는 이러한 스키마의 객체를 보유하는 SYSAUX라는 새로운 테이블스페이스가 도입되었습니다. 이 테이블스페이스는 데이타베이스 생성 도중 자동으로 생성되며 지역적으로 관리됩니다. 또한 데이타 파일 이름만 유일하게 변경할 수 있습니다.

이 접근방법은 SYSTEM이 손상되어 전체 데이타베이스를 복구해야 할 때 복구를 지원합니다. 데이타베이스는 계속 실행하면서 SYSAUX의 객체를 일반 사용자 객체로 복구할 수 있습니다.

하지만 SYSAUX에 있는 이들 스키마의 일부를 다른 테이블스페이스로 옮겨야 한다면 어떻게 할까요? 크기가 늘어나 결국에는 테이블스페이스를 꽉 채우는 일이 빈번한 LogMiner에 사용되는 객체를 예로 들어봅시다. 관리 효율을 높이기 위해 이를 저마다의 테이블스페이스로 옮기는 방법을 고려할 수도 있을 것입니다. 하지만 이것이 최선의 방법일까요?

DBA라면 이러한 특수 객체를 옮기기 위한 올바른 프로시저를 알고 있어야 합니다. 다행히도 Oracle Database 10g에는 이러한 추측 작업을 수행하는 새로운 뷰가 있습니다. 이 V$SYSAUX_OCCUPANTS 뷰에는 SYSAUX 테이블스페이스에 있는 스키마의 이름, 설명, 현재 사용 공간 그리고 이동 방법이 나와 있습니다 (See 표 1 참조).

여기서 LogMiner가 어떻게 분명히 7,488KB를 차지하고 있는 것으로 표시되는지에 주목합니다. LogMiner는 SYSTEM 스키마에 속해 있으며 객체를 이동하려면 패키지 프로시저 SYS.DBMS_LOGMNR_D.SET_TABLESPACE를 실행합니다. 하지만 STATSPACK 객체의 경우 뷰에 엑스포트/임포트 접근방법을 사용하는 것이 좋으며 Streams에는 이동 프로시저가 없으므로 SYSAUX 테이블스페이스에서 이를 쉽게 옮길 수 없습니다. MOVE_PROCEDURE 열에는 SYSAUX에 기본적으로 상주하는 거의 모든 툴에 대한 올바른 이동 프로시저가 표시됩니다. 이동 프로시저는 역방향으로도 사용하여 객체를 다시 SYSAUX 테이블스페이스로 가져올 수 있습니다.

테이블스페이스 이름 바꾸기

데이타 웨어하우스 환경에서는 일반적으로 데이타 마트 아키텍처가 데이타베이스 사이에서 테이블스페이스를 이동합니다. 하지만 원본 및 대상 데이타베이스는 테이블스페이스 이름이 서로 달라야 합니다. 이름이 같은 테이블스페이스가 두 개이면 대상 테이블스페이스의 세그먼트를 다른 테이블스페이스로 옮기고 테이블스페이스를 다시 생성해야 하는데 말처럼 쉽지가 않습니다.

Oracle Database 10g에는 편리한 솔루션이 있어 영구 또는 임시 여부에 관계 없이 기존 테이블스페이스(SYSTEM 및 SYSAUX 제외)의 이름을 다음 명령을 사용해 간단히 변경할 수 있습니다.

ALTER TABLESPACE <oldname> RENAME TO <newname>;

이 기능은 아카이브 프로세스에도 유용하게 사용할 수 있습니다. 매출 기록을 관리하기 위해 범위로 분할된 테이블이 있으며, 매월의 파티션은 해당 월의 이름을 따 명명된 테이블스페이스에 있습니다. 예를 들어, 1월의 파티션에는 JAN이라는 이름이 지정되며 JAN으로 명명된 테이블스페이스에 상주합니다. 보존 정책 기간은 12개월입니다. 따라서 2004년 1월에 2003년 1월의 데이타를 아카이브할 수 있게 되는 것입니다. 대략적인 작업 과정은 다음과 유사한 형태가 됩니다.

1. ALTER TABLE EXCHANGE PARTITION을 사용해 파티션 JAN에서 독립형 테이블 JAN03을 생성합니다.
2. 테이블스페이스 이름을 JAN03으로 변경합니다.
3. 테이블스페이스 JAN03에 설정된 이동 가능한 테이블스페이스를 생성합니다.
4. 테이블스페이스 JAN03의 이름을 다시 JAN으로 변경합니다.
5. 비어 있는 파티션을 다시 테이블로 교환합니다.

1, 2, 4 및 5단계는 순조롭게 진행되며 리두 및 실행 취소 공간 같은 리소스를 과도하게 소모하지 않습니다. 3단계는 단순히 파일을 복사하고 JAN03의 데이타 딕셔너리만 엑스포트하면 되므로 마찬가지로 매우 간단한 프로세스입니다. 이전에 아카이브한 파티션을 다시 유효화해야 하는 경우, 프로시저는 동일한 프로세스를 반대로 수행하는 것만큼 간단합니다.

Oracle Database 10g는 이러한 이름 바꾸기를 처리하는 방식에 있어 상당히 지능적입니다. UNDO로 사용되는 테이블스페이스 또는 기본 임시 테이블스페이스의 이름을 변경하는 경우 혼동이 발생할 수 있습니다. 하지만 데이타베이스가 필요한 레코드를 자동으로 조정하여 변경 내용을 반영합니다. 예를 들어, 기본 테이블스페이스 이름을 USERS에서 USER_DATA로 변경하면 DATABASE_PROPERTIES가 자동으로 변경됩니다. 변경에 앞서 다음 질의가

select property_value from database_properties
where property_name = 'DEFAULT_PERMANENT_TABLESPACE';

USERS를 반환합니다. 다음 문을 실행하고 나면

alter tablespace users rename to user_data;

USER_DATA에 대한 모든 참조가 USER_DATA로 변경되었므로 위의 질의가 USER_DATA를 반환합니다.

기본 임시 테이블스페이스를 변경하는 방법도 이와 동일합니다. UNDO 테이블스페이스 이름을 변경하더라도 다음과 같이 SPFILE에 변경을 트리거합니다.

SQL> select value from v$spparameter where name = 'undo_tablespace';

VALUE
--------
UNDOTBS1

SQL> alter tablespace undotbs1 rename to undotbs;

Tablespace altered.

SQL> select value from v$spparameter where name = 'undo_tablespace';

VALUE
--------
UNDOTBS

결론

객체 처리 기능은 최근의 여러 Oracle 버전을 거치면서 꾸준히 향상되었습니다. Oracle8i에는 한 테이블스페이스에서 다른 테이블스페이스로의 테이블 이동이 도입되었으며, Oracle 9i Database R2는 열 이름 변경 기능을 갖추게 되었습니다. 그리고 지금은 테이블스페이스 자체의 이름을 변경할 수 있는 수준에 이르고 있습니다. 또한 이처럼 기능이 향상되면서 데이타 웨어하우스 또는 마트 환경 등에서 DBA의 작업 부담을 크게 덜어주고 있습니다.

원문 : http://www.oracle.com/technology/global/kr/pub/articles/10gdba/week2_10gdba.html

두번째.
얼마나 더 걸리나요?: 롤백 모니터링

롤백 작업 시간의 정확한 예측

아직 멀었나요? 얼마나 더 걸리죠?

귀에 익은 말들입니까? 이런 질문은 아이들이 좋아하는 테마 공원에 가는 도중 갈수록 횟수를 더하며 끊임없이 뒷좌석에서 들려올 수 있습니다. 이럴 때, 앞으로 정확히 얼마나 더 걸릴지 말해주거나 적어도 그 답을 혼자만이라도 알고 있다면 낫지 않겠습니까?

긴 실행 트랜잭션을 롤백했을 때도 이와 마찬가지로 여러 사용자들이 바짝 따라다니며 같은 질문을 하는 경우가 많습니다. 롤백이 진행되면서 트랜잭션에 잠금이 발생하고 일반적인 처리 성능이 저하되므로 이러한 질문을 하는 것은 당연합니다. Oracle 9i Database 이전 버전에서는 다음 질의를 실행하면,

SELECT USED_UREC
FROM V$TRANSACTION;

현재 트랜잭션에서 사용하는 실행 취소 레코드 수를 반환하며, 반복해서 실행하면 롤백 프로세스가 진행되면서 실행 취소 레코드가 해제되므로 계속해서 줄어든 값이 표시됩니다. 그런 다음 일정 간격의 스냅샷을 얻어 비율을 계산한 후 결과를 추정하여 종료 시간을 예측할 수 있습니다.

V$TRANSACTION 뷰에 START_TIME이라는 열이 있지만, 이 열에는 전체 트랜잭션의 시작 시간만 표시됩니다(즉, 롤백 실행 전). 따라서 실제로 롤백을 실행한 시간을 알 수 있는 방법은 추정뿐입니다.

트랜잭션 롤백을 위한 통계 확장

Oracle Database 10g에서는 이 과제를 간단히 수행할 수 있습니다. 트랜잭션을 롤백하면 이벤트가 V$SESSION_LONGOPS 뷰에 기록되어 긴 실행 트랜잭션을 표시합니다. 롤백을 위한 프로세스가 6초 이상 걸리는 경우 레코드가 뷰에 나타납니다. 롤백의 실행이 끝나면 모니터 스크린을 가리고도 다음 질의를 실행할 수 있을 것입니다.

select time_remaining
from v$session_longops
where sid = <sid of the session doing the rollback>;

이제 이 V$SESSION_LONGOPS 뷰가 얼마나 중요한지 알았으니 이 뷰의 다른 기능들에 대해 살펴보도록 하겠습니다. Oracle Database 10g 이전 버전에도 이 뷰가 있지만 롤백 트랜잭션 정보는 캡처되지 않았습니다. 모든 열을 읽기 쉬운 방식으로 표시하기 위해 Tom Kyte가 AskTom.com에 설명한 PRINT_TABLE 함수를 사용하겠습니다. 이 프로시저는 열을 일반적인 행이 아닌 테이블로만 표시합니다.

SQL> set serveroutput on size 999999
SQL> exec print_table('select * from v$session_longops where sid = 9')
SID                           : 9
SERIAL#                       : 68
OPNAME                        : Transaction Rollback
TARGET                        :
TARGET_DESC                   : xid:0x000e.01c.00000067
SOFAR                         : 10234
TOTALWORK                     : 20554
UNITS                         : Blocks
START_TIME                    : 07-dec-2003 21:20:07
LAST_UPDATE_TIME              : 07-dec-2003 21:21:24
TIME_REMAINING                : 77
ELAPSED_SECONDS               : 77
CONTEXT                       : 0
MESSAGE                       : Transaction Rollback: xid:0x000e.01c.00000067 :
                                 10234 out of 20554 Blocks done
USERNAME                      : SYS
SQL_ADDRESS                   : 00000003B719ED08
SQL_HASH_VALUE                : 1430203031
SQL_ID                        : 306w9c5amyanr
QCSID                         : 0

이제 이 열들을 하나하나 자세히 살펴보도록 하겠습니다. 뷰에는 이전 세션의 모든 긴 실행 작업 기록이 포함되어 있으므로 세션에는 긴 실행 작업이 하나 이상일 수 있습니다. OPNAME 열에는 이 레코드가 “트랜잭션 롤백”용이라고 명시되어 있어 올바른 작업 방향으로 이끌어줍니다. TIME_REMAINING 열은 이전에 기술한 예측 잔여 시간을 초 단위로 표시하며, ELAPSED_SECONDS 열에는 현재까지 사용된 시간이 표시됩니다.

그렇다면 이 테이블에 어떻게 예측 잔여 시간을 나타낼까요? 단서는 총 수행 작업량을 나타내는 TOTALWORK 열과 현재까지 수행한 작업량을 나타내는 SOFAR 열에 있습니다. 작업 단위는 UNITS 열에 나와 있습니다. 여기서는 블록에 들어 있으므로 현재까지 20,554개의 블록 중 총 10,234개를 롤백한 것입니다. 또한 현재까지의 작업 소요 시간은 77초이므로 잔여 블록의 롤백 시간은 다음과 같이 구합니다.

77 * ( 10234 / (20554-10234) ) ≈ 77 seconds

반드시 이러한 경로를 통해서만 수치를 얻어야 하는 것은 아니지만 가장 명확한 방법입니다. 마지막으로 LAST_UPDATE_TIME 열에는 뷰 내용이 통용되는 시간이 표시되어 결과를 보다 쉽게 해석할 수 있도록 해줍니다.

SQL 문

또 하나의 중요하면서도 새로운 정보는 롤백 중인 SQL 문의 식별자입니다. 이전에는 롤백 중인 SQL 문을 가져오기 위해 SQL_ADDRESS 및 SQL_HASH_VALUE를 사용했습니다. 새로운 열인 SQL_ID는 아래에서처럼 V$SQL 뷰의 SQL_ID에 해당됩니다.

SELECT SQL_TEXT
FROM V$SQL
WHERE SQL_ID = <value of SQL_ID from V$SESSION_LONGOPS>;

이 질의를 실행하면 롤백된 문을 반환하므로 SQL 문의 주소 및 해시 값과 함께 추가 검사를 제공합니다.

병렬 인스턴스 복구

DML 작업이 병렬이었으면 QCSID 열에 병렬 질의 서버 세션의 SID가 표시됩니다. 인스턴스 복구 및 실패한 트랜잭션의 후속 복구 과정 같은 병렬 롤백을 실행하는 경우 대개 이 정보를 유용하게 사용할 수 있습니다.

예를 들어, 대규모 업데이트를 수행하는 도중 인스턴스가 비정상적으로 종료되었다고 가정합시다. 인스턴스가 나타나면 실패한 트랜잭션이 롤백됩니다. 병렬 복구를 위한 초기화 매개변수 값을 사용할 수 있는 경우 롤백이 정규 트랜잭션 롤백에서 발생하므로 직렬이 아닌 병렬로 이뤄집니다. 그런 다음 롤백 프로세스의 완료 시간을 예측합니다.

V$FAST_START_TRANSACTIONS 뷰에는 실패한 트랜잭션을 롤백하기 위해 발생한 트랜잭션이 표시됩니다. 이와 유사한 뷰인 V$FAST_START_SERVERS에는 롤백에 실행되는 병렬 질의 서버의 수가 나와 있습니다. 이전 버전에서는 이러한 두 개의 뷰가 있지만, 트랜잭션 식별자를 나타내는 새로운 XID 열이 추가되어 조인이 더 수월해졌습니다. Oracle9i Database 이전에서는 세 개 열(USN – 실행 취소 번호, SLT – 실행 취소 세그먼트 내의 슬롯 번호 및 SEQ – 시퀀스 번호)의 뷰에 조인해야 했습니다. 또한 상위 집합은 PARENTUSN, PARENTSLT 및 PARENTSEQ에 표시되었습니다. 하지만 Oracle Database 10g에서는 XID 열의 뷰에만 조인하면 되고 상위 XID는 알기 쉽게 PXID로 표시됩니다.

가장 유용한 정보는 V$FAST_START_TRANSACTIONS 뷰의 RCVSERVERS 열에 나와 있습니다. 병렬 롤백을 진행하면 이 열에 병렬 질의 서버의 수가 나타나며, 이를 통해 다음과 같이 시작된 병렬 질의 프로세스의 수를 확인할 수 있습니다.

select rcvservers from v$fast_start_transactions;

출력에 1로만 나타나면 트랜잭션이 가장 비효율적인 방식인 SMON 프로세스를 통해 직렬로 롤백되고 있는 것입니다. 이 경우 초기화 매개변수 RECOVERY_PARALLELISM을 0과 1을 제외한 값으로 수정한 다음 병렬 롤백 인스턴스를 다시 시작할 수 있습니다. 그런 다음, ALTER SYSTEM SET FAST_START_PARALLEL_ROLLBACK = HIGH를 실행하면 CPU 수의 네 배나 되는 병렬 서버를 생성할 수 있습니다.

위의 질의 출력이 1을 제외한 임의의 값이 표시되면 병렬 롤백이 이뤄지는 것입니다. 이 경우 동일한 뷰(V$FAST_START_TRANSACTIONS)를 질의하여 상위 및 하위 트랜잭션(상위 트랜잭션 ID – PXID 및 하위 트랜잭션 ID – XID)을 가져올 수 있습니다. 또한 XID는 이 뷰와 V$FAST_START_SERVERS를 조인하는 데 사용하여 상세 내역을 추가로 가져올 수 있습니다.

결론

요컨대 Oracle Database 10g에서 긴 실행 트랜잭션을 롤백할 때는 병렬 인스턴스 복구 세션이나 사용자 실행 롤백 문으로 표시한 다음, V$SESSION_LONGOPS 뷰를 살펴보고 향후 소요 시간의 결과를 예측하기만 하면 됩니다. 이제 테마 공원에 도착하는 시간만 예측할 수 있으면 되겠군요!

원문 : http://blog.naver.com/novaculite?Redirect=Log&logNo=120012048251

튜닝의 도구 – SQL*TRACE와 TKPROF

Oracle의 SQL*TRACE는 사용자가 실행 한 SQL문에 대해 구문분석(Parsing), 실행(execute), 추출(fetch) 부분으로 나누어 각 단계에서 걸리는 Overhead와 시간 등의 통계 정보를 일정한 형태로 저장 합니다. EXPALIN PLAN에서 제공하는 정보와 더블어 CPU/IO의 필요량, 실행계획의 각 단계에서의 레코드 개수등의 정보도 확인 가능 합니다.  EXPLAIN PLAN 명령어와 함께 자주 사용되는 튜닝의 도구 입니다.

SQL*TRACE나 TKPROF를 실행 했을 때의 결과는 이해하기가 쉽지 않지만 강력한 튜닝의 도구 입니다. SQL*TRACE에 의해 분석되는 결과는 바이너리 형태로 운영체제의 파일 시스템에 생성 됩니다. 물론 바이너리 이므로 결과를 직접 눈으로 보면 이해가 되지 않지만 TKPROF 유틸리티를 이용하여 텍스트 파일 형태로 변환 시켜 확인이 가능 합니다.

SQL*TRACE의 결과는 데이터베이스 전체 또는 특정 세션에 대해 적용 할 수 있는데 데이터베이스 전체에 트레이스를 적용하면 실제 Application 수행에 추가적인 부하를 가져오므로 특별한 경우를 제외하고 전체 데이터베이스 시스템에 TRACE를 거는 것은 삼가 해야 합니다. 대부분은 특정 세션에 대해서만 부분적으로 활성화 하여 사용 합니다.


SQL*TRACE의 사용

SQL TRACE를 사용하기 전에 몇 가지 설정이 필요한데 먼저 초기파일에서 USER_DUMP_DEST 파라미터를 확인해야 합니다. 이 매개변수는 TRACE를 실행 할 때 생성되는 파일의 위치를 설정 하는 것입니다. 또한 시간 정보를 TRACE 항목에 추가할려면 TIMED_STATISTICS 항목을 TRUE로 해야 하거나 SQL*Plus등에서는 alter session set timed_statistics=true 라고 해주어야 합니다. 아래에 자세히 확인 하도록 합니다.

TIMED_STATISTICS

시간 통계 정보에 대해 수집여부를 결정, 기본값은 false
세션레벨에서는 alter session set timed_statistics=true라고 하면 됩니다.

MAX_DUMP_FILE_SIZE

TRACE의 결과로 생기는 바이너리 파일의 최대 사이즈를 단위는 블록 입니다. 기본값은 500 블록 입니다. 또한 세션 레벨에서 다음과 같이 지정 가능 합니다. Alter session set max_dump_file_size = 800(800개의 시스템 블록)

USER_DUMP_DEST

TRACE의 결과로 생기는 바이너리 파일의 위치를 지정 합니다. 세션레벨에서는 alter session set user_dump_dest = “C:\oracle\admin\wink\udump” 등으로 지정 합니다.

위의 세개의 파라미터를 init.ora 파일에 지정하였다면 SQL*TRACE의 시작을 전체 데이터베이스에서 할건지 세션 레벨에서 할건지를 정할 수가 있습니다. 인스턴스 레벨에서 할려면 init.ora 파일에서 SQL_TRACE 항목을 TRUE로 설정하면 되구요 세션 레벨에서 할려면 alter session set sql_trace = true 라고 하면 됩니다.

자 이제 실습을 위해 위의 3개의 매개변수를 init.ora 에 설정토록 합니다.

MAX_DUMP_SIZE = 800
TIMED_STATISTICS = TRUE
USER_DUMP_DEST = C:\oracle\admin\wink\udump

다음을 따라 하도록 합니다.

SQL> conn / as sysdba
연결되었습니다.
SQL> shutdown immediate
데이터베이스가 닫혔습니다.
데이터베이스가 마운트 해제되었습니다.
ORACLE 인스턴스가 종료되었습니다.
SQL> startup open
ORACLE 인스턴스가 시작되었습니다.

Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
데이터베이스가 마운트되었습니다.
데이터베이스가 열렸습니다.

SQL>conn scott/tiger

SQL> alter session set sql_trace=true;

세션이 변경되었습니다.

SQL> select job,avg(sal) from emp
  2  group by job
  3  having avg(sal) > (select avg(sal) from emp
  4  where job = 'SALESMAN');

JOB         AVG(SAL)
--------- ----------
ANALYST         3000
MANAGER   2758.33333
PRESIDENT       5000

session에서 trace를 중지

SQL> alter session set sql_trace=false;

SQL*Plus를 종료하고 c:\oracle\admin\wink\udump에 가보면 trc 파일이 생겼을 것
입니다. 저의 경우 DB SID가 wink이므로 wink_ora_3316.trc 와 같은 파일이 생겼습니다.

TKPROF를 이용하여 TRACE파일을 텍스트 파일로 변경 하기

TKPROF Utility를 이용하면 매우 유용한 분석 정보를 얻을 수 있습니다. 즉 TKPROF의 결과 파일은 트레이스가 실행되는 동안 프로세스에 의해 데이터베이스에서 실행된 작업에 대한 요약 정보 입니다.

텍스트 파일의 내용을 보면 PARSE, EXECUTION, FETCH시 작업을 실행 한 횟수, CPU 사용 시간, 검색된 행이 무엇인지, SQL이 수행된 총 소요시간, DISK IO 블록 수, 조건을 만족하는 전체 행의 수, 수행된 SQL문이 사용한 SGA 영역의 크기, SQL문장의 실행 계획, 해당 세션에서 작업했던 전체 작업에 대한 CPU, 메모리, 블록의 크기 등의 정보를 확인 할 수 있습니다.

SQL문을 해석하기 위해서는 아래의 단계가 필요 합니다.

파싱(parse)

SQL문을 실행 계획으로 번역 하는 것을 말합니다. 해당 SQL을 실행 하는데 필요한 적절한 권한, 컬럼이 있는지, 참조된 객체에 관한 확인 등의 작업이 이루어지게 됩니다.

실행(execution)

오라클에 의해 SQL문을 실제 실행 한 것을 말합니다.

추출/인출(fetch)

쿼리에 의해 추출된 레코드를 이여기 합니다.  Select 문에서만 이용 됩니다.


다음은 TKPROF의 통계 정보 컬럼 입니다.

Count : 분석, 실행, 추출을 몇번 했는지를 나타 냅니다.
CPU : 분석, 실행, 추출에 대한 CPU 처리 시간(CURSOR를 공유하면 분석단계의 처리 시간은 0 입니다.)
Elapsed : 분석, 실행, 추출 처리 단계별로 처리된 소요 시간
Disk : 테이블의 데이터를 읽기 위해 데이터 파일로부터 읽어 들인 블록 수
Query : SELECT로 데이터를 읽어 올 때 이미 다른 사용자에 의해 같은 데이터가 사용 되었다면 그 블록에서 데이터를 가져옵니다.
Current : 메모리에 저장된 데이터를 가지고 오기 위해 읽은 버퍼의 블록 수(update, insert, delete 후 select 했을 때)

TKPROF를 실행하기 위한 문법

Explain = 사용자계정/패스워드(명시된 사용자에 대해 EXPLAIN PLAN 실행)
Print = n (트레이스 파일내의 분석된 SQL문의 수를 n 만큼만 제한할 때 이용)
Record = 파일명(트레이스 파일내에 분석된 SQL문을 지정한 파일에 저장)
Sort=option(트레이스 파일내에 분석된 SQL문을 지정한 옵션에 의해 정렬)
Sys=[NO](트레이스 파일내에 생성된 SQL 문장 중에 오라클 서버가 내부적인 작업을 위해 실행한 SQL문장을 출력 시 포함 할건지를 결정)
Table=스키마.테이블명(실행 계획을 지정한 테이블에 저장)

이전의 SQL*TRACE에 의해 생긴 바이너리 파일을 TKPROF를 이용하여 분석을 해보도록 하겠습니다.

명령프롬픝에서 다음과 같이 실행 합니다.(TRACE 파일이 만들어진 곳에서 실행)

C:\oracle\admin\wink\udump>tkprof wink_ora_3316.trc sql1.tkp sys=no explain=scot
t/tiger

TKPROF: Release 9.2.0.1.0 - Production on 목 Dec 16 01:33:23 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

다음은 sql1.tkp 파일의 내용 입니다.



TKPROF: Release 9.2.0.1.0 - Production on 목 Dec 16 01:33:23 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Trace file: wink_ora_3316.trc
Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

alter session set sql_trace=true


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 59  (SCOTT)
********************************************************************************


아래는 사용자가 실행한 SQL 문장 입니다.

select job,avg(sal) from emp
group by job
having avg(sal) > (select avg(sal) from emp
where job = 'SALESMAN')

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          6          0           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.01          0          6          0           3

Misses in library cache during parse: 1 이 값이 0이라는 의미는 실행한 SQL문이 이전에 실행 된적이 없었음을 나타 냅니다.
Optimizer goal: CHOOSE 옵티마이저 모드 입니다.
Parsing user id: 59  (SCOTT)

Rows     Row Source Operation
-------  ---------------------------------------------------
      3  FILTER  
      5   SORT GROUP BY
     14    TABLE ACCESS FULL EMP
      1   SORT AGGREGATE
      4    TABLE ACCESS FULL EMP


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      3   FILTER
      5    SORT (GROUP BY)
     14     TABLE ACCESS   GOAL: ANALYZED (FULL) OF 'EMP'
      1    SORT (AGGREGATE)
      4     TABLE ACCESS   GOAL: ANALYZED (FULL) OF 'EMP'

********************************************************************************

alter session set sql_trace=false


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.01       0.00          0          0          0           0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 59  (SCOTT)



********************************************************************************
아래의 TOTAL은 전체 작업 결과에 대한 분석 결과 입니다.

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      3      0.01       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          6          0           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        7      0.01       0.01          0          6          0           3

Misses in library cache during parse: 1


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.01       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.01       0.00          0          3          0           1

Misses in library cache during parse: 0

    3  user  SQL statements in session.
    1  internal SQL statements in session.
    4  SQL statements in session.
    1  statement EXPLAINed in this session.
********************************************************************************
Trace file: wink_ora_3316.trc
Trace file compatibility: 9.00.01
Sort options: default

       1  session in tracefile.
       3  user  SQL statements in trace file.
       1  internal SQL statements in trace file.
       4  SQL statements in trace file.
       4  unique SQL statements in trace file.
       1  SQL statements EXPLAINed using schema:
           SCOTT.prof$plan_table
             Default table was used.
             Table was created.
             Table was dropped.
      54  lines in trace file.

원문 : http://blog.naver.com/novaculite?Redirect=Log&logNo=120012048251

V$SQLAREA

V$SQLAREA는 SQL튜닝 등에 유용하게 이용 될 수 있는 데이터딕셔너리의 성능 뷰 입니다.  V$SQL, V$SQLTEXT와 같은 뷰들도 비슷한 정보를 제공 합니다. V#SQLAREA는 SGA내 SHARE POOL에 존재하는 SQL문에 대한 PARSE 정보 즉 SQL TEXT, PARSING횟수, REPARSING 횟수와 EXECUTE에 관한 정보 EXECUTE 횟수, RE-LOADING 횟수, SQL문이 재사용되지 못한 횟수, SORTING 횟수, SQL문중 메모리를 많이 점유하는 SQL 확인, 많은 DISK IO를 일으키는 SQL문을 검사 할 때 유용하게 사용 됩니다.

실습을 위한 Table인 myemp를 생성 합니다.

SQL>conn scott/tiger
SQL> create table myemp (
  2      empno number not null primary key,
  3      ename varchar2(20) not null,
  4      sal number(7,2),
  5      job varchar2(20),
  6      mgr number);

테이블이 생성되었습니다.

Table을 생성 후 SQL*Loader를 이용하여 데이터를 6만5000건 정도 입력 하였습니다.
SQL*Loader에 관한 사항은 Oracle Tip에서 확인 바라구요, 방법에 대해 간단히 설면 드리면 Excel에서 데이터를 몇 개 만들어 복사를 해서 65000 건 만든 후 저장 할ㄸ CSV 파일로 저장(emp.csv)을 하여 공백으로 구분토록 하였습니다. 아래의 emp.ctl 파일과 emp.csv 파일을 같은 폴더에 둔 후 SQL*Loader를 실행 하기 바랍니다.

다음은 콘트롤 파일 입니다.

Emp.ctl
LOAD DATA
INFILE 'emp.csv'      
APPEND                  
INTO TABLE myemp
FIELDS TERMINATED BY ','
(empno, ename, sal, job, mgr)

SQL*Loader를 실행 합니다.

D:\sqlloader>sqlldr userid=scott/tiger control='emp.ctl' log=emp.log

Scott/tiger로 접속을 하여 다음과 같은 질의를 실행 해 봅니다.

SQL>select ename, sal from myemp order by job, ename        
SQL>select job, sum(sal) from myemp              
SQL>select job, sum(sal) from myemp group by job        

다음 관리자 계정으로 접속을 하여 V$SQLAREA를 확인해 보겠습니다.

SQL> conn / as sysdba
연결되었습니다.
SQL> select sql_text,
  2         version_count,
  3         loads,
  4         invalidations,
  5         parse_calls,
  6         sorts
  7  from v$sqlarea
  8  where instr(sql_text,'myemp') > 1
  9  and   command_type in (2,3,6,7)
10  order by sql_text
11  /


대충 아래와 같은 결과가 나타납니다.

sql_text                                       version_count  loads  invalidations  parse_calls sorts
----------------------------------------------------------------------------------------------------
select ename, sal from myemp order by job, ename        1        1        0               1         1
select job, sum(sal) from myemp                              1        1        0               0         0
select job, sum(sal) from myemp group by job                1        1        1               0         0


만약 SHARED_POOL을 CLEAR할려면 다음과 같이 하세요~

SQL> conn / as sysdba
연결되었습니다.
SQL> alter system flush shared_pool;

물론 CLEAR 한후 V$SQLAREA를 이용하여 조회 한다면 비어 있겠죠^^;



command_type의 2는 insert문, 3은 select문, 6은 update문, 7은 delete문을 나타내며 컬럼들의 의미는 다음과 같습니다.

Version_count : 사용자 계정은 다르지만 테이블 이름이 같은 경우에 이를 식별하기 위해 사용하는 컬럼 입니다. 만약 서로 다른 계정에서 각각 만든 이름이 같은 테이블을 실행시 SQL_TEXT는 하나만 존재 하지만 version_count는 다르게 나타납니다. 만약 scott계정의 myemp 테이블에서 select * from myemp를 실행 후 sys계정에서 myemp를 똑같이 만들어 실행 한다면  나중에 실행되는 select * from myemp만 X$SQLAREA로 조회되며 version_count는 2가 되는 것입니다.

Load : parsing시 SQL문이 Library Cache 영역에서 발견되지 않으면 parsing후 parse 정보를 library cache로 로딩하게 됩니다. Loads가 1이라면 처음으로 로드 된 것을 의미 합니다. 즉 Library Cache에 로드된 횟수를 나타내므로 1보다 큰 경우엔 좋지 못합니다.

Invalidation : library cache에 이미 SQL문장이 한번 이상 수행되었다고 할 때 그 SQL문장은 로드되어 있을 겁니다. 그때 사용자가 alter table, drop, analyze등을 myemp에 대해 수행 했다고 한다면 현재 library cache에 있는 parsing 정보에 대해 더 이상 무결성을 보장 할 수 없으므로 SQL문이 실행 된다면 다시 parse 과정을 거치게 됩니다. 그러한 경우에 invalidations 컬럼이 set 됩니다.

parse_calls : 실행한 SQL 문장이 처음 사용된 문장이라면 LOADS 컬럼에 1이 할당 됩니다. 이후 다른 사용자(또는 동일한 세션)에 의해 SQL문장이 재사용 되어 진다면 PAESING은 발생하지 않고 이미 PARSING된 정보를 재 사용 합니다. 이때 parse_calss 컴럼의 값이 증가 합니다.

SQL의 파싱된 결과값을 분석하므로써 SQL이 적절하게 BIND변수를 쓰고 있나 분석하기 위한 v$SQLAREA 의 주요 값들

Column	Datatype	Description
SQL_TEXT	VARCHAR2(1000)	First thousand characters of the SQL text for the current cursor
SHARABLE_MEM	NUMBER	Amount of shared memory used by a cursor. If multiple child cursors exist, then the sum of all shared memory used by all child cursors.
PERSISTENT_MEM	NUMBER	Fixed amount of memory used for the lifetime of an open cursor. If multiple child cursors exist, the fixed sum of memory used for the lifetime of all the child cursors.
RUNTIME_MEM	NUMBER	Fixed amount of memory required during execution of a cursor. If multiple child cursors exist, the fixed sum of all memory required during execution of all the child cursors.
SORTS	NUMBER	Sum of the number of sorts that were done for all the child cursors
VERSION_COUNT	NUMBER	Number of child cursors that are present in the cache under this parent
LOADED_VERSIONS	NUMBER	Number of child cursors that are present in the cache and have their context heap (KGL heap 6) loaded
OPEN_VERSIONS	NUMBER	The number of child cursors that are currently open under this current parent
USERS_OPENING	NUMBER	The number of users that have any of the child cursors open
FETCHES	NUMBER	Number of fetches associated with the SQL statement
EXECUTIONS	NUMBER	Total number of executions, totalled over all the child cursors
USERS_EXECUTING	NUMBER	Total number of users executing the statement over all child cursors
LOADS	NUMBER	The number of times the object was loaded or reloaded
FIRST_LOAD_TIME	VARCHAR2(19)	Timestamp of the parent creation time
INVALIDATIONS	NUMBER	Total number of invalidations over all the child cursors
PARSE_CALLS	NUMBER	The sum of all parse calls to all the child cursors under this parent
DISK_READS	NUMBER	The sum of the number of disk reads over all child cursors
BUFFER_GETS	NUMBER	The sum of buffer gets over all child cursors
ROWS_PROCESSED	NUMBER	The total number of rows processed on behalf of this SQL statement
COMMAND_TYPE	NUMBER	The Oracle command type definition
OPTIMIZER_MODE	VARCHAR2(10)	Mode under which the SQL statement was executed
PARSING_USER_ID	NUMBER	The user ID of the user that has parsed the very first cursor under this parent
PARSING_SCHEMA_ID	NUMBER	The schema ID that was used to parse this child cursor
KEPT_VERSIONS	NUMBER	The number of child cursors that have been marked to be kept using the DBMS_SHARED_POOL package
ADDRESS	RAW(4 | 8)	Address of the handle to the parent for this cursor
HASH_VALUE	NUMBER	Hash value of the parent statement in the library cache
MODULE	VARCHAR2(64)	Contains the name of the module that was executing at the time that the SQL statement was first parsed as set by calling DBMS_APPLICATION_INFO.SET_MODULE
MODULE_HASH	NUMBER	Hash value of the module that is named in the MODULE column
ACTION	VARCHAR2(64)	Contains the name of the action that was executing at the time that the SQL statement was first parsed as set by calling DBMS_APPLICATION_INFO.SET_ACTION
ACTION_HASH	NUMBER	Hash value of the action that is named in the ACTION column
SERIALIZABLE_ABORTS	NUMBER	Number of times the transaction fails to serialize, producing ORA-08177 errors, totalled over all the child cursors
IS_OBSOLETE	VARCHAR2(1)	Indicates whether the cursor has become obsolete (Y) or not (N). This can happen if the number of child cursors is too large.
CHILD_LATCH	NUMBER	Child latch number that is protecting the cursor

원문 : http://www.oracle.com/technology/global/kr/pub/articles/10gdba/week1_10gdba.html

첫번째.
사진이 아닌 동영상으로: Flashback 버전 질의

전혀 아무런 설정 없이도 행의 모든 변경 내용을 즉시 식별 가능

Oracle9i Database에는 Flashback 질의 형태의 소위 “타임 머신” 기능이 이미 도입된 바 있습니다. DBA는 이 기능을 통해 실행 취소 세그먼트에 블록의 이전 이미지 복사본이 있으면 열의 값을 특정 시간으로 확인할 수 있습니다. 하지만 Flashback 질의는 두 시점 간의 변경된 데이타를 표시하는 대신 데이타의 고정된 스냅샷을 시간으로 나타내는 데 그칩니다. 외환 관리 등과 관련된 일부 애플리케이션에서는 값 데이타의 변경 내용을 두 시점에서가 아닌 일정 기간으로 확인해야 합니다. Oracle Database 10g에는 Flashback 버전 질의 기능이 있어 이러한 작업을 쉽고 편리하게 수행할 수 있습니다.

테이블 변경 내용 질의

이 예에서는 은행의 외환 관리 애플리케이션을 사용했습니다. 데이타베이스에는 특정 시간의 환율을 기록하는 RATES라는 테이블이 있습니다.

SQL> desc rates
 Name              Null?    Type
 ----------------- -------- ------------
 CURRENCY                   VARCHAR2(4)
 RATE                       NUMBER(15,10)

이 테이블에는 CURRENCY 열에 표시된 기타 여러 통화에 대한 US 달러의 환율이 표시됩니다. 재무 서비스 업계에서는 환율을 단순히 변경될 때마다 업데이트하는 대신 지속적인 기록으로 관리합니다. 이 접근방법은 은행 거래가 “이미 지난 시간”에 이루어져 송금으로 인한 시간상의 손실을 수용할 수 있기 때문에 채택되는 것입니다. 예를 들어, 오전 10:12에 이뤄지지만 오전 9:12에 발효된 거래는 현재 시간이 아닌 오전 9:12의 환율이 적용됩니다.

지금까지는 환율 기록 테이블을 생성해 환율 변경 내용을 저장한 후 해당 테이블에 기록이 있는지 질의할 수 밖에 없었습니다. 이 밖에도 RATES 테이블 자체에 특정 환율이 적용되는 시작 시간과 종료 시간을 기록하는 방법이 있었습니다. 변경 내용이 발생하면 기존 행의 END_TIME 열이 SYSDATE로 업데이트되며 END_TIME이 NULL인 새 환율과 함께 행이 새로 삽입되는 것입니다.

하지만 Oracle Database 10g에는 Flashback 버전 질의 기능이 있어 기록 테이블을 유지하거나 시작 및 종료 시간을 저장할 필요가 없습니다. 대신 이 기능을 사용하면 추가로 설정하지 않고도 과거의 특정 시간에 해당하는 행의 값을 가져올 수 있습니다.

예를 들어, 일상 업무를 수행하는 DBA가 환율을 여러 번 업데이트하거나, 때로는 행을 삭제하고 다시 삽입하기도 한다고 가정합니다.

insert into rates values ('EURO',1.1012);
commit;
update rates set rate = 1.1014;
commit;
update rates set rate = 1.1013;
commit;
delete rates;
commit;
insert into rates values ('EURO',1.1016);
commit;
update rates set rate = 1.1011;
commit;

이러한 일련의 작업이 끝나면 DBA는 다음과 같은 RATE 열의 현재 커밋된 값을 얻게 됩니다.

SQL> select * from rates;

CURR       RATE
---- ----------
EURO     1.1011

이 결과에는 행을 처음 생성했을 때부터 발생한 모든 변경 내용이 아닌 RATE의 현재 값이 표시됩니다. 따라서 Flashback 질의를 사용하면 해당 시점의 값을 검색할 수 있지만, 여기서 보다 핵심적인 의도는 단순히 특정 시점에 얻은 일련의 스냅샷이 아니라 캠코더를 통해 변경 내용을 기록하는 것 같이 변경 내용의 감사추적(Audit Trail)을 구축하려는 것입니다.

다음 질의에는 테이블의 변경 내용이 나와 있습니다.

select versions_starttime, versions_endtime, versions_xid, 
versions_operation, rate 
from rates versions between timestamp minvalue and maxvalue
order by VERSIONS_STARTTIME
/

VERSIONS_STARTTIME     VERSIONS_ENDTIME       VERSIONS_XID     V       RATE
---------------------- ---------------------- ---------------- - ----------
01-DEC-03 03.57.12 PM  01-DEC-03 03.57.30 PM  0002002800000C61 I     1.1012
01-DEC-03 03.57.30 PM  01-DEC-03 03.57.39 PM  000A000A00000029 U     1.1014
01-DEC-03 03.57.39 PM  01-DEC-03 03.57.55 PM  000A000B00000029 U     1.1013
01-DEC-03 03.57.55 PM                         000A000C00000029 D     1.1013
01-DEC-03 03.58.07 PM  01-DEC-03 03.58.17 PM  000A000D00000029 I     1.1016
01-DEC-03 03.58.17 PM                         000A000E00000029 U     1.1011

행을 삭제하고 다시 삽입했더라도 여기에 행의 모든 변경 내용이 표시됩니다. VERSION_OPERATION 열에는 행에서 수행한 작업(삽입/업데이트/삭제)이 나타나며, 이를 위한 모든 과정이 기록 테이블이나 추가 열 없이 이뤄집니다.

위의 질의에서 versions_starttime, versions_endtime, versions_xid, versions_operation 열은 ROWNUM, LEVEL 같이 친숙한 열과 유사한 의사(Pseduo) 열입니다. VERSIONS_STARTSCN 및 VERSIONS_ENDSCN 같은 다른 의사 열에는 해당 시간의 시스템 변경 번호(SCN)가 표시됩니다. 또한 versions_xid 열에는 행을 변경한 트랜잭션 식별자가 표시됩니다. 트랜잭션의 상세 내역은 FLASHBACK_TRANSACTION_QUERY 뷰에 나와 있으며, 여기서 XID 열에는 트랜잭션 ID가 표시됩니다. 예를 들어, 위에서 VERSIONS_XID 값인 000A000D00000029를 사용하면 UNDO_SQL 값에 실제 문이 표시됩니다.

SELECT UNDO_SQL
FROM FLASHBACK_TRANSACTION_QUERY
WHERE XID = '000A000D00000029';

UNDO_SQL
----------------------------------------------------------------------------
insert into "ANANDA"."RATES"("CURRENCY","RATE") values ('EURO','1.1013');

이 뷰에는 실제 문 외에도 커밋의 타임 스탬프와 SCN을 비롯해 질의 시작 시의 SCN과 타임 스탬프가 표시됩니다.

기간 내 변경 내용 확인

이제 이러한 정보를 효과적으로 사용하는 방법에 대해 알아보도록 하겠습니다. 오후 3:57:54의 RATE 열 값을 확인해야 한다고 가정하면 다음과 같이 실행할 수 있습니다.

select rate, versions_starttime, versions_endtime
from rates versions
between timestamp 
to_date('12/1/2003 15:57:54','mm/dd/yyyy hh24:mi:ss')
and to_date('12/1/2003 16:57:55','mm/dd/yyyy hh24:mi:ss')
/

      RATE VERSIONS_STARTTIME     VERSIONS_ENDTIME
---------- ---------------------- ----------------------
    1.1011

이 질의는 Flashback 질의와 유사합니다. 위의 예에서 시작 및 종료 시간은 NULL로 해당 시간 간격 동안 환율이 변하지 않았으며 시간 간격을 포함하고 있음을 나타냅니다. 또한 SCN을 사용하면 이전 버전 값을 확인할 수 있으며, SCN 번호는 의사 열인 VERSIONS_STARTSCN 및 VERSIONS_ENDSCN에서 가져옵니다. 다음은 이에 대한 예입니다.

select rate, versions_starttime, versions_endtime
from rates versions
between scn 1000 and 1001
/ 

키워드 MINVALUE 및 MAXVALUE를 사용하면 실행 취소 세그먼트의 모든 변경 내용이 표시됩니다. 특정 날짜 또는 SCN 값을 범위의 끝점 중 하나로 지정하고 다른 끝점을 리터럴 MAXVALUE 또는 MINVALUE로 지정할 수도 있습니다. 예를 들어, 다음은 전체 범위가 아닌 오후 3:57:52부터의 변경 내용을 알려주는 질의입니다.

select versions_starttime, versions_endtime, versions_xid, 
versions_operation, rate 
from rates versions between timestamp 
to_date('12/11/2003 15:57:52', 'mm/dd/yyyy hh24:mi:ss')
and maxvalue
order by VERSIONS_STARTTIME
/

VERSIONS_STARTTIME     VERSIONS_ENDTIME       VERSIONS_XID     V       RATE
---------------------- ---------------------- ---------------- - ----------
01-DEC-03 03.57.55 PM                         000A000C00000029 D     1.1013
01-DEC-03 03.58.07 PM  01-DEC-03 03.58.17 PM  000A000D00000029 I     1.1016
01-DEC-03 03.58.17 PM                         000A000E00000029 U     1.1011

최종 분석

Flashback 버전 질의는 틀을 벗어나 테이블 변경 내용의 짧은 휘발성 값(value) 감사(Audit)를 복제합니다. 이러한 이점을 통해 DBA는 과거의 특정 값이 아니라 일정 기간의 모든 변경 내용을 가져오므로 실행 취소 세그먼트(Undo Segment)의 데이타를 최대한 활용할 수 있습니다. 따라서 최대한으로 사용할 수 있는 버전은 UNDO_RETENTION 매개변수에 달려있다고 하겠습니다.

Flashback 버전 질의에 대한 자세한 내용은 Oracle Database Concepts 10g Release 1 (10.1) 설명서의 관련 섹션을 참조하십시오.