오라클 성능 트러블슈팅의 기초 (2012년)
세션 스냅샷 리포트
AWR 리포트의 한계
- 짧은 구간의 성능 문제를 분석하기 어렵다
- 특정 세션 레벨의 성능 문제를 파악하기 어렵다
수집해야 할 데이터
가장 기본적인 세 가지의 성능 데이터
- 일량통계 정보(Statistics)
- 대기이벤트 정보
- 타임모델 정보
- 세션레벨로도 시스템레벨로도 선택이 가능함
필수수집 정보
- V$SYSSTAT 혹은 V$SESSTAT 뷰 작업 회수. 가령 Logical Reads 값 등
- V$SYSTEM_EVENT 혹은 V$SESSION_EVENT 뷰 - 대기 시간
- V$SYS_TIME_MODEL 혹은 V$SESS_TIME_MODEL 뷰- 타임 모델
부수수집 정보
- V$LATCH 뷰 - 래치 획득 회수
- V$ROWCACHE 뷰 - 딕셔너리 정보 조회 회수
- V$MUTEX_SLEEP 혹은 V$MUTEX_SLEEP_HISTORY 뷰 - 뮤텍스에 의한 대기 회수
- Buffer Get 정보 - X$KCBSW 뷰와 X$KCBWH 뷰 - 버퍼를 획득한 이유
- V$LIBRARYCACHE 뷰 - 라이브러리 캐시에 대한 획득 회수와 무효화( lnvalidations) 회수
- V$OSSTAT 뷰 - OS레벨의 CPU 및 메모리 사용 현황
수집 및 리포트방법
- 첫번째 스냅샷 조회
- 첫번째 스냅샷 저장
- 대상Action 수행
- 두번째 스냅샷
- 두번째 스냅샷 저장
- 차이비교 제출
사례연구
- 예제1) V$SYSSTAT 뷰에서 차이( Delta) 값 구하기 : 단일 항목 비교 (변수이용)
==========================================================================
col name format a30
col value format 999,999,999,999,999
-- 1. 1st snapshot
col value new_value prev_value
select name, value
from v$sysstat
where name = 'session logical reads';
-- 1-1. save 1st snapshot
prompt &prev_value
-- 2. Workload Process
select count(*) from all_objects ;
-- 3. 2nd snapshot
col value new_value next_value
select name, value
from v$sysstat
where name = 'session logical reads';
-- 3-1. save 2nd snapshot
prompt &next_value
-- 4. Diff Report
select (&next_value - &prev_value) as diff from dual ;
==========================================================================
- 예제2) V$SESSTAT 뷰에서 차이( Delta) 값 구하기 : 단일 항목 비교 (변수이용)
==========================================================================
col name format a30
col value format 999,999,999
-- 1. 1st snapshot
col value new_value prev_value
select s.sid, n.name, s.value
from v$sesstat s,
v$statname n ---------<<<<<<<<<<<<<<< stat명으로 비교하기 위해 조인
where s.statistic# = n.statistic#
and s.sid = userenv('sid')
and n.name = 'session logical reads';
-- 1-1. save 1st snapsho
prompt &prev_value
-- 2. Workload Process
select count(*) from all_objects ;
-- 3. 2nd snapshot
col value new_value next_value
select s.sid, n.name, s.value
from v$sesstat s ,
v$statname n
where s .statistic# = n.statistic#
and s.sid = userenv('sid')
and n.name = 'session logical reads';
-- 3-1. save 2nd snapshot
prompt &next_value
-- Diff
select (&next_value - &prev_value) as diff from dual;
==========================================================================
==========================================================================
-- create temporary table
create global temporary table ses_snapshot(
seq number,
name varchar2 (64) ,
value number
) on commit preserve rows;
-- 1st snapshot
insert into ses_snapshot
select 1,
n.name,
s.value
from v$sesstat s ,
v$statname n
where s.statistic# = n.statistic#
and s.sid = userenv ('sid');
-- 2. Workload Process
select count(*) from all_objects ;
-- 2nd snapshot
insert into ses_snapshot
select 2,
name,
value
from v$sesstat s ,
v$statname n
where s.statistic# = n.statistic#
and s.sid = userenv('sid');
-- report
col name format a40
col value1 format 999,999,999
col value2 format 999,999,999
col diff format 999,999,999
select s1.name,
(s2.value - s1.value) as diff,
s1.value as value1,
s2.value as value2
from ses_snapshot s1 ,
ses_snapshot s2
where s1.seq = 1 -- 1st snapshot
and s2.seq = 2 -- 2nd snapshot
and s1.name = s2.name
and abs(s2.value - s1.value) > 0
order by 2 desc;
==========================================================================
==========================================================================
create global temporary table ses_snapshot(
seq number,
type varchar2 (5) ,
name varchar2 (64) ,
value number
) on commit preserve rows;
-- 1st snapshot
-- v$sesstat
insert into ses_snapshot
select 1,
'STAT',
n.name,
s.value
from v$sesstat s ,
v$statname n
where s.statistic# = n.statistic#
and s.sid = userenv('sid');
-- v$session_event
insert into ses_snapshot
select 1,
'EVENT',
e.event,
e.time_waited
from v$session_event e
where e.sid = userenv ('sid');
-- 2. Workload Process
select count(*) from user_objects ;
-- 2nd snapshot
-- v$sesstat
insert into ses_snapshot
select 2,
'STAT',
n.name,
s.value
from v$sesstat s ,
v$statname n
where s.statistic# = n.statistic#
and s.sid = userenv('sid');
-- v$session_event
insert into ses_snapshot
select 2,
'EVENT',
e.event,
e.time_waited
from v$session_event e
where e.sid = userenv ('sid');
-- report
col name format a50
col value1 forrnat 999,999,999
col value2 forrnat 999,999,999
col diff format 999,999,999
select s2.type,
s2.name,
(s2.value - nvl(s1.value, 0)) as diff,
s1.value as value1 ,
s2.value as value2
from ses_snapshot s1 ,
ses_snapshot s2
where s1.seq(+) = 1 -- 1st snapshot
and s2.seq = 2 -- 2nd snapshot
and s1.name(+) = s2.name
and s1.type(+) = s2.type
and abs(s2.value - nvl(s1.value, 0)) > 0
order by decode(s1.type , 'STAT', 1
, 'EVENT', 2)
, abs(s2.value - s1.value) desc;
==========================================================================
==========================================================================
-- 3rd Workload Process
select count(*) from all_objects ;
-- 3rd snapshot
-- v$sesstat
insert into ses_snapshot
select 3,
'STAT',
n.name,
s.value
from v$sesstat s ,
v$statname n
where s.statistic# = n.statistic#
and s.sid = userenv('sid');
-- v$session_event
insert into ses_snapshot
select 3,
'EVENT',
e.event,
e.time_waited
from v$session_event e
where e.sid = userenv ('sid');
-- report
col name format a40
col diff1 format 999,999,999
col diff2 format 999,999,999
select s3.type,
s3.name,
(nvl(s2.value, 0) - nvl(s1.value, 0)) as diff1,
(nvl(s3.value, 0) - nvl(s2.value, 0)) as diff2
from ses_snapshot s1 ,
ses_snapshot s2 ,
ses_snapshot s3
where s1.seq(+) = 1 -- 1st snapshot
and s2.seq(+) = 2 -- 2nd snapshot
and s3.seq = 3 -- 3rd snapshot
and s1.name(+)= s2.name
and s1.type(+)= s2.type
and s2.name(+)= s3.name
and s2.type(+)= s3.type
and abs(s3.value - nvl(s1.value, 0)) > 0
order by decode(s1.type, 'STAT', 1
, 'EVENT', 2)
, abs(s2.value - s1.value) desc;
==========================================================================
- 예제6) 완전한 형태의 세션 스냅샷 리포트
수집대상 성능 View들
- V$SYSSTAT 혹은 V$SESSTAT 뷰
- V$SYSTEM_EVENT 혹은 V$SESSION_EVENT 뷰
- V$SYS_TIME_MODEL 혹은 V$SESS_TIME_MODEL 뷰
- V$LATCH 뷰
- V$ROWCACHE 뷰
- V$MUTEX_SLEEP 혹은 V$MUTEX_SLEEP_HISTORY 뷰
- Buffer Get 정보 - X$KCBUWHY 뷰와 X$KCBWH 뷰
- V$LIBRARYCACHE 뷰
- V$SGA_RESIZE_OPS
- V$OSSTAT 뷰
-- V$SYSSTAT 혹은 V$SESSTAT 뷰
select SID session_id,
'STAT',
n.name,
s.value
from v$sesstat s
, v$statname n
where s.sid = userenv('sid')
and s.statistic# = n.statistic#;
-- V$SYSTEM_EVENT 혹은 V$SESSION_EVENT 뷰
select SID session_id,
'WAIT',
event,
time_waited
from v$session_event
where sid = userenv('sid');
-- V$SYS_TIME_MODEL 혹은 V$SESS_TIME_MODEL 뷰
select SID session_id,
'TIME',
stat_name,
value
from v$sess_time_model
where sid = userenv('sid');
-- V$LATCH 뷰 : system level
select 'LATCH',
name,
gets
from v$latch;
select 'LATCH MISS',
substr(parent_name || '(' || location || ')', 1, 100) ,
sleep_count
from v$latch_misses;
-- V$ROWCACHE 뷰 : system level
select 'ROWCACHE',
parameter ,
gets
from v$rowcache
where type = 'PARENT';
-- V$MUTEX_SLEEP 혹은 V$MUTEX_SLEEP_HISTORY 뷰 : system level
select 'MUTEX',
mutex_type || '.' || location,
sleeps
from v$mutex_sleep;
-- Buffer Get 정보 - X$KCBUWHY 뷰와 X$KCBWH 뷰
select 'BUFF GET',
kcbwhdes,
why0 + why1 + why2
from X$KCBSW s, X$KCBWH w -- 10g
--X$kcbuwhy s, X$kcbwh w -- llg
where s.indx = w.indx;
-- V$LIBRARYCACHE 뷰 : system level
-- library cache(gets)
select 'LIB GET',
namespace,
gets
from v$librarycache;
-- library cache(invalidations)
select 'LIB INV',
namespace,
invalidations
from v$librarycache;
-- V$SGA_RESIZE_OPS : system level
select 'SGA RESIZE',
item,
max(value)
from (select component || '.' || parameter as item,
final_size as value
from v$sga_resize_ops
union all
select component || '.' || parameter as item,
current_size as value
from v$sga_current_resize_ops )
group by item;
-- V$OSSTAT 뷰
select 'OS STAT',
stat_name,
value
from v$osstat;
세션 스냅샷 리포트 활용 예
성능트러블슈팅의 기본은분석에 필요한데이터를잘수집하고그것을효과적으로 리포팅하는 것
- 세션 스냅샷 리포트를 통해 분석하는 예
- : 특정 세션이 동일한 작업을 반복해서 수행함에도 불구하고 성능이 저하되는 상황
==========================================================================
SQL> create table t1 (c1 , c2)
2 as
3 select level, rpad('X', 1000 )
4 from dual
5 connect by level <= 1000;
테이블이 생성되었습니다.
SQL>
SQL> exec dbms_application_info.set_client_info('session1') ;
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL>
SQL> set serveroutput on
SQL>
SQL>
SQL> declare
2 type varchar2_array is table of varchar2(30000) index by pls_integer;
3 va varchar2_array;
4 v_start_time number := dbms_utility.get_time;
5 v_temp number;
6 begin
7 for idx in 1 .. 5000 loop
8 -- allocate memory
9 va(idx) := rpad('x', 30000 ) ;
10 -- search memory
11 for idx2 in 1 .. 10 loop
12 for idx3 in 1 .. idx loop
13 v_temp := length(va(idx3)) ;
14 end loop;
15 end loop;
16 -- do loqical reads
17 for r in (select count(*) from t1) loop
18 null ;
19 end loop;
20
21 if mod(idx, 100) = 0 then
22 dbms_output.put_line(idx || 'th elapsed time' ||
23 (dbms_utility.get_time - v_start_time ) ||
24 '(cs)' ) ;
25 v_start_time := dbms_utility.get_Time;
26 end if;
27
28 end loop ;
29 end;
30 /
100th elapsed time20(cs)
200th elapsed time17(cs)
300th elapsed time18(cs)
400th elapsed time18(cs)
500th elapsed time21(cs)
600th elapsed time22(cs)
700th elapsed time23(cs)
800th elapsed time25(cs)
900th elapsed time25(cs)
1000th elapsed time27(cs)
1100th elapsed time28(cs)
1200th elapsed time29(cs)
1300th elapsed time30(cs)
1400th elapsed time31(cs)
1500th elapsed time33(cs)
1600th elapsed time33(cs)
1700th elapsed time36(cs)
1800th elapsed time36(cs)
1900th elapsed time37(cs)
2000th elapsed time39(cs)
2100th elapsed time39(cs)
2200th elapsed time43(cs)
2300th elapsed time42(cs)
2400th elapsed time42(cs)
2500th elapsed time45(cs)
2600th elapsed time46(cs)
2700th elapsed time46(cs)
2800th elapsed time49(cs)
2900th elapsed time50(cs)
3000th elapsed time51(cs)
3100th elapsed time52(cs)
3200th elapsed time53(cs)
3300th elapsed time55(cs)
3400th elapsed time55(cs)
3500th elapsed time57(cs)
3600th elapsed time58(cs)
3700th elapsed time60(cs)
3800th elapsed time59(cs)
3900th elapsed time62(cs)
4000th elapsed time63(cs)
4100th elapsed time64(cs)
4200th elapsed time66(cs)
4300th elapsed time67(cs)
4400th elapsed time69(cs)
4500th elapsed time68(cs)
4600th elapsed time71(cs)
4700th elapsed time71(cs)
4800th elapsed time74(cs)
4900th elapsed time75(cs)
5000th elapsed time76(cs)
PL/SQL 처리가 정상적으로 완료되었습니다.
==========================================================================
성능문제에 대한 대응
반드시 데이터로 이야기해야 함!!
- 문제 해결을 위한 데이터 수집과 리포트
=========================================================================
SQL> set linesize 150 pagesize 999
SQL>
SQL> col sid new_value sid;
SQL> select sid from v$session where client_info = 'session1' ;
SID
----------
144
147
SQL>
SQL> col type format a20
SQL> col name format a45
SQL> col diff format a50
SQL> ;
1* select sid from v$session where client_info = 'session1'
SQL> -- session snapshot
SQL> select *
2 from table( WOONG_PKG.SNAP_REPORT(&sid, 5 , 1));
구 2: from table( WOONG_PKG.SNAP_REPORT(&sid, 5 , 1))
신 2: from table( WOONG_PKG.SNAP_REPORT( 147, 5 , 1))
TYPE NAME DIFF
-------------------- --------------------------------------------- --------------------------------------------
STAT session pga memory 17760256→16777216→14942208→14417920
STAT session pga memory max 17694720→16777216→14942208→14417920
STAT table scan rows gotten 270000→256000→227000→220000
STAT consistent gets 39837→37587→33561→32340
STAT consistent gets from cache 39837→37632→33516→32340
STAT session logical reads 39690→37632→33516→32340
STAT no work - consistent read gets 38753→36465→32604→31460
STAT table scan blocks gotten 38610→36608→32461→31460
STAT calls to get snapshot scn: kcmgss 1350→1280→1140→1100
STAT recursive calls 270→256→228→220
STAT table scans (short tables) 270→255→228→220
STAT execute count 270→256→228→219
STAT CPU used by this session 105→109→104→107
STAT recursive cpu usage 8→3→3→0
TIME DB time 4783238→0→0→0
TIME sql execute elapsed time 4783163→0→0→0
TIME DB CPU 4770000→0→0→0
TIME PL/SQL execution elapsed time 1008553→1039995→1007708→1026838
LIB GET BODY 1→0→0→0
LIB GET TABLE/PROCEDURE 1→0→0→0
ROWCACHE dc_users 5→5→5→5
ROWCACHE dc_tablespaces 5→5→5→5
LATCH cache buffers chains 80577→76386→68209→66023
LATCH simulator hash latch 2455→2314→2074→2003
LATCH simulator lru latch 2446→2308→2068→1997
LATCH library cache 555→515→461→446
LATCH library cache pin 548→514→460→444
LATCH object queue header operation 81→76→77→84
LATCH SQL memory manager workarea list latch 12→12→79→12
LATCH undo global data 46→50→50→49
LATCH checkpoint queue latch 8→0→32→8
LATCH row cache objects 30→30→30→30
LATCH enqueue hash chains 28→18→20→24
LATCH enqueues 24→15→17→22
LATCH messages 15→5→13→9
LATCH channel operations parent latch 6→0→8→6
LATCH redo writing 4→3→5→4
LATCH redo allocation 5→5→5→5
LATCH library cache lock 5→0→0→0
LATCH session allocation 3→2→2→4
LATCH In memory undo latch 4→4→4→4
LATCH session state list latch 3→0→0→0
LATCH ksuosstats global area 2→2→2→2
LATCH session idle bit 2→1→1→2
LATCH active service list 0→2→0→0
LATCH dml lock allocation 2→2→2→2
LATCH compile environment latch 1→1→1→2
LATCH lgwr LWN SCN 1→1→1→1
LATCH library cache lock allocation 1→0→0→0
LATCH active checkpoint queue latch 1→0→0→1
LATCH resmgr:actses active list 1→1→1→1
LATCH sort extent pool 0→0→0→1
LATCH transaction allocation 0→0→0→1
LATCH mostly latch-free SCN 1→1→1→1
LATCH OS process allocation 1→0→1→0
LATCH session timer 1→0→1→0
LATCH resmgr:schema config 1→1→1→1
LATCH Consistent RBA 1→1→1→1
LATCH FOB s.o list latch 1→0→0→0
LATCH list of block allocation 1→0→0→0
LATCH KMG MMAN ready and startup request latch 0→0→1→0
OS STAT AVG_SYS_TIME 1→0→2→4611686018427387903
OS STAT IDLE_TIME 311→320→304→313
OS STAT BUSY_TIME 114→119→120→112
OS STAT USER_TIME 107→115→109→111
OS STAT AVG_IDLE_TIME 77→79→76→77
OS STAT AVG_BUSY_TIME 28→29→30→27
OS STAT AVG_USER_TIME 26→28→27→27
OS STAT SYS_TIME 7→4→11→1
69 개의 행이 선택되었습니다.
=========================================================================
결론
- 두 개의 작업간의 차이를 비교할 때 스냄삿을 만들고 스냄삿간의 차이를 보여주는 기법은 쉬우면서도 매우 강력한 기능이다.
"데이터베이스 스터디모임" 에서 2012년에 "오라클 성능 트러블슈팅의 기초
" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/4107
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.