Re: Checkpoint not complete error

From: Saad Khan <saad4u_at_gmail.com>
Date: Mon, 27 Apr 2009 14:13:55 -0400
Message-ID: <76b3d4e30904271113q1cac8a49sd34aef978d7ec677_at_mail.gmail.com>

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_at_gmail.com> wrote:

>
> On Fri, Apr 24, 2009 at 1:37 PM, Saad Khan <saad4u_at_gmail.com> 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
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Apr 27 2009 - 13:13:55 CDT