One of my database is generating archive logs a lot lately. There is more that 1 session doing a lot of dml.
how can i identify the top session that generate the most redo?
Printable View
One of my database is generating archive logs a lot lately. There is more that 1 session doing a lot of dml.
how can i identify the top session that generate the most redo?
mine the logs and see what is actually in them
i need a query, i cannot open 100MEG archive logs and check them
there is no such query
Identify the time of archive log generation and schedule the below query in appropriate interval.
select se.username,st.value
from v$sesstat st, v$statname sn,v$session se
where st.sid=se.sid
and st.STATISTIC# = sn.STATISTIC#
and sn.name ='redo size'
and se.username is not null
order by value desc;
Capture the statisitcs to your own table.
thats not going to work if the session is not alive anymore or not running.
Whats wrong with mining 100Mb logs - they are pretty small really
Fine... Capture the statistics from a "logoff" trigger. Redo mining is not an issue as long as it is managble.Quote:
Originally Posted by davey23uk
What about
column name format a 20
set pause on
select a.sid,b.name,a.value
from v$sesstat a,v$statname b
where a.statistic#=b.statistic#
and upper(b.name) like '%REDO%' order by sid
Pay attention on
redo entries
redo writes
redo blocks written
redo write time
PLEASE NOTE: This shows only the redo gereration of the currently connected sessions since the moment they got connected. No idea about all the sessions that have generated redo and got disconnected