Instance

General Status

select name, log_mode, open_mode
from v$database;

Archive Log Usage
This query summarizes Archive Log Switches for today. Change the where clause in this statement to change the time period analyzed.

select trunc(completion_time,'HH'), count(*)
from v$archived_log
where trunc(completion_time) = trunc(sysdate)
group by trunc(completion_time,'HH');

Transactions Per Second
Defined as User Commits + User Rollbacks.

-- Calculate Transactions Per Second
SET SERVEROUTPUT ON
DECLARE
begindate date;
enddate date;
beginval number;
endval number;
BEGIN

select sysdate, sum(value)
into begindate, beginval
from v$sysstat
where name in ('user commits','user_rollbacks');

dbms_lock.sleep(5);

select sysdate, sum(value)
into enddate, endval
from v$sysstat
where name in ('user commits','user_rollbacks');

dbms_output.put_line( (endval-beginval) ||
' transactions');

dbms_output.put_line( ((enddate-begindate) * 86400) ||
' seconds');

dbms_output.put_line( (endval-beginval) / ((enddate-begindate) * 86400) ||
' transactions per second');

END;
/

Undo Status

alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
select usn,
state,
undoblockstotal "Total",
undoblocksdone "Done",
undoblockstotal-undoblocksdone "ToDo",
decode(cputime,
0,
'unknown',
sysdate+(((undoblockstotal-undoblocksdone) /
(undoblocksdone / cputime)) / 86400))
"Estimated time to complete"
from v$fast_start_transactions;