Thanks Jared, Vamshi, Sarah, Mark, Jonathan and Ivan for your valuable input. I've set the following parameters to get a control over abrupt logswitching and havent heard any cry from the users yet. The room for performance improvement is still there though: LOG_CHECKPOINT_INTERVAL = 10000 LOG_CHECKPOINT_TIMEOUT = 1800 LOG_CHECKPOINTS_TO_ALERT= TRUE ARCHIVE_LAG_TARGET= 1800 And here is the extract of alert logfile after these changes reflecting the logswitch occuring in exactly 30 min: *Mon Apr 27 13:06:52 2009 Beginning log switch checkpoint up to RBA [0xc5a8.2.10], SCN: 30981657 Thread 1 advanced to log sequence 50600 Current log# 5 seq# 50600 mem# 0: /dblogs/oracle/CLDQA1/redo/logCLDQA105.ora Mon Apr 27 13:11:32 2009 Completed checkpoint up to RBA [0xc5a8.2.10], SCN: 30981657 Mon Apr 27 13:14:25 2009 Incremental checkpoint up to RBA [0xc5a8.419.0], current log tail at RBA [0xc5a8.419.0] Mon Apr 27 13:36:51 2009 Beginning log switch checkpoint up to RBA [0xc5a9.2.10], SCN: 30985876 Thread 1 advanced to log sequence 50601 Current log# 6 seq# 50601 mem# 0: /dblogs/oracle/CLDQA1/redo/logCLDQA106.ora Mon Apr 27 13:41:54 2009 Completed checkpoint up to RBA [0xc5a9.2.10], SCN: 30985876 Mon Apr 27 13:44:28 2009 Incremental checkpoint up to RBA [0xc5a9.935.0], current log tail at RBA [0xc5a9.c9e.0] Mon Apr 27 14:06:52 2009 Beginning log switch checkpoint up to RBA [0xc5aa.2.10], SCN: 30988484* Any suggestions to further improve the performance are appreciated. On Fri, Apr 24, 2009 at 5:19 PM, Jared Still <jkstill@xxxxxxxxx> wrote: > > On Fri, Apr 24, 2009 at 1:37 PM, Saad Khan <saad4u@xxxxxxxxx> wrote: > >> Ok gurus, I've resized the redologs to 500MB but the users are now >> complaining that the app is dragging. In the alert logs I dont see any other >> message neither I see logswitches occuring fastly. >> > > It might be interesting to see what the user sessions are waiting on. > > Assuming of course that the users app directly logs on to the database. > If not, then this may be less helpful. > No one here knows anything about your application or environment. > Increasing the number and/or size of logs is what is normally needed > for 'cannot archive log' errors. > > But then again, maybe your storage system is overwhelmed? > Hard to say without more info. > > But, back to the users. > > The following query will show what sessions are currently waiting on . > > Keep in mind this bit from the docs: > > WAIT_TIME NUMBER A nonzero value is the session's last wait time. A zero > value means the session is currently waiting. SECONDS_IN_WAIT NUMBER If > WAIT_TIME = 0, then SECONDS_IN_WAIT is the seconds spent in the current > wait condition. If WAIT_TIME > 0, then SECONDS_IN_WAIT is the seconds > since the start of the last wait, and SECONDS_IN_WAIT - WAIT_TIME / 100 is > the active seconds since the last wait ended. STATE VARCHAR2(19) Wait > state: > > - > > 0 - WAITING (the session is currently waiting) > - > > -2 - WAITED UNKNOWN TIME (duration of last wait is unknown) > - > > -1 - WAITED SHORT TIME (last wait <1/100th of a second) > - > > >0 - WAITED KNOWN TIME (WAIT_TIME = duration of last wait) > > > > select > s.username username, > e.event event, > s.sid, > e.p1text, > e.p1, > e.p2text, > e.p2, > e.seq# seq, > e.wait_time, > e.seconds_in_wait, > e.state > from v$session s, v$session_wait e > where s.username is not null > and s.sid = e.sid > and s.username like upper('&uusername') > -- skip sqlnet idle session messages > and e.event not like '%message%client' > order by s.username, upper(e.event) > / > > > Jared Still > Certifiable Oracle DBA and Part Time Perl Evangelist > > >