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

BN - if the redo is coming from a currently connected session, then you should be able to check v$sysstat with something like this:

select a.sid, a.value from v$sesstat a, v$statname b where a.statistic# = b.statistic# and b.name = 'redo size' order by 2;

Regards,
Brandon

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of BN Sent: Monday, August 29, 2005 6:53 AM
To: oracle-l_at_freelists.org
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

Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.

--

http://www.freelists.org/webpage/oracle-l Received on Mon Aug 29 2005 - 10:07:35 CDT