dramatically increased # of log-switches/archivelogs

[Christian Schulze]

Hi fellows,

We have two databases which are very important to our business. Since some days ago on both systems there is an increasing amount of log-switches, which tend to fill up the archivelog-destinations.

Is there a simple way to find out which DB-User is causing a lot of inserts/updates ?


I have not yet opened a call at oracle. I first wanna make sure I checked all other 'simple' tricks ...

Any good Ideas ?

Christian

[Indira Aramandla · Accepted solution]

Hi Christian,

The only (supported) way you have is to use the LogMiner, in order to view any block modifications logged. This way, you should be able to view why such an amount of redo was generated. Refer "Oracle8i Administrator guide and Oracle8i Supplied Package reference (DBMS_LOGMNR)".

You can calculate the Table Access Statistics using LogMiner to Analyze Online and Archived Redo Logs (Using LogMiner: Scenarios described in the administration guide). This will point out which tables are hit the most and you can go from there to isolate further what activity is occurring on particular tables

You might try using v$session_longops

Also, the OEM Diagnostics Pack has a "Top Sessions" and "Top SQL" which you may be able to look at the different stats in there to determine which SQL is processing the most rows.


Indira A

[Yogeeraj_1]

hi christian,

First of all, i would use the v$sysstat table to see redo generated over different interval on a period of time. Look at it, come back later (e.g. 15mins), look again and subtract.

anyway, before doing that you should gather all the information on the latest changes done to the system..

also, run the following query to get an indication on Redologs:
select name, value from v$sysstat where name like '%redo%';

hope this helps!
kind regards
yogeeraj

[Yogeeraj_1]

hi,

you can also query the suspected Top processes (OSPID) using the following query at the database level:

select b.sid SID,b.serial# "Serial#", c.spid "srvPID", b.osuser, b.username, b.status, b.client_info from v$session b, v$process c where b.paddr = c.addr
and c.sPID = &OSPID

hope this helps!
regards
yogeeraj

[Patti Johnson]

Take a look at Metalink Note 167492.1, it explains how to find the sessions generating excessive redo information.

Patti

[Christian Schulze]

Thank you all,

this helped a lot.


Christian