ORACLE 歸檔日誌資訊sql

PROMPT

PROMPT redolog switch

set linesize 300
set pages 100
column d1 form a20 heading "Date"
column sw_cnt form 99999 heading 'Number|of|Switches'
column Mb form 999,999 heading "Redo Size"
column redoMbytes form 999,999,9999 heading "Redo Log File Size (Mb)"

break on report
compute sum of sw_cnt on report
compute sum of Mb on report

var redoMbytes number;
begin
   select max(bytes)/1024/1024 into :redoMbytes from v$log;
end;
/

print redoMbytes

select trunc(first_time) d1
       , count(*) sw_cnt
       , count(*) * :redoMbytes Mb
from v$log_history
group by trunc(first_time)
/

最近三個月日誌歸檔頻繁度

select to_char(first_time,'yyyy-mm-dd'),thread#,count(*)
from v$log_history
where first_time>sysdate-90
group by to_char(first_time,'yyyy-mm-dd'),thread#
having count(*)>20
order by thread#
/

select a.f_time "日期",

a.thread#,

ceil(sum(a.blocks * a.block_size) / 1024 / 1024 / 1024) "每天歸檔量(G)",

ceil(sum(a.blocks * a.block_size) / 1024 / 1024 / 24) "每小時avg歸檔量(M)"

from (select distinct sequence#,

thread#,

blocks,

block_size,

to_char(first_time, 'yyyy/mm/dd') f_time

from v$archived_log) a

group by a.f_time, a.thread#

order by 3 desc;

日期  THREAD# 每天歸檔量(G) 每小時avg歸檔量(M)

---------- ---------- ------------- ------------------

2011/10/26  1  10  402

2011/10/31  1  5  175

2011/10/21  1  5  179

2011/10/27  1  5  171

2011/11/01  1  5  179

2011/10/18  1  5  173

2011/10/19  1  5  176

2011/10/20  1  5  185

2011/10/23  1  4  149

2011/10/29  1  4  147

2011/10/17  1  4  168

日期  THREAD# 每天歸檔量(G) 每小時avg歸檔量(M)

---------- ---------- ------------- ------------------

2011/10/28  1  4  129

2011/10/16  1  4  141

2011/10/24  1  4  168

2011/10/25  1  4  169

2011/10/30  1  4  132

2011/10/22  1  2  84

2011/11/02  1  1  8

2011/10/15  1  1  21

select to_char(first_time,'yyyy/mm/dd:hh24') "日期",thread#,count(1) "高峰時每小時歸檔個數"

from v$log_history

where trunc(first_time)

in (select d_time

from (select max(count(1)) m_arch

from v$log_history group by trunc(first_time)) a,

(select trunc(first_time) d_time,count(1) d_arch

from v$log_history group by trunc(first_time)) b

where a.m_arch=b.d_arch)

group by to_char(first_time,'yyyy/mm/dd:hh24'),thread# order by 3 desc ,thread#,1 ;

日期  THREAD# 高峰時每小時歸檔個數

------------- ---------- --------------------

2011/10/31:18  1  21

2011/10/31:17  1  7

2011/10/31:10  1  3

2011/10/31:04  1  2

2011/10/31:00  1  1

2011/10/31:09  1  1

2011/10/31:13  1  1

2011/10/31:16  1  1