topic Re: dramatically increased # of log-switches/archivelogs in Operating System - HP-UX

dramatically increased # of log-switches/archivelogs

Christian Schulze — Tue, 13 Sep 2005 02:10:21 GMT

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

Re: dramatically increased # of log-switches/archivelogs

Indira Aramandla — Tue, 13 Sep 2005 03:29:38 GMT

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

Re: dramatically increased # of log-switches/archivelogs

Yogeeraj_1 — Tue, 13 Sep 2005 03:34:11 GMT

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

Re: dramatically increased # of log-switches/archivelogs

Yogeeraj_1 — Tue, 13 Sep 2005 03:37:42 GMT

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

Re: dramatically increased # of log-switches/archivelogs

Patti Johnson — Tue, 13 Sep 2005 06:56:16 GMT

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

Patti

Re: dramatically increased # of log-switches/archivelogs

Christian Schulze — Tue, 13 Sep 2005 10:39:50 GMT

Thank you all,

this helped a lot.

Christian