isitdevops.com/databases

Select inst_id, sum(blocks*block_size)/1024/1024 as MB, trunc(completion_time)

From gv$archived_log

Group by inst_id, trunk(completion_time);

AND

 

select distinct a.thread#, a.sequence#, a.completion_time,

(a.blocks * a.block_size)/1024/1024 as MB,

(a.completion_time – b.first_time)*86400 as “Elapsed Write Secs”,

ROUND ( ((a.blocks * a.block_size)/1024/1024) / DECODE(((a.completion_time – b.first_time)*86400),0,1,((a.completion_time – b.first_time)*86400) )) AS “M/S”

from v$archived_log a, v$archived_log b

where a.sequence# +1 = b.sequence#

and a.thread# = b.thread#

and a.name like ‘+%’

and a.completion_time between

to_date(’01/aug/2013 17:00:00′,’dd/mon/yyyy hh24:mi:ss’) and

to_date(’19/aug/2013 22:00:00′,’dd/mon/yyyy hh24:mi:ss’)

and a.thread# = 1

order by 1,2;

THREAD#  SEQUENCE# COMPLETION_TIME              MB Elapsed Write Secs        M/S

———- ———- ——————– ———- —————— ———-

1      19508 17-aug-2013 14:22:25 2555.51221                 13        197

1      19509 17-aug-2013 14:31:17 2562.60986                 11        233

1      19510 17-aug-2013 14:37:05 2534.86865                 11        230

etc

 

AND

SQL TO GET “APPROXIMATE” TIME TO WRITE AN ARCHIVELOG TO DISK

select distinct a.thread#, a.sequence#, a.completion_time,

(a.blocks * a.block_size)/1024/1024 as MB,

(a.completion_time – b.first_time)*86400 as “Elapsed Write Secs”,

ROUND ( ((a.blocks * a.block_size)/1024/1024) / DECODE(((a.completion_time – b.first_time)*86400),0,1,((a.completion_time – b.first_time)*86400) )) AS “M/S”

from v$archived_log a, v$archived_log b

where a.sequence# +1 = b.sequence#

and a.thread# = b.thread#

and a.name like ‘+%’ — SHOULD USE destination = 1, not name

and a.completion_time between

to_date(’01/aug/2013 17:00:00′,’dd/mon/yyyy hh24:mi:ss’) and

to_date(’19/aug/2013 22:00:00′,’dd/mon/yyyy hh24:mi:ss’)

and a.thread# = 1

order by 1,2;

THREAD#  SEQUENCE# COMPLETION_TIME              MB Elapsed Write Secs        M/S

———- ———- ——————– ———- —————— ———-

1      19508 17-aug-2013 14:22:25 2555.51221                 13        197

1      19509 17-aug-2013 14:31:17 2562.60986                 11        233

1      19510 17-aug-2013 14:37:05 2534.86865                 11        230

 

 

etc