Oracle Database 10g: DBA를 위한 20가지 주요 기능 - 11번째
Wait 인터페이스
10g의 wait 인터페이스는 ADDM에 의해 아직 캡처 되지 않은 현 시점의 성능 문제를 진단하고 해결하기 위한 유용한 정보를 제공합니다.
"데이타베이스가 너무 느리네요!"
성능에 불만을 가진 사용자들은 이렇게 말하곤 합니다. 여러분들도 필자와 같다면, DBA로 근무하면서 이런 이야기를 수없이 들어왔을 것입니다.
그렇다면 성능 문제를 해결하기 위해 어떻게 대처하십니까? 사용자의 불만을 무시하는 방법도 있겠지만(대부분의 DBA에게 이런 사치는 허용되지 않으므로), 데이타베이스 내부 또는 외부에 세션 wait 이벤트가 발생했는지 확인하는 것이 첫 번째로 하는 일이 될 것입니다.
오라클은 이를 위해 단순하면서도 강력한 메커니즘을 제공합니다. V$SESSION_WAIT 뷰가 바로 그것입니다. V$SESSION_WAIT 뷰를 통해 세션이 대기 중인 이벤트가 무엇이고 얼마나 자주, 얼마나 오랫동안 대기했는지 등의 정보를 확인할 수 있습니다. 예를 들어 특정 세션이 “db file sequential read” 이벤트를 대기하고 있는 경우, 컬럼 P1과 P2는 세션이 대기 중인 블록의 file_id와 block_id를 각각 표시하게 됩니다. 대부분의 경우, V$SESSION_WAIT 뷰만으로 wait 이벤트에 관련한 충분한 정보를 확인할 수 있습니다. 하지만 다음과 같은 두 가지 약점이 있습니다:
For most wait events this view is sufficient, but it is hardly a robust tuning tool for at least two important reasons:
- V$SESSION_WAIT 뷰는 현 시점의 상태만을 표시합니다. wait 이벤트가 종료되면, 그 히스토리 정보 역시 소실되어 버립니다. 또 V$SESSION_EVENT 뷰는 세션이 시작된 이후의 누적된 정보를 제공하지만 그 정보가 상세하지 않습니다.
- V$SESSION_WAIT 뷰는 wait 이벤트에 대한 정보만을 포함하고 있습니다. 그 밖의 다른 정보(userid, terminal 등)를 확인하려면 V$SESSION 뷰와 조인해야 합니다.
Oracle Database 10g는 최소한의 작업으로 보다 많은 정보를 얻을 수 있도록 wait 인터페이스를 혁신적으로 개선하였습니다. 이번 연재에서는, wait 인터페이스와 관련하여 Oracle Database 10g가 제공하는 새로운 기능을 소개하고 성능 문제 해결을 위한 활용 방안을 설명합니다. 대부분의 경우 ADDM(Automatic Database Diagnostic Manager)이 성능 분석 툴로 활용되겠지만, ADDM에 의해 아직 수집되지 않은 현 시점의 성능 문제를 분석하는 데에는 wait 인터페이스가 유용합니다.
Session Wait 뷰의 기능 향상
먼저 V$SESSION_WAIT의 개선된 기능에 대해 예를 통해 설명하도록 하겠습니다.
사용자의 세션이 갑자기 느려졌다는 불만이 제기된 경우를 가정해 봅시다. 먼저 사용자 세션의 SID를 확인하고, 해당 SID에 대해 V$SESSION_WAIT 뷰를 조회합니다. 그 결과가 아래와 같습니다:
SID : 269
SEQ# : 56
EVENT : enq: TX - row lock contention
P1TEXT : name|mode
P1 : 1415053318
P1RAW : 54580006
P2TEXT : usn<<16 | slot
P2 : 327681
P2RAW : 00050001
P3TEXT : sequence
P3 : 43
P3RAW : 0000002B
WAIT_CLASS_ID : 4217450380
WAIT_CLASS# : 1
WAIT_CLASS : Application
WAIT_TIME : -2
SECONDS_IN_WAIT : 0
STATE : WAITED UNKNOWN TIME
굵은 글씨체로 표시된 컬럼을 주목하시기 바랍니다. WAIT_CLASS_ID, WAIT_CLASS#, WAIT_CLASS 등은 모두 10g에서 새로 추가된 컬럼들입니다. WAIT_CLASS는 wait의 유형을 구분해 주는 컬럼으로, idle한 wait이므로 무시해도 되는지 또는 유효한 wait인지를 판단하는 중요한 기준으로 활용됩니다. 위의 예에서는 wait class가 “Application”으로 표시되고 있으며, 따라서 주목할 만한 이유가 충분한 것으로 판단할 수 있습니다.
이러한 컬럼들은 튜닝 과정에서 매우 요긴하게 활용됩니다. 예를 들어 다음과 같은 쿼리를 작성하여 세션 wait에 관련한 정보를 얻을 수 있습니다.
select wait_class, event, sid, state, wait_time, seconds_in_wait
from v$session_wait
order by wait_class, event, sid
/
쿼리 실행 결과의 예가 다음과 같습니다:
WAIT_CLASS EVENT SID STATE WAIT_TIME SECONDS_IN_WAIT
---------- -------------------- ---------- ------------------- ---------- ---------------
Application enq: TX - 269 WAITING 0 73
row lock contention
Idle Queue Monitor Wait 270 WAITING 0 40
Idle SQL*Net message from client 265 WAITING 0 73
Idle jobq slave wait 259 WAITING 0 8485
Idle pmon timer 280 WAITING 0 73
Idle rdbms ipc message 267 WAITING 0 184770
Idle wakeup time manager 268 WAITING 0 40
Network SQL*Net message to client 272 WAITED SHORT TIME -1 0
여기 몇 가지 이벤트(Queue Monitor Wait, JobQueue Slave등)가 “Idle” 이벤트로 표시되고 있음을 확인할 수 있습니다. 이러한 항목들은 nonblocking wait로 간주하고 고려 대상에서 제외할 수도 있습니다. 하지만 “idle” 이벤트가 다른 문제를 암시하는 경우도 있습니다. 예를 들어, “idle”로 표시되는 SQL*Net 관련 이벤트는 네트워크 지연율이 높음을 나타낼 수도 있습니다.
WAIT_TIME이 -2의 값을 갖는 경우도 참고할 필요가 있습니다. Windows와 같은 일부 플랫폼은 fast timing mechanism을 지원하지 않습니다. 해당 플랫폼에 초기화 매개변수 TIMED_STATISTICS 가 설정되어 있지 않은 경우, 정확한 시간 통계를 얻을 수 없습니다. 이 경우 Oracle9i에서는 관련된 컬럼에 비정상적으로 높은 숫자가 표시되며, 이로 인해 성능 문제의 진단이 더욱 어려워질 수 있습니다. 10g에서는 이러한 경우 일률적으로 -2의 값을 표시합니다. 즉, -2는 플랫폼이 fast timing mechanism을 지원하지 않으며 TIMED_STATISTICS 매개변수가 설정되어 있지 않음을 의미합니다. (이 문서의 뒷부분에서는 fast timing mechanism이 사용 가능한 것으로 가정하고 설명을 진행합니다.)
Session 뷰와 Session Wait 뷰의 통합
세션에 대한 자세한 정보를 얻기 위해서 V$SESSION_WAIT를 V$SESSION과 조인해야만 했던 사실을 기억하고 계실 겁니다. 10g에서는 V$SESSION 뷰 안에 V$SESSION_WAIT의 정보가 기본적으로 포함됩니다. V$SESSION이 wait 이벤트에 관련하여 추가로 제공하는 컬럼이 아래와 같습니다:
EVENT# NUMBER
EVENT VARCHAR2(64)
P1TEXT VARCHAR2(64)
P1 NUMBER
P1RAW RAW(4)
P2TEXT VARCHAR2(64)
P2 NUMBER
P2RAW RAW(4)
P3TEXT VARCHAR2(64)
P3 NUMBER
P3RAW RAW(4)
WAIT_CLASS_ID NUMBER
WAIT_CLASS# NUMBER
WAIT_CLASS VARCHAR2(64)
WAIT_TIME NUMBER
SECONDS_IN_WAIT NUMBER
STATE VARCHAR2(19)
이 컬럼들은 V$SESSION_WAIT에서 제공되는 것들과 동일한 이름을 가지며 동일한 정보를 제공합니다. 결국 이벤트에 대한 세션 wait 정보를 확인하기 위해 V$SESSION_WAIT를 따로 조회할 필요가 없게 된 것입니다.
그럼 다시 처음에 설명한 예로 돌아가 봅시다. SID 269의 세션이 “enq: TX - row lock contention”이벤트를 대기 중이며, 이는 이 세션이 다른 세션이 점유하고 있는 락(lock)을 대기 중임을 의미합니다. 문제를 진단하기 위해서는 “다른” 세션이 무엇인지 확인해야 합니다. 그렇다면 어떤 방법을 써야 할까요?
Oracle9i와 이전 버전에서는 매우 복잡한 쿼리를 실행해야만 락을 소유한 세션의 SID를 알아낼 수 있었습니다. 10g에서는 다음과 같은 간단한 쿼리로 해결 가능합니다:
select BLOCKING_SESSION_STATUS, BLOCKING_SESSION
from v$session
where sid = 269
BLOCKING_SE BLOCKING_SESSION
----------- ----------------
VALID 265
이렇게 간단한 작업만으로 SID 265 세션이 세션 269를 블로킹하고 있음이 확인되었습니다.
얼마나 많은 Wait가 발생했는가?
아직도 사용자를 만족시키기에는 이릅니다. 사용자의 세션이 느려진 이유는 도대체 무엇일까요? 다음과 같은 쿼리를 먼저 실행해 봅시다:
select * from v$session_wait_class where sid = 269;
결과는 다음과 같이 표시됩니다:
SID SERIAL# WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS TOTAL_WAITS TIME_WAITED
---- ------- ------------- ----------- ------------- ----------- -----------
269 1106 4217450380 1 Application 873 261537
269 1106 3290255840 2 Configuration 4 4
269 1106 3386400367 5 Commit 1 0
269 1106 2723168908 6 Idle 15 148408
269 1106 2000153315 7 Network 15 0
269 1106 1740759767 8 User I/O 26 1
위 결과는 세션 wait에 대한 매우 상세한 정보를 제공하고 있습니다. 위 결과를 통해 애플리케이션에 관련한 세션 wait 이벤트가 873회, 총 261,537 centi-second에 걸쳐 발생되었으며, 네트워크 관련한 wait 이벤트가 15회 발생되었음을 확인할 수 있습니다.
이 방법을 응용하여, wait class에 관련한 시스템의 전반적인 통계를 확인할 수도 있습니다. (여기에서도 시간은 centi-second 단위로 표시됩니다.)
select * from v$system_wait_class;
WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS TOTAL_WAITS TIME_WAITED
------------- ----------- ------------- ----------- -----------
1893977003 0 Other 2483 18108
4217450380 1 Application 1352 386101
3290255840 2 Configuration 82 230
3875070507 4 Concurrency 80 395
3386400367 5 Commit 2625 1925
2723168908 6 Idle 645527 219397953
2000153315 7 Network 2125 2
1740759767 8 User I/O 5085 3006
4108307767 9 System I/O 127979 18623
대부분의 성능 문제는 독립적으로 존재하지 않으며, 문제의 패턴을 암시하는 단서로서 해석되는 것이 일반적입니다. 이러한 패턴을 확인하려면 wait class의 히스토리 뷰를 조회해야 합니다:
select * from v$waitclassmetric;
V$WAITCLASSMETRIC 뷰는 지난 1분 동안의 wait class 통계를 제공합니다.
select wait_class#, wait_class_id,
average_waiter_count "awc", dbtime_in_wait,
time_waited, wait_count
from v$waitclassmetric
/
WAIT_CLASS# WAIT_CLASS_ID AWC DBTIME_IN_WAIT TIME_WAITED WAIT_COUNT
----------- ------------- ---- -------------- ----------- ----------
0 1893977003 0 0 0 1
1 4217450380 2 90 1499 5
2 3290255840 0 0 4 3
3 4166625743 0 0 0 0
4 3875070507 0 0 0 1
5 3386400367 0 0 0 0
6 2723168908 59 0 351541 264
7 2000153315 0 0 0 25
8 1740759767 0 0 0 0
9 4108307767 0 0 8 100
10 2396326234 0 0 0 0
11 3871361733 0 0 0 0
WAIT_CLASS_ID 컬럼을 주목하시기 바랍니다. 지난 1분 동안 4217450380의 WAIT_CLASS_ID 값을 갖는 wait class를 2개의 세션이 총 5회, 1,499 centi-second에 걸쳐 대기하였음을 알 수 있습니다. 그렇다면 이 wait class는 무엇일까요? V$SYSTEM_WAIT_CLASS를 조회하면 이 wait class가 바로 "Application” class임을 확인할 수 있습니다.
DBTIME_IN_WAIT 컬럼도 매우 유용하게 활용됩니다. Automatic Workload Repository(AWR)를 주제로 한 제 6번째 연재에서, 10g가 한층 세분화된 시간 통계를 제공하며, 데이타베이스 내부에서 실제로 사용된 시간을 정확하게 확인할 수 있음을 설명한 바 있습니다. DBTIME_IN_WAIT가 바로 데이타베이스 내부에서 사용된 시간을 표시하는 컬럼입니다.
단서는 항상 남는다
해당 사용자의 세션이 종료되자 DBA는 안도의 한숨을 내쉽니다. 하지만 도대체 어떤 wait 때문에 세션에 성능 문제가 발생했는지에 대한 궁금증이 가시지 않습니다. V$SESSION_WAIT를 조회하면 쉽게 해답을 얻을 수 있겠지만, 이제 wait 이벤트가 종료된 상황이므로 이 뷰에는 더 이상 기록이 남아있지 않을 것입니다. 이런 경우 어떻게 하시겠습니까?
10g는 액티브 세션의 마지막 10개 이벤트에 관련한 session wait 히스토리 정보를 자동 저장하고 관리하며, 이 결과는 V$SESSION_WAIT_HISTORY 뷰를 통해 조회할 수 있습니다:
select event, wait_time, wait_count
from v$session_wait_history
where sid = 265
/
EVENT WAIT_TIME WAIT_COUNT
------------------------------ ---------- ----------
log file switch completion 2 1
log file switch completion 1 1
log file switch completion 0 1
SQL*Net message from client 49852 1
SQL*Net message to client 0 1
enq: TX - row lock contention 28 1
SQL*Net message from client 131 1
SQL*Net message to client 0 1
log file sync 2 1
log buffer space 1 1
세션이 inactive 상태가 되거나 연결이 끊어진 경우, 관련된 기록도 뷰에서 삭제됩니다. 하지만 히스토리 정보는 AWR 테이블에 별도로 저장됩니다. AWR의 V$ACTIVE_SESSION_HISTORY 뷰는 session wait에 관련한 정보를 제공합니다. (AWR에 관련한 자세한 정보는 본 시리즈의 제 6번째 연재를 참고하시기 바랍니다.)
결론
Oracle Database 10g의 향상된 wait 모델을 사용하여 한층 쉽게 성능 분석 작업을 진행할 수 있습니다. 세션 히스토리 정보를 이용하면 문제의 징후가 사라진 이후에도 문제 원인을 진단할 수 있습니다. 또 wait를 wait class로 구분함으로써, 각 wait 유형별 영향을 이해하고 효율적으로 대처할 수 있게 됩니다.
제공 : DB포탈사이트 DBguide.net
Oracle Database 10g: DBA를 위한 20가지 주요 기능 - 12번째
Materialized Views
10g에서는 강제적인 query rewrite, tuning advisor 등의 새로운 기능을 통해 materialized view의 관리 기능을 향상시켰습니다.
스냅샷(snapshot)이라 불리기도 하는 Materialized view(MV)는 오라클에서 오래 전부터 구현해 온 기능의 하나입니다. MV는 쿼리 결과를 별도 세그먼트에 저장하고, 쿼리가 재실행되는 경우 사용자에게 미리 저장된 결과를 전달함으로써 쿼리를 여러 차례 재실행하는 데 따르는 성능적인 부담을 줄여줍니다. MV는 데이타 웨어하우스 환경에서 특히 유용합니다. 또 “fast refresh” 메커니즘을 이용해 MV를 전체적으로 또는 부분적으로 refresh할 수 있습니다.
다음과 같은 materialized view를 구현한 경우를 가정해 봅시다:
create materialized view mv_hotel_resv
refresh fast
enable query rewrite
as
select distinct city, resv_id, cust_name
from hotels h, reservations r
where r.hotel_id = h.hotel_id';
어떻게 하면 이 MV가 완벽하게 동작하는데 필요한 오브젝트들이 모두 생성되었는지 확인할 수 있을까요? Oracle Database 10g 이전 버전에서는, DBMS_MVIEW 패키지의 EXPLAIN_MVIEW 프로시저와 EXPLAIN_REWRITE 프로시저를 이용해 이를 확인할 수 있었습니다. 이 프로시저들은 10g에서도 여전히 사용 가능합니다. 이 프로시저들을 이용하면 특정 MV에 “fast refreshability”, “query rewritability” 등의 기능 구현 여부를 정확하게 확인할 수 있었지만, 어떻게 하면 이러한 기능을 구현할 수 있는지에 대한 조언을 구할 수는 없었습니다. 이를 위해서는, 각각의 MV 구조를 비주얼한 방법으로 확인해야만 했으며, 실질적으로 이 방법은 효용성이 없었습니다.
10g에서는 새로 추가된 DBMS_ADVISOR 패키지에 포함된 TUNE_MVIEW 프로시저를 통해 이러한 작업을 간단하게 수행할 수 있습니다. 먼저 IN 매개변수에 MV 생성 스크립트의 텍스트를 저장한 후 패키지를 호출합니다. 프로시저는 자동 생성된 이름을 가진 Advisor Task를 생성하며, OUT 매개변수를 통해 사용자에게 생성된 Advisor의 이름을 반환합니다.
예를 들어 설명해 보겠습니다. 먼저 SQL*Plus에서 OUT 매개변수에 저장할 변수를 정의해야 합니다.
SQL> -- first define a variable to hold the OUT parameter
SQL> var adv_name varchar2(20)
SQL> begin
2 dbms_advisor.tune_mview
3 (
4 :adv_name,
5 'create materialized view mv_hotel_resv refresh fast enable query rewrite as
select distinct city, resv_id, cust_name from hotels h,
reservations r where r.hotel_id = h.hotel_id');
6* end;
다음에는 adv_name 변수를 조회하여 Advisor의 이름을 확인합니다.
SQL> print adv_name
ADV_NAME
-----------------------
TASK_117
다음으로, DBA_TUNE_MVIEW 뷰를 질의하여 Advisor가 제공하는 권고내역을 확인합니다. 이 명령을 실행하기 전에 SET LONG 999999 명령을 실행하는 것을 잊지 마시기 바랍니다. (DBA_TUNE_MVIEW 뷰의 STATEMENT 컬럼은 CLOB 데이타타입을 사용하므로, 문자 출력을 80개로 제한하는 디폴트 환경을 수정해야 합니다.)
select script_type, statement
from dba_tune_mview
where task_name = 'TASK_117'
order by script_type, action_id;
실행 결과는 아래와 같습니다:
SCRIPT_TYPE STATEMENT
-------------- ------------------------------------------------------------
IMPLEMENTATION CREATE MATERIALIZED VIEW LOG ON "ARUP"."HOTELS" WITH ROWID,
SEQUENCE ("HOTEL_ID","CITY") INCLUDING NEW VALUES
IMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE ON "ARUP"."HOTELS" ADD
ROWID, SEQUENCE ("HOTEL_ID","CITY") INCLUDING NEW VALUES
IMPLEMENTATION CREATE MATERIALIZED VIEW LOG ON "ARUP"."RESERVATIONS" WITH
ROWID, SEQUENCE ("RESV_ID","HOTEL_ID","CUST_NAME")
INCLUDING NEW VALUES
IMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE ON "ARUP"."RESERVATIONS"
ADD ROWID, SEQUENCE ("RESV_ID","HOTEL_ID","CUST_NAME")
INCLUDING NEW VALUES
IMPLEMENTATION CREATE MATERIALIZED VIEW ARUP.MV_HOTEL_RESV REFRESH FAST
WITH ROWID ENABLE QUERY REWRITE AS SELECT
ARUP.RESERVATIONS.CUST_NAME C1, ARUP.RESERVATIONS.RESV_ID
C2, ARUP.HOTELS.CITY C3, COUNT(*) M1 FROM ARUP.RESERVATIONS,
ARUP.HOTELS WHERE ARUP.HOTELS.HOTEL_ID =
ARUP.RESERVATIONS.HOTEL_ID GROUP BY
ARUP.RESERVATIONS.CUST_NAME, ARUP.RESERVATIONS.RESV_ID,
ARUP.HOTELS.CITY
UNDO DROP MATERIALIZED VIEW ARUP.MV_HOTEL_RESV
SCRIPT_TYPE 컬럼은 제공되는 조언의 유형을 의미합니다. 대부분의 내용이 실제 구현에 관련된 권고사항으로 구성되어 있으므로 SCRIPT_TYPE으로 “IMPLEMENTATION”이 설정된 것을 확인할 수 있습니다. 사용자가 승인하는 경우, 제시된 권고사항은 ACTION_ID 컬럼에 정의된 순서대로 실행됩니다.
Advisor가 제시한 권고사항을 자세히 살펴보면, 우리가 비주얼 분석 작업을 통해 생성하는 것과 유사한 내용으로 구성되어 있다는 사실을 알 수 있을 것입니다. 위에서 제시된 권고사항은 매우 논리적입니다. Fast refresh를 구현하려면 베이스 테이블MATERIALIZED VIEW LOG가 생성되어야 하며, 이 때 “including new values”와 같은 조건을 포함하여야 합니다. STATEMENT 컬럼은 이러한 권고사항을 구현하기 위해 실행되는 SQL 구문을 담고 있습니다.
구현 작업의 마지막 단계로서, Advisor는 MV 생성 과정에서 수정이 필요한 부분을 지적해 줍니다. 위의 예에서 무엇이 달라졌는지 확인해 보시기 바랍니다. count(*)가 MV에 추가되었습니다. MV가 “fast refresh”를 지원하는 것으로 설정했기 때문에 count(*)가 반드시 포함되어야 하며, Advisor는 이 부분이 생략된 것을 발견하고 권고사항에 포함시킨 것입니다.
프로시저 TUNE_MVIEW 는 EXPLAIN_MVIEW 와 EXPLAIN_REWRITE가 제공하는 것보다 수준 높은 조언을 제공하며, MV를 생성하는 보다 쉽고 효율적인 방법도 함께 제시합니다. 때로 Advisor는 질의를 보다 효율적으로 하기 위한 방법으로 하나 이상의 MV를 제안하기도 합니다.
어떤 분은 그게 과연 얼마나 유용할까라고 의문을 던질 수도 있을 것입니다. 숙련된 DBA라면 누구나 MV 생성 스크립트에서 빠진 부분을 찾아내어 직접 수정할 수 있기 때문입니다. 사실 Advisor가 하는 역할이 바로 이것입니다. Advisor는 마치 숙련된 DBA처럼 전문적인 권고를 제시합니다. 한 가지 분명하게 다른 점은, Advisor가 돈을 안받고 일할 뿐 아니라 휴가나 월급인상을 요구하지도 않는다는 사실입니다. 이러한 기능이 있음으로 해서, 선임 DBA가 하급 DBA에게 반복적인 업무를 인계하고 보다 전략적인 목표에 집중할 수 있게 되는 것입니다.
TUNE_MVIEW 프로시저를 실행할 때 Advisor name을 미리 지정해서 매개변수를 통해 전달할 수도 있습니다. 이렇게 하는 경우 Advisor는 자동 생성된 이름 대신 사용자가 지정한 이름을 사용합니다.
쉬워진 구현 작업
이제 권고사항을 확인하고 바로 구현 작업에 들어갈 준비가 되었습니다. 위 실행결과의 STATEMENT 컬럼을 조회한 결과를 별도 스크립트 파일에 스풀링한 다음, 그 파일을 실행하는 것도 한 방법입니다. 아니면 그보다 쉬운 방법으로 별도 제공되는 프로시저를 실행할 수도 있습니다:
begin
dbms_advisor.create_file (
dbms_advisor.get_task_script ('TASK_117'),
'MVTUNE_OUTDIR',
'mvtune_script.sql'
);
end;
/
이 프로시저는 아래와 같은 방법으로 디렉토리 오브젝트가 미리 생성되어 있음을 가정합니다:
create directory mvtune_outdir as '/home/oracle/mvtune_outdir';
dbms_advisor를 호출하면 /home/oracle/mvtune_outdir 디렉토리에 mvtune_script.sql이라는 이름의 파일이 생성됩니다. 이 파일을 열어 보면 다음과 같은 내용이 포함되어 있음을 확인할 수 있을 것입니다:
Rem SQL Access Advisor: Version 10.1.0.1 - Production
Rem
Rem Username: ARUP
Rem Task: TASK_117
Rem Execution date:
Rem
set feedback 1
set linesize 80
set trimspool on
set tab off
set pagesize 60
whenever sqlerror CONTINUE
CREATE MATERIALIZED VIEW LOG ON
"ARUP"."HOTELS"
WITH ROWID, SEQUENCE("HOTEL_ID","CITY")
INCLUDING NEW VALUES;
ALTER MATERIALIZED VIEW LOG FORCE ON
"ARUP"."HOTELS"
ADD ROWID, SEQUENCE("HOTEL_ID","CITY")
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON
"ARUP"."RESERVATIONS"
WITH ROWID, SEQUENCE("RESV_ID","HOTEL_ID","CUST_NAME")
INCLUDING NEW VALUES;
ALTER MATERIALIZED VIEW LOG FORCE ON
"ARUP"."RESERVATIONS"
ADD ROWID, SEQUENCE("RESV_ID","HOTEL_ID","CUST_NAME")
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW ARUP.MV_HOTEL_RESV
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT ARUP.RESERVATIONS.CUST_NAME C1, ARUP.RESERVATIONS.RESV_ID C2, ARUP.HOTELS.CITY
C3, COUNT(*) M1 FROM ARUP.RESERVATIONS, ARUP.HOTELS WHERE ARUP.HOTELS.HOTEL_ID
= ARUP.RESERVATIONS.HOTEL_ID GROUP BY ARUP.RESERVATIONS.CUST_NAME, ARUP.RESERVATIONS.RESV_ID,
ARUP.HOTELS.CITY;
whenever sqlerror EXIT SQL.SQLCODE
begin
dbms_advisor.mark_recommendation('TASK_117',1,'IMPLEMENTED');
end;
/
이 파일은 권고사항을 구현하기 위해 필요한 모든 정보를 포함하고 있으며, 수작업으로 파일을 생성하는 수고를 덜어줍니다. DBA 로봇이 다시 한 번 그 위력을 발휘하는 순간입니다.
Rewrite이 불가능한 경우의 실행 차단
이제 여러분들도 Query Rewrite 기능이 얼마나 중요하고 유용한지 깨닫게 되셨으리라 믿습니다. Query Rewrite는 I/O 작업과 프로세싱 작업을 줄여주고 결과를 한층 빠르게 얻을 수 있게 합니다.
위의 예를 기준으로 계속 설명해 보겠습니다. 사용자가 다음과 같은 쿼리를 실행하는 경우를 생각해 봅시다:
Select city, sum(actual_rate)
from hotels h, reservations r, trans t
where t.resv_id = r.resv_id
and h.hotel_id = r.hotel_id
group by city;
실행 통계는 아래와 같이 확인되었습니다:
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
478 bytes sent via SQL*Net to client
496 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
consistent gets가 6의 값을 갖는 것에 주목하시기 바랍니다. 이것은 매우 낮은 수치입니다. 이는 3 개의 테이블을 기반으로 생성된 2개의 MV를 이용하도록 쿼리가 재작성 되었기 때문에 얻어진 결과입니다. Select 작업은 테이블이 아닌 MV를 통해 수행되었으며, 그 덕분에 훨씬 적은 I/O와 CPU를 사용했습니다.
하지만 Query Rewrite가 실패하면 어떻게 될까요? 실패의 이유에는 여러 가지가 있을 수 있습니다. 초기화 매개변수 query_rewrite_integrity이 “TRUSTED”로 설정되고 MV status가 “STALE”로 설정되었다면 쿼리는 재작성 되지 않을 것입니다. 쿼리를 실행하기 전에 세션 매개변수를 설정함으로써 이 현상을 테스트해 볼 수 있습니다:
alter session set query_rewrite_enabled = false;
위 명령을 실행하고 나면, explain plan은 MV가 아닌 3개 테이블로부터 select를 수행하는 것으로 변경됩니다. 실행 통계도 아래와 같이 달라지게 됩니다:
0 recursive calls
0 db block gets
16 consistent gets
0 physical reads
0 redo size
478 bytes sent via SQL*Net to client
496 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
consistent gets의 값이 6에서 16으로 크게 증가했음을 확인할 수 있습니다. 추가 자원을 활용하기 어려운 실제 환경에서는, 이러한 변화를 용납하기는 어려우며, 따라서 쿼리를 재작성 하는 방법을 선택할 수 밖에 없습니다. 이러한 경우 쿼리가 재작성 되는 경우에만 실행 가능하도록 설정해야 할 수도 있습니다.
Oracle9i Database와 그 이전 버전에서는 단 한 가지 옵션만 가능했습니다. Query Rewrite를 disable할 수는 있었지만 베이스 테이블에 대한 액세스는 막을 수 없었습니다. 반면 Oracle Database 10g는 REWRITE_OR_ERROR라는 힌트를 이용하여 베이스 테이블에 대한 접근을 차단하는 기능을 제공합니다. 위의 쿼리는 아래와 같은 형태로 재작성될 수 있습니다:
select /*+ REWRITE_OR_ERROR */ city, sum(actual_rate)
from hotels h, reservations r, trans t
where t.resv_id = r.resv_id
and h.hotel_id = r.hotel_id
group by city;
에러 메시지는 아래와 같이 표시됩니다:
from hotels h, reservations r, trans t
*
ERROR at line 2:
ORA-30393: a query block in the statement did not rewrite
ORA-30393은 구문이 MV를 사용할 수 있는 형태로 재작성될 수 없으며, 이로 인해 구문 실행에 실패하였음을 의미하는 에러입니다. 이 방법을 사용하여 시스템 리소스를 많이 잡아먹는 쿼리를 사전에 차단할 수 있습니다. 다만 한 가지 주의할 점은, MV 중 (전체가 아닌) 하나만이라도 이용이 가능한 경우 쿼리가 실행된다는 사실입니다. 예를 들어 MV_ACTUAL_SALES은 사용할 수 있지만 MV_HOTEL_RESV는 사용할 수 없는 경우라 해도, 쿼리는 재작성되고 실행되며, 에러는 발생하지 않습니다. 이 경우 execution plan은 아래와 같습니다:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=11 Card=6 Bytes=156)
1 0 SORT (GROUP BY) (Cost=11 Card=6 Bytes=156)
2 1 HASH JOIN (Cost=10 Card=80 Bytes=2080)
3 2 MERGE JOIN (Cost=6 Card=80 Bytes=1520)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'HOTELS' (TABLE) (Cost=2 Card=8 Bytes=104)
5 4 INDEX (FULL SCAN) OF 'PK_HOTELS' (INDEX (UNIQUE)) (Cost=1 Card=8)
6 3 SORT (JOIN) (Cost=4 Card=80 Bytes=480)
7 6 TABLE ACCESS (FULL) OF 'RESERVATIONS' (TABLE) (Cost=3 Card=80 Bytes=480)
8 2 MAT_VIEW REWRITE ACCESS (FULL) OF 'MV_ACTUAL_SALES' (MAT_VIEW REWRITE) (Cost=3 Card=80 Bytes=560)
이 쿼리는 MV_ACTUAL_SALES은 사용하지만 MV_HOTEL_RESV는 사용하지 않고, 대신 HOTELS 테이블과 RESERVATIONS 테이블에 직접 액세스합니다. 만일 HOTELS 또는 RESERVATIONS 테이블에 풀 테이블 스캔이 발생한다면, MV를 사용하는 경우보다 훨씬 많은 리소스를 사용하게 될 것입니다. 쿼리와 MV를 설계하는 과정에서 반드시 참고해야 할 부분입니다.
물론 Resource Manager를 이용하면 리소스 사용을 통제할 수도 있습니다. 하지만 REWRITE_OR_ERROR 힌트를 이용하면 Resource Manager가 호출되기도 전에 쿼리의 실행을 사전 차단하는 것이 가능합니다. Resource Manager는 옵티마이저 통계에 근거하여 필요한 리소스를 예측하며, 통계가 존재하지 않거나 그 정확도가 떨어지는 경우 잘못된 예측을 할 수도 있습니다. 반면 REWRITE_OR_ERROR 힌트를 이용하면 통계와 무관하게 테이블 액세스를 차단할 수 있다는 장점이 있습니다.
Explain Plan의 개선 기능
앞의 explain plan 조회결과 예에서 아래 행을 참고하시기 바랍니다:
MAT_VIEW REWRITE ACCESS (FULL) OF 'MV_ACTUAL_SALES' (MAT_VIEW REWRITE)
MAT_VIEW REWRITE 액세스 방식은 10g에서 처음 소개되는 것입니다. 이것은 테이블 또는 세그먼트가 아닌 MV가 액세스되고 있음을 의미합니다. 이 프로시저를 사용하면 테이블과 MV 중 어느 쪽이 사용되고 있는지를 바로 확인할 수 있습니다.
결론
10g에 새로 추가된tuning advisor의 강력한 권고 기능을 이용하여 MV를 더 쉽게 관리할 수 있습니다. 필자는 개인적으로, 튜닝 권고내역이 바로 실행 가능한 형태의 완성된 스크립트로 제공된다는 점이 마음에 듭니다. 리소스를 최대한 아껴야 하는 의사결정 시스템에서는, REWRITE_OR_ERROR 힌트를 사용하여 쿼리 재작성이 불가능한 경우 실행 자체를 차단하는 방법이 매우 유용하게 사용될 수 있습니다.
제공 : DB포탈사이트 DBguide.net
Oracle Database 10g: DBA를 위한 20가지 주요 기능 - 13번째
Enterprise Manager 10g
마침내, 초심자와 전문가 모두를 위해 Oracle 관리와 운영에 관한 문제를 한꺼번에 해결해 줄 수 있는 툴이 완성되었습니다.
여러분이 일상적인 DBA 업무를 위해 주로 사용하는 툴은 무엇입니까? 사용자 그룹 모임에서 똑같은 질문을 던진 적이 있었습니다.
답변은 DBA의 경력에 따라 달랐습니다. 오랜 경력의 관리자일 수록 SQL*Plus 커맨드 라인 툴을 가장 선호하였고 (필자 역시 그러합니다), 다른 이들의 경우 몇 가지 써드 파티 제품을 중심으로 의견이 갈라졌습니다. 하지만 초심자 수준의 DBA의 경우에는 Enterprise Manager(EM)을 단연 선호하는 툴로 꼽았습니다.
이러한 결과는 충분히 예상할 수 있는 것입니다. Oracle Enterprise Manager는 지난 수 년 동안 꾸준히 발전되어 왔습니다. 처음에 캐릭터 모드 디스플레이의 SQL*DBA로 시작된 EM은 클라이언트 운영체제 기반의 툴로, 그리고 최종적으로는 자바 기반 환경으로 진화되었습니다. EM은 대부분의 DBA 작업을 수행하기에 충분한 수준의 정보를 제공하며, 새로운 문법을 배우기 귀찮아하는 사용자, 또는 GUI 툴을 사용하여 사용자 추가, 데이타파일 수정, 롤백 세그먼트 점검 등의 일상적인 작업을 수행하고자 하는 관리자들을 위한 솔루션으로 자리잡았습니다.
하지만 EM이 기대만큼 빠르게 확산되지 않은 이유 중 하나는, EM이 데이타베이스 서버의 발전속도를 따라잡지 못했다는 사실에 있습니다. 예를 들어 Oracle9i Database의 EM은 (이미 Oracle8i부터 지원되던 기능인) subpartitioning을 지원하지 않았습니다..
Oracle Database 10g에 포함된 EM의 새로운 버전은 과거의 문제로부터의 결별을 선언합니다. 새로운 아키텍처와 새로운 인터페이스가 적용되었으며, 초심자와 숙련된 사용자를 모두 만족할 수 있는 강력하고도 포괄적인 DBA 툴박스를 제공합니다. 더 중요한 사실은, 추가적인 비용 없이 데이타베이스와 함께 기본으로 제공된다는 것입니다. 만일 지금 써드 파티 툴을 검토하고 있는 중이라면, 그 경쟁 제품으로서 EM을 심각하게 고려해 볼 필요가 있습니다. 여러분이 (필자처럼) 커맨드 라인 툴의 맹신자라 하더라도, EM이 여러 상황에서 매우 유용하게 사용될 수 있음을 깨닫게 될 것입니다.
이번 연재에서는 EM의 새로운 기능을 소개합니다. Enterprise Manager가 제공하는 기능의 영역이 워낙 광범위하기 때문에 전체 기능을 종합적으로 다루기는 불가능합니다. 대신, 몇 가지 기본적인 내용을 설명하고 관련 자료를 소개하는 한편, (이 시리즈의 기본 취지에 맞추어) 현실적인 문제를 해결하기 위해 툴을 활용하는 사례들을 제공하고자 합니다.
아키텍처
EM 10g는 Database 10g 소프트웨어와 함께 디폴트로 설치됩니다. 클라이언트 운영체제를 기반으로 하던 이전 버전과 달리, 새로운 버전은 (DB Console이라 불리는) HTTP 서버의 형태로 데이타베이스 서버 상에 설치됩니다 (그림 1 참고). EM 인터페이스는 모든 종류의 웹 브라우저를 지원합니다.
그림 1: EM 아키텍처
DB Console의 포트 번호는 $ORACLE_HOME/install/portlist.ini 파일에 정의되어 있습니다. 이 파일의 예가 아래와 같습니다 (포트 번호는 설치 예에 따라 달라질 수 있습니다).
Ultra Search HTTP port number = 5620
iSQL*Plus HTTP port number = 5560
Enterprise Manager Agent Port =
Enterprise Manager Console HTTP Port (starz10) = 5500
Enterprise Manager Agent Port (starz10) = 1830
위 파일에서 데이타베이스 starz10의 Agent가 포트 1830에서, EM 콘솔이 포트 5500에서 리스닝 하고 있음을 확인할 수 있습니다. 아래 URL을 입력하면 EM 로그온 스크린을 호출할 수 있습니다:
http://starz/em/console/logon/logon
로그온 스크린에서는 DBA 사용자로서 로그인이 가능합니다. 이번 예에서는 SYS 사용자로 로그인해 보겠습니다.
메인 데이타베이스 홈 페이지
로그인 후, 메인 데이타베이스의 홈 페이지가 표시됩니다. 홈 페이지의 상단 부분은 데이타베이스의 중요 정보를 요약 형식으로 제공하고 있습니다 (그림 2 참고).
그림 2: 메인 데이타베이스 홈 페이지 (상단)
위 그림에서 참고할 부분에 동그라미 표시를 하고 번호를 매겼습니다. 제일 먼저 “General” (1)이라 표시된 영역을 참고하시기 바랍니다. 이 영역은 데이타베이스에 관련한 가장 기초적인 정보(인스턴스 네임, 데이타베이스 시작 시간 등)를 제공합니다. Oracle Home 항목의 하이퍼링크를 클릭하면 같은 홈을 공유하는 모든 제품과 오라클 데이타베이스가 표시됩니다. Listeners 항목의 하이퍼링크는 하이퍼링크 상에 표시된 리스너를 통해 등록된 모든 데이타베이스와 인스턴스의 정보로 연결됩니다. 그리고 마지막으로 호스트 명(starz)이 표시됩니다.
"Host CPU" (2) 항목에서는 CPU 관련 정보가 간략하게 제공됩니다. "Active Sessions" (3) 항목은 액티브 세션 통계와 세션의 현재 실행 상태에 대한 정보 (4)를 제공합니다. 위 그림에서는 세션이 사용한 시간 중 99%가 wait에 사용되었음을 확인할 수 있습니다 (그 원인은 뒤에서 확인하기로 합니다). "High Availability" (5) 항목은 가용성에 관련한 정보를 제공합니다. 예를 들어 “Instant Recovery Time”은 인스턴스의 MTTR 목표, 다시 말해 인스턴스 장애 발생 시 복구(instance crash recovery)에 얼마나 많은 시간이 사용될 것인지를 의미합니다.
"Space Usage" (6) 항목을 주목하시기 바랍니다. 이 항목은 23 개의 세그먼트에 대한 경고 메시지가 존재함을 알려주고 있습니다 (경고 메시지의 내용에 대해서는 뒷부분에서 확인하기로 합니다). "Diagnostic Summary" (7)는 데이타베이스의 진단 결과를 요약하고 있습니다. “Performance Finding”의 숫자는, 10g에 새로 추가된 성능 진단 엔진인 ADDM(Automatic Database Diagnostic Monitor)이 발견한 성능 이슈의 수를 의미합니다. 또, EM은 환경을 자동으로 분석하여 베스트 프랙티스를 위반한 사례가 있는지 점검하는 기능을 제공합니다. 분석 작업의 결과는 “Policy Violation”에 표시됩니다. 마지막으로 “Alert Log”는 최근 발생한 ORA error에 대한 정보를 제공합니다. 이 정보는 매우 중요한 가치를 갖습니다. Alert log에 대한 자동 검색을 통해 Oracle 에러를 확인해 줌으로써, 수작업으로 에러 메시지를 찾는 시간을 상당 수준 절감할 수 있습니다.
그림 3은 데이타베이스 홈 페이지의 하단 영역을 보여주고 있습니다. 여기에서는 메시지의 상세한 내용을 확인할 수 있습니다. "Alerts" (1) 항목은 주목할 필요가 있는 알림(alert) 메시지들의 목록을 표시합니다. 제일 첫 번째 메시지(2)는 Archiver 프로세스에 hang이 발생했음을 알려 주고 있습니다. 그 이유를 확인하기 위해 메시지에 연결된 링크를 클릭하면 에러 정보를 담은 alert.log 파일의 상세한 내용이 표시됩니다. 이 경우, flashback recovery area의 공간이 부족한 것이 원인임을 확인되었고, 공간 확보 작업을 거침으로써 Archiver가 다시 정상적으로 동작하게 될 것입니다.
그림 3: 메인 데이타베이스 홈 페이지 (하단)
또 다른 알림 메시지(3)은 wait에 관한 정보를 표시하고 있습니다. 데이타베이스가 69%의 시간을 “Application” wait class에 관련된 wait에 사용하고 있습니다. 홈 페이지의 상단에 있던 wait에 관련한 세션 정보를 기억하십니까? 이 메시지를 통해 그 원인이 확인된 셈입니다. 하이퍼링크를 클릭하면 실제 발생 중인 wait에 대한 상세 정보가 표시됩니다.
다음의 알림 메시지(4)는 감사에 관련된 내용입니다. 사용자 SYS가 특정 클라이언트 머신으로부터 데이타베이스에 접속했습니다. 하이퍼링크를 클릭하면 연결에 관련한 상세 정보를 얻을 수 있습니다. 마지막 메시지(5)는 몇 가지 invalid 오브젝트에 대해 알려주고 있습니다. 하이퍼링크를 클릭하면 invalid 오브젝트에 대한 상세 정보 화면이 표시됩니다.
지금까지 확인한 것처럼, 데이타베이스 홈 페이지는 주목할 필요가 있는 모든 정보를 표시하는 대시보드의 역할을 담당합니다. 세부적인 정보로 화면을 가득 채우는 대신, 인터페이스를 매우 간결한 형태로 정리하고 한 번의 마우스 클릭만으로 상세한 정보를 확인할 수 있도록 배려하였습니다. 이 모든 정보를 직접 수집할 수도 있겠지만 그렇게 하려면 많은 시간과 노력이 필요할 것입니다. EM 10g는 설치 후 별도의 구성 과정을 거치지 않고도 필요한 모든 정보를 제공합니다.
일반적 용례
이번에는 새로운 EM을 활용하여 보다 일상적인 업무를 해결하는 사례에 대해 알아보기로 합시다.
DBA가 수행하는 일상 업무의 하나로 테이블 및 인덱스의 변경을 들 수 있습니다. 데이타베이스 홈 페이지에서 “Administration” 탭을 선택하여 (그림 3의 6번) 클릭합니다. 이 페이지를 통해서 undo segment의 설정, 테이블스페이스 및 스키마 오브젝트의 생성, Resource manager설정, Scheduler의 설정 등의 데이타베이스 관리 작업을 수행할 수 있습니다. “Tables” 탭을 클릭하면 그림 4와 같은 화면이 표시됩니다.
그림 4: 테이블 관리
붉은 원 안에 표시된 플래시 심볼을 주목하시기 바랍니다. 이 심볼은 값의 목록을 불러오기 위해 사용하는 버튼입니다. 화면에서 LOV 심볼을 클릭하면 데이타베이스에 존재하는 사용자의 목록이 표시되어 그 중 하나를 선택할 수 있게 합니다. “Go” 버튼을 클릭하면 해당 사용자를 위한 테이블의 목록이 표시됩니다. 이 과정에서 와일드카드 문자(%)를 사용하는 것도 가능합니다. 예를 들어 “%TRANS%”는 테이블명에 TRANS라는 문자열을 포함하는 모든 테이블을 표시하는 조건으로 사용됩니다.
예를 들어 설명해 보겠습니다. 컬럼의 변경을 위해 TRANS 테이블을 선택합니다 하이퍼링크를 클릭하면 “Edit Table” 스크린이 표시됩니다 (그림 5 참고).
그림 5: 테이블 관리
ACTUAL_RATE 컬럼의 데이타 타입을 NUMBER(10)에서 NUMBER(11)으로 변경하고자 하는 경우, 숫자(그림 5의1번 참고)를 변경한 후 “Apply”를 클릭합니다. 이 작업에 사용되는 SQL 구문을 확인하려면 “Show SQL” 버튼을 클릭합니다.
같은 화면에서 용량의 증가 현황에 관련한 정보를 확인할 수도 있습니다. 나중에 세그먼트 관리에 관한 연재에서 자세히 설명하겠지만, 일정 기간 동안 오브젝트 크기가 증가한 추이를 관찰하는 것이 가능합니다. EM은 같은 정보를 그래픽 형태로 표시합니다. 이 정보를 얻으려면 “Segment” 탭(그림5의 2번 참고))을 클릭합니다. 그러면 그림 6에서 보여지는 것과 같은 Segment 스크린이 표시됩니다.
그림 6: Segment 스크린
붉은 색 원 안에 표시된 항목들을 주목하십시오: 이 화면은 얼마나 많은 공간이 세그먼트에 할당되었고(2), 얼마나 많은 공간이 실제로 사용되고 있으며(1), 얼마나 많은 공간이 낭비되고 있는지(3)를 알려줍니다. 스크린의 하단(4)에는 오브젝트에 할당된 공간과 사용된 공간을 그래픽을 통해 표시하고 있습니다. 이 경우, 테이블의 용량은 일정하게 유지되고 있음을 알 수 있습니다.
그 밖에도 다양한 테이블 관련 작업을 수행할 수 있습니다. 예를 들어 “Constraints” 탭은 제약조건 관리에 활용됩니다.
EM을 이용한 성능 튜닝
지금까지 확인한 것처럼, EM은 룩앤필 면에서 많은 변화를 겪었지만 과거 Java 버전에서 제공되던 기능을 모두 그대로 포함하고 있습니다. 하지만 이전 버전과 분명하게 구분되는 점은, EM이 Oracle Database의 새로운 기능을 모두 지원한다는 사실입니다. (예를 들어 EM을 이용하여 subpartition 기능을 구현할 수 있게 되었습니다.)
하지만 숙련된 DBA라면 툴이 더 많은 기능을 제공할 수 있기를 원할 것입니다. 성능 문제에 관련한 트러블슈팅 및 사전 예방적인 성능튜닝과 같은 경우입니다. 한 가지 예를 들어보겠습니다. 앞부분의 예에서 데이타베이스가 “Application” wait class에 대한 대기에 많은 시간을 소모하고 있음을 확인한 바 있으며 그 원인을 진단하길 원하고 있습니다(그림 3의 3)). 어떠한 튜닝 작업이든 CPU, 디스크, 호스트 서브시스템의 상호 관계를 확인하는 것이 중요하며, 따라서 이러한 모든 변수들을 하나의 맥락으로 파악할 수 있다면 도움이 될 것입니다. 이를 위해 데이타베이스 홈 페이지에서 “Performance” 탭을 클릭합니다. 그림 7에서 보여지는 것과 같은 화면이 뜰 것입니다.
그림 7: Performance 탭
같은 시간대를 기준으로 다양한 성능 지표가 표시되고 있으며, 따라서 다양한 성능 변수의 상호관계를 쉽게 파악할 수 있습니다. 그림 7 (3)번의 급격한 부하 증가 현상은 Scheduler 태스크의 실행 주기와 일치합니다. 또 같은 기간 동안 7개의 세션이 Scheduler 관련 wait를 위해 대기하였음을 알 수 있습니다. 그렇다면 어떤 영향이 있었을까요? 같은 시기의 CPU 성능지표를 확인하시기 바랍니다 (초록색 영역). (4)번의 기준선으로 미루어 CPU의 사용률이 가장 높게 올라간 것을 확인할 수 있습니다. 그 이전과 이후에는 CPU 사용량이 급격하게 증가한 경우가 한 차례도 없었습니다. (1) 번의 Run Queue Length가 증가한 것은 Scheduler가 실행되면서 과도한 메모리를 요구했기 때문으로 보이며, 이로 인해 페이징 작업(2)이 늘어난 것을 알 수 있습니다. 이처럼 모든 현상을 하나의 맥락으로 이해함으로써 데이타베이스 성능 문제를 보다 정확하게 진단할 수 있습니다.
뒷부분의 Run Queue Length(5)와 Paging Rate(6) 곡선은 Physical Reads(7)의 곡선과 일치합니다. 그 원인은 무엇일까요?
위의 결과를 “Sessions: Waiting and Working” 그래프와 비교해 보면, 대부분의 세션이 “Application” wait class를 대기하고 있었음을 알 수 있습니다. 하지만 해당 시간대에 대기 중이었던 이벤트가 무엇이었는지 분명하게 파악할 필요가 있습니다. 해당 시간대의 영역을 클릭하면 그림 8에서 보여지는 것과 같은 Active Sessions 스크린이 표시됩니다.
그림 8: Active Sessions Waits
위 화면을 통해 세션이 enq: TX - row lock contention 이벤트를 대기 중이었음을 알 수 있습니다. 그렇다면 그 원인이 된 SQL 구문은 무엇일까요? 이미 화면에 문제의 SQL 구문에 대한 SQL ID(8rkquk6u9fmd0)가 표시되고 있습니다 (붉은색 원 참고). 해당 SQL ID를 클릭하면 그림 9와 같은 SQL Details 스크린이 표시됩니다.
그림 9: SQL Details 스크린
위 스크린을 통해 SQL 구문과 execution plan을 포함하는 상세 정보를 확인할 수 있습니다. 이 경우 SQL이 row lock contention을 발생시킨 것을 알 수 있으며, 따라서 애플리케이션 설계가 성능 문제의 원인이 된 것으로 결론을 내릴 수 있습니다.
Latch Contention
"Performance" 탭을 클릭한 결과가 아래 그림과 같은 경우를 가정해 봅시다.
그림 10: Performance 탭 (두 번째 예)
그림에서 붉은색 사각형으로 표시된 영역의 성능 지표를 주목하시기 바랍니다. 오전 12시 20분을 전후하여 CPU에 관련한 wait가 많이 발생했고, 이로 인해 CPU의 run queue 사이즈가 증가했음을 알 수 있습니다. 이제 wait 현상의 원인을 분석할 차례입니다.
먼저 그래프의 CPU 경합에 관련된 정보 영역을 클릭하여 (“Click Here”표 표시된 부분 참고) wait에 관련한 상세한 정보를 확인합니다.
그림 11: Active Session Waits
"Active Sessions Working: CPU Used" 그래프의 회색으로 표시된 영역(1)을 참고하시기 바랍니다. 마우스를 드래그하여 해당 영역의 위치를 바꿀 수도 있으며, 이 경우, 아래의 파이 차트(2와 3)는 선택된 시간대를 기준으로 다시 계산됩니다. 위 그림에서 ID 8ggw94h7mvxd7를 갖는 SQL 구문에 많은 부하가 걸리고 있음을 알 수 있습니다(2). 또 사용자 ARUP의 SID 265 세션이 가장 많은 자원을 사용하고 있다는 사실도 확인할 수 있습니다(3). 해당 세션을 클릭하면 “Session Details” 스크린이 표시됩니다. “Wait Events” 탭을 클릭하여 세션에 관련된 wait 이벤트의 상세한 정보를 확인할 수 있습니다 (그림 12 참고).
그림 12: Wait Event 상세 정보
위 그림에서, library cache에 대한 wait가 118 centisecond로 가장 오랜 시간이 걸리고 있음을 확인합니다. “Latch: Library Cache”의 하이퍼링크를 클릭하면 그림 13과 같은 화면이 나타납니다.
그림 13: Wait Histogram
위 화면은 10g 이전 버전에서는 제공되지 않던 정보를 표시하고 있습니다. Latch 경합에 관련된 문제를 진단하면서, 118 centisecond의 대기시간이 여러 세션들의 짧은 wait들이 합산된 결과인지, 아니면 하나의 세션이 오랜 시간을 대기한 결과인지 어떻게 알 수 있을까요?
위의 히스토그램이 그 정보를 제공합니다. 약 250여 개의 세션이 wait에 1 millisecond (첫 번째 붉은색 원 참고)를 사용했으며, 180여 개의 세션이 4~8 millisecond(두 번째 붉은색 원 참고)를 사용했음을 알 수 있습니다. 따라서 매우 짧은 시간의 wait가 원인이 되고 있으며, 따라서 latch 경합이 심각한 수준이 아니라는 결론을 내릴 수 있습니다.
데이타베이스 홈 페이지에서 “Advisor Central” 탭을 클릭하면, ADDM, SQL Access Advisor를 비롯한 각종 Advisor 툴에 접근할 수 있습니다. ADDM은 자동으로 성능지표를 수집하여 Advisor Central 페이지에 그 결과를 표시하며, 각각의 결과를 클릭하면 ADDM이 제시하는 권고안을 확인할 수 있습니다. SQL Tuning Advisor가 제시하는 권고안도 이 페이지에서 확인할 수 있습니다. (ADDM과 SQL Tuning Advisor에 대한 자세한 내용은 향후 연재에서 설명합니다.)
더욱 쉬워진 유지보수 작업
데이타베이스 홈 페이지의 “Maintenance” 탭은 백업 및 복구, 데이타 익스포트/임포트 (Data Pump), 데이타베이스 클로닝 등의 유지보수 작업에 관련한 툴의 정보를 제공합니다. 이 화면에서 유지보수 정책에 위반된 내역을 확인하고 베스트 프랙티스를 구현할 수 있습니다.
결론
이 문서는 EM의 기능 중 극히 일부만을 설명하고 있습니다. 이 문서는 EM의 기능을 전반적으로 설명하기보다, 특정한 업무를 위해 EM을 사용하는 예에 초점을 맞추어 논의를 진행했습니다.
제공 : DB포탈사이트 DBguide.net
Oracle Database 10g: DBA를 위한 20가지 주요 기능 - 14번째
Virtual Private Database
VPD에 새롭게 추가된 4가지 policy type, 컬럼 단위 policy 적용, column masking 등의 신기능을 활용하여 한층 강력하고 유연한 보안 환경을 구현할 수 있습니다.
Fine Grained Access Control이라는 용어로 불리기도 하는 Virtual Private Database(VPD)는 로우(row) 레벨의 강력한 보안 기능을 제공합니다. VPD는 Oracle8i에서 처음 소개된 이후, 교육용 소프트웨어에서 금융 애플리케이션에 이르기까지 다양한 영역에서 활용되고 있습니다.
VPD는 접수된 데이타 쿼리를 사용자의 권한에 맞도록 테이블의 일부분만을 포함하는 부분적인 뷰에 대한 쿼리로 자동 변경합니다. VPD는 모든 쿼리에 대해 사용자에게 접근 허용된 로우(row)만을 필터링하도록 쿼리 조건을 추가합니다. 예를 들어 사용자가 SCOTT가 account manager로 할당된 account만을 보아야 하는 경우, VPD는 쿼리를 아래와 같이 재작성합니다:
select * from accounts;
to:
select * from accounts
where am_name = 'SCOTT';
DBA가 ACCOUNTS 테이블에 보안 정책을 설정하면, 설정된 정책에는 policy function이라 불리는 함수가 적용됩니다. 이 함수는 “where am_name = 'SCOTT'“와 같은 문자열을 반환하고, 생성된 문자열은 쿼리 조건에 추가되는 predicate으로 활용됩니다. VPD 기능에 대해 친숙하지 않은 경우라면, 오라클 매거진의 기사 "Keeping Information Private with VPD를 참고하실 것을 권장 드립니다."
Policy Types
이처럼 반복적인 파싱을 통해predicate을 생성하는 것은 성능적으로 부담이 될 수 있습니다. 경우에 따라서는 성능 개선을 위한 대안을 고려할 수 있습니다. 대부분의 경우 predicate은 사용자가 누구이고, 그 사용자의 권한이 어디까지이고, 사용자의 상급 관리자가 누구인지 등의 여부에 따라 다이내믹하게 결정됩니다. Policy function에 의해 생성 및 반환되는 문자열은 매우 다이내믹한 성격을 가지며, 따라서 오라클은 정확성을 보장하기 위해서 매번 policy function을 반복적으로 수행합니다. 이는 성능 및 자원사용률 면에서 낭비를 초래할 수 있습니다. 이처럼 predicate이 실행할 때마다 달라지는 경우의 policy를 “dynamic” policy라 부릅니다. Dynamic policy는 Oracle9i 데이타베이스와 그 이전 릴리즈에서 이미 지원되어 왔습니다.
이와 별도로, Oracle Database 10g는 성능 향상을 목적으로 context_sensitive, shared_context_sensitive, shared_static, static 등의 다양한 policy 유형을 추가로 제공합니다: 이제 각 policy 유형의 의미와 활용 방안을 알아 보기로 합시다.
Dynamic Policy. 하위 버전과의 호환성을 유지하기 위한 목적에서, 10g의 디폴트 policy 유형은 “dynamic”으로 설정되어 있습니다. Dynamic policy는 테이블이 액세스 될 때마다 각각의 로우(row) 및 모든 사용자에 대해서 policy function을 실행합니다. Dynamic policy가 predicate를 활용하는 방법에 대해 좀 더 자세히 알아보겠습니다:
where am_name = 'SCOTT'
where 키워드를 제외한다면, predicate은 크게 두 부분으로 나뉘어집니다. 등호 기호의 앞부분(am_name)과 뒷부분(‘SCOTT’)로 나뉘어집니다. 대부분의 경우, 뒷부분에는 사용자 데이타로부터 제공되는 변수와 같습니다. (예를 들어 사용자가 SCOTT라면 값은 ‘SCOTT’가 됩니다.) 등호 기호의 앞부분은 static한 문자열로 이루어지며, 따라서 각 로우(row) 별로 policy function을 반복적으로 수행할 필요가 없습니다. 이처럼 등호 기호의 앞부분과 뒷부분의 static / dynamic 여부를 미리 알고 있다면 부분적으로 성능을 개선하는 것이 가능해집니다. 10g에서는 이를 위해 “context_sensitive” 타입을 지원하며, dbms_rls.add_policy 호출 과정에서 이 타입을 매개변수로 사용할 수 있습니다:
policy_type => dbms_rls.context_sensitive
이번에는 여러 개의 컬럼을 갖는 ACCOUNTS 테이블에 대한 예를 설명하겠습니다. ACCOUNTS 테이블에 포함된 BALANCE 컬럼은 해당 계좌의 잔액을 표시합니다. 특정 사용자가 application context에 의해 정의된 액수 이하의 잔액을 갖는 계좌들을 조회할 수 있도록 허용되었다고 가정해 봅시다. 하드 코딩을 통해 계좌 잔액의 액수를 입력하는 대신 Policy function을 이용해 다음과 같이 application context를 활용할 수 있습니다:
create or replace vpd_pol_func
(
p_schema in varchar2,
p_table in varchar2
)
return varchar2
is
begin
return 'balance < sys_context(''vpdctx'', ''maxbal'')';
end;
Application context VPDCTX 의MAXBAL 속성을 설정해 두고 함수가 런타임에 값을 가져오도록 할 수 있습니다.
위의 코드 예제를 주의 깊게 살펴 보시기 바랍니다. Predicate은 크게 두 부분(‘<’기호 이전과 이후)으로 나뉘어집니다. 앞부분의 “balance”는 static한 문자열입니다. 뒷부분은 application context가 변경되기 전까지는 원래 값을 그대로 유지하므로 어느 정도 static하다고 볼 수 있습니다. 따라서 application context속성이 변경되지 않는 한, 전체 predicate이 static하다고 판단할 수 있으며, 따라서 함수를 재실행할 필요가 없습니다. Oracle Database 10g는 policy type이 context sensitive로 지정된 경우 이러한 최적화 알고리즘을 사용합니다. 세션에서 context 변경이 발생하지 않는 경우 함수는 재실행되지 않으며, 상당한 수준의 성능 향상 효과를 볼 수 있습니다.
Static Policy. 경우에 따라서는 보다 static한 형태의 predicate이 사용될 수도 있습니다. 위의 예제에서는 maximum balance를 변수로서 정의했습니다. 이러한 접근방식은 Oracle userid가 많은 웹 사용자에 의해 공유되고 사용자의 권한에 따라 이 변수(application context)가 변경되어야 하는 웹 애플리케이션 환경에서 유용합니다. 예를 들어 TAO와 KARTHIK이라는 두 사용자가 APPUSER라는 동일한 데이타베이스 사용자로 접근하는 경우에도, 각 세션 별로 설정된 application context에 의해 서로 다른 권한을 할당 받게 됩니다. 다시 말해 MAXBAL의 값은 Oracle userid가 아닌 TAO와 KARTHIK의 개별 세션 별로 바인드 됩니다.
Static policy의 경우 predicate은 아래와 같이 보다 예측 가능한 형태로 제시됩니다.
LORA와 MICHELLE는 각각 Acme Bearings와 Goldtone Bearings의 어카운트 관리자입니다. 두 사람은 데이타베이스에 연결할 때 개인 ID를 사용하며 각자에게 허용된 로우(row)만을 조회할 수 있습니다. Lora의 경우 predicate은 “where CUST_NAME = 'ACME'”, Michelle의 경우 predicate은 “where CUST_NAME = 'GOLDTONE'”입니다. 이 경우 predicate은 사용자ID와 연결되며, 따라서 그들에 의해 생성된 어떤 세션이든 application context가 제공하는 같은 값을 predicate으로 사용하게 됩니다.
10g는 이러한 경우 SGA 캐시에 predicate을 저장하고 해당 세션에 대해서는 policy function을 재실행하지 않고 계속적으로 재활용합니다. Policy function은 아래와 같습니다:
create or replace vpd_pol_func
(
p_schema in varchar2,
p_table in varchar2
)
return varchar2
is
begin
return 'cust_name = sys_context(''vpdctx'', ''cust_name'')';
end;
Policy는 아래와 같이 정의됩니다:
policy_type => dbms_rls.static
이와 같은 접근법을 사용하면 policy function이 단 한 차례만 수행됨을 보장할 수 있습니다. 세션에서 application context가 변경되는 경우에도 함수는 재실행되지 않으므로 성능이 대폭적으로 향상됩니다.
Static policy는 여러 구독자(subscriber)가 사용하는 애플리케이션 환경에서 유용합니다. 이 경우 여러 사용자 또는 구독자가 단일 데이타베이스의 데이타를 공유하게 됩니다. 구독자가 로그인하면, 로그인 과정에서 “after-login trigger”가 동작하여 application context를 설정하고 policy function을 실행함으로써 predicate을 얻게 됩니다.
하지만 static policy는 양날의 칼과도 같습니다. 위의 예에서는, application context의 VPDCTX.CUST_NAME속성의 값이 세션 내에서 변경되지 않는다고 가정했습니다. 만일 그 가정이 잘못되었다면 어떻게 될까요? 속성 값이 변해도 policy function이 재실행되지 않으므로 predicate에 새로운 값이 반영되지 않을 것이며, 따라서 완전히 잘못된 결과가 나올 것입니다. 그러므로 static policy를 사용할 때는 주의할 필요가 있으며, 속성 값이 변하지 않음을 분명히 확신할 수 있을 때에만 static policy를 사용해야 합니다. 이러한 가정이 불가능하다면, context sensitive policy를 사용하는 것이 무난합니다.
Shared Policy Types. 코드를 재활용하고 파싱된 코드의 활용도를 최대로 높이려면, 여러 테이블에 공통적으로 적용되는 policy function을 구현할 필요가 있습니다. 예를 들어, 위의 예에서 계좌 별로 두 개의 테이블(SAVINGS와 CHECKING)이 존재하지만 규칙은 똑같이 적용되는 경우를 가정해 봅시다. 사용자는 자신에게 허용된 것 이외의 계좌 잔액은 조회할 수 없습니다. 이 경우 CHECKING 테이블과 SAVINGS 테이블에 사용되는 policy function은 동일합니다. 생성되는 policy의 유형은 context_sensitive로 가정합니다.
다음과 같은 이벤트가 순서대로 발생한다고 가정해 봅시다:
1. 세션 연결
2. application context 설정
3. select * from savings;
4. select * from checking;
Application context가 3번, 4번 과정에서 변경되지 않았음에도 조회되는 테이블이 다르기 때문에 policy function은 재실행될 것입니다. 하지만 policy function이 같기 때문에 재실행할 필요는 없었습니다. 10g는 동일한 policy를 여러 오브젝트가 공유하는 기능을 제공합니다. 위의 예의 경우 policy type은 다음과 같이 정의됩니다:
New in 10g is the ability to share a policy across objects. In the above example, you would define the policy type of these policies as:
policy_type => dbms_rls.shared_context_sensitive
이처럼 policy를 “shared”로 지정함으로써 함수의 불필요한 실행을 방지하고 성능을 향상시킬 수 있습니다.
Selective Columns
이번에는 특정 컬럼이 select 된 경우에만 VPD policy가 적용되는 경우를 생각해 봅시다. ACCOUNTS 테이블이 아래와 같은 레코드를 갖는다고 가정합니다:
ACCTNO ACCT_NAME BALANCE
------ ------------ -------
1 BILL CAMP 1000
2 TOM CONNOPHY 2000
3 ISRAEL D 1500
Michelle은 balance가 1,600이 넘는 account를 조회할 수 없습니다. Michelle이 다음과 같은 쿼리를 실행한 경우:
select * from accounts;
아래와 같은 결과를 확인하게 될 것입니다:
ACCTNO ACCT_NAME BALANCE
------ ------------ -------
1 BILL CAMP 1000
3 ISRAEL D 1500
1,600이 넘는 balance를 갖는 acctno 2는 실행 결과에 포함되지 않았습니다. Michelle의 관점에서 볼 때 이 테이블은 3개가 아닌 2개의 로우를 갖습니다. 따라서 Michelle이 아래와 같은 쿼리를 실행하는 경우에도 그 결과는 3이 아닌 2가 반환됩니다:
select count(*) from accounts;
하지만 security policy에 예외 조건을 적용할 필요도 있습니다.
지금 Michelle은 모든 account balance를 조회하려 하는 것이 아니고 그저 데이타의 count만을 확인하려 하는 것입니다. Michelle이 조회할 수 없는 레코드의 count를 포함시키는 것을 허용하는 것을 고려할 수도 있을 것입니다. 10g는 이러한 경우를 위해 dbms_rls.add_policy 호출 과정에서 사용할 수 있는 새로운 매개변수를 추가하였습니다:
sec_relevant_cols => 'BALANCE'
이 매개변수가 사용된 경우, 사용자가 BALANCE 컬럼을 명시적으로 또는 암시적으로(예: select *) 조회하는 경우에 한해 VPD policy는 해당 로우를 조회 대상에서 제외시킵니다. 하지만 그 밖의 경우(예를 들어 사용자가 전체 로우의 count를 조회하는 경우)에는 테이블의 모든 로우에 대한 select가 허용됩니다. 이 경우 아래와 같이 쿼리를 수행하면 그 결과로 2가 아닌 3이 반환됩니다:
select count(*) from accounts;
하지만 아래의 쿼리는 두 개의 레코드만을 반환합니다.
select * from accounts;
Column Masking
이번에는 다른 조건이 붙는 경우를 생각해 봅시다. 일정한 임계값 이상의 balance를 갖는 레코드에 대한 조회를 완전히 제한하는 대신, 임계값 이상의 balance 컬럼에 대해 마스킹(masking)을 수행하는 조건으로 모든 레코드를 반환하는 방법을 선택할 수도 있습니다.
Michelle은 1,600 이상의 balance를 갖는 account를 조회할 수 없습니다. Michelle이 아래와 같은 쿼리를 실행하면:
select * from accounts;
acctno 1과 acctno3의 두 가지 레코드만을 확인하게 됩니다. 그 대신 아래와 같은 결과를 보기 원할 수 있습니다:
ACCTNO ACCT_NAME BALANCE
------ ------------ -------
1 BILL CAMP 1000
2 TOM CONNOPHY
3 ISRAEL D 1500
위의 경우 모든 레코드가 표시되지만, 2000의 balance를 가진 acctno2 레코드의 BALANCE 컬럼은 null로 표시됩니다. 이러한 방법을 “column masking”이라 부르며, dbms_rls.add_policy 호출 과정에서 아래 매개변수를 사용하여 활성화할 수 있습니다:
sec_relevant_cols_opt => dbms_rls.all_rows
이 방법은 특정 컬럼에 대한 보안 유지가 필요한 경우 매우 유용하게 활용되며, 구현과정에서 별도의 코딩이 불필요합니다. 또 이 방법을 데이타 암호화의 대안으로 활용할 수도 있습니다.
결론
Oracle Database 10g의 VPD 기능은 매우 강력한 형태로 발전되었으며, policy를 기준으로 선택적인 컬럼을 마스킹하거나, 특정 컬럼이 액세스되는 경우에만 policy를 적용하는 등의 다양한 요구사항을 지원합니다. 또 애플리케이션의 성격에 따라 다른 policy를 적용함으로써 성능을 향상시킬 수도 있습니다.
제공 : DB포탈사이트 DBguide.net
Oracle Database 10g: DBA를 위한 20가지 주요 기능 - 15번째
세그먼트의 관리
Oracle Database 10g가 새로 제공하는 공간 재확보 기능, 온라인 테이블 재구성, 스토리지 증가량 예측 기능 등을 이용하여 세그먼트의 공간을 효율적으로 관리할 수 있습니다.
오래 전, Oracle Database의 경쟁 RDBMS 제품을 평가해 달라는 요청을 받은 일이 있습니다. 경쟁사의 프리젠테이션이 진행되는 동안, 청중들이 가장 감탄했던 기능이 바로 온라인 재구성(online reorganization) 기능이었습니다. 이 제품은 (오라클로 따지면 세그먼트에 해당하는) 영역의 데이타 블록을 온라인 상태에서 재배치하는 기능을 제공했습니다.
당시 오라클이 제공하던 Oracle9i Database는 이러한 기능을 제공하지 못했습니다. 이제 Oracle Database 10g는 온라인 상태에서 낭비되는 공간을 재확보하고 오브젝트를 보다 컴팩트(compact)한 형태로 관리할 수 있게 하는 기능을 추가적으로 제공합니다.
이 기능을 자세히 살펴 보기에 앞서, 이전에는 세그먼트 관리를 어떤 방법으로 수행했는지 설명하도록 하겠습니다.
기존의 관리 방법
그림 1과 같은 형태로 채워진 세그먼트를 가정해 봅시다. 작업이 수행되면서 그림 2와 같이 일부 로우(row)가 삭제되고 나면 낭비되는 공간이 생기게 됩니다. 낭비되는 공간은 (i) 남아있는 블록의 마지막 영역과 기존 테이블의 마지막 영역의 사이에서, 그리고 (ii) 로우가 부분적으로만 삭제된 블록 내부에서 발생합니다.
그림 1: 테이블에 할당된 블록. (로우는 회색 사각형으로 표시됨)
오라클이 이 영역에 대한 할당을 바로 해제하지 않고, 새로운 insert 작업 및 기존 로우의 확장에 대비한 예비 공간으로 활용합니다. 지금까지의 점유되었던 공간의 최고점을 High Water Mark(HWM)이라 부릅니다 (그림 2 참고).
그림 2: 일부 로우가 삭제된 후 (HWM은 변경되지 않았음)
하지만 이와 같은 접근 방식에는 두 가지 문제점이 존재합니다:
- 사용자의 쿼리가 풀 테이블 스캔을 발생시키는 경우, 오라클은 (설사 관련된 데이타가 전혀 존재하지 않는 경우라 하더라도) HWM 아래쪽의 모든 영역을 스캔합니다. 이로 인해 풀 테이블 스캔에 소요되는 시간이 길어질 수 있습니다.
- 로우가 direct path 정보와 함께 insert 되는 경우 (예를 들어 APPEND 힌트를 사용한 Insert, 또는 SQL*Loader direct path를 통해 insert 되는 경우) 새로 추가되는 데이타 블록은 HWM의 위쪽 영역에 추가됩니다. 따라서 HWM의 아래쪽 영역은 낭비된 채로 남게 됩니다.
- Oracle9i와 그 이전 버전에서 공간을 재확보하려면, 테이블을 drop하고 다시 생성한 다음 데이타를 다시 로드하는 방식, 또는 ALTER TABLE MOVE 명령을 사용하여 테이블을 다른 테이블스페이스로 이동하는 방식을 사용해야 했습니다. 이 두 가지 방식은 모두 오프라인 상태에서 수행되어야 한다는 문제가 있습니다. 그 대안으로 online table reorganization 기능을 사용할 수도 있지만, 이를 위해서는 기존 테이블 크기의 두 배나 되는 공간이 필요했습니다.
10g의 경우 이러한 작업은 훨씬 간소화되었습니다. 10g의 Automatic Segment Space Management(ASSM)이 해당 테이블스페이스에 활성화되어 있는 경우, 세그먼트, 테이블, 인덱스를 shrink하고 free block을 재확보한 뒤 다른 용도로 할당하도록 데이터베이스로 반환됩니다. 그 자세한 방법을 알아보기로 합시다.
10g의 세그먼트 관리 기능
웹사이트를 통해 온라인으로 접수된 예약 정보를 보관하는 BOOKINGS라는 이름의 테이블이 존재한다고 가정해 봅시다. 확인 절차를 거친 예약은 BOOKINGS_HIST 테이블에 저장되고 해당 레코드는 BOOKINGS 테이블에서 삭제됩니다. 예약에서 확인까지 걸리는 시간은 고객에 따라 다릅니다. 이 경우 레코드 삭제로 인해 남은 공간이 충분하지 않은 경우에는 레코드가 테이블 HWM의 위쪽 영역에 insert 됩니다.
이제 낭비되는 공간을 재확보할 차례입니다. 먼저 해당 세그먼트에서 얼마나 많은 공간을 확보할 수 있는지 확인해야 합니다. 이 테이블은 ASSM이 적용된 테이블스페이스에 위치하고 있으므로, 아래와 같이 DBMS_SPACE 패키지의 SPACE_USAGE 프로시저를 사용해야 합니다:
declare
l_fs1_bytes number;
l_fs2_bytes number;
l_fs3_bytes number;
l_fs4_bytes number;
l_fs1_blocks number;
l_fs2_blocks number;
l_fs3_blocks number;
l_fs4_blocks number;
l_full_bytes number;
l_full_blocks number;
l_unformatted_bytes number;
l_unformatted_blocks number;
begin
dbms_space.space_usage(
segment_owner => user,
segment_name => 'BOOKINGS',
segment_type => 'TABLE',
fs1_bytes => l_fs1_bytes,
fs1_blocks => l_fs1_blocks,
fs2_bytes => l_fs2_bytes,
fs2_blocks => l_fs2_blocks,
fs3_bytes => l_fs3_bytes,
fs3_blocks => l_fs3_blocks,
fs4_bytes => l_fs4_bytes,
fs4_blocks => l_fs4_blocks,
full_bytes => l_full_bytes,
full_blocks => l_full_blocks,
unformatted_blocks => l_unformatted_blocks,
unformatted_bytes => l_unformatted_bytes
);
dbms_output.put_line(' FS1 Blocks = '||l_fs1_blocks||' Bytes = '||l_fs1_bytes);
dbms_output.put_line(' FS2 Blocks = '||l_fs2_blocks||' Bytes = '||l_fs2_bytes);
dbms_output.put_line(' FS3 Blocks = '||l_fs3_blocks||' Bytes = '||l_fs3_bytes);
dbms_output.put_line(' FS4 Blocks = '||l_fs4_blocks||' Bytes = '||l_fs4_bytes);
dbms_output.put_line('Full Blocks = '||l_full_blocks||' Bytes = '||l_full_bytes);
end;
/
The output is:
FS1 Blocks = 0 Bytes = 0
FS2 Blocks = 0 Bytes = 0
FS3 Blocks = 0 Bytes = 0
FS4 Blocks = 4148 Bytes = 0
Full Blocks = 2 Bytes = 16384
실행 결과를 통해 4,148개의 블록이 75-100%의 free space(FS4)를 포함하고 있으며, 이를 제외하고는 free space가 전혀 존재하지 않음을 확인할 수 있습니다. Full block은 단 2개에 불과합니다. 따라서 4,148 개의 블록에서 공간을 확보할 수 있습니다.
이제 테이블에 row-movement가 활성화되어 있는지 점검해야 합니다. row-movement를 활성화 하기 위해서는 아래와 같이 입력합니다:
alter table bookings enable row movement;
또는 Enterprise Manager 10g의 Administration 페이지에서 작업할 수도 있습니다. 또, 테이블의 모든 rowid 기반 트리거가 비활성화되어 있는지 점검해야 합니다. (로우가 이동되면서 rowid가 변경될 수 있기 때문입니다.)
마지막으로, 아래 명령을 사용하여 테이블의 기존 로우를 재구성합니다.
alter table bookings shrink space compact;
이 명령은 그림 3과 같은 형태가 되도록 블록 내부의 로우를 재배치하고, HWM 아래쪽 영역에 free block을 확보합니다. (하지만 HWM 자체는 변경되지 않습니다.)
그림 3: 재구성작업을 거친 뒤의 테이블 블록
작업이 완료된 후 공간 사용률에 변화가 있는지 확인해 봅시다. 앞에서 소개한 PL/SQL 코드를 사용하여 얼마나 많은 블록이 재구성되었는지 확인할 수 있습니다:
FS1 Blocks = 0 Bytes = 0
FS2 Blocks = 0 Bytes = 0
FS3 Blocks = 1 Bytes = 0
FS4 Blocks = 0 Bytes = 0
Full Blocks = 2 Bytes = 16384
이제 매우 중요한 변화가 있었음을 확인할 수 있습니다. FS4 블록 (75-100%의 여유 공간을 갖는 블록)의 수가 4,148에서 0으로 바뀌었습니다. 또 FS3 블록(50-75%의 여유 공간을 갖는 블록)의 수가 0에서 1로 증가했습니다. 반면 HWM은 변경되지 않았으며, 전체 공간사용률에도 아무런 변화가 없었습니다. 사용중인 전체 공간은 아래와 같이 확인할 수 있습니다:
SQL> select blocks from user_segments where segment_name = 'BOOKINGS';
BLOCKS
---------
4224
테이블이 점유중인 블록의 수(4,224)는 변경되지 않았으며, HWM도 기존 위치를 그대로 유지하고 있습니다. 다음과 같은 명령을 사용하면 HWM의 위치를 아래쪽을 이동하고 상위 영역을 재확보할 수 있습니다:
alter table bookings shrink space;
여기서 COMPACT 키워드가 사용되지 않은 점을 주목하시기 바랍니다. 위 구문을 실행하면 테이블이 사용되지 않은 블록을 반환하고 HWM을 재설정합니다. 아래와 같이 테이블에 할당된 공간을 확인하고 그 결과를 점검할 수 있습니다:
SQL> select blocks from user_segments where segment_name = 'BOOKINGS'; BLOCKS ---------- 8
블록의 수가 4,224 개에서 8개로 줄었습니다. 그림 4에서 보여지는 것처럼 테이블 내에서 사용되지 않던 모든 공간이 반납되어 다른 세그먼트에서 활용할 수 있게 되었습니다.
그림 4: Shrink 작업 수행 후 free block이 데이타베이스로 반납된 결과
Shrink 작업은 온라인 상태에서 수행되며 사용자에게 아무런 영향을 미치지 않습니다.
테이블 인덱스에 대한 shrink 작업도 아래와 같이 수행할 수 있습니다:
alter table bookings shrink space cascade;
온라인 shrink 명령은 낭비되는 공간을 재확보하고 HWM을 재설정하는 매우 강력한 기능입니다. 필자는 개인적으로 HWM 재설정 기능의 유용성을 높이 평가합니다. HWM을 재설정함으로써 풀 테이블 스캔의 성능을 향상시킬 수 있기 때문입니다.
Shrinking 작업 대상 세그먼트 찾기
온라인 shrink 작업을 수행하기 전에, 압축율을 비약적으로 향상시킬 수 있는 대상 세그먼트를 찾아내는 작업을 수행해야 할 수도 있습니다. dbms_space 패키지에 내장된 verify_shrink_candidate 함수를 사용하여 이 작업을 간단하게 마무리할 수 있습니다. 아래 PL/SQL 코드는 대상 세그먼트가 1,300,000 바이트로 shrink 될 수 있는지 테스트합니다:
begin
if (dbms_space.verify_shrink_candidate
('ARUP','BOOKINGS','TABLE',1300000)
) then
:x := 'T';
else
:x := 'F';
end if;
end;
/
PL/SQL procedure successfully completed.
SQL> print x
X
--------------------------------
T
If you use a low number for the target shrinkage, say 3,000:
begin
if (dbms_space.verify_shrink_candidate
('ARUP','BOOKINGS','TABLE',30000)
) then
:x := 'T';
else
:x := 'F';
end if;
end;
이 경우 변수 x의 값은 ‘F’로 반환되었습니다. 이는 테이블이 3,000 바이트로 shrink 될 수 없음을 의미합니다.
인덱스 크기의 예측
이번에는 특정 테이블, 또는 여러 개의 테이블에 대해 인덱스를 생성해야 하는 경우를 가정해 봅시다. 컬럼, uniqueness 등의 구조에 관련한 일반적인 고려사항을 제외하고 가장 중요한 작업을 들라면, 인덱스의 크기를 예상하는 일을 꼽을 수 있을 것입니다. 테이블스페이스의 공간이 새로운 인덱스를 수용할 수 있을 만큼 충분한지 확인해야 합니다.
Oracle9i Database와 그 이전 버전의 경우, DBA들은 스프레드시트 또는 별개의 프로그램의 사용하여 인덱스의 크기를 예측하곤 했습니다. 10g에서는 새로 추가된 DBMS_SPACE 패키지를 이용해서 이 작업을 간단하게 마무리할 수 있습니다. 그렇다면 그 실제 활용 사례를 알아봅시다.
BOOKINGS 테이블의 booking_id 컬럼과 cust_name 컬럼을 대상으로 하는 새로운 인덱스를 추가해야 합니다. 새로운 인덱스가 얼마나 많은 공간을 사용하게 될까요? 아래와 같은 PL/SQL 스크립트를 실행하면 간단하게 확인할 수 있습니다:
declare
l_used_bytes number;
l_alloc_bytes number;
begin
dbms_space.create_index_cost (
ddl => 'create index in_bookings_hist_01 on bookings_hist '||
'(booking_id, cust_name) tablespace users',
used_bytes => l_used_bytes,
alloc_bytes => l_alloc_bytes
);
dbms_output.put_line ('Used Bytes = '||l_used_bytes);
dbms_output.put_line ('Allocated Bytes = '||l_alloc_bytes);
end;
/
실행 결과가 아래와 같습니다:
Used Bytes = 7501128
Allocated Bytes = 12582912
인덱스의 크기를 증가시킬 수 있는 매개변수(INITRANS 등)를 사용한 경우를 가정해 봅시다.
declare
l_used_bytes number;
l_alloc_bytes number;
begin
dbms_space.create_index_cost (
ddl => 'create index in_bookings_hist_01 on bookings_hist '||
'(booking_id, cust_name) tablespace users initrans 10',
used_bytes => l_used_bytes,
alloc_bytes => l_alloc_bytes
);
dbms_output.put_line ('Used Bytes = '||l_used_bytes);
dbms_output.put_line ('Allocated Bytes = '||l_alloc_bytes);
end;
/
실행 결과는 아래와 같습니다:
Used Bytes = 7501128
Allocated Bytes = 13631488
INITRANS 매개변수의 값을 높인 결과 Allocated Bytes가 훨씬 증가했음을 확인할 수 있습니다. 이와 같은 방법으로 인덱스가 사용하게 될 공간의 크기를 쉽게 예측할 수 있습니다.
하지만 두 가지 주의해야 할 점이 있습니다. 먼저, 이 프로세스는 “SEGMENT SPACE MANAGEMENT AUTO”가 활성화된 테이블스페이스에만 적용 가능합니다. 두 번째로, 패키지는 테이블 통계를 근거로 인덱스의 크기를 예측합니다. 따라서 테이블의 통계가 최신 상태를 유지하고 있는지 점검하는 것이 중요합니다. 가장 주의할 점은, 테이블에 통계가 존재하지 않는 경우 패키지가 에러를 발생시키는 대신 엉뚱한 계산 결과를 제시한다는 사실입니다.
테이블 크기의 예측
이번에는 BOOKING_HIST 테이블이 평균 30,000의 row length를 가진 로우로 구성되어 있고 테이블의 PCTFREE 매개변수가 20으로 설정된 경우를 가정해 보겠습니다. PCT_FREE를 30으로 올리는 경우 테이블의 크기가 얼마나 증가하게 될까요? PCT_FREE가 10% 증가한 만큼, 테이블의 크기도 10% 증가하게 될까요? DBMS_SPACE 패키지의 CREATE_TABLE_COST 프로시저를 사용하면 간단하게 확인할 수 있습니다. 테이블의 크기를 예측하기 위한 코드가 아래와 같습니다:
declare
l_used_bytes number;
l_alloc_bytes number;
begin
dbms_space.create_table_cost (
tablespace_name => 'USERS',
avg_row_size => 30,
row_count => 30000,
pct_free => 20,
used_bytes => l_used_bytes,
alloc_bytes => l_alloc_bytes
);
dbms_output.put_line('Used: '||l_used_bytes);
dbms_output.put_line('Allocated: '||l_alloc_bytes);
end;
/
실행 결과는 다음과 같습니다:
Used: 1261568
Allocated: 2097152
테이블의 PCT_FREE 매개변수를 30에서 20으로 아래와 같이 조정한 후 다시 실행합니다:
pct_free => 30
we get the output:
Used: 1441792
Allocated: 2097152
사용된 공간의 크기가 1,261,568에서 1,441,792로 증가했습니다. 이는 PCT_FREE 매개변수가 데이타 블록에 더 많은 여유 공간을 할당하기 대문입니다. 증가된 비율은 예상대로 10%가 아닌 14%로 확인되었습니다. 이처럼 DBMS_SPACE 패키지를 사용하여 PCT_FREE와 같은 매개변수를 변경하는 경우 또는 테이블을 다른 테이블스페이스로 이동하는 경우의 테이블 크기를 예측할 수 있습니다.
세그먼트의 크기 예측
Acme Hotel은 주말을 맞아 수요가 급증할 것을 예상하고 있습니다. DBA는 증가하는 수요를 감당하기에 충분한 공간이 남아 있는지 확인하려 합니다. 테이블의 크기가 얼마나 증가할지 어떻게 예측할 수 있을까요?
10g가 제공하는 예측 기능의 정확성은 우리를 놀라게 하기에 충분합니다. 결과를 얻기 위해서는 아래와 같은 쿼리를 실행하기만 하면 됩니다.
select * from
table(dbms_space.OBJECT_GROWTH_TREND
('ARUP','BOOKINGS','TABLE'));
dbms_space.object_growth_trend() 함수는 PIPELINEd 포맷으로 레코드를 반환하며, TABLE() casting을 통해 그 결과를 확인할 수 있습니다. 출력된 결과가 아래와 같습니다:
TIMEPOINT SPACE_USAGE SPACE_ALLOC QUALITY
------------------------------ ----------- ----------- ------------
05-MAR-04 08.51.24.421081 PM 8586959 39124992 INTERPOLATED
06-MAR-04 08.51.24.421081 PM 8586959 39124992 INTERPOLATED
07-MAR-04 08.51.24.421081 PM 8586959 39124992 INTERPOLATED
08-MAR-04 08.51.24.421081 PM 126190859 1033483971 INTERPOLATED
09-MAR-04 08.51.24.421081 PM 4517094 4587520 GOOD
10-MAR-04 08.51.24.421081 PM 127469413 1044292813 PROJECTED
11-MAR-04 08.51.24.421081 PM 128108689 1049697234 PROJECTED
12-MAR-04 08.51.24.421081 PM 128747966 1055101654 PROJECTED
13-MAR-04 08.51.24.421081 PM 129387243 1060506075 PROJECTED
14-MAR-04 08.51.24.421081 PM 130026520 1065910496 PROJECTED
출력된 결과는 시간(TIMEPOINT 컬럼)별로 BOOKINGS 테이블 크기의 증가 추이를 보여주고 있습니다. SPACE_ALLOC 컬럼은 테이블에 할당된 바이트 수를 의미하며 SPACE_USAGE 컬럼은 그 중 몇 바이트가 실제로 사용되고 있는지를 나타내고 있습니다. 이 정보는 Automatic Workload Repository(AWR, 본 연재 제 6 주 참고)에 의해 수집된 데이타를 기반으로 합니다. 위 데이타 중 실제로 데이타가 수집된 것은 2004년 3월 9일입니다 (QUALITY 컬럼의 값이 “GOOD”인 것으로 확인합니다). 따라서 해당 시점의 할당 공간 및 사용 공간의 수치는 정확하다고 판단할 수 있습니다. 반면, 이후 모든 데이타의 QUALITY 컬럼은 “PROJECTED”의 값을 가지며, 이는 데이타가 AWR에 의해 수집된 데이타를 근거로 추정된 것임을 의미합니다.
3월 9일 이전 데이타의 경우 QUALITY 컬럼의 값이 “INTERPOLATED”로 표시되어 있습니다. 이 데이타는 수집되거나 추정된 것이 아니며, 단순히 수집된 데이타의 패턴에 대한 interpolation을 통해 얻어진 것입니다. 이처럼 데이타가 수집되지 않은 과거 시점이 존재하는 경우, 그 값은 interpolation을 통해 계산됩니다.
결론
세그먼트 단위의 관리 기능을 이용하여 세그먼트 내부의 공간에 대한 설정을 변경하고, 테이블 내부의 여유 공간을 재확보하거나 온라인 테이블 재구성 작업을 통해 성능을 향상시킬 수 있습니다. 10g의 새로운 기능은 테이블 재구성에 관련된 반복적인 업무를 절감하는 효과를 제공합니다. 특히 온라인 세그먼트에 대한 shrink 기능은, 내부 fragmentation을 제거하고 high water mark를 조정함으로써 풀 테이블 스캔의 성능을 극적으로 향상시키는 효과가 있습니다.
제공 : DB포탈사이트 DBguide.net
Oracle Database 10g: DBA를 위한 20가지 주요 기능 - 16번째
Transportable Tablespaces
10g의 transportable tablespace는 서로 다른 플랫폼 간의 데이타 이동을 지원하므로, 데이타 배포 작업을 한층 쉽고 빠르게 수행할 수 있습니다. 또, external table을 이용한 다운로드 기능을 활용하여 데이타 이동 및 변환 작업을 보다 효율적으로 완료할 수 있습니다.
데이타베이스 간의 데이타 이동 작업을 어떻게 처리하십니까? 여러 가지 방법이 있겠지만 그 중에서도 가장 돋보이는 것이 바로 transportable tablespace입니다. Transportable tablespace는 대상 테이블스페이스 집합이 자체적으로 다른 테이블스페이스에 있는 오브젝트를 참조하는 것이 없는 “self-contained”이어야 하며, 테이블스페이스를 읽기전용 상태로 설정한 뒤 메타데이타만을 먼저 익스포트(export)하고, OS 레벨의 카피 작업을 통해 데이타파일을 타겟 플랫폼으로 복사한 다음, 데이타 딕셔너리에 메타데이타를 임포트(이 프로세스를 “plugging”이라 부르기도 합니다.)하는 방식으로 데이타를 전송합니다. .
OS 파일 카피 작업은 SQL*Loader를 이용한 익스포트/임포트 작업과 같은 데이타 이동 방식에 비해 일반적으로 훨씬 빠른 처리 성능을 보입니다. 하지만 Oracle9i Database와 그 이전 버전의 경우, 소스 데이타베이스와 타겟 데이타베이스가 동일 OS플랫폼으로 구성되어야 한다는 제약사항 때문에 그 유용성에 제한을 받았습니다 (예를 들어 Solaris와 HP-UX 간의 테이블스페이스 전송은 불가능했습니다).
Oracle Database 10g에서는 이러한 기능 제약이 사라졌습니다. OS byte order가 동일하기만 하면 서로 다른 플랫폼 간이라도 테이블스페이스 전송이 가능해졌습니다. byte order에 대한 상세한 설명은 이 세션의 범위를 넘어서지만, 간략히 살펴보면 Windows를 포함하는 일부 운영체제의 경우, 멀티-바이트 바이너리 데이타를 저장할 때 least significant byte를 최하위 메모리 주소에 저장하는 방식을 사용합니다. 이러한 시스템을 “little endian”이라 부릅니다. 반면, Solaris를 비롯한 다른 운영체제는 most significant byte를 최하위 메모리 주소에 저장하며, 이러한 시스템을 “big endian”이라 부릅니다. Big-endian 시스템이 little-endian 시스템으로부터 데이타를 읽어 들이려면 변환 프로세스를 거쳐야 합니다. 그렇지 않은 경우, byte order 문제로 데이타가 올바르게 표시되지 않습니다. 하지만 동일한 endian을 갖는 플랫폼 간에 테이블스페이스를 전송하는 경우에는 변환 작업이 필요하지 않습니다.
그렇다면 어떤 운영체제가 어떤 byte order를 사용하는지 어떻게 알 수 있을 까요? 아래와 같은 쿼리를 사용하면 바로 확인할 수 있습니다:
SQL> select * from v$transportable_platform order by platform_id;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ----------------------------------- --------------
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
3 HP-UX (64-bit) Big
4 HP-UX IA (64-bit) Big
5 HP Tru64 UNIX Little
6 AIX-Based Systems (64-bit) Big
7 Microsoft Windows IA (32-bit) Little
8 Microsoft Windows IA (64-bit) Little
9 IBM zSeries Based Linux Big
10 Linux IA (32-bit) Little
11 Linux IA (64-bit) Little
12 Microsoft Windows 64-bit for AMD Little
13 Linux 64-bit for AMD Little
15 HP Open VMS Little
16 Apple Mac OS Big
인텔 기반 Linux 운영체제를 사용하는 SRC1서버의 USERS 테이블스페이스를, Microsoft Windows 기반 TGT1 서버로 전송하는 경우를 생각해 봅시다. 이 경우 소스 플랫폼과 타겟 플랫폼 모두 little endian type 시스템입니다. USERS 테이블스페이스의 데이타파일은 users_01.dbf입니다. 전송 작업은 아래와 같은 절차를 거쳐 수행됩니다:
테이블을 READ ONLY 상태로 설정합니다:
alter tablespace users read only;
테이블을 익스포트 합니다.. 운영체제 프롬프트에서 다음과 같이 입력합니다:
exp tablespaces=users transport_tablespace=y file=exp_ts_users.dmp
exp_ts_users.dmp 파일은 메타데이타만을 포함하고 있으므로 그 크기가 매우 작습니다.
exp_ts_users.dmp 파일과 users_01.dbf 파일을 TGT1 서버로 복사합니다. FTP를 사용하는 경우에는 binary 옵션을 설정합니다.
데이타베이스에 테이블스페이스를 “플러깅(plugging)” 합니다. 운영체제 프롬프트에서 다음과 같이 입력합니다.
imp tablespaces=users transport_tablespace=y file=exp_ts_users.dmp datafiles='users_01.dbf'
4번째 단계를 마치고 나면 타겟 데이타베이스에 USERS 테이블스페이스가 생성되며, 테이블스페이스의 컨텐트도 사용 가능한 상태가 됩니다.
시스템 SRC1과 TGT1은 각각 Linux, Windows 운영체제를 사용한다는 사실을 명심하시기 바랍니다. 만일 Oracle9i 환경이었다면 TGT1의 데이타베이스가 users_01.dbf 데이타파일을 인식하지 못했을 것이고, 결국 전체 프로세스가 실패로 돌아갔을 것입니다. 이러한 경우라면 일반적인 익스포트/임포트 기능을 이용하거나, 플랫 파일을 생성한 뒤 SQL*Loader로 로드하거나, 데이타베이스 링크를 통해 direct load insert를 실행해야 할 것입니다.
10g에서는 타겟 데이타베이스가 다른 플랫폼으로부터 전송된 데이타파일을 정상적으로 인식하므로, 이러한 대안을 고려할 필요가 없습니다. 위의 예에서는 OS의 byte order 역시 동일하므로 (little endian), 변환 작업을 수행할 필요도 없습니다.
이 기능은 데이타 웨어하우스의 데이타가, 특수한 목적으로 운영되는 소규모 데이타 마트(data mart)에 정기적으로 전송되는 환경에서 특히 유용합니다. 10g 환경으로 구성된 경우, 데이타 웨어하우스는 대형 엔터프라이즈급 서버에, 데이타 마트는 Linux 기반 인텔 머신과 같은 저가형 서버에 구성하는 것이 가능해집니다. 이처럼 transportable tablespace를 사용하여 다양한 하드웨어와 운영체제를 조합한 환경을 구현할 수 있습니다.
서로 다른 endian을 갖는 시스템 간의 데이타 전송
소스 플랫폼과 타겟 플랫폼이 서로 다른 endian을 갖는 경우 어떻게 데이타 전송을 처리할 수 있을까요? 앞에서 설명한 것처럼 타겟 서버와 소스 서버의 byte order가 다르면 전송된 데이타를 올바르게 인식할 수 없으므로, 단순 카피 작업으로 데이타 파일을 이동하는 것이 불가능합니다. 하지만 방법은 있습니다. 바로 Oracle 10g RMAN 유틸리티가 데이타파일을 다른 byte order로 변환하는 기능을 지원하고 있습니다.
위의 예에서, 만일 SRC1 서버가 Linux(little endian)를 기반으로 하고, TGT1 서버가 HP-UX(big endian)을 기반으로 한다면, 3단계와 4단계의 사이에 변환을 위한 별도의 단계를 적용해야 합니다. RMAN을 사용하면 Linux 환경의 데이타파일을 HP-UX 포맷으로 변환할 수 있습니다 (단 테이블스페이스가 읽기전용 상태로 설정되어 있어야 합니다).
RMAN> convert tablespace users
2> to platform 'HP-UX (64-bit)'
3> format='/home/oracle/rman_bkups/%N_%f';
Starting backup at 14-MAR-04
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00004 name=/usr/oradata/dw/starz10/users01.dbf
converted datafile=/home/oracle/rman_bkups/USERS_4
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
Finished backup at 14-MAR-04
위 과정을 거치면 /home/oracle/rman_bkups 디렉토리에 표준 RMAN 파일 포맷의 파일이 _ 의 파일명으로 생성됩니다. 결국 USERS 테이블스페이스 자체는 전혀 변경되지 않았고, HP-UX 환경을 위한 새로운 파일이 생성되었습니다. 이제 이 파일을 타겟 시스템으로 복사한 뒤 위에서 설명한 것과 같은 처리 과정을 거치면 됩니다.
RMAN 변환 명령은 매우 강력합니다. 위와 같은 명령을 사용하는 경우, RMAN은 순차적으로 데이타파일을 생성합니다. 여러 개의 데이타파일을 포함하는 테이블스페이스를 처리할 때에는 여러 개의 변환 프로세스를 병렬적으로 수행하도록 명령할 수도 있습니다. 그렇게 하려면 위 명령에 아래 구문을 삽입하면 됩니다:
parallelism = 4
위와 같이 하면 네 개의 RMAN 채널이 생성되어 각각 별도의 데이타파일에 대해 변환 작업을 수행합니다. 하지만 parallelism이 정말로 효과를 발휘하는 것은, 많은 수의 테이블스페이스를 한꺼번에 변환할 때입니다. 아래는 두 개의 테이블스페이스(USERS와 MAINTS)를 HP-UX 포맷으로 변경하는 예입니다:
RMAN> convert tablespace users, maints
2> to platform 'HP-UX (64-bit)'
3> format='/home/oracle/rman_bkups/%N_%f'
4> parallelism = 5;
Starting backup at 14-MAR-04
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=244 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=243 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=245 devtype=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: sid=272 devtype=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: sid=253 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00004 name=/usr/oradata/dw10/dw10/users01.dbf
channel ORA_DISK_2: starting datafile conversion
input datafile fno=00005 name=/usr/oradata/dw10/dw10/users02.dbf
channel ORA_DISK_3: starting datafile conversion
input datafile fno=00006 name=/usr/oradata/dw10/dw10/maints01.dbf
channel ORA_DISK_4: starting datafile conversion
input datafile fno=00007 name=/usr/oradata/dw10/dw10/maints02.dbf
converted datafile=/home/oracle/rman_bkups/USERS_4
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
converted datafile=/home/oracle/rman_bkups/USERS_5
channel ORA_DISK_2: datafile conversion complete, elapsed time: 00:00:00
converted datafile=/home/oracle/rman_bkups/MAINTS_6
channel ORA_DISK_3: datafile conversion complete, elapsed time: 00:00:01
converted datafile=/home/oracle/rman_bkups/MAINTS_7
channel ORA_DISK_4: datafile conversion complete, elapsed time: 00:00:01
Finished backup at 14-MAR-04
위의 실행결과에서, 변환된 파일이 기존 파일명과 무관하고 이해하기도 어려운 파일명을 갖게 되는 것을 볼 수 있습니다 (예를 들어, users01.dbf는 USERS_4로 변환됩니다). 원하는 경우 데이타파일의 naming format을 변경할 수 있습니다. 이 프로세스는 Data Guard에서 사용하는 데이타파일 naming 방식과 유사합니다:
RMAN> convert tablespace users
2> to platform 'HP-UX (64-bit)'
3> db_file_name_convert '/usr/oradata/dw10/dw10','/home/oracle/rman_bkups'
4> ;
Starting backup at 14-MAR-04
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00004 name=/usr/oradata/dw10/dw10/users01.dbf
converted datafile=/home/oracle/rman_bkups/users01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00005 name=/usr/oradata/dw10/dw10/users02.dbf
converted datafile=/home/oracle/rman_bkups/users02.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished backup at 14-MAR-04
위와 같이 하면 기존의 파일명을 그대로 유지할 수 있습니다. /home/oracle/rman_bkups 디렉토리에 가 보면, users01.dbf와 users02.dbf가 생성된 것을 확인할 수 있습니다. 위 예제의 경우, 파일의 변환 작업은 소스 플랫폼에서 수행되었습니다. 필요한 경우 타겟 플랫폼에서 변환을 수행할 수도 있습니다. 예를 들어, users01.dbf를 HP-UX 기반의 TGT1 서버로 카피한 후 아래와 같이 HP-UX 포맷으로 변환할 수 있습니다:
In the above cases, we converted the files on the source platform. However, you can do that on the target platform as well. For example, you can copy file users01.dbf to host TGT1 running HP-UX and then convert the file to HP-UX format with:
RMAN> convert
2> datafile '/usr/oradata/dw10/dw10/users01.dbf'
3> format '/home/oracle/rman_bkups/%N_%f'
4> ;
이렇게 함으로써 해당 디렉토리에 지정된 포맷의 파일을 생성할 수 있습니다.
그렇다면 데이타파일을 굳이 타겟 플랫폼에서 변환하는 이유가 무엇일까요? 첫 번째로, 소스 플랫폼의 테이블스페이스를 READ ONLY 상태로 두는 기간이 짧아지므로 다운타임을 줄일 수 있다는 점을 들 수 있습니다. 데이타파일을 3중 미러 형태로 구성하고 테이블스페이스를 읽기 전용으로 설정한 다음, 3번째 미러를 분리한 후 곧바로 테이블스페이스를 읽기/쓰기 모드로 변경할 수도 있습니다. 분리된 3번째 미러는 타겟 시스템에 마운트된 후 변환됩니다. 이렇게 하면 테이블스페이스가 읽기 전용 상태에 있는 기간을 최소화할 수 있습니다.
또 다른 이유로 성능을 들 수 있습니다. 지속적으로 부하가 발생하는 OLTP 데이타베이스에서 RMAN 변환 작업을 수행함으로써 시스템에 불필요한 부담을 주게 될 수 있습니다. 그 대신, 병렬 작업에 최적화된 데이타 웨어하우스 서버에서 오프라인 형태로 변환 작업을 처리하는 것이 바람직할 수 있습니다.
External Table을 데이타 전송 매개체로 활용하기
Oracle9i Database에서 처음 소개된 external table은 일정한 형식을 갖춘 일반 텍스트 파일을 테이블처럼 보이게 하고 SQL 구문을 통해 접근할 수 있게 하는 기능입니다. OLTP 데이타베이스에서 운영 중인 TRANS 테이블의 컨텐트를, external table을 사용하여 데이타 웨어하우스 데이타베이스로 이동해야 하는 경우를 생각해 봅시다. 그 과정이 아래와 같습니다: OLTP 데이타베이스에서, TRANS 테이블의 컨텐트를 포함하는 텍스트 파일을 생성합니다. 생성된 텍스트 파일을 /home/oracle/dump_dir 디렉토리에 trans_flat.txt라는 이름으로 저장합니다. (SQL 구문을 이용하여 텍스트 파일의 생성이 가능합니다.)
spool trans_flat.txt
select ||','|| ||','|| ...
from trans;
spool off
ftp, rcp 등의 전송 메커니즘을 사용하여 파일을 데이타 웨어하우스 서버에 복사합니다. (파일은 /home/oracle/dump_dir 디렉토리에 위치하고 있습니다.) 데이타 웨어하우스 데이타베이스에서 dump_dir 디렉토리를 생성합니다:
On the data warehouse database, create a directory object named dump_dir as:
create directory dump_dir as '/home/oracle/dump_dir';
external table을 생성합니다:
create table trans_ext
(
... ...
)
organization external
(
type oracle_loader
default directory admin
access parameters
(
records delimited by newline
badfile 'trans_ext.bad'
discardfile 'trans_ext.dis'
logfile 'trans_ext.log'
fields terminated by "," optionally enclosed by '"'
(
... ...
)
)
location ('trans_flat.txt')
)
reject limit unlimited;
Direct load insert, merge 등의 일반적인 방법을 사용하여 external table을 일반 테이블로 로드합니다.
위에서 텍스트 파일을 생성하는 첫 번째 단계는 가장 많은 시간을 소요합니다. SQL 구문을 사용하여 텍스트를 생성하고 파일에 스풀링하는 과정은 절차 상으로는 간단하지만 실행 시간이 오래 걸립니다. SQL*Plus 대신 Pro*C 또는 OCI 프로그램을 사용하여 처리 시간을 어느 정도 단축할 수 있지만 그래도 꽤 오랜 시간이 필요합니다. 컬럼을 수작업으로 지정하는 것도 작업을 지체시키는 요인이 됩니다.
이 두 가지 문제는 10g에서 완전히 해결되었습니다. 이제 external table 생성 프로세스를 사용하여 테이블을 포터블 포맷으로 신속하게 언로드할 수 있습니다. 위 예의 첫 번째 단계는 아래와 같은 간단한 SQL 구문으로 대치됩니다:
create directory dump_dir as '/home/oracle/dump_dir';
create table trans_dump
organization external
(
type oracle_datapump
default directory dump_dir
location ('trans_dump.dmp')
)
as
select * from trans
/
위 명령은 /home/oracle/dump_dir 디렉토리에 trans_dump.dmp라는 이름의 파일을 생성합니다. 이 파일은 ASCII 텍스트 파일이 아닙니다. 메타데이타는 일반 텍스트이지만, 실제 데이타는 raw 포맷을 사용하고 있습니다. 하지만, 이 파일은 export dump 파일과 마찬가지로 모든 운영체제에서 호환 가능하며, 데이타의 다운로드가 매우 빠르게 수행된다는 점에서 export와 차별화됩니다. 이 파일을 데이타 웨어하우스 서버에 카피하고 위에서 설명한 것과 동일한 방법으로 external table을 생성할 수 있습니다.
그렇다면 지금까지 설명한 방법이 기존에 사용되어 오던 데이타 전송 메커니즘과 어떤 차이가 있는 것일까요? 첫 번째로, 복잡한SQL 구문을 작성하지 않고도 포터블 파일을 매우 빠르게 생성할 수 있습니다. 두 번째로, 이 파일을 external table의 input으로 적용해서 일반적인 테이블을 다루듯 다른 테이블로의 데이타 로드 작업을 간단하게 완료할 수 있습니다. 또 아래와 같은 구문을 사용하면 external table로의 데이타 다운로드 성능을 향상시킬 수 있습니다:
create table trans_dump
organization external
(
type oracle_datapump
default directory dump_dir
location ('trans_dump.dmp')
)
parallel 2
as
select * from trans
/
위 명령은 병렬적인 형태로 파일 생성 작업을 수행하도록 합니다. 이 방법은 멀티-CPU 환경에서 유용합니다. 이와 별도로, 동시에 여러 개의 external table을 생성하도록 할 수도 있습니다:
create table trans_dump
organization external
(
type oracle_datapump
default directory dump_dir
location ('trans_dump_1.dmp','trans_dump_2.dmp')
)
parallel 4
as
select * from trans
/
위 명령은 trans_dump_1.dmp와 trans_dump_2.dmp라는 두 개의 파일을 생성합니다. 이 방법은 파일을 여러 개의 물리적 디바이스 또는 컨트롤러로 분산하고 I/O 성능을 향상시키는데 유용합니다.
결론
10g의 transportable tablespace를 적극적으로 활용함으로써, 분석된 데이타가 더 신속하게, 그리고 더 높은 빈도로 사용자에게 제공되는 환경을 구현할 수 있습니다. 또 이 기능은 오프라인 미디어를 통해 이기종 시스템의 데이타베이스로 데이타를 배포하는 데에도 이용됩니다. External table을 이용한 다운로드 기능은 대용량 데이타 처리를 위한 ETL 툴로써 손색이 없습니다.
Furthermore, by making transportable tablespaces viable, 10g makes data refreshes quicker and more frequent so that analyzed data is available to end users sooner. This capability can also be used to publish data via offline media to different databases, regardless of their host systems. Using external table downloads the utility to move large quantities of data as an ETL tool is finally available to the end user.
제공 : DB포탈사이트 DBguide.net
Oracle Database 10g: DBA를 위한 20가지 주요 기능 - 17번째
Automatic Shared Memory Management
오라클 인스턴스의 메모리 풀에 필요한 만큼의 메모리를 할당하는 작업 때문에 번거로우셨습니까? ? Automatic Shared Memory Management 기능을 이용하여 필요한 영역에 메모리를 자동으로 할당할 수 있습니다.
여러분이 초심자이든, 또는 숙련된 DBA이든 아래와 같은 에러를 최소한 한 번쯤은 경험해 보셨을 것입니다:
ORA-04031: unable to allocate 2216 bytes of shared memory ("shared pool"... ...
또는:
ORA-04031: unable to allocate XXXX bytes of shared memory
("large pool","unknown object","session heap","frame")
또는:
ORA-04031: unable to allocate bytes of shared memory ("shared pool",
"unknown object","joxlod: init h", "JOX: ioc_allocate_pal")
첫 번째 에러의 원인은 명백해 보입니다. shared pool에 할당된 메모리가 사용자 요청에 응답하기에 충분하지 못하기 때문입니다 (이 경우 shared pool의 크기가 문제가 아니라, 바인드된 변수를 사용하지 않는 데 따르는 과도한 파싱 작업으로 인해 발생한 fragmentation이 원인일 수도 있습니다. 이것인 필자가 즐겨 언급하는 주제이긴 하지만, 여기에서는 당면한 과제에 집중하기로 합시다.) 두 번째와 세 번째 에러는 각각 large pool과 Java pool의 공간이 충분하지 못한 것이 원인이 되어 발생합니다.
애플리케이션의 변경 작업을 거치지 않고 이 에러를 해결해야 합니다. 그렇다면 어떤 방법을 사용해야 할까요? 결국 사용 가능한 메모리를 오라클 인스턴스가 사용하는 모든 풀에 어떻게 분배할 것인가의 문제로 귀결됩니다.
어떻게 배분할 것인가?
오라클 인스턴스의 System Global Area (SGA)는 buffer cache, shared pool, Java pool, large pool, redo log buffer 등의 메모리 영역을 포함하고 있습니다. 각각의 풀은 고정된 크기의 운영체제의 메모리 공간을 점유하며, 그 크기는 초기화 매개변수 파일을 통해 DBA가 지정할 수 있습니다.
4종류의 풀(db block buffer cache, shared pool, Java pool, large pool)은 SGA 공간의 대부분을 차지합니다. (redo log buffer는 상대적으로 작은 공간을 사용할 뿐 아니라, 그 성격상 이 문서에서 논의되는 내용과 무관합니다.) DBA는 각각의 영역에 할당된 메모리가 충분한지 점검해야 합니다.
각 영역의 크기를 2GB, 1GB, 1GB, 1GB로 설정하기로 결정한 경우를 가정해 봅시다. 먼저 아래와 같이 초기화 매개변수를 설정하여 데이타베이스 인스턴스의 풀 사이즈를 변경합니다:
db_cache_size = 2g
shared_pool_size = 1g
large_pool_size = 1g
java_pool_size = 1g
이제 이 매개변수를 자세히 분석해 봅시다. 과연 설정된 값이 정확하다는 걸 보증할 수 있을까요?
아마 여러분 모두 나름대로 의심을 품고 있을 것입니다. 실제로 각각의 풀에 필요한 만큼의 공간이 정확히 할당되었다고 자신할 수 있는 이는 아무도 없습니다. 필요한 메모리 공간은 데이타베이스 내부 프로세싱에 따라 결정되며, 이는 시시각각으로 변화하기 때문입니다.
예제를 통해 설명해 보겠습니다. “전형적인” OTLP 데이타베이스 환경에 상대적으로 적은 용량의 메모리가 buffer cache에 할당되어 있습니다. 어느 날, 사용자가 일별 마감 보고서를 작성하기 위해 대규모의 테이블 스캔 작업을 실행합니다. Oracle9i Database는 온라인 상태에서 메모리 할당량을 변경하는 기능을 제공합니다. 전체 메모리 용량이 제한되어 있는 상황에서, DBA는 large pool과 Java pool에 할당된 일부 공간을 buffer cache로 돌리기로 결정합니다:
alter system set db_cache_size = 3g scope=memory;
alter system set large_pool_size = 512m scope=memory;
alter system set java_pool_size = 512m scope=memory;
이 방법은 일시적으로 그 효과를 발휘합니다. 하지만 large pool을 사용하는 RMAN 작업이 야간에 실행되면서 large pool의 용량이 부족해집니다. DBA가 이번에는 db cache의 용량 일부를 large pool에 할당합니다.
RMAN 작업은 완료되었지만, 다음에는 Java pool을 사용하는 배치 프로그램이 실행됩니다. Java pool에 관련한 에러를 확인한 DBA는 Java pool과 db cache의 용량을 확보하기 위해 아래와 같이 실행합니다:
alter system set db_cache_size = 2G scope=memory;
alter system set large_pool_size = 512M scope=memory;
alter system set java_pool_size = 1.5G scope=memory;
다음날 아침, OLTP 작업이 재개되고 DBA는 똑같은 과정을 다시 반복해야 합니다!
이러한 악순환의 고리를 끊어버리기 위해 각각의 풀에 최대한의 용량을 영구적으로 할당하는 방법을 고려할 수 있습니다. 하지만 사용 가능한 실제 메모리보다 많은 용량을 SGA 영역에 할당함으로써 스와핑과 페이징이 빈번하게 발생하는 위험을 감수해야만 합니다. 차라리 수작업으로 메모리를 재할당하는 방법이 (번거롭긴 하지만) 적절해 보입니다.
또 다른 방법으로 각 풀에 합리적인 선의 최소 용량을 할당하는 방법을 생각해 볼 수 있습니다. 하지만 요구되는 용량이 증가할 때마다 성능이 저하될 것입니다.
어떤 방법을 사용하든 SGA에 할당된 전체 메모리 용량은 변하지 않는 반면, 각각의 풀에 할당되는 용량은 자주 변경되어야 합니다. 그렇다면, RDBMS가 사용자의 요구를 감지하고 메모리를 자동으로 재할당한다면 작업이 훨씬 수월해지지 않을까요?
Oracle Database 10g의 Automatic Shared Memory Management가 바로 이러한 기능을 제공합니다. SGA_TARGET 매개변수를 통해 SGA의 전체 사이즈를 설정하고 나면, SGA 내부의 각 풀은 워크로드를 기준으로 다이내믹하게 관리됩니다. 결국 DBA가 해야 할 일은 SGA_TARGET 매개변수를 설정하는 것 밖에 없게 됩니다.
Automatic Shared Memory Management 설정
예를 통해 설명해 보겠습니다. 먼저, SGA의 전체 크기를 결정합니다. 현재 얼마나 많은 용량이 할당되어 있는지 확인하려면 아래와 같이 입력합니다:
SQL> select sum(value)/1024/1024 from v$sga;
SUM(VALUE)/1024/1024
--------------------
500
SGA의 현재 크기는 약 500MB로 설정되어 있으므로 이 수치를 SGA_TARGET에 적용하면 됩니다. 다음으로 아래와 같이 구문을 실행합니다:
alter system set sga_target = 500M scope=both;
위와 같이 설정함으로써, 각각의 풀에 대한 용량을 설정할 필요가 없게 됩니다. 이제 각각의 풀에 관련한 매개변수의 값을 0으로 설정하거나, 해당 항목을 완전히 삭제합니다.
shared_pool_size = 0
large_pool_size = 0
java_pool_size = 0
db_cache_size = 0
데이타베이스를 다시 시작하고 변경된 설정을 적용합니다.
같은 작업을 Enterprise Manager 10g에서 수행할 수도 있습니다. 데이타베이스 홈 페이지에서 “Administration” 탭을 선택한 후 “Memory Parameter”를 클릭합니다. 메모리 관련 매개변수가 수동 설정되어 있는 경우에는 설정된 항목별로 “Enable” 버튼이 표시될 것입니다. “Automatic Shared Memory Management” 옆에 있는 “Enable” 버튼을 눌러 Automatic Shared Memory Management 기능을 활성화합니다. 나머지 작업은 Enterprise Manager에 의해 자동 수행됩니다.
설정 작업을 마친 후 각 풀의 크기를 확인하려면 아래와 같이 실행합니다:
SQL> select current_size from v$buffer_pool;
CURRENT_SIZE
------------
340
SQL> select pool, sum(bytes)/1024/1024 Mbytes from v$sgastat group by pool;
POOL MBYTES
------------ ----------
java pool 4
large pool 4
shared pool 148
설정 작업을 마친 후 각 풀의 크기를 확인하려면 아래와 같이 실행합니다:
그림 1: 초기 할당 결과
이제 오라클에서 사용 가능한 메모리 크기가 500MB에서 300MB로 줄었다고 가정해 봅시다. 먼저 SGA 영역을 위해 설정된 target size를 변경해야 할 것입니다:
alter system set sga_target = 300M scope=both;
다시 메모리 할당 현황을 점검하면 아래와 같은 결과를 확인할 수 있습니다:
SQL> select current_size from v$buffer_pool;
CURRENT_SIZE
------------
244
SQL> select pool, sum(bytes)/1024/1024 Mbytes from v$sgastat group by pool;
POOL MBYTES
------------ ----------
java pool 4
large pool 4
shared pool 44
전체 사용 공간은 240+4+4+44 = 296MB로, 전체 용량(300MB)와 거의 일치합니다. SGA_TARGET을 변경한 후의 메모리 할당 내역이 그림 2와 같습니다.
그림 2: SGA size를 300MB로 변경한 후의 할당 결과
풀의 크기는 다이내믹하게 조정됩니다. 워크로드가 증가하면 그에 비례하여 풀의 크기도 증가하고, 다른 풀에 관련된 워크로드가 증가하는 경우에는 줄어들기도 합니다. 이러한 확장/수축 과정은 DBA의 개입 없이 자동적으로 수행됩니다. 앞부분에 설명한 예에서 대량의 large pool을 사용하는 RMAN 작업이 시작되는 경우, large pool은 자동적으로 4MB에서 40MB로 증가합니다. 그림 3에서 보여지는 것처럼, 추가로 필요한 36MB는 db block buffer에서 가져오고, 결과적으로 db block buffer는 줄어들 것입니다.
그림 3: large pool의 수요가 증가하는 경우의 자동 할당 결과
변경되는 풀의 크기는 워크로드에 따라 달라집니다. SGA의 전체 크기도 언제나 SGA_TARGET에서 지정한 최대치를 넘지 않으므로, 실제 메모리보다 많은 영역을 할당하여 과도한 페이징과 스와핑을 발생시키는 문제를 방지할 수 있습니다. SGA_MAX_SIZE 매개변수를 조정하면 SGA_TARGET의 값을 가능한 최대치인 SGA_MAX_SIZE까지 다이내믹하게 증가시킬 수 있습니다.
자동 튜닝을 지원하지 않는 Pool.
SGA에서 관리되는 풀 중 일부는 다이내믹한 변경을 허용하지 않으며, 따라서 명시적으로 설정되어야 합니다. 특히 비표준 블록 사이즈를 갖는 buffer pool과 KEEP / RECYCLE 풀의 경우가 그러합니다. 데이타베이스의 블록 사이즈가 8K인 환경에서, 2K, 4K, 16K, 32K 등의 블록 사이즈를 갖는 풀들을 구성하려면, 수작업으로 설정하는 방법 밖에 없습니다. 이렇게 설정된 풀의 크기는 고정된 값을 유지하며, 부하 수준에 따라 확장되거나 수축되지 않습니다. KEEP/RECYCLE 풀을 구성하는 경우에도 마찬가지입니다. log buffer 역시 다이내믹하게 조정되지 않습니다. 또한 log_buffer 매개변수를 통해 설정된 값은 고정된 값을 유지합니다. (10g에서는 “Streams pool”이라는 새로운 유형의 풀을 지원합니다. 이 풀은 streams_pool_size 매개변수를 통해 설정되며, 역시 자동 메모리 튜닝을 지원하지 않습니다.)
여기서 한 가지 의문이 생깁니다. 비표준 블록 사이즈를 갖는 풀을 설정하는 경우, 다른 풀들에 관련한 메모리 자동 관리 기능에는 어떤 영향이 있을까요?
자동 튜닝이 불가능한 매개변수(예: db_2K_cache_size)를 설정하는 경우, 데이타베이스는 SGA_TARGET에서 이 매개변수의 값을 차감한 후 남은 공간을 활용하여 자동 메모리 관리를 수행합니다. 예를 들어, 아래와 같이 설정된 경우를 가정해 봅시다:
sga_target = 500M
db_2k_cache_size = 50M
다른 풀 관련 매개변수는 설정되어 있지 않은 것으로 가정합니다. 이 경우, 2KB buffer pool에 할당된 50MB를 제외한 450MB가 default block size buffer pool(db_cache_size), shared pool, Java pool, large pool 등의 자동 관리에 이용됩니다. 자동 튜닝이 불가능한 매개변수에 변경이 발생하는 경우, 자동 튜닝에 사용되는 메모리 크기 역시 변경됩니다. 예를 들어, db_2K_cache_size의 값을 50MB에서 100MB로 변경하면, shared pool, large pool, default buffer pool에 사용되는 메모리 크기는 450MB에서 400MB로 자동 변경됩니다 (그림 4 참조).
그림 4: 자동 튜닝을 지원하지 않는 버퍼 매개변수를 변경한 경우
하지만 메모리가 충분하거나, 위에서 언급한 위험요소를 걱정할 필요가 없다면 Automatic Memory Management 기능을 사용하지 않아도 무방합니다. 이 경우 SGA_TARGET 매개변수를 정의하지 않거나 그 값을 (ALTER SYSTEM 명령을 통해서, 또는 매개변수 파일 편집을 통해서) 0으로 설정하면 됩니다. SGA_TARGET이 0으로 설정되면, 각 풀의 현재 크기가 매개변수 값으로 자동 설정됩니다.
Enterprise Manager의 활용
Enterprise Manager 10g을 이용해서 이 매개변수들을 조작할 수도 있습니다. 데이타베이스 홈 페이지에서 “Memory Parameters”를 클릭하면 그림 5와 같은 화면이 표시됩니다.
Enterprise Manager 10g을 이용해서 이 매개변수들을 조작할 수도 있습니다. 데이타베이스 홈 페이지에서 “Memory Parameters”를 클릭하면 그림 5와 같은 화면이 표시됩니다.
붉은색 원으로 표시된 항목을 참고하시기 바랍니다. 데이타베이스는 Automatic Shared Memory Management 모드로 동작하고 있으며 전체 메모리 크기는 564MB로, SGA_TARGET 매개변수에 설정된 값과 동일합니다. 이 화면에서 설정값을 수정하고 Apply 버튼을 누르면 매개변수 값이 자동으로 조정됩니다.
각 풀의 최소 메모리 크기 지정
SGA_TARGET을 600MB로 설정하고 Automatic Shared Memory Management를 이용하는 경우를 가정해 봅시다. 자동 설정된 풀의 크기가 아래와 같습니다:
풀 사이즈 (MB)
Buffer 404
Java 4
Large 4
Shared 148
Java pool과 large pool의 크기(각각 4MB)가 너무 작다고 판단되는 경우, 각 영역의 최소값을 (8MB, 16MB 등으로) 설정하여 온라인 상태에서 적용할 수 있습니다. 아래와 같이 ALTER SYSTEM 명령을 통해 최소값을 명시하면 그 결과가 다이내믹하게 적용됩니다:
alter system set large_pool_size = 16M;
alter system set java_pool_size = 8M;
이제 풀의 크기를 다시 조회하면 아래와 같이 달라진 것을 확인할 수 있습니다:
SQL> select pool, sum(bytes)/1024/1024 Mbytes from v$sgastat group by pool;
POOL MBYTES
------------ ----------
java pool 8
large pool 16
shared pool 148
SQL> select current_size from v$buffer_pool;
CURRENT_SIZE
------------
388
재할당된 풀의 메모리 크기가 아래와 같습니다:
풀 사이즈 (MB)
Buffer 388
Java 8
Large 16
Shared 148
Java pool과 large pool은 각각 8MB, 16MB로 재조정되었고, 전체 SGA의 크기는 600MB 이하를 유지하고 있습니다 (buffer pool의 크기가 404MB에서 388MB로 감소했습니다). 물론 Automatic Shared Memory Management는 여전히 동작하고 있습니다. 방금 전에 설정한 값은 풀의 최소 크기를 정의한 것이며, 이제 Java pool과 large pool은 8MB, 16MB 이하의 크기로 줄어들지 않을 것입니다.
결론
Oracle SGA에서 관리되는 각 풀의 메모리 요구량은 시스템 상황에 따라 끊임없이 변화합니다. Oracle Database 10g의 Automatic Shared Memory Management 기능은 필요한 영역에 자원을 다이내믹하게 할당함으로써, 시스템 메모리 자원을 보다 효율적으로 이용할 수 있게 합니다. 이처럼 메모리 관리를 효율화함으로써 메모리 요구량을 줄이고, 하드웨어 비용을 절감할 수도 있습니다.
제공 : DB포탈사이트 DBguide.net
Oracle Database 10g: DBA를 위한 20가지 주요 기능 - 18번째
ADDM과 SQL Tuning Advisor
이제 Oracle Database가 직접 제공하는 SQL 튜닝 서비스를 활용해 보십시오! SQL Profile를 이용하여 쿼리 성능을 향상시키고 ADDM을 통해 일반적인 성능 문제를 쉽고 빠르게 해결하는 방법을 배워보십시오.
오늘은 조용한 하루입니다. 데이타베이스에도 문제가 없어 보입니다. DBA는 긴장을 풉니다. 미루어 두었던 RMAN 매개변수, 블록 사이즈 점검 작업을 하기에 좋은 날입니다.
갑자기 개발자 한 명이 DBA의 자리로 달려 옵니다. 그가 사용하는 SQL 쿼리의 성능이 저하되었습니다. 그가 말합니다. “가능한 한 빨리 해결해 주셨으면 좋겠습니다.”
어쩌면 긴장을 너무 빨리 풀었는지도 모릅니다. 데이타베이스의 성능과 보안을 향상시키려는 기존의 계획은 뒤로 밀리고, 오늘도 급한 불을 끄기 위해 하루를 보내야 합니다.
DBA는 반복적인 잡무에서 벗어나 좀 더 전략적인 과제에 집중할 수 있기를 원합니다. 그렇다면 반복적인 업무를 대신해 줄 조수를 하나 고용하면 좋지 않을까요?
Oracle Database 10g에 추가된 Automatic Database Diagnostic Monitor(ADDM)이 바로 이런 역할을 해 줍니다. ADDM은 데이타베이스 성능 통계에 대한 철저한 분석을 통해 성능 병목을 확인하고, SQL 구문에 대한 분석을 통해 성능 향상을 위한 조언을 제공합니다. 또 SQL Tuning Advisor와 연동된 기능을 제공하기도 합니다. 이번 연재에서는, ADDM을 이용한 성능 향상 방안에 대해 설명합니다.
Automatic Database Diagnostic Monitor (ADDM)
제 6 주 연재에서, Automatic Workload Repository (AWR)에 대해 설명한 바 있습니다. AWR은 데이타베이스로부터 상세한 성능 관련 지표를 주기적으로 수집하여 저장합니다. 스냅샷 생성 작업이 완료될 때마다, ADDM이 호출되어 서로 다른 스냅샷의 데이타와 성능 지표를 비교 분석하고 성능 향상을 위한 조언을 제공합니다. 문제가 발견된 후, ADDM은 다른 어드바이저 툴(SQL Tuning Advisor 등)을 호출하여 해결 방법을 찾아내기도 합니다.
예를 통해 ADDM의 기능을 설명해 보도록 하겠습니다. 원인이 확인되지 않은 성능 문제에 대한 진단 작업에 착수한 경우를 가정해 봅시다. DBA는 문제가 되는 SQL 구문이 무엇인지 확인해 둔 상태입니다. 그러나 실제 환경에서는 이와 같은 유용한 단서도 모를 경우도 있습니다.
10g에서 진단 작업을 수행하는 과정에서, drill-down분석을 위해 적절한 시간 간격을 두고 생성된 스냅샷들을 선택할 수 있습니다. Enterprise Manager 10g의 데이타베이스 홈 페이지에서 “Advisor Central”을 선택하고 “ADDM”을 클릭하면 그림 1과 같은 화면이 표시됩니다.
그림 1: ADDM 태스크의 생성
이 화면에서 ADDM을 이용한 분석 작업을 생성할 수 있습니다. 성능 문제가 오후 11시에 발생했음을 알고 있는 DBA는, “Period Start”와 “Period End”의 값을 설정하여 해당 시간 대의 스냅샷들을 선택합니다. (붉은색 원으로 표시된) 카메라 모양의 아이콘을 클릭하여 스냅샷 시작 시간과 종료 시간을 지정할 수도 있습니다. 시간대를 설정하고 난 뒤 “OK” 버튼을 누르면 그림 2와 같은 화면이 표시됩니다.
그림 2: ADDM 분석 결과
ADDM은 해당 시간대에 관련한 두 가지 성능 문제를 발견해 냈습니다. 일부 SQL 구문이 지나치게 많은 CPU 시간을 사용하고 있으며, 이로 인해 데이타베이스의 성능이 전체적으로 저하되었습니다. 발견된 내용을 근거로, ADDM은 해당 구문에 대한 SQL 튜닝을 수행할 것을 권고하고 있습니다.
각각의 문제 항목을 클릭하면 그림 3에서 보여지는 것과 같은 상세 정보를 얻을 수 있습니다.
그림 3: ADDM 분석 결과 상세 정보
위 화면에서 문제의 원인이 된 SQL 구문을 확인할 수 있습니다. ADDM은 SQL Tuning Advisor를 이용해 이 SQL 구문에 대한 분석 작업을 수행할 것을 권고하고 있습니다. “Run Advisor Now” 버튼을 클릭하면 SQL Tuning Advisor가 호출되어 분석 작업을 시작합니다.
그림 2 화면의 “View Report” 버튼을 참고하시기 바랍니다. 개별 웹 페이지 별로 권고 사항을 제시하는 것과 별도로, ADDM은 전체 분석 결과에 대한 텍스트 리포트를 생성합니다. Listing 1에서 생성된 텍스트 리포트의 내용을 확인하실 수 있습니다. 텍스트 리포트는 문제가 되는 SQL 구문과 그 hash value 등의 상세한 정보를 제공합니다. 또 텍스트 리포트의 SQL ID 정보를 이용하여 SQL Tuning Advisor 또는 커맨드 라인을 통한 분석 작업을 수행할 수 있습니다.
ADDM은 AWR 스냅샷이 생성될 때마다 호출되고, 가장 최근의 스냅샷과의 비교를 통해 권고사항을 제시합니다. 따라서 비교해야 할 두 스냅샷이 서로 인접한 경우에는 (이미 보고서가 생성되어 있으므로) 별도로 ADDM 태스크를 실행할 필요가 없으며, 스냅샷이 인접해 있지 않은 경우에만 ADDM 태스크를 실행할 필요가 있습니다.
ADDM의 기능이 단순히 SQL 구문의 분석에 한정되지 않는다는 사실을 명심하시기 바랍니다. 과거 연재에서 확인한 것과 같이, ADDM은 메모리 관리, 세그먼트 관리, redu/undo 등의 영역에 대한 다양한 분석 기능을 제공합니다. 제한된 지면을 통해 ADDM의 기능을 모두 설명하는 것은 어차피 불가능하므로, 지금부터는 SQL Tuning Advisor에 초점을 맞추어 설명을 진행하도록 하겠습니다.
SQL Tuning Advisor를 이용한 Access분석
오라클 데이타베이스의 옵티마이저(optimizer)는 가능한 액세스 경로를 여럿 생성한 뒤, 오브젝트 통계정보를 기준으로 가장 적은 비용이 드는 하나를 선택하는 방식으로 runtime optimization을 수행합니다. 하지만 옵티마이저는 (시간의 제약을 받는 만큼) SQL 구문의 튜닝이 필요한지, 통계가 정확한지, 새로운 인덱스를 생성해야 하는지 등의 여부를 판단하지 않습니다. 반면 SQL Tuning Advisor는 일종의 “전문가 시스템”과 같은 역할을 합니다. 옵티마이저가 “현재 가능한 대안 중 최적의 결과를 얻을 수 있는 것은 무엇인가?”라는 질문에 대한 답변을 제공한다면, SQL Tuning Advisor는 “사용자의 요구사항을 기반으로 고려했을 때, 성능을 향상시키기 위해 할 수 있는 일이 무엇인가?”라는 질문의 답을 제공합니다.
이러한 “전문가 시스템”으로서의 작업은 CPU 등의 자원을 많이 소모합니다. 이러한 이유로 SQL Tuning Advisor는 데이타베이스가 Tuning Mode로 설정된 경우에만 SQL 구문에 대한 분석작업을 수행합니다. Tuning Mode는 튜닝 태스크를 생성하는 과정에서 SCOPE 및 TIME 매개변수를 설정함으로써 지정됩니다. 사용자에 대한 영향을 최소화하려면 데이타베이스 활동이 적은 시간대를 선택하여 Tuning Mode를 사용하는 것이 바람직합니다.
이제 예를 통해 설명하도록 하겠습니다. 문제가 되는 SQL 구문이 아래와 같습니다:
select account_no from accounts where old_account_no = 11
실제로 튜닝하기 어렵지 않은 구문이지만, 이해를 돕기 위해 간단한 구문을 사용하였습니다. 어드바이저는 Enterprise Manager 또는 커맨드 라인을 통해 실행할 수 있습니다.
먼저, 커맨드 라인을 이용하는 방법을 알아봅시다. 아래와 같은 방법으로 dbms_sqltune 패키지를 호출하고 어드바이저를 실행합니다.
declare
l_task_id varchar2(20);
l_sql varchar2(2000);
begin
l_sql := 'select account_no from accounts where old_account_no = 11';
dbms_sqltune.drop_tuning_task ('FOLIO_COUNT');
l_task_id := dbms_sqltune.create_tuning_task (
sql_text => l_sql,
user_name => 'ARUP',
scope => 'COMPREHENSIVE',
time_limit => 120,
task_name => 'FOLIO_COUNT'
);
dbms_sqltune.execute_tuning_task ('FOLIO_COUNT');
end;
/
위 패키지는 FOLIO_COUNT라는 이름의 튜닝 태스크를 생성하고 실행합니다. 다음에는 아래와 같이 입력하여 태스크 실행 결과를 확인합니다.
set serveroutput on size 999999
set long 999999
select dbms_sqltune.report_tuning_task ('FOLIO_COUNT') from dual;
실행 결과는 Listing 2에서 확인할 수 있습니다. 어드바이저가 제공하는 권고 사항을 자세히 살펴보시기 바랍니다. 이 경우, 어드바이저는 OLD_ACCOUNT_NO 컬럼에 인덱스를 생성할 것을 권고하고 있습니다. 그 뿐 아니라, 인덱스가 생성된 경우의 비용을 계산하고, 기대되는 성능 향상 효과를 구체적인 형태로 제시하고 있습니다. 예로 든 구문 자체가 단순한 만큼, 굳이 어드바이저를 이용해서 이런 결과를 얻을 필요는 없었을 것입니다. 하지만 보다 복잡한 형태의 쿼리에 성능 문제가 발생한 경우라면, 어드바이저를 통해 수작업으로는 불가능한 성능 개선 효과를 얻을 수 있습니다.
중급 레벨 튜닝: Query Restructuring
조금 더 복잡한 쿼리를 분석하는 경우를 생각해 봅시다:
select account_no from accounts a
where account_name = 'HARRY'
and sub_account_name not in
( select account_name from accounts
where account_no = a.old_account_no and status is not null);
어드바이저는 다음과 같은 권고 사항을 제시하였습니다:
1- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
The optimizer could not unnest the subquery at line ID 1 of the execution
plan.
Recommendation
--------------
Consider replacing "NOT IN" with "NOT EXISTS" or ensure that columns used
on both sides of the "NOT IN" operator are declared "NOT NULL" by adding
either "NOT NULL" constraints or "IS NOT NULL" predicates.
Rationale
---------
A "FILTER" operation can be very expensive because it evaluates the
subquery for each row in the parent query. The subquery, when unnested can
drastically improve the execution time because the "FILTER" operation is
converted into a join. Be aware that "NOT IN" and "NOT EXISTS" might
produce different results for "NULL" values.
이번에는 인덱스 생성과 같은 오브젝트 구조 변경을 제안하는 대신, NOT IN 대신 NOT EXIST를 사용하도록 쿼리를 수정할 것을 제안하고 있습니다. 어드바이저는 근본적인 원인을 통해 권장 내용의 근거를 설명하고, 그 결정을 DBA의 판단에 맡깁니다.
고급 튜닝: SQL Profiles
옵티마이저는 쿼리가 사용하는 오브젝트 통계정보(object statistics)를 점검한 뒤 가장 적은 비용을 사용하는 경로를 선택함으로써 execution plan을 생성합니다. 쿼리가 두 개 이상의 테이블을 참조하는 경우, 옵티마이저는 관련된 테이블의 통계를 모두 점검한 뒤 가장 적은 비용을 사용하는 경로를 선택하지만, 테이블 간의 관계에 대해서는 아무 것도 이해하지 못합니다. 예를 들어, DELINQUENT(연체) 상태가 $1,000 이하의 balance를 갖는 한 account가 있다는 경우를 가정해 봅시다. ACCOUNTS 테이블과 BALANCES 테이블을 join하는 쿼리에 status가 DELINQUENT인 데이타만을 필터링하는 조건을 추가함으로써, 보다 적은 수의 결과를 얻을 수 있을 것입니다. 옵티마이저는 이러한 테이블 간의 복잡한 관계를 이해하지 못합니다. 하지만 어드바이저의 경우는 다릅니다. 어드바이저는 데이터로부터 이들 관계정보를 “수집”해 낸 뒤 SQL Profile의 형태로 이를 저장합니다. 이제 SQL Profile에 접근할 수 있게 된 옵티마이저는 테이블의 데이타 분포뿐 아니라 데이타 간의 관계까지도 이해할 수 있게 됩니다. 이렇게 추가로 제공되는 정보를 활용하면 보다 뛰어난 수준의 execution plan을 생성하고 성능을 향상시킬 수 있습니다.
SQL Profile을 이용하면, 쿼리 힌트를 코드에 삽입하는 형태의 SQL 구문 튜닝이 불필요해집니다. 따라서 SQL Tuning Advisor를 이용하여, 코드에 전혀 손을 대지 않고도 패키지 애플리케이션을 튜닝할 수 있습니다.
오브젝트 통계가 하나 또는 그 이상의 오브젝트에 매핑되는 반면, SQL Profile은 쿼리에 매핑됩니다. 같은 테이블(ACCOUNTS와 BALANCES)을 참조하더라도 쿼리가 다르면 프로파일도 달라집니다.
SQL Tuning Advisor는 실행 과정에서 프로파일을 생성한 뒤 이를 “Accept”할 것을 권고합니다. 사용자가 “Accept”하지 않는 이상 프로파일은 구문에 반영되지 않습니다. 아래와 같이 실행하면 아무 때나 프로파일을 accept할 수 있습니다:
begin
dbms_sqltune.accept_sql_profile (
task_name => 'FOLIO_COUNT',
name => 'FOLIO_COUNT_PROFILE'
description => 'Folio Count Profile',
category => 'FOLIO_COUNT');
end;
위 명령은 어드바이저가 생성한 FOLIO_COUNT_PROFILE을 위의 예에서 설명한 FOLIO_COUNT 튜닝 태스크에 연관된 구문에 연결합니다. (DBA가 직접 SQL Profile을 생성할 수는 없으며, 어드바이저만이 SQL Profile을 생성할 수 있음을 참고하시기 바랍니다. 사용자는 SQL Profile의 사용 여부만을 선택할 뿐입니다.)
DBA_SQL_PROFILES 딕셔너리 뷰를 통해 생성된 SQL Profile을 확인할 수 있습니다. SQL_TEXT 컬럼은 프로파일이 할당된 SQL 구문을, STATUS 컬럼은 프로파일의 활성화 여부를 표시합니다. (프로파일이 특정 구문에 연결되어 있는 경우라 하더라도, 활성화하기 전에는 execution plan에 반영되지 않습니다.)
ADDM과 SQL Tuning Advisor의 활용
위에 예로 든 세 가지 경우 이외에도, SQL Tuning Advisor는 쿼리에서 사용하는 오브젝트에 통계가 누락되어 있는지의 여부를 확인해 줍니다. 요약하자면, 어드바이저는 다음과 같은 네 가지 유형의 작업을 수행합니다.
● 최적화를 위한 오브젝트들의 유효하고, 사용 가능한 통계정보를 보유하고 있는지 확인합니다.
● 성능 향상을 위해 쿼리를 재작성하는 방안을 권고합니다.
● 접근 경로를 확인하고 인덱스, MV(materialized view) 등을 추가하여 성능을 향상시킬 수 있는 방법을 조사하고 제안합니다.
● SQL Profile을 생성하고, 이를 특정 쿼리에 연결합니다.
ADDM과 SQL Tuning Advisor가 유용하게 활용되는 경우를, 다음과 같은 세 가지 시나리오를 기준으로 검토해 볼 수 있습니다.
● 사후조치적 튜닝(Reactive Tuning): 애플리케이션의 성능이 갑자기 저하됩니다. DBA는 ADDM을 사용하여 문제가 되는 SQL 구문을 확인합니다. ADDM의 권고사항에 따라 SQL Tuning Advisor를 실행하고 문제를 해결합니다.
● 사전예방적 튜닝(Proactive Tuning): 애플리케이션은 정상적으로 동작합니다. DBA는 유지보수를 위해 필요한 작업을 실행하고 쿼리를 좀 더 개선할 방법이 있는지 확인하고자 합니다. DBA는 스탠드얼론 모드로 SQL Tuning Advisor를 실행하고 가능한 대안을 검토합니다.
● 개발단계 튜닝(Development Tuning): QA 단계 또는 운영 단계보다는, 개발 단계에서 쿼리 튜닝을 수행하는 것이 상대적으로 용이합니다. 이때 어드바이저의 커맨드 라인 버전을 사용하여 개별 SQL 구문을 튜닝할 수 있습니다.
Enterprise Manager의 활용
앞의 예는 SQL Tuning Advisor를 커맨드 라인 모드에서 사용하는 방법을 기준으로 설명되었습니다. 커맨드 라인 모드는 태스크 실행을 위한 스크립트 생성이 용이하다는 장점이 있습니다. 하지만, 사용자에 의해 이미 문제가 보고된 경우라면 Enterprise Manager 10g를 이용하는 방법이 더 효과적일 수 있습니다.
(제 13주) 연재에서, 새롭게 개선된 Enterprise Manager 인터페이스에 대해 소개한 바 있습니다. 이번에는 EM을 활용하여 SQL 구문을 진단하고 튜닝하는 방법을 설명해 보겠습니다. 데이타베이스 홈 페이지 하단의 “Advisor Central” 링크를 클릭하면 다양한 어드바이저를 위한 메뉴를 제공하는 화면이 표시됩니다. 이 화면 상단의 “SQL Tuning Advisor”를 클릭합니다 (그림 4 참조).
그림 4: Enterprise Manager의 Advisor Central 화면
이제 SQL Tuning Advisor가 실행됩니다. 다음 화면에서 "Top SQL"을 클릭합니다 (그림 5 참조).
그림 5: SQL Tuning Advisor
그림 6에서 보여지는 것과 같은 화면이 표시되며, 이 화면에서 표시되는 그래프를 통해 다양한 wait class에 관련된 정보를 시간대 별로 확인할 수 있습니다.
그림 6: Top SQL 화면
그래프의 관심 영역은 회색 사각형으로 표시됩니다. CPU wait이 높게 나타나는 영역에 마우스를 드래그하여 관심영역을 조정합니다. 화면의 하단에는 해당 시간대에 수행된 SQL 구문 관련 정보가 표시됩니다 (그림 7 참조).
그림 7: SQL 구문의 Activity 정보
상단(붉은색으로 표시된 부분)에는 가장 많은 CPU 자원을 소모하는 SQL 구문들이 표시되고 있습니다. 각 구문의 SQL ID를 클릭하면 그림 8과 같은 화면이 표시됩니다.
그림 8: SQL Details 화면
위 화면을 통해 문제가 되는 SQL 구문을 확인할 수 있습니다. “Run SQL Tuning Advisor” 버튼을 클릭하면 어드바이저가 실행되고 그림 9와 같은 화면이 표시됩니다.
그림 9: SQL Tuning Advisor의 스케줄링
어드바이저 스케줄러 화면에서, 태스크의 유형과 분석의 심도를 결정할 수 있습니다. 위 화면에서는 “comprehensive” analysis를 선택하고 어드바이저를 바로 실행하도록 설정하였습니다. 어드바이저의 실행이 완료되면 그림 10과 같은 화면을 통해 어드바이저의 권고 사항을 확인할 수 있습니다.
그림 10: Advisor Recommendation 화면
EM을 이용한 SQL Tuning Advisor 실행 방법은, 앞에서 설명한 커맨드 라인 버전을 이용하는 경우와 유사합니다. 하지만, 발생된 문제를 확인하여 드릴다운을 수행하고, 권고사항을 생성한 후 이를 승인하는 전체 과정이, 실제 발생한 문제를 해결하기에 편리한 형태로 구성되어 있다는 점에서 차이가 있습니다.
결론
ADDM은 성능 지표를 자동적으로 분석하고 오라클 전문가가 구현한 베스트 프랙티스 및 방법론을 기반으로 권고사항을 제시해 주는 강력한 성능 관리 툴입니다. 이 기능을 활용하여 발생된 문제와 그 원인을 확인할 수 있을 뿐 아니라, 취해야 할 조치에 대한 조언을 얻을 수 있습니다.
제공 : DB포탈사이트 DBguide.net
Oracle Database 10g: DBA를 위한 20가지 주요 기능 - 19번째
Scheduler
dbms_job 패키지의 실행 주기 설정을 수작업으로 관리하는 것이 번거로우십니까? 이제 10g데이타베이스가 제공하는 Scheduler를 이용해 보십시오.
여러분들 중 일부는 백그라운드 데이타베이스 작업 스케줄을 설정하기 위해 dbms_job 패키지를 사용하고 있을 것입니다. 하지만 필자가 알기로는 대부분의 DBA들이 dbms_job 패키지를 사용하지 않고 있습니다.
이 패키지는 PL/SQL 코드 세그먼트만을 처리할 수 있으며, 데이타베이스 외부의 운영체제 파일 또는 실행 파일 이미지를 처리할 수 없다는 기능적 한계를 갖고 있습니다. 이 때문에 DBA들은 Unix의 cron, Windows의 AT 명령 등을 사용하여 운영 체제 레벨에서 스케줄링을 설정하는 방법을 선택합니다. 또는 그래픽 사용자 인터페이스를 제공하는 써드 파티 툴을 사용하기도 합니다.
그러나 dbms_job은 이들과 다른 장점들이 있습니다. 이 중 하나는 데이터베이스가 실행되고 있는 경우에만 활성화된다는 것입니다. 만약 데이터베이스가 다운되어 있다면 해당 Job들은 실행되지 않습니다. 데이터베이스 외부에 존재하는 툴들은 수작업을 통해 데이터베이스가 실행되고 있는지 조사되어야 하며, 복잡한 작업일 수 있습니다. Dbms_job의 다른 장점으론 데이터베이스 내부에 존재한다는 것입니다. 그러므로 SQL*Plus와 같은 유틸리티를 이용해 쉽게 접근 가능하다는 것입니다.
Oracle Database 10g Scheduler는 모든 종류의 작업을 지원하는 내장형 작업 스케줄러 유틸리티를 제공합니다. 10g Scheduler의 가장 큰 장점은, 데이타베이스에 포함된 형태로 제공되므로 추가적인 비용이 들지 않는다는 것입니다. 이번 연재에서는 Scheduler의 기능에 대해 자세히 살펴보기로 합니다.
Creating Jobs Without Programs
개념의 이해를 돕기 위해 예를 통해 설명하겠습니다. 아카이브 로그 파일을 다른 파일시스템으로 이동하기 위해 아래와 같은 이름의 셸 스크립트를 생성했다고 가정해 봅시다:
/home/arup/dbtools/move_arcs.sh
별도의 프로그램을 생성하지 않고도 작업 유형으로 “Executable”을 지정하고 스케줄링을 설정할 수 있습니다.
begin
dbms_scheduler.create_job
(
job_name => 'ARC_MOVE_2',
schedule_name => 'EVERY_30_MINS',
job_type => 'EXECUTABLE',
job_action => '/home/arup/dbtools/move_arcs.sh',
enabled => true,
comments => 'Move Archived Logs to a Different Directory'
);
end;
/
또 Schedule Name을 설정하지 않고 작업을 생성하는 것도 가능합니다.
begin
dbms_scheduler.create_job
(
job_name => 'ARC_MOVE_3',
job_type => 'EXECUTABLE',
job_action => '/home/arup/dbtools/move_arcs.sh',
repeat_interval => 'FREQ=MINUTELY; INTERVAL=30',
enabled => true,
comments => 'Move Archived Logs to a Different Directory'
);
end;
/
앞의 두 가지 예를 통해 dbms_job과 비교했을 때의 Scheduler의 장점을 확인할 수 있습니다. Scheduler를 이용하면 PL/SQL 프로그램뿐 아니라 OS 유틸리티와 프로그램을 실행하는 것이 가능합니다. 이러한 기능을 활용하여 범용적인 데이타베이스 작업 관리 환경을 구현할 수 있습니다. 또 Scheduler는 자연 언어(natural language)를 사용하여 실행 주기를 정의할 수 있다는 매우 중요한 이점을 제공합니다. 스케줄을 매 30분 단위로 실행하고자 하는 경우, (PL/SQL 문법 대신) 자연 언어 형식의 표현을 사용하여 REPEAT_INTERVAL 매개변수를 아래와 같이 정의할 수 있습니다:
'FREQ=MINUTELY; INTERVAL=30'
좀 더 복잡한 예를 들어 설명해 보겠습니다. 운영 중인 애플리케이션이 오전 7시에서 오후 3시까지의 시간대에 집중적으로 사용된다고 가정해 봅시다. Statspack을 실행하면 월요일~금요일 오전 7시 ~ 오후 3시의 시스템 통계를 수집할 수 있습니다. DBMS_JOB.SUBMIT을 사용하여 작업을 생성하는 경우라면, NEXT_DATE 매개변수는 아래와 같이 정의됩니다:
DECODE
(
SIGN
(
15 - TO_CHAR(SYSDATE,'HH24')
),
1,
TRUNC(SYSDATE)+15/24,
TRUNC
(
SYSDATE +
DECODE
(
TO_CHAR(SYSDATE,'D'), 6, 3, 1
)
)
+7/24
)
위의 코드가 이해하기 쉽습니까? 전혀 그렇지 않습니다.
이번에는 DBMS_SCHEDULER를 이용하는 방법을 살펴 보겠습니다. REPEAT_INTERVAL 매개변수는 아래와 같이 간단하게 정의됩니다:
'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=7,15'
이 매개변수를 이용해 실행 주기를 다양한 형태로 설정할 수 있습니다. 몇 가지 예가 아래와 같습니다:
● 매월 마지막 일요일:
FREQ=MONTHLY; BYDAY=-1SUN
● 매월 세 번째 금요일:
FREQ=MONTHLY; BYDAY=3FRI
● 매월 뒤에서 두 번째 금요일
FREQ=MONTHLY; BYDAY=-2FRI
마이너스(-) 기호는 숫자가 뒤에서부터 계산된다는 의미입니다.
설정된 실행 주기가 올바른지 확인하려면 어떻게 해야 할까요? 캘린더 문자열을 이용해서 날짜를 미리 확인할 수 있다면 편리하지 않을까요? EVALUATE_CALENDAR_STRING 프로시저를 이용하여 실행 예정 시각을 미리 검토할 수 있습니다. 위의 예 ? 월~금7:00 AM ~ 3:00 PM Statspack 실행 ? 에서 설정된 실행 주기를 확인하는 방법이 아래와 같습니다:
set serveroutput on size 999999
declare
L_start_date TIMESTAMP;
l_next_date TIMESTAMP;
l_return_date TIMESTAMP;
begin
l_start_date := trunc(SYSTIMESTAMP);
l_return_date := l_start_date;
for ctr in 1..10 loop
dbms_scheduler.evaluate_calendar_string(
'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=7,15',
l_start_date, l_return_date, l_next_date
);
dbms_output.put_line('Next Run on: ' ||
to_char(l_next_date,'mm/dd/yyyy hh24:mi:ss')
);
l_return_date := l_next_date;
end loop;
end;
/
실행 결과는 다음과 같습니다:
Next Run on: 03/22/2004 07:00:00
Next Run on: 03/22/2004 15:00:00
Next Run on: 03/23/2004 07:00:00
Next Run on: 03/23/2004 15:00:00
Next Run on: 03/24/2004 07:00:00
Next Run on: 03/24/2004 15:00:00
Next Run on: 03/25/2004 07:00:00
Next Run on: 03/25/2004 15:00:00
Next Run on: 03/26/2004 07:00:00
Next Run on: 03/26/2004 15:00:00
위 결과로 미루어 설정된 내용에 문제가 없음을 확인할 수 있습니다.
작업과 프로그램의 연계
위의 사례는 특정 프로그램과 연결되지 않은 작업을 생성하는 방법을 설명하고 있습니다. 이번에는 특정 OS 유틸리티를 사용하는 프로그램을 생성하고, 실행 주기를 정의한 스케줄을 설정한 뒤, 이 두 가지를 조합하여 새로운 작업을 생성하는 방법을 설명합니다.
먼저 작업 내에서 프로그램을 사용한다는 사실을 데이타베이스가 인지하도록 해야 합니다. 이 프로그램을 생성하려면, CREATE JOB 권한을 갖고 있어야 합니다.
begin
dbms_scheduler.create_program
(
program_name => 'MOVE_ARCS',
program_type => 'EXECUTABLE',
program_action => '/home/arup/dbtools/move_arcs.sh',
enabled => TRUE,
comments => 'Moving Archived Logs to Staging Directory'
);
end;
/
이제 named program unit을 생성하여, 그 유형을 “executable”로 지정하고 실행될 program unit의 이름을 정의하였습니다.
다음으로, 매 30분 간격으로 실행되는 EVERY_30_MIN이라는 이름의 스케줄을 생성합니다:
begin
dbms_scheduler.create_schedule
(
schedule_name => 'EVERY_30_MINS',
repeat_interval => 'FREQ=MINUTELY; INTERVAL=30',
comments => 'Every 30-mins'
);
end;
/
프로그램과 스케줄을 생성한 뒤, 이 두 가지를 연관시킴으로써 새로운 작업을 생성합니다.
begin
dbms_scheduler.create_job
(
job_name => 'ARC_MOVE',
program_name => 'MOVE_ARCS',
schedule_name => 'EVERY_30_MINS',
comments => 'Move Archived Logs to a Different Directory',
enabled => TRUE
);
end;
/
이제 매 30분마다 move_arcs.sh 셸 스크립트를 실행하는 작업이 생성되었습니다. 이 스케줄은 데이타베이스 내부의 Scheduler 기능을 통해 관리되며, 따라서 cron 또는 AT 유틸리티를 사용할 필요가 없습니다.
Classes, Plans, and Windows
작업 스케줄링 시스템에 효과적으로 활용하려면 작업의 우선순위 지정이 가능해야 합니다. 예를 들어 OLTP 워크로드가 실행 중인 시간대에 통계 수집 작업이 갑자기 실행되어 성능을 저하시킬 수 있습니다. 통계 수집 작업이 OLTP 성능에 영향을 미치지 않도록 하기 위해, Scheduler가 제공하는 job classes, resource plans, and Scheduler Windows기능을 활용할 수 있습니다.
Job class는 할당된 자원을 공유하는 “resource consumer group”으로 매핑됩니다. Job class를 생성하기 위해, 먼저 OLTP_GROUP이라는 이름의 resource consumer group을 정의해 보겠습니다.
begin
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.create_consumer_group (
consumer_group => 'oltp_group',
comment => 'OLTP Activity Group'
);
dbms_resource_manager.submit_pending_area();
end;
/
다음에는 resource plan을 생성합니다.
begin
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.create_plan
('OLTP_PLAN', 'OLTP Database Activity Plan');
dbms_resource_manager.create_plan_directive(
plan => 'OLTP_PLAN',
group_or_subplan => 'OLTP_GROUP',
comment => 'This is the OLTP Plan',
cpu_p1 => 80, cpu_p2 => NULL, cpu_p3 => NULL, cpu_p4 => NULL,
cpu_p5 => NULL, cpu_p6 => NULL, cpu_p7 => NULL, cpu_p8 => NULL,
parallel_degree_limit_p1 => 4,
active_sess_pool_p1 => NULL,
queueing_p1 => NULL,
switch_group => 'OTHER_GROUPS',
switch_time => 10,
switch_estimate => true,
max_est_exec_time => 10,
undo_pool => 500,
max_idle_time => NULL,
max_idle_blocker_time => NULL,
switch_time_in_call => NULL
);
dbms_resource_manager.create_plan_directive(
plan => 'OLTP_PLAN',
group_or_subplan => 'OTHER_GROUPS',
comment => NULL,
cpu_p1 => 20, cpu_p2 => NULL, cpu_p3 => NULL, cpu_p4 => NULL,
cpu_p5 => NULL, cpu_p6 => NULL, cpu_p7 => NULL, cpu_p8 => NULL,
parallel_degree_limit_p1 => 0,
active_sess_pool_p1 => 0,
queueing_p1 => 0,
switch_group => NULL,
switch_time => NULL,
switch_estimate => false,
max_est_exec_time => 0,
undo_pool => 10,
max_idle_time => NULL,
max_idle_blocker_time => NULL,
switch_time_in_call => NULL
);
dbms_resource_manager.submit_pending_area();
end;
/
마지막으로 앞에서 생성된 resource consumer group을 이용해 job class를 생성합니다.
begin
dbms_scheduler.create_job_class(
job_class_name => 'OLTP_JOBS',
logging_level => DBMS_SCHEDULER.LOGGING_FULL,
log_history => 45,
resource_consumer_group => 'OLTP_GROUP',
comments => 'OLTP Related Jobs'
);
end;
/
이 프로시저에서 사용된 매개변수들을 설명해 보겠습니다. LOGGING_LEVEL 매개변수는 해당 job class를 위해 얼마나 많은 로그 데이타를 기록할 것인지 정의하는데 사용됩니다. LOGGING_FULL은 job class에 포함된 작업의 모든 활동(생성, 삭제, 실행, 변경 등)을 로그에 기록함을 의미합니다. 로그는 DBA_SCHEDULER_JOB_LOG 뷰를 통해 확인할 수 있으며, LOG_HISTORY 매개변수에 저장된 대로 45일간 보관됩니다 (디폴트 값은 30일입니다). 이 class와 연관된 resource_consumer_group 도 정의되어 있습니다. DBA_SCHEDULER_JOB_CLASSES 뷰를 통해 정의된 job class들을 확인할 수 있습니다.
작업을 생성하는 과정에서, 필요한 경우 해당 작업을 job class에 할당할 수 있습니다. 예를 들어 collect_opt_stats() 저장 프로시저를 실행하여 옵티마이저 통계를 수집하는 COLLECT_STATS 작업을 생성하면서, 아래와 같이 job class를 할당할 수 있습니다.
begin
dbms_scheduler.create_job
(
job_name => 'COLLECT_STATS',
job_type => 'STORED_PROCEDURE',
job_action => 'collect_opt_stats',
job_class => 'OLTP_JOBS',
repeat_interval => 'FREQ=WEEKLY; INTERVAL=1',
enabled => true,
comments => 'Collect Optimizer Stats'
);
end;
/
위 명령을 실행하면 생성된 작업이 OLTP_JOBS 클래스에 할당됩니다. OLTP_JOBS 클래스에 적용되는 OLTP_GROUP resource plan을 통해 프로세스에 할당되는 CPU 자원, 다른 그룹으로 전환되기 전에 최대 실행 가능한 횟수, 전환되는 그룹 등을 설정할 수 있습니다. 동일한 job class에 할당된 작업은 동일한 resource plan의 적용을 받습니다. 이 기능을 활용하면 서로 다른 유형의 작업이 같은 리소스를 두고 경합을 벌이는 상황을 방지할 수 있습니다.
Scheduler Window는 특정 resource plan이 사용되는 시간대를 의미합니다. 예를 들어, 실시간 의사결정 작업에 관련한 업데이트 배치 작업이 주간에는 높은 우선순위를 갖는 반면 야간에는 낮은 우선순위를 갖는다고 가정해 봅시다. 이 경우 시간대별로 다른 resource plan을 정의하고, 정의된 내용을 Scheduler Window를 이용해 적용할 수 있습니다.
모니터링
실행 중인 작업의 상태는 DBA_SCHEDULER_JOB_LOG 뷰를 통해 확인할 수 있습니다. 이 뷰의 STATUS 컬럼은 작업의 현재 상태를 표시하는데 사용됩니다. 만일 STATUS 컬럼이 FAILED로 표시된다면, DBA_SCHEDULER_JOB_RUNS_DETAILS 뷰를 통해 그 원인을 확인할 수 있습니다.
관리
지금까지 여러 가지 유형의 오브젝트(program, schedule, job, job class 등)를 생성하는 방법에 대해 설명했습니다. 이렇게 생성된 오브젝트를 변경할 필요가 있다면, DBMS_SCHEDULER 패키지가 제공하는 API를 사용하면 됩니다.
Enterprise Manager 10g 홈 페이지에서 Administration 링크를 클릭하면, 그림 1과 같이 Administration 화면이 표시됩니다. Scheduler와 관련된 작업은 우측 하단의 “Scheduler” 항목에 표시됩니다 (그림의 붉은색 원 참조).
그림 1: Administration 페이지
이 페이지에서 제공되는 하이퍼링크를 활용하면 작업의 생성, 삭제, 관리와 같은 Scheduler 관련 작업을 쉽게 수행할 수 있습니다. 몇 가지 예를 들어 설명해 보겠습니다. 이미 작업을 생성해 둔 상태이므로, Job 탭을 클릭하면 그림 2와 같은 화면이 표시될 것입니다.
그림 2: 작업 스케줄의 확인
COLLECT_STATS 작업을 클릭하여 그 속성을 변경해 보도록 합시다. “Name” 필드를 클릭하면 그림 3과 같은 화면이 표시됩니다.
그림 3: 작업 매개변수
지금까지 확인한 것처럼, EM을 이용하면 작업, 스케줄, 옵션 등에 관련한 매개변수를 수정할 수 있습니다. 변경 작업을 완료한 뒤 “Apply” 버튼을 누르면 변경사항은 영구적으로 적용됩니다. “Apply” 버튼을 누르기 전에 “Show SQL” 버튼을 눌러 실행되는 SQL 구문을 확인하는 것도 가능합니다. 또 SQL 구문을 스크립트에 저장하여 나중에 실행하거나, 템플릿 용도로 활용할 수도 있습니다.
제공 : DB포탈사이트 DBguide.net
Oracle Database 10g: DBA를 위한 20가지 주요 기능 - 20번째
Arup Nanda
그 밖의 유용한 기능
지금까지 소개한 기능 이외에도, Oracle Database 10g는 통계 자동 수집, undo 데이타 보존기간(undo retention)의 “guarantee”, 더욱 쉽고 강력해진 암호화 패키지 등의 유용한 기능을 제공합니다.
드디어 우리의 긴 여행도 막바지에 이르렀습니다. 지난 19주 동안 DBA의 작업을 쉽고 편하게 해 주는 다양한 툴, 팁, 테크닉을 소개해 드렸습니다.
지금까지 Oracle Database 10g의 특출한 기능이라 하더라도 DBA에게 많은 도움이 되지 않는다면 소개하지 않는 것을 원칙으로 했습니다. 마지막 연재도 Oracle 10g의 모든 기능을 섭렵하기에는 지면이 충분하지 않습니다. 이제 그간의 연재를 마무리하면서, 언급될 가치가 있는 Oracle 10g의 중요한 기능을 몇 가지 골라 소개하려 합니다.
자동적인 통계 수집
이미 많은 분들이 알고 계시겠지만, Oracle 10g에 오면서 마침내 Rules-Based Optimized(RBO)는 더 이상 지원되지 않는 (기능은 남아있지만) 것으로 결정되었습니다. Oracle9i Database를 사용하는 IT 조직은 Cost-Based Optimizer(CBO) 환경으로 업그레이드함으로써 계속적인 기술지원을 받을 수 있을 뿐 아니라 query rewrite, partition pruning과 같은 고급 기능을 활용할 수 있게 됩니다. 하지만 이 경우 통계(statistics)가 (정확히 말하면 통계의 부재가) 문제가 됩니다.
CBO가 최적의 실행 경로를 생성하려면 정확한 통계를 필요로 하며, DBA는 통계가 정기적으로 수집될 수 있도록 데이타베이스를 관리해야 합니다. 10g 이전의 환경에서는 (여러 가지 이유 때문에) 정확한 통계의 수집을 위한 DBA의 노력이 수포로 돌아가는 경우가 허다했습니다. 이러한 문제 때문에 CBO가 “제멋대로 동작한다”는, 다시 말해 임의적으로 실행 경로를 바꾸곤 한다는 이론이 제기되기도 했습니다.
10g는 통계의 자동적인 수집 기능을 제공하며, 따라서 이와 같은 문제를 상당부분 해결하였습니다. Oracle9i의 경우 테이블의 데이타가 얼마나 변경되었는지 확인하기 위해서는 테이블의 모니터링 옵션을 활성화하고 (ALTER TABLE... MONITORING) 해당 테이블에 대한 DBA_TAB_MODIFICATIONS 뷰를 조회해야 했습니다.
10g에서는 MONITORING 키워드가 사라진 대신, 초기화 매개변수 STATISTICS_LEVEL을 TYPICAL 또는 ALL로 설정함으로써 통계 수집을 자동화하는 기능을 제공합니다. (디폴트 값은 TYPICAL이며, 따라서 데이타베이스 설치 후 바로 통계 수집 작업이 자동 수행됩니다.) Oracle Database 10g는 사전 정의된 Scheduler (Scheduler에 대해서는 제 19 주에 설명했습니다) 작업 GATHER_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하는 환경에서도, exchanged 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 참조).
그림 1: Database Usage Statistics 페이지
그림 2: Database Usage Statistics - 기능별 드릴다운
더 쉽고 강력해진 암호화 옵션
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 함수(프로시저로도 사용됩니다)는 아래와 같은 매개변수를 사용합니다:
|
매개변수 설명 |
설명 |
|
SRC |
암호화 대상이 되는 입력 데이타입니다. RAW 데이타 타입만을 지원하며, 다른 데이타 타입의 경우 먼저 변환 과정을 거쳐야 합니다. character 변수 p_in_val에 대해 변환작업을 수행하려면 다음과 같이 실행합니다: |
|
KEY |
암호화 키를 정의합니다. 키의 길이는 사용되는 알고리즘에 따라 달라집니다. |
|
TYP |
암호화 타입과 padding 방법을 정의합니다. 예를 들어 AES 256-bit 알고리즘, Cipher Block Chaining, PKCS#5 padding을 사용하려는 경우 아래와 같이 입력합니다: typ => dbms_cryptio.encrypt_aes256 + dbms_cryptio.chain_cbc + dbms_cryptio.pad_pkcs5 |
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주 동안 소개되지 않았던 몇 가지 기능을 마지막으로 설명해 드렸습니다. 이번 연재가 여러분에게 유익했기를 바랍니다. 의견이 있으신 경우 언제든 보내주시기 바라며, 여러분이 가장 마음에 들어 하는 기능이 어떤 것이었는지 알려주시면 제게 많은 참고가 될 것입니다.
제공 : DB포탈사이트 DBguide.net
출처 : Tong - redyoon님의 DB통