CH03.오라클 성능관리 - 05.V$SYSSTAT,06.V$SYSTEM_EVENT

05.V$SYSSTAT

오라클은 성능 측정 지표로써 활용 가능한 항목들을 선정하고, SQL이 수행되는 동안 지속적으로 그 항목들에 대한 누적 통계치를 저장합니다.
인스턴스 기동 후 현재까지 누전 된 수행 통계치를 시스템 레벨로 확인하고자 할 때 사용하는 뷰가 v$sysstat이고, 개별 세션별로 확인할 때 사용하는 뷰가 v$sesstat입니다. 현재 접속해 있는 본인 세션에 대한 수행통계는 v$mystat을 통해 확인할 수 있습니다.

v$statname을 조회해보면 9i 기준 264개, 11g 기준 469개가 있습니다. 19.3.0.0 기준 2036개가 있습니다.
앞에서 AutoTrace의 Statistics 옵션을 활성화시켰을 때 별도의 세션을 띄어 sys.v_$sesstat(v$sesstat의 원본 뷰,세션레벨에서의 통계를 보기 위해 사용)를 조회할 수 있는데, 그때 사용했던 통계항목들을 시스템 레벨로 확인하려면 v$sysstat을 조회하면 됩니다.

(1)시스템 수행통계 수집 및 분석
v$sysstat이나 v$sesstat에 나타나는 값들은 인스턴스 기동 후 또는 세션 수립후 현재까지 누적된 값이므로 그 값의 크고 작음만으로 의미 있는 정보를 얻기는 어렵습니다. 이를 제대로 활용하는 방법은 두 구간 사이의 변화량(증분,delta)를 구해 SQL 수행 도중에 내부적으로 어떤일들이 발생했는지를 판명하는 것입니다.
create table로 통계치를 저장할 테이블을 생성하고 SQL이나 일련의 배치 Job을 수행합니다. 작업이 완료되면 insert 문을 수행하고 커밋합니다.
그 후에 SQL수행 전의 데이터와 수행 후의 통계데이터를 비교하면 유용합니다.

-추후에 테스트하여 추가로 업로드 해보겠습니다.

(2)Ratio 기반 성능 분석
위에서 수집된 수행 통계 자료를 이용해 DB의 전반적인 건강상태를 체크할 수 있는 의미 있는 Ratio값을 구할 수 있습니다.

Buffer Nowait%: 버퍼 블록을 읽으려 할 때 buffer busy waits 대기 없이 곧바로 읽기에 성공한 비율
select round(100*(1-(bfwt/gets)),2) “Buffer NoWait$”
from
(select sum(count) bfwt from v$waitstats ),
(select value gets from v$sysstat where name = ‘session logical reads’)

Redo NoWait% : Redo 로그를 기록할 공간을 요청하지 않고 곧바로 Redo 엔트리를 기록한 비율
이 비율이 낮다면, 로그 스위칭이 느리거나 너무 자주 발생함을 의미합니다. 로그 스위칭 문제이면 로그 파일 크기 증가를 통해 해소 가능. 로그 스위칭이 자주 발생하지 않는데도 이 수치가 낮으면 I/O 서브 시스템이 느린것이 원인입니다. 덜 바쁜 디스크로 redo 로그 파일을 옮기거나 더 빠른 디바이스(SSD같은..비용이 허락되면)로 교체하는것으로 해소가능합니다.
select round(100*(1-(rlsr/rent),2) “ Redo NoWait%”
from
(select value rlsr from v$sysstat where name = ‘redo log space requests’ ),
(select value rent from v$sysstat where name = ‘redo entries’)

LGWR write
1. 3초마다
2.로그 버퍼의 1/3이 차거나 기록된 redo 레코드양이 1MB 넘을때
3. 사용자가 커밋 또는 롤백을 날릴때
1과 2는 비동기 방식으로 LGWR에 의해 진행되기 때문에 트랜잭션이 redo 레코드를 기록하면서 작업을 계속 진행하는데 영향을 미치지 않습니다. LGWR이 redo 로그 버퍼를 기록하는 동안에도 서버프로세스는 새로운 redo 엔트리를 redo 버퍼에 기록할 수 있습니다. 로그버퍼가 로그 블록단위(OS의 최소 I/O단위에 의해 결정됨,버퍼캐시블록과다름)순환방식으로 관리되기 때문에 발생합니다. 단 이때도 LGWR작업속도보다 redo 생성이 더 빨리 진행되면 redo 엔트리를 기록할 공간이 부족하게 되므로 log buffer space 이벤트가 발생합니다.
3은 동기방식으로 redo 레코드가 redo 로그에 기록되었음이 보장되어야만 트랜잭션을 마칠 수 있습니다. 따라서 LGWR이 작업을 완료할때 까지 대기해야하고 log file sync 이벤트가 발생합니다. LGWR이 작업을 빨리 마치지 못하면 1초 간격으로 log file sync 이벤트를 발생시키며 대기하게 됩니다.

Buffer Hit% : 디스크 읽기를 수반하지 않고 버퍼 캐시에서 블록 찾기에 성공한 비율. 버퍼 캐시 히트율이라고도 불림
select round(100*1-(phyr-phyrd-nvl(phyrdl,0)/gets,2) “Buffer Hit%”
from
(select value phyr from v$sysstat where name =‘physical reads’),
(select value phyrd from v$sysstat where name = ‘physical reads direct’),
(select value phyrdl from v$sysstat where name = ‘physical reads direct (lob)’),
(select value gets from v$sysstat where name = ‘session logical reads’)

Latch Hit% : 래치 경합 없이 첫번째 시도에서 곧바로 래치를 획득한 비율
select round(100*(1-sum(misses)/sum(gets),2) “Latch Hit %”
from v$latch

Library Hit%: 라이브러리 캐시율은 Get 히트율과 Pin 히트율로 나눌 수 있는데, 여기서는 Pin히트율을 표시하고 있음.
Pin 히트율은 실행 단계와 관련이 있고, 라이브러리 캐시에 이미 적재된 SQL커서를 실행하거나 오브젝트 정보를 읽으려 할 때 해당 커서 또는 오브젝트 정보가 힙영역에서 찾아진다면 히트에 성공한 것. 만약 캐시에서 밀려나 찾을 수 없는 경우가 빈번하게 발생한다면 히트율이 낮게 나타나고 그만큼 다시 로드해야 하는 부하가 생기므로 효율이 좋지 않음을 뜻함
select round(100* sum(pinhits)/sum(pins),2) “Library Cache Pin Hit%”
from v$librarycache

Get Hit율은 Parse단계와 관련이 있음. 이 수치가 낮다면 해당 SQL 커서 또는 오브젝트에 대한 핸들을 찾을 수 없어 하드파싱 또는 최초 로드가 자주 발생하는 경우임
select round(100*sum(gethits)/sum(gets),2) “Library Cache Get Hit%”
from v$librarycache

Soft Parse %: 실행계획이 라이브러리 캐시에서 찾아져 하드파싱을 일으키지 않고 SQL을 수행한 비율
select round(100*(1-(hprs/prse),2) “Soft Parse%”
from
(select value hpprs from v$sysstat where name = ‘parse count (hard)’),
(select value prse from v$sysstat where name = ‘parse count (total)’)

Parse CPU to Parse Elapsed % : 파싱 총 소요 시간 중 CPU Time이 차지한 비율.
이 값이 낮다면 파싱도중 대기가 많이 발생했음을 의미. 이 수치가 낮다면 Shared Pool과 라이브러리 캐시에 경합이 많다는 껏이고 대게 하드 파싱 부하 때문임
select decode(prsela,0,to)number(null),round(prscpu/prsela*100,2)) “Parse CPU Parse Elapsed%”
from
(select value prsela from v$sysstat where name = ‘parse time elapsed’),
(Select value prscpu from v$sysstat where name =‘parse time cpu’)

%Non-Parse Cpu: SQL을 수행하면서 사용한 전체 CPU Time 중 파싱 이외의 작업이 차지한 비율
이 비율이 낮다면 파싱과정에서 소비되는 CPU Time 비율이 높은 것이므로 파싱 부하를 줄이도록 애플리케이션을 개선 필요
select decode (tcpu,0,to_number(null),round(100*(1-prscpu.tcpu),2)) “%Non-Parse CPU”
from
(select value tcpu from v$sysstat where name = ‘CPU used by this session’),
(select value prscpu from v$sysstat where name = ‘parse time cpu’)

In-memory-Sort%: 전체 소트 수행 횟수에서 In-Memory 소트 방식으로 수행한 비율
select decode((strm+strd),0,to_number(null),round(100*srtm/(srtd+srtm),2)) “In-memory Sort%”
from
(select value srtm from v$sysstat where name = ‘sorts (memory)’),
(select value srtd from v$sysstat where name = ‘sorts (disk)’)

Memory Usage%: Shared Pool 내에서 현재 사용 중인 메모리 비중
select 100*(1-sum(decode(name,’free memory’,bytes))/sum(bytes))
from v$sgastat
where pool = ‘shared pool’

% SQL with executions>1 전체 SQL 개수에서 두 번 이상 수행된 SQL이 차지하는 비율
이 값이 낮으면 바인드 변수를 사용하지 않고 Literal 상수 값을 이용하는 쿼리의 수행빈도가 높은 것을 의미

% Memory for SQL w/exec>1 전체 SQL이 차지하는 메모리 중 두 번 이상 수행된 SQL이 차지하는 메모리 비중
이 값이 낮으면 바인드변수를 사용하지않고 literal 상수 값을 사용하는 쿼리에 의해 shared pool이 낭비되고 있음

이런 지표들은 DB전반적인 건강상태를 체크하기 위해 매우 유용한지표입니다. 하지만 문제 발생 시 그 원인을 분석하는 데에 취약한 단점이 있습니다.
시스템 레벨에서 평균적 의미의 측정이 이무러지므로 개별 세션이나 트랜잭션 레벨에서 느껴지는 속도감과 결과값이 차이가 날 수있는 것도 단점입니다.
세션 레벨에서도 측정은 가능하지만 원인 분석엔 큰 도움이 안되므로 사용을 하지 않습니다.

06.V$SYSTEM_EVENT

v$system_event는 인스턴스 기동 후 현재까지 누적된 이벤트 발생 현황을 시스템레벨로 확인하고자 할때 사용합니다. 개별 세션별로 누적치를 확인하려면 v$session_event뷰를 조회하면 됩니다. 그리고 세션레벨에서 좀더 유용하게 사용할 수 있는 뷰가 v$session_wait인데, 이는 세션별로 현재 진행 중이거나 바로 직전에 발생했던 이벤트 정보만을 보여줍니다.

앞에서 설명한 10046 이벤트 트레이스에서 레벨을 8 또는 12로 설정하면 SQL수행 도중 대기 이벤트가 발생할 때마다 트레이스 파일에 기록을 남깁니다.
OS상의 trc파일을 tail을 걸어 이벤트 발생을 실시간으로 확인할 수도 있지만 v$session_wait뷰를 통해 세션에서 어떤 이벤트가 발생하고 있는지 곧바로 확인해 볼 수 있습니다.
v$session_wait는 WAIT_TIME 컬럼 값 결과에 따라 해석이 달라집니다.
WAIT_TIME >0 마지막 대기이벤트를 대기한 시간
WAIT_TIME =0 이 이벤트를 현재 대기중
WAIT_TIME=-1 마지막 대기 이벤트를 대기한 시간이 10ms 미만
WAIT_TIME= -2 타이밍이 활성화되지 않음