Re: DBWR Performance.

A DBWR Timeout occurs when DBWR has waited for 3 seconds without being called two write. At this point it does a few 'idle writes'. The higher the value, the less forced work DBWR is doing.

Temporary tablespace blocks are written to the database in 7.3.4, but you are correct that a proper temporary tablespace will allow the instance to allocate a single segment and allocate it on demand.

If you feel that the allocation and de-allocation of 'permanent' temporary segments is likely to be causing a signficant overhead (and in most cases the overhead is minor), then

1. increase the sort_area_size and sort_area_retained_size to reduce the number of sorts that go to disc (check v$sysstat) or
2. Increase the extent size in the temp tablespace to reduce the number of space management calls.

Check the write to the temp tablespace in v$filestat. If blocks written is the same as write requests, then you are writing temp through the db_block_buffers - which can add an heavy burden. Set sort_direct_writes to true (an if memory allows, increase the sort_area_size, sort_write_buffers
and sort_write_buffer_size).

As for redo allocation latch misses -

    how many CPUs do you have
    how many spin_gets are you getting on this latch     what is your redo_log_small_entry_size     how many immediate_gets do you have on the redo copy latch.     How does your redo copies and small copies compare in v$sysstat

You may reduce the stress on this latch by reducing the log_small_entry_size parameter so that most redo copying is done on (one of the) redo copy latches. At present you may find that a lot of redo copies are done on the allocation latch.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Bold wrote in message <388CCC6E.7032327C_at_yahoo.com>...
>Thanks for the suggestions. I know how to make a permanent tablespace
>temporary, just checking to see if others felt that was the gist of the
>cause of the performance problems.
>
>The reason I believe the performance issue could be attributed to the
>absense of a temporary tablespace is the following:
>
>1.. From what I know, DBWR does not write out dirty buffers resulting
>from temporary operations (such as blocks from sort-area segments in the
>temporary tablespace).
>2. Whenever a permanent tablespace is used for storing temporary
>segments, doesn't each transaction requiring temporary space need to
>create a temporary segment and then drop it after its use?
> This to me would create some serious overhead from segment creation,
>lock allocation, and then segment dropping..?
>3. If a temp tablespace is identified, then a temp segment is created
>the first time a transaction needs to use it, then the segment is reused
>by all other transactions requiring temp space.
>
>It seems that the constant overhead of segment creation and dropping is
>therefore avoided. Plus, don't dedicated temporary tablespaces help in
>reducing data-block buffer overhead, since DBWR doesn't write out dirty
>buffers resulting from operations on these?
>The redo log switches aren't that frequent on this database, so I don't
>think it has to do with the size or member numbers (could be wrong
>here).
>Yet the Dirty buffers inspected are high, and the DBWR timeouts seem
>unusually high to me. Plus, how would a session have negative redo size
>and wasteage numbers?.
>I just wanted to see what others felt, and whether I was heading in a
>totally off-the-wall direction.
>
>Thanks for the input.
>
>Steven Hauser wrote:
>
>> alter tablespace X temporary;
>> Unless someone uses it for permanent objects.
>>
>> How about increasing the redo log_buffer size. and increasing the
>> time between checkpoints.
>>
>> --
>> ---------------------------------------------------------
>> Steven Hauser
>> email: hause011@tc.umn.edu URL: http://www.tc.umn.edu/~hause011
>> ---------------------------------------------------------
>
>Bold wrote:
>
>> I've inherited an Oracle Instance, running on Oracle7.3.4 -- Solaris
>> 2.6.
>> There are currently some performance problems occuring with this
>> instance.
>>
>> Here are a couple of things I have noticed right away:
>>
>> 1. No Temporary Tablespace -- There is one built, but it was never
>> defined as Temporary.
>> 2. Redo Logs and DBWR seem to be having some problems, but maybe I'm
>> over-reacting here...
>> examples: redo allocation -- gets 41790370
>> misses 405,588
>> DBWR timeouts 985,407
>> Redo wastage and redo size are negative
>> numbers
>>
>> for a couple of the sessions.
>> High Redo dirty buffers being inspected.
>>
>> I am beginning to believe that much of the performance problems can be
>>
>> attributed to the absense of a temporary tablespace.
>> I plan on digging further, but ideas are welcome.
>>
>> Thanks.
>
>
>
>
>
Received on Tue Jan 25 2000 - 14:01:25 CST