[ORACLE] REDO_SIZE, USER_CALL 분석 쿼리
※
:DBID
:INSTANCE_NUMBER
:BEGIN_SNAP_ID
:END_SNAP_ID
값 설정후 스크립트 실행
snap_id 찾기
select snap_id, to_char(min(begin_time),'dd hh24:mi') DTIME, to_char(min(end_time),'dd hh24:mi') ETIME
FROM DBA_HIST_SYSMETRIC_SUMMARY
WHERE begin_time > sysdate-2
and instance_number=1
group by snap_id
order by snap_id
/
Redo Generation Size
select TO_CHAR(END_INTERVAL_TIME, 'DD HH24:MI') TIME,
round(REDO_SIZE/1024/EL,2) "Redo Size"
from (select SNAP_ID,
REDO_SIZE - LAG(REDO_SIZE, 1, 0) OVER (ORDER BY SNAP_ID) AS REDO_SIZE,
LOGICAL_READS - LAG(LOGICAL_READS, 1, 0) OVER (ORDER BY SNAP_ID) AS LOGICAL_READS,
BLOCK_CHANGES - LAG(BLOCK_CHANGES, 1, 0) OVER (ORDER BY SNAP_ID) AS BLOCK_CHANGES,
PHYSICAL_READS - LAG(PHYSICAL_READS, 1, 0) OVER (ORDER BY SNAP_ID) AS PHYSICAL_READS,
PHYSICAL_WRITES - LAG(PHYSICAL_WRITES, 1, 0) OVER (ORDER BY SNAP_ID) AS PHYSICAL_WRITES,
USER_CALLS - LAG(USER_CALLS, 1, 0) OVER (ORDER BY SNAP_ID) AS USER_CALLS,
PARSES - LAG(PARSES, 1, 0) OVER (ORDER BY SNAP_ID) AS PARSES,
HARD_PARSES - LAG(HARD_PARSES, 1, 0) OVER (ORDER BY SNAP_ID) AS HARD_PARSES,
SORTS - LAG(SORTS, 1, 0) OVER (ORDER BY SNAP_ID) AS SORTS,
LOGONS - LAG(LOGONS, 1, 0) OVER (ORDER BY SNAP_ID) AS LOGONS,
EXECUTES - LAG(EXECUTES, 1, 0) OVER (ORDER BY SNAP_ID) AS EXECUTES,
TRANSACTIONS - LAG(TRANSACTIONS, 1, 0) OVER (ORDER BY SNAP_ID) AS TRANSACTIONS
from (select SNAP_ID,
sum(case when STAT_NAME = 'redo size' then VALUE else 0 end) Redo_size,
sum(case when STAT_NAME = 'session logical reads' then VALUE else 0 end) Logical_reads,
sum(case when STAT_NAME = 'db block changes' then VALUE else 0 end) Block_changes,
sum(case when STAT_NAME = 'physical reads' then VALUE else 0 end) Physical_reads,
sum(case when STAT_NAME = 'physical writes' then VALUE else 0 end) Physical_writes,
sum(case when STAT_NAME = 'user calls' then VALUE else 0 end) User_calls,
sum(case when STAT_NAME = 'parse count (total)' then VALUE else 0 end) Parses,
sum(case when STAT_NAME = 'parse count (hard)' then VALUE else 0 end) Hard_parses,
sum(case when STAT_NAME in ('sorts (disk)','sorts (memory)') then VALUE else 0 end) Sorts,
sum(case when STAT_NAME = 'logons cumulative' then VALUE else 0 end) Logons,
sum(case when STAT_NAME = 'execute count' then VALUE else 0 end) Executes,
sum(case when STAT_NAME in ('user commits','user rollbacks') then VALUE else 0 end) Transactions
from DBA_HIST_SYSSTAT
where INSTANCE_NUMBER = :INSTANCE_NUMBER and DBID = :DBID and SNAP_ID between :BEGIN_SNAP_ID and :END_SNAP_ID
and STAT_NAME in ('redo size','session logical reads','db block changes','physical reads',
'physical writes','user calls','parse count (total)','parse count (hard)','sorts (disk)',
'sorts (memory)','logons cumulative','execute count','user commits','user rollbacks')
group by SNAP_ID)) p,
(select SNAP_ID, END_INTERVAL_TIME, extract(day from (END_INTERVAL_TIME-BEGIN_INTERVAL_TIME))*86400 +
extract(hour from (END_INTERVAL_TIME-BEGIN_INTERVAL_TIME))*3600 +
extract(minute from (END_INTERVAL_TIME-BEGIN_INTERVAL_TIME))*60 +
round(extract(second from (END_INTERVAL_TIME-BEGIN_INTERVAL_TIME))) EL
from DBA_HIST_SNAPSHOT
where INSTANCE_NUMBER = :INSTANCE_NUMBER and DBID = :DBID and SNAP_ID between :BEGIN_SNAP_ID and :END_SNAP_ID) s
where s.SNAP_ID = p.SNAP_ID
and p.SNAP_ID > :BEGIN_SNAP_ID
order by s.SNAP_ID
/
User Calls
select TO_CHAR(END_INTERVAL_TIME, 'DD HH24:MI') TIME,
round(USER_CALLS/EL,2) "User calls"
from (select SNAP_ID,
REDO_SIZE - LAG(REDO_SIZE, 1, 0) OVER (ORDER BY SNAP_ID) AS REDO_SIZE,
LOGICAL_READS - LAG(LOGICAL_READS, 1, 0) OVER (ORDER BY SNAP_ID) AS LOGICAL_READS,
BLOCK_CHANGES - LAG(BLOCK_CHANGES, 1, 0) OVER (ORDER BY SNAP_ID) AS BLOCK_CHANGES,
PHYSICAL_READS - LAG(PHYSICAL_READS, 1, 0) OVER (ORDER BY SNAP_ID) AS PHYSICAL_READS,
PHYSICAL_WRITES - LAG(PHYSICAL_WRITES, 1, 0) OVER (ORDER BY SNAP_ID) AS PHYSICAL_WRITES,
USER_CALLS - LAG(USER_CALLS, 1, 0) OVER (ORDER BY SNAP_ID) AS USER_CALLS,
PARSES - LAG(PARSES, 1, 0) OVER (ORDER BY SNAP_ID) AS PARSES,
HARD_PARSES - LAG(HARD_PARSES, 1, 0) OVER (ORDER BY SNAP_ID) AS HARD_PARSES,
SORTS - LAG(SORTS, 1, 0) OVER (ORDER BY SNAP_ID) AS SORTS,
LOGONS - LAG(LOGONS, 1, 0) OVER (ORDER BY SNAP_ID) AS LOGONS,
EXECUTES - LAG(EXECUTES, 1, 0) OVER (ORDER BY SNAP_ID) AS EXECUTES,
TRANSACTIONS - LAG(TRANSACTIONS, 1, 0) OVER (ORDER BY SNAP_ID) AS TRANSACTIONS
from (select SNAP_ID,
sum(case when STAT_NAME = 'redo size' then VALUE else 0 end) Redo_size,
sum(case when STAT_NAME = 'session logical reads' then VALUE else 0 end) Logical_reads,
sum(case when STAT_NAME = 'db block changes' then VALUE else 0 end) Block_changes,
sum(case when STAT_NAME = 'physical reads' then VALUE else 0 end) Physical_reads,
sum(case when STAT_NAME = 'physical writes' then VALUE else 0 end) Physical_writes,
sum(case when STAT_NAME = 'user calls' then VALUE else 0 end) User_calls,
sum(case when STAT_NAME = 'parse count (total)' then VALUE else 0 end) Parses,
sum(case when STAT_NAME = 'parse count (hard)' then VALUE else 0 end) Hard_parses,
sum(case when STAT_NAME in ('sorts (disk)','sorts (memory)') then VALUE else 0 end) Sorts,
sum(case when STAT_NAME = 'logons cumulative' then VALUE else 0 end) Logons,
sum(case when STAT_NAME = 'execute count' then VALUE else 0 end) Executes,
sum(case when STAT_NAME in ('user commits','user rollbacks') then VALUE else 0 end) Transactions
from DBA_HIST_SYSSTAT
where INSTANCE_NUMBER = :INSTANCE_NUMBER and DBID = :DBID and SNAP_ID between :BEGIN_SNAP_ID and :END_SNAP_ID
and STAT_NAME in ('redo size','session logical reads','db block changes','physical reads',
'physical writes','user calls','parse count (total)','parse count (hard)','sorts (disk)',
'sorts (memory)','logons cumulative','execute count','user commits','user rollbacks')
group by SNAP_ID)) p,
(select SNAP_ID, END_INTERVAL_TIME, extract(day from (END_INTERVAL_TIME-BEGIN_INTERVAL_TIME))*86400 +
extract(hour from (END_INTERVAL_TIME-BEGIN_INTERVAL_TIME))*3600 +
extract(minute from (END_INTERVAL_TIME-BEGIN_INTERVAL_TIME))*60 +
round(extract(second from (END_INTERVAL_TIME-BEGIN_INTERVAL_TIME))) EL
from DBA_HIST_SNAPSHOT
where INSTANCE_NUMBER = :INSTANCE_NUMBER and DBID = :DBID and SNAP_ID between :BEGIN_SNAP_ID and :END_SNAP_ID) s
where s.SNAP_ID = p.SNAP_ID
and p.SNAP_ID > :BEGIN_SNAP_ID
order by s.SNAP_ID
/