http://www.oracle.com/technology/global/kr/pub/articles/schumacher_analysis.html
http://www.oracle.com/technology/global/kr/pub/articles/schumacher_analysis.html
오라클 DBA와 성능 분석 담당자들은 데이타베이스의 성능을 극대화하기 위해, 시스템과 사용자 세션에 관련한 정확한 응답시간 메트릭(metric)을 얻기 위해 고군분투해 왔습니다. DBA는 크게 두 가지 측면에서 어려움을 겪곤 합니다. 먼저, 데이타베이스 또는 사용자 세션이 "어디"에 시간을 사용했는지 확인하는 문제, 두 번째로 사용자의 실제 경험을 객관적으로 평가하는 문제가 그것입니다.
데이타베이스 내부작업 및 상호작용의 복잡성을 고려할 때, 이것은 결코 쉬운 문제가 아닙니다. 이미 오래 전에 소개된 바 있는 Oracle Wait Interface는 운영자들에게 매우 유용한 툴로 활용되어 왔지만, 시스템 또는 사용자 세션이 얼마나 효율적으로 트랜잭션 또는 쿼리를 처리하고 있는지 판단하기 위한 이상적인 환경을 제공하지는 못했습니다. 트레이스 파일을 활성화하면 필요한 수준의 상세한 정보를 얻을 수 있지만, 대규모 데이타베이스 환경을 관리하는 DBA들에게 이것은 너무 벅찬 작업입니다.
다행스러운 사실은, Oracle Database 10g 환경으로 업그레이드한 오라클 DBA들의 경우 시스템 및 세션 레벨의 응답시간 메트릭에 관련하여 보다 개선된 기능을 활용할 수 있다는 것입니다. 특히 Oracle Automatic Database Diagnostic Monitor(ADDM)를 통해 응답시간을 정밀하게 분석할 수 있을 뿐 아니라, Oracle Enterprise Manager Grid Control GUI를 통해 수집된 통계를 분석하고, 문제 영역을 진단하고, 필요한 조언을 제공하는 모든 절차를 자동 수행할 수 있습니다.
이에 더하여 Oracle Database 10g의 히스토리컬 메커니즘(historical mechanism)은, DBA로 하여금 과거 시간대의 응답시간 추이 분석을 수행함으로써 피크 트랜잭션/시스템 시간을 확인하고, 배치 사이클 및 ETL 작업 수행 시간에 악영향을 미치는 로그(rogue) 프로세스/SQL 구문을 확인할 수 있게 합니다.
이 문서는 시스템, 세션, SQL 레벨에서 사용되는 히스토리컬 메커니즘에 대해 설명하는 것을 목적으로 작성되었습니다. 그 밖의 ADDM에 관련한 정보는 Oracle 제품문서와 Arup Nanda의 "Oracle Database 10g: DBA를 위한 20가지 기능" 시리즈의 "ADDM and SQL Tuning Advisor" 편을 참고하시기 바랍니다.
시스템 레벨 응답 시간 분석
글로벌 또는 시스템 레벨에서, DBA는 다음과 같은 질문을 가장 먼저 던지곤 합니다:
- 데이타베이스의 전반적인 상태는 어떠한가? 데이타베이스의 효율성은 어떻게 정의되는가?
- 사용자가 경험하는 평균응답시간은 얼마나 되는가?
- 전체 응답시간에 가장 많은 영향을 미치는 작업은 무엇인가?
Oracle Database 10g가 소개되기 전까지, 이러한 질문에 대한 대답을 DBA가 얻는 것은 결코 쉽지 않았습니다. 하지만 이제 오라클이 제공하는 최신 기능을 활용하여 위와 같은 메트릭을 쉽게 캡처할 수 있게 되었습니다.
먼저, 데이타베이스의 전반적인 상태를 확인하기 위해 Oracle Database 10g에서 아래와 같은 쿼리를 수행할 수 있습니다:
select METRIC_NAME, VALUE from SYS.V_$SYSMETRIC where METRIC_NAME IN ('Database CPU Time Ratio', 'Database Wait Time Ratio') AND INTSIZE_CSEC = (select max(INTSIZE_CSEC) from SYS.V_$SYSMETRIC); METRIC_NAME VALUE ------------------------------ ---------- Database Wait Time Ratio 6 Database CPU Time Ratio 94
Oracle Database 10g V$SYSMETRIC 뷰는 매우 유용한 응답시간 메트릭을 포함하고 있으며, Database Wait Time Ratio와 Database CPU Time Ratio는 그 중 두 가지입니다. 위의 쿼리는 이 두 통계치의 최신 스냅샷 정보를 보여주고 있습니다. 이 정보를 이용하여 데이타베이스가 대기작업/성능병목이 심한 상태에 있는지, 아니면 원활하게 운영되고 있는지 판단할 수 있습니다. Database CPU Time Ratio는 데이타베이스에서 사용한 총 CPU 시간을 “database time”으로 나눈 값으로, (인스턴스 백그라운드 프로세스의 작업을 제외한) 사용자 레벨의 호출에 사용된 데이타베이스 시간을 의미합니다. Database CPU Time Ratio가 높으면 (90~95% 이상) 대기작업/성능병목이 적으며 따라서 성능적인 면에서 문제가 없음을 의미합니다. (하지만 시스템에 따라 기준치가 달라지므로 이 값을 참고 용도로만 활용하시기 바랍니다.)
또 아래 쿼리를 사용하여 지난 한 시간 동안 데이타베이스의 성능이 급격히 저하된 경우가 있었는지 살펴 볼 수 있습니다:
select end_time, value from sys.v_$sysmetric_history where metric_name = 'Database CPU Time Ratio' order by 1; END_TIME VALUE -------------------- ---------- 22-NOV-2004 10:00:38 98 22-NOV-2004 10:01:39 96 22-NOV-2004 10:02:37 99 22-NOV-2004 10:03:38 100 22-NOV-2004 10:04:37 99 22-NOV-2004 10:05:38 77 22-NOV-2004 10:06:36 100 22-NOV-2004 10:07:37 96 22-NOV-2004 10:08:39 100 . .
또는, V$SYSMETRIC_SUMMARY 뷰에 대해아래와 같은 쿼리를 수행함으로써 전반적인 데이타의 효율성을 점검하고 성능 메트릭의 최소, 평균, 최대값을 확인할 수 있습니다:
select CASE METRIC_NAME WHEN 'SQL Service Response Time' then 'SQL Service Response Time (secs)' WHEN 'Response Time Per Txn' then 'Response Time Per Txn (secs)' ELSE METRIC_NAME END METRIC_NAME, CASE METRIC_NAME WHEN 'SQL Service Response Time' then ROUND((MINVAL / 100),2) WHEN 'Response Time Per Txn' then ROUND((MINVAL / 100),2) ELSE MINVAL END MININUM, CASE METRIC_NAME WHEN 'SQL Service Response Time' then ROUND((MAXVAL / 100),2) WHEN 'Response Time Per Txn' then ROUND((MAXVAL / 100),2) ELSE MAXVAL END MAXIMUM, CASE METRIC_NAME WHEN 'SQL Service Response Time' then ROUND((AVERAGE / 100),2) WHEN 'Response Time Per Txn' then ROUND((AVERAGE / 100),2) ELSE AVERAGE END AVERAGE from SYS.V_$SYSMETRIC_SUMMARY where METRIC_NAME in ('CPU Usage Per Sec', 'CPU Usage Per Txn', 'Database CPU Time Ratio', 'Database Wait Time Ratio', 'Executions Per Sec', 'Executions Per Txn', 'Response Time Per Txn', 'SQL Service Response Time', 'User Transaction Per Sec') ORDER BY 1 METRIC_NAME MINIMUM MAXIMUM AVERAGE ------------------------------ ---------- ---------- ---------- CPU Usage Per Sec 0 7 1 CPU Usage Per Txn 1 29 8 Database CPU Time Ratio 61 100 94 Database Wait Time Ratio 0 39 5 Executions Per Sec 2 60 8 Executions Per Txn 16 164 41 Response Time Per Txn (secs) 0 .28 .08 SQL Service Response Time (sec 0 0 0 User Transaction Per Sec 0 1 0
위 쿼리에는 Database CPU Ratio, Wait Time Ratio 이외에도 다양한 응답시간 메트릭이 사용되고 있습니다. 각각의 메트릭에 대해서는 뒤에서 설명하겠지만, 위 쿼리가 갖는 효과는 출력된 결과를 통해서도 쉽게 확인할 수 있습니다. 위의 실행 예에서는, Database CPU Time Ratio의 평균값이 94로, 정상적인 수준을 유지하고 있습니다.
다음으로 시스템 레벨에서 DBA가 확인해야 할 사항으로, 사용자들이 실제로 경험하는 평균적인 응답시간을 들 수 있습니다. (Oracle Database 10g 이전 버전의 경우에는 이와 같은 종류의 데이타를 확인하는 것이 어려웠습니다.) 위의 쿼리는 V$SYSMETRIC_SUMMARY 뷰를 통해 이에 필요한 정보를 질의하고 있습니다. 사용자들이 응답시간 문제로 불만을 제기하고 있는 상황이라면, DBA는 Response Time Per Txn과 SQL Service Response Time 메트릭을 통해 문제가 데이타베이스에 있는지 확인할 수 있습니다. 위의 실행 예에서는 사용자 트랜잭션 당 최대 응답시간이 0.28초, 평균 응답시간이 0.08초에 불과하므로 데이타베이스에는 아무런 문제가 없다고 판단됩니다.
응답시간이 일정 수준을 넘어서는 경우, DBA는 어떤 종류의 사용자 작업이 데이타베이스에 부담을 주고 있는지 확인해야 합니다. 역시, Oracle Database 10g 이전 버전에서는 이와 같은 정보를 얻는 것이 어려웠습니다. 하지만 10g 환경에서는 간단한 쿼리로 이러한 문제를 해결할 수 있습니다:
select case db_stat_name when 'parse time elapsed' then 'soft parse time' else db_stat_name end db_stat_name, case db_stat_name when 'sql execute elapsed time' then time_secs - plsql_time when 'parse time elapsed' then time_secs - hard_parse_time else time_secs end time_secs, case db_stat_name when 'sql execute elapsed time' then round(100 * (time_secs - plsql_time) / db_time,2) when 'parse time elapsed' then round(100 * (time_secs - hard_parse_time) / db_time,2) else round(100 * time_secs / db_time,2) end pct_time from (select stat_name db_stat_name, round((value / 1000000),3) time_secs from sys.v_$sys_time_model where stat_name not in('DB time','background elapsed time', 'background cpu time','DB CPU')), (select round((value / 1000000),3) db_time from sys.v_$sys_time_model where stat_name = 'DB time'), (select round((value / 1000000),3) plsql_time from sys.v_$sys_time_model where stat_name = 'PL/SQL execution elapsed time'), (select round((value / 1000000),3) hard_parse_time from sys.v_$sys_time_model where stat_name = 'hard parse elapsed time') order by 2 desc; DB_STAT TIME_SECS PCT_TIME ----------------------------- --------- -------- sql execute elapsed time 13263.707 45.84 PL/SQL execution elapsed time 13234.738 45.74 hard parse elapsed time 1943.687 6.72 soft parse time 520.584 1.8 . .
위의 실행 예는 데이타베이스가 대부분의 시간을 SQL 및 PL/SQL 요청을 처리하는데 사용하였음을 보여주고 있습니다. V$SYS_TIME_MODEL 뷰가 지원하는 모든 통계에 대한 정보는 여기에서 확인할 수 있습니다.
active time 이외에도 DBA는 global wait time을 확인하고 싶어할 것입니다. Oracle Database 10g 이전 버전에서는 wait 및 성능병목을 확인하기 위해 개별 wait 이벤트를 조회해야만 했지만, 이제 오라클이 제공하는 summary/rollup 메커니즘을 이용하여 wait 클래스 별로 wait 통계를 조회할 수 있습니다.
select WAIT_CLASS,
TOTAL_WAITS,
round(100 * (TOTAL_WAITS / SUM_WAITS),2) PCT_WAITS,
ROUND((TIME_WAITED / 100),2) TIME_WAITED_SECS,
round(100 * (TIME_WAITED / SUM_TIME),2) PCT_TIME
from
(select WAIT_CLASS,
TOTAL_WAITS,
TIME_WAITED
from V$SYSTEM_WAIT_CLASS
where WAIT_CLASS != 'Idle'),
(select sum(TOTAL_WAITS) SUM_WAITS,
sum(TIME_WAITED) SUM_TIME
from V$SYSTEM_WAIT_CLASS
where WAIT_CLASS != 'Idle')
order by 5 desc;
WAIT_CLASS TOTAL_WAITS PCT_WAITS TIME_WAITED_SECS PCT_TIME
--------------- ----------- ---------- ---------------- ----------
User I/O 2245204 7.48 4839.43 54.39
System I/O 2438387 8.12 2486.21 27.94
Application 920385 3.07 513.56 5.77
Other 39962 .13 422.36 4.75
Commit 200872 .67 284.76 3.2
Network 24133213 80.38 162.26 1.82
Concurrency 6867 .02 102.63 1.15
Configuration 39377 .13 86.21 .97
개별적인 wait 이벤트를 일일이 확인하고 합산하는 대신, 위의 쿼리를 사용하여 wait 타임이 대체적으로 user I/O wait에서 발생하고 있음을 확인할 수 있었습니다. 또 아래와 같은 쿼리를 통해 지난 몇 시간 동안의 상황을 확인하는 것도 가능합니다:
select to_char(a.end_time,'DD-MON-YYYY HH:MI:SS') end_time, b.wait_class, round((a.time_waited / 100),2) time_waited from sys.v_$waitclassmetric_history a, sys.v_$system_wait_class b where a.wait_class# = b.wait_class# and b.wait_class != 'Idle' order by 1,2; END_TIME WAIT_CLASS TIME_WAITED -------------------- --------------- ----------- 22-NOV-2004 11:28:37 Application 0 22-NOV-2004 11:28:37 Commit .02 22-NOV-2004 11:28:37 Concurrency 0 22-NOV-2004 11:28:37 Configuration 0 22-NOV-2004 11:28:37 Network .01 22-NOV-2004 11:28:37 Other 0 22-NOV-2004 11:28:37 System I/O .05 22-NOV-2004 11:28:37 User I/O 0 . .
또는 단일 SID를 기준으로 V$SESS_TIME_MODEL 뷰를 조회하고, 특정 세션에 대한 모든 통계 정보를 확인할 수 있습니다. 또 아래 쿼리를 사용하여 현재 발생 중인 session wait을 조회할 수 있습니다:
select a.sid, b.username, a.wait_class, a.total_waits, round((a.time_waited / 100),2) time_waited_secs from sys.v_$session_wait_class a, sys.v_$session b where b.sid = a.sid and b.username is not null and a.wait_class != 'Idle' order by 5 desc; SID USERNAME WAIT_CLASS TOTAL_WAITS TIME_WAITED_SECS --- ---------- --------------- ----------- ---------------- 257 SYSMAN Application 356104 75.22 255 SYSMAN Commit 14508 25.76 257 SYSMAN Commit 25026 22.02 257 SYSMAN User I/O 11924 19.98 . . .
이 과정을 거치고 난 뒤에는 (오라클 이전 버전에서 지원되는) $SESSION_WAIT와 V$SESSION_EVENT를 이용하여 개별 wait 이벤트를 점검할 수 있습니다. Oracle Database 10g에서는 이 두 가지 뷰에도 새로운 클래스가 추가되었습니다.
또 과거에 어떤 세션이 로그온하였는지, 어떤 세션이 가장 많은 자원을 사용하였는지 확인하기 위해 아래 쿼리를 사용할 수도 있습니다. 아래 예에서는 2004년 11월 21일 자정부터 오전 5시까지의 user I/O wait를 조회하고 있습니다:
select sess_id,
username,
program,
wait_event,
sess_time,
round(100 * (sess_time / total_time),2) pct_time_waited
from
(select a.session_id sess_id,
decode(session_type,'background',session_type,c.username) username,
a.program program,
b.name wait_event,
sum(a.time_waited) sess_time
from sys.v_$active_session_history a,
sys.v_$event_name b,
sys.dba_users c
where a.event# = b.event# and
a.user_id = c.user_id and
sample_time > '21-NOV-04 12:00:00 AM' and
sample_time < '21-NOV-04 05:00:00 AM' and
b.wait_class = 'User I/O'
group by a.session_id,
decode(session_type,'background',session_type,c.username),
a.program,
b.name),
(select sum(a.time_waited) total_time
from sys.v_$active_session_history a,
sys.v_$event_name b
where a.event# = b.event# and
sample_time > '21-NOV-04 12:00:00 AM' and
sample_time < '21-NOV-04 05:00:00 AM' and
b.wait_class = 'User I/O')
order by 6 desc;
SESS_ID USERNAME PROGRAM WAIT_EVENT SESS_TIME PCT_TIME_WAITED
------- -------- ---------- ------------------------- ---------- -------------
242 SYS exp@RHAT9K db file scattered read 3502978 33.49
242 SYS oracle@RHA db file sequential read 2368153 22.64
242 SYS oracle@RHA db file scattered read 1113896 10.65
243 SYS oracle@RHA db file sequential read 992168 9.49
이 시점에서 Oracle Database 10g V$ACTIVE_SESSION_HISTORY 뷰를 통해 특정 시간대의 세션 상황을 점검할 수 있습니다. 이 뷰를 이용하면 복잡하고 번거로운 트레이스 기능을 이용하지 않고도 유용한 정보를 캡처할 수 있습니다. V$ACTIVE_SESSION_HISTORY 뷰를 이용한 SQL 구문 응답시간의 분석 사례에 대해서는 다음 섹션에서 자세히 다루기로 합니다.
SQL 응답시간 분석
SQL 구문의 응답시간 분석 기능은 Oracle9i 및 Oracle 10g에서 한층 강화되었습니다. DBA들은 오라클이 제공하는 여러 가지 툴을 이용하여 비효율적인 데이타베이스 코드를 추적할 수 있습니다.
이전부터 가장 많이 사용되어 온 V$ 뷰로 V$SQLAREA를 들 수 있습니다. Oracle9i에서 추가된 ELAPSED_TIME 및 CPU_TIME 컬럼은 실제 사용자가 SQL 구문을 실행하는데 소요되는 시간을 측정하는데 매우 유용합니다 (ELAPSED_TIME과 CPU_TIME을 EXECUTIONS 컬럼의 값으로 나누면, 구문의 실행에 소요된 평균 시간을 얻을 수 있습니다).
Oracle Database 10g에서는 V$SQLAREA 뷰에 새로운 wait 및 시간 관련 컬럼이 6가지나 추가되었습니다:
- APPLICATION_WAIT_TIME
- CONCURRENCY_WAIT_TIME
- CLUSTER_WAIT_TIME
- USER_IO_WAIT_TIME
- PLSQL_EXEC_TIME
- JAVA_EXEC_TIME
새로운 컬럼을 이용하여, 프로시저가 PL/SQL 코드 및 표준 SQL 구문의 실행에 각각 소비하는 시간을 확인하거나, 특정 SQL 구문에 대해 발생한 user I/O wait를 확인할 수 있습니다. 예를 들어, 가장 높은 user I/O wait를 갖는 Top 5 SQL 구문을 확인하기 위한 쿼리가 아래와 같습니다:
select * from (select sql_text, sql_id, elapsed_time, cpu_time, user_io_wait_time from sys.v_$sqlarea order by 5 desc) where rownum < 6; SQL_TEXT SQL_ID ELAPSED_TIME CPU_TIME USER_IO_WAIT_TIME ------------------------- ------------ ------------ ---------- --------------- select /*+ rule */ bucket db78fxqxwxt7 47815369 19000939 3423 SELECT :"SYS_B_0" FROM SY agdpzr94rf6v 36182205 10170226 2649 select obj#,type#,ctime,m 04xtrk7uyhkn 28815527 16768040 1345 select grantee#,privilege 2q93zsrvbdw4 28565755 19619114 803 select /*+ rule */ bucket 96g93hntrzjt 9411028 3754542 606
가장 높은 elapsed time 또는 wait time을 갖는 SQL 구문을 확인하는 것은 중요합니다. 하지만 좀 더 자세한 정보를 파악할 필요가 있다면, 앞에서 설명한 V$ACTIVE_SESSION_HISTORY 뷰를 활용할 수 있습니다. 이 뷰를 이용하면 SQL 구문의 지연 요인이 되는 실제 wait 이벤트를 확인하고, wait의 원인이 된 파일, 오브젝트, 오브젝트 블록 등을 확인할 수 있습니다.
예를 들어, 매우 높은 user I/O wait time을 갖는 SQL 구문을 발견한 경우를 가정해 봅시다. 해당 쿼리와 관련된 개별 wait 이벤트를 확인하고, 이벤트 별로 wait time 또는 wait의 원인이 된 파일 및 오브젝트를 조회하기 위해 아래와 같은 쿼리를 수행할 수 있습니다:
select event, time_waited, owner, object_name, current_file#, current_block# from sys.v_$active_session_history a, sys.dba_objects b where sql_id = '6gvch1xu9ca3g' and a.current_obj# = b.object_id and time_waited <> 0; EVENT TIME_WAITED OWNER OBJECT_NAME file block ------------------------- ----------- ------ --------------------- ---- ------ db file sequential read 27665 SYSMAN MGMT_METRICS_1HOUR_PK 3 29438 db file sequential read 3985 SYSMAN SEVERITY_PRIMARY_KEY 3 52877
과거 시점을 기준으로 V$ACTIVE_SESSION_HISTORY를 조회하고, 특정 시간대에서 최적화되지 않은 SQL 구문을 가려낼 수도 있습니다. 이처럼, Oracle Database 10g에서는 트레이스를 이용한 복잡한 방법을 사용하지 않고, 간단한 데이타 딕셔너리 뷰에 대한 조회를 통해 SQL 구문의 응답시간을 분석할 수 있습니다.
결론
Oracle Database 10g에는 DBA와 성능분석 담당자들이 매우 유용하게 활용할 수 있는 응답시간 메트릭이 추가되었습니다. 이러한 통계를 이용하여 대규모 데이타베이스 환경에서 복잡한 성능 문제를 신속하고 간단하게 확인하고 해결할 수 있습니다.
