Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

dramatically increased # of log-switches/archivelogs

SOLVED
Go to solution
Christian Schulze
Regular Advisor

dramatically increased # of log-switches/archivelogs

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
never touch a running system
5 REPLIES
Indira Aramandla
Honored Contributor
Solution

Re: dramatically increased # of log-switches/archivelogs

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
Never give up, Keep Trying
Yogeeraj_1
Honored Contributor

Re: dramatically increased # of log-switches/archivelogs

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor

Re: dramatically increased # of log-switches/archivelogs

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Patti Johnson
Respected Contributor

Re: dramatically increased # of log-switches/archivelogs

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

Patti
Christian Schulze
Regular Advisor

Re: dramatically increased # of log-switches/archivelogs

Thank you all,

this helped a lot.


Christian
never touch a running system