# sys user 실행 - 패키지 설치
@$ORACLE_HOME/rdbms/admin/spcreate.sql
# perfstat user 실행 - 스냅샷 자동 스케줄링 등록
$ORALCE_HOME/rdbms/admin/spauto.sql
SQL> EXEC STATSPACK.SNAP(i_snap_level =>7);
#스냅샷 확인
SELECT SNAP_ID, SNAP_TIME FROM STATS$SNAPSHOT;
# 수동 스냅샷 실행(perfstat)
EXEC STATSPACK.SNAP
# statspack 삭제
@$ORACLE_HOME/rdbms/admin/sptrunc.sql
# statspack 리포트
cd $ORACLE_HOME/rdbms/admin/spreport.sql
Enter value for begin_snap: 1
End Snapshot Id specified: 12
report_name : C:\report.txt
# statspack 테이블과 EE의 hist 테이블 상관관계
PERFSTAT.STATS$SNAPSHOT = dba_hist_snapshot
PERFSTAT.STATS$SYSSTAT = dba_hist_sysstat
PERFSTAT.STATS$SYS_TIME_MODEL + PERFSTAT.STATS$TIME_MODEL_STATNAME = dba_hist_sys_time_model
PERFSTAT.STATS$SQL_SUMMARY + PERFSTAT.STATS$SQL_PLAN_USAGE = dba_hist_sqlstat
PERFSTAT.STATS$SQLTEXT = dba_hist_sqltext
# statspack top sql
select * from
(
select SQL_ID,
OLD_HASH_VALUE,
MODULE,
EXECUTIONS,
round(READS/EXECUTIONS,2) READS_PER_EXEC,
round(WRITES/EXECUTIONS,2) WRITES_PER_EXEC,
round((CPU/1000000)/EXECUTIONS,2) CPU_SEC_PER_EXEC,
round((ELAPSED/1000000)/EXECUTIONS,2) ELAPSED_SEC_PER_EXEC,
TEXT_SUBSET
from
(
select
SQL_ID,
OLD_HASH_VALUE,
TEXT_SUBSET,
SQL_TEXT,
MODULE,
sum(EXECUTIONS) EXECUTIONS,
sum(DISK_READS) READS,
sum(DIRECT_WRITES) WRITES,
sum(CPU_TIME) CPU,
sum(ELAPSED_TIME) ELAPSED
from PERFSTAT.STATS$SQL_SUMMARY su, PERFSTAT.STATS$SNAPSHOT sn
where su.snap_id=sn.snap_id
-- and sn.SNAP_id between 1 and 49
and su.MODULE not like '%Orange%'
and su.TEXT_SUBSET not like '%SQL Analyze(1)%'
and su.TEXT_SUBSET not like '%DYNAMIC_SAMPLING%'
group by SQL_ID, OLD_HASH_VALUE, TEXT_SUBSET, SQL_TEXT, MODULE
)
where EXECUTIONS > 0
)
where rownum < 1000
and ELAPSED_SEC_PER_EXEC > 3
order by ELAPSED_SEC_PER_EXEC desc
;
# buffer cache
SELECT snap_time ||','||
MAX(DECODE(name, 'db block gets', change))||','||
MAX(DECODE(name, 'consistent gets', change)) ||','||
(MAX(DECODE(name, 'db block gets', change))+MAX(DECODE(name, 'consistent gets', change)))||','||
MAX(DECODE(name, 'physical reads', change)) ||','||
ROUND((MAX(DECODE(name, 'db block gets', change)) + MAX(DECODE(name, 'consistent gets', change)) - MAX(DECODE(name, 'physical reads', change))) /(MAX(DECODE(name, 'db block gets', change)) + MAX(DECODE(name, 'consistent gets', change))), 4) ||','||
MAX(DECODE(name, 'buffer busy waits', change)) ||','||
ROUND(MAX(DECODE(name, 'buffer busy waits', change)) /(MAX(DECODE(name, 'db block gets', change)) + MAX(DECODE(name, 'consistent gets', change))), 4)
FROM (SELECT t.snap_time ,
s.name ,
s.value change
FROM perfstat.stats$sysstat s , perfstat.stats$SNAPSHOT t
WHERE s.snap_id = t.snap_id
and name in ('db block gets' ,
'consistent gets',
'physical reads') )
GROUP BY snap_time
order by 1
## dictionary cache miss ratio
SELECT snap_time
||','||ROUND(DECODE(gets,0,1,1-(getmisses/gets))*100,2)
FROM (SELECT DECODE(ROW_NUMBER() OVER(ORDER BY snap_time),1,'TOTAL'
,snap_time || ' ~ ' || lag(snap_time) OVER(ORDER BY snap_time)) snap_time
,DECODE(ROW_NUMBER() OVER(ORDER BY snap_time)
,1,substr(max(lpad(snap_time,6,'0')||SUM(gets)) OVER(),7)-SUM(gets)
,SUM(gets) - lag(SUM(gets)) OVER(ORDER BY snap_time)) gets
,DECODE(ROW_NUMBER() OVER(ORDER BY snap_time)
,1,substr(max(lpad(snap_time,6,'0')||SUM(getmisses)) OVER(),7)-SUM(getmisses)
,SUM(getmisses) - lag(SUM(getmisses)) OVER(ORDER BY snap_time)) getmisses
FROM perfstat.stats$rowcache_summary s, perfstat.stats$SNAPSHOT t
WHERE s.snap_id = t.snap_id
GROUP BY snap_time
)
order by 1;
## library cache miss ratio
SELECT snap_time
||','||ROUND(DECODE(gets,0,1,gethits / gets) * 100,2)
FROM (SELECT DECODE(ROW_NUMBER() OVER(ORDER BY snap_time),1,'TOTAL'
,snap_time || ' ~ ' || lag(snap_time) OVER(ORDER BY snap_time)) snap_time
,DECODE(ROW_NUMBER() OVER(ORDER BY snap_time)
,1,substr(max(lpad(snap_time,6,'0')||SUM(gets)) OVER(),7)-SUM(gets)
,SUM(gets) - lag(SUM(gets)) OVER(ORDER BY snap_time)) gets
,DECODE(ROW_NUMBER() OVER(ORDER BY snap_time)
,1,substr(max(lpad(snap_time,6,'0')||SUM(gethits)) OVER(),7)-SUM(gethits)
,SUM(gethits) - lag(SUM(gethits)) OVER(ORDER BY snap_time)) gethits
FROM perfstat.stats$librarycache s, perfstat.stats$SNAPSHOT t
WHERE s.snap_id = t.snap_id
GROUP BY snap_time
)
order by 1;
## wait stat
SELECT SNAP_TIME||','||CLASS||','||WAIT_COUNT||','||TIME
FROM (select decode(row_number() over(partition by class order by snap_time),1,'TOTAL'
,snap_time || ' ~ ' || lag(snap_time) over(partition by class order by snap_time)) snap_time
,class
,decode(row_number() over(partition by class order by snap_time)
,1,substr(max(lpad(snap_time,6,'0')||wait_count) over(partition by class),7) - wait_count
,wait_count - lag(wait_count) over(partition by class order by snap_time)) wait_count
,decode(row_number() over(partition by class order by snap_time)
,1,substr(max(lpad(snap_time,6,'0')||time) over(partition by class),7) - time
,time - lag(time) over(partition by class order by snap_time)) time
from perfstat.stats$waitstat s , perfstat.stats$SNAPSHOT t
WHERE s.snap_id = t.snap_id
and s.wait_count <> 0
)
ORDER BY snap_time, wait_count DESC, upper(class)