원문 : http://www.ischo.net -- 조인상 //시스템 엔지니어

+++++++++++++++++++++++++++++++++++++++++++++++++++++++

 

 

1. 성능분석에 이용되는 뷰

 

오라클에서 수행 누적 통계치를 저장하고 확인하고자 할때 사용하는 시스템 별로 사용하는 뷰들은 다음과 같다. 

 

v$sysstat : 오라클에서 인스턴스 구동후 현재까지의 "누적 통계치"를 확인할 때 사용할 수 있는 뷰

v$sesstat : 수행하는 세션별로 통계치를 확인 하는 뷰

v$mystat :  현재 접속해 있는 자기 세션에 대한 수행통계

v$system_event : 이벤트에 대한 대기시간 정보를 포함하고 있는 뷰. 이벤트 발생시의 정보을 볼 수 있다.

 

 

다음 명령으로 전체 측정항목을 볼수 있다.

SQL> select * from v$statname;

STATISTIC# NAME                                                    CLASS    STAT_ID
---------- -------------------------------------------------- ---------- ----------
         0 logons cumulative                                           1 2666645286
         1 logons current                                              1 3080465522
         2 opened cursors cumulative                                   1   85052502
         3 opened cursors current                                      1 2301954928
         4 user commits                                                1  582481098
         5 user rollbacks                                              1 3671147913
         6 user calls                                                  1 2882015696
       ....
       378 OS Signals received                                        16  210375991
       379 OS Voluntary context switches                              16 2422402766
       380 OS Involuntary context switches                            16 3316937952

381 rows selected.

 

 

2. 뷰에서 성능분석에 자주 이용되는 항목들

 

----------------------------------------------------------------  자주 사용되는 항목들 -------------------------------------------------------------------

1. Buffer NoWait %
버퍼블록을 읽으려 할 때, buffer busy waits대기 없이 곧바로 읽기에 성공한 비율을 나타냄.

 

select  round(100*(1-bfwt/gets),2) "Buffer Nowait %"
from    (
        select  sum(a.count) bfwt
        from    v$waitstat a
        ),
        (
        select  b.value gets
        from    v$sysstat b
        where   b.name = 'session logical reads'
        );
 

2. Redo NoWait %
Redo로그를 기록할 공간을 요청하지 않고 곧바로 Redo 엔트리를 기록한 비율을 나타냄.

 

select  round(100*(1-rlsr/rent),2) "Redo Nowait %"
from    (
        select  a.value rlsr
        from    v$sysstat a
        where   a.name = 'redo log space requests'
        ),
        (
        select  value rent
        from    v$sysstat b
        where   b.name = 'redo entries'
        );

 

3. Buffer Hit %
디스크 읽기를 수반하지 않고 버퍼캐시에서 블록찾기에 성공한 비율을 나타냄.

 

select  round(100*(1-(phyr-phyrd-nvl(phyrdl,0))/gets),2) "Buffer Hit %"
from    (
        select  value phyr
        from    v$sysstat a
        where   a.name = 'physical reads'
        ),
        (
        select  value phyrd
        from    v$sysstat b
        where   b.name = 'physical reads direct'
        ),
        (
        select  value phyrdl
        from    v$sysstat c
        where   c.name = 'physical reads direct (lob)'
        ),
        (
        select  value gets
        from    v$sysstat d
        where   d.name = 'session logical reads'
        );

 

 

4. Latch Hit %
래치 경합없이 첫번째 시도에서 곧바로 래치를 획득한 비율을 나타냄.

 

select  round(100*(1-sum(a.misses)/sum(a.gets)),2) "Latch Hit %"
from    v$latch a;

 

5. Library Hit %
파싱부하와 관련 있는 항목.
라이브러리 캐시에 이미 적재된 SQL커서를 생행하거나 오브젝트정보를 읽으려할 때 커서 또는 오브젝트정보가 Heap영역에서 찾아지는 비율을 나타냄.

 

-- Library Hit (Get) %
select  round(100*sum(a.gethits)/sum(a.gets),2) "Library Cache Get Hit %"
from    v$librarycache a;
 
-- Library Hit (Pin) %
select  round(100*sum(a.pinhits)/sum(a.pins),2) "Library Cache Pin Hit %"
from    v$librarycache a;

 

 

6. Soft Parse %
파싱부하와 관련 있는 항목.
실행계획이 라이브러리 캐시에서 찾아져 하드파싱을 일으키지 않고 SQL을 수행한 비율을 나타냄.

 

-- Soft Parse %
select  round(100*(1-hprs/prse),2) "Soft Parase %"
from    (
        select  a.value hprs
        from    v$sysstat a
        where   a.name = 'parse count (hard)'
        ),
        (
        select  b.value prse
        from    v$sysstat b
        where   b.name = 'parse count (total)'
        );

 

7. Execute to Parse %
파싱부하와 관련 있는 항목.
Parse Call없이 곧바로 SQL을 수행한 비율. 즉, 커서를 애플리케이션에서 캐싱한 채 반복 수행한 비율을 나타냄.

 

-- Execute to Paraes %
select  round((1-prse/exe)*100,2) "Execute to Parse %"
from    (
        select  a.value prse
        from    v$sysstat a
        where   a.name = 'parse count (total)'
        ),
        (
        select  b.value exe
        from    v$sysstat b
        where   b.name = 'execute count'
        );

8. Parse CPU to Parse Elapsd %
파싱부하와 관련 있는 항목.
파싱 총 소요 시간 중 CPU time이 차지한 비율. 파싱에 소요된 시간 중 실제 일을 수행한 시간비율을 나타냄.

 

