Re: Synchronous writes & TEMP

boston103_at_hotmail.com (VC) wrote in message news:<31e0625e.0405130945.54d00c58_at_posting.google.com>...
> "VC" <boston103_at_hotmail.com> wrote in message news:<MqIoc.39444$xw3.2544076_at_attbi_s04>...
> > Does anybody know why Oracle uses synchronous writes while writing to the
> > temporary tablespace ? Presumably we do not care about data loss in this
> > case.
> >
> > Your thouhts are much appreciated.
> >
> > VC
>
> Probably I was being a little imprecise judging by lack of feedback.
>
> What I am asking here is why Oracle insists on executing synchronous
> write operations whilst writing to the TEMP tablespace. A synchronous
> write means such operation that does not return control to the caller
> until the data is presumed to be safely on the disk. By contrast, a
> non-synchronous write (the 'default') operation returns control as
> soon as the data is transferred to the filesystem cache. The
> rationale for executing synchronous writes against the data files and
> redo logs is obvious.
>
> I believe (but I may be wrong since I checked only Solaris and Linux)
> that writes to TEMP are synchronous regardless of the OS or Oracle
> version.
>
> Please do not confuse synchronous writes with asynchronous I/O --
> that's a different beast entirely. I am curious as to why exactly the
> OS write system calls against TEMP are synchronous if data integrity
> is not an issue, that's all.
>
> Thank you.
>
> VC

Interesting.
Here's a bit of a thought experiment, I'll try to verify it afterwards.
Its assumptions and guesses and may be completely wrong until its verified.

Lets assume that async_io is working on Oracle 9.2.0.5 Std Ed on RHEL 3.0 ES.
(this is where I would produce my test case).

Say that we used the correct Linux kernel and applied any/all Oracle patches to get async_io for work properly and we have verified that DBWR is executing successfully asynchronous writes. Does DBWR write to the tempfiles, or do the user processes do so? Well, what type of oracle writes perform writes to tempfiles? Direct writes (sort_direct_writes perhaps). This leads me to believe that its the user process that is writing to the tempfiles, not the DBWRn process, in batches specified by the deprecated parameter db_file_direct_io_count. Could it be that there is not a mechanism for DIRECT_IO on your platform to support asynch_io?
It could also be that since the sort_segment usage is governed by the process that has a lock on it, and not governed as part of the buffer cache, that there may be no rety mechanism if a (queued async) write request were to fail.

I glanced at James Morle's "Scaling Oracle 8i" and Jonathan Lewis "Practical Oracle 8i" but didn't find anything in particular covering writes to tempfiles, direct_io and async_io.

I guess I'll pause here, with the guess that since its the user process that is performing the direct write, and that such blocks of data are in the pga/uga not the buffer cache, that they might not have a sufficient latching structure for protection for access via multiple processes, hence an async_io write might not be a good idea.

Pd Received on Thu May 13 2004 - 20:09:07 CDT