Re: Checkpoint not complete error
- From: Jared Still <jkstill@xxxxxxxxx>
- To: saad4u@xxxxxxxxx
- Date: Fri, 24 Apr 2009 14:19:18 -0700
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
Other related posts:
