ORACLE 리소스 모니터링

1 DB 튜닝을 위한 v$sysstat, v$sesstat, v$system_event 모니터링[편집]

1.1 성능분석 뷰[편집]

v$sysstat
오라클 DB 인스턴스 기동 이후 “현재까지의 누적 통계치”
v$sesstat
수행 세션별 통계치 확인 뷰
v$mystat
현재 접속해 있는 자기 세션에 대한 수행통계 확인 뷰
v$system_event
이벤트에 대한 대기시간 정보를 포함하고 있는 뷰. 이벤트 발생시 정보

1.2 성능 측정 항목 조회[편집]

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.

1.3 성능분석 항목[편집]

1.3.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'
        );

1.3.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'
        );

1.3.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'
        );

1.3.4 Latch Hit %[편집]

래치 경합없이 첫번째 시도에서 곧바로 래치를 획득한 비율

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

1.3.5 Library Hit %[편집]

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

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;

1.3.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)'
        );

1.3.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'
        );

1.3.8 Parse CPU to Parse Elapsed %[편집]

파싱부하와 관련 있는 항목.
파싱 총 소요 시간 중 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'
        );

1.3.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'
        );

1.3.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)'
        );

1.3.11 OS에서 실시간 CPU 사용률[편집]

v$osstat
CPU 관련 정보 `BUSY_TIME`, `IDLE_TIME`, `NUM_CPUS` 등의 통계를 사용하여 CPU 사용률을 계산 가능

SELECT
    (SELECT value FROM v$osstat WHERE stat_name = 'BUSY_TIME') AS busy_time,
    (SELECT value FROM v$osstat WHERE stat_name = 'IDLE_TIME') AS idle_time,
    (SELECT value FROM v$osstat WHERE stat_name = 'NUM_CPUS') AS num_cpus,
    ROUND((busy_time / (busy_time + idle_time)) * 100, 2) AS cpu_usage_percentage -- cpu 사용률
FROM dual;

V$OSSTAT 의 항목 설명

BUSY_TIME : CPU가 바쁘게 사용된 시간.
IDLE_TIME : CPU가 idle(유휴 상태) 인 시간.
NUM_CPUS : 시스템의 CPU 코어 수.
* CPU 사용률 계산 *
```
`busy_time / (busy_time + idle_time)`
```
: 전체 CPU 시간 중 바쁜 시간의 비율을 계산하고, 이를 100으로 곱해 백분율로 표시

1.3.12 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';

1.3.13 % SQL with executions>1[편집]

전체 SQL 개수에서 2회 이상 수행된 SQL이 차지하는 비중

1.3.14 % Memory for SQL w/exec>1[편집]

전체 SQL이 차지하는 메모리 중 2회 이상 수행된 SQL이 차지하는 메모리 비중을 나타냄.

1.3.15 SESSION LOGICAL READS[편집]

SESSION 의 논리적 읽기 ( 메모리->CPU ) 확인
- 이 값의 일정시간 당 델타값이 증가한 다는 것 => 일량이 증가하고 있다고 판단.
- 이상징후를 확인 시 해당 SID 에 대해 TRACE, 수행 쿼리를 조회하여 조치 필요.

1.3.16 PHYSICAL READS[편집]

SESSION 의 물리적 읽기 ( 디스크->메모리 ) 확인
- 이 값이 증가하면 증가할 수록 디스크의 I/O 가 많이 발생 한것
- 이와 관련해 I/O 관련 WAIT EVENT 들이 관측될 수 있다.
많이 유발되는 SID 를 찾아 조치 필요

1.3.17 PARSE COUNT (HARD)[편집]

리터럴(LITERAL) SQL 찾아내기
- 이 지표는 상황에 따라 SYSTEM 이나 SESSION LEVEL 로 조회해야 한다.
- 순간적으로 세션을 맺고 LITERAL SQL 을 수행하고 빠지는 세션이 많은 경우엔 SYSTEM LEVEL 로 봐야 시스템에 HARD PARSING 이 많이 발생하고 있다는 것을 알 수 있음

1.3.18 EXECUTE COUNT[편집]

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

1.4 성능 분석용 조회 SQL[편집]

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'
    )
;

1.5 변화량(델타값) 조회 시[편집]

위의 뷰들을 검색한 결과로는 토탈 수치만 나오기 때문에 그것만으로는 변화량을 이해할수 없다.
의미를 부여하기 위해서는 변화량(델타값)이 필요
뷰를 검색한 결과를 별도의 저장소(테이블)에 기록한 후 현재값과의 연산으로 변화량을 구해야 한다
예전값을 기록 하여 현재량과의 변화량에 증요 하다는것.

ORACLE 리소스 모니터링

DB CAFE

목차

1 DB 튜닝을 위한 v$sysstat, v$sesstat, v$system_event 모니터링[편집]

1.1 성능분석 뷰[편집]

1.2 성능 측정 항목 조회[편집]

1.3 성능분석 항목[편집]

1.3.1 Buffer NoWait %[편집]

1.3.2 Redo NoWait %[편집]

1.3.3 Buffer Hit %[편집]

1.3.4 Latch Hit %[편집]

1.3.5 Library Hit %[편집]

1.3.6 Soft Parse %[편집]

1.3.7 Execute to Parse %[편집]

1.3.8 Parse CPU to Parse Elapsed %[편집]

1.3.9 % Non-Parse CPU[편집]

1.3.10 In-memory Sort %[편집]

1.3.11 OS에서 실시간 CPU 사용률[편집]

1.3.12 Memory Usage %[편집]

1.3.13 % SQL with executions>1[편집]

1.3.14 % Memory for SQL w/exec>1[편집]

1.3.15 SESSION LOGICAL READS[편집]

1.3.16 PHYSICAL READS[편집]

1.3.17 PARSE COUNT (HARD)[편집]

1.3.18 EXECUTE COUNT[편집]

1.4 성능 분석용 조회 SQL[편집]

1.5 변화량(델타값) 조회 시[편집]