Re: Identify the User & the SQL Generating tons of Redo

From: arun chakrapani rao <arunrao_oradba_at_yahoo.co.in>
Date: Mon, 29 Aug 2005 16:20:05 +0100 (BST)
Message-ID: <20050829152005.15249.qmail@web8509.mail.in.yahoo.com>

Use this sql to find the person who is generating lots of redo.

SELECT s.sid, s.serial#, s.username, s.program, i.block_changes FROM v$session s, v$sess_io i WHERE s.sid = i.sid ORDER BY 5 desc, 1, 2, 3, 4;

Run the query multiple times and examine the delta between each occurrence of BLOCK_CHANGES. Large deltas indicate high redo generation by the session. Read the Doc 167492.1 in metalink. quite helpfull.

I set this up with 9I Oem performance manager to find the delta and I was able to some of the guys generating massive redo's in one of our production database.

BN <bnsarma_at_gmail.com> wrote:

> Greetings,
>
> Is there a simple/SQL (other than Logminer) to
> Identify the User(s)
> and the affending SQL thats generating tons of REDO.
>
> It so happend last week that one of our DBs started
> generating lot of
> redologs , at one tme it became difficult to manage
> the free space for
> archived log files.
>
> We never see more than 300+ archived redolog files
> in day, that day
> we just crossed half day and we had already crossed
> 500+ archived
> redologs.
>
> Generally we will have around 300+ connections, but
> for some strange
> reason, we had 600+ connections. I cannot go and
> kill the connections
> unless I bring all the users and DEV teams on the
> call to see what
> they are trying to do.
>
> Nobody came forward to tell that they may be running
> a batch job.
> This is Orlace 9i and HP box. I couldn't see any
> thing specific at the
> unix level, when i searched sql_text, I found
> a few INSERT statements ...
>
> I would like to know if there is a SQL way in 8i and
> 9i to identify
> the users and SQL that's generating the REDO.
>
> I was trying to hit the long running jobs... I
> couldn't figure out...
> --
> Regards & Thanks
> BN
> --
> http://www.freelists.org/webpage/oracle-l
>

How much free photo storage do you get? Store your friends 'n family snaps for FREE with Yahoo! Photos http://in.photos.yahoo.com

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

Received on Mon Aug 29 2005 - 10:22:06 CDT