다른 명령
DB 튜닝을 위한 v$sysstat, v$sesstat, v$system_event 모니터링
성능분석 뷰
- v$sysstat
- 오라클 DB 인스턴스 기동 이후 “현재까지의 누적 통계치”
- v$sesstat
- 수행 세션별 통계치 확인 뷰
- v$mystat
- 현재 접속해 있는 자기 세션에 대한 수행통계 확인 뷰
- v$system_event
- 이벤트에 대한 대기시간 정보를 포함하고 있는 뷰. 이벤트 발생시 정보
동적성능뷰 와 AWR 뷰 비교
성능 측정 항목 조회
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.
성능분석 항목
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' );
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' );
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' );
Latch Hit %
- 래치 경합없이 첫번째 시도에서 곧바로 래치를 획득한 비율
select round(100*(1-sum(a.misses)/sum(a.gets)),2) "Latch Hit %" from v$latch a;
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;
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)' );
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' );
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' );
% 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' );
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)' );
OS에서 실시간 CPU 사용률
- v$osstat
- CPU 관련 정보 `BUSY_TIME`, `IDLE_TIME`, `NUM_CPUS` 등의 통계를 사용하여 CPU 사용률을 계산 가능
SELECT busy_time , idle_time , ROUND((busy_time / (busy_time + idle_time)) * 100, 2) AS cpu_usage_percentage -- cpu 사용률 FROM (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 FROM dual);
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';
OS에서 실시간 CPU 사용률(5초간 차이) 프로시져
CREATE OR REPLACE procedure DBCAFE.SP_OSSTAT /* -- dbcafe -- V$OSSTAT 에서 CPU, VM 등의 OS 성능지표 확인 프로시져 5초 동안의 delta 값을 구해서 계산 */ IS v_cpu_busy_tm1 number := 0; /* cpu_busy time 1 */ v_cpu_busy_tm2 number := 0; /* cpu_busy time 2 */ v_cpu_idle_tm1 number := null; /* cpu_idle time 1 */ v_cpu_idle_tm2 number := null; /* cpu_idle time 2 */ v_cpu_usage_rt varchar2(3) := null; /* cpu usage ratio */ v_cpu_load varchar2(4) := null; v_vm_in_by1 number := null; v_vm_in_by2 number := null; v_vm_in_by_delta varchar2(4) := null; v_vm_out_by1 number := 0; v_vm_out_by2 number := 0; v_vm_out_by_delta varchar2(4) := ' 0'; begin -- for OS statistics select sum(decode(stat_name,'IDLE_TIME',value)) , sum(decode(stat_name,'BUSY_TIME',value)) , sum(decode(stat_name,'VM_IN_BYTES',value)) , sum(decode(stat_name,'VM_OUT_BYTES',value)) ------ ------------------------------------------- dbcafe into v_cpu_idle_tm1 , v_cpu_busy_tm1 , v_vm_in_by1 , v_vm_out_by1 ------ ------------------------------------------- dbcafe from v$osstat where stat_name in ('IDLE_TIME','BUSY_TIME','LOAD','VM_IN_BYTES','VM_OUT_BYTES') ; sys.dbms_lock.sleep(5) ; -- after 5sec. get usage try again select sum(decode(stat_name,'IDLE_TIME',value)) , sum(decode(stat_name,'BUSY_TIME',value)) , sum(decode(stat_name,'VM_IN_BYTES',value)) , sum(decode(stat_name,'VM_OUT_BYTES',value)) , lpad(trunc(sum(decode(stat_name,'LOAD',value)),1),4) ------ ------------------------------------------- dbcafe into v_cpu_idle_tm2 , v_cpu_busy_tm2 , v_vm_in_by2 , v_vm_out_by2 , v_cpu_load ------ ------------------------------------------- dbcafe from v$osstat where stat_name in ('IDLE_TIME','BUSY_TIME','LOAD','VM_IN_BYTES','VM_OUT_BYTES'); -- CPU busy rate computing if v_cpu_busy_tm2 is null then v_cpu_usage_rt := ''; else v_cpu_usage_rt := lpad(round((v_cpu_busy_tm2 - v_cpu_busy_tm1)*100 / ((v_cpu_busy_tm2 - v_cpu_busy_tm1) + (v_cpu_idle_tm2 - v_cpu_idle_tm1))),3); end if; -- When the Load is Null if v_cpu_load is null then v_cpu_load := ''; end if; --VM computing if v_vm_in_by2 is null then v_vm_in_by_delta := ''; v_vm_out_by_delta := ''; else v_vm_in_by_delta := lpad(trunc((v_vm_in_by2 - v_vm_in_by1)/1048576),4); v_vm_out_by_delta := lpad(trunc((v_vm_out_by2 - v_vm_out_by1)/1048576),4); end if; dbms_output.put_line('----------------------------'); dbms_output.put_line('|Vi(MB) Vo(MB) Load CPU(%) |'); dbms_output.put_line('----------------------------'); dbms_output.put_line('|'|| v_vm_in_by_delta || ' ' || v_vm_out_by_delta || ' ' || v_cpu_load || ' ' || v_cpu_usage_rt || ' |' ); dbms_output.put_line('----------------------------'); exception when others then dbms_output.put_line('Code : '||sqlcode); dbms_output.put_line('Message : '||sqlerrm); end;
% SQL with executions>1
- 전체 SQL 개수에서 2회 이상 수행된 SQL이 차지하는 비중
% Memory for SQL w/exec>1
- 전체 SQL이 차지하는 메모리 중 2회 이상 수행된 SQL이 차지하는 메모리 비중을 나타냄.
SESSION LOGICAL READS
- SESSION 의 논리적 읽기 ( 메모리->CPU ) 확인
- 이 값의 일정시간 당 델타값이 증가한 다는 것 => 일량이 증가하고 있다고 판단.
- 이상징후를 확인 시 해당 SID 에 대해 TRACE, 수행 쿼리를 조회하여 조치 필요.
PHYSICAL READS
- SESSION 의 물리적 읽기 ( 디스크->메모리 ) 확인
- 이 값이 증가하면 증가할 수록 디스크의 I/O 가 많이 발생 한것
- 이와 관련해 I/O 관련 WAIT EVENT 들이 관측될 수 있다.
- 많이 유발되는 SID 를 찾아 조치 필요
PARSE COUNT (HARD)
- 리터럴(LITERAL) SQL 찾아내기
- 이 지표는 상황에 따라 SYSTEM 이나 SESSION LEVEL 로 조회해야 한다.
- 순간적으로 세션을 맺고 LITERAL SQL 을 수행하고 빠지는 세션이 많은 경우엔 SYSTEM LEVEL 로 봐야 시스템에 HARD PARSING 이 많이 발생하고 있다는 것을 알 수 있음
EXECUTE COUNT
- SQL 을 수행한 횟수를 의미.
- (RECURSIVE CALL 제외) 시스템의 활동성을 의미한다.
- 보통 급증보다 급감하는 경우에 이슈가 있는 경우가 있다. (시스템이 IDLE 인 경우를 제외)
성능 분석용 조회 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' ) ;
변화량(델타값) 조회 시
- 위의 뷰들을 검색한 결과로는 토탈 수치만 나오기 때문에 그것만으로는 변화량을 이해할수 없다.
- 의미를 부여하기 위해서는 변화량(델타값)이 필요
- 뷰를 검색한 결과를 별도의 저장소(테이블)에 기록한 후 현재값과의 연산으로 변화량을 구해야 한다
- 예전값을 기록 하여 현재량과의 변화량에 증요 하다는것.