Re: URGENT! Disk writes while doing a SELECT?

On Wed, 3 Feb 1999, Satar wrote:
>
> I'm observing the I/O of the Sun E450 Server. I do a Basic select on a view
> (which is join of 5 tables). It is a basic select with no sorting (no Order
> by). I notice that three of my datafiles are being written to: rbs, temp, and
> my data datafile.
>
> My question:
> What is going on? What is Oracle doing? Why did I see write activity (every
> time) and no Read activity (Maybe there was Read activity only the first time
> running the SELECT statement?).

How do you know there is no sort going on? Did you look at the explain plan of the query? If it chose HASH or MERGE operations, it could certainly be sorting.

Another reason that databases write during reads is to free a buffer. If your query wants to cache data and the data buffer it wants is dirty, it will write it out.

Another reason that writes during selects can occur is for BLOCK CLEANOUT operations. Block cleanouts are a standard operation in Oracle. When you commit data in Oracle, the blocks that get committed are not revisited and marked as committed and placed on the dirty list for writeout at the next checkpoint. Only the rollback segment header where the beore images were stored get marked with the commit. This speeds processing and is a design superiority of Oracle. Later, the next person who happens to look at this block, either to update or select it, will incur the work of marking the block header to prevent future sessions from having to do the block cleanout operation. There is a good description of how this works on MetaLink. Just search for "block cleanout."

If you really want to know what operations are incurring writes, just check v$sesstat and see what kind of operations your select is incurring.

> Bonus Question: I'm experiencing a 50% performance loss with the same
> database on two servers. The only thing I can think of (which isn't logical)
> is that the high performance database is on Solaris 2.5.1 and the low
> performance database is on 2.6. Can anyone relate to this?

Without looking at system events, any guess would be pure conjecture. If you have an operation that takes twice as long on the big box than on the small one, perform the operation, selecting event and time_waited from v$session_event before the operation, then after the operation. Subtract the differences, and that will tell you where all the time went.

Good luck,
--
Jeremiah Wilton http://www.wolfenet.com/~jeremiah Received on Wed Feb 03 1999 - 10:15:37 CST