-- Parase CPU to Parse Elapsed %
select  decode(prsela,0,to_number(null),round(prscpu/prsela*100,2)) "Parse CPU to Parse Elapsed %"
from    (
        select  a.value prsela
        from    v$sysstat a
        where   a.name = 'parse time elapsed'
        ),
        (
        select  b.value prscpu
        from    v$sysstat b
        where   b.name = 'parse time cpu'
        );
 
 

9. % Non-Parse CPU
파싱부하와 관련 있는 항목.
SQL을 수행하면서 사용한 전체 CPU time중 파싱 이외의 작업이 차지한 비율을 나타냄.

 

-- Non-Parse CPU %
select  decode(tcpu,0,to_number(null),round(100*(1-(prscpu/tcpu)),2)) "% Non-Parase CPU"
from    (
        select  a.value tcpu
        from    v$sysstat a
        where   a.name = 'CPU used by this session'
        ),
        (
        select  b.value prscpu
        from    v$sysstat b
        where   b.name = 'parse time cpu'
        );

 

 

10. In-memory Sort %
전체 소트 수행횟수에서 In-Memory방식으로 소트한 비율을 나타냄.

 

-- In-memory Sort %
select  decode((srtm+srtd),0,to_number(null),round(100*srtm/(srtd+srtm),2)) "In-memory Sort %"
from    (
        select  a.value srtm
        from    v$sysstat a
        where   a.name = 'sorts (memory)'
        ),
        (
        select  b.value srtd
        from    v$sysstat b
        where   b.name = 'sorts (disk)'
        );
        

11. Memory Usage %
Shared Pool내에서 현재 사용중인 메모리 비중을 나타냄.

 

-- Memory Usage %
select  100*(1-sum(decode(a.name,'free memory',a.bytes))/sum(a.bytes))
from    v$sgastat a
where   a.pool = 'shared pool';

 

 

12. % SQL with executions>1
전체 SQL 개수에서 두번이상 수행된 SQL이 차지하는 비중을 나타냄.

 

13. % Memory for SQL w/exec>1
전체 SQL이 차지하는 메모리 중 두번이상 수행된 SQL이 차지하는 메모리 비중을 나타냄.

 

14. SESSION LOGICAL READS
SESSION 의 논리적 읽기 ( 메모리->CPU ) 를 알 수 있다. 이 값의 일정시간 당 델타값이 증가한 다는 것은 일량이 증가하고 있다고 해석하면 된다. 여기서 이상징후를 확인하면 해당 SID 에 대해 TRACE 라던가 수행 쿼리를 조회하는 행동으로 연계할 수 있다.

15. PHYSICAL READS
SESSION 의 물리적 읽기 ( 디스크->메모리 ) 를 알 수 있다. 이 값이 증가하면 증가할 수록 디스크의 I/O 가 많이 발생한다. 이와 관련해 I/O 관련 WAIT EVENT 들이 관측될 수 있다. SESSION LOGICAL READS 와 마찬가지로 많이 유발하는 SID 를 찾아 추가적인 조치를 취할 수 있다.

16. PARSE COUNT (HARD)
이것은 순전히 LITERAL SQL 수행을 잡아내기 위한 방편이다. 이 지표는 상황에 따라 SYSTEM  | SESSION LEVEL 로 조회해야 한다. 이를테면 순간적으로 세션을 맺고 LITERAL SQL 을 수행하고 빠지는 세션이 많은 경우엔 SYSTEM LEVEL 로 봐야 시스템에 HARD PARSING 이 많이 발생하고 있다는 것을 알 수 있을 것이다.

17. EXECUTE COUNT
이 지표는 SQL 을 수행한 수를 의미한다. (RECURSIVE CALL 제외) 시스템의 활동성을 의미한다. 보통 급증보다 급감하는 경우에 이슈가 있는 경우가 있다. (시스템이 IDLE 인 경우를 제외)

------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

 위 점검항목중 SESSION LOGICAL READS, PHYSICAL READS, PARSE COUNT (HARD), EXECUTE COUNT, dbfile SEQ READ,dbfile scattred read 항목을 v$sysstat와 v$session_wait 으로 부터 조회하는 쿼리.

 

SQL> col sread for 99999999999999999999;

SQL> col pread for 99999999999999999999;

SQL> select
sum(decode(name, 'session logical reads',value,0)) sread,
sum(decode(name, 'physical reads',value, 0)) pread,
sum(decode(name, 'parse count (hard)',value, 0)) pcount,
sum(decode(name, 'execute count',value, 0)) exec,
sum(decode(name, 'seq',value, 0)) "db file seq",
sum(decode(name, 'scatt', value, 0)) "db file scatt"
    from
    (

    select name,value from v$sysstat
    where name in ('session logical reads','physical reads','execute count','parse count (hard)','db file sequential reads')
        union all
    select 'seq',nvl(sum(seconds_in_wait),0) from v$session_wait
    where event ='db file sequential read'
        union all
    select 'scat',nvl(sum(seconds_in_wait),0) from v$session_wait
    where event ='db file scattered read'

    )
;

 

 

 

3. 변화량(델타값) 스크립팅

 

하지만 위의 뷰들을 검색한 결과로는 토탈 수치만 나오기 때문에 그것만으로는 변화량을 이해할수 없다.

의미를 부여하기 위해서는 변화량(델타값)이 필요하다.

때문에 뷰를 검색한 결과를 별도의 뷰에 기록한 후 현재값과의 연산으로 변화량을 구하던지,

쉘스크립트 등을 이용하여 연산해야 한다.

요는 어디든 예전값을 기록해야 한다는것.