RE: DBWR pegged out
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <ora_kclosson@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 9 Mar 2011 18:16:12 -0500
Oh, is TEMP the name of his temporary tablespace?
So Doug, what is your application doing?
Greg's reference in line is write complete waits
The session waits for a buffer to be written. The write is caused by normal
aging or by a cross-instance call.
Wait Time: 1 second
Parameter
Description
file#
The rollback segment id that contains the transaction that is being rolled
back
block#
The transaction flags (options) set for the transaction that is being rolled
back
id
Identifies the reason for waiting
So he asked about a rollback. But I'm not so sure the buffer being written
is a rollback or just associated with a rollback. Since it seems to be on
TEMP and we naturally suspect that is a temporary tablespace, the only thing
I can think of that would have to wait to write on TEMP in a rollback is
something on a global temporary table. Otherwise I think all that has to
happen on TEMP is freeing the space or direct writes that should have
exclusive rights to where they are writing. That seems very unlikely to
generate a lot of waits.
I could be completely wrong. But it seems like any rational person would
name that rollback buffer write complete waits if it can only be a rollback
buffer that you're waiting to have written. If that is the case, shame on
you for naming your undo tablespace TEMP.
Something doesn't add up.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Kevin Closson
Sent: Tuesday, March 08, 2011 9:55 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: DBWR pegged out
Greg,
Stop asking the *right* question! I need my bandwidth allotment to ask
rhetorical questions.
PS. Beer circa EOM March! Be there!
_____
From: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
Sent: Tue, March 8, 2011 10:51:41 AM
Subject: Re: DBWR pegged out
I'd be asking what "write complete waits" have to due with temp.
Looks to me that its something to do with transactions and
rollback/undo.
http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/waitevents003
.htm#sthref4131
What does the ASH report show?
On Tue, Mar 8, 2011 at 9:59 AM, Kevin Closson <ora_kclosson@xxxxxxxxx>
wrote:
> I'd be asking myself (and oracle perhaps) two questions: 1) why am I
writing
> to TEMP at all and 2) if sessions need to spill to temp on 11.2 why are
they
> not doing so in the direct path?
>
> BTW, what class of server is this?
>
> ________________________________
> From: Doug Gernaat <dag@xxxxxxxxxxxxxxx>
> To: oracle-l@xxxxxxxxxxxxx
> Sent: Tue, March 8, 2011 7:55:04 AM
> Subject: DBWR pegged out
>
> after oracle suggested bumping up db_writer_processes from 2 to 4, at
times
> it
> is still getting pegged out. "write complete waits" will become a top time
> event.
> drilling down will show:
>
> SID User Wait state/time Waiting Location
> 23 db_user In waiting (2ms) write complete waits, TEMP
> datafile:604
> 30 db_user In waiting (88ms) write complete waits, TEMP
> datafile:604
> 179 db_user In waiting (101ms) write complete waits,
TEMP
> datafile:604
> 189 db_user In waiting (0ms) write complete waits, TEMP
> datafile:604
> 354 db_user In waiting (109ms) write complete waits,
TEMP
> datafile:604
> 1532 db_user In waiting (6ms) write complete waits,
TEMP
> datafile:604
>
> and diff users will fall in out of these write complete waits. i always
see
> TEMP... not any other dfiles.
> also during these times... ora_ckpt will start blocking other sessions. is
> this I/O contention? any suggestions?
>
> dbwr_io_slaves is set to 0.
> this is 11.2.0.2 on solaris sparc 64.
>
> thanks
> -doug-
--
Regards,
Greg Rahn
http://structureddata.org
--
//www.freelists.org/webpage/oracle-l
Other related posts:
