주요 성능분석지표 SQL

/*
Buffer Nowait : 버퍼블록을 읽으려 할때 buffer busy waits 대기 없이 읽기에 성공한 비율
Redo Nowait   : Redo 로그를 기록할 공간을 요청하지 않고 바로 Redo 엔트리를 기록한 비율
Buffer Hit%   : 디스크 읽기를 수반하지 않고 버퍼캐시에서 블록찾기에 성공한 비율
Latch Hit%    : 래치 경합없이 첫번째 시도에서 바로 래치를 획득한 비율
Library Hit(PIN)% : 실행단계에서 라이브러리 캐시에 이미 적재된 SQL 커서를 실행하거나 오브젝트 정보를 읽으려 할때 힙영역에서 찾은 비율
Library Hit(GET)% : Parse 단계에서 SQL or 오브젝트에 대한 핸들이 없어 하드파싱 또는 최초로드가 자주 발생하면 이수치가 낮다
Soft Parse%   : 실행계획이 라이브러리 캐시에서 찾아져 하드파싱을 일으키지 않고 SQL을 수행한 비율
Execute To Parse : Parse Call 없이 곧바로 SQL을 수행한 비율,즉 커서를 App에서 캐싱한 채 반복 수행한 비율
Parse CPU to Parse Elapsed : 파싱 총 소요시간 중 CPU Time이 차지한 비율(이값이 낮으면 파싱도중 대기가 많이 발생했음을 의미)
%Non-Parse CPU : SQL을 수행중 사용한 전체 CPU Time 중 파싱 이외의 작업이 차지한 비율이다. 이 비율이 낮다면 CPU Time비율이 높은것이므로
                  파싱부하가 높다는것을 의미한다.(애플리케이션 개선필요)
In-memory Sort% : 메모리 소트비율
Memory Usage : Shared Pool 내에서 현재 사용중인 메모리 비중
*/
select sum(a) "Buffer Nowait%",sum(b) "Redo Nowait%",sum(c) "Buffer Hit%",sum(d) "Latch Hit%",sum(e) "Library Cache Hit(PIN)%",sum(f) "Library Cache Hit(GET)%",sum(g) "Soft Parse%",sum(h) "Execute To Parse%"
,sum(i) "Parse CPU to Parse Elapsed%",sum(j) "%Non-Parse CPU",sum(k) "In-memory Sort%",sum(l) "Memory Usage%"
from
(
select round(100*(1-bfwt/gets),2) a,0 b,0 c,0 d,0 e,0 f,0 g,0 h,0 i,0 j,0 k,0 l
from
(select sum(count) bfwt from v$waitstat),
(select value gets from v$sysstat where name ='session logical reads')
union all
select 0,round(100*(1-rlsr/rent),2) "Redo Nowait%" ,0,0,0,0,0,0,0,0,0,0
from
(select value rlsr from v$sysstat where name ='redo log space requests'),
(select value rent from v$sysstat where name ='redo entries')
union all
select 0,0,round(100*(1-(phyr-phyrd-nvl(phyrdl,0))/gets),2) "Buffer Hit%",0,0,0,0,0,0,0,0,0
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')
union all
select 0,0,0,round(100*(1-sum(misses)/sum(gets)),2) "Latch Hit%" ,0,0,0,0,0,0,0,0
from v$latch
union all
select 0,0,0,0,round(100*sum(pinhits)/sum(pins),2) "Library Cache Pin Hit%",0,0,0,0,0,0,0
from v$librarycache
union all
select 0,0,0,0,0,round(100*sum(gethits)/sum(gets),2) "Library Cache get Hit%",0,0,0,0,0,0
from v$librarycache
union all
select 0,0,0,0,0,0,round(100*(1-hprs/prse),2) "Soft Parse%",0,0,0,0,0
from
(select value hprs from v$sysstat where name = 'parse count (hard)'),
(select value prse from v$sysstat where name = 'parse count (total)')
union all
select 0,0,0,0,0,0,0,round((1-prse/exe)*100,2) "Execute to Parse%",0,0,0,0
from
(select value prse from v$sysstat where name ='parse count (total)'),
(select value exe from v$sysstat where name ='execute count')
union all
select 0,0,0,0,0,0,0,0,decode(prsela,0,to_number(null),round(1-(prscpu/prsela)*100,2)) "Parse CPU to Parse Elapsd%",0,0,0
from (select value prsela from v$sysstat where name ='parse time elapsed'),
(select value prscpu from v$sysstat where name ='parse time cpu')
union all
select 0,0,0,0,0,0,0,0,0,decode(tcpu,0,to_number(null),round(100*(1-(prscpu/tcpu)),2)) "%Non-Parse CPU",0,0
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')
union all
select 0,0,0,0,0,0,0,0,0,0,decode((srtm+srtd),0,to_number(null),round(100*srtm/(srtd+srtm),2)) "In-memory Sort%",0
from (select value srtm from v$sysstat where name ='sorts (memory)'),
(select value srtd from v$sysstat where name ='sorts (disk)')
union all
select 0,0,0,0,0,0,0,0,0,0,0,round(100*(1-sum(decode(name,'free memory',bytes))/sum(bytes)),2)
from v$sgastat
where pool='shared pool'
);