RE: To find out the Top process or Top User sessions generating highest number of archive logs

  • From: Michael Dinh <mdinh@xxxxxxxxx>
  • To: "'jwilton@xxxxxxxxxxxxx'" <jwilton@xxxxxxxxxxxxx>, "mark.powell2@xxxxxx" <mark.powell2@xxxxxx>
  • Date: Fri, 17 Feb 2012 09:15:47 -0800

A little late?

But wanted to share.

//www.freelists.org/post/oracle-l/archived-log-switch-detecting-whos-causing-excessive-redo-generation

Michael Dinh
Disparity Breaks Automation (DBA)

Great minds discuss ideas; average minds discuss events; small minds discuss 
people - Eleanor Roosevelt 
Confidence comes not from always being right but from not fearing to be wrong - 
Peter T Mcintyre
 
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Jeremiah Wilton
Sent: Thursday, February 16, 2012 11:13 AM
To: mark.powell2@xxxxxx
Cc: ORACLE-L
Subject: Re: To find out the Top process or Top User sessions generating 
highest number of archive logs

On Feb 16, 2012, at 11:27 AM, Powell, Mark wrote:

> I note that the query below always seems to return smon as the largest redo 
> generator on the couple of systems where I checked so if you are interested 
> in only user sessions you might want to add "and s.username is not null" as a 
> condition to filter out the Oracle rdbms background sessions.

I don't see a good reason to exclude background processes, especially if one 
choses to sample and delta the redo size over a number of samples. What if one 
of them is somehow generating the large amount of redo that the I am trying to 
track down?

If you do choose to exclude background processes, there's a column for that in 
gv$session.  Just select where type != 'BACKGROUND'.

Another trick to avoid doing deltas (why are we avoiding that when we have 
Tanel's snapper handy?) might be to divide the redo size by the length of time 
the session has been logged in.  That way you get sort of redo size per unit 
time figure, instead of the grand total for the lifetime of the session (which 
is what is making SMON look so large).

Regards,
--
Jeremiah Wilton
http://www.bluegecko.net
Remote DBA Services

--
//www.freelists.org/webpage/oracle-l


--
//www.freelists.org/webpage/oracle-l


Other related posts: