BN,
I agree with Michael if you have STATSPACK configured. Otherwise, look in
V$SQL, joined with V$SESSION. It doesn't directly have redo logs, but this
process will probably stand out in other ways, like the amount of execution
time.
Dennis Williams
On 8/29/05, Michael McMullen <ganstadba_at_hotmail.com> wrote:
>
> I had the same problem once. My statspack snapshots located the offender
> pretty quickly.
> ----- Original Message -----
> From: "BN" <bnsarma_at_gmail.com>
> To: <oracle-l_at_freelists.org>
> Sent: Monday, August 29, 2005 9:52 AM
> Subject: Identify the User & the SQL Generating tons of Redo
>
>
> > 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
> >
> --
> http://www.freelists.org/webpage/oracle-l
>
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Aug 29 2005 - 09:20:07 CDT
