오라클 성능 트러블슈팅의 기초 (2012년)
세션 스냅샷 리포트 0 0 46,642

by 구루비스터디 세션 스냅샷 리포트 [2018.09.27]


세션 스냅샷 리포트

AWR 리포트의 한계

  • 짧은 구간의 성능 문제를 분석하기 어렵다
  • 특정 세션 레벨의 성능 문제를 파악하기 어렵다


  1. 세션 스냅샷 리포트
    1. 수집해야 할 데이터
      1. 필수수집 정보
      2. 부수수집 정보
    2. 수집 및 리포트방법
      1. 사례연구
      2. 세션 스냅샷 리포트 활용 예
      3. 결론


수집해야 할 데이터

가장 기본적인 세 가지의 성능 데이터

  • 일량통계 정보(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 수행
  • 두번째 스냅샷
    • 두번째 스냅샷 저장
  • 차이비교 제출


사례연구


==========================================================================
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 ;
==========================================================================



==========================================================================
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;
==========================================================================



수집대상 성능 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

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입